Pandas DataFrame按S分组判断各X列最大值是否大于O=1对应值

实现代码 #

import pandas as pd

# 构造示例DataFrame，你可以替换为自己读取数据的逻辑
data = {
    'S': [100,100,100,100,150,150,150,150,200,200,200,200,250,250,250,250],
    'O': [1,3,7,9,1,3,7,9,1,3,7,9,1,3,7,9],
    'X1': [0.107455,0.375586,0.167457,0.835885,0.997843,0.904277,0.907843,0.33937,0.054206,0.233063,0.87344,0.922502,0.016137,0.402824,0.220363,0.37158],
    'X2': [0.446583,0.314810,0.555283,0.213843,0.837116,0.276030,0.387135,0.990797,0.105728,0.972236,0.395052,0.471666,0.478540,0.466885,0.134676,0.429023],
    'X3': [0.220452,0.417982,0.335208,0.376132,0.509243,0.309795,0.506080,0.803394,0.220876,0.323389,0.508753,0.372094,0.118725,0.953571,0.384890,0.893135],
    'X4': [0.105891,0.974419,0.152041,0.605004,0.993932,0.623847,0.685169,0.385693,0.399901,0.322506,0.962736,0.380467,0.815293,0.133401,0.931463,0.297627]
}
df = pd.DataFrame(data)

# 提取O=1的基准值
base_df = df[df['O'] == 1].set_index('S')[['X1','X2','X3','X4']]
# 计算O为3/7/9时各S分组的X列最大值
max_df = df[df['O'].isin([3,7,9])].groupby('S')[['X1','X2','X3','X4']].max()
# 比较得到1/0结果
compare_res = (max_df > base_df).astype(int)
# 转换为要求的长表格式
final_res = compare_res.reset_index().melt(id_vars='S', var_name='X列', value_name='结果')
# 按X列排序匹配预期输出顺序
final_res = final_res.sort_values('X列').reset_index(drop=True)
print(final_res)

运行输出结果 #

S X列  结果
0   100  X1   1
1   150  X1   0
2   200  X1   1
3   250  X1   1
4   100  X2   1
5   150  X2   1
6   200  X2   1
7   250  X2   0
8   100  X3   1
9   150  X3   1
10  200  X3   1
11  250  X3   1
12  100  X4   1
13  150  X4   0
14  200  X4   1
15  250  X4   1

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