Polars多DataFrame多表连接实现：含示例的技术问询

原始数据定义代码 #

import polars as pl

# 用于创建AuctionDF的拍卖数据
auctiondata = {"AuctionId": [2095293259, 2096131235, 2094319272, 2094265820, 2094902378, 2096005275],
               "Bid": [9499998, 8499998, 8500000 , 1400832, 1400000, 872],
               "Buyout": [9499998, 9499998, 8500000, 1450832, 1500000, 900],
               "Quantity": [1, 1, 1, 1, 1, 1],
               "Time_Left": ['Short', 'Very long', 'Long', 'Short', 'Long', 'Long'],
               "ItemId": [24655, 24648, 3184, 14187,6580,1482],
               "ItemRand": [-39, -19, 24, 2032, 1020,None],
               "ItemSeed": [886505522, 483524644, 384031104, 1995900544,2119510144,None],
               "SuffixFactor": [50, 36, 55680, 1664, 10368,None],
               "Faction": ['Horde', 'Alliance', 'Alliance', 'Alliance', 'Horde','Horde'],
               "RealmName": ['Mograine', 'Bloodfang', 'Firemaw', 'Firemaw', 'Mograine','Mograine'],
               "BuyoutGold": ['45', '77', '24', '39', '120','42'],
               "BuyoutSilver": ['40', '44', '12', '33', '12','51'],
               "BuyoutCopper": ['12', '11', '21', '52', '32','42'],
               "BidGold": ['39', '12', '11', '27', '99','23'],
               "BidSilver": ['32', '14', '44', '12', '4','42'],
               "BidCopper": ['21', '12', '32', '12', '45','33']
               }

# 用于创建itemsDF的物品数据
itemsData = {"ID": [24655,24648,3184,14187,6580],
             "Display_lang" : ['Consortium Robe','Astralaan Gloves','Hook Dagger','Raincaller Cuffs','Defender Tunic']}

# 用于创建suffixDF的后缀数据
suffixData = {"ID": [19, 39],
               "Name_lang": ['of Intellect', 'of the Invoker'],
               "Enchantment[0]": [2804, 2804],
               "Enchantment[1]": [0, 2824],
               "Enchantment[2]": [0, 2822],
               "Enchantment[3]": [0, 0],
               "Enchantment[4]": [0, 0],
               "AllocationPct[0]": [10000, 5259],
               "AllocationPct[1]": [None, 6153],
               "AllocationPct[2]": [None, 5259],
               "AllocationPct[3]": [None, None],
               "AllocationPct[4]": [None, None]
               }

# 用于创建propertiesDF的属性数据
propertiesData = {"ID": [24, 1020, 2032],
               "Name_lang": ['of Strength', 'of the Whale', 'of Healing'],
               "Enchantment[0]": [70, 98, 2312],
               "Enchantment[1]": [0, 103, 0],
               "Enchantment[2]": [0, 0, 0],
               "Enchantment[3]": [0, 0, 0],
               "Enchantment[4]": [0, 0, 0]
               }

# 用于创建enchantmentDF的附魔数据
enchantmentsData = {"ID": [70, 98, 103, 2312, 2804, 2822, 2824],
                    "Name_lang" : ['+3 Strength','+4 Spirit','+5 Stamina','+7 Spell Power','+$i Intellect', '+$i Critical Strike Rating', '+$i Spell Power']}

# 初始化各DataFrame
auctionDF = pl.DataFrame(auctiondata)
itemsDF = pl.DataFrame(itemsData)
suffixDF = pl.DataFrame(suffixData)
propertiesDF = pl.DataFrame(propertiesData)
enchantmentsDF = pl.DataFrame(enchantmentsData)

需求逻辑说明 #

以auctionDF为核心，按以下规则生成resultDF：

1. 物品名称关联：通过ItemId关联itemsDF的ID列，取Display_lang作为ItemName；不存在匹配项时ItemName为空字符串。
2. 正ItemRand处理：ItemRand为正时，关联propertiesDF的ID列取Name_lang作为EnchantmentName；同时将propertiesDF中Enchantment[0-4]的ID关联enchantmentsDF，取Name_lang作为Stat0-4；附魔ID为0时对应Stat列为空。
3. 负ItemRand处理：ItemRand为负时，先将suffixDF的ID转为负数，关联后取Name_lang作为EnchantmentName；Enchantment[0-4]关联enchantmentsDF后，若附魔名称含+$i，则计算floor(AllocationPct[X]/10000 * SuffixFactor)替换$i；附魔ID为0时对应Stat列为空。

负ItemRand计算示例 #

auctionDF中ItemId=24655的ItemRand=-39、SuffixFactor=50：

