Mehdi Farhangian Mehdi Farhangian - 10 months ago 41
R Question

A Pivot table in r with binary output

I have the following dataset

#datset

id attributes value
1 a,b,c 1
2 c,d 0
3 b,e 1


I wish to make a pivot table out of them and assign binary values to the attribute (1 to the attributes if they exist otherwise assign 0 to them). My ideal output will be the following:

#output

id a b c d e Value
1 1 1 1 0 0 1
2 0 0 1 1 0 0
3 0 1 0 0 1 1


Any tip is really appreciated.

Answer Source

We split the 'attributes' column by ',', get the frequency with mtabulate from qdapTools and cbind with the first and third column.

library(qdapTools)
cbind(df1[1], mtabulate(strsplit(df1$attributes, ",")), df1[3])
#  id a b c d e value
#1  1 1 1 1 0 0     1
#2  2 0 0 1 1 0     0
#3  3 0 1 0 0 1     1