CODEWITHSUNDEEP
rdata.tableconditional-statementsdifftime
few fda
few fda

Reputation: 1

Conditionally Perform difftime in R

I have a data table with devices that are inspected "annually." When the device has an inspection code "AC" (inspection_disposition_cd="AC"), then I need to count the days passed since the inspection record listed in the row directly above it.

The code I am using does the math just fine, however, I am struggling with coding so that ONLY the rows that have the AC code receive this action. Can anyone provide any advice as to how to single out these rows?

This is the working code: 

DispNotClear2$First<-c(0,with(DispNotClear2,difftime(insp_dt[2:length(insp_dt)],insp_dt[1], unit="days")))  
DispNotClear2$BETWEEN<-c(0,with(DispNotClear2,diff(insp_dt[1:(length(insp_dt)-1)],unit="days")))}

Also, here are 30 rows of sample data from my data table: 

library(data.table)
SampleData <- setDT(structure(list(record_num = c(12354L, 6764L, 9959L, 94L, 16463L, 
6859L, 80678L, 87555L, 77980L, 2839L, 4785L, 6082L, 28271L, 54L, 
23000L, 2565L, 3507L, 25607L, 106L, 14621L, 33525L, 6335L, 24970L, 
4851L, 77057L, 17247L, 14488L, 2754L, 88945L, 2710L), device_num = c("2P4564", 
"1P27589", "1P9215", "1P32077", "3P2093", "1P29651", "3P13054", 
"1P14559", "3P7242", "1P30282", "1P12286", "1P13149", "3P10127", 
"1P18885", "4P5121", "1P14621", "1P6278", "1P35255", "1P28871", 
"3P2643", "3P12113", "1P31196", "4P2597", "1W5192", "2P5643", 
"3P6750", "3F1928", "1P11978", "1P33505", "1P14572"), year = c(2016, 
2011, 2016, 2010, 2011, 2013, 2014, 2015, 2013, 2012, 2015, 2016, 
2013, 2015, 2010, 2011, 2011, 2015, 2013, 2012, 2016, 2011, 2015, 
2016, 2012, 2016, 2011, 2015, 2010, 2014), inspection_type_cd = c("CATEGORY 1 (1YR)", 
"CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", 
"CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", 
"CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", 
"CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", 
"CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", 
"CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", 
"CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", "CATEGORY 1 (1YR)", 
"CATEGORY 1 (1YR)"), inspection_disposition_cd = c("AC", "AC", 
"AC", "AU", "AC", "AC", "AU", "AC", "AC", "AC", "AC", "AU", "AU", 
"AU", "AU", "AU", "RD", "AU", "AU", "AC", "AU", "AC", "AU", "AU", 
"AC", "AU", "AU", "AC", "AU", "AC"), remarks = c("2015 AFFIRMATION OF CORRECTION", 
NA, "2016 AOC", NA, "PENALTY PAID", "2012 AOC", NA, "2015 AFFIRMATION OF CORRECTION", 
"2013 AFFIRMATION OF CORRECTION", "2011 CORRECTION", "2015 AFFIRMATION OF CORRECTION", 
NA, NA, "2015 CAT1", NA, NA, "AFFIRMATION OF CORRECTION FILED 68 DAYS LATE", 
NA, NA, "2012 CORRECTION", "2016 CAT1", "2010 AFFIRMATION OF CORRECTION", 
NA, NA, "2012 CORRECTION", "2016 CAT1", NA, "2015 AFFIRMATION OF CORRECTION", 
NA, "2013 AOC"), insp_dt = structure(c(16911, 15271, 17060, 14784, 
15014, 16009, 16339, 16658, 16044, 15363, 16576, 17151, 16069, 
16444, 14714, 15184, 15237, 16631, 16059, 15415, 16979, 14995, 
16748, 17137, 15644, 16973, 15211, 16636, 14957, 16087), class = "Date"), 
    days_late = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, 68, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA)), .Names = c("record_num", "device_num", 
"year", "inspection_type_cd", "inspection_disposition_cd", "remarks", 
"insp_dt", "days_late"), class = "data.frame", row.names = c(NA, -30L)))

