我有以下数据帧:
Date Holiday
0 2018-01-01 New Year's Day
1 2018-01-15 Martin Luther King, Jr. Day
2 2018-02-19 Washington's Birthday
3 2018-05-08 Truman Day
4 2018-05-28 Memorial Day
... ... ...
58 2022-10-10 Columbus Day
59 2022-11-11 Veterans Day
60 2022-11-24 Thanksgiving
61 2022-12-25 Christmas Day
62 2022-12-26 Christmas Day (Observed)
我想重新采样此数据框,使其是每日 df 的每小时 df(同时将假期列中的内容复制到正确的日期(。我希望它看起来像这样[忽略表格的索引,它应该比这多得多的数字]
Timestamp Holiday
0 2018-01-01 00:00:00 New Year's Day
1 2018-01-01 01:00:00 New Year's Day
2 2018-01-01 02:00:00 New Year's Day
3 2018-01-01 03:00:00 New Year's Day
4 2018-01-01 04:00:00 New Year's Day
5 2018-01-01 05:00:00 New Year's Day
... ... ...
62 2022-12-26 20:00:00 Christmas Day (Observed)
63 2022-12-26 21:00:00 Christmas Day (Observed)
64 2022-12-26 22:00:00 Christmas Day (Observed)
65 2022-12-26 23:00:00 Christmas Day (Observed)
最快的方法是什么?提前谢谢。
怎么样
df.set_index("Date").resample("H").ffill().reset_index().rename(
{"Date": "Timestamp"}, axis=1
)
(1( 使用date_range
创建一个新的数据帧,(2( 将其与原始 DF 连接,(3( 使用reset_index
再次将日期作为列,(4( 使用groupby
和ffill
填充空插槽,(5( 排序值并删除重复项/NaN 值。
dates = pd.DataFrame(pd.date_range(df2['date'].min(), df2['date'].max(), freq='H'), columns=['date']).set_index('date')
df3 = pd.concat([df2.set_index('date'), dates], sort = False)
df3.reset_index(inplace = True)
df3['Holiday'] = df3.groupby(df3['date'].dt.date)['Holiday'].ffill()
df3 = df3.sort_values('date').drop_duplicates().dropna(axis = 0)