基于索引匹配与时钟速度近似匹配合并DataFrame并计算平均空闲功耗

步骤说明 #

要实现基于Chips/Cores精确匹配、Clock Speed最接近匹配后取平均功耗的需求，可按以下步骤操作：

1. 关联两个DataFrame，匹配对应芯片和核心数字段；
2. 计算每行时钟频率的绝对差值；
3. 对每个服务器条目，筛选出差值最小的匹配行，计算这些行的平均空闲功耗；
4. 将结果合并回原DataFrame。

完整代码 #

import pandas as pd

# 初始化两个DataFrame
data1 = {'Server Name': ['PhysicalWindows1', 'PhysicalWindows2', 'PhysicalLinux1', 'PhysicalLinux2'],
        'Chips1': [1, 1, 2, 2], 
        'pCpu Cores': [8, 8, 32, 32],
        'Cpu Clock': [3400, 3400, 2600, 2600]}
df1 = pd.DataFrame(data1)

data2 = {'Chips': [1, 1, 1, 2, 2],
        'Cores': [8, 8, 8, 11, 11],
        'Clock Speed': [3300, 3500, 2900, 900, 100], 
        'Avg Watts Idle': [58.5, 63, 25, 83.8, 65]}
df2 = pd.DataFrame(data2)

# 关联两个DataFrame，匹配Chips和Cores字段
merged = pd.merge(df1, df2, left_on=['Chips1', 'pCpu Cores'], right_on=['Chips', 'Cores'], how='left')

# 计算时钟频率的绝对差值
merged['clock_diff'] = abs(merged['Cpu Clock'] - merged['Clock Speed'])

# 定义函数：对每组服务器，筛选出差值最小的行并计算平均功耗
def calc_avg_idle_watts(group):
    min_diff = group['clock_diff'].min()
    # 仅保留差值等于最小值的行
    valid_rows = group[group['clock_diff'] == min_diff]
    return valid_rows['Avg Watts Idle'].mean() if not valid_rows.empty else float('nan')

# 按服务器名称分组计算平均功耗
avg_watts_result = merged.groupby('Server Name').apply(calc_avg_idle_watts).reset_index(name='Avg Watts Idle')

# 合并回原DataFrame得到最终结果
final_df = pd.merge(df1, avg_watts_result, on='Server Name')

# 打印结果
print(final_df)

输出结果 #

Server Name  Chips1  pCpu Cores  Cpu Clock  Avg Watts Idle
0  PhysicalWindows1       1           8       3400           60.75
1  PhysicalWindows2       1           8       3400           60.75
2  PhysicalLinux1         2          32       2600             NaN
3  PhysicalLinux2         2          32       2600             NaN

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