Faster way to loop through dataframes?

Question

I'm so desperate to seek help. I have two dataframes,

df1 is the patient's hospital admission record.

patient_key	admission_dtm	admission_key
P001	41765	P001-001
P001	42223	P001-002
P001	42681	P001-003
P001	43139	P001-004
P001	43597	P001-005
P001	44055	P001-006

df2 is the patient's outpatient appointment record

patient_key	appointment_dtm
P001	41645
P001	41687
P001	41717
P001	42162
P001	42193
P001	42497

What I want to do is to find an outpatient appointment before each admission. For example, before P001-001 admission, Patient P001 has 3 times outpatient appointments.

the expected outcome would be like this in df2:

patient_key	appointment_dtm	admission_key
P001	41645	P001-001
P001	41687	P001-001
P001	41717	P001-001
P001	42162	P001-002
P001	42193	P001-002
P001	42497	P001-003

I have used a very silly method like this

df2['admission_key'] = ''
for i in df2.index:
    for j in df1.index:
        if df2.['patient_key'].iloc[i] == df1['patient_key'].iloc[i] and 
           df2.['appointment_dtm'].iloc[i] > df1['admission_dtm'].iloc[i] and
           df2.['appointment_dtm'].iloc[i] < df1['admission_dtm'].iloc[i].shift(-1):
           df2['admission_key'] = df1['admission_key']

However, since the size is too large and it takes a very long time to run. May I know it there are any smarter ways to do this? Thank you so so much.

Peter Leimbigler · Accepted Answer · 2022-07-05 17:49:57Z

5

This is a perfect use case for merge_asof:

pd.merge_asof(df2, df1, 
              by='patient_key', 
              left_on='appointment_dtm',
              right_on='admission_dtm',
              direction='forward').drop(columns='admission_dtm')

Result:

patient_key	appointment_dtm	admission_key
P001	41645	P001-001
P001	41687	P001-001
P001	41717	P001-001
P001	42162	P001-002
P001	42193	P001-002
P001	42497	P001-003

answered Jul 5, 2022 at 17:49

Peter Leimbigler

11.1k1 gold badge27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Faster way to loop through dataframes?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related