python - 基于组和前一行 Pandas 的前向填充(ffill)

Question

我有一个大型数据框(400,000 多行)，如下所示:

data = np.array([
          [1949, '01/01/2018', np.nan, 17,     '30/11/2017'],
          [1949, '01/01/2018', np.nan, 19,      np.nan],
          [1811, '01/01/2018',     16, np.nan, '31/11/2017'],
          [1949, '01/01/2018',     15, 21,     '01/12/2017'],
          [1949, '01/01/2018', np.nan, 20,      np.nan],
          [3212, '01/01/2018',     21, 17,     '31/11/2017']
         ])
columns = ['id', 'ReceivedDate', 'PropertyType', 'MeterType', 'VisitDate']
pd.DataFrame(data, columns=columns)

结果 df:

     id     ReceivedDate    PropertyType    MeterType   VisitDate
0   1949    01/01/2018       NaN              17       30/11/2017
1   1949    01/01/2018       NaN              19       NaN
2   1811    01/01/2018       16              NaN       31/11/2017
3   1949    01/01/2018       15               21       01/12/2017
4   1949    01/01/2018       NaN              20       NaN
5   3212    01/01/2018       21               17       31/11/2017

我想根据 groupby(id 和接收日期)转发填充 - 仅当它们在索引中按顺序排在下一个时(即仅转发填充索引位置 1 和 4)。

我想有一个列说明是否应该根据标准填写，但我如何检查上面的行？

(我计划按照以下答案使用解决方案:pandas fill forward performance issue

df.isnull().astype(int)).groupby(level=0).cumsum().applymap(lambda x: None if x == 0 else 1)

因为 x = df.groupby(['id','ReceivedDate']).ffill() 非常慢。)

期望的 df:

     id     ReceivedDate    PropertyType    MeterType   VisitDate
0   1949    01/01/2018       NaN              17       30/11/2017
1   1949    01/01/2018       NaN              19       30/11/2017
2   1811    01/01/2018       16              NaN       31/11/2017
3   1949    01/01/2018       15               21       01/12/2017
4   1949    01/01/2018       15               20       01/12/2017
5   3212    01/01/2018       21               17       31/11/2017

Answer 1

groupby 和 ffill with limit=1

df.groupby(['id', 'ReceivedDate']).ffill(limit=1)

     id ReceivedDate PropertyType MeterType   VisitDate
0  1949   01/01/2018          NaN        17  30/11/2017
1  1949   01/01/2018          NaN        19  30/11/2017
2  1811   01/01/2018           16        18  31/11/2017
3  1949   01/01/2018           15        21  01/12/2017
4  1949   01/01/2018           15        20  01/12/2017
5  3212   01/01/2018           21        17  31/11/2017

---

groupby 与 masking 和 shift

尝试用 groupby、mask 和 shift 填充 NaN -

i = df[['id', 'ReceivedDate']]
j = i.ne(i.shift().values).any(1).cumsum()

df.mask(df.isnull().astype(int).groupby(j).cumsum().eq(1), df.groupby(j).shift())

或者，

df.where(df.isnull().astype(int).groupby(j).cumsum().ne(1), df.groupby(j).shift())

     id ReceivedDate PropertyType MeterType   VisitDate
0  1949   01/01/2018          NaN        17  30/11/2017
1  1949   01/01/2018          NaN        19  30/11/2017
2  1811   01/01/2018           16        18  31/11/2017
3  1949   01/01/2018           15        21  01/12/2017
4  1949   01/01/2018           15        20  01/12/2017
5  3212   01/01/2018           21        17  31/11/2017