which looks like 

    record_num device_num year inspection_type_cd inspection_disposition_cd                                      remarks    insp_dt days_late
 1:      12354     2P4564 2016   CATEGORY 1 (1YR)                        AC               2015 AFFIRMATION OF CORRECTION 2016-04-20        NA
 2:       6764    1P27589 2011   CATEGORY 1 (1YR)                        AC                                           NA 2011-10-24        NA
 3:       9959     1P9215 2016   CATEGORY 1 (1YR)                        AC                                     2016 AOC 2016-09-16        NA
 4:         94    1P32077 2010   CATEGORY 1 (1YR)                        AU                                           NA 2010-06-24        NA
 5:      16463     3P2093 2011   CATEGORY 1 (1YR)                        AC                                 PENALTY PAID 2011-02-09        NA
 6:       6859    1P29651 2013   CATEGORY 1 (1YR)                        AC                                     2012 AOC 2013-10-31        NA
 7:      80678    3P13054 2014   CATEGORY 1 (1YR)                        AU                                           NA 2014-09-26        NA
 8:      87555    1P14559 2015   CATEGORY 1 (1YR)                        AC               2015 AFFIRMATION OF CORRECTION 2015-08-11        NA
 9:      77980     3P7242 2013   CATEGORY 1 (1YR)                        AC               2013 AFFIRMATION OF CORRECTION 2013-12-05        NA
10:       2839    1P30282 2012   CATEGORY 1 (1YR)                        AC                              2011 CORRECTION 2012-01-24        NA
11:       4785    1P12286 2015   CATEGORY 1 (1YR)                        AC               2015 AFFIRMATION OF CORRECTION 2015-05-21        NA
12:       6082    1P13149 2016   CATEGORY 1 (1YR)                        AU                                           NA 2016-12-16        NA
13:      28271    3P10127 2013   CATEGORY 1 (1YR)                        AU                                           NA 2013-12-30        NA
14:         54    1P18885 2015   CATEGORY 1 (1YR)                        AU                                    2015 CAT1 2015-01-09        NA
15:      23000     4P5121 2010   CATEGORY 1 (1YR)                        AU                                           NA 2010-04-15        NA
16:       2565    1P14621 2011   CATEGORY 1 (1YR)                        AU                                           NA 2011-07-29        NA
17:       3507     1P6278 2011   CATEGORY 1 (1YR)                        RD AFFIRMATION OF CORRECTION FILED 68 DAYS LATE 2011-09-20        68
18:      25607    1P35255 2015   CATEGORY 1 (1YR)                        AU                                           NA 2015-07-15        NA
19:        106    1P28871 2013   CATEGORY 1 (1YR)                        AU                                           NA 2013-12-20        NA
20:      14621     3P2643 2012   CATEGORY 1 (1YR)                        AC                              2012 CORRECTION 2012-03-16        NA
21:      33525    3P12113 2016   CATEGORY 1 (1YR)                        AU                                    2016 CAT1 2016-06-27        NA
22:       6335    1P31196 2011   CATEGORY 1 (1YR)                        AC               2010 AFFIRMATION OF CORRECTION 2011-01-21        NA
23:      24970     4P2597 2015   CATEGORY 1 (1YR)                        AU                                           NA 2015-11-09        NA
24:       4851     1W5192 2016   CATEGORY 1 (1YR)                        AU                                           NA 2016-12-02        NA
25:      77057     2P5643 2012   CATEGORY 1 (1YR)                        AC                              2012 CORRECTION 2012-10-31        NA
26:      17247     3P6750 2016   CATEGORY 1 (1YR)                        AU                                    2016 CAT1 2016-06-21        NA
27:      14488     3F1928 2011   CATEGORY 1 (1YR)                        AU                                           NA 2011-08-25        NA
28:       2754    1P11978 2015   CATEGORY 1 (1YR)                        AC               2015 AFFIRMATION OF CORRECTION 2015-07-20        NA
29:      88945    1P33505 2010   CATEGORY 1 (1YR)                        AU                                           NA 2010-12-14        NA
30:       2710    1P14572 2014   CATEGORY 1 (1YR)                        AC                                     2013 AOC 2014-01-17        NA
    record_num device_num year inspection_type_cd inspection_disposition_cd                                      remarks    insp_dt days_late

Upvotes: 0

Views: 148

Answers (2)

Erdem Akkas
Erdem Akkas

Reputation: 2070

If I understood correctly, below code takes difference between above row for the records where inspection_disposition_cd=="AC"

SampleData[,day_passed:=ifelse(inspection_disposition_cd=="AC",difftime(shift(insp_dt,1),insp_dt,units = "days"),0)]

alternatively

SampleData[inspection_disposition_cd=="AC",day_passed:=difftime(shift(insp_dt,1),insp_dt,units = "days")]

Upvotes: 0

Imran Kocabiyik
Imran Kocabiyik

Reputation: 439

An alternative solution with dplyr and lubridate:

library(lubridate)
library(dplyr)
SampleData %>%
        filter(inspection_disposition_cd=="AC") %>% 
        mutate(day_passed = today()-insp_dt)

Upvotes: 0

Related Questions