Group dataframe row and column wise based on other dataframe?

Question

I have a dataframe that I would like to group in both directions, first rowise and columnwise after. The first part worked well, but I am stuck with the second one. I would appreciate any help or advice for a solution that does both steps at the same time.

This is the dataframe:

df1 <- data.frame(
  ID = c(rep(1,5),rep(2,5)),
  ID2 = rep(c("A","B","C","D","E"),2),
  A = rnorm(10,20,1),
  B = rnorm(10,50,1),
  C = rnorm(10,10,1),
  D = rnorm(10,15,1),
  E = rnorm(10,5,1)
)

This is the second dataframe, which holds the "recipe" for grouping:

df2 <- data.frame (
  Group_1 = c("B","C"),
  Group_2 = c("D","A"),
  Group_3 = ("E"), stringsAsFactors = FALSE)

Rowise grouping:

df1_grouped<-bind_cols(df1[1:2], map_df(df2, ~rowSums(df1[unique(.x)])))

Now i would like to apply the same grouping to the ID2 column and sum the values in the other columns. My idea was to mutate a another column (e.g. "group", which contains the name of the final group of ID2. After this i can use group_by() and summarise() to calculate the sum for each. However, I can't figure out an automated way to do it

bind_cols(df1_grouped,

    #add group label
    data.frame(
    group = rep(c("Group_2","Group_1","Group_1","Group_2","Group_3"),2))) %>%

    #remove temporary label column and make ID a character column
    mutate(ID2=group,
           ID=as.character(ID))%>%
    select(-group) %>%

    #summarise
    group_by(ID,ID2)%>%
    summarise_if(is.numeric, sum, na.rm = TRUE)

This is the final table I need, but I had to manually assign the groups, which is impossible for big datasets

Yuriy Saraykin Yuriy Saraykin · Accepted Answer · 2020-04-15T07:07:46

I will offer such a solution

library(tidyverse)
set.seed(1)
df1 <- data.frame(
  ID = c(rep(1,5),rep(2,5)),
  ID2 = rep(c("A","B","C","D","E"),2),
  A = rnorm(10,20,1),
  B = rnorm(10,50,1),
  C = rnorm(10,10,1),
  D = rnorm(10,15,1),
  E = rnorm(10,5,1)
)

df2 <- data.frame (
  Group_1 = c("B","C"),
  Group_2 = c("D","A"),
  Group_3 = ("E"), stringsAsFactors = FALSE) 

df2 <- df2 %>% pivot_longer(everything())

df1 %>% 
  pivot_longer(-c(ID, ID2)) %>% 
  mutate(gr_r = df2$name[match(ID2, table = df2$value)],
         gr_c = df2$name[match(name, table = df2$value)]) %>% 
  arrange(ID, gr_r, gr_c) %>% 
  pivot_wider(c(ID, gr_r), names_from = gr_c, values_from = value, values_fn = list(value = sum))

Group dataframe row and column wise based on other dataframe?

1 Answers