Cross Tabulation in R Dataframe

Question

I have a dataframe in R:

Subject  T  O  E  P  Score
1        0  1  0  1   256
2        1  0  1  0   325 
2        0  1  0  1   125
3        0  1  0  1   27
4        0  0  0  1   87
5        0  1  0  1   125
6        0  1  1  1   100

This is just a display of the dataframe. In reality, I have a lot of lines for each of the subjects. But the subjects are only from 1 to 6

For each Subject, the possible values are:

• T : 0 or 1

• O : 0 or 1

• E : 0 or 1

• P : 0 or 1

• Score : Numeric value

I want to create a new dataframe with 6 lines (one for each subject) and the calculated MEAN score for each of these combinations :

T , O , E , P , TO , TE, TP, OE , OP , PE , TOP , TOE , POE , PET

The above will the columns of the new dataframe.

The final output should look like this

Subject  T    O   E   P   TO  TE  TP   OE   OP  PE  TOP  TOE  POE  PET
1       
2
3
4
5
6

For each of these lines x columns the value is the MEAN SCORE

I tried aggregate and table but I can't seem to get what I want

Sorry I am new to R

Thanks

Answer 1

I'd do it like this:

# using your table data
df = read.table(text = 
"Subject  T  O  E  P  Score
1        0  1  0  1   256
2        1  0  1  0   325 
2        0  1  0  1   125
3        0  1  0  1   27
4        0  0  0  1   87
5        0  1  0  1   125
6        0  1  1  1   100", stringsAsFactors = FALSE, header=TRUE)

# your desired column names
new_names <- c("T", "O", "E", "P", "TO", "TE", "TP", "OE",
               "OP", "PE", "TOP", "TOE", "POE", "PET")

# assigning each of your scores to one of the desired column names
assign_comb <- function(dfrow) {
  selection <- c("T", "O", "E", "P")[as.logical(dfrow[2:5])]
  do.call(paste, as.list(c(selection, sep = "")))
}
df$comb <- apply(df, 1, assign_comb)

# aggregate all the means together
df_agg <- aggregate(df$Score ~ df$comb + df$Subject, FUN = mean)

# reshape the data to wide format
df_new <- reshape(df_agg, v.names = "df$Score", idvar = "df$Subject", 
                  timevar = "df$comb", direction = "wide")

# clean up the column names to match your desired output
# any column names not found will be added as NA
colnames(df_new) <- gsub("df\\$|Score\\.", "", colnames(df_new))
df_new[, new_names[!new_names %in% colnames(df_new)]] <- NA
df_new <- df_new[, c("Subject", new_names)]

With the result:

> df_new
  Subject  T  O  E  P TO  TE TP OE  OP PE TOP TOE POE PET
1       1 NA NA NA NA NA  NA NA NA 256 NA  NA  NA  NA  NA
2       2 NA NA NA NA NA 325 NA NA 125 NA  NA  NA  NA  NA
4       3 NA NA NA NA NA  NA NA NA  27 NA  NA  NA  NA  NA
5       4 NA NA NA 87 NA  NA NA NA  NA NA  NA  NA  NA  NA
6       5 NA NA NA NA NA  NA NA NA 125 NA  NA  NA  NA  NA
7       6 NA NA NA NA NA  NA NA NA  NA NA  NA  NA  NA  NA

Answer 2

I had to rebuild sample data to answer the question as I understood it, tell me if it works for you :

set.seed(2)
df <- data.frame(subject=sample(1:3,9,T),
                 T = sample(c(0,1),9,T),
                 O = sample(c(0,1),9,T),
                 E = sample(c(0,1),9,T),
                 P = sample(c(0,1),9,T),
                 score=round(rnorm(9,10,3)))

#   subject T O E P score
# 1       1 1 0 0 1    12
# 2       3 1 0 1 0     9
# 3       2 0 1 0 1    13
# 4       1 1 0 0 0     3
# 5       3 0 1 0 1    14
# 6       3 0 0 1 0    13
# 7       1 1 0 1 0    17
# 8       3 1 0 1 0    12
# 9       2 0 0 1 1    14

cols1 <- c("T","O","E","P")
df$comb <- apply(df[cols1],1,function(x) paste(names(df[cols1])[as.logical(x)],collapse=""))

#   subject T O E P score comb
# 1       1 1 0 0 1    12   TP
# 2       3 1 0 1 0     9   TE
# 3       2 0 1 0 1    13   OP
# 4       1 1 0 0 0     3    T
# 5       3 0 1 0 1    14   OP
# 6       3 0 0 1 0    13    E
# 7       1 1 0 1 0    17   TE
# 8       3 1 0 1 0    12   TE
# 9       2 0 0 1 1    14   EP

library(tidyverse)

df %>%
  group_by(subject,comb) %>%
  summarize(score=mean(score)) %>%
  spread(comb,score) %>%
  ungroup

# # A tibble: 3 x 7
#   subject     E    EP    OP     T    TE    TP
# *   <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
# 1       1    NA    NA    NA     3  17.0    12
# 2       2    NA    14    13    NA    NA    NA
# 3       3    13    NA    14    NA  10.5    NA

The second step in base R:

means <- aggregate(score ~ subject + comb,df,mean)
means2 <- reshape(means,timevar="comb",idvar="subject",direction="wide")
setNames(means2,c("subject",sort(unique(df$comb))))
#   subject  E EP OP  T   TE TP
# 1       3 13 NA 14 NA 10.5 NA
# 2       2 NA 14 13 NA   NA NA
# 5       1 NA NA NA  3 17.0 12