JSON data for NVD3 grouped barcharts

Consider such a dataset:

dat

   Code Project      Status
1   A01     Pr1 In progress
2   A02     Pr1    Complete
3   A03     Pr2    Complete
4   A04     Pr1    Complete
5   A05     Pr1 In progress
6   A06     Pr3    Complete
7   A07     Pr3 In progress
8   A08     Pr3    Complete
9   A09     Pr2    Complete
10  A10     Pr2 In progress

You want to make this bar chart:

library(ggplot2)
ggplot(dat, aes(x=Project)) + geom_bar(aes(fill=Status))

Nice, but you’d prefer an interactive bar chart, such as those we can create with the Javascript NVD3 library.

To do so, you need JSON data like this:

[
  {
    "key": "Complete",
    "values": [
      {
        "label": "Pr1",
        "value": 2
      },
      {
        "label": "Pr2",
        "value": 2
      },
      {
        "label": "Pr3",
        "value": 2
      }
    ],
    "color": "blue"
  },
  {
    "key": "In progress",
    "values": [
      {
        "label": "Pr1",
        "value": 2
      },
      {
        "label": "Pr2",
        "value": 1
      },
      {
        "label": "Pr3",
        "value": 1
      }
    ],
    "color": "red"
  }
] 

It is very easy to generate this JSON data with R.

Proceed as follows:

library(data.table)
DT0 <- as.data.table(dat)
DT1 <- DT0[, .(count=.N), keyby=.(Project,Status)]
DT2 <- DT1[CJ(Project=unique(DT1$Project), Status=unique(DT1$Status))]
DT2[is.na(count), count:=0L]
DT3 <- DT2[, .(values=list(data.table(label=`Project`, value=`count`))), by="Status"]
names(DT3)[1] <- "key"
DT3[, `:=`(color=c("blue","red"))]

And that’s it:

jsonlite::toJSON(DT3, pretty=TRUE)

[
  {
    "key": "Complete",
    "values": [
      {
        "label": "Pr1",
        "value": 2
      },
      {
        "label": "Pr2",
        "value": 2
      },
      {
        "label": "Pr3",
        "value": 2
      }
    ],
    "color": "blue"
  },
  {
    "key": "In progress",
    "values": [
      {
        "label": "Pr1",
        "value": 2
      },
      {
        "label": "Pr2",
        "value": 1
      },
      {
        "label": "Pr3",
        "value": 1
      }
    ],
    "color": "red"
  }
]