3

I have a dataset in wide format with participants' information for multiple waves of a survey, including their country, gender, age at interview, and the year and whether they participated in each wave of a survey.

Here is a sample of the information of three participants:

#Dataset
df <- data.frame(
id = c(1,2,3),
country = c("UK", "Spain", "Sweden"),
gender = c(1, 1, 2),
interview_w1 = c(1, 2, 2),
interview_w2 = c(2, 2, 2),
interview_w3 = c(1, 1, 1),
int_year_w1 = c(2007, 2008, 2007),
int_year_w2 = c(2010, 2009, 2010),
int_year_w3 = c(2012, 2012, 2013),
age_int_w1 = c(60, 40, 50),
age_int_w2 = c(63, 41, 53),
age_int_w3 = c(65, 44, 56)
)

I want to convert this dataset to long format using the pivot_longer() function in R. However, I am having difficulty achieving the desired result. Specifically, I want to pivot the columns starting with 'interview_', 'int_year_' and 'age_int_'.

Here is a table showing the desired result:

     id country gender wave  interview  year   age
  <dbl> <chr>    <dbl> <chr>     <dbl> <dbl> <dbl>
1     1 UK           1 w1            1  2007    60
2     2 Spain        1 w1            2  2008    40
3     3 Sweden       2 w1            2  2007    50
4     1 UK           1 w2            2  2010    63
5     2 Spain        1 w2            2  2009    41
6     3 Sweden       2 w2            2  2010    53
7     1 UK           1 w3            1  2012    65
8     2 Spain        1 w3            1  2012    44
9     3 Sweden       2 w3            1  2013    56

Can someone please provide guidance on how to do that?

I tried using the names_to and names_pattern arguments in pivot_longer() without success as I don't fully understand how they work.

neilfws
  • 32,751
  • 5
  • 50
  • 63
saif
  • 31
  • 1

1 Answers1

2

You can do this as follows:

> library(tidyr)
> pivot_longer(df, cols=-c(id,country,gender), 
               names_to=c(".value", "wave"), 
               names_pattern="(.*)_(w.)") %>% 
    arrange(wave)
# A tibble: 9 × 7
     id country gender wave  interview int_year age_int
  <dbl> <chr>    <dbl> <chr>     <dbl>    <dbl>   <dbl>
1     1 UK           1 w1            1     2007      60
2     2 Spain        1 w1            2     2008      40
3     3 Sweden       2 w1            2     2007      50
4     1 UK           1 w2            2     2010      63
5     2 Spain        1 w2            2     2009      41
6     3 Sweden       2 w2            2     2010      53
7     1 UK           1 w3            1     2012      65
8     2 Spain        1 w3            1     2012      44
9     3 Sweden       2 w3            1     2013      56
Edward
  • 10,360
  • 2
  • 11
  • 26