如何使用Pandas将指定数据集整理为目标交叉表格式？

问题描述 #

输入的原始数据集格式如下：

title                  topic
building something     3       alice    roy      jane
taking a break         5       stanley  bernard  alice   jane
how to make a cake     2       stanley  robert
playing by the pool    3       robert   alice

需要将其转换为如下交叉表格式，统计每个用户在不同topic下的出现次数：

topic
name        1   2   3   4   5
alice       0   0   2   0   1
bernard     0   0   0   0   1
jane        0   0   1   0   1
robert      0   1   1   0   0
roy         0   0   1   0   0
stanley     0   1   0   0   1

解决方案步骤 #

以下是用Pandas实现的具体操作：

1. 读取并规整原始数据 #

原始数据的列结构不规则，第一行为表头，后续行中title是第一列、topic是第二列，剩余列均为用户名称。我们可以通过空格分隔读取数据：

import pandas as pd

# 从文本文件读取数据（如果是字符串数据，可改用注释内的方式）
df = pd.read_csv('your_data.txt', sep='\s+', header=0)

# 若数据是字符串形式，用以下方式读取
# data = """title                  topic
# building something     3       alice    roy      jane
# taking a break         5       stanley  bernard  alice   jane
# how to make a cake     2       stanley  robert
# playing by the pool    3       robert   alice"""
# df = pd.read_csv(pd.compat.StringIO(data), sep='\s+', header=0)

2. 重塑数据为长格式 #

把每行中的多个用户名称拆分为单独行，与对应的topic关联：

# 提取所有用户名称列，将数据转为长格式
user_cols = df.columns[2:]
melted_df = df.melt(id_vars=['topic'], value_vars=user_cols, value_name='name')
# 移除空值（部分行用户数量少，后续列会产生空值）
melted_df = melted_df.dropna(subset=['name'])

3. 生成交叉表并补全缺失topic #

用交叉表统计用户在各topic下的出现次数，同时补全1-5的所有topic类别，缺失项填充0：

# 生成用户与topic的次数交叉表
result = pd.crosstab(melted_df['name'], melted_df['topic'])
# 补全1到5的所有topic列，缺失列填充0
result = result.reindex(columns=[1,2,3,4,5], fill_value=0)
# 设置双层表头，匹配目标格式
result.columns = pd.MultiIndex.from_tuples([('topic', col) for col in result.columns])
# 将name从索引转为列
result = result.reset_index().rename(columns={'index': 'name'})

4. 调整显示格式（可选） #

如果需要和目标格式的对齐效果一致，可以设置打印选项：

pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)
print(result.to_string(index=False))

运行上述代码后，即可得到目标格式的输出结果。

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