tidyr unnest, prefix column names with nested name during unnesting

Question

When running unnest on a data.frame is there a way to add the group name of nested item to the individual columns it contains (either as a suffix or prefix). Or does renaming have to be done manually via rename?

This is particularly relevant with 'unnesting' multiple groups that contain columns with the same names.

In the example below the base aggregate command does this well (eg. Petal.Length.mn), but I couldn't find an option to get unnest to do the same thing?

I'm using nest with purrr::map as I want the flexibility to mix functions, eg. calculate means and sd on a couple of variables and also run a t test to look at differences between them.

library(dplyr, warn.conflicts = FALSE)

msd_c <- function(x) c(mn = mean(x), sd = sd(x))
msd_df <- function(x) bind_rows(c(mn = mean(x), sd = sd(x)))

aggregate(cbind(Petal.Length, Petal.Width) ~ Species, 
          data = iris, FUN = msd_c)
#>      Species Petal.Length.mn Petal.Length.sd Petal.Width.mn Petal.Width.sd
#> 1     setosa       1.4620000       0.1736640      0.2460000      0.1053856
#> 2 versicolor       4.2600000       0.4699110      1.3260000      0.1977527
#> 3  virginica       5.5520000       0.5518947      2.0260000      0.2746501

iris %>% 
  select(Petal.Length:Species) %>% 
  group_by(Species) %>% 
  tidyr::nest() %>% 
  mutate(
    Petal.Length = purrr::map(data, ~ msd_df(.$Petal.Length)),
    Petal.Width = purrr::map(data, ~ msd_df(.$Petal.Width)),
    Correlation = purrr::map(data, ~ broom::tidy(cor.test(.$Petal.Length, .$Petal.Width))),
  ) %>% 
  select(-data) %>% 
  tidyr::unnest(c(Petal.Length, Petal.Width, Correlation), names_repair = tidyr::tidyr_legacy)
#> # A tibble: 3 x 13
#> # Groups:   Species [3]
#>   Species    mn    sd   mn1   sd1 estimate statistic  p.value parameter conf.low
#>   <fct>   <dbl> <dbl> <dbl> <dbl>    <dbl>     <dbl>    <dbl>     <int>    <dbl>
#> 1 setosa   1.46 0.174 0.246 0.105    0.332      2.44 1.86e- 2        48   0.0587
#> 2 versic~  4.26 0.470 1.33  0.198    0.787      8.83 1.27e-11        48   0.651 
#> 3 virgin~  5.55 0.552 2.03  0.275    0.322      2.36 2.25e- 2        48   0.0481
#> # ... with 3 more variables: conf.high <dbl>, method <chr>, alternative <chr>

^{Created on 2020-05-20 by the reprex package (v0.3.0)}

Answer 1

The answer to this was somewhat obvious, use the names_sep option rather than the names_repair option. As quoted from the nest help menu under names_sep:

If a string, the inner and outer names will be used together. In nest(), the names of the new outer columns will be formed by pasting together the outer and the inner column names, separated by names_sep. In unnest(), the new inner names will have the outer names (+ names_sep) automatically stripped. This makes names_sep roughly symmetric between nesting and unnesting.

library(dplyr, warn.conflicts = FALSE)

msd_c <- function(x) c(mn = mean(x), sd = sd(x))
msd_df <- function(x) bind_rows(c(mn = mean(x), sd = sd(x)))

iris %>% 
  select(Petal.Length:Species) %>% 
  group_by(Species) %>% 
  tidyr::nest() %>% 
  mutate(
    Petal.Length = purrr::map(data, ~ msd_df(.$Petal.Length)),
    Petal.Width = purrr::map(data, ~ msd_df(.$Petal.Width)),
    Correlation = purrr::map(data, ~ broom::tidy(cor.test(.$Petal.Length, .$Petal.Width))),
  ) %>% 
  select(-data) %>% 
  tidyr::unnest(c(Petal.Length, Petal.Width, Correlation), names_sep = ".")
