Pandas 用多列填充 DataFrame 中缺失的日期 [英] Pandas fill in missing dates in DataFrame with multiple columns
问题描述
我想为特定日期范围添加缺失的日期,但保留所有列.我发现很多帖子使用 afreq()
、resample()
、reindex()
,但它们似乎是针对系列的,我不能让它们为我的 DataFrame 工作.
I want to add missing dates for a specific date range, but keep all columns. I found many posts using afreq()
, resample()
, reindex()
, but they seemed to be for Series and I couldn't get them to work for my DataFrame.
给定一个示例数据帧:
data = [{'id' : '123', 'product' : 'apple', 'color' : 'red', 'qty' : 10, 'week' : '2019-3-7'}, {'id' : '123', 'product' : 'apple', 'color' : 'blue', 'qty' : 20, 'week' : '2019-3-21'}, {'id' : '123', 'product' : 'orange', 'color' : 'orange', 'qty' : 8, 'week' : '2019-3-21'}]
df = pd.DataFrame(data)
color id product qty week
0 red 123 apple 10 2019-3-7
1 blue 123 apple 20 2019-3-21
2 orange 123 orange 8 2019-3-21
我的目标是回到下面;填写数量为0,但填写其他列.当然,我还有很多其他的id.我希望能够指定要填写的开始/结束日期;本例使用 3/7 到 3/21.
My goal is to return below; filling in qty as 0, but fill other columns. Of course, I have many other ids. I would like to be able to specify the start/end dates to fill; this example uses 3/7 to 3/21.
color id product qty week
0 red 123 apple 10 2019-3-7
1 blue 123 apple 20 2019-3-21
2 orange 123 orange 8 2019-3-21
3 red 123 apple 0 2019-3-14
4 red 123 apple 0 2019-3-21
5 blue 123 apple 0 2019-3-7
6 blue 123 apple 0 2019-3-14
7 orange 123 orange 0 2019-3-7
8 orange 123 orange 0 2019-3-14
如何保持 DataFrame 的其余部分完好无损?
How can I keep the remainder of my DataFrame intact?
推荐答案
在你的情况下,你只需要 unstack
和 stack
+ reindex代码>
In you case , you just need do with unstack
and stack
+ reindex
df.week=pd.to_datetime(df.week)
s=pd.date_range(df.week.min(),df.week.max(),freq='7 D')
df=df.set_index(['color','id','product','week']).\
qty.unstack().reindex(columns=s,fill_value=0).stack().reset_index()
df
color id product level_3 0
0 blue 123 apple 2019-03-14 0.0
1 blue 123 apple 2019-03-21 20.0
2 orange 123 orange 2019-03-14 0.0
3 orange 123 orange 2019-03-21 8.0
4 red 123 apple 2019-03-07 10.0
5 red 123 apple 2019-03-14 0.0
这篇关于Pandas 用多列填充 DataFrame 中缺失的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!