How to pivot_longer on multiple columns with unequal numbers of comma-separated values

user_8039978 0
Viewed 353

I have messy-looking data where multiple columns have multiple comma-separated values:

df <- data.frame(
  Line = 1:2,
  Utterance = c("hi there", "how're ya"),
  A_aoi = c("C*B*C", "*"),
  A_aoi_dur = c("100,25,30,50,144", "200"),
  B_aoi = c("*A", "*A*A*C"),
  B_aoi_dur = c("777,876", "50,22,33,100,150,999")
)

What I'd like to do is pivot_longer so that each of the comma-separated values has their own row. I can accomplish this but it seems the way I'm accomplishing is anything but accomplished as it involves multiple intermediary steps and temporary dfs making the code long-winded and heavy:

library(dplyr)
library(tidyr)

# first temporary `df`:
df1 <- df %>%
  select(-ends_with("dur")) %>%
  pivot_longer(cols = ends_with("aoi"),
               names_to = "Speaker") %>%
  separate_rows(value, sep = "(?!^|$)") %>%
  mutate(Speaker = sub("^(.).*", "\\1", Speaker)) %>%
  rename(AOI = value)

# second temporary `df`:
df2 <- df %>%
  select(-ends_with("aoi")) %>%
  pivot_longer(cols = ends_with("dur")) %>%
  separate_rows(value, sep = ",") %>%
  rename(Dur = value)

# final `df` (aka, the **expected outcome**):
df3 <- cbind(df1, df2[,4])

df3
   Line Utterance Speaker AOI Dur
1     1  hi there       A   C 100
2     1  hi there       A   *  25
3     1  hi there       A   B  30
4     1  hi there       A   *  50
5     1  hi there       A   C 144
6     1  hi there       B   * 777
7     1  hi there       B   A 876
8     2 how're ya       A   * 200
9     2 how're ya       B   *  50
10    2 how're ya       B   A  22
11    2 how're ya       B   *  33
12    2 how're ya       B   A 100
13    2 how're ya       B   * 150
14    2 how're ya       B   C 999

How can this transfomation be achieved more succintly?

tidyr dplyr r
2 Answers

I don't know whether this is really "more succinct", but here's an approach that does everything in a single pipe chain.

  1. All the values in columns other than Line and Utterance are going to end up in value columns, so pivot them all longer
  2. Separate out the Speaker from the column name
  3. Pivot the value columns wider into the final shape (one column for AOI, one for Dur). This pattern of longer then wider with different keys is pretty common
  4. Lastly, split up the values so that we can put them on their own rows. I don't think separate_rows handles different separators for columns well, especially where we need to split each character, so we can do it manually and unnest to get the desired output. Note that this depends on the AOI and the Dur having the same number of elements, which I'm assuming is true given the input.
library(tidyverse)
df <- data.frame(
  Line = 1:2,
  Utterance = c("hi there", "how're ya"),
  A_aoi = c("C*B*C", "*"),
  A_aoi_dur = c("100,25,30,50,144", "200"),
  B_aoi = c("*A", "*A*A*C"),
  B_aoi_dur = c("777,876", "50,22,33,100,150,999")
)

df %>%
  pivot_longer(
    cols = matches("(aoi|dur)$"),
    names_to = "name",
    values_to = "value"
  ) %>%
  separate(name, into = c("Speaker", "aoi_dur"), sep = "_", extra = "merge") %>%
  pivot_wider(names_from = aoi_dur, values_from = value) %>%
  rename(AOI = aoi, Dur = aoi_dur) %>%
  mutate(
    AOI = str_split(AOI, pattern = ""),
    Dur = str_split(Dur, pattern = ",")
  ) %>%
  unnest(c(AOI, Dur))
#> # A tibble: 14 x 5
#>     Line Utterance Speaker AOI   Dur  
#>    <int> <chr>     <chr>   <chr> <chr>
#>  1     1 hi there  A       C     100  
#>  2     1 hi there  A       *     25   
#>  3     1 hi there  A       B     30   
#>  4     1 hi there  A       *     50   
#>  5     1 hi there  A       C     144  
#>  6     1 hi there  B       *     777  
#>  7     1 hi there  B       A     876  
#>  8     2 how're ya A       *     200  
#>  9     2 how're ya B       *     50   
#> 10     2 how're ya B       A     22   
#> 11     2 how're ya B       *     33   
#> 12     2 how're ya B       A     100  
#> 13     2 how're ya B       *     150  
#> 14     2 how're ya B       C     999

Created on 2021-06-24 by the reprex package (v1.0.0)

user_70284590

Here, is an option in tidyverse

  1. We rename the columns (rename_with) that ends_with '_aoi' by pasteing the '_AOI'
  2. Reshape from 'wide' to 'long' - pivot_longer
  3. Insert delimiter , in 'AOI' between each character to make common delimiter - str_replace_all
  4. Finally, use separate_rows on the , delimiter
library(dplyr)
library(tidyr)
library(stringr)
df %>% 
    rename_with(~ str_c(., "_AOI"), ends_with("_aoi")) %>% 
    pivot_longer(cols = contains("_"), 
      names_to = c("Speaker", ".value"), names_pattern = "^(.*)_([^_]+$)") %>% 
    mutate(AOI = str_replace_all(AOI, "(?<=.)(?=.)", ",")) %>% 
    separate_rows(c(AOI, dur), sep = ",", convert = TRUE)

-output

# A tibble: 14 x 5
    Line Utterance Speaker AOI     dur
   <int> <chr>     <chr>   <chr> <int>
 1     1 hi there  A_aoi   C       100
 2     1 hi there  A_aoi   *        25
 3     1 hi there  A_aoi   B        30
 4     1 hi there  A_aoi   *        50
 5     1 hi there  A_aoi   C       144
 6     1 hi there  B_aoi   *       777
 7     1 hi there  B_aoi   A       876
 8     2 how're ya A_aoi   *       200
 9     2 how're ya B_aoi   *        50
10     2 how're ya B_aoi   A        22
11     2 how're ya B_aoi   *        33
12     2 how're ya B_aoi   A       100
13     2 how're ya B_aoi   *       150
14     2 how're ya B_aoi   C       999
user_37322710
Related