<jupyter_start><jupyter_text>1. We start off by getting the matches from the pickled files<jupyter_code>practice_data_2000_3000 = pd.read_pickle("../data/practice_data_2000_3000.pkl")
practice_data_3000_4000 = pd.read_pickle("../data/practice_data_3000_4000.pkl")
practice_data_no_limit = pd.read_pickle("../data/practice_data_no_limit.pkl")

test_data_2000_3000 = pd.read_pickle("../data/test_data_2000_3000.pkl")
test_data_3000_4000 = pd.read_pickle("../data/test_data_3000_4000.pkl")
test_data_no_limit = pd.read_pickle("../data/test_data_no_limit.pkl")<jupyter_output><empty_output><jupyter_text>2. Let's start inspecting our dataframe **We do have to be mindful that all matches are duplicated 10 times, since there is a row for each picked hero in each match**<jupyter_code>practice_data_2000_3000<jupyter_output><empty_output><jupyter_text>**As seen below, all the datatypes seem to come in the right format**<jupyter_code>practice_data_2000_3000.dtypes<jupyter_output><empty_output><jupyter_text>**There are zero null values, as this was handled in the SQL query to the API**<jupyter_code>practice_data_2000_3000.isna().sum()<jupyter_output><empty_output><jupyter_text>3. We now want to clean our dataframe to a usable format**We want to go from this table:**| match_id | ... | player_slot | hero_id ||------|------|------|------||5513437212|...|Dire|84||5513437212|...|Dire|8||5513437212|...|Dire|39||5513437212|...|Dire|29||5513437212|...|Dire|80||5513437212|...|Radiant|46||5513437212|...|Radiant|75||5513437212|...|Radiant|96||5513437212|...|Radiant|4||5513437212|...|Radiant|25|**To this table:**| match_id | ... | radiant1 | radiant2 | radiant3 | radiant4 | radiant5 |  dire1 | dire2 | dire3 | dire4 | dire5 ||------|------|------|------|------|------|------|------|------|------|------|------||5513437212|...|46|75|96|4|25|84|8|39|29|80| **We write a function to do this:**<jupyter_code>def table_cleaner(dataframe):
    # 0-4: Radiant team
    # 128-132: Dire team
    #If the player slot is between 0-4 the player is on the radiant team
    #if the player slot is between 128-132 the player is on the dire team
    slot_dict = {0: "radiant_1",
                 1: "radiant_2",
                 2: "radiant_3",
                 3: "radiant_4",
                 4: "radiant_5",
                 128: "dire_1",
                 129: "dire_2",
                 130: "dire_3",
                 131: "dire_4",
                 132: "dire_5"}
    
    # We drop the columns we don't need
    dataframe.drop(["num_mmr", "lobby_type", "game_mode", "avg_rank_tier", "num_rank_tier"], axis=1, inplace=True)
    
    # We create columns for each player slot to make the df horisontal rather than vertical
    for slot in slot_dict:
        dataframe.loc[dataframe["player_slot"] == slot, slot_dict[slot]] = 1
    
    # We fill the NaN values with 0 and converts the columns to integers rather than floats
    dataframe = dataframe.fillna(0).astype(int)
    
    # We now multiply the binary columns with the `hero_id` to get the hero id out there. 
    # If the column is 0, it will stay 0
    # for the radiant team
    dataframe["radiant_1"] = dataframe["hero_id"] * dataframe["radiant_1"]
    dataframe["radiant_2"] = dataframe["hero_id"] * dataframe["radiant_2"]
    dataframe["radiant_3"] = dataframe["hero_id"] * dataframe["radiant_3"]
    dataframe["radiant_4"] = dataframe["hero_id"] * dataframe["radiant_4"]
    dataframe["radiant_5"] = dataframe["hero_id"] * dataframe["radiant_5"]
    # for the dire team
    dataframe["dire_1"] = dataframe["hero_id"] * dataframe["dire_1"]
    dataframe["dire_2"] = dataframe["hero_id"] * dataframe["dire_2"]
    dataframe["dire_3"] = dataframe["hero_id"] * dataframe["dire_3"]
    dataframe["dire_4"] = dataframe["hero_id"] * dataframe["dire_4"]
    dataframe["dire_5"] = dataframe["hero_id"] * dataframe["dire_5"]
    
    # We don't need the "hero_id" or "player_slot" columns anymore, so we drop them
    dataframe.drop(["hero_id", "player_slot"], axis=1, inplace=True)
    
    # We now group the matches to only have one row per match_id
    dataframe = dataframe.groupby(["match_id",
                                   "match_seq_num",
                                   "radiant_win",
                                   "start_time",
                                   "duration",
                                   "avg_mmr"]).agg({"radiant_1": "sum",
                                                    "radiant_2": "sum",
                                                    "radiant_3": "sum",
                                                    "radiant_4": "sum",
                                                    "radiant_5": "sum",
                                                    "dire_1": "sum",
                                                    "dire_2": "sum",
                                                    "dire_3": "sum",
                                                    "dire_4": "sum",
                                                    "dire_5": "sum"}).reset_index()
    
    # We want to make sure "radiant_win" stays a boolean and does not get converted to an int
    dataframe["radiant_win"] = dataframe["radiant_win"].astype(bool)
    
    dataframe = dataframe.replace(0, np.nan)
    dataframe = dataframe.dropna()
    dataframe[["radiant_1",
               "radiant_2",
               "radiant_3",
               "radiant_4",
               "radiant_5",
               "dire_1",
               "dire_2",
               "dire_3",
               "dire_4",
               "dire_5"]] = dataframe[["radiant_1",
                                       "radiant_2",
                                       "radiant_3",
                                       "radiant_4",
                                       "radiant_5",
                                       "dire_1",
                                       "dire_2",
                                       "dire_3",
                                       "dire_4",
                                       "dire_5"]].astype(int)
    
    # We return the processed dataframe
    return dataframe.reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>4. Creating winrate pivot tables for synergy and counter**We want to create pivot tables that can compute the winrate of synergy and counter in each hero pair****The synergy pivot table would look like this:**| hero_id | 1 | 2 | 3 | 4 | ... | 128 | 129 ||------|------|------|------|------|------|------|------|| **1** |NaN|0.53|0.42|0.52|...|0.63|0.57|| **2** |0.53|NaN|0.51|0.53|...|0.53|0.53|| **3** |0.42|0.51|NaN|0.55|...|0.53|0.53|| **4** |0.52|0.53|0.55|NaN|...|0.53|0.51|| **5** |0.56|0.53|0.53|0.53|...|0.53|0.58||...|...|...|...|...|...|...|...|| **128** |0.63|0.53|0.53|0.53|...|NaN|0.51|| **129** |0.57|0.53|0.51|0.53|...|0.51|NaN|In this case, the pivot table is mirrored as it is equally good/bad when the heroes are on the same team. **The counter pivot table would look like this:**| hero_id | 1 | 2 | 3 | 4 | ... | 128 | 129 ||------|------|------|------|------|------|------|------|| **1** |NaN|0.47|0.58|0.48|...|0.37|0.43|| **2** |0.53|NaN|0.49|0.47|...|0.47|0.47|| **3** |0.42|0.51|NaN|0.45|...|0.47|0.49|| **4** |0.52|0.53|0.55|NaN|...|0.47|0.47|| **5** |0.56|0.53|0.53|0.53|...|0.53|0.58||...|...|...|...|...|...|...|...|| **128** |0.63|0.53|0.53|0.53|...|NaN|0.49|| **129** |0.57|0.53|0.51|0.53|...|0.51|NaN|When it comes to the counter pivot table, the winrate will always be 1 minus the winrate for a pair. For instance hero 129 has a winrate of 0.63 vs hero 13. This means that hero 13 has a winrate of 0.37 vs hero 129**Now we make a function that splits the dataset in radiant wins and dire wins**<jupyter_code># The split the dataframe into two, one when radiant won, and one when dire won
