如何为3个球员DataFrame生成统一唯一整数键适配FeatureTools？

步骤1：统一清洗球员姓名（可选但关键） #

不同DataFrame里的同一球员姓名可能存在大小写、空格差异，先统一格式避免ID分配错误：

import pandas as pd
import featuretools as ft

# 假设你的三个DataFrame是df1、df2、df3，均包含"player_name"列
def clean_player_name(name):
    return name.strip().lower()

# 对每个DataFrame的姓名列做清洗
df1["player_name"] = df1["player_name"].apply(clean_player_name)
df2["player_name"] = df2["player_name"].apply(clean_player_name)
df3["player_name"] = df3["player_name"].apply(clean_player_name)

步骤2：生成全局唯一的球员整数ID映射 #

收集所有DataFrame中的球员姓名，去重后为每个球员分配唯一整数ID：

# 合并所有球员姓名并去重
all_unique_players = pd.concat([df1["player_name"], df2["player_name"], df3["player_name"]]).unique()

# 创建姓名到ID的映射字典
player_id_mapping = {name: idx for idx, name in enumerate(all_unique_players)}

# 或者用pandas factorize更简洁，效果一致
# all_players_series = pd.concat([df1["player_name"], df2["player_name"], df3["player_name"]])
# _, unique_names = pd.factorize(all_players_series)
# player_id_mapping = dict(zip(unique_names, range(len(unique_names))))

步骤3：为每个DataFrame添加player_id列 #

将每个DataFrame中的球员姓名替换为对应的整数ID：

df1["player_id"] = df1["player_name"].map(player_id_mapping)
df2["player_id"] = df2["player_name"].map(player_id_mapping)
df3["player_id"] = df3["player_name"].map(player_id_mapping)

步骤4：创建EntitySet并关联实体 #

将三个DataFrame作为实体加入EntitySet，用player_id作为关联键：

# 初始化EntitySet
es = ft.EntitySet(id="basketball_player_data")

# 添加第一个实体（比如基础球员信息表，用player_id作为主键）
es = es.add_dataframe(
    dataframe_name="player_basic",
    dataframe=df1,
    index="player_id",
    make_index=False  # 已有主键，无需自动生成
)

# 添加第二个实体（比如比赛统计数据，假设没有自带主键，自动生成）
es = es.add_dataframe(
    dataframe_name="game_stats",
    dataframe=df2,
    make_index=True,  # 自动生成名为"game_stats_index"的主键
    foreign_keys=["player_id"]  # 通过player_id关联player_basic
)

# 添加第三个实体（比如薪资数据）
es = es.add_dataframe(
    dataframe_name="salary_info",
    dataframe=df3,
    make_index=True,
    foreign_keys=["player_id"]
)

验证关联是否正确 #

可以检查每个实体中同一球员的ID是否一致，或者用可视化确认关系：

# 检查df1和df2中同一球员的ID是否匹配
sample_player = df1["player_name"].iloc[0]
print(f"球员{sample_player}在df1中的ID：{df1.loc[df1['player_name']==sample_player, 'player_id'].iloc[0]}")
print(f"球员{sample_player}在df2中的ID：{df2.loc[df2['player_name']==sample_player, 'player_id'].iloc[0]}")

# 可视化实体关系（需安装graphviz）
# es.plot()

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