我有一個如下所示的 pandas 資料框（示例），我想創建一個帶有額外列“NewDate”的新表，該表將查看 StartDate 并顯示該月的最后一個日期作為開始日期，然后顯示每個月的最后一個日期，直到每個 ID 的結束日期，如果我的 ID 的結束日期為 Null，則該系列將在當月的最后一個日期停止，即 2022 年 5 月。
ID StartDate EndDate
100 1/01/2022 26/04/2022
101 20/04/2022 Null
102 1/01/2022 27/02/2022
....
我的預期輸出：
ID StartDate EndDate NewDate
100 1/01/2022 26/04/2022 31/01/2022
100 1/01/2022 26/04/2022 28/02/2022
100 1/01/2022 26/04/2022 31/03/2022
100 1/01/2022 26/04/2022 30/04/2022
101 20/04/2022 Null 30/04/2022
101 20/04/2022 Null 31/05/2022
102 1/01/2022 27/02/2022 31/01/2022
102 1/01/2022 27/02/2022 28/02/2022
...
uj5u.com熱心網友回復：
您需要先修剪日期，然后我們使用pd.date_range和explode列創建日期范圍
s1 = pd.to_datetime(df.StartDate, format = '%d/%m/%Y')
s2 = pd.to_datetime(df.EndDate, format = '%d/%m/%Y', errors = 'coerce') pd.offsets.MonthEnd(0)
s2 = s2.fillna(s1 pd.offsets.MonthEnd(2))
df['new'] = [pd.date_range(x, y , freq= 'M',closed = 'left') for x , y in zip(df.StartDate, s pd.offsets.MonthEnd(1))]
out = df.explode('new')
out
Out[206]:
ID StartDate EndDate new
0 100 1/01/2022 26/04/2022 2022-01-31
0 100 1/01/2022 26/04/2022 2022-02-28
0 100 1/01/2022 26/04/2022 2022-03-31
0 100 1/01/2022 26/04/2022 2022-04-30
1 101 20/04/2022 Null 2022-04-30
1 101 20/04/2022 Null 2022-05-31
2 102 1/01/2022 27/02/2022 2022-01-31
2 102 1/01/2022 27/02/2022 2022-02-28
更新
s2 = s2.fillna(s1 pd.offsets.MonthEnd(1))
df['new_date'] = [pd.date_range(x, y , freq= 'M',closed = 'left') for x , y in zip(df.start_date, s2 pd.offsets.MonthEnd(1))]
output = df.explode('new_date')
uj5u.com熱心網友回復：
試試這個
# convert each date column to datetime
df['StartDate'] = pd.to_datetime(df['StartDate'])
df['EndDate'] = pd.to_datetime(df['EndDate'])
# create date ranges for each row
f = lambda s,e: pd.date_range(s, e pd.DateOffset(months=1), freq='M')
df['NewDate'] = [f(s,e) if e==e else f(s,pd.datetime.now()) for s, e in zip(df['StartDate'], df['EndDate'])]
# explode the new column
df = df.explode('NewDate')
print(df)
ID StartDate EndDate NewDate
0 100 2022-01-01 2022-04-26 2022-01-31
0 100 2022-01-01 2022-04-26 2022-02-28
0 100 2022-01-01 2022-04-26 2022-03-31
0 100 2022-01-01 2022-04-26 2022-04-30
1 101 2022-04-20 NaT 2022-04-30
1 101 2022-04-20 NaT 2022-05-31
2 102 2022-01-01 2022-02-27 2022-01-31
2 102 2022-01-01 2022-02-27 2022-02-28
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/480129.html
下一篇：JSDate對DST更改沒有反應