将大小大于1的组替换为数据帧中的平均值

因此,我想按某些列分组,对于每个大小大于1的组,取其余列中的平均值(如果所有值都是nan,那么这应该是nan如果不是,我希望在平均值计算中删除nan,这是默认行为)。然后我要删除多余的行。下面的代码用于:

import pandas as pd
import numpy as np

df = pd.DataFrame(
    [[1, np.nan, 100, 63], [2, np.nan, 101, 63], [2, 12, 102, 63],
     [2, 14, 102, 63], [2, 14, 102, 64], [1, np.nan, 200, 63]],
    columns=['group', 'value', 'value2', 'dummy'])
print(df)
df = df.set_index(['group', 'dummy'])
groupby = df.groupby(['group', 'dummy'])
idx = groupby.size() > 1
df_groups_to_process = df.loc[idx]
# the code below would calculate the mean for all groups, huge performance hit
# df.loc[idx, ['value', 'value2']] = \
#     groupby[['value', 'value2']].transform('mean')[idx].values
df.loc[idx, ['value', 'value2']] = \
    df_groups_to_process.groupby(['group', 'dummy'])[
        ['value', 'value2']].transform('mean').values
print(df)
df = df.groupby(['group', 'dummy']).first()
print(df)

印刷品:

   group  value  value2  dummy
0      1    NaN     100     63
1      2    NaN     101     63
2      2   12.0     102     63
3      2   14.0     102     63
4      2   14.0     102     64
5      1    NaN     200     63
sys:1: PerformanceWarning: indexing past lexsort depth may impact performance.
             value      value2
group dummy                   
1     63       NaN  150.000000
2     63      13.0  101.666667
      63      13.0  101.666667
      63      13.0  101.666667
      64      14.0  102.000000
1     63       NaN  150.000000
             value      value2
group dummy                   
1     63       NaN  150.000000
2     63      13.0  101.666667
      64      14.0  102.000000

Process finished with exit code 0

但是,有几件事可以改进:

• df_groups_to_process = df.loc[idx] 不确定这是否会创建一个副本,但由于我的真实数据是巨大的,我希望完全避免这种情况,并将其合并到 df.loc[idx, ['value', 'value2']] = ...
• 我在最后重复groupby操作以首先调用-如何避免这种情况?
• 奖金:奖金 sys:1: PerformanceWarning: indexing past lexsort depth may impact performance.