data wrangling - from long to wide

Question

I´ve got the following data:

A<-tibble::tribble(
  ~ID,   ~NR,   ~INDX.DATE,   ~REOP_DATE,  ~REC, ~TYPE1, ~TYPE2, ~TYPE3, ~TYPE4,
   1L, 1234L, "2012-11-04", "2013-12-31", "YES",     NA,     NA,     NA,     NA,
   1L,    NA,           NA, "2015-01-01",  "NO",     0L,     0L,     1L,     0L,
   1L,    NA,           NA, "2015-02-02", "YES",     NA,     NA,     NA,     NA,
   1L,    NA,           NA, "2016-01-01",  "NO",     1L,     0L,     0L,     0L,
   2L,    NA,           NA,           NA,    NA,     NA,     NA,     NA,     NA,
   3L,    NA,           NA,           NA,    NA,     NA,     NA,     NA,     NA
  )

Which looks like this:

# A tibble: 6 × 9
     ID    NR INDX.DATE  REOP_DATE  REC   TYPE1 TYPE2 TYPE3 TYPE4
  <int> <int> <chr>      <chr>      <chr> <int> <int> <int> <int>
1     1  1234 2012-11-04 2013-12-31 YES      NA    NA    NA    NA
2     1    NA NA         2015-01-01 NO        0     0     1     0
3     1    NA NA         2015-02-02 YES      NA    NA    NA    NA
4     1    NA NA         2016-01-01 NO        1     0     0     0
5     2    NA NA         NA         NA       NA    NA    NA    NA
6     3    NA NA         NA         NA       NA    NA    NA    NA

But I want to reshape it like this:

     ID    NR INDX.DATE  REC1_DATE  REC2_DATE  REOP1_DATE REOP1_TYPE REOP2_DATE REOP2_TYPE
  <int> <int> <chr>      <chr>      <chr>      <chr>           <int> <chr>           <int>
1     1  1234 2012-11-04 2013-12-31 2015-02-02 2015-01-01          3 2016-01-01          1
2     2    NA NA         NA         NA         NA                 NA NA                 NA
3     3    NA NA         NA         NA         NA                 NA NA                 NA

I´ve been looking into tidyr and pivot_wider but I can´t figure how to make the columns dynamically.

What's the logic behind `REOP1_TYPE` and `REOP2_TYPE`? – Maël Mar 06 '23 at 08:40 — Maël, Mar 06 '23 at 08:40
REOP1_TYPE is depending on TYPE1,TYPE2,TYPE3 and TYPE4 – hklovs Mar 06 '23 at 08:42 — hklovs, Mar 06 '23 at 08:42
REOP1_DATE reflects the lowest REOP_DATE where REC=NO – hklovs Mar 06 '23 at 08:52 — hklovs, Mar 06 '23 at 08:52

Maël · Accepted Answer · 2023-03-06T09:07:51.187

Your data is quite disorganized, so you may want to change its construction upstream, but here's a way to recover your final output. Note that it requires dplyr 1.1.0, but you can change .by parameter with group_by functions otherwise.

The first set of mutate is just to get a tidyer data frame. You can check here what I mean by tidy, but this is then easier for pivot_wider to understand. Then I create an id per row per group, then pivot the data. Finally, I remove all columns with only NAs (no useful information), and summarize the data in one line per group:

library(tidyr)
library(dplyr)
A %>% 
  mutate(EVENT = ifelse(REC == "YES", "REC", "REOP"),
         TYPE = ifelse(if_all(TYPE1:TYPE4, is.na), NA, max.col(across(TYPE1:TYPE4))),
         DATE = REOP_DATE,
         .keep = "unused") %>% 
  mutate(row_n = row_number(), .by = c(ID, EVENT)) %>% 
  pivot_wider(names_from = c("EVENT", "row_n"), values_from = c("DATE", "TYPE"),
              names_glue = "{EVENT}{row_n}_{.value}") %>%
  select(where(~ any(complete.cases(.x)))) %>% 
  summarise(across(everything(), ~ ifelse(all(is.na(.x)), NA, max(.x, na.rm = TRUE))), .by = ID)


# A tibble: 3 × 9
     ID    NR INDX.DATE  REC1_DATE  REOP1_DATE REC2_DATE  REOP2_DATE REOP1_TYPE REOP2_TYPE
  <int> <int> <chr>      <chr>      <chr>      <chr>      <chr>           <int>      <int>
1     1  1234 2012-11-04 2013-12-31 2015-01-01 2015-02-02 2016-01-01          3          1
2     2    NA NA         NA         NA         NA         NA                 NA         NA
3     3    NA NA         NA         NA         NA         NA                 NA         NA

score 2 · Answer 2 · answered Mar 06 '23 at 16:38

To solve this I needed to grab all of my knowledge. The solution is verbose and not very elegant:

library(dplyr)
library(tidyr)
library(stringr)
library(glue)


# function to coalesce rowwise
coalesce_by_column <- function(df) {
  return(coalesce(df[1], df[2]))
}


A %>% 
  mutate(REC = ifelse(REC=="YES", REOP_DATE, REC),
         REOP_DATE = ifelse(REC == "NO", REOP_DATE, NA_character_),
         REC = ifelse(REC == "NO", NA_character_, REC)) %>% 
  mutate(across(TYPE1:TYPE4, ~case_when(. == 1 ~ cur_column()), .names = 'new_{col}')) %>%
  unite(REOP_TYPE, starts_with('new'), na.rm = TRUE, sep = ' ') %>% 
  mutate(REOP_TYPE = na_if(REOP_TYPE, "")) %>% 
  pivot_longer(c(REOP_DATE, REC, REOP_TYPE)) %>% 
  arrange(name) %>% 
  group_by(name) %>% 
  mutate(x = cumsum(!is.na(value))) %>% 
  arrange(ID) %>% 
  ungroup() %>% 
  mutate(Col1 = rep(row_number(), each=2, length.out = n())) %>% 
  group_by(Col1) %>% 
  arrange(value, .by_group = TRUE) %>% 
  slice(1) %>% 
  mutate(name = ifelse(name=="REC", paste0(name,x,"_DATE"), str_replace(name, "OP", glue::glue("OP{x}")))) %>%
  ungroup() %>% 
  dplyr::select(-starts_with("TYPE"),-x, -Col1) %>% 
  pivot_wider(names_from = name, values_from = value) %>% 
  group_by(ID) %>% 
  summarise_all(coalesce_by_column)

     ID    NR INDX.DATE  REC1_DATE  REC2_DATE  REOP1_DATE REOP2_DATE REOP1_TYPE REOP2_TYPE
  <int> <int> <chr>      <chr>      <chr>      <chr>      <chr>      <chr>      <chr>     
1     1  1234 2012-11-04 2013-12-31 2015-02-02 2015-01-01 2016-01-01 TYPE3      TYPE1     
2     2    NA NA         NA         NA         NA         NA         NA         NA        
3     3    NA NA         NA         NA         NA         NA         NA         NA

data wrangling - from long to wide

2 Answers2