Pandas DataFrame两类样本差值计算问题求助

操作1：按Book_No分组减去control样本的均值 #

需求 #

针对Smell、Taste、Odour、Volatility列，按Book_No分组计算control样本的各列均值，再将同Book_No的Sample A、Sample B、Sample C的对应列值减去该均值，control样本保持原值。

原始DataFrame #

Book_No  Replicate    Sample  Smell  Taste  Odour  Volatility                    Notes
0   12, 43          1   control    0.3   10.0     71           1                      NaN
1   12, 43          2   control    0.4    8.0     63           3                      NaN
2   12, 43          3   control    0.1    3.0     22           2                      NaN
3   19, 21          1   control    1.1    2.0     80           3                      NaN
4   19, 21          2   control    0.4    8.0      0           4                      NaN
5   19, 21          3   control    0.9    3.0      4           6                      NaN
6   19, 21          4   control    2.1    6.0     50           4                      NaN
7   11, 22          1   control    3.4    3.0     23           3                      NaN
8   12, 43          1  Sample A    1.1   11.2     75           7                      NaN
9   12, 43          2  Sample A    1.4    3.3     87           6  Temperature was too hot
10  12, 43          3  Sample A    0.7    7.4     91           5                      NaN
11  19, 21          1  Sample B    2.1    3.2     99           7                      NaN
12  19, 21          2  Sample B    2.2   11.3     76           8                      NaN
13  19, 21          3  Sample B    1.9    9.3     89           9     sample spilt by user
14  19, 21          1  Sample C    3.2    4.0    112          10                      NaN
15  19, 21          2  Sample C    2.1    5.0     96          15                      NaN
16  19, 21          3  Sample C    2.7    7.0    105          13             Was too cold
17  11, 22          1  Sample C    2.4    3.0    121          19                      NaN

错误尝试分析 #

你之前的代码错误在于分组时包含了Sample列，导致计算的是每个Book_No+Sample的均值，而非仅control样本的分组均值；掩码逻辑也无法正确匹配control数据。

正确解决方案 #

import pandas as pd

# 加载原始DataFrame（若已存在可跳过此步）
df = pd.DataFrame({
    'Book_No': ['12, 43', '12, 43', '12, 43', '19, 21', '19, 21', '19, 21', '19, 21', '11, 22',
                '12, 43', '12, 43', '12, 43', '19, 21', '19, 21', '19, 21', '19, 21', '19, 21',
                '19, 21', '11, 22'],
    'Replicate': [1,2,3,1,2,3,4,1,1,2,3,1,2,3,1,2,3,1],
    'Sample': ['control','control','control','control','control','control','control','control',
               'Sample A','Sample A','Sample A','Sample B','Sample B','Sample B','Sample C','Sample C','Sample C','Sample C'],
    'Smell': [0.3,0.4,0.1,1.1,0.4,0.9,2.1,3.4,1.1,1.4,0.7,2.1,2.2,1.9,3.2,2.1,2.7,2.4],
    'Taste': [10.0,8.0,3.0,2.0,8.0,3.0,6.0,3.0,11.2,3.3,7.4,3.2,11.3,9.3,4.0,5.0,7.0,3.0],
    'Odour': [71,63,22,80,0,4,50,23,75,87,91,99,76,89,112,96,105,121],
    'Volatility': [1,3,2,3,4,6,4,3,7,6,5,7,8,9,10,15,13,19],
    'Notes': [pd.NA,pd.NA,pd.NA,pd.NA,pd.NA,pd.NA,pd.NA,pd.NA,pd.NA,'Temperature was too hot',pd.NA,pd.NA,pd.NA,'sample spilt by user',pd.NA,pd.NA,'Was too cold',pd.NA]
})

# 1. 计算control样本的Book_No分组均值
control_means = df[df['Sample'] == 'control'].groupby('Book_No')[['Smell', 'Taste', 'Odour', 'Volatility']].mean().add_suffix('_mean')

# 2. 合并均值到原DataFrame
df_merged = df.merge(control_means, on='Book_No', how='left')

# 3. 对非control样本执行减法
target_cols = ['Smell', 'Taste', 'Odour', 'Volatility']
for col in target_cols:
    df_merged.loc[df_merged['Sample'] != 'control', col] = df_merged[col] - df_merged[f'{col}_mean']

