How to calculate and store results based upon the Matching Rows of two different Pandas Dataframes in Python

Asad Hussain 0
Viewed 238

I have three DataFrames which I am importing from Excel Files. The dataframes are given below as HTML Tables,

Season Wise Record (this contains a Column Reward which is initialized with 0 initially)

<table><tbody><tr><th>Unnamed: 0</th><th>Name</th><th>Team</th><th>Position</th><th>Games Played</th><th>PassingCompletions</th><th>PassingYards</th><th>PassingTouchdowns</th><th>RushingYards</th><th>RushingTouchdowns</th><th>ReceivingYards</th><th>Receptions</th><th>Touchdowns</th><th>Type</th><th>Sacks</th><th>SoloTackles</th><th>TacklesForLoss</th><th>FumblesForced</th><th>DefensiveTouchdowns</th><th>Interceptions</th><th>PassesDefended</th><th>ReceivingTouchdowns</th><th>Reward</th></tr><tr><td>0</td><td>Tom Brady</td><td>TAM</td><td>QB</td><td>17</td><td>485</td><td>5316</td><td>43</td><td>81</td><td>2</td><td>0</td><td>0</td><td>2</td><td>OFFENSE</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>1</td><td>Justin Herbert</td><td>LAC</td><td>QB</td><td>17</td><td>443</td><td>5014</td><td>38</td><td>302</td><td>3</td><td>0</td><td>0</td><td>3</td><td>OFFENSE</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>2</td><td>Matthew Stafford</td><td>LAR</td><td>QB</td><td>17</td><td>404</td><td>4886</td><td>41</td><td>43</td><td>0</td><td>0</td><td>0</td><td>0</td><td>OFFENSE</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>3</td><td>Patrick Mahomes</td><td>KAN</td><td>QB</td><td>17</td><td>436</td><td>4839</td><td>37</td><td>381</td><td>2</td><td>0</td><td>0</td><td>2</td><td>OFFENSE</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>4</td><td>Derek Carr</td><td>LVR</td><td>QB</td><td>17</td><td>428</td><td>4804</td><td>23</td><td>108</td><td>0</td><td>0</td><td>0</td><td>0</td><td>OFFENSE</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>5</td><td>Joe Burrow</td><td>CIN</td><td>QB</td><td>16</td><td>366</td><td>4611</td><td>34</td><td>118</td><td>2</td><td>0</td><td>0</td><td>2</td><td>OFFENSE</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>6</td><td>Dak Prescott</td><td>DAL</td><td>QB</td><td>16</td><td>410</td><td>4449</td><td>37</td><td>146</td><td>1</td><td>0</td><td>0</td><td>1</td><td>OFFENSE</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>7</td><td>Josh Allen</td><td>BUF</td><td>QB</td><td>17</td><td>409</td><td>4407</td><td>36</td><td>763</td><td>6</td><td>0</td><td>0</td><td>6</td><td>OFFENSE</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>88</td><td>Ezekiel Elliott</td><td>DAL</td><td>RB</td><td>17</td><td>1</td><td>4</td><td>0</td><td>1002</td><td>10</td><td>287</td><td>47</td><td>12</td><td>OFFENSE</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>2</td><td>0</td></tr><tr><td>89</td><td>Marcus Mariota</td><td>LVR</td><td>QB</td><td>10</td><td>1</td><td>4</td><td>0</td><td>87</td><td>1</td><td>0</td><td>0</td><td>1</td><td>OFFENSE</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>90</td><td>Johnny Hekker</td><td>LAR</td><td>QB</td><td>17</td><td>1</td><td>2</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>OFFENSE</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>91</td><td>Greg Ward</td><td>PHI</td><td>QB</td><td>17</td><td>1</td><td>2</td><td>0</td><td>0</td><td>0</td><td>95</td><td>7</td><td>3</td><td>OFFENSE</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>3</td><td>0</td></tr><tr><td>92</td><td>Kendall Hinton</td><td>DEN</td><td>WR</td><td>16</td><td>1</td><td>1</td><td>0</td><td>0</td><td>0</td><td>175</td><td>15</td><td>1</td><td>OFFENSE</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td></tr><tr><td>93</td><td>Keenan Allen</td><td>LAC</td><td>WR</td><td>16</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1138</td><td>106</td><td>6</td><td>OFFENSE</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>6</td><td>0</td></tr><tr><td>94</td><td>Danny Amendola</td><td>HOU</td><td>QB</td><td>8</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>248</td><td>24</td><td>3</td><td>OFFENSE</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>3</td><td>0</td></tr><tr><td>95</td><td>Cole Beasley</td><td>BUF</td><td>WR</td><td>16</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>693</td><td>82</td><td>1</td><td>OFFENSE</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td></tr></tbody></table>

