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屋！