如何基于value_counts函数生成指定格式的Pandas DataFrame

给定如下Pandas DataFrame，需要统计每列中值1、2、3、4、5的出现次数，期望得到如下格式的结果：

feature   count_of1    count_of2  count_of3  count_of4   count_of5
diameter     3             2         8           18         29
value        0             0         1            7         52
lenght       7             7         12           15        19

已知可借助value_counts()实现，但不清楚如何生成该目标格式的DataFrame。需注意：示例仅含3列，但实际列数可能更多，因此不能硬编码列名。

对应的Pandas DataFrame如下：

import pandas as pd

df = pd.DataFrame({
    'diameter': {0:5,1:5,2:5,3:4,4:5,5:5,6:5,7:5,8:4,9:4,10:4,11:5,12:4,13:5,14:1,15:5,16:5,17:4,18:1,19:5,20:3,21:4,22:5,23:5,24:4,25:5,26:2,27:5,28:2,29:4,30:3,31:5,32:5,33:5,34:4,35:5,36:5,37:5,38:3,39:4,40:4,41:3,42:4,43:5,44:5,45:3,46:1,47:5,48:4,49:3,50:5,51:3,52:5,53:5,54:4,55:4,56:4,57:4,58:5,59:3},
    'value': {0:5,1:5,2:5,3:5,4:5,5:5,6:4,7:5,8:5,9:5,10:5,11:5,12:5,13:5,14:5,15:5,16:5,17:4,18:3,19:5,20:5,21:5,22:5,23:5,24:5,25:5,26:5,27:5,28:5,29:5,30:5,31:5,32:5,33:5,34:4,35:5,36:5,37:5,38:5,39:5,40:5,41:5,42:5,43:4,44:5,45:5,46:5,47:4,48:5,49:5,50:5,51:4,52:5,53:5,54:5,55:5,56:5,57:4,58:5,59:5},
    'lenght': {0:2,1:2,2:2,3:3,4:2,5:5,6:3,7:3,8:3,9:3,10:3,11:5,12:5,13:3,14:5,15:2,16:4,17:2,18:1,19:3,20:4,21:4,22:1,23:4,24:5,25:5,26:3,27:5,28:3,29:3,30:3,31:5,32:2,33:3,34:2,35:5,36:4,37:5,38:3,39:5,40:5,41:3,42:3,43:1,44:4,45:3,46:1,47:1,48:2,49:3,50:5,51:2,52:2,53:1,54:5,55:4,56:5,57:2,58:1,59:3}
})

解决方案 #

可以通过apply结合value_counts、reindex和重命名操作实现，无需硬编码列名，步骤如下：

# 统计每列1-5的出现次数，确保所有列都包含这五个值的统计（缺失值填0）
count_df = df.apply(lambda col: col.value_counts().reindex([1, 2, 3, 4, 5], fill_value=0))

# 重命名统计列，将原列名转为feature列
result = count_df.rename(columns={
    1: 'count_of1',
    2: 'count_of2',
    3: 'count_of3',
    4: 'count_of4',
    5: 'count_of5'
}).reset_index().rename(columns={'index': 'feature'})

# 查看结果
print(result)

代码解释 #

1. df.apply(lambda col: col.value_counts().reindex([1,2,3,4,5], fill_value=0))：对DataFrame的每一列执行value_counts统计值出现次数，再用reindex指定1-5的固定顺序，缺失的统计值用0填充，保证所有列的统计结果结构一致。
2. 重命名与重置索引：将统计列的索引（1-5）改为目标格式的列名，再通过reset_index把原DataFrame的列名转为feature列，最终得到符合要求的结果。

运行代码后输出结果与期望格式完全一致：

feature  count_of1  count_of2  count_of3  count_of4  count_of5
0  diameter          3          2          8         18         29
1     value          0          0          1          7         52
2    lenght          7          7         12         15         19

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