Pandas:groupby 向前填充日期时间索引 [英] Pandas: groupby forward fill with datetime index
问题描述
我有一个包含两列的数据集:公司和价值.
它有一个日期时间索引,其中包含重复项(同一天,不同的公司有不同的值).这些值缺少数据,所以我想用来自同一家公司的以前的数据点向前填充缺失的数据.
I have a dataset that has two columns: company, and value.
It has a datetime index, which contains duplicates (on the same day, different companies have different values). The values have missing data, so I want to forward fill the missing data with the previous datapoint from the same company.
然而,我似乎无法找到一个好的方法来做到这一点,而不会遇到奇怪的 groupby 错误,这表明我做错了什么.
However, I can't seem to find a good way to do this without running into odd groupby errors, suggesting that I'm doing something wrong.
玩具数据:
a = pd.DataFrame({'a': [1, 2, None], 'b': [12,None,14]})
a.index = pd.DatetimeIndex(['2010', '2011', '2012'])
a = a.unstack()
a = a.reset_index().set_index('level_1')
a.columns = ['company', 'value']
a.sort_index(inplace=True)
尝试的解决方案(不起作用:ValueError:无法从重复轴重新索引
):
Attempted solutions (didn't work: ValueError: cannot reindex from a duplicate axis
):
a.groupby('company').ffill()
a.groupby('company')['value'].ffill()
a.groupby('company').fillna(method='ffill')
hacky 解决方案(提供了预期的结果,但显然只是一个丑陋的解决方法):
Hacky solution (that delivers the desired result, but is obviously just an ugly workaround):
a['value'] = a.reset_index().groupby(
'company').fillna(method='ffill')['value'].values
可能有一种简单而优雅的方法可以做到这一点,这在 Pandas 中是如何执行的?
There is probably a simple and elegant way to do this, how is this performed in Pandas?
推荐答案
一种方法是使用transform
函数来填充group by后的value
列:>
One way is to use the transform
function to fill the value
column after group by:
import pandas as pd
a['value'] = a.groupby('company')['value'].transform(lambda v: v.ffill())
a
# company value
#level_1
#2010-01-01 a 1.0
#2010-01-01 b 12.0
#2011-01-01 a 2.0
#2011-01-01 b 12.0
#2012-01-01 a 2.0
#2012-01-01 b 14.0
为了比较,原始数据框看起来像:
To compare, the original data frame looks like:
# company value
#level_1
#2010-01-01 a 1.0
#2010-01-01 b 12.0
#2011-01-01 a 2.0
#2011-01-01 b NaN
#2012-01-01 a NaN
#2012-01-01 b 14.0
这篇关于Pandas:groupby 向前填充日期时间索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!