Matching datetime column in pandas with another datetime column and return index-CodePudding

I have two DataFrames - df1 and df2. Both of them contain a datetime column, say date1 and date2. I want to match each value of date1 column to date2 and store the index in a new column. I am trying the following code:

df1['location'] = df2.loc[df1['date1'] == df2['date2']]

but this line throws out following error:

Can only compare identically-labeled series objects.

I also tried using the index function as follows:

df1['location'] = df2.index(df1['date1'] == df2['date2'])

This also raised the same error as the previous code.

How can I get the index of date from df2 DataFrame which matches date in df1 DataFrame? I need to do this for each value in df1.

CodePudding user response：

Try to setup a MRE:

df1 = pd.DataFrame({'date1': pd.date_range('2022-1-1', periods=5, freq='D')})
df2 = pd.DataFrame({'date2': pd.date_range('2022-1-3', periods=4, freq='D')})

# df1
#        date1
# 0 2022-01-01
# 1 2022-01-02
# 2 2022-01-03
# 3 2022-01-04
# 4 2022-01-05

# df2
#        date2
# 0 2022-01-03
# 1 2022-01-04
# 2 2022-01-05
# 3 2022-01-06

Swap current index of df2 with date2 columns and map the series to date1 column of df1:

df1['location'] = df1['date1'].map(df2.reset_index().set_index('date2')['index'])
print(df1)

# Output
       date1  location
0 2022-01-01       NaN
1 2022-01-02       NaN
2 2022-01-03       0.0
3 2022-01-04       1.0
4 2022-01-05       2.0

CodePudding user response：

for i, row in df2.iterrows():
    df1.loc[df1['date1'] == df2.at[i, 'date2'], 'location'] = i