How to mutate multiple columns with dynamic variable using purrr:map function?

Question

I have a data frame as below:

df <- data.frame(
  id  = c(1:5),
  a   = c(3,10,4,0,15),
  b   = c(2,1,1,0,3),
  c   = c(12,3,0,3,1),
  d   = c(9,7,8,0,0),
  e   = c(1,2,0,2,2)
  )

I need to add multiple columns of which names are given by a combination of a:c and 3:5. 3:5 is also used insum function:

df %>% mutate(
  usa_3 = sum(1+3),
  usa_4 = sum(1+4),
  usa_5 = sum(1+5),
  canada_3 = sum(1+3),
  canada_4 = sum(1+4),
  canada_5 = sum(1+5),
  nz_3 = sum(1+3),
  nz_4 = sum(1+4),
  nz_5 = sum(1+5)
  )

The result is really simple but I do not want to put similar codes repeatedly.

  id  a b  c d e usa_3 usa_4 usa_5 canada_3 canada_4 canada_5 nz_3 nz_4 nz_5
1  1  3 2 12 9 1     4     5     6        4        5        6    4    5    6
2  2 10 1  3 7 2     4     5     6        4        5        6    4    5    6
3  3  4 1  0 8 0     4     5     6        4        5        6    4    5    6
4  4  0 0  3 0 2     4     5     6        4        5        6    4    5    6
5  5 15 3  1 0 2     4     5     6        4        5        6    4    5    6

The variables are alphabetical prefix and range of integers as postfix. Postfix is also related to the sum funcion as 1+postfix. In this case, they have 3 values for each so the result have 9 additional columns.

I do not prefer to define function outside the a bunch of codes and suppose map functino in purrr may help it.

Do you know how to make it work? Especially it is difficult to give dynamic column name in pipe.

I found some similar questions but it does not match my need.

Multivariate mutate
How to use map from purrr with dplyr::mutate to create multiple new columns based on column pairs

===== ADDITIONAL INFO =====
Let me clarify some conditions of this issue. Actually sum(1+3), sum(1+4)... part is replaced by as.factor(cutree(X,k=X)) where X is reuslt of cluster analysis and Y is a variable defined as 3:5 in the example. cutree() is a function to define in which part we cut a dendrogram stored in the result of cluster analysis.

As for the column names usa_3, usa_4 ... nz_5, country name is replaced by methods of cluster analysis such as ward, McQuitty, Median method, etc. (seven methods), and integers 3, 4, 5, are the parameter to define in which part I need to cut a dendrogram as explained.

As for an X in the functionas.factor(cutree(X,k=X)), results of cluster analysis also have several data frame which is corresponded to each method. I realized that another issue how to apply the function to each data frame (result of cluster analysis stored in different dataframe).
Actual scripts that I am using currently is something like this:

cluste_number <- original_df %>% mutate(
    ## Ward
    ward_3=as.factor(cutree(clst.ward,k=3)),
    ward_4=as.factor(cutree(clst.ward,k=4)),
    ward_5=as.factor(cutree(clst.ward,k=5)),
    ward_6=as.factor(cutree(clst.ward,k=6)),
    ## Single
    sing_3=as.factor(cutree(clst.sing,k=3)),
    sing_4=as.factor(cutree(clst.sing,k=4)),
    sing_5=as.factor(cutree(clst.sing,k=5)),
    sing_6=as.factor(cutree(clst.sing,k=6)))

It is sorry not to clarify the actual issue; howerver, due to this reason above, number of countries as usa, canada, nz and number of parameters as 1:3 do not match. Also some suggestions using i + . does not meet the issue as a function as.factor(cutree(X,k=X)) is used in the actual operation.

Thank you for your support.

Answer 1

Not sure what you are up to, but maybe this helps to clarify the issue ..

library(tidyverse)

df <- data.frame(
  id  = c(1:5),
  a   = c(3,10,4,0,15),
  b   = c(2,1,1,0,3),
  c   = c(12,3,0,3,1),
  d   = c(9,7,8,0,0),
  e   = c(1,2,0,2,2)
)

ctry <- rep(c("usa", "ca", "nz"), each = 3)
nr <- rep(seq(3,5), times = 3)
df %>%
  as_tibble() %>%
  bind_cols(map_dfc(seq_along(ctry), ~1+nr[.x] %>%
                      rep(nrow(df))) %>%
              set_names(str_c(ctry, nr, sep = "_")))

