Sum pandas columns by row and selecting comparative by Qgrid row

Question

import pandas as pd
import seaborn as srn 
import statistics as sts

dataset = pd.read_excel('/content/drive/MyDrive/Data science /BRA 2020.xlsx')

dataset.head()

dataset = dataset.drop ('League', axis = 1)
dataset = dataset.drop ('Country', axis = 1)
dataset = dataset.drop ('Time', axis = 1)
dataset = dataset.drop ('Date', axis = 1)
dataset = dataset.drop ('Season', axis = 1)
dataset = dataset.drop ('PH', axis = 1)
dataset = dataset.drop ('PD', axis = 1)
dataset = dataset.drop ('PA', axis = 1)

display(dataset)

dataset.columns = ["Home","Away","G Home", "G Away", "Resultado"]

dataset.head()

M_G_Home = dataset[['Home','G Home']].groupby('Home').mean()
M_G_Home.rename(columns={'G Home':'M_G_Home'}, inplace=True)
dataset.head(20)

I took the medias :

dataset['M_G_Home']=dataset.groupby("Home")["G Home"].transform("mean")
dataset.head(20)

I averaged out the team

M_G_Awya = dataset[['Away','G Away']].groupby('Away').mean()
M_G_Awya.rename(columns={'G Away':'M_G_Awya'}, inplace=True)

dataset['M_G_Awya']=dataset.groupby("Away")["G Away"].transform("mean")
dataset.head(20)

However I’m having difficulty adding these two columns per row and does not leave the final result .

https://www.football-data.co.uk/brazil.php

Answer 1

Importing the libs

import pandas as pd
import seaborn as srn 
import statistics as sts

Loading the data

dataset = pd.read_excel('/content/drive/MyDrive/Data science /BRA 2020.xlsx')

Excluding the columns

dataset.drop(columns=['League','Country', 'Time', 'Date',
                       'Season', 'PH','PD','PA', 'MaxH',
                       'MaxD', 'MaxA', 'AvgH', 'AvgD', 'AvgA'], inplace=True)

Renaming the columns

dataset.columns = ["Home","Away","G Home", "G Away", "Resultado"]

Averaging

dataset['M_G_Home'] = dataset.groupby('Home')['G Home'].transform('mean')
dataset['M_G_Away'] = dataset.groupby('Home')['G Away'].transform('mean')

Calculating the sum

dataset['S_G_Home'] = dataset.groupby('Home')['G Home'].transform('sum')
dataset['S_G_Away'] = dataset.groupby('Home')['G Away'].transform('sum')

Displaying the data frame

display(dataset)