Using `map` to find rowMeans by column names

user_10952717 0
Viewed 107

I have a dataset with consistently named columns and I would like to take the average of the columns by their group e.g.,

library(dplyr)
library(purrr)
library(glue)

df <- tibble(`1_x_blind` = 1:3, 
       `1_y_blind` = 7:9,
       `2_x_blind` = 4:6, 
       `2_y_blind` = 5:7)
  
df %>% 
  mutate(`1_overall_test` = rowMeans(select(., matches(glue("^1_.*_blind$")))))
#> # A tibble: 3 x 5
#>   `1_x_blind` `1_y_blind` `2_x_blind` `2_y_blind` `1_overall_test`
#>         <int>       <int>       <int>       <int>            <dbl>
#> 1           1           7           4           5                4
#> 2           2           8           5           6                5
#> 3           3           9           6           7                6

This method works fine. The next step for me would be to scale it so that I can do the entire series of columns e.g., something like

df %>% 
  mutate(overall_blind = map(1:2, ~rowMeans(select(., matches(glue("^{.x}_.*_blind$"))))))
#> Error: Problem with `mutate()` input `overall_blind`.
#> x no applicable method for 'select' applied to an object of class "c('integer', 'numeric')"
#> ℹ Input `overall_blind` is `map(1:2, ~rowMeans(select(., matches(glue("^{.x}_.*_blind$")))))`.

I think the problem here is that select is confusing the . operator. Is it possible to map over a series of column names in this way? Ideally I'd like the column names to follow the {.x}_overall pattern as in the example above.

purrr dplyr r
3 Answers

Here is one solution:

map_dfc(1:2, function(x) {
  select(df, matches(glue("^{x}_.*_blind$"))) %>%
    mutate(overall_blind = rowMeans(select(., matches(glue("^{x}_.*_blind$"))))) %>%
    
    # General but not perfect names
    # set_names(paste0(x, "_", names(.)))
    
    # Hand-tailored names
    set_names(c(names(.)[1], names(.)[2], paste0(x, "_", names(.)[3])))
  })

#> # A tibble: 3 x 6
#>   `1_x_blind` `1_y_blind` `1_overall_blind` `2_x_blind` `2_y_blind` `2_overall_blind`
#>         <int>       <int>             <dbl>       <int>       <int>             <dbl>
#> 1           1           7                 4           4           5               4.5
#> 2           2           8                 5           5           6               5.5
#> 3           3           9                 6           6           7               6.5

I added two possibilities of naming the overall_blind column for each group, one more general but not perfect names (it duplicates the 1_ or 2_ for the data columns), and another that gives the names that you want but require knowing in advance the number of columns per group.

user_77050000

Update Here's a cleaner way that doesn't require rename or bind_cols:

map_dfc(1:2, 
        function(x) df %>% 
          select(matches(glue("^{x}_.*_blind$"))) %>%
          mutate("{x}_overall_blind" := rowMeans(.))
        )

# A tibble: 3 x 6
  `1_x_blind` `1_y_blind` `1_overall_blind` `2_x_blind` `2_y_blind` `2_overall_blind`
        <int>       <int>             <dbl>       <int>       <int>             <dbl>
1           1           7                 4           4           5               4.5
2           2           8                 5           5           6               5.5
3           3           9                 6           6           7               6.5

Previous
Here's a map approach.
The challenge is mutating two new columns based on separate groups of existing columns. Easiest just to do that in its own map_dfc() and then bind that to the existing df.


df %>%
  bind_cols(
    map_dfc(1:2, ~rowMeans(df %>% select(matches(glue("^{.x}_.*_blind$"))))) %>%
      rename_with(~paste0(str_replace(., "\\...", ""), "_overall_blind"))
  )

# A tibble: 3 x 6
  `1_x_blind` `1_y_blind` `2_x_blind` `2_y_blind` `1_overall_blind` `2_overall_blind`
        <int>       <int>       <int>       <int>             <dbl>             <dbl>
1           1           7           4           5                 4               4.5
2           2           8           5           6                 5               5.5
3           3           9           6           7                 6               6.5

And here's a way to get your rowwise column-group averages using pivots, which avoids regex and mutate/map operations:

df %>%
  mutate(row = row_number()) %>%
  pivot_longer(-row) %>%
  separate(name, c("grp"), sep = "_", extra = "drop") %>%
  group_by(row, grp) %>%
  summarise(overall_blind = mean(value)) %>%
  ungroup() %>%
  pivot_wider(id_cols = row, names_from = grp, values_from = overall_blind, 
              names_glue = "{grp}_{.value}") %>%
  bind_cols(df)

# A tibble: 3 x 6
  `1_overall_blind` `2_overall_blind` `1_x_blind` `1_y_blind` `2_x_blind` `2_y_blind`
              <dbl>             <dbl>       <int>       <int>       <int>       <int>
1                 4               4.5           1           7           4           5
2                 5               5.5           2           8           5           6
3                 6               6.5           3           9           6           7
user_27999410

We can use split.default to split the data into list of datasets based on the column name pattern, then get the rowMeans and bind with the original data

library(dplyr)
library(purrr)
library(stringr)
df %>%
      split.default(readr::parse_number(names(.))) %>%
      map_dfc(rowMeans) %>% 
      set_names(str_c(names(.), "_overall_blind")) %>%
      bind_cols(df, .)
# A tibble: 3 x 6
#  `1_x_blind` `1_y_blind` `2_x_blind` `2_y_blind` `1_overall_blind` `2_overall_blind`
#        <int>       <int>       <int>       <int>             <dbl>             <dbl>
#1           1           7           4           5                 4               4.5
#2           2           8           5           6                 5               5.5
#3           3           9           6           7                 6               6.5
user_37322710
Related