Sum multiple columns to a two column output/dataframe

user_19182946 0
Viewed 56

I am exploring a rugby dataset where I am stuck on a bit of an issue. The dataset contains matches played in the the Six Nations over a year, and the tries are put into two separate columns, which is "Home try's" and "Away try's". I am trying to print out the output of the teams with the largest amount of try`s. Below is an example of how the dataset looks.

Home Away Home try's Away try's
England France 3 2
Ireland Scotland 1 4
France Ireland 1 0
Scotland England 1 3

I have tried to put the home and away columns to a single column as a list and use the arguments df.explode and df.groupby argument to create a dataframe, but it doesnt work. Any tips on how I could solve this?

The ideal output would be:

England Scotland France Ireland
6 5 3 1
pandas dataframe python
3 Answers

How about this?

series_home = df.groupby('Home').sum()["Home try's"]
series_away = df.groupby('Away').sum()["Away try's"]
combined_series = series_home + series_away
combined_df = pd.DataFrame(combined_series).transpose()
user_56790470

I was able to do this in a way combining the two DFs and using a pivot.

df_Home = df[['Home', 'Home_Try']]
df_Away = df[['Away', 'Away_Try']]
df_Away.columns = [['Team', 'Tries']]
df_Home.columns = [['Team', 'Tries']]
df2 = pd.concat([df_Home, df_Away])
df2.columns = ['Team', 'Tries']
df_pivot = pd.pivot_table(df2, values='Tries', columns=['Team'], aggfunc=np.sum)
df_pivot
user_177504310

Set the "Home" column as an index and sort the dataframe. Create another frame by copying the "Away try's" column into it and setting the "Away" column as the index. I copy the "Home try's" column to the new frame. I count the amounts.

df = df.set_index("Home").sort_index()
df1  = pd.DataFrame(df["Away try's"].values, index=df["Away"]).sort_index()
df1["Home try's"] = df["Home try's"]

print(df1.sum(axis=1))

Output

Away
England     6
France      3
Ireland     1
Scotland    5
dtype: int64
user_119850880
Related