def winning_side_splitter(dataframe):
    # Dataframe containing only radiant wins
    df_matches_radiant = dataframe.loc[dataframe["radiant_win"] == 1].reset_index()
    # Dataframe containing only dire wins
    df_matches_dire = dataframe.loc[dataframe["radiant_win"] == 0].reset_index()
    return df_matches_radiant, df_matches_dire<jupyter_output><empty_output><jupyter_text>We want to create a two pivot tables:* Synergy winrate (contains the winrate-synergy between two heroes)* Counter winrate (contains the winrate-counter between two heroes)To get that, we need the following pivot tables:* radiant_synergy_win* radiant_synergy_loss* radiant_opponent_win* radiant_opponent_loss* dire_synergy_win* dire_synergy_loss* dire_opponent_win* dire_opponent_loss**To get the synergy winrate:**synergy_wins = dire_synergy_wins + radiant_synergy_winssynergy_losses = dire_synergy_loss + radiant_synergy_losssynergy_winrate = synergy_wins / (synergy_wins + synergy_losses)**The same goes for the counter winrate:**counter_wins = dire_counter_wins + radiant_counter_winscounter_losses = dire_counter_losses + radiant_counter_lossescounter_winrate = counter_wins / (counter_wins + counter_losses) We start of by creating the functions for the synergy pivot table**Now we create the function that creates the 20 combined synergy pivot tables, to later add together**<jupyter_code>def pair_pivots_synergy(matches_dataframe, permutations):
    # We declared the list to append all the pivot_tables on
    list_of_dfs = []
    # We declared the name count to keep track of the pivot tables
    name_count = 0
    # We declare an empty dataframe to input the pivot tables
    pivot = {}
    
    # Now we loop through the columns in the team except its own columns
    for permutation1 in range(len(permutations)):
        # We loop through the perpendicular columns
        for permutation2 in range(len(permutations)):
            # We make sure not to include the column in question
            if permutations[permutation1] != permutations[permutation2]:
                # Increment the name_count by one
                name_count+=1
                
                #We create a pivot table for each hero matchup that happened where the given side won
                pivot = pd.pivot_table(matches_dataframe
                                 # We group by the hero1 with hero2
                                 .groupby([permutations[permutation1], permutations[permutation2]])
                                 # We count the occurences of each occurence
                                 .agg(count=(permutations[permutation2], "count"))
                                 # We reset the index
                                 .reset_index()
                                 # And rename the columns from "direX" and "direY" to "hero_1" and "hero_2"
                                 # to be able to add them together later on column names
                                 .rename(columns={permutations[permutation1]: "hero_1", permutations[permutation2]: "hero_2", "count": f"count{name_count}"}),
                                     index="hero_1",
                                     columns="hero_2",
                                     values=f"count{name_count}",
                                     dropna=False,
                                     fill_value=0)
                
                # We append the pivot table to a list of dataframes for later use
                list_of_dfs.append(pivot)
    
    return list_of_dfs<jupyter_output><empty_output><jupyter_text>**Now we combine the pivot tables into one**<jupyter_code># From the pair_pivots function we are returning 20 dataframes in a list. 
