My initial dataset
df1 <- structure(list(id = c(1, 1, 2, 3, 3, 3),
name = c("james", "james", "peter", "anne", "anne", "anne"),
trip_id = c(10,11,10,30,11,32),
date = c("2021/01/01", "2021/06/01","2021/08/01","2021/10/01","2021/10/21","2021/12/01"),
cost = c(100,150,3000,1200,1100,5000)
),
row.names = c(NA,-6L),
class = c("tbl_df", "tbl", "data.frame"))
I require to pivot wider the date and the cost of each trip so they are both together in pairs. I think im closer but would appreciate your feedback.
My current code
df2= df1 %>% pivot_wider(names_from = trip_id,
values_from = c(date, cost))
My desired result
df2 <- structure(list(id = c(1, 2, 3),
name = c("james", "peter", "anne"),
date_10 = c("2021/01/01","2021/08/01",NA),
cost_10 = c(100,3000,NA),
date_11 = c("2021/06/01",NA,"2021/10/21"),
cost_11 = c(150,NA,1100),
date_30 = c(NA,NA,"2021/10/01"),
cost_30 = c(NA,NA,1200),
date_32 = c(NA,NA,"2021/12/01"),
cost_32 = c(NA,NA,5000)
),
row.names = c(NA,-3L),
class = c("tbl_df", "tbl", "data.frame"))
It looks like you were quite close. We take the trip_id before pivot_wider to help reorder the columns. You may or may not need the sort depending on your desired result. If you just want pairs, there's no need to sort.
library(tidyverse)
nums <- sort(unique(df1$trip_id))
nums <- as.character(nums)
df2 <-
df1 %>%
pivot_wider(names_from = trip_id,
values_from = c(date, cost)) %>%
select(id, name, ends_with(nums))
df2
#> # A tibble: 3 x 10
#> id name date_10 cost_10 date_11 cost_11 date_30 cost_30 date_32 cost_32
#> <dbl> <chr> <chr> <dbl> <chr> <dbl> <chr> <dbl> <chr> <dbl>
#> 1 1 james 2021/01/01 100 2021/0~ 150 <NA> NA <NA> NA
#> 2 2 peter 2021/08/01 3000 <NA> NA <NA> NA <NA> NA
#> 3 3 anne <NA> NA 2021/1~ 1100 2021/1~ 1200 2021/1~ 5000