基于分组后的出现情况为Pandas DataFrame创建二进制列

现有数据 #

目标空DataFrame #

import pandas as pd
import numpy as np

dfw_columns = pd.DataFrame({
    "col1": [],
    "col2": [],
    "col3": [],
    "col4": [],
    "col5": []
})

实际数据DataFrame #

df = pd.DataFrame({
    "Name": ["abc", "abc", "abc", "def", "def", "ghi", "ghi"],
    "colids": ["col1", "col33", np.nan, "col5", "col1", "col2", np.nan]
})

期望输出 #

desireddf = pd.DataFrame({
    "Name": ["abc", "def", "ghi"],
    "col1": [1,1, 0],
    "col2": [0,0, 1],
    "col3": [0,0, 0],
    "col4": [0,0, 0],
    "col5": [0,1,0]
})

解决方案 #

实现思路 #

• 过滤colids中的无效值（空值或不在目标列列表中的值）
• 按Name分组，标记每个目标列的出现情况
• 补全所有目标列，缺失列填充0

代码实现 #

import pandas as pd
import numpy as np

# 初始化数据
dfw_columns = pd.DataFrame({
    "col1": [],
    "col2": [],
    "col3": [],
    "col4": [],
    "col5": []
})

df = pd.DataFrame({
    "Name": ["abc", "abc", "abc", "def", "def", "ghi", "ghi"],
    "colids": ["col1", "col33", np.nan, "col5", "col1", "col2", np.nan]
})

# 获取目标列集合
target_cols = dfw_columns.columns

# 筛选有效数据：排除空值和非目标列的colids
valid_data = df.dropna(subset=["colids"]).query("colids in @target_cols")

# 标记存在的列
valid_data["flag"] = 1

# 透视表聚合：每个Name对应目标列的出现情况
result = valid_data.pivot(
    index="Name",
    columns="colids",
    values="flag"
).fillna(0).astype(int)

# 补全所有目标列，缺失列填0，并重置索引
result = result.reindex(columns=target_cols, fill_value=0).reset_index()

print(result)

运行结果 #

Name  col1  col2  col3  col4  col5
0   abc     1     0     0     0     0
1   def     1     0     0     0     1
2   ghi     0     1     0     0     0

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