如何用Python Pandas按SP、State、Year计算半对角线平均值？

给定如下Pandas DataFrame，需基于SP、State和Year字段，按照半对角线规则计算每行的平均值：

• 当SP=0时，取列名2的第1行、列名3的第2行……列名18的第17行，计算这些值的平均值
• 当SP=1时，取列名2的第0行、列名3的第1行……列名18的第16行，计算平均值
  以此类推，每个SP=i对应的是列名j ≥ i+1且**行索引 = j - (i+1)**的元素集合的平均值。

原始DataFrame代码：

import pandas as pd

data = {
    'SP': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17],
    'State': ['cm']*18,
    'Year': [2020]*18,
    1: [0.01, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None],
    2: [0.023, 0.32, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None],
    3: [0.015, 0.5, 0.6, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None],
    4: [0.021, 0.68, 1.339, 1.998, None, None, None, None, None, None, None, None, None, None, None, None, None, None],
    5: [0.0235, 0.86, 1.6965, 2.533, 3.3695, None, None, None, None, None, None, None, None, None, None, None, None, None],
    6: [0.026, 1.04, 2.054, 3.068, 4.082, 5.096, None, None, None, None, None, None, None, None, None, None, None, None],
    7: [0.0285, 1.22, 2.4115, 3.603, 4.7945, 5.986, 7.1775, None, None, None, None, None, None, None, None, None, None, None],
    8: [0.031, 1.4, 2.769, 4.138, 5.507, 6.876, 8.245, 9.614, None, None, None, None, None, None, None, None, None, None],
    9: [0.0335, 1.58, 3.1265, 4.673, 6.2195, 7.766, 9.3125, 10.859, 12.4055, None, None, None, None, None, None, None, None, None],
    10: [0.036, 1.76, 3.484, 5.208, 6.932, 8.656, 10.38, 12.104, 13.828, 15.552, None, None, None, None, None, None, None, None],
    11: [0.0385, 1.94, 3.8415, 5.743, 7.6445, 9.546, 11.4475, 13.349, 15.2505, 17.152, 19.0535, None, None, None, None, None, None, None],
    12: [0.041, 2.12, 4.199, 6.278, 8.357, 10.436, 12.515, 14.594, 16.673, 18.752, 20.831, 22.91, None, None, None, None, None, None],
    13: [0.0435, 2.3, 4.5565, 6.813, 9.0695, 11.326, 13.5825, 15.839, 18.0955, 20.352, 22.6085, 24.865, 27.1215, None, None, None, None, None],
    14: [0.046, 2.48, 4.914, 7.348, 9.782, 12.216, 14.65, 17.084, 19.518, 21.952, 24.386, 26.82, 29.254, 31.688, None, None, None, None],
    15: [0.0485, 2.66, 5.2715, 7.883, 10.4945, 13.106, 15.7175, 18.329, 20.9405, 23.552, 26.1635, 28.775, 31.3865, 33.998, 36.6095, None, None, None],
    16: [0.051, 2.84, 5.629, 8.418, 11.207, 13.996, 16.785, 19.574, 22.363, 25.152, 27.941, 30.73, 33.519, 36.308, 39.097, 41.886, None, None],
    17: [0.0535, 3.02, 5.9865, 8.953, 11.9195, 14.886, 17.8525, 20.819, 23.7855, 26.752, 29.7185, 32.685, 35.6515, 38.618, 41.5845, 44.551, 47.5175, None],
    18: [0.056, 3.2, 6.344, 9.488, 12.632, 15.776, 18.92, 22.064, 25.208, 28.352, 31.496, 34.64, 37.784, 40.928, 44.072, 47.216, 50.36, 53.504]
}

df = pd.DataFrame(data)

方案1：向量化高效实现（推荐） #

利用Pandas的索引切片和对角线提取，避免逐行循环，效率更高：

# 提取所有数值列（列名为整数1到18）
numeric_cols = [col for col in df.columns if isinstance(col, int)]
numeric_df = df[numeric_cols]

# 对每个SP对应的行，计算半对角线平均值
df['Diag_Mean'] = [
    # 切片：行索引从0到17-i，列索引从i到17，提取对角线元素后求平均
    numeric_df.iloc[:18-i, i:].values.diagonal().mean()
    for i in range(len(df))
]

方案2：逐行循环实现（直观易懂） #

如果需要更直观的逻辑，可以用apply逐行处理：

def get_diag_average(row):
    sp_val = row['SP']
    values = []
    # 遍历列名2到18
    for col_name in range(2, 19):
        # 计算目标行索引：列名 - (SP值 +1)
        target_row = col_name - (sp_val + 1)
        # 确保行索引在有效范围内
        if 0 <= target_row < len(df):
            val = df.loc[target_row, col_name]
            if pd.notna(val):
                values.append(val)
    # 返回平均值，若无有效值则返回None
    return pd.Series(values).mean() if values else None

# 应用函数到每一行
df['Diag_Mean'] = df.apply(get_diag_average, axis=1)

结果说明 #

执行后，df会新增一列Diag_Mean，存储每个SP对应的半对角线平均值。例如：

• SP=0的平均值为(0.32 + 0.6 + 1.998 + ... + 53.504)/17
• SP=1的平均值为(0.023 + 1.339 + 3.3695 + ... + 50.36)/16

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