How to take column data from one DF, divide it by the mean of another df's column data, and then add it to a new df

First, i read on how to post a good question as suggested, and i will try my best here. I just popped positive for COVID and I feel like crap, but coding for me never stops. Bare with me. I have spent a lot of my day trying to find the answer and toy with this myself.

Now I need to replace those values in each cell of each column based on the player's stats cell value divided by the team's defense cell value. Only if the player's team matches the team's defense team name.

df1 = pd.DataFrame([['Atlanta Hawks', 'ATL', 12, 10, 3], ['Boston Celtics', 'BOS', 42, 20, 13]],                 columns=['TEAM_NAME', 'TEAM_ABBREVIATION', 'stat1', 'stat2', 'stat3'])    df2 = pd.DataFrame([['Player One', 'ATL', 20, 14, 31], ['Player Two', 'BOS', 62, 40, 33]],                     columns=['PLAYER_NAME', 'TEAM_ABBREVIATION', 'stat1', 'stat2', 'stat3'])  

I am using a team defense dictionary that pulls each team's defensive data from the worksheets in the csv and stores that value with the team's abbreviation as the key.

team_dict = {'ATL': [team1.df], 'BOS': [team2.df]}    for column in df2.columns[2:]:  for k, v in team_dict.items():      #  if df2['TEAM_ABBREVIATION'] == k, then df2[column] / v[column].mean()      df2[column] = df2[column] / v[column]   

I know there is a more in line way to express that with a for and if statement, but i am still new to pandas and python.

I would appreciate any help with this one. I feel like I am close, but i just can't get it.

edit* I know that i could break this down and do it cell by cell for df2 while matching the conditions. That would take multiple lines and seems it would be a heck of a lot slower.

Update* I came up with a robust solution that works, but I know there has to be a more readable/faster way

new_list = []  for k, v in team_dict.items():      a = df2.loc[df2['TEAM_ABBREVIATION'] == k]      for column in a.columns[4:]:          a[column] = a[column] / v[column].mean()      new_list.append(a)    new_df = pd.DataFrame()  for df in new_list:      new_df = new_df.append(df)  new_df.sort_values('PLAYER_NAME', inplace=True)  

https://stackoverflow.com/questions/66112047/how-to-take-column-data-from-one-df-divide-it-by-the-mean-of-another-dfs-colum February 09, 2021 at 10:14AM