CODEWITHSUNDEEP
rreshapetidyr
milan_filip
milan_filip

Reputation: 1

pivot_longer for multiple observations in a row

I have a data set with a multiple observations within 1 row that I am trying to reshape into a long format. Here's what the data will look like

    df_have <- data.frame(group = c(1,2), p1_age = c(18,30), p1_sex = c("male", "male"),
                   p2_age = c(22,51), p2_sex = c("female", "male"))

  group p1_age p1_sex p2_age p2_sex
1     1     18   male     22 female
2     2     30   male     51   male

There will be more persons and variables that I want to reshape. I want to reshape it so it looks something like this:

    df_want <- data.frame(person = c(1,2,1,2), group = c(1,1,2,2), age = c(18,22,30,51),
                   sex = c("male", "female", "male", "male"))
  person group age    sex
1      1     1  18   male
2      2     1  22 female
3      1     2  30   male
4      2     2  51   male

I've tried using ".value" in a pivot_longer step to try and generate the what I want, but I can't figure it out.

Upvotes: 0

Views: 637

Answers (2)

akrun
akrun

Reputation: 887088

We can use pivot_longer and specify the names_pattern to capture the digits (\\d+) and the word (\\w+) after the _

library(dplyr)
library(tidyr)
df_have %>%
     pivot_longer(cols = -group, names_to = c("person", ".value"),
       names_pattern = ".(\\d+)_(\\w+)")%>% 
     mutate(person = as.integer(person))

-output

# A tibble: 4 x 4
  group person   age sex   
  <dbl> <int>  <dbl> <chr> 
1     1 1         18 male  
2     1 2         22 female
3     2 1         30 male  
4     2 2         51 male

Upvotes: 1

Onyambu
Onyambu

Reputation: 79208

library(tidyverse)
pivot_longer(df_have, -group, names_to = c("person", ".value"), names_sep = "_")

# A tibble: 4 x 4
  group person    age sex   
  <dbl>  <chr> <dbl> <chr> 
1     1  p1       18 male  
2     1  p2       22 female
3     2  p1       30 male  
4     2  p2       51 male

Upvotes: 0

Related Questions