I have two dataframes:-
df1:-
PID DATE Time Amount
xy1 01/10/2019 8:37 100
xy1 01/10/2019 12:30 100
xy2 01/10/2019 5:01 100
xy3 01/10/2019 23:57 100
df2
PID DATE Time Amount Code
xy1 01/10/2019 8:00 4 38
xy1 01/10/2019 8:05 50 38
xy1 01/10/2019 8:20 60 39
xy1 01/10/2019 8:45 80 39
xy2 01/10/2019 1:02 100 39
xy2 01/10/2019 3:05 100 38
xy2 01/10/2019 5:10 90 38
I want to get the count of df1['PID'] depending on the date&time in df1&df2. i.e How many times df1['PID'][0] is in df2 before the df1['Date', 'Time'] as compared to df2 date time. Really appreciate the help.
Output shall be like this:-
PID Before_df1time_38 Before_df1time_39 Total After_df1time_38 After_df1time_39
xy1 2 1 3 0 1