1. -39关联suffixDF转负后的ID，得到Enchantment[0]=2804；
2. 2804关联enchantmentsDF，得到名称+$i Intellect；
3. 取suffixDF的AllocationPct[0]=5259，计算5259/10000*50=26.295，取floor值26；
4. 最终Stat0为+26 Intellect。

实现代码 #

# 预处理suffixDF，生成负ID用于关联
suffixDF = suffixDF.with_columns(pl.col("ID").mul(-1).alias("NegID"))

# 第一步：关联物品名称
auction_with_item = auctionDF.join(
    itemsDF,
    left_on="ItemId",
    right_on="ID",
    how="left"
).with_columns(
    pl.col("Display_lang").fill_null("").alias("ItemName")
)

# 处理正ItemRand的情况
# 先关联propertiesDF与enchantmentsDF生成Stat模板
props_stat = propertiesDF.join(
    enchantmentsDF, left_on="Enchantment[0]", right_on="ID", how="left"
).rename({"Name_lang": "Stat0"})
props_stat = props_stat.join(
    enchantmentsDF, left_on="Enchantment[1]", right_on="ID", how="left"
).rename({"Name_lang": "Stat1"})
props_stat = props_stat.join(
    enchantmentsDF, left_on="Enchantment[2]", right_on="ID", how="left"
).rename({"Name_lang": "Stat2"})
props_stat = props_stat.join(
    enchantmentsDF, left_on="Enchantment[3]", right_on="ID", how="left"
).rename({"Name_lang": "Stat3"})
props_stat = props_stat.join(
    enchantmentsDF, left_on="Enchantment[4]", right_on="ID", how="left"
).rename({"Name_lang": "Stat4"})

# 填充空值为空字符串
props_stat = props_stat.with_columns(
    pl.col(f"Stat{x}").fill_null("") for x in range(5)
)

# 关联正ItemRand的拍卖数据
positive_rand = auction_with_item.filter(pl.col("ItemRand") > 0).join(
    props_stat,
    left_on="ItemRand",
    right_on="ID",
    how="left"
).with_columns(
    pl.col("Name_lang").alias("EnchantmentName")
)

# 处理负ItemRand的情况
# 先关联suffixDF与enchantmentsDF生成Stat模板
suffix_stat = suffixDF.join(
    enchantmentsDF, left_on="Enchantment[0]", right_on="ID", how="left"
).rename({"Name_lang": "Stat0_template"})
suffix_stat = suffix_stat.join(
    enchantmentsDF, left_on="Enchantment[1]", right_on="ID", how="left"
).rename({"Name_lang": "Stat1_template"})
suffix_stat = suffix_stat.join(
    enchantmentsDF, left_on="Enchantment[2]", right_on="ID", how="left"
).rename({"Name_lang": "Stat2_template"})
suffix_stat = suffix_stat.join(
    enchantmentsDF, left_on="Enchantment[3]", right_on="ID", how="left"
).rename({"Name_lang": "Stat3_template"})
suffix_stat = suffix_stat.join(
    enchantmentsDF, left_on="Enchantment[4]", right_on="ID", how="left"
).rename({"Name_lang": "Stat4_template"})

# 关联负ItemRand的拍卖数据
negative_rand = auction_with_item.filter(pl.col("ItemRand") < 0).join(
    suffix_stat,
    left_on="ItemRand",
    right_on="NegID",
    how="left"
)

# 计算并替换Stat中的$i变量
negative_rand = negative_rand.with_columns([
    pl.when(pl.col(f"Stat{x}_template").str.contains(r"\$i"))
    .then(pl.col(f"Stat{x}_template").str.replace(
        r"\$i",
        pl.floor(pl.col(f"AllocationPct[{x}]") / 10000 * pl.col("SuffixFactor")).cast(str)
    ))
    .otherwise(pl.col(f"Stat{x}_template"))
    .fill_null("")
    .alias(f"Stat{x}")
    for x in range(5)
]).with_columns(
    pl.col("Name_lang").alias("EnchantmentName")
)

# 处理ItemRand为None的情况
none_rand = auction_with_item.filter(pl.col("ItemRand").is_null()).with_columns([
    pl.lit("").alias(col) for col in ["EnchantmentName", "Stat0", "Stat1", "Stat2", "Stat3", "Stat4"]
])

# 合并所有部分并整理列顺序
resultDF = pl.concat([positive_rand, negative_rand, none_rand], how="vertical").select(
    "AuctionId", "ItemId", "ItemName", "RealmName", "Faction", "EnchantmentName",
    "Stat0", "Stat1", "Stat2", "Stat3", "Stat4",
    "BuyoutGold", "BuyoutSilver", "BuyoutCopper",
    "BidGold", "BidSilver", "BidCopper"
).sort("AuctionId")

# 输出结果
print(resultDF)

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