我有一个 pandas 数据框,其中包含日期、客户、商品以及购买的美元价值。
date customer product amt
1/1/2017 tim apple 3
1/1/2017 jim melon 2
1/1/2017 tom apple 5
1/1/2017 tom melon 4
1/4/2017 tim melon 3
1/4/2017 jim apple 2
1/4/2017 tom melon 1
1/4/2017 tom orange 4
我试图只查看性能,但我想转发填充最小和最大日期范围内的所有日期,并为每个产品的每个客户填充
就像是:
date customer product amt
1/1/2017 tim apple 3
1/1/2017 tim melon 0
1/1/2017 tim orange 0
1/1/2017 jim melon 2
1/1/2017 jim apple 0
1/1/2017 jim orange 0
1/1/2017 tom apple 5
1/1/2017 tom melon 4
1/1/2017 tom orange 0
1/2/2017 tim apple 0
1/2/2017 tim melon 0
1/2/2017 tim orange 0
1/2/2017 jim melon 0
1/2/2017 jim apple 0
1/2/2017 jim orange 0
1/2/2017 tom apple 0
1/2/2017 tom melon 0
1/2/2017 tom orange 0
1/3/2017 tim apple 0
1/3/2017 tim melon 0
1/3/2017 tim orange 0
1/3/2017 jim melon 0
1/3/2017 jim apple 0
1/3/2017 jim orange 0
1/3/2017 tom apple 0
1/3/2017 tom melon 0
1/3/2017 tom orange 0
1/4/2017 tim melon 3
1/4/2017 tim apple 0
1/4/2017 tim orange 0
1/4/2017 jim apple 2
1/4/2017 jim melon 0
1/4/2017 jim orange 0
1/4/2017 tom melon 1
1/4/2017 tom orange 4
1/4/2017 tom apple 0
我知道我可以根据最大和最小日期创建重新索引,但这也会使我的客户和产品值变为 0。还有其他方法可以解决此问题吗?我是不是漏掉了一个步骤或者什么?感谢您的帮助
请注意,这使用stack
and unstack
几次
df.set_index(['date','customer','product']).amt.unstack(-3).\
reindex(columns=pd.date_range(df['date'].min(),
df['date'].max()),fill_value=0).\
stack(dropna=False).unstack().stack(dropna=False).\
unstack('customer').stack(dropna=False).reset_index().\
fillna(0).sort_values(['level_1','customer','product'])
Out[314]:
product level_1 customer 0
0 apple 2017-01-01 jim 0.0
12 melon 2017-01-01 jim 2.0
24 orange 2017-01-01 jim 0.0
1 apple 2017-01-01 tim 3.0
13 melon 2017-01-01 tim 0.0
25 orange 2017-01-01 tim 0.0
2 apple 2017-01-01 tom 5.0
14 melon 2017-01-01 tom 4.0
26 orange 2017-01-01 tom 0.0
3 apple 2017-01-02 jim 0.0
15 melon 2017-01-02 jim 0.0
27 orange 2017-01-02 jim 0.0
4 apple 2017-01-02 tim 0.0
16 melon 2017-01-02 tim 0.0
28 orange 2017-01-02 tim 0.0
5 apple 2017-01-02 tom 0.0
17 melon 2017-01-02 tom 0.0
29 orange 2017-01-02 tom 0.0
6 apple 2017-01-03 jim 0.0
18 melon 2017-01-03 jim 0.0
30 orange 2017-01-03 jim 0.0
7 apple 2017-01-03 tim 0.0
19 melon 2017-01-03 tim 0.0
31 orange 2017-01-03 tim 0.0
8 apple 2017-01-03 tom 0.0
20 melon 2017-01-03 tom 0.0
32 orange 2017-01-03 tom 0.0
9 apple 2017-01-04 jim 2.0
21 melon 2017-01-04 jim 0.0
33 orange 2017-01-04 jim 0.0
10 apple 2017-01-04 tim 0.0
22 melon 2017-01-04 tim 3.0
34 orange 2017-01-04 tim 0.0
11 apple 2017-01-04 tom 0.0
23 melon 2017-01-04 tom 1.0
35 orange 2017-01-04 tom 4.0
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)