Transform dataframe (crosstab by counts)

user_7824826 0
Viewed 77

I have a df (coming from a csv) that has this structure

id  att1_beer att1_wine att2_beer att2_wine
1   1         1         0         0
2   0         1         0         1
3   1         1         0         1
4   0         1         0         1
5   1         1         0         0

I would like to get a table (preferably with Tidyverse) to this format:

      Beer Wine
Att1   3    5
Att2   0    3

Is this possible? I'm trying to avoid exporting to Excel to do it.

tidyverse r
3 Answers

Can reshape to 'long' with pivot_longer and then get the sum by group

library(dplyr)
df %>% 
  select(-id) %>%
  pivot_longer(cols =everything(), names_sep="_",
    names_to = c("grp", ".value")) %>% 
  group_by(grp) %>% 
  summarise(across(everything(), sum), .groups = 'drop')

Or using base R

sapply(split.default(df[-1], sub(".*_", "", names(df)[-1])), colSums)

data

df <- structure(list(id = 1:5, att1_beer = c(1L, 0L, 1L, 0L, 1L), 
att1_wine = c(1L, 
1L, 1L, 1L, 1L), att2_beer = c(0L, 0L, 0L, 0L, 0L), att2_wine = c(0L, 
1L, 1L, 1L, 0L)), class = "data.frame", row.names = c(NA, -5L
))
user_37322710

data.table solution for completeness' sake

library( data.table ); setDT(df)  #prepare
ans <- melt( df, id.vars = "id" )  #melt to long format
ans[, c("att", "drink") := tstrsplit( variable, "_" )]  #split column to variables
dcast(ans, att ~ drink, fun.aggregate = sum )  #cast to wide and sum
user_63562780

A base R option using xtabs + colSums

u <- colSums(df[-1])
xtabs(
  u ~ .,
  data.frame(
    u,
    do.call(rbind, strsplit(names(u), "_"))
  )
)

gives

      X2
X1     beer wine
  att1    3    5
  att2    0    3
user_121587570
Related