pivot_longer into several pairs of columns

user_11156039 0
Viewed 116

I'm needing to pivot_longer across multiple groups of columns, creating multiple names--values pairs.

For instance, I need to go from something like this:

df_raw <- tribble(
  ~id, ~belief_dog, ~belief_bull_frog, ~belief_fish, ~age, ~norm_bull_frog, ~norm_fish, ~norm_dog, ~gender,
  "b2x8",    1,           4,          3,         41,     4,       2,          10,         2,
  "m89w",    3,           6,          2,         19,     1,       2,           3,         1,
  "32x8",    1,           5,          2,         38,     9,       1,           8,         3
)

And turn it into something lie this:

df_final <- tribble(
  ~id,   ~belief_animal, ~belief_rating, ~norm_animal, ~norm_rating, ~age,   ~gender,
  "b2x8",    "dog",           1,          "bull_frog",      4,        41,       2,
  "b2x8",    "bull_frog",     4,          "fish",           2,        41,       2,
  "b2x8",    "fish",          3,          "dog",            10,       41,       2,
  "m89w",    "dog",           3,          "bull_frog",      1,        19,       1,
  "m89w",    "bull_frog",     6,          "fish",           2,        19,       1,
  "m89w",    "fish",          2,          "dog",            3,        19,       1,
  "32x8",    "dog",           1,          "bull_frog",      9,        38,       3,
  "32x8",    "bull_frog",     5,          "fish",           1,        38,       3,
  "32x8",    "fish",          2,          "dog",            8,        38,       3
)

In other words, anything starting with "belief_" should get pivoted in one names--values pair & anything starting with "norm_" should be pivoted into another names--values pair.

I tried looking at several other Stack Overflow pages with somewhat related content but wasn't able to translate those solutions to this situation.

Any help would be appreciated, with a strong preference for dplyr solutions.

THANKS!

dplyr tidyverse pivot r
3 Answers

Solved it with a bit more experimentation!

The key comes down to both the names_to & the names_pattern arguments.

df_raw %>% pivot_longer(
  cols = c(belief_dog:belief_fish, norm_bull_frog:norm_dog),
  names_to = c(".value", "rating"),
  names_pattern = "([a-z]+)_*(.+)"
)

I don't really understand how ".value" or the regex "([a-z]+)_*(.+)" work, but the solution works nonetheless.

user_111560390

With tidyverse, we can pivot on the two sets of columns that starts with belief and norm. We can then use regex to split into groups according to the first underscore (since some column names have multiple underscores). Essentially, we are saying to put belief or norm (the first group in the column name) into their own columns (i.e., .value), then the second part of the group (i.e., animal names) are put into one column named animal.

library(tidyverse)

df_raw %>%
  pivot_longer(cols = c(starts_with("belief"), starts_with("norm")),
               names_to = c('.value', 'animal'),
               names_pattern = '(.*?)_(.*)') %>% 
  rename(belief_rating = belief, norm_rating = norm)

Output

  id      age gender animal    belief_rating norm_rating
  <chr> <dbl>  <dbl> <chr>             <dbl>       <dbl>
1 b2x8     41      2 dog                   1          10
2 b2x8     41      2 bull_frog             4           4
3 b2x8     41      2 fish                  3           2
4 m89w     19      1 dog                   3           3
5 m89w     19      1 bull_frog             6           1
6 m89w     19      1 fish                  2           2
7 32x8     38      3 dog                   1           8
8 32x8     38      3 bull_frog             5           9
9 32x8     38      3 fish                  2           1
user_152931910

For these data:

library(dplyr)
library(tidyr)

df_raw %>% 
  pivot_longer(
    cols = -c(id, age, gender),
    names_to = "name1",
    values_to = "belief_rating"
  ) %>% 
  separate(name1, c("A", "B"), sep = '\\_' , extra = 'merge') %>%  
  group_by(id) %>% 
  mutate(helper = rep(row_number(), each=3, length.out = n())) %>% 
  pivot_wider(
    names_from = A,
    values_from = B,
    names_glue = "{A}_animal"
  ) %>% 
  mutate(norm_rating = ifelse(helper == 1, lead(belief_rating, 3), NA),
         norm_animal = ifelse(helper == 1, lead(norm_animal, 3), NA)) %>% 
  slice(1:3) %>% 
  select(id, belief_animal, belief_rating, norm_animal, norm_rating, age, gender)

  id    belief_animal belief_rating norm_animal norm_rating   age gender
  <chr> <chr>                 <dbl> <chr>             <dbl> <dbl>  <dbl>
1 32x8  dog                       1 bull_frog             9    38      3
2 32x8  bull_frog                 5 fish                  1    38      3
3 32x8  fish                      2 dog                   8    38      3
4 b2x8  dog                       1 bull_frog             4    41      2
5 b2x8  bull_frog                 4 fish                  2    41      2
6 b2x8  fish                      3 dog                  10    41      2
7 m89w  dog                       3 bull_frog             1    19      1
8 m89w  bull_frog                 6 fish                  2    19      1
9 m89w  fish                      2 dog                   3    19      1
user_133216470
Related