是否为两列之间的所有日期添加行?

2022-02-26 00:00:00 python-3.x pandas pandas-groupby

问题描述

在两列之间添加所有日期的行?

ID     Initiation_Date  Step    Start_Date   End_Date    Days

P-03    29-11-2018        3      2018-11-29  2018-12-10  11.0
P-04    29-11-2018        4      2018-12-03  2018-12-07   4.0
P-05    29-11-2018        5      2018-12-07  2018-12-07   0.0

解决方案

使用:

mydata = [{'ID' : '10', 'Entry Date': '10/10/2016', 'Exit Date': '15/10/2016'},
          {'ID' : '20', 'Entry Date': '10/10/2016', 'Exit Date': '18/10/2016'}]

df = pd.DataFrame(mydata)

#convert columns to datetimes
df[['Entry Date','Exit Date']] = df[['Entry Date','Exit Date']].apply(pd.to_datetime)

#repeat index by difference of dates
df = df.loc[df.index.repeat((df['Exit Date'] - df['Entry Date']).dt.days + 1)]
#add counter duplicated rows to day timedeltas to new column
df['Date'] = df['Entry Date'] + pd.to_timedelta(df.groupby(level=0).cumcount(), unit='d')
#default RangeIndex
df = df.reset_index(drop=True)
print (df)
   Entry Date  Exit Date  ID       Date
0  2016-10-10 2016-10-15  10 2016-10-10
1  2016-10-10 2016-10-15  10 2016-10-11
2  2016-10-10 2016-10-15  10 2016-10-12
3  2016-10-10 2016-10-15  10 2016-10-13
4  2016-10-10 2016-10-15  10 2016-10-14
5  2016-10-10 2016-10-15  10 2016-10-15
6  2016-10-10 2016-10-18  20 2016-10-10
7  2016-10-10 2016-10-18  20 2016-10-11
8  2016-10-10 2016-10-18  20 2016-10-12
9  2016-10-10 2016-10-18  20 2016-10-13
10 2016-10-10 2016-10-18  20 2016-10-14
11 2016-10-10 2016-10-18  20 2016-10-15
12 2016-10-10 2016-10-18  20 2016-10-16
13 2016-10-10 2016-10-18  20 2016-10-17
14 2016-10-10 2016-10-18  20 2016-10-18

相关文章