R Transpose A column into a Binary Row

0
votes

I have a single data column that looks like this for "one record" There can be hundreds if not thousands of records where each record has a different ID but many different attributes ColName2 I want to transpose the table so it looks like table 2 Is this possible in R. It is possible in Rapid-miner but i would like to implement it in R

What I have Table 1

  • ID ColName2
  • 1A Item1
  • 1A Item2
  • 1A Item3
  • 1A Item4
  • 2A Item5

What I want - Table 2

  • ID Item1 Item2 Item3 Item4 Item 5
  • 1A 1 1 1 1 0
  • 2A 0 0 0 0 1

Thanks

1
sqlrtranspose

1 Answers

1
votes

You can use reshape2 for this, for example:

> df <- data.frame(ID = c(rep("1A", 4), "2A"), ColName = 1:5)
> df
#  ID ColName
#1 1A       1
#2 1A       2
#3 1A       3
#4 1A       4
#5 2A       5

library(reshape2)

> df2 <- dcast(df, ID ~ ColName, fun.aggregate = any, value.var = "ColName")

The result of this reshapeing is:

  ID     1     2     3     4     5
1 1A  TRUE  TRUE  TRUE  TRUE FALSE
2 2A FALSE FALSE FALSE FALSE  TRUE

So you have logical values (TRUE where you want 1 and FALSE where you want 0). Since you can convert logical values to numeric, where TRUE == 1 and FALSE == 0, you just need to convert all columns (except the first) to numeric. To do this, you can use lapply on the data.frame except the first column (indicated by df2[-1]) and apply the function as.numeric to each of the other columns:

> df2[-1] <- lapply(df2[-1], as.numeric)
> df2
#  ID 1 2 3 4 5
#1 1A 1 1 1 1 0
#2 2A 0 0 0 0 1

lapply is often quite useful if you want to apply a function to all columns of a data.frame or all elements in a list. For some more information check out ?lapply and this question.