Game Wise Record (I am only adding some sample rows, there are 20k+ rows in it)

<table><tbody><tr><th>Index</th><th>Week</th><th>Name</th><th>Team</th><th>Starter</th><th>Interceptions</th><th>PassesDefended</th><th>Sacks</th><th>SoloTackles</th><th>TacklesForLoss</th><th>FumblesForced</th><th>PassesCompletions</th><th>PassingYards</th><th>PassingTouchdowns</th><th>PassingInterceptions</th><th>RushingYards</th><th>RushingTouchdowns</th><th>Receptions</th><th>ReceivingYards</th><th>ReceivingTouchdowns</th></tr><tr><td>0</td><td>1</td><td>Jourdan Lewis</td><td>DAL</td><td>1</td><td>1</td><td>2</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>1</td><td>1</td><td>Trevon Diggs</td><td>DAL</td><td>1</td><td>1</td><td>2</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>2</td><td>1</td><td>Anthony Brown</td><td>DAL</td><td>1</td><td>0</td><td>0</td><td>0</td><td>6</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>3</td><td>1</td><td>Jayron Kearse</td><td>DAL</td><td>0</td><td>0</td><td>0</td><td>0</td><td>5</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>4</td><td>1</td><td>Micah Parsons</td><td>DAL</td><td>1</td><td>0</td><td>1</td><td>0</td><td>3</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>5</td><td>1</td><td>Keanu Neal</td><td>DAL</td><td>1</td><td>0</td><td>0</td><td>0</td><td>3</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>6</td><td>1</td><td>DeMarcus Lawrence</td><td>DAL</td><td>1</td><td>0</td><td>0</td><td>0</td><td>4</td><td>0</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>7</td><td>1</td><td>Jaylon Smith</td><td>DAL</td><td>0</td><td>0</td><td>0</td><td>0</td><td>2</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>8</td><td>1</td><td>Dorance Armstrong Jr.</td><td>DAL</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>9</td><td>1</td><td>Tarell Basham</td><td>DAL</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>5175</td><td>5</td><td>Patrick Mahomes</td><td>KAN</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>33</td><td>272</td><td>2</td><td>2</td><td>61</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>5176</td><td>5</td><td>Darrel Williams</td><td>KAN</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>27</td><td>0</td><td>3</td><td>18</td><td>0</td></tr><tr><td>5177</td><td>5</td><td>Tyreek Hill</td><td>KAN</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>15</td><td>0</td><td>7</td><td>63</td><td>0</td></tr><tr><td>5178</td><td>5</td><td>Clyde Edwards-Helaire</td><td>KAN</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>13</td><td>0</td><td>1</td><td>11</td><td>0</td></tr><tr><td>5179</td><td>5</td><td>Jerick McKinnon</td><td>KAN</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>2</td><td>0</td><td>2</td><td>13</td><td>0</td></tr><tr><td>5180</td><td>5</td><td>Michael Burton</td><td>KAN</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>2</td><td>0</td><td>0</td><td>0</td><td>0</td></tr><tr><td>5181</td><td>5</td><td>Mecole Hardman</td><td>KAN</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>9</td><td>76</td><td>0</td></tr><tr><td>5182</td><td>5</td><td>Travis Kelce</td><td>KAN</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>0</td><td>6</td><td>57</td><td>1</td></tr></tbody></table>

And lastly, there's a Player Goals File (this is an Excel File containing Sheets for each of the position, I am only sharing for QB sheet, to keep the question short. IF needed, I can share the rest too)

