CODEWITHSUNDEEP
rtidyverse
Aaron M
Aaron M

Reputation: 125

Convert only certain rows from long to wide in R

I have a dataframe that begins like this:

schools <- structure(list(
    school = c("Griffith Elementary", "Griffith Elementary", "Griffith Elementary", "Griffith Elementary", "Debs Elementary", "Debs Elementary", "Debs Elementary", "Debs Elementary", "Griffith Magnet Elementary", "Griffith Magnet Elementary", "Griffith Magnet Elementary", "Griffith Magnet Elementary"), 
    year = c(2016, 2017, 2018, 2019, 2016, 2017, 2018, 2019, 2016, 2017, 2018, 2019), 
    unique_id = c(25, 25, 25, 25, 14, 14, 14, 14, 25, 25, 25, 25), 
    enrollment = c(100, 105, 115, 110, 135, 140, 150, 145, 50, 50, 55, 55))) %>% 
    as.data.frame()

And looks like this:

enter image description here

What I would like to do is to use pivot_wider to transform only the rows with the same unique ID. So my desired dataframe looks like this:

enter image description here

Is this possible? Any help appreicated.

Upvotes: 1

Views: 47

Answers (1)

akrun
akrun

Reputation: 887158

We could use rleid from data.table on the 'unique_id' column to generate distinct ids when the adjacent values are not similar, then grouped by 'unique_id', change the 'new' column to create sequential values using match, create a sequence column with row_number() to account for duplicate elements before doing the pivot_wider to reshape into 'wide'

library(dplyr)
library(data.table)
library(tidyr)
library(stringr)
schools %>%
    mutate(new = rleid(unique_id)) %>% 
    group_by(unique_id) %>%
    mutate(new = match(new, unique(new))) %>% 
    group_by(new) %>%
    mutate(rn = row_number()) %>% 
    pivot_wider(names_from = new,
     values_from = c(school, year, unique_id, enrollment), names_sep = "") %>%
    select(-rn) %>% 
    select(order(rowid(str_remove(names(.), "\\d+"))))

-ouptut

# A tibble: 8 x 8
  school1             year1 unique_id1 enrollment1 school2                    year2 unique_id2 enrollment2
  <chr>               <dbl>      <dbl>       <dbl> <chr>                      <dbl>      <dbl>       <dbl>
1 Griffith Elementary  2016         25         100 Griffith Magnet Elementary  2016         25          50
2 Griffith Elementary  2017         25         105 Griffith Magnet Elementary  2017         25          50
3 Griffith Elementary  2018         25         115 Griffith Magnet Elementary  2018         25          55
4 Griffith Elementary  2019         25         110 Griffith Magnet Elementary  2019         25          55
5 Debs Elementary      2016         14         135 <NA>                          NA         NA          NA
6 Debs Elementary      2017         14         140 <NA>                          NA         NA          NA
7 Debs Elementary      2018         14         150 <NA>                          NA         NA          NA
8 Debs Elementary      2019         14         145 <NA>                          NA         NA          NA

Upvotes: 1

Related Questions