How to merge consecutive rows into one row based on condition

user_3632206 0
Viewed 807

I have a dataframe containing hospital admission episodes with patientIDs and dates.

The problem

I would like to merge any row where the HospNum_Id is the same as the previous row AND the difference in date between the two rows is >3 days.

Input

A synthetic dataset is shown here:

structure(list(HospNum_Id = structure(c(1L, 1L, 1L, 2L, 2L, 2L, 
2L, 2L, 2L, 3L, 3L, 3L), .Label = c("A791697", "V682805", "X608693"
), class = "factor"), VisitDate = structure(c(17181, 17183, 17192, 
17168, 17169, 17186, 17189, 17212, 17215, 17167, 17173, 17190
), class = "Date"), diffDate = structure(c(-2, -9, NA, -1, -17, 
-3, -23, -3, NA, -6, -17, NA), class = "difftime", units = "days")), .Names = c("HospNum_Id", 
"VisitDate", "diffDate"), row.names = c(NA, -12L), class = "data.frame")

My attempts

The steps I have taken are

1. Order the columns

Mydf<-Mydf[order(Mydf$HospNum_Id,Mydf$VisitDate),]

2. Get a date diff column added

library(rlang)
library(dplyr)

SurveilTimeByRow <-
  function(Mydf, HospNum_Id, VisitDate) {
    HospNum_Ida <- sym(HospNum_Id)
    VisitDatea <- sym(VisitDate)
    ret<-dataframe %>% arrange(!!HospNum_Ida,!!VisitDatea) %>%
      group_by(!!HospNum_Ida) %>%
      mutate(diffDate = difftime(as.Date(!!VisitDatea), lead(as.Date(
        !!VisitDatea
      ), 1), units = "days"))
    dataframe<-data.frame(ret)
    return(dataframe)
  }

Mydf<-SurveilTimeByRow(try,"HospNum_Id","VisitDate")

3. Add the row to the previous row if the dateDiff for the row is >=-3 or <=3

This is the part I am stuck on.

The output required

HospNum_Id  VisitDate       diffDate   HospNum_Id.1  VisitDate.1       diffDate.1
A791697        2017-01-15  -2 days     A791697         2017-01-17       -9 days
V682805        2017-01-02  -1 days    V682805         2017-01-03        -17 days
V682805        2017-01-20  -3 days    V682805         2017-01-23        -23 days
V682805        2017-02-15  -3 days    V682805         2017-02-18         NA days

I will get rid of the last column difftime.1 which in the end will be redundant

r
1 Answers

Here's one possible solution using the data you posted as df:

library(tidyverse)

# create an id to flag consecutive rows within each HospNum
df %>%
  group_by(HospNum_Id) %>%
  mutate(id = ceiling(row_number() / 2)) %>%
  ungroup() -> df2

# split to even and odd rows within each HospNum
df_odd = df2 %>% group_by(HospNum_Id) %>% filter(row_number() %in% seq(1, nrow(df2), 2)) %>% ungroup()
df_even = df2 %>% group_by(HospNum_Id) %>% filter(row_number() %in% seq(2, nrow(df2), 2)) %>% ungroup()  

# join on both ids and remove rows
inner_join(df_odd, df_even, by=c("id","HospNum_Id")) %>%
  filter(between(diffDate.x, -3, 3) & !is.na(diffDate.y)) %>%
  select(-id)

# # A tibble: 3 x 5
#   HospNum_Id VisitDate.x diffDate.x VisitDate.y diffDate.y
#   <fct>      <date>      <time>     <date>      <time>    
# 1 A791697    2017-01-15  -2 days    2017-01-17  " -9 days"
# 2 V682805    2017-01-02  -1 days    2017-01-03  -17 days  
# 3 V682805    2017-01-20  -3 days    2017-01-23  -23 days

You combine the above logic in one piped chain like this:

df %>%
  group_by(HospNum_Id) %>%
  mutate(id = ceiling(row_number() / 2),
         even_row = row_number() %in% seq(2, nrow(df), 2)) %>%
  ungroup() %>%
  nest(-even_row) %>%
  pull(data) %>%
  reduce(function(x,y) inner_join(x,y,by=c("id","HospNum_Id"))) %>%
  filter(between(diffDate.x, -3, 3) & !is.na(diffDate.y)) %>%
  select(-id)
user_51934850
Related