tidyverse do a pivot_wider with two different reshaping strategies (creating categorical and binary columns)

user_2725773 0
Viewed 119

Using the following data:

df <- data.frame(id = c("A", "B", "C", "A", "B", "A"),
                 value = c(1, 2, 3, 4, 5, 6))

I want to pivot_wider this data so that the reshaping creates two different sets of columns:

  • One set where I create a bunch of binary columns that take the column names from the value columns (e.g. bin_1, bin_2 and so on) and that are coded as 0/1.
  • An additional set where I create as many necessary columns to store the values in a "categorical" way. Here, id "A" has three values, so I want to create three columns cat_1, cat_2, cat_3 and for IDs B and C I want to fill them up with NAs if there's no value.

Now, I know how to create these two things separately from each other and merge them afterwards via a left_join.

However, my question is: can it be done in one pipeline, where I do two subsequent pivot_widers? I tried, but it doesn't work (obviously because my way of copying the value column and then try to use one for the binary reshape and one for the categorial reshape is wrong).

Any ideas?

Code so far that works:

df1 <- df %>%
  group_by(id) %>%
  mutate(group_id = 1:n()) %>%
  ungroup() %>%
  pivot_wider(names_from = group_id,
              names_prefix = "cat_",
              values_from = value)

df2 <- df %>%
  mutate(dummy = 1) %>%
  arrange(value) %>%
  pivot_wider(names_from = value,
              names_prefix = "bin_",
              values_from = dummy,
              values_fill = list(dummy = 0),
              values_fn = list(dummy = length))

df <- df1 %>%
  left_join(., df2, by = "id)

Expected output:

# A tibble: 3 x 10
  id    cat_1 cat_2 cat_3 bin_1 bin_2 bin_3 bin_4 bin_5 bin_6
  <chr> <dbl> <dbl> <dbl> <int> <int> <int> <int> <int> <int>
1 A         1     4     6     1     0     0     1     0     1
2 B         2     5    NA     0     1     0     0     1     0
3 C         3    NA    NA     0     0     1     0     0     0
tidyverse pivot r
4 Answers

In base you can try:

tt <- unstack(df[2:1])
x <- cbind(t(sapply(tt, "[", seq_len(max(lengths(tt))))),
           t(+sapply(names(tt), "%in%", x=df$id)))
colnames(x) <- c(paste0("cat_", seq_len(max(lengths(tt)))),
              paste0("bin_", seq_len(nrow(df))))
x
#  cat_1 cat_2 cat_3 bin_1 bin_2 bin_3 bin_4 bin_5 bin_6
#A     1     4     6     1     0     0     1     0     1
#B     2     5    NA     0     1     0     0     1     0
#C     3    NA    NA     0     0     1     0     0     0
user_104885040

With the addition of purrr, you could do:

map(.x = reduce(range(df$value), `:`),
    ~ df %>%
     group_by(id) %>%
     mutate(!!paste0("bin_", .x) := as.numeric(.x %in% value))) %>%
 reduce(full_join) %>%
 mutate(cats = paste0("cat_", row_number())) %>%
 pivot_wider(names_from = "cats",
             values_from = "value")

  id    bin_1 bin_2 bin_3 bin_4 bin_5 bin_6 cat_1 cat_2 cat_3
  <fct> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 A         1     0     0     1     0     1     1     4     6
2 B         0     1     0     0     1     0     2     5    NA
3 C         0     0     1     0     0     0     3    NA    NA
user_59645570

Slightly modifying your approach by reducing df2 code and including it all in one pipe by taking advantage of the list and . trick which allows you to work on two versions of df in the same call.

Its not much of an improvement on what you have done but it is now all in one call. I can't think of way you can do it without a merge/join.

library(tidyverse)
df %>%
  list(
    pivot_wider(., id_cols = id, 
                names_from = value,
                names_prefix = "bin_") %>% 
      mutate_if(is.numeric, ~ +(!is.na(.))), #convert to binary
    
    group_by(., id) %>%
      mutate(group_id = 1:n()) %>%
      ungroup() %>%
      pivot_wider(names_from = group_id,
                  names_prefix = "cat_",
                  values_from = value)    
  ) %>%
  .[c(2:3)] %>%
  reduce(left_join)

#   id    bin_1 bin_2 bin_3 bin_4 bin_5 bin_6 cat_1 cat_2 cat_3
#   <chr> <int> <int> <int> <int> <int> <int> <dbl> <dbl> <dbl>
# 1 A         1     0     0     1     0     1     1     4     6
# 2 B         0     1     0     0     1     0     2     5    NA
# 3 C         0     0     1     0     0     0     3    NA    NA
user_40837430

Even you can join both your syntax into one without creating any intermediate object

df %>%
  group_by(id) %>%
  mutate(group_id = row_number()) %>%
  pivot_wider(names_from = group_id,
              names_prefix = "cat_",
              values_from = value) %>% left_join(df %>% mutate(dummy = 1) %>% arrange(value) %>% pivot_wider(names_from = value, 
                                                                                                             names_prefix = "bin_", 
                                                                                                             values_from = dummy, 
                                                                                                             values_fill = list(dummy = 0), 
                                                                                                             values_fn = list(dummy = length)), by = "id")

# A tibble: 3 x 10
# Groups:   id [3]
  id    cat_1 cat_2 cat_3 bin_1 bin_2 bin_3 bin_4 bin_5 bin_6
  <chr> <dbl> <dbl> <dbl> <int> <int> <int> <int> <int> <int>
1 A         1     4     6     1     0     0     1     0     1
2 B         2     5    NA     0     1     0     0     1     0
3 C         3    NA    NA     0     0     1     0     0     0
user_28848590
Related