nimz713
Reputation: 35
Finding the minimum date for matching record in another data frame
I have a dataset of hospital encounters like so:
VISIT_KEY PAT_KEY DICT_ENC_TYPE_KEY HOSP_DISCHRG_DT MED_ORD_ID HAD_FOLLOWUP
1 82919395 8979499 83 2014-09-07 10:47:00 58826846 1
2 82919395 8979499 83 2014-09-07 10:47:00 58826847 1
3 82919395 8979499 83 2014-09-07 10:47:00 58826848 1
4 82919395 8979499 83 2014-09-07 10:47:00 58826845 1
5 77312433 8979499 83 2015-02-01 09:33:00 98525833 1
6 77312433 8979499 83 2015-02-01 09:33:00 98525834 1
7 77312433 8979499 83 2015-02-01 09:33:00 98525835 1
and a data set of follow-up encounters like so:
VISIT_KEY PAT_KEY DICT_ENC_TYPE_KEY APPT_CHECKIN_DT
1 84273751 8979499 108 2015-02-07 11:57:46
2 83999897 8979499 108 2014-09-13 16:51:22
3 83881023 8979499 108 2014-11-12 10:37:51
4 83999896 8979499 108 2014-11-20 09:23:25
5 95164335 8979499 108 2016-07-27 15:30:25
6 83922326 8979499 108 2014-11-16 09:08:47
I am trying to get the minimum value of the APP_CHECKIN_DT for an encounter into a new field for the hospital encounter dataset, FOLLOWUP_DT. This would need to be the minimum APP_CHECKIN_DT that is also greater than the HOSP_DISCHRG_DT.
For example:
- For VISIT_KEY = 82919395, the earliest APP_CHECKIN_DT in the second dataset that is greater than the HOSP_DISCHRG_DT of 2014-09-07 10:47:00 for this patient would be 2014-09-13 16:51:22
- For VISIT_KEY = 77312433, the earliest APP_CHECKIN_DT in the second dataset that is greater than the HOSP_DISCHRG_DT of 2015-02-01 09:33:00 for this patient would be 2015-02-07 11:57:46
The final hospital encounter dataset would look like:
VISIT_KEY PAT_KEY DICT_ENC_TYPE_KEY HOSP_DISCHRG_DT MED_ORD_ID HAD_FOLLOWUP FOLLOWUP_DT
1 82919395 8979499 83 2014-09-07 10:47:00 58826846 1 2014-09-13 16:51:22
2 82919395 8979499 83 2014-09-07 10:47:00 58826847 1 2014-09-13 16:51:22
3 82919395 8979499 83 2014-09-07 10:47:00 58826848 1 2014-09-13 16:51:22
4 82919395 8979499 83 2014-09-07 10:47:00 58826845 1 2014-09-13 16:51:22
5 77312433 8979499 83 2015-02-01 09:33:00 98525833 1 2015-02-07 11:57:46
6 77312433 8979499 83 2015-02-01 09:33:00 98525834 1 2015-02-07 11:57:46
7 77312433 8979499 83 2015-02-01 09:33:00 98525835 1 2015-02-07 11:57:46
I tried some FOR loops with ifelse statements like below in order to find see if the encounter had a followup, then get the APPT_CHECKIN_DT see if the hospital encounter PAT_KEY matches the outpatient encounter PAT_KEY and the APPT_CHECKIN_DT is greater than the HOSP_DISCHRG_DT, and then take the minimum APPT_CHECKIN_DT to get the followup date:
for (i in 1:nrow(children_dx)) {
children_dx$FOLLOW_UP_DATE[i] <-
ifelse(children_dx$HAD_FOLLOWUP[i] == 1,
ifelse(outpatient_visits$APPT_CHECKIN_DT[children_dx$PAT_KEY[i] == outpatient_visits$PAT_KEY] > children_dx$HOSP_DISCHRG_DT[i],
as.character(min(outpatient_visits$APPT_CHECKIN_DT[children_dx$PAT_KEY[i] == outpatient_visits$PAT_KEY])),
NA),NA)
}
However, this takes very long to run for the full dataset, and even when completed, the FOLLOWUP_DATE is the overall minimum value for APPT_CHECKIN_DT for the whole dataset, not just the records where PAT_KEY matches.
Upvotes: 0
Views: 47
Answers (2)
Parfait
Reputation: 107767
Consider a merge with subset then aggregate with min then merge on original df:
Data
setClass('myDate')
setAs('character', 'myDate', function(from) as.POSIXct(from, format='%Y-%m-%d %H:%M:%S'))
hospital_encounters <- read.table(text="
VISIT_KEY PAT_KEY DICT_ENC_TYPE_KEY HOSP_DISCHRG_DT MED_ORD_ID HAD_FOLLOWUP
1 82919395 8979499 83 '2014-09-07 10:47:00' 58826846 1
2 82919395 8979499 83 '2014-09-07 10:47:00' 58826847 1
3 82919395 8979499 83 '2014-09-07 10:47:00' 58826848 1
4 82919395 8979499 83 '2014-09-07 10:47:00' 58826845 1
5 77312433 8979499 83 '2015-02-01 09:33:00' 98525833 1
6 77312433 8979499 83 '2015-02-01 09:33:00' 98525834 1
7 77312433 8979499 83 '2015-02-01 09:33:00' 98525835 1",
header=TRUE, colClasses = c('numeric', 'numeric', 'numeric', 'numeric', 'myDate', 'numeric', 'numeric'),
stringsAsFactors = FALSE)
follow_up_encounters <- read.table(text=" VISIT_KEY PAT_KEY DICT_ENC_TYPE_KEY APPT_CHECKIN_DT
1 84273751 8979499 108 '2015-02-07 11:57:46'
2 83999897 8979499 108 '2014-09-13 16:51:22'
3 83881023 8979499 108 '2014-11-12 10:37:51'
4 83999896 8979499 108 '2014-11-20 09:23:25'
5 95164335 8979499 108 '2016-07-27 15:30:25'
6 83922326 8979499 108 '2014-11-16 09:08:47'",
header=TRUE, colClasses = c('numeric', 'numeric', 'numeric', 'numeric', 'myDate'),
stringsAsFactors = FALSE)
Process
mdf <- subset(merge(hospital_encounters, follow_up_encounters[c("PAT_KEY", "APPT_CHECKIN_DT")],
by=c("PAT_KEY")), APPT_CHECKIN_DT > HOSP_DISCHRG_DT)
aggdf <- setNames(aggregate(APPT_CHECKIN_DT~ PAT_KEY + VISIT_KEY, mdf, FUN=min),
c("PAT_KEY", "VISIT_KEY", "FOLLOWUP_DT"))
hospital_encounters <- merge(hospital_encounters, aggdf, c("PAT_KEY", "VISIT_KEY"))
Output
hospital_encounters
# PAT_KEY VISIT_KEY DICT_ENC_TYPE_KEY HOSP_DISCHRG_DT MED_ORD_ID HAD_FOLLOWUP FOLLOWUP_DT
# 1 8979499 77312433 83 2015-02-01 09:33:00 98525833 1 2015-02-07 11:57:46
# 2 8979499 77312433 83 2015-02-01 09:33:00 98525834 1 2015-02-07 11:57:46
# 3 8979499 77312433 83 2015-02-01 09:33:00 98525835 1 2015-02-07 11:57:46
# 4 8979499 82919395 83 2014-09-07 10:47:00 58826846 1 2014-09-13 16:51:22
# 5 8979499 82919395 83 2014-09-07 10:47:00 58826847 1 2014-09-13 16:51:22
# 6 8979499 82919395 83 2014-09-07 10:47:00 58826848 1 2014-09-13 16:51:22
# 7 8979499 82919395 83 2014-09-07 10:47:00 58826845 1 2014-09-13 16:51:22
Upvotes: 1
chinsoon12
Reputation: 25208
this question is probably easier and faster to solve using data.table
library(data.table)
#read in the data, convert to data.table using setDT, convert the datetime column into POSIX format, set up a joining key
dt1 <- setDT(read.table(text="VISIT_KEY PAT_KEY DICT_ENC_TYPE_KEY HOSP_DISCHRG_DT MED_ORD_ID HAD_FOLLOWUP
82919395 8979499 83 '2014-09-07 10:47:00' 58826846 1
82919395 8979499 83 '2014-09-07 10:47:00' 58826847 1
82919395 8979499 83 '2014-09-07 10:47:00' 58826848 1
82919395 8979499 83 '2014-09-07 10:47:00' 58826845 1
77312433 8979499 83 '2015-02-01 09:33:00' 98525833 1
77312433 8979499 83 '2015-02-01 09:33:00' 98525834 1
77312433 8979499 83 '2015-02-01 09:33:00' 98525835 1", header=TRUE))[,
HOSP_DISCHRG_DT:=strptime(HOSP_DISCHRG_DT, format="%Y-%m-%d %H:%M:%S")][,
KEY_DATE:=HOSP_DISCHRG_DT]
dt2 <- setDT(read.table(text="VISIT_KEY PAT_KEY DICT_ENC_TYPE_KEY APPT_CHECKIN_DT
84273751 8979499 108 '2015-02-07 11:57:46'
83999897 8979499 108 '2014-09-13 16:51:22'
83881023 8979499 108 '2014-11-12 10:37:51'
83999896 8979499 108 '2014-11-20 09:23:25'
95164335 8979499 108 '2016-07-27 15:30:25'
83922326 8979499 108 '2014-11-16 09:08:47'", header=TRUE))[,
APPT_CHECKIN_DT:=strptime(APPT_CHECKIN_DT, format="%Y-%m-%d %H:%M:%S")][,
list(APPT_CHECKIN_DT, KEY_DATE=APPT_CHECKIN_DT)]
#do check out ?data.table to understand the meaning of roll
dt2[dt1, on="KEY_DATE", roll=-Inf][,
KEY_DATE:=NULL] #remove the joining key
Upvotes: 0
Related Questions
- How to pick the nearest date above a certain date in one dataframe from another
- R searching for minimum date across columns based on a condition
- R Find minimum Date Value for Dataframe column
- Taking minimum difference of dates between two data frame for the same id
- Loop through all entries in one dataframe to find matches based on date criteria in another dataframe in R
- R: Finding the closest of two dates according to an index column
- R: Findest nearest smaller value/date from another dataframe
- How to match by nearest date from two data frames?
- R: Find Min Date from Another Matrix
- R conditionally matching date-time from one dataframe to closest date-time field in second dataframe