Is there a way using dplyr to create a new column based on dividing by group_by of another column?

Question

I am trying to create a new column by dividing a column A of integers (data1/2/3 below) by the mode of column A when grouped by another column B of integers (group1/2 below)

group1=rep(1:5,each=2)
group2=rep(6:10, each=2)
data1=c(1,1,1,1,1,4,5,6,3,8)
data2=c(5,4,5,7,8,5,2,1,1,5)
data3=c(6,6,8,9,5,4,3,3,1,1)
DF=data.frame(group1,group2,data1,data2,data3)

   group1 group2 data1 data2 data3
1       1      6     1     5     6
2       1      6     1     4     6
3       2      7     1     5     8
4       2      7     1     7     9
5       3      8     1     8     5
6       3      8     4     5     4
7       4      9     5     2     3
8       4      9     6     1     3
9       5     10     3     1     1
10      5     10     8     5     1

I have been successful in doing this one column at a time (see code below), but I would like to be able to generalize it:

DF %>%
  group_by(group2) %>%
  mutate(group2_mode = as.integer(head(names(sort(table(data2))),1))) %>%
  mutate(group2_data2 = data2/group2_mode) %>%
  #select(-c(group1_mode)) %>%
           ungroup()

# A tibble: 10 x 7
   group1 group2 data1 data2 data3 group2_mode group2_data2
    <int>  <int> <dbl> <dbl> <dbl>       <int>        <dbl>
 1      1      6     1     5     6           4         1.25
 2      1      6     1     4     6           4         1   
 3      2      7     1     5     8           5         1   
 4      2      7     1     7     9           5         1.4 
 5      3      8     1     8     5           5         1.6 
 6      3      8     4     5     4           5         1   
 7      4      9     5     2     3           1         2   
 8      4      9     6     1     3           1         1   
 9      5     10     3     1     1           1         1   
10      5     10     8     5     1           1         5

This works but is clunky when written out for each data/group combination.

I have tried iterating through for loops as follows:

for (i in colnames(DF[,3:5])){
  for (k in colnames(DF[,1:2])){
    DF %>%
      group_by(k) %>%
      mutate(paste(c(k,"_",i), collapse = '') <- i/as.integer(head(names(sort(table(i))),1)))
  }
}

And receive the following error:

Error: Column `k` is unknown

I expect the output to be similar to the first code chunk above but for each data/group combination. I have also tried labeling all of the mutated columns in the for loop the same thing, but that also results in the same error. I suspect the issue lies in the group_by statement, but I can't figure out how.

Thank you for your time

What's the output you're trying to get? I think you might need to do some tidyeval in order to create new columns with dynamic names inside your `mutate` call — camille, Aug 28 '19 at 23:44
Thank you for your response. I modified the question to reflect the output from the 'clunky' method. Ideally I would like there to be 6 columns added to the original dataframe with all combinations of data/group. — SPJ, Aug 28 '19 at 23:52

Jon Spring · Answer 1 · 2019-08-29T03:36:50.340

Borrowing from here, we can define a helper mode function:

mode <- function(codes){
  which.max(tabulate(codes))
}

Then:

DF %>%
  group_by(group2) %>%
  mutate_at(vars(matches("data")), ~. / mode(.))

