I have a data frame which looks like this:
structure(list(ab = c(0, 1, 1, 1, 1, 0, 0, 0, 1, 1), bc = c(1,
1, 1, 1, 0, 0, 0, 1, 0, 1), de = c(0, 0, 1, 1, 1, 0, 1, 1, 0,
1), cl = c(1, 2, 3, 1, 2, 3, 1, 2, 3, 2)), .Names = c("ab", "bc",
"de", "cl"), row.names = c(NA, -10L), class = "data.frame")
The column cl indicates a cluster association and the variables ab,bc & de carry binary answers, where 1 indicates yes and 0 - No.
I am trying to create a table cross tabbing cluster along with all the other columns in the data frame viz ab, bc and de, where the clusters become column variables. The desired output is like this
1 2 3
ab 1 3 2
bc 2 3 1
de 2 3 1
I tried the following code:
with(newdf, tapply(newdf[,c(3)], cl, sum))
This provides me values cross tabbing only one column at a time. My data frame has 1600+ columns with 1 cluster column. Can someone help?
Your data is in a half-long half-wide format, and you want it in a fully wide format. This is easiest if we first covert it to a fully long format:
library(reshape2)
df_long = melt(df, id.vars = "cl")
head(df_long)
# cl variable value
# 1 1 ab 0
# 2 2 ab 1
# 3 3 ab 1
# 4 1 ab 1
# 5 2 ab 1
# 6 3 ab 0
Then we can turn it into a wide format, using sum as the aggregating function:
dcast(df_long, variable ~ cl, fun.aggregate = sum)
# variable 1 2 3
# 1 ab 1 3 2
# 2 bc 2 3 1
# 3 de 2 3 1