Get all the times an items value comes up in a data frame

user_15007268 0
Viewed 34

So I have a data Frame with a list of teams and their goals. I have googled different ways to solve but I cant find a way that I want it done.

I have tried value_counts() And it seems to get all the Goals for each team I cant find a way to add them together.

HomeTeam      TeamsGoals
Liverpool          4
                   0
                   3
                   6
                   1

  matchdata.groupby("HomeTeam")["FullTimeHomeTeamsGoals"].value_counts()

I have tried many diffrent thing but I cant get the right output

My dataSet looks something like this:

HOME             AWAY               HOMEGOALS     AWAYGOALS
Liverpool       Man City               5             3
Man u           Man City               0             2
LiverPool       Man u                  6             2     
Man u           LiverPool              0             2    
Man City        Man U                  7             4
Man City        Liverpool              2             2

wanted output:

HOME           ToalScoreHome                       
Liverpool       11                            
Man City        9 
Man u           0
pandas dataframe python
2 Answers

Just use groupby and sum

matchdata.groupby('HOME')['HOMEGOALS'].sum()

HOME
LiverPool    6
Liverpool    5
Man City     9
Man u        0
Name: HOMEGOALS, dtype: int64

Or if LiverPool really has a differ case Liverpool then

matchdata.groupby(matchdata['HOME'].str.lower())['HOMEGOALS'].sum()

HOME
liverpool    11
man city      9
man u         0
Name: HOMEGOALS, dtype: int64
user_91778770

The reason that this does not work as you would expect is because LiverPool != Liverpool, so groupby won't do what you expect it to do, which makes sense why. Convert that, and try again:

df.replace({'LiverPool':'Liverpool'},inplace=True)
df.groupby('HOME',as_index=False)['HOMEGOALS'].sum()


        HOME  HOMEGOALS
0  Liverpool         11
1   Man City          9
2      Man u          0

Note you might need to do this for your other values if they are misspellings between them.

user_91673820
Related