python - 试图在两个日期列之间进行区分

Question

我有一个数据框如下:

name   country    Join Date      End date 
Wrt     IND        1-2-2016      8-9-2017
Grt     China      3-2-2015     12-6-2018
frt     France     8-3-2017     continuing 
srt     Scottland   9-4-2018     continuing
crt     china       9-7-2016     7-8-2018

我试图找出加入日期和结束日期之间的区别。我尝试使用 f9['Num of days'] = f9['End date'] - f9['Join Date']，但收到以下错误:

TypeError: unsupported operand type(s) for -: 'DatetimeIndex' and 'float'

我的预期输出应该是:

   name   country    Join Date      End date   diff 
   Wrt     IND        1-2-2016      8-9-2017   395
   Grt     China      3-2-2017      12-6-2018  160
   frt     France     8-3-2017     continuing  continuing
   srt     Scottland   9-4-2018     continuing  continuing
   crt     china       9-7-2017     7-8-2018     280

Answer 1

首先将两列转换为带有参数 errors='coerce' 的日期时间，如果日期错误如字符串 continuing 则缺少值，如有必要还添加参数 dayfirst= True，然后减去值，通过 Series.dt.days 得到天数从 timedeltas 和 last 如有必要用 Series.fillna 替换错误值:

f9['Join Date'] = pd.to_datetime(f9['Join Date'], errors='coerce', dayfirst=True)
f9['End date'] = pd.to_datetime(f9['End date'], errors='coerce', dayfirst=True)

f9['Num of days'] = (f9['End date'] - f9['Join Date']).dt.days.fillna('continuing')
print (f9)
  name    country  Join Date   End date Num of days
0  Wrt        IND 2016-02-01 2017-09-08         585
1  Grt      China 2015-02-03 2018-06-12        1225
2  frt     France 2017-03-08        NaT  continuing
3  srt  Scottland 2018-04-09        NaT  continuing
4  crt      china 2016-07-09 2018-08-07         759

或者:

f9['Join Date'] = pd.to_datetime(f9['Join Date'], errors='coerce')
f9['End date'] = pd.to_datetime(f9['End date'], errors='coerce')

f9['Num of days'] = (f9['End date'] - f9['Join Date']).dt.days.fillna('continuing')
print (f9)
  name    country  Join Date   End date Num of days
0  Wrt        IND 2016-01-02 2017-08-09         585
1  Grt      China 2015-03-02 2018-12-06        1375
2  frt     France 2017-08-03        NaT  continuing
3  srt  Scottland 2018-09-04        NaT  continuing
4  crt      china 2016-09-07 2018-07-08         669

另外最后一步应该是替换缺失值，但丢失了datetime列，得到带有datetimes的混合字符串，所以后来的datetimelike函数失败了:

f9['End date'] = f9['End date'].fillna('continuing')
print (f9)
  name    country  Join Date             End date Num of days
0  Wrt        IND 2016-01-02  2017-08-09 00:00:00         585
1  Grt      China 2015-03-02  2018-12-06 00:00:00        1375
2  frt     France 2017-08-03           continuing  continuing
3  srt  Scottland 2018-09-04           continuing  continuing
4  crt      china 2016-09-07  2018-07-08 00:00:00         669

编辑:

您可以添加多个条件，从顶部数字或底部数字，这里也可以使用 Series.between功能:

f9['Join Date'] = pd.to_datetime(f9['Join Date'], errors='coerce')
f9['End date'] = pd.to_datetime(f9['End date'], errors='coerce')

f9['Num of days'] = (f9['End date'] - f9['Join Date']).dt.days

m1 = f9['Num of days'] > 730
m2 = f9['Num of days'].between(365, 730)
m3 = f9['Num of days'] < 365 
m4 = f9['Num of days'].isna()

f9['Status'] = np.select([m1, m2, m3,m4], ['U','L', 'N','EOL']) 

f9[['End date','Num of days']] = f9[['End date','Num of days']].fillna('continuing')
print (f9)

  name    country  Join Date             End date Num of days Status
0  Wrt        IND 2016-01-02  2017-08-09 00:00:00         585      L
1  Grt      China 2015-03-02  2018-12-06 00:00:00        1375      U
2  frt     France 2017-08-03           continuing  continuing    EOL
3  srt  Scottland 2018-09-04           continuing  continuing    EOL
4  crt      china 2016-09-07  2018-07-08 00:00:00         669      L

另一个想法是使用 cut用于装箱:

f9['Join Date'] = pd.to_datetime(f9['Join Date'], errors='coerce')
f9['End date'] = pd.to_datetime(f9['End date'], errors='coerce')

f9['Num of days'] = (f9['End date'] - f9['Join Date']).dt.days

f9['Status']=pd.cut(f9['Num of days'],bins=[-np.inf, 365, 730, np.inf],labels=['U','L', 'N'])
f9['Status'] = f9['Status'].cat.add_categories(['EOL']).fillna('EOL')
f9[['End date','Num of days']] = f9[['End date','Num of days']].fillna('continuing')
print (f9)
  name    country  Join Date             End date Num of days Status
0  Wrt        IND 2016-01-02  2017-08-09 00:00:00         585      L
1  Grt      China 2015-03-02  2018-12-06 00:00:00        1375      N
2  frt     France 2017-08-03           continuing  continuing    EOL
3  srt  Scottland 2018-09-04           continuing  continuing    EOL
4  crt      china 2016-09-07  2018-07-08 00:00:00         669      L