library(tidyverse)
df <- data.frame(names = c("John", "Peter", "Mary", "Allan", "Anne"),
year = 2001:2005,
first_class = letters[1:5],
second_class = c("NA", "c", "d", "NA", "e"),
third_class = c(letters[1:3], "NA", "NA"),
first_day = c("one", "two", "NA", "two", "one"),
second_day = c("two", "NA", "one", "one", "two"),
third_day = c("NA", "one", "two", "NA", "two"))
I want to pivot two sets of columns, “first_class:third_class” and “first_day:third_day”. I have tried the following syntax:
df |>
pivot_longer(cols = first_class:third_class,
values_to = "my_class",
names_to = NULL) |>
pivot_longer(cols = first_day:third_day,
values_to = "my_day",
names_to = NULL)
However, the output repeats the same information three times:
# A tibble: 45 × 4
names year my_class my_day
<chr> <int> <chr> <chr>
1 John 2001 a one
2 John 2001 a two
3 John 2001 a NA
4 John 2001 NA one
5 John 2001 NA two
6 John 2001 NA NA
7 John 2001 a one
8 John 2001 a two
9 John 2001 a NA
10 Peter 2002 b two
The expected result should be as follows:
names year my_class my_day
1 John 2001 a one
2 John 2001 NA two
3 John 2001 a NA
4 Peter 2002 b two
5 Peter 2002 c NA
6 Peter 2002 b one
You could do something like this:
df %>%
pivot_longer(3:8,
names_to = c("Schedule", ".value"),
names_sep = "_")
By using .value you are generating dynamically new columns with the second part of your column names (after the underscore) and you pivot longer the first part assigning it to the variable Schedule.
names year Schedule class day
<chr> <int> <chr> <chr> <chr>
John 2001 first a one
John 2001 second NA two
John 2001 third a NA
Peter 2002 first b two
Peter 2002 second c NA
Peter 2002 third b one
Mary 2003 first c NA