<table><tbody><tr><th>Goal</th><th>Goal Type</th><th>PCC Reward</th><th>Target</th><th>Min Value</th><th>Max Value</th><th>Games Required</th><th>Started</th><th>Level 99 PCC Reward x4 (current series)</th><th>TImes achieved</th><th>PCC Rewarded</th><th> </th></tr><tr><td>Throw 300-399 yds</td><td>Game</td><td>25</td><td>PassingYards</td><td>300</td><td>399</td><td>0</td><td>0</td><td>100</td><td>8</td><td>200</td><td> </td></tr><tr><td>Throw 400-499 yds</td><td>Game</td><td>50</td><td>PassingYards</td><td>400</td><td>499</td><td>0</td><td>0</td><td>200</td><td>5</td><td>250</td><td>1000</td></tr><tr><td>Throw 500+ yds</td><td>Game</td><td>150</td><td>PassingYards</td><td>500</td><td>99999</td><td>0</td><td>0</td><td>600</td><td> </td><td>0</td><td>0</td></tr><tr><td>Throw 2 TDs</td><td>Game</td><td>50</td><td>Touchdowns</td><td>2</td><td>2</td><td>0</td><td>0</td><td>200</td><td>9</td><td>450</td><td>1800</td></tr><tr><td>Throw 3 TDs</td><td>Game</td><td>75</td><td>Touchdowns</td><td>3</td><td>3</td><td>0</td><td>0</td><td>300</td><td>4</td><td>300</td><td>1200</td></tr><tr><td>Throw 4 TDs</td><td>Game</td><td>100</td><td>Touchdowns</td><td>4</td><td>4</td><td>0</td><td>0</td><td>400</td><td>2</td><td>200</td><td>800</td></tr><tr><td>Throw 5+ TDs</td><td>Game</td><td>300</td><td>Touchdowns</td><td>5</td><td>10000</td><td>0</td><td>0</td><td>1200</td><td> </td><td>0</td><td>0</td></tr><tr><td>30-39 Completions</td><td>Game</td><td>50</td><td>PassingCompletions</td><td>30</td><td>39</td><td>0</td><td>0</td><td>200</td><td>5</td><td>250</td><td>1000</td></tr><tr><td>40+ Completions</td><td>Game</td><td>200</td><td>PassingCompletions</td><td>40</td><td>9999</td><td>0</td><td>0</td><td>800</td><td>1</td><td>200</td><td>800</td></tr><tr><td>0 INTs (must have been designated starter)</td><td>Game</td><td>200</td><td>PassingInterceptions</td><td>0</td><td>0</td><td>0</td><td>1</td><td>800</td><td>7</td><td>1400</td><td>5600</td></tr><tr><td>3500-3999 Passing YDs</td><td>Season</td><td>500</td><td>PassingYards</td><td>3500</td><td>3999</td><td>0</td><td>0</td><td>2000</td><td> </td><td>0</td><td>0</td></tr><tr><td>4000-4999 Passing YDS</td><td>Season</td><td>750</td><td>PassingYards</td><td>4000</td><td>4999</td><td>0</td><td>0</td><td>3000</td><td> </td><td>0</td><td>0</td></tr><tr><td>5000+ Passing YDS</td><td>Season</td><td>1250</td><td>PassingYards</td><td>5000</td><td>99999</td><td>0</td><td>0</td><td>5000</td><td> </td><td>0</td><td>0</td></tr><tr><td>30-39 Passing TDS</td><td>Season</td><td>750</td><td>PassingTouchdowns</td><td>30</td><td>39</td><td>0</td><td>0</td><td>3000</td><td> </td><td>0</td><td>0</td></tr><tr><td>40-45 Passing TDS</td><td>Season</td><td>1250</td><td>PassingTouchdowns</td><td>40</td><td>49</td><td>0</td><td>0</td><td>5000</td><td> </td><td>0</td><td>0</td></tr><tr><td>50+ Passing TDS</td><td>Season</td><td>2000</td><td>PassingTouchdowns</td><td>50</td><td>99999</td><td>0</td><td>0</td><td>8000</td><td> </td><td>0</td><td>0</td></tr></tbody></table>

What I want to do is analyze the Records of the Season Wise Records and the Game Wise Records, and based upon the Goals given in the Player Goals File, I want to add the Reward for all the players.

This is player position dependent so I made the following function to calculate Rewards for all the players (for the Season Records only)