[This should work, in theory, but this mode function seems to work differently than yours, and I don't see how to resolve yet.]

Edit: To do this with a few multiple groups, you could create new columns like so:

  DF %>%
    group_by(group1) %>%
    mutate_at(vars(matches("data")), 
              .funs = list(gp1 = ~. / mode(.))) %>%
    group_by(group2) %>%
    mutate_at(vars(matches("data")), 
              .funs = list(gp2 = ~. / mode(.)))

# A tibble: 10 x 14
# Groups:   group2 [5]
   group1 group2 data1 data2 data3 data1_gp1 data2_gp1 data3_gp1 data1_gp2 data2_gp2 data3_gp2 data1_gp1_gp2 data2_gp1_gp2 data3_gp1_gp2
    <int>  <int> <dbl> <dbl> <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>         <dbl>         <dbl>         <dbl>
 1      1      6     1     5     6      1         1.25      1         1         1.25      1             1             1.25          1   
 2      1      6     1     4     6      1         1         1         1         1         1             1             1             1   
 3      2      7     1     5     8      1         1         1         1         1         1             1             1             1   
 4      2      7     1     7     9      1         1.4       1.12      1         1.4       1.12          1             1.4           1.12
 5      3      8     1     8     5      1         1.6       1.25      1         1.6       1.25          1             1.6           1.25
 6      3      8     4     5     4      4         1         1         4         1         1             4             1             1   
 7      4      9     5     2     3      1         2         1         1         2         1             1             2             1   
 8      4      9     6     1     3      1.2       1         1         1.2       1         1             1.2           1             1   
 9      5     10     3     1     1      1         1         1         1         1         1             1             1             1   
10      5     10     8     5     1      2.67      5         1         2.67      5         1             2.67          5             1

If you have many groups, then we might want to create a function for this. This one mostly works, except for the naming step -- I want my group selection to also provide the name for the new column labels. := didn't seem to work for me here, which seems otherwise to be the way to name new columns in tidyeval. Can someone help me here?

add_grouped_medians <- function(df, group) {
  suffix = !!group  # This part seems to be missing the right
                    #  syntax. I want to make the group input available to the
                    #  .funs list below....
  df %>%
    group_by(!! group) %>%
    mutate_at(vars(matches("data")),
              .funs = list( suffix = ~. / mode(.)))
}

Note how the output uses "suffix" literally instead of using the group name in its place:

> DF %>% add_grouped_medians(group1, "gp1")
# A tibble: 10 x 9
# Groups:   <int> [5]
   group1 group2 data1 data2 data3 `<int>` data1_suffix data2_suffix data3_suffix
    <int>  <int> <dbl> <dbl> <dbl>   <int>        <dbl>        <dbl>        <dbl>
 1      1      6     1     5     6       1         1            1.25         1   
 2      1      6     1     4     6       1         1            1            1   
 3      2      7     1     5     8       2         1            1            1   
 4      2      7     1     7     9       2         1            1.4          1.12
 5      3      8     1     8     5       3         1            1.6          1.25
 6      3      8     4     5     4       3         4            1            1   
 7      4      9     5     2     3       4         1            2            1   
 8      4      9     6     1     3       4         1.2          1            1   
 9      5     10     3     1     1       5         1            1            1   
10      5     10     8     5     1       5         2.67         5            1

This gets me part of the way towards my goal. With this I can mutate the existing columns to be the mode ratio I am looking for, but I would like to be able to do this iteratively through each group all in one function. — SPJ, Aug 29 '19 at 00:13
Added a manual way to get more groupings, which might be fine for a small number of group columns. I haven't quite cracked how to make a more generalized version work with `tidyeval`... — Jon Spring, Aug 29 '19 at 03:37

Cole · Accepted Answer · 2019-08-30T02:09:17.713

A base solution might be just as useful - I used the mode function suggested by @Jon Spring.

mode <- function(codes){
  which.max(tabulate(codes))
}

groups <- c('group1', 'group2')
datas <- c('data1', 'data2', 'data3')

for (grp in groups) {
  for (col in datas) {
    DF[, paste(col, grp, sep = '_')] <- ave(x = DF[[col]], DF[[grp]], FUN = function(x) x / mode(x))
  }
}

   group1 group2 data1 data2 data3 data1_group1 data2_group1 data3_group1 data1_group2 data2_group2 data3_group2
1       1      6     1     5     6     1.000000         1.25        1.000     1.000000         1.25        1.000
2       1      6     1     4     6     1.000000         1.00        1.000     1.000000         1.00        1.000
3       2      7     1     5     8     1.000000         1.00        1.000     1.000000         1.00        1.000
4       2      7     1     7     9     1.000000         1.40        1.125     1.000000         1.40        1.125
5       3      8     1     8     5     1.000000         1.60        1.250     1.000000         1.60        1.250
6       3      8     4     5     4     4.000000         1.00        1.000     4.000000         1.00        1.000
7       4      9     5     2     3     1.000000         2.00        1.000     1.000000         2.00        1.000
8       4      9     6     1     3     1.200000         1.00        1.000     1.200000         1.00        1.000
9       5     10     3     1     1     1.000000         1.00        1.000     1.000000         1.00        1.000
10      5     10     8     5     1     2.666667         5.00        1.000     2.666667         5.00        1.000

This does exactly what I need. Thank you for your help! – SPJ Aug 29 '19 at 23:46 — SPJ, Aug 29 '19 at 23:46

score 1 · Answer 3 · answered Aug 29 '19 at 01:45

You could try some tidy evaluation. The definition of Mode is taken from here.

Mode <- function(x) {
    ux <- unique(x)
    ux[which.max(tabulate(match(x, ux)))]
}

We can use grep to separate group and data columns. Then use a for loop over them

library(dplyr)
library(rlang)

group_cols <- grep("^group", names(DF), value = TRUE)
data_cols <- grep("^data", names(DF), value = TRUE)

for (col  in seq_along(group_cols)) {
    data  <- sym(data_cols[col])
    DF <- DF %>%
           group_by_at(group_cols[col]) %>%
           mutate(!!paste0("group", col, "mode") := !!data/Mode(!!data))
}
DF

#   group1 group2 data1 data2 data3 group1mode group2mode
#    <int>  <int> <dbl> <dbl> <dbl>      <dbl>      <dbl>
# 1      1      6     1     5     6       1         1    
# 2      1      6     1     4     6       1         0.8  
# 3      2      7     1     5     8       1         1    
# 4      2      7     1     7     9       1         1.4  
# 5      3      8     1     8     5       1         1    
# 6      3      8     4     5     4       4         0.625
# 7      4      9     5     2     3       1         1    
# 8      4      9     6     1     3       1.2       0.5  
# 9      5     10     3     1     1       1         1    
#10      5     10     8     5     1       2.67      5

Few things to note, as already mentioned by @Jon Spring your Mode calculation is different than the standard one. If needed you can change the above Mode to your way of calculating it. Also in reality I hope you would have same number of group and data columns (here they are unequal).

Is there a way using dplyr to create a new column based on dividing by group_by of another column?

3 Answers3