Mehdi Farhangian Mehdi Farhangian - 2 months ago 6
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

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