Labeling by date in two data frames

user_12681165 0
Viewed 174
df1
name date
A    14-04-05
A    14-05-08
A    14-08-09
A    15-01-05
B    18-07-05
B    18-08-09
B    18-10-02
C    19-01-03
C    19-02-04
C    19-03-30
D    16-04-01
D    16-08-04

df2
name startdate
A    14-07-07 
B    18-09-09
C    19-03-15
D    16-06-28

Record all dates in one dataset, The second dataset records the start date.

I want to label the records of df1, compared to the start date of df2, record '0' if it is earlier than the start date and '1' if it is the day after the start date.

result what I want

df1
name date     Label startdate
A    14-04-05 0     14-07-07
A    14-05-08 0     14-07-07
A    14-08-09 1     14-07-07
A    15-01-05 1     14-07-07
B    18-07-05 0     18-09-09
B    18-08-09 0     18-09-09
B    18-10-02 1     18-09-09
C    19-01-03 0     19-03-15
C    19-02-04 0     19-03-15
C    19-03-30 1     19-03-15
D    16-04-01 0     16-06-28
D    16-08-04 1     16-06-28

I tried to handle it using datetime, but it didn't work..

Simple example dataset sample

df1 = pd.DataFrame(np.array([['A', '2015-12-21'],['A', '2015-12-22'], ['A', '2015-12-25'], ['B', '2018-01-28'],['B', '2018-02-28'],['B', '2018-03-28']]),
                   columns=['name', 'date'])

df2 = pd.DataFrame(np.array([['A', '2015-12-23'], ['B', '2018-03-01']]),
                   columns=['name', 'startdate'])

Thank you for reading

pandas dataframe label python
2 Answers

Use DataFrame.merge for add new column and then compare by Series.gt for greater with DataFrame.insert for new column by position, for convert to numeric 0,1 is used Series.view:

df1['date'] = pd.to_datetime(df1['date'])
df2['startdate'] = pd.to_datetime(df2['startdate'])

df = df1.merge(df2, on='name', how='left')
df.insert(2, 'Label', df['date'].gt(df['startdate']).view('i1'))
print (df)
   name       date  Label  startdate
0     A 2014-04-05      0 2014-07-07
1     A 2014-05-08      0 2014-07-07
2     A 2014-08-09      1 2014-07-07
3     A 2015-01-05      1 2014-07-07
4     B 2018-07-05      0 2018-09-09
5     B 2018-08-09      0 2018-09-09
6     B 2018-10-02      1 2018-09-09
7     C 2019-01-03      0 2019-03-15
8     C 2019-02-04      0 2019-03-15
9     C 2019-03-30      1 2019-03-15
10    D 2016-04-01      0 2016-06-28
11    D 2016-08-04      1 2016-06-28

Or:

df1['date'] = pd.to_datetime(df1['date'])
df2['startdate'] = pd.to_datetime(df2['startdate'])

df1['startdate'] = df1['name'].map(df2.set_index('name')['startdate'])
df1.insert(2, 'Label', df1['date'].gt(df1['startdate']).view('i1'))
print (df1)
   name       date  Label  startdate
0     A 2014-04-05      0 2014-07-07
1     A 2014-05-08      0 2014-07-07
2     A 2014-08-09      1 2014-07-07
3     A 2015-01-05      1 2014-07-07
4     B 2018-07-05      0 2018-09-09
5     B 2018-08-09      0 2018-09-09
6     B 2018-10-02      1 2018-09-09
7     C 2019-01-03      0 2019-03-15
8     C 2019-02-04      0 2019-03-15
9     C 2019-03-30      1 2019-03-15
10    D 2016-04-01      0 2016-06-28
11    D 2016-08-04      1 2016-06-28
user_29010020

You can map it:

print (df1.assign(new=(df1["date"]>df1["name"].map(df2.set_index("name")["startdate"])).astype(int),
                  start=df1["name"].map(df2.set_index("name")["startdate"])))

   name      date  new     start
0     A  14-04-05    0  14-07-07
1     A  14-05-08    0  14-07-07
2     A  14-08-09    1  14-07-07
3     A  15-01-05    1  14-07-07
4     B  18-07-05    0  18-09-09
5     B  18-08-09    0  18-09-09
6     B  18-10-02    1  18-09-09
7     C  19-01-03    0  19-03-15
8     C  19-02-04    0  19-03-15
9     C  19-03-30    1  19-03-15
10    D  16-04-01    0  16-06-28
11    D  16-08-04    1  16-06-28
user_92844230
Related