[Python] Polars - How can I make multiple joins cross multiple Dataframes, examples included

import polars as pl

#Auctiondata which is used to create the 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']
}

#itemsData which is used to create the itemsDF
itemsData = {"ID": [24655,24648,3184,14187,6580],
"Display_lang" : ['Consortium Robe','Astralaan Gloves','Hook Dagger','Raincaller Cuffs','Defender Tunic']}

#suffixData which is used to create the 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]
}

#propertiesData which is used to create the 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]
}

#enchantmentsData which is used to create the 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']}

#resultData which is used in order to create the resultDF
resultData = {"AuctionId" : [2095293259, 2096131235, 2094319272, 2094265820, 2094902378, 2096005275],
"ItemId" : [24655, 24648, 3184, 14187, 6580, 1482],
"ItemName" : ['Consortium Robe','Astralaan Gloves','Hook Dagger','Raincaller Cuffs','Defender Tunic',''],
"RealmName" : ['Mograine', 'Bloodfang', 'Firemaw', 'Firemaw', 'Mograine','Mograine'],
"Faction" : ['Horde', 'Alliance', 'Alliance', 'Alliance', 'Horde','Horde'],
"EnchantmentName" : ['of the Invoker','of Intellect','of Strength','of Healing','of the Whale',''],
"Stat0" : ['+26 Intellect','+36 Intellect','+3 Strength','+13 Healing Spells and +5 Damage Spells','+4 Spirit',''],
"Stat1" : ['+30 Spell Damage and Healing','','','','+5 Stamina',''],
"Stat2" : ['+26 Spell Critical Strike Rating','','','','',''],
"Stat3" : ['','','','','',''],
"Stat4" : ['','','','','',''],
"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']}

#"Main" DF
auctionDF = pl.DataFrame(auctiondata)

#The ID column of the below Dataframe refrence to "ItemId" in auctionDF, it's not ALWAYS the ItemId from AuctionDF is within the itemsDF tho, but 99% of the time it is.
itemsDF = pl.DataFrame(itemsData)

#All negative ItemRands (ItemRands lower than 0) from AuctionDF refrences to the ID column of suffixDF, so I would imagine one of the first things to do is to make the ID column in suffixDF negative for a later join?
suffixDF = pl.DataFrame(suffixData)

#All positive ItemRands (ItemRands larger than 0) from AuctionDF refrences to the ID column of propertiesDF
propertiesDF = pl.DataFrame(propertiesData)

#All the various "Enchantment[X] columns from suffixDF and propertiesDF references to the ID column of the enchantmentDF"
enchantmentsDF = pl.DataFrame(enchantmentsData)

#The reason ItemName is blank for the ItemId 1482 is because it does not exist in the itemsDF
resultDF = pl.DataFrame(resultData)
print(resultDF)


The resultDF is the result I want to obtain.

So basically we have the mainDF "AuctionDF": ItemId references to the ID column in itemsDF where we will need the Display_lang in order to make the "ItemName" column in the resultDF ItemRand references to either the propertiesDF (if the ItemRand is positive) or the suffixDF (if the ItemRand is negative) In the propertiesDF we need to get the column "Name_lang" which gives us the column "EnchantmentName" in the resultDF Furthermore we also need to use the Enchantment[0-4] columns from propertiesDF, these columns contains an ID which references over to enchantmentsDF where we need the column Name_lang. In the resultDF you will notice there are 5x StatX columns (stat0-4), those contains the value of Name_lang from the EnchantmnetsDF Lastly if the ItemRand from AuctionsDF had been a negative value, we would have gone into the "suffixDF" as the negative ItemRand is a reference to the ID column in SuffixDF In SuffixDF you also find Enchantment[0-4] as we did in the "propertiesDF", and those also references to ID in enchantmentsDF. However you will fast notice the Name_lang for those values looks a bit differently, such as "+$i Intellect", this is because we have to calculate the $i value our selfs. In the SuffixDF there also are an additional 5 columns AllocationPct[0-4] all these values has to be divided by 10000 firstly, as an example then for each AllocationPct[0-4] which has a value, we multiply it with the SuffixFactor So for negative rands (SuffixDF) Enchantment0 and AllocationPct0 gives Stat0, Enchantment1 and AllocationPct1 gives Stat1 and so on

Example of item calucation of a negative rand: ItemId 24655 from AuctionDF has ItemRand -39 and SuffixFactor 50 We take the -39, lookup in SuffixDF (ID column), we see Enchantment0 = 2804 We lookup 2804 in the ID column in EnchantmentsDF which has the Name_lang = +$i Intellect Now we calculate $i by look at AllocationPct0 in the SuffixDF which is 5259 Then we divide 5259 by 10000 (5259/10000 = 0,5259) Now we multiply 0,5259 with the SuffixFactor (50) and use floor on it (0,5259*50 = 26,295 = floor(26,295) = 26) Now we have Stat0 = +26 Intellect

I might have made slight mistakes in the data as I typed all this out manually. If there are any questions or you think I might have made a mistake, feel free to ask.

Best regards

Continue reading...