用 Pandas 填补累积和的空白 [英] Filling gaps for cumulative sum with Pandas
问题描述
我正在尝试根据 Pandas 中每月存储桶中的表格计算库存库存.这是表:
I'm trying to calculate the inventory of stocks from a table in monthly buckets in Pandas. This is the table:
Goods | Incoming | Date
-------+------------+-----------
'a' | 10 | 2014-01-10
'a' | 20 | 2014-02-01
'b' | 30 | 2014-01-02
'b' | 40 | 2014-05-13
'a' | 20 | 2014-06-30
'c' | 10 | 2014-02-10
'c' | 50 | 2014-05-10
'b' | 70 | 2014-03-10
'a' | 10 | 2014-02-10
这是我目前的代码:
import pandas as pd
df = pd.DataFrame({
'goods': ['a', 'a', 'b', 'b', 'a', 'c', 'c', 'b', 'a'],
'incoming': [0, 20, 30, 40, 20, 10, 50, 70, 10],
'date': ['2014-01-10', '2014-02-01', '2014-01-02', '2014-05-13', '2014-06-30', '2014-02-10', '2014-05-10', '2014-03-10', '2014-02-10']})
df['date'] = pd.to_datetime(df['date'])
# we don't care about year in this example
df['month'] = df['date'].map(lambda x: x.month)
dfg = df.groupby(['goods', 'month'])['incoming'].sum()
# flatten multi-index
dfg = dfg.reset_index ()
dfg['level'] = dfg.groupby(['goods'])['incoming'].cumsum()
dfg
哪个返回
goods month incoming level
0 a 1 0 0
1 a 2 30 30
2 a 6 20 50
3 b 1 30 30
4 b 3 70 100
5 b 5 40 140
6 c 2 10 10
7 c 5 50 60
虽然这很好，但我使用的可视化方法需要(1)每组(商品")具有相同数量的数据点，(2)时间序列的相同范围(即最早/最晚月份是所有时间序列都相同)和 (3) 在任何时间序列中都没有差距"(min(month) 和 max(month) 之间的一个月，有一个数据点).
While this is good, the visualisation method that I use requires (1) the same number of data points per group ('goods'), (2) the same extent of the time-series (i.e. earliest/latest month is the same for all time series) and (3) that there are no "gaps" in any time series (a month between min(month) and max(month) with a data point).
我怎样才能用 Pandas 做到这一点?请注意，即使认为这种结构可能有点低效，我还是想坚持事物的一般流程.也许可以插入一些后处理"来填补空白.
How can I do this with Pandas? Note, even thought this structure may be a bit inefficient, I'd like to stick with the general flow of things. Perhaps it's possible to insert some "post-processing" to fill in the gaps.
更新
为了总结下面的回复，我选择这样做:
To summarise the response below, I chose to do this:
piv = dfg.pivot_table(["level"], "month", "goods")
piv = piv.reindex(np.arange(piv.index[0], piv.index[-1] + 1))
piv = piv.ffill(axis=0)
piv = piv.fillna(0)
piv.index.name = 'month'
我也加了
piv = piv.stack()
print r.reset_index()
得到一个类似于输入表的表:
to get a table similar to the input table:
month goods level
0 1 a 0
1 1 b 30
2 1 c 0
3 2 a 30
4 2 b 30
5 2 c 10
6 3 a 30
7 3 b 100
8 3 c 10
9 4 a 30
10 4 b 100
11 4 c 10
12 5 a 30
13 5 b 140
14 5 c 60
15 6 a 50
16 6 b 140
17 6 c 60
推荐答案
我认为您想使用 pivot_table:
I think you want to use pivot_table:
In [11]: df.pivot_table(values="incoming", index="month", columns="goods", aggfunc="sum")
Out[11]:
goods a b c
month
1 0 30 NaN
2 30 NaN 10
3 NaN 70 NaN
5 NaN 40 50
6 20 NaN NaN
要获得几个月的填充，您可以重新索引(这感觉有点hacky，可能有更简洁的方法):
To get the filled in months, you can reindex (this feels a little hacky, there may be a neater way):
In [12]: res.reindex(np.arange(res.index[0], res.index[-1] + 1))
Out[12]:
goods a b c
1 0 30 NaN
2 30 NaN 10
3 NaN 70 NaN
4 NaN NaN NaN
5 NaN 40 50
6 20 NaN NaN
<小时>
这里的一个问题是月份独立于年份，可能最好有一个周期索引:
One issue here is that month is independent of year, in may be preferable to have a period index:
In [21]: df.pivot_table(values="incoming", index=pd.DatetimeIndex(df.date).to_period("M"), columns="goods", aggfunc="sum")
Out[21]:
goods a b c
2014-01 0 30 NaN
2014-02 30 NaN 10
2014-03 NaN 70 NaN
2014-05 NaN 40 50
2014-06 20 NaN NaN
然后您可以按周期范围重新索引:
and then you can reindex by the period range:
In [22]: res2.reindex(pd.period_range(res2.index[0], res2.index[-1], freq="M"))
Out[22]:
goods a b c
2014-01 0 30 NaN
2014-02 30 NaN 10
2014-03 NaN 70 NaN
2014-04 NaN NaN NaN
2014-05 NaN 40 50
2014-06 20 NaN NaN
<小时>
也就是说，您可以对 dfg 执行相同操作:
In [31]: dfg.pivot_table(["incoming", "level"], "month", "goods")
Out[31]:
incoming level
goods a b c a b c
month
1 0 30 NaN 0 30 NaN
2 30 NaN 10 30 NaN 10
3 NaN 70 NaN NaN 100 NaN
5 NaN 40 50 NaN 140 60
6 20 NaN NaN 50 NaN NaN
并重新索引.
这篇关于用 Pandas 填补累积和的空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
