CODEWITHSUNDEEP
rtidyr
EStark
EStark

Reputation: 193

Pivot longer using Tidyr - Multiple variables stored in column names

I am trying to reshape the following input:

  family_number father_name father_code mother_name mother_code children
1             1        John         M11        Jane         F21        2
2             2      Thomas         M12        Mary         F22        3
3             3        Jack         M13    Jennifer         F23        0

into longer format using tidyr::pivot_longer:

  family_number     name code children
1             1     John  M11        2
2             1     Jane  F21        2
3             2   Thomas  M12        3
4             2     Mary  F22        3
5             3     Jack  M13        0
6             3 Jennifer  F23        0

I don't quite understand how it works if you have multiple columns you specifically want to pivot. Thank you !

Input table code:

foo <- data.frame(family_number = c(1, 2, 3),
                  father_name = c("John", "Thomas", "Jack"),
                  father_code = c("M11", "M12", "M13"),
                  mother_name = c("Jane", "Mary", "Jennifer"),
                  mother_code = c("F21", "F22", "F23"),
                  children = c(2, 3, 0))

Upvotes: 2

Views: 701

Answers (2)

akrun
akrun

Reputation: 887831

We could use pivot_longer by specifying the names_to to match the pattern in the column name

library(dplyr)
library(tidyr)

foo %>% 
   pivot_longer(cols = -c(family_number, children), 
        names_to = c("grp", ".value"), names_sep = "_") %>%
        select(-grp)
# A tibble: 6 x 4
#  family_number children name     code 
#          <dbl>    <dbl> <chr>    <chr>
#1             1        2 John     M11  
#2             1        2 Jane     F21  
#3             2        3 Thomas   M12  
#4             2        3 Mary     F22  
#5             3        0 Jack     M13  
#6             3        0 Jennifer F23

Upvotes: 4

luismf
luismf

Reputation: 371

I don't think you can accomplish that in a single pivot operation. You can only pivot several columns in a single column, afaik. My approach would be uniting columns, pivoting and then separating them.

foo %>%
  tidyr::unite("father", father_name, father_code) %>% 
  tidyr::unite("mother", mother_name, mother_code) %>% 
  tidyr::pivot_longer(c(father, mother), values_to = "parent") %>% 
  select(-name) %>% 
  tidyr::separate(parent, into=c("name", "code"))

# A tibble: 6 x 4
  family_number children name     code 
          <dbl>    <dbl> <chr>    <chr>
1             1        2 John     M11  
2             1        2 Jane     F21  
3             2        3 Thomas   M12  
4             2        3 Mary     F22  
5             3        0 Jack     M13  
6             3        0 Jennifer F23

Upvotes: 1

Related Questions