def calculatePointsSeason(target, min_value, games_played_condition, max_value, tier_position, player_position, reward, games_played):
    if player_position in positions[tier_position]:
        if games_played > games_played_condition:
            if target >= min_value and target <= max_value:
                return reward 
    return 0

Similarly, I made this function to calculate Game wise Record,

def calculatePointsGame(target, min_value, max_value, tier_position, player_position, reward, started, started_condition):
    if player_position in positions[tier_position]:
        if started == started_condition:
            if target >= min_value and target <= max_value:
                return reward 
    return 0

Following is the function in which I am applying these two functions to calculate the Reward for each player,

for key, value in positions.items(): # Positions has a list of all the positions 
    for (idx, row) in rewards[key].iterrows(): # Rewards is a Dict containing Pandas Dataframes against each position
        if row['Goal Type'] == 'Season':
            df = df.copy(deep=True) # df contains the Season Wise Record Dataframe
            df['Reward'] += df.apply(lambda x: calculatePointsSeason(x[row['Target']], row['Min Value'], row['Games Required'],
                                                               row['Max Value'], key, x['Position'],
                                                                row['PCC Reward'], x['Games Played']), axis=1)
        else: # For Game wise points
            for (i, main_player) in df.iterrows():
                for (j, game_player) in data.iterrows(): # data contains the Game Wise Record dataframe
                    if main_player['Name'] == game_player['Name']:
                        main_player['Reward'] += calculatePointsGame(main_player[row['Target']], 
                                                                    row['Min Value'], row['Max Value'], 
                                                                    key, main_player['Position'], row['PCC Reward'], 
                                                                    game_player['Starter'], row['Started'])

This function works well for the Season Wise Records, but for the Game Wise, I couldn't come up with any Pandas way to do it (eliminating the need of iteration of two Dataframes). I want some way to,

  • Match the Rows given in the Game Wise Record file with the Season Wise Record file, based upon the Name attribute

  • Send the Values from the Game Wise Record to the Custom Function and the Position of the player from the Season Wise Record (so that, only the specific reward is calculated for the player, e.g. if player is QB, so only QB Rewards will be match with him and etc. There are Excel Sheets for each position rewards)

  • Get the Reward Value back and add it to the Reward in the Season Wise Record against that specific player record.

I previously tried to do it by comparing the Name of the Player in the Season Wise Record with the Game Wise Record, but it didn't work. Is there any Pandas way to solve this issue? (where you don't have to iterate all the rows two times)

pandas python
1 Answers

I hope I understood correctly your intentions. To avoid double for loops, you need to use groupby() method and then apply the desired function to every row of the group; finally the aggregation function (sum()) should be applied to the group. Although you can use the Name as a key for grouping, I recommend to add PlayerID.

The approach needs little preparation:

data = data.join(
    df.reset_index().set_index(['Name', 'Team'], drop=False)[['index','Position']],
    on=['Name','Team'],
    how='left'
).rename({'index':'PlayerID'}, axis=1)

We add 2 columns to data DataFrame, namely Position and PlayerID which is the index of the first DataFrame df. We search for the ID checking Name and Team that still may cause a collision (when there 2 players with identical name in the same team).

When it's done the last part of the code will be like this:

for key, value in positions.items(): # Positions has a list of all the positions 
    for (_, row) in rewards[key].iterrows(): # Rewards is a Dict containing Pandas Dataframes against each position
        if row['Goal Type'] == 'Season':
            if row['Target'] in df.columns:
                df['Reward'] += df.apply(lambda x: calculatePointsSeason(
                    x[row['Target']], row['Min Value'], row['Games Required'],
                    row['Max Value'], key, x['Position'],
                    row['PCC Reward'], x['Games Played']
                ), axis=1)
        else: # For Game wise points
            if row['Target'] in data.columns: # I added these 2 checks because sometimes target is not presented in the columns which raises the error
                df['Reward'] = df['Reward'].add(
                    data.groupby('PlayerID').apply(
                        lambda group: group.apply(lambda game_player: calculatePointsGame(
                            game_player[row['Target']], 
                            row['Min Value'], row['Max Value'], 
                            key, game_player['Position'],
                            row['PCC Reward'], 
                            game_player['Starter'],
                            row['Started']
                        ), axis=1).sum()
                    ),
                    fill_value=0
                )
Boris Silantev0
Related