# we add them together in to one with the "pivot_combiner"
def pivot_combiner(list_of_pivots):
    frame1 = list_of_pivots[0]
    for frame in range(len(list_of_pivots)):
        if frame != 0:
            frame1 = frame1 + list_of_pivots[frame]
    return frame1<jupyter_output><empty_output><jupyter_text>**Now we create a wrapper to calculate the synergy winrate for each hero**<jupyter_code># The synergy_wrapper takes the dataframe that contains the match data with the radiant_1 - dire_5 in the columns
# and creates a synergy_pivot table based on that. The table will be used later to compute the winrate of certain
# pair_matchups
def synergy_wrapper(dataframe):
    # We pass the columns statically, as they will never change for a dota match
    permutations1 = ["radiant_1", "radiant_2", "radiant_3", "radiant_4", "radiant_5"]
    permutations2 = ["dire_1", "dire_2", "dire_3", "dire_4", "dire_5"]
    
    # We use unpacking to return the dfs where dire won and where radiant won
    df_matches_radiant, df_matches_dire = winning_side_splitter(dataframe)
    
    # We create the 20 pivot tables that contains all the columns win pair combinations for dire
    dire_synergy_wins = pivot_combiner(pair_pivots_synergy(df_matches_dire, permutations2))
    
    # We create the 20 pivot tables that contains all the columns loss pair combinations for dire
    dire_synergy_losses = pivot_combiner(pair_pivots_synergy(df_matches_radiant, permutations2))
    
    # We create the 20 pivot tables that contains all the columns win pair combinations for radiant
    radiant_synergy_wins = pivot_combiner(pair_pivots_synergy(df_matches_radiant, permutations1))
    
    # We create the 20 pivot tables that contains all the columns loss pair combinations for radiant
    radiant_synergy_losses = pivot_combiner(pair_pivots_synergy(df_matches_dire, permutations1))
    
    # We compute the total amount of synergy wins
    synergy_wins = dire_synergy_wins + radiant_synergy_wins
    
    # We compute the total amount of synergy losses
    synergy_losses = dire_synergy_losses + radiant_synergy_losses
    
    # We compute the synergy_winrate
    synergy_winrate = synergy_wins.div((synergy_wins.add(synergy_losses)))
    
    # We return the synergy_winrate pivot table
    return round(synergy_winrate,2)<jupyter_output><empty_output><jupyter_text>We will now create the functions to calculate the counter-winrate of the heroes on each team**We now have the function to compute the synergy of two heroes on the same team.**We want to create the same pivot tables as seen in the previous section<jupyter_code>def pair_pivots_counter(matches_dataframe, permutations1, permutations2):
    # We declare the list to append all the pivot_tables on
    list_of_dfs = []
    # We declare the name count to keep track of the pivot tables
    name_count = 0
    # We declare an empty dataframe to input the pivot tables
    pivot = {}
    
    # Now we loop through the columns in the team
    for permutation1 in range(len(permutations1)):
        # We loop through the perpendicular columns on the opponent team
        for permutation2 in range(len(permutations2)):
            # Increment the name_count by one
            name_count+=1
            
            #We create a pivot table for each hero matchup that happened where the given side won
            pivot = pd.pivot_table(matches_dataframe
                             # We group by the hero1 with hero2
                             .groupby([permutations1[permutation1], permutations2[permutation2]])
                             # We count the occurences of each matchup
                             .agg(count=(permutations2[permutation2], "count"))
                             # We reset the index
                             .reset_index()
                             # And rename the columns from "radiantX" and "direY" to "hero_1" and "hero_2"
                             # to be able to add them together later on column names
                             .rename(columns={permutations1[permutation1]: "hero_1", permutations2[permutation2]: "hero_2", "count": f"count{name_count}"}),
                                 index="hero_1",
                                 columns="hero_2",
                                 values=f"count{name_count}",
                                 dropna=False,
                                 fill_value=0)
            
            # We append the pivot table to a list of dataframes for later use
            list_of_dfs.append(pivot)
    
    return list_of_dfs<jupyter_output><empty_output><jupyter_text>**Now we wrap this in a `counter_wrapper` to get our counter-pivot**<jupyter_code>def counter_wrapper(dataframe):
    # We pass the columns statically, as they will never change for a dota match
    permutations1 = ["radiant_1", "radiant_2", "radiant_3", "radiant_4", "radiant_5"]
    permutations2 = ["dire_1", "dire_2", "dire_3", "dire_4", "dire_5"]
    
    # We use unpacking to return the dfs where dire won and where radiant won
    df_matches_radiant, df_matches_dire = winning_side_splitter(dataframe)
    
    # We create the 25 pivot tables that contains all the columns win counter combinations for dire vs radiant
    dire_counter_wins = pivot_combiner(pair_pivots_counter(df_matches_dire, permutations2, permutations1))
    
    # We create the 25 pivot tables that contains all the columns loss counter combinations for dire vs radiant
    dire_counter_losses = pivot_combiner(pair_pivots_counter(df_matches_radiant, permutations2, permutations1))
    
    # We create the 25 pivot tables that contains all the columns win counter combinations for radiant vs dire
    radiant_counter_wins = pivot_combiner(pair_pivots_counter(df_matches_radiant, permutations1, permutations2))
    
    # We create the 25 pivot tables that contains all the columns loss counter combinations for radiant vs dire
    radiant_counter_losses = pivot_combiner(pair_pivots_counter(df_matches_dire, permutations1, permutations2))
    
    # We compute the total amount of counter wins
    counter_wins = dire_counter_wins + radiant_counter_wins
    
    # We compute the total amount of counter losses
    counter_losses = dire_counter_losses + radiant_counter_losses
    
    # We compute the counter_winrate
    counter_winrate = counter_wins.div((counter_wins.add(counter_losses)))
    
    # We return the counter_winrate pivot table
    return round(counter_winrate,2)<jupyter_output><empty_output><jupyter_text>5. Instantiate the functions and save as pickled dataframes **We now instantiate the functions written above**<jupyter_code>train_2000_3000 = table_cleaner(practice_data_2000_3000)
