Data wrangling to add columns that sum up counts of mapped values R

user_10156381 0
Viewed 68

I have a data frame of counts per person that looks like this:

Person_ID Apple Pear Chicken Steak Spinach
   1        1    0      0      5      1
   2        1    1      1      0      0
   3        0    0      0      3      2

I have another dataframe that maps which food belongs to which food group and looks like this:

Food     Group
Apple    Fruit
Pear     Fruit
Chicken  Meat
Steak    Meat
Spinach  Vegetable

I want to use the 2nd dataframe to add new columns on the 1st, basically creating new columns representing the food groups and collecting the counts based on the sum of their constituent columns, so that the final output looks like this:

Person_ID Apple Pear Chicken Steak Spinach Fruit Meat Vegetable
   1        1    0      0      5      1      1    5       1
   2        1    1      1      0      0      2    1       0
   3        0    0      0      3      2      0    3       2

I am having trouble doing this in a clean way, and it seems quite complicated. I am wondering if there is a simple solution, and would appreciate advice on any solution at all

dataframe r
3 Answers

We just need assignment i.e. select the subset of columns of 'df1' with 'Food' column of 'df2', split those with 'Group' column into a list, get the rowSums and assign those to create new columns in 'df1' based on the 'Group' column values

m1 <- sapply(split.default(df1[df2$Food], df2$Group), rowSums)
df1[colnames(m1)] <- m1

-ouptut

df1
  Person_ID Apple Pear Chicken Steak Spinach Fruit Meat Vegetable
1         1     1    0       0     5       1     1    5         1
2         2     1    1       1     0       0     2    1         0
3         3     0    0       0     3       2     0    3         2

data

df1 <- structure(list(Person_ID = 1:3, Apple = c(1L, 1L, 0L), Pear = c(0L, 
1L, 0L), Chicken = c(0L, 1L, 0L), Steak = c(5L, 0L, 3L), Spinach = c(1L, 
0L, 2L)), class = "data.frame", row.names = c(NA, -3L))

df2 <- structure(list(Food = c("Apple", "Pear", "Chicken", "Steak", 
"Spinach"), Group = c("Fruit", "Fruit", "Meat", "Meat", "Vegetable"
)), class = "data.frame", row.names = c(NA, -5L))
user_37322710

You can also use the following tidyverse solution:

library(dplyr)
library(tidyr)

df1 %>%
  left_join(df1 %>%
              pivot_longer(!Person_ID, names_to = "Food") %>%
              left_join(df2, by = "Food") %>%
              group_by(Person_ID, Group) %>%
              summarise(sum = sum(value), .groups = "drop") %>%
              pivot_wider(names_from = Group, values_from = sum), by = "Person_ID")

  Person_ID Apple Pear Chicken Steak Spinach Fruit Meat Vegetable
1         1     1    0       0     5       1     1    5         1
2         2     1    1       1     0       0     2    1         0
3         3     0    0       0     3       2     0    3         2
user_143145200

You may also use purrr::reduce here

library(tidyverse)

reduce(unique(df2$Group), 
       .init = df1,
       ~ .x %>% 
         mutate(!!.y := rowSums(df1[names(df1) %in% df2$Food[df2$Group == .y]]))
       )

#>   Person_ID Apple Pear Chicken Steak Spinach Fruit Meat Vegetable
#> 1         1     1    0       0     5       1     1    5         1
#> 2         2     1    1       1     0       0     2    1         0
#> 3         3     0    0       0     3       2     0    3         2

Or equivalently in baseR

Reduce(function(x, y) {x[[y]] <- rowSums(x[names(df1) %in% df2$Food[df2$Group == y]])
  x}, init = df1, unique(df2$Group))

  Person_ID Apple Pear Chicken Steak Spinach Fruit Meat Vegetable
1         1     1    0       0     5       1     1    5         1
2         2     1    1       1     0       0     2    1         0
3         3     0    0       0     3       2     0    3         2
user_28848590
Related