在Pandas中统计各列最大值对应行的出现次数

问题描述 #

1. 原始DataFrame需求 #

我有如下DataFrame：

import pandas as pd

data = {
  "a": [0.02, 0.04, -0.1,-0.02],
  "b": [0.04, -0.1, -0.02,0.01],
  "c": [0.01, 0.3, 0.02,0.02],
  "d": [-0.07,0.02,-0.01,0.0]
}

df = pd.DataFrame(data)

希望得到统计各列成为每行最大值的次数的表格，结果如下：

其中c的次数为3，是因为在4行（对应月份）中有3行c的数值是所有列里最大的。

2. 补充疑问 #

如果将DataFrame改为如下结构，获取结果的方式是否需要调整？创建该DataFrame的代码如下：

a = [0.02, 0.04, -0.1,-0.02]
b = [0.04, -0.1, -0.02,0.01]
c = [0.01, 0.3, 0.02,0.02]
d = [-0.07,0.02,-0.01,0.0]

total = [a,b,c,d]
total2 = ['a','b','c','d']

df_final = pd.DataFrame(columns=list(range(4)), index=['a','b','c','d'])
i=0

for l in total2:
    df_final.loc[l] = total[i]
    i += 1
    
print(df_final)

解决方案 #

针对原始DataFrame的处理 #

要统计每列成为每行最大值的次数，按以下步骤操作即可：

1. 找出每行最大值对应的列名
2. 统计每个列名的出现次数
3. 补充出现次数为0的列，再整理成指定格式的表格

代码实现：

import pandas as pd

data = {
  "a": [0.02, 0.04, -0.1,-0.02],
  "b": [0.04, -0.1, -0.02,0.01],
  "c": [0.01, 0.3, 0.02,0.02],
  "d": [-0.07,0.02,-0.01,0.0]
}

df = pd.DataFrame(data)

# 统计每行最大值对应列名的出现次数
max_counts = df.idxmax(axis=1).value_counts()
# 补充所有列，确保次数为0的列被包含
all_columns = df.columns
result = max_counts.reindex(all_columns, fill_value=0)
# 转换为目标格式并排序
result_df = result.reset_index()
result_df.columns = ['dataset', 'no of being max a column']
result_df = result_df.sort_values(by='no of being max a column', ascending=False).reset_index(drop=True)

print(result_df)

针对修改后DataFrame的处理 #

修改后的df_final是原始DataFrame的转置结构——行对应原数据的列，列对应原数据的行。此时仅需调整idxmax的轴参数即可，核心逻辑不变：

代码实现：

import pandas as pd

a = [0.02, 0.04, -0.1,-0.02]
b = [0.04, -0.1, -0.02,0.01]
c = [0.01, 0.3, 0.02,0.02]
d = [-0.07,0.02,-0.01,0.0]

total = [a,b,c,d]
total2 = ['a','b','c','d']

df_final = pd.DataFrame(columns=list(range(4)), index=['a','b','c','d'])
i=0

for l in total2:
    df_final.loc[l] = total[i]
    i += 1

# 调整轴参数为axis=0，统计每列最大值对应的行名次数
max_counts = df_final.idxmax(axis=0).value_counts()
# 补充所有行，确保次数为0的行被包含
all_indices = df_final.index
result = max_counts.reindex(all_indices, fill_value=0)
# 转换为目标格式并排序
result_df = result.reset_index()
result_df.columns = ['dataset', 'no of being max a column']
result_df = result_df.sort_values(by='no of being max a column', ascending=False).reset_index(drop=True)

print(result_df)

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