Split values in row duplicating other colums values R

Question

I have this kind of dataset

Name    Year    Subject                                        State
Jack    2003    Math/ Sci/ Music                               MA/ AB/ XY
Sam     2004    Math/ PE                                       CA/ AB
Nicole  2005    Math/ Life Sci/ Geography                      NY/ DE/ FG

This is what I want as output:

Name    Year    Subject            State
Jack    2003    Math               MA
Jack    2003    Sci                AB
Jack    2003    Music              XY
Sam     2004    Math               CA
Sam     2004    PE                 AB
Nicole  2005    Math               NY
Nicole  2005    Life Sci           DE
Nicole  2005    Geography          FG

Keep in mind that the first element of 'subject' corresponds to the first of 'State' and so on. I need to keep this correspondence. I think I have to use something like 'pivot_longer' but I do not use R every day and I'm not skilled enought. Thanks in advance :) Thanks!!

score 4 · Accepted Answer · answered Jan 27 '22 at 15:46

Name <- c("Jack", "Sam", "Nicole")
Year = c(2003, 2004, 2005)
Subject = c("Math/ Sci/ Music",  "Math/ PE", "Math/ Life Sci/ Geography")
State = c("MA/ AB/ XY", "CA/ AB", "NY/ DE/ FG")

library(tidyr)
df <- data.frame(Name, Year, Subject, State)
df %>% separate_rows(Subject, State, sep = "/ ")
#> # A tibble: 8 × 4
#>   Name    Year Subject   State
#>   <chr>  <dbl> <chr>     <chr>
#> 1 Jack    2003 Math      MA   
#> 2 Jack    2003 Sci       AB   
#> 3 Jack    2003 Music     XY   
#> 4 Sam     2004 Math      CA   
#> 5 Sam     2004 PE        AB   
#> 6 Nicole  2005 Math      NY   
#> 7 Nicole  2005 Life Sci  DE   
#> 8 Nicole  2005 Geography FG

^{Created on 2022-01-27 by the reprex package (v2.0.1)}

score 0 · Answer 2 · answered Jan 27 '22 at 16:20

Alternatively, using pivot_wider after pivot_longer:

library(tidyverse)

df <- data.frame(
  stringsAsFactors = FALSE,
  Name = c("Jack", "Sam", "Nicole"),
  Year = c(2003L, 2004L, 2005L),
  Subject = c("Math/ Sci/ Music","Math/ PE",
              "Math/ Life Sci/ Geography"),
  State = c("MA/ AB/ XY", "CA/ AB", "NY/ DE/ FG")
)

df %>% 
  pivot_longer(cols = 3:4) %>% 
  pivot_wider(id_cols = c(Name, Year),  values_fn = \(x) str_split(x, "/ ")) %>% 
  unnest(everything())

#> # A tibble: 8 × 4
#>   Name    Year Subject   State
#>   <chr>  <int> <chr>     <chr>
#> 1 Jack    2003 Math      MA   
#> 2 Jack    2003 Sci       AB   
#> 3 Jack    2003 Music     XY   
#> 4 Sam     2004 Math      CA   
#> 5 Sam     2004 PE        AB   
#> 6 Nicole  2005 Math      NY   
#> 7 Nicole  2005 Life Sci  DE   
#> 8 Nicole  2005 Geography FG

Split values in row duplicating other colums values R

2 Answers2