编写函数获取多列value_counts并合并为单个DataFrame

现有样例数据如下：

col1    col2    col3    col4
1       a       as      12
1       f       as      13
2       d       af      13
3       f       as      12
4       f       at      14
4       d       at      14
5       h       am      15

手动对每列执行value_counts()再合并的方式可行，但代码冗余，示例如下：

col1 = df['col1'].value_counts().reset_index()
col1.columns = ['value','vaule_count']
col1['column'] = 'col1'
col2 = df['col2'].value_counts().reset_index()
col2.columns = ['value','vaule_count']
col2['column'] = 'col2'
col3 = df['col3'].value_counts().reset_index()
col3.columns = ['value','vaule_count']
col3['column'] = 'col3'
col4 = df['col4'].value_counts().reset_index()
col4.columns = ['value','vaule_count']
col4['column'] = 'col4'

df1 = pd.DataFrame()
df1 = pd.concat([col1,col2,col3,col4],ignore_index=True)
df1

期望输出格式如下：

value   vaule_count column
0   1   2   col1
1   4   2   col1
2   2   1   col1
3   3   1   col1
4   5   1   col1
5   f   3   col2
6   d   2   col2
7   a   1   col2
8   h   1   col2
9   as  3   col3
10  at  2   col3
11  af  1   col3
12  am  1   col3
13  12  2   col4
14  13  2   col4
15  14  2   col4
16  15  1   col4

优化方案 #

编写通用函数批量处理指定列，自动完成value_counts计算、列重命名与结果合并：

import pandas as pd

def get_combined_value_counts(df, columns):
    result_dfs = []
    for col in columns:
        # 计算当前列的value_counts并重置索引
        count_df = df[col].value_counts().reset_index()
        # 重命名列名
        count_df.columns = ['value', 'vaule_count']
        # 添加当前列的标识
        count_df['column'] = col
        result_dfs.append(count_df)
    # 合并所有结果并重置索引
    return pd.concat(result_dfs, ignore_index=True)

# 函数使用示例
df = pd.DataFrame({
    'col1': [1,1,2,3,4,4,5],
    'col2': ['a','f','d','f','f','d','h'],
    'col3': ['as','as','af','as','at','at','am'],
    'col4': [12,13,13,12,14,14,15]
})

final_result = get_combined_value_counts(df, ['col1','col2','col3','col4'])
print(final_result)

该函数通过遍历指定列批量处理逻辑，既避免了重复代码，也支持灵活扩展处理任意数量的目标列。

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