Easy way to convert long to wide format with count

Question

This question already has an answer here:

• Faster ways to calculate frequencies and cast from long to wide 3 answers

I have the following data set:

sample.data <- data.frame(Step = c(1,2,3,4,1,2,1,2,3,1,1),
                          Case = c(1,1,1,1,2,2,3,3,3,4,5),
                          Decision = c("Referred","Referred","Referred","Approved","Referred","Declined","Referred","Referred","Declined","Approved","Declined"))

sample.data

   Step Case Decision
1     1    1 Referred
2     2    1 Referred
3     3    1 Referred
4     4    1 Approved
5     1    2 Referred
6     2    2 Declined
7     1    3 Referred
8     2    3 Referred
9     3    3 Declined
10    1    4 Approved
11    1    5 Declined

Is it possible in R to translate this into a wide table format, with the decisions on the header, and the value of each cell being the count of the occurrence, for example:

Case    Referred    Approved    Declined
1          3           1            0
2          1           0            1
3          2           0            1
4          0           1            0
5          0           0            1

Answer 1

You can accomplish this with a simple table() statement. You can play with setting factor levels to get your responses the way you want.

sample.data$Decision <- factor(x = sample.data$Decision,
                               levels = c("Referred","Approved","Declined"))

table(Case = sample.data$Case,sample.data$Decision)

Case Referred Approved Declined
   1        3        1        0
   2        1        0        1
   3        2        0        1
   4        0        1        0
   5        0        0        1

Answer 2

We can use the base R xtabs

xtabs(Step~Case+Decision, transform(sample.data, Step=1))
#      Decision
#  Case Approved Declined Referred
#  1        1        0        3
#  2        0        1        1
#  3        0        1        2
#  4        1        0        0
#  5        0        1        0

Answer 3

The aggregation parameter in the dcast function of the reshape2-package defaults to length (= count). In the data.table-package an improved version of the dcastfunction is implemented. So in your case this would be:

library('reshape2') # or library('data.table')
newdf <- dcast(sample.data, Case ~ Decision)

or with using the parameters explicitly:

newdf <- dcast(sample.data, Case ~ Decision,
               value.var = "Decision", fun.aggregate = length)

This gives the following dataframe:

> newdf
  Case Approved Declined Referred
1    1        1        0        3
2    2        0        1        1
3    3        0        1        2
4    4        1        0        0
5    5        0        1        0

If you don't specify an aggregation function, you get a warning telling you that dcast is using lenght as a default.

Answer 4

Here's a dplyr + tidyr approach:

if (!require("pacman")) install.packages("pacman")
pacman::p_load(dplyr, tidyr)

sample.data %>%
    count(Case, Decision) %>%
    spread(Decision, n, fill = 0)

##    Case Approved Declined Referred
##   (dbl)    (dbl)    (dbl)    (dbl)
## 1     1        1        0        3
## 2     2        0        1        1
## 3     3        0        1        2
## 4     4        1        0        0
## 5     5        0        1        0