python - 如何在按 pandas 数据框中的另一列分组后插入缺失的日期和前向填充列

Question

我有每月可用的数据(针对不同的证券)，我想通过添加缺失的日期并向前填充该月所有日期的每月数据(即 12/3 的数据)将其转换为每日数据/2015 = 2015 年 12 月 1 日的数据，所有证券依此类推)。

我的数据如下所示:

x = pd.DataFrame({'ticker': ['a','a','a','b','b'], 'dt': ['12/1/2015','1/1/2016','2/1/2016','1/1/2016','2/1/2016'], 'score': [2.8,3.8,3.8,1.9,1.7]})

我尝试使用日期和股票行情创建多索引，并使用每日频率重新采样。但是，我收到“ValueError:无法从重复轴重新索引”错误。

x['dt'] = pd.to_datetime(x['dt'])
dates = x.set_index('dt').resample('D').asfreq().index
ticker = x['ticker'].unique()
idx = pd.MultiIndex.from_product((dates, ticker), names=['dt', 'ticker'])
x.set_index(['dt', 'ticker']).reindex(idx, method='bfill').reset_index()
x.set_index(['dt', 'ticker']).reindex(idx, method='ffill').reset_index().sort_values(by='ticker')

预期结果:

x = pd.DataFrame({'ticker': ['a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b'], 'dt': ['12/1/2015', '12/2/2015', '12/3/2015', '12/4/2015', '12/5/2015', '12/6/2015', '12/7/2015', '12/8/2015', '12/9/2015', '12/10/2015', '12/11/2015', '12/12/2015', '12/13/2015', '12/14/2015', '12/15/2015', '12/16/2015', '12/17/2015', '12/18/2015', '12/19/2015', '12/20/2015', '12/21/2015', '12/22/2015', '12/23/2015', '12/24/2015', '12/25/2015', '12/26/2015', '12/27/2015', '12/28/2015', '12/29/2015', '12/30/2015', '12/31/2015', '1/1/2016', '1/2/2016', '1/3/2016', '1/4/2016', '1/5/2016', '1/6/2016', '1/7/2016', '1/8/2016', '1/9/2016', '1/10/2016', '1/11/2016', '1/12/2016', '1/13/2016', '1/14/2016', '1/15/2016', '1/16/2016', '1/17/2016', '1/18/2016', '1/19/2016', '1/20/2016', '1/21/2016', '1/22/2016', '1/23/2016', '1/24/2016', '1/25/2016', '1/26/2016', '1/27/2016', '1/28/2016', '1/29/2016', '1/30/2016', '1/31/2016', '2/1/2016','12/1/2015', '12/2/2015', '12/3/2015', '12/4/2015', '12/5/2015', '12/6/2015', '12/7/2015', '12/8/2015', '12/9/2015', '12/10/2015', '12/11/2015', '12/12/2015', '12/13/2015', '12/14/2015', '12/15/2015', '12/16/2015', '12/17/2015', '12/18/2015', '12/19/2015', '12/20/2015', '12/21/2015', '12/22/2015', '12/23/2015', '12/24/2015', '12/25/2015', '12/26/2015', '12/27/2015', '12/28/2015', '12/29/2015', '12/30/2015', '12/31/2015', '1/1/2016', '1/2/2016', '1/3/2016', '1/4/2016', '1/5/2016', '1/6/2016', '1/7/2016', '1/8/2016', '1/9/2016', '1/10/2016', '1/11/2016', '1/12/2016', '1/13/2016', '1/14/2016', '1/15/2016', '1/16/2016', '1/17/2016', '1/18/2016', '1/19/2016', '1/20/2016', '1/21/2016', '1/22/2016', '1/23/2016', '1/24/2016', '1/25/2016', '1/26/2016', '1/27/2016', '1/28/2016', '1/29/2016', '1/30/2016', '1/31/2016', '2/1/2016'], 'score': [2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 2.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 3.8, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.9, 1.7]})

实际结果:出现错误 - 'ValueError:无法从重复轴重新索引'

如果有任何帮助，我将不胜感激。谢谢

Answer 1

IIUC，让我们尝试使用 groupby 和 resample:

x['dt'] = pd.to_datetime(x['dt'])
x = x.set_index('dt')

df_out = x.groupby('ticker', as_index=False, group_keys=False)\
          .apply(lambda d: d.resample('D').ffill())

输出:

          ticker  score
dt                      
2015-12-01      a    2.8
2015-12-02      a    2.8
2015-12-03      a    2.8
2015-12-04      a    2.8
2015-12-05      a    2.8
2015-12-06      a    2.8
2015-12-07      a    2.8
2015-12-08      a    2.8
2015-12-09      a    2.8
2015-12-10      a    2.8
2015-12-11      a    2.8
2015-12-12      a    2.8
2015-12-13      a    2.8
2015-12-14      a    2.8
2015-12-15      a    2.8
2015-12-16      a    2.8
2015-12-17      a    2.8
2015-12-18      a    2.8
2015-12-19      a    2.8
2015-12-20      a    2.8
2015-12-21      a    2.8
2015-12-22      a    2.8
2015-12-23      a    2.8
2015-12-24      a    2.8
2015-12-25      a    2.8
2015-12-26      a    2.8
2015-12-27      a    2.8
2015-12-28      a    2.8
2015-12-29      a    2.8
2015-12-30      a    2.8
...           ...    ...
2016-01-03      b    1.9
2016-01-04      b    1.9
2016-01-05      b    1.9
2016-01-06      b    1.9
2016-01-07      b    1.9
2016-01-08      b    1.9
2016-01-09      b    1.9
2016-01-10      b    1.9
2016-01-11      b    1.9
2016-01-12      b    1.9
2016-01-13      b    1.9
2016-01-14      b    1.9
2016-01-15      b    1.9
2016-01-16      b    1.9
2016-01-17      b    1.9
2016-01-18      b    1.9
2016-01-19      b    1.9
2016-01-20      b    1.9
2016-01-21      b    1.9
2016-01-22      b    1.9
2016-01-23      b    1.9
2016-01-24      b    1.9
2016-01-25      b    1.9
2016-01-26      b    1.9
2016-01-27      b    1.9
2016-01-28      b    1.9
2016-01-29      b    1.9
2016-01-30      b    1.9
2016-01-31      b    1.9
2016-02-01      b    1.7

[95 rows x 2 columns]