train_3000_4000 = table_cleaner(practice_data_3000_4000)
train_no_limit = table_cleaner(practice_data_no_limit)

synergy_pivot_2000_3000 = synergy_wrapper(train_2000_3000)
counter_pivot_2000_3000 = counter_wrapper(train_2000_3000)

synergy_pivot_3000_4000 = synergy_wrapper(train_3000_4000)
counter_pivot_3000_4000 = counter_wrapper(train_3000_4000)

synergy_pivot_no_limit = synergy_wrapper(train_no_limit)
counter_pivot_no_limit = counter_wrapper(train_no_limit)<jupyter_output><empty_output><jupyter_text>**We make sure to clean the test data as well**<jupyter_code>test_2000_3000 = table_cleaner(test_data_2000_3000)
test_3000_4000 = table_cleaner(test_data_3000_4000)
test_no_limit = table_cleaner(test_data_no_limit)<jupyter_output><empty_output><jupyter_text>**And we save it as pickled dataframes for later use**<jupyter_code>train_2000_3000.to_pickle("../data/clean_train_2000_3000.pkl")
train_3000_4000.to_pickle("../data/clean_train_3000_4000.pkl")
train_no_limit.to_pickle("../data/clean_train_no_limit.pkl")

test_2000_3000.to_pickle("../data/clean_test_2000_3000.pkl")
test_3000_4000.to_pickle("../data/clean_test_3000_4000.pkl")
test_no_limit.to_pickle("../data/clean_test_no_limit.pkl")

