Easy way to convert long to wide format with counts

Question

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

The aggregation parameter in the `dcast` function of the `reshape2` package defaults to `lenght` (= count). So in your case this would be: `dcast(sample.data, Case ~ Decision)` — Jaap, Dec 22 '15 at 14:23
Thanks @Jaap. I will definitely check out the dcast function more, and learn its uses. Even though the `length` (= count) had me for a minute. — dGecko, Dec 22 '15 at 14:40

Jaap · Answer 1 · 2017-09-15T11:07:16.643

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.

score 10 · Accepted Answer · answered Dec 22 '15 at 14:29

10

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

answered Dec 22 '15 at 14:29

TARehman

6,659
3
33
60

How would this work if `sample.data` has 2 factor variables. `table(Case = sample.data$Case,sample.data$Decision,sample.data$Reason)` gives a confusing result. – dGecko Dec 22 '15 at 15:09
While this is technically a separate question, basically, use `as.data.frame` to coerce the table into a data frame. – TARehman Dec 22 '15 at 15:09
Apologies. Will make a new question for it. – dGecko Dec 22 '15 at 15:21
Good use of `table` plus one. – akrun Dec 22 '15 at 16:15

score 6 · Answer 3 · answered Dec 22 '15 at 16:17

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

score 3 · Answer 4 · answered Dec 22 '15 at 16:18

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

Easy way to convert long to wide format with counts

4 Answers4

Linked

Related