Search code examples
rdataframedplyrtidyversestringr

Pivote_wider with sort based off another column

I am wondering if there is a pivot_wider like function that takes the information from the columns and paste them as a field in their respective row.

Sample code:

library(tidyverse)
sample <- structure(list(station = c("Station1", "Station1", "Station2", 
                                     "Station3", "Station3", "Station4"), year = c(2020, 2021, 2020, 
                                                                                   2020, 2021, 2021), amount.mm = c(50, 100, 25, 50, 75, 10)), row.names = c(NA, 
                                                                                                                                                             -6L), class = c("tbl_df", "tbl", "data.frame"))

sample$paste <- str_c("Year:",sample$year,"/mm:",sample$amount.mm, sep = "")

Pseudo code:

sample <- sample %>%
  group_by(station) %>%
  pivot_wider(from "paste" to "values", sort by = year, sep = " ") %>%
  select(-2,-3)

Desired output:

enter image description here

Solution

  • You can use str_c again, but grouped by station

    Code

    sample %>% 
  group_by(station) %>% 
  summarise(values = str_c(paste,collapse = " "))

    Output

    # A tibble: 4 x 2
  station  values                          
  <chr>    <chr>                           
1 Station1 Year:2020/mm:50 Year:2021/mm:100
2 Station2 Year:2020/mm:25                 
3 Station3 Year:2020/mm:50 Year:2021/mm:75 
4 Station4 Year:2021/mm:10