I have 2 Pandas dataframe as below:
A = pd.DataFrame({'UserId':['U1','U1','U1','U2','U2','U3'],'Start_Week':['1/6/2020','15/6/2020','6/7/2020','4/5/2020','18/5/2020','20/4/2020'],'Rollback':['1/6/2019','15/6/2019','6/7/2019','4/5/2019','18/5/2019','20/4/2019']})
B = pd.DataFrame({'UserId':['U1','U1','U1','U1','U2','U2','U3'],'Date':['5/6/2019','4/3/2019','1/1/2019','11/7/2019','4/5/2019','7/11/2019','9/10/2019'],'V1':[2,3,1,2,4,1,1],'V2':[0,1,0,0,1,2,1]})
I want to do below next steps and add more columns to dataframe A
- For each row of 'A', find all corresponding rows from dataframe 'B' that fall between columns Rollback and Start_Week.
For example - 1st row of 'A' -> Rollback is '1/6/2019' and Start_Week is '1/6/2020. All rows from dataframe 'B' that fall between these dates are 2nd and 3rd rows i.e. dates 4/06/2019 and 1/7/2019 (for User U1).
For these 2 rows, I will sum up column V1 and display this value in front of 1st row of 'A'
Similarly, for column V2, I will find count values > 0 in column V2 and display in front of 1st row of dataframe 'A'
- The similar operation to be done for all rows of dataframe 'A'
Important - This has to be done user wise.
The output will look like this
pd.DataFrame({'UserId':['U1','U1','U1','U2','U2','U3'],'Start_Week':['1/6/2020','15/6/2020','6/7/2020','4/5/2020','18/5/2020','20/4/2020'],'V1':[4,3,2,1,0,0],'V2':[1,0,0,1,0,0]})
My dataframe has large number of rows. Is there a quick way to get this done ?
