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

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

CodePudding user response：

Use Series.dt.isocalendar().week with df.merge:

# Find the week number in df1
In [283]: df1['week_num'] = pd.to_datetime(df1[0]).dt.isocalendar().week

# Find the week number in df2
In [284]: df2['week_num'] = pd.to_datetime(df2[0]).dt.isocalendar().week

# Join both df1 and df2 on common week_num
In [293]: df3 = df1.merge(df2, on='week_num').drop('week_num', 1).rename(columns={'0_x': 0, '0_y': 1})

In [294]: df3
Out[294]: 
            0           1
0  01-25-2022  01-26-2022
1  01-25-2022  01-27-2022
2  01-17-2022  01-20-2022

CodePudding user response：

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