synergy_pivot_2000_3000.to_pickle("../data/2000_3000_synergy_pivot.pkl")
counter_pivot_2000_3000.to_pickle("../data/2000_3000_counter_pivot.pkl")
synergy_pivot_3000_4000.to_pickle("../data/3000_4000_synergy_pivot.pkl")
counter_pivot_3000_4000.to_pickle("../data/3000_4000_counter_pivot.pkl")
synergy_pivot_no_limit.to_pickle("../data/no_limit_synergy_pivot.pkl")
counter_pivot_no_limit.to_pickle("../data/no_limit_counter_pivot.pkl")<jupyter_output><empty_output><jupyter_text>6. Run below cells to check that tables look correct<jupyter_code>pd.read_pickle("../data/df_matches_2000_3000.pkl")
pd.read_pickle("../data/df_matches_3000_4000.pkl")
pd.read_pickle("../data/df_matches_no_limit.pkl")
pd.read_pickle("../data/2000_3000_synergy_pivot.pkl")
pd.read_pickle("../data/2000_3000_counter_pivot.pkl")
pd.read_pickle("../data/3000_4000_synergy_pivot.pkl")
pd.read_pickle("../data/3000_4000_counter_pivot.pkl")
pd.read_pickle("../data/no_limit_synergy_pivot.pkl")
pd.read_pickle("../data/no_limit_counter_pivot.pkl")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11138770927905502, 'fraction_numerical': 0.051252013471957826, 'mean_word_length': 2.7284804367606914, 'pattern_counts': {'":': 12, '<': 61, '<?xml version=': 0, '>': 61, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 54, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '174208', 'license': 'MIT', 'n_tokens_mistral': 7115, 'n_tokens_neox': 6348, 'n_words': 1584, 'path': 'notebooks/data_cleaning_winrate.ipynb', 'repo_name': 'MikkiSeidenschnur/data_ber_dota2_winpredictor', 'size': '20487'}