基于Pandas实现分组列均值计算与指定行数据替换

给定如下表格数据：

time1         x     y        z         GMT- 5             key     time2        a          b          c           GMT                   cut_off   time_diff new_column
1   1.674841e+09    -1.10   64.11   -1.33   2023-01-27 12:43:22 PM  0   1.674841e+09    2.96    606.270614  2.80    2023-01-27 12:43:24 PM  1.674841e+09    2.308100    NaN
2   1.674841e+09    -1.10   64.11   -1.33   2023-01-27 12:43:22 PM  0   1.674841e+09    2.96    584.696883  2.80    2023-01-27 12:43:26 PM  1.674841e+09    4.303636    NaN
3   1.674841e+09    -1.10   64.11   -1.33   2023-01-27 12:43:22 PM  0   1.674841e+09    2.96    615.295633  2.80    2023-01-27 12:43:28 PM  1.674841e+09    6.298568    NaN
4   1.674841e+09    -1.10   64.11   -1.33   2023-01-27 12:43:22 PM  0   1.674841e+09    2.96    587.050575  2.80    2023-01-27 12:43:30 PM  1.674841e+09    8.293623    NaN
5   1.674841e+09    -2.24   93.51   -2.36   2023-01-27 12:43:46 PM  0   1.674841e+09    2.96    584.700016  2.80    2023-01-27 12:43:46 PM  1.674841e+09    0.007554    0.007554
100 1.674842e+09    -1.24   84.73   -2.44   2023-01-27 12:49:07 PM  0   1.674843e+09    2.30    1024.363758 2.64    2023-01-27 01:13:11 PM  1.674843e+09    1444.068500 NaN
101 1.674842e+09    -1.24   84.73   -2.44   2023-01-27 12:49:07 PM  0   1.674843e+09    2.31    1011.438119 2.64    2023-01-27 01:13:13 PM  1.674843e+09    1446.063470 NaN
102 1.674842e+09    -1.24   84.73   -2.44   2023-01-27 12:49:07 PM  0   1.674843e+09    2.32    1005.181835 2.64    2023-01-27 01:13:15 PM  1.674843e+09    1448.058710 NaN
103 1.674842e+09    -1.24   84.73   -2.44   2023-01-27 12:49:07 PM  0   1.674843e+09    2.34    989.515657  2.64    2023-01-27 01:13:17 PM  1.674843e+09    1450.053643 NaN
104 1.674842e+09    -1.24   84.73   -2.44   2023-01-27 12:49:07 PM  0   1.674843e+09    2.34    1016.183097 2.64    2023-01-27 01:13:19 PM  1.674843e+09    1452.048679 NaN
105 1.674842e+09    -1.57   80.04   -1.96   2023-01-27 12:49:06 PM  0   1.674842e+09    2.02    1652.185708 2.88    2023-01-27 12:49:06 PM  1.674842e+09    0.001867    0.001867

需完成以下操作：

• 筛选出new_column列无NaN值的行（行5、行105）；
• 分别计算行1-5、行100-105中x、y、z列的平均值；
• 将平均值替换到对应目标行的x、y、z列；
• 最终保留替换后的目标行。

使用Python的pandas库可以高效实现需求，代码如下：

import pandas as pd

# 构造原始数据
data = {
    "time1": [1.674841e+09]*5 + [1.674842e+09]*6,
    "x": [-1.10, -1.10, -1.10, -1.10, -2.24, -1.24, -1.24, -1.24, -1.24, -1.24, -1.57],
    "y": [64.11, 64.11, 64.11, 64.11, 93.51, 84.73, 84.73, 84.73, 84.73, 84.73, 80.04],
    "z": [-1.33, -1.33, -1.33, -1.33, -2.36, -2.44, -2.44, -2.44, -2.44, -2.44, -1.96],
    "GMT- 5": ["2023-01-27 12:43:22 PM"]*4 + ["2023-01-27 12:43:46 PM"] + ["2023-01-27 12:49:07 PM"]*5 + ["2023-01-27 12:49:06 PM"],
    "key": [0]*11,
    "time2": [1.674841e+09]*5 + [1.674843e+09]*5 + [1.674842e+09],
    "a": [2.96]*5 + [2.30,2.31,2.32,2.34,2.34,2.02],
    "b": [606.270614,584.696883,615.295633,587.050575,584.700016,1024.363758,1011.438119,1005.181835,989.515657,1016.183097,1652.185708],
    "c": [2.80]*5 + [2.64]*5 + [2.88],
    "GMT": ["2023-01-27 12:43:24 PM","2023-01-27 12:43:26 PM","2023-01-27 12:43:28 PM","2023-01-27 12:43:30 PM","2023-01-27 12:43:46 PM","2023-01-27 01:13:11 PM","2023-01-27 01:13:13 PM","2023-01-27 01:13:15 PM","2023-01-27 01:13:17 PM","2023-01-27 01:13:19 PM","2023-01-27 12:49:06 PM"],
    "cut_off": [1.674841e+09]*5 + [1.674843e+09]*5 + [1.674842e+09],
    "time_diff": [2.308100,4.303636,6.298568,8.293623,0.007554,1444.068500,1446.063470,1448.058710,1450.053643,1452.048679,0.001867],
    "new_column": [float('nan')]*4 + [0.007554] + [float('nan')]*5 + [0.001867]
}

# 创建DataFrame并设置行索引
df = pd.DataFrame(data, index=[1,2,3,4,5,100,101,102,103,104,105])

# 定义需要处理的分组
groups = [df.loc[1:5], df.loc[100:105]]

result_rows = []
for group in groups:
    # 计算分组内x、y、z的平均值
    avg_vals = group[['x','y','z']].mean()
    # 筛选分组中new_column非空的行
    target_row = group[group['new_column'].notna()].copy()
    # 替换目标行的x、y、z值
    target_row[['x','y','z']] = avg_vals
    result_rows.append(target_row)

# 合并结果并输出
final_df = pd.concat(result_rows)
print(final_df.to_string())

运行结果 #

执行代码后，输出与预期一致：

time1     x     y      z         GMT- 5  key       time2     a           b     c                   GMT       cut_off  time_diff  new_column
5    1.67484e+09 -1.328  69.99 -1.536 2023-01-27 12:43:46 PM    0  1.67484e+09  2.96   584.700016  2.80 2023-01-27 12:43:46 PM  1.67484e+09   0.007554     0.007554
105  1.67484e+09 -1.295  69.82 -2.360 2023-01-27 12:49:06 PM    0  1.67484e+09  2.02  1652.185708  2.88 2023-01-27 12:49:06 PM  1.67484e+09   0.001867     0.001867

内容的提问来源于stack exchange，提问作者user20777609