python - pandas:根据具有重复值的另一列填充 nan 值

Question

我有两个要合并的表，df_a 和 df_b，但它们都缺少一些值。合并进行得很好，但是我正在尝试加快合并后填充 NaN 值的过程。

数据:

df_a = pd.DataFrame(data={
    'id': [1, 2, 3, 1, 6, 5, 3],
    'name': [np.nan, 'two', 'three', 'one', np.nan, np.nan, np.nan],
    'length': ['l1', 'l2', 'l3', 'l1', 'l6', np.nan, np.nan],
    'Q1': [11, 22, 33, 11, 66, 55, 33],
    'Q2': [111, 222, 333, 111, 666, 555, 333]})

df_b = pd.DataFrame(data={
    'id': [5, 2, 4, 1, 3, 1, 7, 3],
    'name': ['five', 'two', 'four', 'one_extra', 'three', 'one', 'seven', 'three'],
    'length': ['l5', 'l2', 'l4', 'l1', 'l3', 'l1', 'l7', 'l3_extra'],
    'Q1': [2055, 2022, 2044, 2011, 2033, 2011, 2077, 2033],
    'Q2': [2055, 2022, 2044, 2011, 2033, 2011, 2077, 2033]})

df_m = df_a.merge(df_b, on=['id', 'name', 'length'], how='outer',
                  suffixes=['_a', '_b'])
Output:

       id   name length  Q1   Q2
    0   1    NaN     l1  11  111
    1   2    two     l2  22  222
    2   3  three     l3  33  333
    3   1    one     l1  11  111
    4   6    NaN     l6  66  666
    5   5    NaN    NaN  55  555
    6   3    NaN    NaN  33  333

       id       name    length    Q1    Q2
    0   5       five        l5  2055  2055
    1   2        two        l2  2022  2022
    2   4       four        l4  2044  2044
    3   1  one_extra        l1  2011  2011
    4   3      three        l3  2033  2033
    5   1        one        l1  2011  2011
    6   7      seven        l7  2077  2077
    7   3      three  l3_extra  2033  2033

        id       name    length  Q1_a   Q2_a    Q1_b    Q2_b
    0    1        NaN        l1  11.0  111.0     NaN     NaN
    3    1        one        l1  11.0  111.0  2011.0  2011.0
    9    1  one_extra        l1   NaN    NaN  2011.0  2011.0
    1    2        two        l2  22.0  222.0  2022.0  2022.0
    2    3      three        l3  33.0  333.0  2033.0  2033.0
    6    3        NaN       NaN  33.0  333.0     NaN     NaN
    11   3      three  l3_extra   NaN    NaN  2033.0  2033.0
    8    4       four        l4   NaN    NaN  2044.0  2044.0
    5    5        NaN       NaN  55.0  555.0     NaN     NaN
    7    5       five        l5   NaN    NaN  2055.0  2055.0
    4    6        NaN        l6  66.0  666.0     NaN     NaN
    10   7      seven        l7   NaN    NaN  2077.0  2077.0

端点

我正在尝试让 df_m 进入一种状态，其中任何 NaN 值都根据相应 id 列中的值进行填充.每当有重复的 id 时，我都想保留这两个值，这样我就可以得到以下 df:

   id       name    length  Q1_a   Q2_a    Q1_b    Q2_b
0   1        one        l1  11.0  111.0  2011.0  2011.0
1   1  one_extra        l1  11.0  111.0  2011.0  2011.0
2   2        two        l2  22.0  222.0  2022.0  2022.0
3   3      three        l3  33.0  333.0  2033.0  2033.0
4   3      three  l3_extra  33.0  333.0  2033.0  2033.0
5   4       four        l4   NaN    NaN  2044.0  2044.0
6   5       five        l5  55.0  555.0  2055.0  2055.0
7   6        NaN        l6  66.0  666.0     NaN     NaN
8   7      seven        l7   NaN    NaN  2077.0  2077.0

请注意，有一些 NaN 值，其中一个表中有一个条目，而另一个表中没有，例如df_a 中没有 id=4 的条目，因此在 Q1_a 和 Q2_a< 处有 NaN/.

到目前为止我的解决方案

下面的工作，但我想向量化这段代码，因为它只需要 4 秒多的时间，只有 ~200 行:

dupe_ids = pd.value_counts(df_m.id)[
    pd.value_counts(df_m.id) > 1].index.values

for dupe_id in dupe_ids:
    df_m.loc[df_m.id == dupe_id] = df_m.loc[df_m.id == dupe_id].ffill().bfill()

df_m = df_m.drop_duplicates().sort_values(by='id').reset_index(drop=True)

我尝试将代码矢量化，如下所示，但结果很奇怪(参见 id=4)。

df_m.sort_values(by='id').groupby(
    'id').ffill().bfill().drop_duplicates().reset_index(drop=True)

Output:

       id       name    length  Q1_a   Q2_a    Q1_b    Q2_b
    0   1        one        l1  11.0  111.0  2011.0  2011.0
    1   1  one_extra        l1  11.0  111.0  2011.0  2011.0
    2   2        two        l2  22.0  222.0  2022.0  2022.0
    3   3      three        l3  33.0  333.0  2033.0  2033.0
    4   3      three  l3_extra  33.0  333.0  2033.0  2033.0
    5   4       four        l4  55.0  555.0  2044.0  2044.0
    6   5       five        l5  55.0  555.0  2055.0  2055.0
    7   6      seven        l6  66.0  666.0  2077.0  2077.0
    8   7      seven        l7   NaN    NaN  2077.0  2077.0

我实际尝试合并的两个表要复杂得多，因此上面的示例导致的行为比在那里看到的要奇怪得多。有超过 100 列，不同的列可以在不同的地方有 NaN 值。

如有任何帮助，我们将不胜感激!

Answer 1

问题是您需要按组执行 ffill + bfill。您当前的矢量化尝试仅按组执行 ffill。

res = df_m.sort_values('id')\
          .groupby('id').apply(lambda x: x.ffill().bfill())\
          .drop_duplicates()

print(res)

    id       name    length  Q1_a   Q2_a    Q1_b    Q2_b
0    1        one        l1  11.0  111.0  2011.0  2011.0
9    1  one_extra        l1  11.0  111.0  2011.0  2011.0
1    2        two        l2  22.0  222.0  2022.0  2022.0
2    3      three        l3  33.0  333.0  2033.0  2033.0
11   3      three  l3_extra  33.0  333.0  2033.0  2033.0
8    4       four        l4   NaN    NaN  2044.0  2044.0
5    5       five        l5  55.0  555.0  2055.0  2055.0
4    6        NaN        l6  66.0  666.0     NaN     NaN
10   7      seven        l7   NaN    NaN  2077.0  2077.0