Using this as a starting point:
a=[['username1','Tesco','09:28:27'],['username2','Target','09:01:10'],['username3','Lily','08:27:48']]
df_a=pd.DataFrame(a,columns=['username','pos_name','end_visit'])
b=[['Done','2022-03-13','09:28:00'],['Done','2022-03-13','09:01:00'],['Done','2022-03-13','08:42:00'],['Done','2022-03-13','08:27:00']]
df_b=pd.DataFrame(b,columns=['planogramme','date','hour'])
The result is 2 dataframes that looks like this:
username pos_name end_visit
0 username1 Tesco 09:28:27
1 username2 Target 09:01:10
2 username3 Lily 08:27:48
planogramme date hour
0 Done 2022-03-13 09:28:00
1 Done 2022-03-13 09:01:00
2 Done 2022-03-13 08:42:00
3 Done 2022-03-13 08:27:00
As you can see,it's not the same dimensions and i want to actually compare the hour of 'df_b' with the 'end_visit' of 'df_a', if they are the same i want to create a new column on 'df_a' and copy the value of df_a['planogramme'],in the end it would need to look like something like this
username pos_name end_visit plannograme_done
0 username1 Tesco 09:28:27 Done
1 username2 Target 09:01:10 Done
2 username3 Lily 08:27:48 Done
The problem is that for username3 for example,it needs to iterate over all the rows of 'df_b' and not return the value of the 2nd row but rather the 3rd one.
CodePudding user response:
The easiest approach would be to extract the hour
from df_a:
df_a['hour'] = df_a['end_visit'].str[:5] ':00'
df_a
username pos_name end_visit hour
0 username1 Tesco 09:28:27 09:28:00
1 username2 Target 09:01:10 09:01:00
2 username3 Lily 08:27:48 08:27:00
Then merge df_a
and df_b
on hour
:
df_a.merge(df_b, on = 'hour')
Output:
username pos_name end_visit hour planogramme date
0 username1 Tesco 09:28:27 09:28:00 Done 2022-03-13
1 username2 Target 09:01:10 09:01:00 Done 2022-03-13
2 username3 Lily 08:27:48 08:27:00 Done 2022-03-13