I have two data frames

wizards = {player_id': ["3", "4", "0", "9"],
    'name':["beal", "avdija", "hachimura", "dinwiddie"],
    'total points':}
stat_sheet = {jersey_number': ["9", "9" , "0", "3", "4", "0", "9", "9", , "9" , "0", , "3", 
"4", "0", "9"], 'total points':[40, 20, 12, 14, 55, 67, 10, 22, 22, 5, 3, 12, 5, 1]}
wiz_df = pd.DataFrame(wizards)
weeklystats_df = pd.DataFrame(stat_sheet)

I need to make sure that I add up all the points for each player ex:

Beal scored in total 17 points so for his section within the wiz_df it would be 17 points for his row within the wiz_df

So this would need to be done for each player as the unique ID is the player_id and jersey_number

I tried writing a multiple nested for loop which wouldn't work and I tried joining the tables which also did not work either. Kind of stuck on this would appreciate all the help I can get.

CodePudding user response：

You can combine .groupby on weeklystats_df and then .merge with wiz_df:

x = wiz_df.merge(
    weeklystats_df.groupby("jersey_number").sum(),
    left_on="player_id",
    right_index=True,
    how="left",
)
print(x)

Prints:

  player_id       name  total points
0         3       beal            17
1         4     avdija            67
2         0  hachimura            89
3         9  dinwiddie           115

wiz_df used:

  player_id       name
0         3       beal
1         4     avdija
2         0  hachimura
3         9  dinwiddie

weeklystats_df used:

   jersey_number  total points
0              9            40
1              9            20
2              0            12
3              3            14
4              4            55
5              0            67
6              9            10
7              9            22
8              9            22
9              0             5
10             3             3
11             4            12
12             0             5
13             9             1