基于 pandas 特定条件的时差天数 [英] Time difference in days based on specific condition in pandas
问题描述
我有一个如下所示的数据框
I have a data frame as shown below
ID CONSTRUCTION_DATE START_DATE END_DATE CANCELLED_DATE
1 2016-02-06 2016-02-26 2017-02-26 NaT
1 2016-02-06 2017-03-27 2018-02-26 2017-05-22
1 2016-02-06 2017-08-27 2019-02-26 2017-10-21
1 2016-02-06 2018-07-27 2021-02-26 NaT
2 2016-05-06 2017-03-27 2018-02-26 NaT
2 2016-05-06 2018-08-27 2019-02-26 NaT
以上数据必须根据 ID 和 START_DATE 进行排序.
Above data has to be order based on ID and START_DATE.
从上面的数据框我想准备下面的数据框
From the above data frame I would like to prepare below dataframe
ID D_from_C_to_first_S_D T_D_V_aft_c T_D_V_w_cancel N_of_cancel Lst_END_DATE_to_today
1 20 376 29 2 After_today
1 325 NaN 182 0 358
哪里
CANCELLED_DATE = NaT 表示合同未取消
CANCELLED_DATE = NaT means contract not cancelled
D_from_C_to_first_S_D = 从 CONSTRUCTION_DATE 到第一个 START_DATE 的天数.
D_from_C_to_first_S_D = Days from CONSTRUCTION_DATE to first START_DATE.
T_D_V_aft_c = 取消后总空缺天数(对于 ID = 1，两个取消日期，97+279 = 376)
T_D_V_aft_c = Total days vacant after cancellation ( for ID = 1, two cancelled date, 97+279 = 376)
T_D_V_w_cancel = 未取消的总空置天数(只需求上一个 END_DATE 与下一个 START_DATE 的差值之和.
T_D_V_w_cancel = Total days vacant without cancellation (just find the sum of difference of previous END_DATE with next START_DATE.
Lst_END_DATE_to_today = 从上次结束日期到今天的天数.
Lst_END_DATE_to_today = Days from last end date to today.
推荐答案
首先创建了新列，以便可能的简单验证解决方案:
First was created new columns for possible easy verify solution:
today = pd.to_datetime('now').floor('d')
m = df['CANCELLED_DATE'].isna()
df['D_from_C_to_first_S_D'] = df['START_DATE'].sub(df['CONSTRUCTION_DATE']).dt.days
df['T_D_V_aft_c'] = df.groupby('ID')['START_DATE'].shift(-1).sub(df['CANCELLED_DATE']).dt.days
df['T_D_V_w_cancel'] = df.groupby('ID')['START_DATE'].shift(-1).sub(df.loc[m, 'END_DATE']).dt.days
df['N_of_cancel'] = np.where(m, 0, 1)
s = df['END_DATE'].rsub(today).dt.days
df['Lst_END_DATE_to_today'] = s.mask(s.lt(0), 'After_today')
<小时>
print (df)
ID CONSTRUCTION_DATE START_DATE END_DATE CANCELLED_DATE
0 1 2016-02-06 2016-02-26 2017-02-26 NaT
1 1 2016-02-06 2017-03-27 2018-02-26 2017-05-22
2 1 2016-02-06 2017-08-27 2019-02-26 2017-10-21
3 1 2016-02-06 2018-07-27 2021-02-26 NaT
4 2 2016-05-06 2017-03-27 2018-02-26 NaT
5 2 2016-05-06 2018-08-27 2019-02-26 NaT
ID CONSTRUCTION_DATE START_DATE END_DATE CANCELLED_DATE \
0 1 2016-02-06 2016-02-26 2017-02-26 NaT
1 1 2016-02-06 2017-03-27 2018-02-26 2017-05-22
2 1 2016-02-06 2017-08-27 2019-02-26 2017-10-21
3 1 2016-02-06 2018-07-27 2021-02-26 NaT
4 2 2016-05-06 2017-03-27 2018-02-26 NaT
5 2 2016-05-06 2018-08-27 2019-02-26 NaT
D_from_C_to_first_S_D T_D_V_aft_c T_D_V_w_cancel N_of_cancel \
0 20 NaN 29.0 0
1 415 97.0 NaN 1
2 568 279.0 NaN 1
3 902 NaN NaN 0
4 325 NaN 182.0 0
5 843 NaN NaN 0
Lst_END_DATE_to_today
0 1089
1 724
2 359
3 After_today
4 724
5 359
然后通过GroupBy.agg by first, last 和 sum 在 NaN 的自定义函数中s 代替 0:
And then aggregate by GroupBy.agg by first, last and sum in custom function for NaNs instead 0:
f = lambda x: x.sum(min_count=1)
df1 = df.groupby('ID').agg(D_from_C_to_first_S_D=('D_from_C_to_first_S_D','first'),
T_D_V_aft_c=('T_D_V_aft_c',f),
T_D_V_w_cancel=('T_D_V_w_cancel',f),
N_of_cancel=('N_of_cancel',f),
Lst_END_DATE_to_today=('Lst_END_DATE_to_today','last'),
).reset_index()
print (df1)
ID D_from_C_to_first_S_D T_D_V_aft_c T_D_V_w_cancel N_of_cancel \
0 1 20 376.0 29.0 2
1 2 325 NaN 182.0 0
Lst_END_DATE_to_today
0 After_today
1 359
这篇关于基于 pandas 特定条件的时差天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