#> # A tibble: 3 x 13
#> # Groups:   Species [3]
#>   Species Petal.Length.mn Petal.Length.sd Petal.Width.mn Petal.Width.sd
#>   <fct>             <dbl>           <dbl>          <dbl>          <dbl>
#> 1 setosa             1.46           0.174          0.246          0.105
#> 2 versic~            4.26           0.470          1.33           0.198
#> 3 virgin~            5.55           0.552          2.03           0.275
#> # ... with 8 more variables: Correlation.estimate <dbl>,
#> #   Correlation.statistic <dbl>, Correlation.p.value <dbl>,
#> #   Correlation.parameter <int>, Correlation.conf.low <dbl>,
#> #   Correlation.conf.high <dbl>, Correlation.method <chr>,
#> #   Correlation.alternative <chr>

^{Created on 2020-06-10 by the reprex package (v0.3.0)}

Answer 2

To apply multiple functions to multiple columns I would use summarise_at/mutate_at instead of nesting and unnesting data.

For example, in this case we can do :

library(dplyr)
iris %>% 
  group_by(Species) %>% 
  summarise_at(vars(Petal.Length:Petal.Width), list(mn = mean, sd = sd))


#  Species    Petal.Length_mn Petal.Width_mn Petal.Length_sd Petal.Width_sd
#  <fct>                <dbl>          <dbl>           <dbl>          <dbl>
#1 setosa                1.46          0.246           0.174          0.105
#2 versicolor            4.26          1.33            0.470          0.198
#3 virginica             5.55          2.03            0.552          0.275

This automatically adds a prefix to column names which we are applying the function to. Also, this is equivalent dplyr version of aggregate function you tried.

Also note that summarise_at will soon be replaced with across in upcoming version of dplyr.

Answer 3

You can use setNames like below. It is a little bit wordy, but it seems like you plan to specify each function for each column, this may be of interest.

iris %>% 
  select(Petal.Length:Species) %>% 
  group_by(Species) %>% 
  tidyr::nest() %>% 
  mutate(
    Petal.Length = purrr::map(data, ~ msd_df(.x$Petal.Length) %>%
                                setNames(paste0("Petal.Length.", names(.)))),
    Petal.Width = purrr::map(data, ~ msd_df(.$Petal.Width) %>%
                                setNames(paste0("Petal.Width.", names(.)))),
    Ratio = purrr::map(data, ~ msd_df(.$Petal.Length/.$Petal.Width) %>%
                               setNames(paste0("Ratio.", names(.))))
  ) %>% 
  select(-data) %>% 
  tidyr::unnest(c(Petal.Length, Petal.Width, Ratio))
# A tibble: 3 x 7
# Groups:   Species [3]
  Species    Petal.Length.mn Petal.Length.sd Petal.Width.mn Petal.Width.sd Ratio.mn Ratio.sd
  <fct>                <dbl>           <dbl>          <dbl>          <dbl>    <dbl>    <dbl>
1 setosa                1.46           0.174          0.246          0.105     6.91    2.85 
2 versicolor            4.26           0.470          1.33           0.198     3.24    0.312
3 virginica             5.55           0.552          2.03           0.275     2.78    0.407

Or modify your function to allow it being able to modify the column name like this.

msd_df_name <- function(x, name){
  bind_rows(c(mn = mean(x), sd = sd(x))) %>%
    setNames(paste0(name, ".", names(.)))
}

iris %>% 
  select(Petal.Length:Species) %>% 
  group_by(Species) %>% 
  tidyr::nest() %>% 
  mutate(
    Petal.Length = purrr::map(data, ~ msd_df_name(.x$Petal.Length, "Petal.Length")),
    Petal.Width = purrr::map(data, ~ msd_df_name(.$Petal.Width, "Petal.Width")),
    Ratio = purrr::map(data, ~ msd_df_name(.$Petal.Length/.$Petal.Width, "Ratio"))
  ) %>% 
  select(-data) %>% 
  tidyr::unnest(c(Petal.Length, Petal.Width, Ratio))