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

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

CodePudding user response：

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

CodePudding user response：

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')['HOMEGOALS'].sum()

HOME
Liverpool    11
Man City      9
Man u         0
Name: HOMEGOALS, dtype: int64

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