Equivalent of two table() functions in dplyr

Question

I have a column with two variables that I would like to count occurrences of by date.

> testData
   plan_type       date
1 subscriber 2016-09-06
2 subscriber 2017-01-19
3 subscriber 2016-10-07
4        PPU 2017-01-19
5        PPU 2015-06-17
6        PPU 2015-07-03

I know that this can be done for example by subsetting into two different dataframes - one with only subscriber and one with only PPU data, then use table() and bind the two dataframes together. But I'd like to find a more efficient solution with dplyr that can do this in one command.

The output should look something like this, with NA values where there is no data for one of the variables.

> output 
        date subscriber  PPU
1 2015-06-17       <NA>    1
2 2015-07-03       <NA>    1
3 2016-09-06          1 <NA>
4 2016-10-07          1 <NA>
5 2017-01-19          1    1

Is there a specific formula that would perform this function in dplyr?

`reshape2::dcast(testData, date ~ plan_type, length)`. See also http://stackoverflow.com/questions/34417973/easy-way-to-convert-long-to-wide-format-with-counts/34418124 — David Arenburg, Apr 03 '17 at 10:44

score 1 · Accepted Answer · answered Apr 03 '17 at 10:44

With dplyr you can create a new costant columns n and then spread():

library(dplyr)
library(tidyr)

df %>% 
    mutate(n = 1) %>% 
    spread(plan_type, n)

#>         date PPU subscriber
#> 1 2015-06-17   1         NA
#> 2 2015-07-03   1         NA
#> 3 2016-09-06  NA          1
#> 4 2016-10-07  NA          1
#> 5 2017-01-19   1          1

Data:

df <- read.table(text = '   plan_type       date
                 1 subscriber 2016-09-06
                 2 subscriber 2017-01-19
                 3 subscriber 2016-10-07
                 4        PPU 2017-01-19
                 5        PPU 2015-06-17
                 6        PPU 2015-07-03', header = T)

score 0 · Answer 2 · answered Apr 03 '17 at 10:45

With data.table we can use dcast

library(data.table)
dcast(setDT(df), date~plan_type, length)
#         date PPU subscriber
#1: 2015-06-17   1          0
#2: 2015-07-03   1          0
#3: 2016-09-06   0          1
#4: 2016-10-07   0          1
#5: 2017-01-19   1          1

Equivalent of two table() functions in dplyr

2 Answers2