Using query to combine pandas dataframes

Question

I'm working on a problem where I need to merge two dataframes together and apply a condition similar to the 'where' clause in SQL. To start I have two dataframes with me :

Member_Timepoints = pd.DataFrame(list(zip([1001,1001,1002,1003],['2016-09-02','2018-01-30','2018-03-17','2019-01-10'])),columns = ['Member_ID','Discharge_Date'])

Enrollment_Information = pd.DataFrame(list(zip([1001,1001,1002,1003,1003,1003,1003], ['2015-07-01','2018-01-01','2018-03-01','2017-11-01','2018-08-01','2019-07-01','2019-09-01'], ['2018-01-01','2262-04-11','2018-08-01','2018-08-01','2019-06-01','2019-08-01','2262-04-11'])), columns = ['Member_ID','Coverage_Effective_Date','Coverage_Cancel_Date'])

Member_Timepoints['Discharge_Date'] = pd.to_datetime(Member_Timepoints['Discharge_Date'])
Enrollment_Information['Coverage_Effective_Date'] = pd.to_datetime(Enrollment_Information['Coverage_Effective_Date'])
Enrollment_Information['Coverage_Cancel_Date'] = pd.to_datetime(Enrollment_Information['Coverage_Cancel_Date'])

I need to join these dataframes together on 'Member_ID' and want to use the following condition as a filtration criteria :

Coverage_Effective_Date <= Discharge_Date and Coverage_Cancel_Date >= Discharge_Date + 30

I referred Join pandas dataframes based on different conditions to start, However, I am still struggling to merge the dataframes together with the above condition applied.

Can anyone please help me to implement this in pandas using query?

Inputvector · Accepted Answer · 2021-08-27 19:11:47Z

1

The first thing I've seen in this condition is data type and integer addition. You cannot add different data type. You should use timedelta:

from datetime import timedelta
some_date_type + timedelta(days=30)

For the query part you can use .loc after merging:

data = Enrollment_Information.merge(Member_Timepoints, on=['Member_ID'])
data.loc[(data['Coverage_Cancel_Date'] <= data['Discharge_Date'] ) &
         (data['Coverage_Cancel_Date'] >= data['Discharge_Date']+timedelta(days=30)) ]

edited Aug 27, 2021 at 19:11

answered Aug 26, 2021 at 1:06

Inputvector

1,09310 silver badges22 bronze badges

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

2 Comments

user47 Over a year ago

That was a condition, not part of the code. Thank you for the suggestion and your answer:)

Inputvector Over a year ago

You're right I just noticed. I was thinking you are using the condition in your code. I fix it :)

Collectives™ on Stack Overflow

Using query to combine pandas dataframes

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related