Comparing id date combinations in Pandas

user_14273297 0
Viewed 95

I'm a complete beginner to Pandas and wanted to know how to compare a row pair combination in one dataframe with another.

I have df:

    id  dates
0   A   2000-01-01
1   B   2000-01-02
2   C   2000-01-03

and wanted to compare this with df2:

    id  dates
0   A   2000-01-01
1   B   2000-01-03
2   C   2000-01-01
3   D   2000-01-04
4   E   2000-01-05

For each specific id in df2 it compares the corresponding date to df1 (and tells me if the date in df1 is on, or later than date in df2)

So I should get:

    id  dates       later
0   A   2000-01-01  1
1   B   2000-01-02  0
2   C   2000-01-03  1

What's a good way to think about this?

Thanks!

pandas dataframe compare python-3.x
2 Answers

Method 1: You can use Series.map to map the dates from df2 to df1 based on the id, then using Series.ge compare the corresponding dates:

df1['later'] = df1['dates'].ge(df1['id'].map(df2.set_index('id')['dates'])).view('i1')

Method 2: Left merge the dataframe df1 with df2 then use Series.ge to compare the corresponding dates:

df = df1.merge(df2, on='id', suffixes=['', '_r'], how='left')
df['later'] = df['dates'].ge(df.pop('dates_r')).view('i1')

  id      dates  later
0  A 2000-01-01      1
1  B 2000-01-02      0
2  C 2000-01-03      1
user_128331660

You can use pd.merge() on id to compare the dates columns and get the max of the two date columns by passing axis=1. This will return True or False. Then you can do astype(int) to change from True and False to 1 and 0, respectively:

df['later'] = (df['dates'] == pd.merge(df,df2,on='id')[['dates_x','dates_y']].max(axis=1)).astype(int)
df

    id       dates  later
0   A   2000-01-01      1
1   B   2000-01-02      0
2   C   2000-01-03      1
user_63667700
Related