CODEWITHSUNDEEP
r
user1745691
user1745691

Reputation: 315

How to sum row values if date is between 2 dates in specified row

I have a dataset as below

    patient number    drugtype   date   date - 1 year  date + 1 year
    11                G5        1/1/15    1/1/14         1/1/16
    16                G5        5/4/13    5/4/12         5/4/14
    19                R6        2/1/12    2/1/11         2/1/13
    11                G5        7/4/14    7/4/13         7/4/15
    19                R6        4/6/11    4/6/10         4/6/12
    16                G5        3/2/12    3/2/11         3/2/13
    32                G5        2/4/14    2/4/13         2/4/15
    11                G5        1/1/10    1/1/9          1/1/11
    16                G5        1/4/15    1/3/14         1/4/16
    11                G5        31/12/15  31/12/14       31/12/16

I am trying to find the number of times a drug is prescribed for a unique patients within a year of taking their drug so to end up with a table like this:

patient number    drugtype   date   date - 1 year  date + 1 year   number of prescriptions within year
    11                G5        1/1/15    1/1/14         1/1/16     3
    16                G5        5/4/13    5/4/12         5/4/14     1
    19                R6        2/1/12    2/1/11         2/1/13     2
    11                G5        7/4/14    7/4/13         7/4/15     2
    19                R6        4/6/11    4/6/10         4/6/12     2
    16                G5        3/2/12    3/2/11         3/2/13     1
    32                G5        2/4/14    2/4/13         2/4/15     1
    11                G5        1/1/10    1/1/9          1/1/11     1
    16                G5        1/4/15    1/3/14         1/4/16     1
    11                G5        31/12/15  31/12/14       31/12/16   2

So logically I need to cycle through the rows where numberofprescriptionswithinayear <- ifelse(patient number=x & drugtype=y & date > date-1year & date+1year, 1, 0) and sum.

Is there an easier way to loop through as I will also need to add additional variables such as number of prescriptions of G5 within a year, and number of prescriptions of R6 within a year as well.

The calculation of the number of prescriptions I did initially get wrong. How it is calculated is whether how many other prescriptions are given a year around the actual date (that lies between the plus one and minus one) - sorry for any lack of clarity.

Upvotes: 0

Views: 172

Answers (2)

SymbolixAU
SymbolixAU

Reputation: 26258

I think the values in your table of expected output are incorrect.

You can achieve your result using data.table, by joining your data with itself, then doing a simple subset & calculation

library(data.table)

## data (with formatted headings & date types)
dt <- structure(list(patient_number = c(11L, 16L, 19L, 11L, 19L, 16L, 
32L, 11L, 16L, 11L), drugtype = structure(c(1L, 1L, 2L, 1L, 2L, 
1L, 1L, 1L, 1L, 1L), .Label = c("G5", "R6"), class = "factor"), 
    date = structure(c(16436, 15800, 15341, 16167, 15129, 15373, 
    16162, 14610, 16526, 16800), class = "Date"), previous_year = structure(c(16071, 
    15435, 14976, 15802, 14764, 15008, 15797, 14245, 16130, 16435
    ), class = "Date"), next_year = structure(c(16801, 16165, 
    15707, 16532, 15495, 15739, 16527, 14975, 16892, 17166), class = "Date")), .Names = c("patient_number", 
"drugtype", "date", "previous_year", "next_year"), row.names = c(NA, 
-10L), class = c("data.table", "data.frame"))

setDT(dt)

dt[ dt, on=c("patient_number", "drugtype"), allow.cartesian = T  ## join it all together
    ][ 
      previous_year <= i.date & i.date <= next_year              ## filter results
      ][
        , .(n_count = .N), by=.(patient_number, drugtype, date)  ## do the calculation
        ]

#     patient_number drugtype       date n_count
#  1:             11       G5 2015-01-01       3
#  2:             11       G5 2014-04-07       2
#  3:             11       G5 2015-12-31       2
#  4:             16       G5 2013-04-05       1
#  5:             19       R6 2012-01-02       2
#  6:             19       R6 2011-06-04       2
#  7:             16       G5 2012-02-03       1
#  8:             32       G5 2014-04-02       1
#  9:             11       G5 2010-01-01       1
# 10:             16       G5 2015-04-01       1

An alternative method but giving the same result

dt[ dt, 
    {
      idx = i.previous_year <= date &  date <= i.next_year
      .(date = date[idx],
        previous_year = previous_year[idx],
        next_year = next_year[idx])
    },
    on=c("patient_number", "drugtype"),
    by=.EACHI
    ][, .(n_count = .N), by=.(patient_number, drugtype, date)]

Upvotes: 1

M. Kooi
M. Kooi

Reputation: 255

I am not be able to comment because i have'nt received enough reputation. With the package lubridate --> int_overlaps() you can determine if a datetime is in an interval of two other datetimes. this might help you.

Upvotes: 1

Related Questions