# 4. 移除临时列并恢复原列顺序
df_result1 = df_merged.drop([f'{col}_mean' for col in target_cols], axis=1)[df.columns]

print(df_result1.round(4))

预期结果 #

Book_No  Replicate    Sample     Smell  Taste  Odour  Volatility                    Notes
0   12, 43          1   control  0.300000  10.00   71.0        1.00                      NaN
1   12, 43          2   control  0.400000   8.00   63.0        3.00                      NaN
2   12, 43          3   control  0.100000   3.00   22.0        2.00                      NaN
3   19, 21          1   control  1.100000   2.00   80.0        3.00                      NaN
4   19, 21          2   control  0.400000   8.00    0.0        4.00                      NaN
5   19, 21          3   control  0.900000   3.00    4.0        6.00                      NaN
6   19, 21          4   control  2.100000   6.00   50.0        4.00                      NaN
7   11, 22          1   control  3.400000   3.00   23.0        3.00                      NaN
8   12, 43          1  Sample A  0.833333   4.20   23.0        5.00                      NaN
9   12, 43          2  Sample A  1.133333  -3.70   35.0        4.00  Temperature was too hot
10  12, 43          3  Sample A  0.433333   0.40   39.0        3.00                      NaN
11  19, 21          1  Sample B  0.975000  -1.55   65.5        2.75                      NaN
12  19, 21          2  Sample B  1.075000   6.55   42.5        3.75                      NaN
13  19, 21          3  Sample B  0.775000   4.55   55.5        4.75     sample spilt by user
14  19, 21          1  Sample C -0.200000   1.00   89.0        7.00                      NaN
15  19, 21          2  Sample C -1.300000   2.00   73.0       12.00                      NaN
16  19, 21          3  Sample C -0.700000   4.00   82.0       10.00             Was too cold
17  11, 22          1  Sample C -1.000000   0.00   98.0       16.00                      NaN

操作2：按Book_No+Replicate匹配减去control对应值 #

需求 #

当Book_No和Replicate同时匹配时，将Sample A、Sample B、Sample C的对应列值减去control的对应值，control样本保持原值。

正确解决方案 #

# 1. 提取control数据，以Book_No和Replicate为索引
control_data = df[df['Sample'] == 'control'].set_index(['Book_No', 'Replicate'])[['Smell', 'Taste', 'Odour', 'Volatility']].add_suffix('_control')

# 2. 合并control数据到原DataFrame
df_merged2 = df.merge(control_data, left_on=['Book_No', 'Replicate'], right_index=True, how='left')

# 3. 对非control样本执行减法
for col in target_cols:
    df_merged2.loc[df_merged2['Sample'] != 'control', col] = df_merged2[col] - df_merged2[f'{col}_control']

# 4. 移除临时列并恢复原列顺序
df_result2 = df_merged2.drop([f'{col}_control' for col in target_cols], axis=1)[df.columns]

print(df_result2)

预期结果 #

Book_No  Replicate    Sample  Smell  Taste  Odour  Volatility                    Notes
0   12, 43          1   control    0.3   10.0     71           1                      NaN
1   12, 43          2   control    0.4    8.0     63           3                      NaN
2   12, 43          3   control    0.1    3.0     22           2                      NaN
3   19, 21          1   control    1.1    2.0     80           3                      NaN
4   19, 21          2   control    0.4    8.0      0           4                      NaN
5   19, 21          3   control    0.9    3.0      4           6                      NaN
6   19, 21          4   control    2.1    6.0     50           4                      NaN
7   11, 22          1   control    3.4    3.0     23           3                      NaN
8   12, 43          1  Sample A    0.8    1.2      4           6                      NaN
9   12, 43          2  Sample A    1.0   -4.7     24           3  Temperature was too hot
10  12, 43          3  Sample A    0.6    4.4     69           3                      NaN
11  19, 21          1  Sample B    1.0    1.2     19           4                      NaN
12  19, 21          2  Sample B    1.8    3.3     76           4                      NaN
13  19, 21          3  Sample B    1.0    6.3     85           3     sample spilt by user
14  19, 21          1  Sample C    2.1    2.0     32           7                      NaN
15  19, 21          2  Sample C    1.7   -3.0     96          11                      NaN
16  19, 21          3  Sample C    1.8    4.0    101           7             Was too cold
17  11, 22          1  Sample C   -1.0    0.0     98          16                      NaN

内容的提问来源于stack exchange，提问作者Steve..Johnson