Pandas dates - pairing Mondays with other days from the same week

user_15524510 0
Viewed 75

I have a dataframe full of Mondays or Tuesdays as dates and another dataframe full of Mondays, Tuesdays and Wednesdays. I'd like to match each of the dates in the second dataframe with the Monday or Tuesday in the first dataframe of the same week:

import pandas as pd

df1 = pd.DataFrame(['01-25-2022','01-17-2022'])
df2 = pd.DataFrame(['01-26-2022','01-27-2022','01-20-2022'])

So in that example I would like a third dataframe as output which combines df1 and df2:

df3 = pd.DataFrame([['01-25-2022','01-25-2022','01-17-2022'],['01-26-2022','01-27-2022','01-20-2022']]).T
pandas merge date
1 Answers

You can get the week (Mon-Sun) by using .dt.to_period('W') (by default .dt.to_period('W-SUN') for Sunday as last week day):

df1 = pd.DataFrame({'A': ['01-25-2022','01-17-2022']},
                    dtype='datetime64[s]')
df2 = pd.DataFrame({'B': ['01-26-2022','01-27-2022','01-20-2022']},
                    dtype='datetime64[s]')

df1.merge(df2,
          left_on=df1['A'].dt.to_period('W'),
          right_on=df2['B'].dt.to_period('W'),
          how='right'
         ).drop(columns='key_0')

output:

           A          B
0 2022-01-25 2022-01-26
1 2022-01-25 2022-01-27
2 2022-01-17 2022-01-20
user_163434640
Related