How do I extract the rows from another dataframe based on a datetime constraint?

Question

Basically I am trying to answer the question: "What are the clients most recent 4 orders from X date..." but the problem is I am trying to do that for each row in a table that is ordered by email logs and that have a unique date.

So I would have to look at each date in those email logs (df1), find out if the agent_id in df1 matches the agent_id df2 (order history), and then pull the most recent 4 orders from df2. I.E: Client 123 receives an email on March 3rd (df1)... Then I would need to pull the 4 most recent entries of client 123 in df2 that are equal or less than the date (March 3rd).

I came up with this messy function but it's not really practical when looping over 1000s of rows... Any ideas for scaling this up?

Thanks,

df1 = pd.DataFrame({'unique_col': ['a', 'b', 'c', 'd'], 'agent_id': [1, 2, 3, 4], 'created_at_email': ['1/5/2020', '1/6/2020', '1/8/2020', '1/8/2020']})
df2 = pd.DataFrame({'unique_col': ['a', 'b', 'c', 'd'], 'agent_id': [1, 1, 3, 1], 'created_at': ['1/4/2020', '1/5/2020', '1/6/2020', '1/9/2020']})

# note: super not optimized at all...
def function():
    for index, row in df1.iterrows():
        for index, row2 in df2.iterrows():
            if row['agent_id'] == row2['agent_id']:
                if row2['created_at'] <= row['created_at_email']:
                    print( 1, row2['created_at'], row['created_at_email'], row2['agent_id'], row['agent_id'], row['unique_col'], row2['unique_col'])
                else:
                    print( 0, row2['created_at'], row['created_at_email'], row2['agent_id'], row['agent_id'], row['unique_col'], row2['unique_col'])
            #else:
                #print( 0, row2['created_at'], row['created_at_email'], row2['agent_id'], row['agent_id'])
                    
                    
            
function()

output:
1 1/4/2020 1/5/2020 1 1 a a
1 1/5/2020 1/5/2020 1 1 a b
0 1/9/2020 1/5/2020 1 1 a d
1 1/6/2020 1/8/2020 3 3 c c

user_12608730 · Answer

Firstly, we need to do a correspondence between df1 and df2 to retrieve the created_at_email

To do this you can set both dataframes indexes to be the agent_id column, after this we can join both dataframes

df1.index = df1.agent_id
df2.index = df2.agent_id
result = df2.join(df1,lsuffix='_df2')

The join method match based on the equality of the index.

We will reset the index of result to prevent errors during the groupby and also remove the unnecessary columns

result.reset_index(drop=True,inplace=True)
result.drop(['agent_id_df2'],axis=1,inplace=True)

To maintain the most recent orders of each agent_id we will sort the dataframe on created_at and agent_id

result.sort_values(['agent_id','created_at'],inplace=True)

After that you can do a groupby with the head method to retrieve the 4 recent orders of each agent

result = result.groupby('agent_id').head(4)

Final Result

	unique_col_df2	created_at	unique_col	agent_id	created_at_email
0	a	1/4/2020	a	1	1/5/2020
1	b	1/5/2020	a	1	1/5/2020
2	d	1/9/2020	a	1	1/5/2020
3	c	1/6/2020	c	3	1/8/2020

How do I extract the rows from another dataframe based on a datetime constraint?

1 Answers

Final Result