Python pandas：插入缺失日期的行，时间序列在分组数据框中

Question

我有一个数据框 df:

   Serial_no       date  Index     x    y
           1 2014-01-01      1   2.0  3.0
           1 2014-03-01      2   3.0  3.0
           1 2014-04-01      3   6.0  2.0
           2 2011-03-01      1   5.1  1.3
           2 2011-04-01      2   5.8  0.6
           2 2011-05-01      3   6.5 -0.1
           2 2011-07-01      4   3.0  5.0
           3 2019-10-01      1   7.9 -1.5
           3 2019-11-01      2   8.6 -2.2
           3 2020-01-01      3  10.0 -3.6
           3 2020-02-01      4  10.7 -4.3
           3 2020-03-01      5   4.0  3.0

注意： 数据按 Serial_no 分组，date 是每月报告的数据（每月第一天）。 Index 列被设置为使得每个连续报告日期是系列中的连续数字。 每个组Serial_no 中报告的日期数不同。 每个组Serial_no 的报告日期间隔date也不同（它们在每个组中不以相同的日期开始或结束）。

问题： 时间序列中某些date日期没有报告的数据。请注意，在每个Serial_no组中都缺少一些日期。 我想在每个组中添加一行缺少的日期date，并将x和y列中的数据报告为“NaN”。

需要的数据框示例：

   Serial_no       date  Index       x       y
           1 2014-01-01      1     2.0     3.0
           1 2014-02-01      2     NaN     NaN
           1 2014-03-01      3     3.0     3.0
           1 2014-04-01      4     6.0     2.0
           2 2011-03-01      1     5.1     1.3
           2 2011-04-01      2     5.8     0.6
           2 2011-05-01      3     6.5    -0.1
           2 2011-06-01      4     NaN     NaN
           2 2011-07-01      5     3.0     5.0
           3 2019-10-01      1     7.9    -1.5
           3 2019-11-01      2     8.6    -2.2
           3 2019-12-01      3     NaN     NaN
           3 2020-01-01      4    10.0    -3.6
           3 2020-02-01      5    10.7    -4.3
           3 2020-03-01      6     4.0     3.0

我知道如何使用以下代码将带有缺失日期的行中的空单元格替换为NaN:

import pandas as pd
import numpy as np

df['x'].replace('', np.nan, inplace=True)
df['y'].replace('', np.nan, inplace=True)

我还知道如何使用以下代码重置索引，一旦缺失日期的行被插入：

df["Index"] = df.groupby("Serial_no",).cumcount('date')

然而，我不确定如何定位每个组中缺失的日期并插入这些（月报告）日期的行。感激不尽任何帮助。

- cowboykevin05

Answer 1

使用自定义函数在GroupBy.apply中结合DataFrame.asfreq，然后使用GroupBy.cumcount重新指定Index：

df['date'] = pd.to_datetime(df['date'])

df = (df.set_index('date')
        .groupby('Serial_no')
        .apply(lambda x: x.asfreq('MS'))
        .drop('Serial_no', axis=1))
df = df.reset_index()
df["Index"] = df.groupby("Serial_no").cumcount() + 1
print (df)
    Serial_no       date  Index     x    y
0           1 2014-01-01      1   2.0  3.0
1           1 2014-02-01      2   NaN  NaN
2           1 2014-03-01      3   3.0  3.0
3           1 2014-04-01      4   6.0  2.0
4           2 2011-03-01      1   5.1  1.3
5           2 2011-04-01      2   5.8  0.6
6           2 2011-05-01      3   6.5 -0.1
7           2 2011-06-01      4   NaN  NaN
8           2 2011-07-01      5   3.0  5.0
9           3 2019-10-01      1   7.9 -1.5
10          3 2019-11-01      2   8.6 -2.2
11          3 2019-12-01      3   NaN  NaN
12          3 2020-01-01      4  10.0 -3.6
13          3 2020-02-01      5  10.7 -4.3
14          3 2020-03-01      6   4.0  3.0

使用DataFrame.reindex的替代解决方案：

df['date'] = pd.to_datetime(df['date'])

f = lambda x: x.reindex(pd.date_range(x.index.min(), x.index.max(), freq='MS', name='date'))
df = df.set_index('date').groupby('Serial_no').apply(f).drop('Serial_no', axis=1)
df = df.reset_index()
df["Index"] = df.groupby("Serial_no").cumcount() + 1

- jezrael

---

嗨@jezrael，如果我想在每个组中添加相同日期范围，我该怎么做？ - Roy

@cph_sto - 你认为将 x.asfreq('MS') 改为 x.asfreq('MS', method='ffill') 好吗？ - jezrael

@jezrael 非常感谢您的及时帮助，一如既往的 :) - cph_sto

@cph_sto - 看起来数据没有排序，请在解决方案之前尝试 df = df.sort_values(['Serial_no','date'])。 - jezrael

@cph_sto - 不幸的是我不知道 :( 需要解决难点 - 调试并找到失败的组，然后查找组的日期时间是否有重复或者应该引发错误的内容。 - jezrael

Answer 2

一种选择是使用complete和pyjanitor，它抽象了暴露缺失行的过程：

# pip install pyjanitor
import pandas as pd
import janitor

# create a mapping that is applied across each Serial_no group
new_dates = {'date':lamba d: pd.date_range(d.min(), d.max(), freq='MS')}

(df
.complete(new_dates, by='Serial_no')
.assign(Index = lambda df: df.groupby('Serial_no')
                             .Index
                             .cumcount()
                             .add(1))
)
    Serial_no       date  Index     x    y
0           1 2014-01-01      1   2.0  3.0
1           1 2014-02-01      2   NaN  NaN
2           1 2014-03-01      3   3.0  3.0
3           1 2014-04-01      4   6.0  2.0
4           2 2011-03-01      1   5.1  1.3
5           2 2011-04-01      2   5.8  0.6
6           2 2011-05-01      3   6.5 -0.1
7           2 2011-06-01      4   NaN  NaN
8           2 2011-07-01      5   3.0  5.0
9           3 2019-10-01      1   7.9 -1.5
10          3 2019-11-01      2   8.6 -2.2
11          3 2019-12-01      3   NaN  NaN
12          3 2020-01-01      4  10.0 -3.6
13          3 2020-02-01      5  10.7 -4.3
14          3 2020-03-01      6   4.0  3.0

- sammywemmy