# A tibble: 5 x 15
     id     a     b     c     d     e usa_3 usa_4 usa_5  ca_3  ca_4  ca_5  nz_3  nz_4  nz_5
  <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1     1     3     2    12     9     1     4     5     6     4     5     6     4     5     6
2     2    10     1     3     7     2     4     5     6     4     5     6     4     5     6
3     3     4     1     0     8     0     4     5     6     4     5     6     4     5     6
4     4     0     0     3     0     2     4     5     6     4     5     6     4     5     6
5     5    15     3     1     0     2     4     5     6     4     5     6     4     5     6

Answer 2

I'm not sure if I understand the spirit of the problem, but here is one way to generate a data frame with the column names and values you want.

You can change ~ function(i) i + . to be whatever function of i (the column being mutated) you want, and change either of the ns in setNames(n, n) to incorporate a different value into the function you're creating (first n) or change the names of the resulting columns (second n).

countries <- c('usa', 'canada', 'nz')
n <- 3:5

as.data.frame(matrix(1, nrow(df), length(n))) %>% 
  rename_all(~countries) %>%
  mutate_all(map(setNames(n, n), ~ function(i) i + .)) %>% 
  select(-countries) %>% 
  bind_cols(df)

#   usa_3 canada_3 nz_3 usa_4 canada_4 nz_4 usa_5 canada_5 nz_5 id  a b  c d e
# 1     4        4    4     5        5    5     6        6    6  1  3 2 12 9 1
# 2     4        4    4     5        5    5     6        6    6  2 10 1  3 7 2
# 3     4        4    4     5        5    5     6        6    6  3  4 1  0 8 0
# 4     4        4    4     5        5    5     6        6    6  4  0 0  3 0 2
# 5     4        4    4     5        5    5     6        6    6  5 15 3  1 0 2

Answer 3

Kinda of a dirty solution, but it does what you want. It combines two map_dfc functions.

library(dplyr)
library(purrr)

df <- tibble(id  = c(1:5),
             a   = c(3,10,4,0,15),
             b   = c(2,1,1,0,3),
             c   = c(12,3,0,3,1),
             d   = c(9,7,8,0,0),
             e   = c(1,2,0,2,2))

create_postfix_cols <- function(df, country, n) {
  # df = a dataframe
  # country = suffix value (e.g. "canada")
  # n = vector of postfix values (e.g. 3:5)

  map2_dfc(.x = rep(country, length(n)),
           .y = n,
           ~ tibble(col = rep(1 + .y, nrow(df))) %>%
             set_names(paste(.x, .y, sep = "_")))
}

countries <- c("usa", "canada", "nz")
n <- 3:5

df %>%
  bind_cols(map_dfc(.x = countries, ~create_postfix_cols(df, .x, n)))


# A tibble: 5 x 15
     id     a     b     c     d     e usa_3 usa_4 usa_5 canada_3 canada_4 canada_5
  <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>    <dbl>    <dbl>    <dbl>
1     1     3     2    12     9     1     4     5     6        4        5        6
2     2    10     1     3     7     2     4     5     6        4        5        6
3     3     4     1     0     8     0     4     5     6        4        5        6
4     4     0     0     3     0     2     4     5     6        4        5        6
5     5    15     3     1     0     2     4     5     6        4        5        6
# ... with 3 more variables: nz_3 <dbl>, nz_4 <dbl>, nz_5 <dbl>

Answer 4

Here is a base R solution. You can rearrange columns if you would like, but this should get your started:

# Create column names using an index and country names
idx <- 3:5
countries <- c("usa", "canada", "nz")
new_columns <- unlist(lapply(countries, paste0, "_", idx))

# Adding new values using index & taking advantage of recycling
df[new_columns] <- sort(rep(1+idx, nrow(df)))
df
  id  a b  c d e usa_3 usa_4 usa_5 canada_3 canada_4 canada_5 nz_3 nz_4 nz_5
1  1  3 2 12 9 1     4     5     6        4        5        6    4    5    6
2  2 10 1  3 7 2     4     5     6        4        5        6    4    5    6
3  3  4 1  0 8 0     4     5     6        4        5        6    4    5    6
4  4  0 0  3 0 2     4     5     6        4        5        6    4    5    6
5  5 15 3  1 0 2     4     5     6        4        5        6    4    5    6

Or, if you prefer:

# All in one long line
df[unlist(lapply(countries, paste0, "_", idx))] <- sort(rep(1+idx, nrow(df)))