R: Spread key-value pairs when keys are in different columns and return value frequency

1
votes

I have searched around but could not find a particular answer to my question.

Suppose I have a data frame df: 

df = data.frame(id = c(10, 11, 12, 13, 14),
                V1 = c('blue', 'blue', 'blue', NA, NA),
                V2 = c('blue', 'yellow', NA, 'yellow', 'green'),
                V3 = c('yellow', NA, NA, NA, 'blue'))

I want to use the values of V1-V3 as unique column headers and I want the occurrence frequency of each of those per row to populate the rows.

Desired output:

desired = data.frame(id = c(10, 11, 12, 13, 14),
                     blue = c(2, 1, 1, 0, 1),
                     yellow = c(1, 1, 0, 1, 0),
                     green = c(0, 0, 0, 0, 1))

There is probably a really cool way to do this with tidyr::spread and dplyr::summarise. However, I don't know how to spread the V* columns when the keys I want to spread by are all over the place in different columns and include NAs.

Thanks for any help!

1
rdplyrtidyr

1 Answers

2
votes

Using meltand dcast from package reshape2:

dcast(melt(df, id="id", na.rm = TRUE), id~value)

  id blue green yellow
1 10    2     0      1
2 11    1     0      1
3 12    1     0      0
4 13    0     0      1
5 14    1     1      0

As suggested by David Arenburg, it is just simpler to use recast, a wrapper for melt and dcast:

recast(df, id ~ value, id.var = "id")[,1:4]   # na.rm is not possible then

  id blue green yellow
1 10    2     0      1
2 11    1     0      1
3 12    1     0      0
4 13    0     0      1
5 14    1     1      0