compare columns dynamically in data.table in R

Question

I have a data.table, which has multiple date columns. The are K date columns, whereby K changes. I have calculated the time difference between the columns:

K <- numberOfYears
dateCols = c("fromdatenext", paste0("fromdate" , 1:K))

# create formulas dynamically 
all_operations = lapply(seq_len(length(dateCols) - 1), function(i){
  as.formula(paste("~difftime(", dateCols[i + 1], ",", dateCols[i],", units = c('weeks'))"))
})

df %>%
  mutate_(.dots = setNames(all_operations, paste0("Diff", seq_len(length(dateCols) - 1))))

and get something like:

  fromdatenext  fromdate1  fromdate2  fromdate3  fromdate4           Diff1           Diff2           Diff3           Diff4
1   2018-01-01 2017-01-01 2016-01-01 2015-01-01 2014-01-01 -52.14286 weeks -52.28571 weeks -52.14286 weeks -52.14286 weeks
2   2018-10-01 2017-10-01 2016-10-01 2015-10-01 2014-10-01 -52.14286 weeks -52.14286 weeks -52.28571 weeks -53.14286 weeks
3   2018-09-08 2017-09-08 2016-09-08 2015-09-08 2014-09-08 -52.14286 weeks -52.14286 weeks -52.28571 weeks -52.14286 weeks
4   2018-09-22 2017-09-22 2016-09-22 2015-09-22 2014-09-22 -52.14286 weeks -52.14286 weeks -52.28571 weeks -52.14286 weeks
5   2018-05-01 2017-05-01 2016-05-01 2015-05-01 2014-05-01 -52.14286 weeks -52.14286 weeks -52.28571 weeks -52.14286 weeks
6   2018-01-01 2017-01-01 2016-01-01 2015-01-01 2014-01-01 -50.14286 weeks -52.28571 weeks -52.14286 weeks -52.14286 weeks

Now i need to remove all rows, which do not have equal time interval. How can this be done dynamically, such that when K it will still work?

so the desired result would be:

  fromdatenext  fromdate1  fromdate2  fromdate3  fromdate4           Diff1           Diff2           Diff3           Diff4
1   2018-01-01 2017-01-01 2016-01-01 2015-01-01 2014-01-01 -52.14286 weeks -52.28571 weeks -52.14286 weeks -52.14286 weeks
3   2018-09-08 2017-09-08 2016-09-08 2015-09-08 2014-09-08 -52.14286 weeks -52.14286 weeks -52.28571 weeks -52.14286 weeks
4   2018-09-22 2017-09-22 2016-09-22 2015-09-22 2014-09-22 -52.14286 weeks -52.14286 weeks -52.28571 weeks -52.14286 weeks
5   2018-05-01 2017-05-01 2016-05-01 2015-05-01 2014-05-01 -52.14286 weeks -52.14286 weeks -52.28571 weeks -52.14286 weeks

You tagged with [tag:data.table], but nothing you show here is from the [`data.table`](https://cran.r-project.org/web/packages/data.table/index.html) *package*. I'm editing your tags to clear that up, my apologies if I'm missing something. — r2evans, Jun 16 '20 at 16:16

score 0 · Answer 1 · answered Jun 16 '20 at 16:36

In general, I think this type of data (variable number of identically-structured/purposed columns) might do better in a "long" format, in which case I suggest your method should be:

library(dplyr)
library(tidyr) # pivot_*
dat2 <- dat %>%
  mutate(rn = row_number()) %>%
  pivot_longer(matches("fromdate[0-9]+"), names_to = "num", values_to = "fromdate") %>%
  group_by(rn) %>%
  mutate(
    fromdatenext = as.Date(fromdatenext),
    fromdate = as.Date(fromdate),
    num = gsub("\\D", "", num),
    Diff = `units<-`(diff(c(first(fromdatenext), fromdate)), "weeks")
  ) %>%
  ungroup()
dat2
# # A tibble: 24 x 5
#    fromdatenext    rn num   fromdate   Diff          
#    <date>       <int> <chr> <date>     <drtn>        
#  1 2018-01-01       1 1     2017-01-01 -52.1429 weeks
#  2 2018-01-01       1 2     2016-01-01 -52.2857 weeks
#  3 2018-01-01       1 3     2015-01-01 -52.1429 weeks
#  4 2018-01-01       1 4     2014-01-01 -52.1429 weeks
#  5 2018-10-01       2 1     2017-10-01 -52.1429 weeks
#  6 2018-10-01       2 2     2016-10-01 -52.1429 weeks
#  7 2018-10-01       2 3     2015-10-01 -52.2857 weeks
#  8 2018-10-01       2 4     2014-10-01 -52.1429 weeks
#  9 2018-09-08       3 1     2017-09-08 -52.1429 weeks
# 10 2018-09-08       3 2     2016-09-08 -52.1429 weeks
# # ... with 14 more rows

However, if you want/need them in the wider format, we can just re-pivot back to wide:

dat2 %>%
  pivot_wider(
    id_cols = rn:fromdatenext,
    names_from = num,
    values_from = c(fromdate, Diff),
    names_sep = ""
  )
# # A tibble: 6 x 10
#      rn fromdatenext fromdate1  fromdate2  fromdate3  fromdate4  Diff1          Diff2          Diff3          Diff4         
#   <int> <date>       <date>     <date>     <date>     <date>     <drtn>         <drtn>         <drtn>         <drtn>        
# 1     1 2018-01-01   2017-01-01 2016-01-01 2015-01-01 2014-01-01 -52.1429 weeks -52.2857 weeks -52.1429 weeks -52.1429 weeks
# 2     2 2018-10-01   2017-10-01 2016-10-01 2015-10-01 2014-10-01 -52.1429 weeks -52.1429 weeks -52.2857 weeks -52.1429 weeks
# 3     3 2018-09-08   2017-09-08 2016-09-08 2015-09-08 2014-09-08 -52.1429 weeks -52.1429 weeks -52.2857 weeks -52.1429 weeks
# 4     4 2018-09-22   2017-09-22 2016-09-22 2015-09-22 2014-09-22 -52.1429 weeks -52.1429 weeks -52.2857 weeks -52.1429 weeks
# 5     5 2018-05-01   2017-05-01 2016-05-01 2015-05-01 2014-05-01 -52.1429 weeks -52.1429 weeks -52.2857 weeks -52.1429 weeks
# 6     6 2018-01-01   2017-01-01 2016-01-01 2015-01-01 2014-01-01 -52.1429 weeks -52.2857 weeks -52.1429 weeks -52.1429 weeks

Data:

dat <- read.table(header = TRUE, text = "
fromdatenext  fromdate1  fromdate2  fromdate3  fromdate4
  2018-01-01 2017-01-01 2016-01-01 2015-01-01 2014-01-01
  2018-10-01 2017-10-01 2016-10-01 2015-10-01 2014-10-01
  2018-09-08 2017-09-08 2016-09-08 2015-09-08 2014-09-08
  2018-09-22 2017-09-22 2016-09-22 2015-09-22 2014-09-22
  2018-05-01 2017-05-01 2016-05-01 2015-05-01 2014-05-01
  2018-01-01 2017-01-01 2016-01-01 2015-01-01 2014-01-01")

compare columns dynamically in data.table in R

1 Answers1