How to convert the value_counts() output into a dataframe?

Question

I have a dataframe like this:

match       team1       team2       winner
1            MI           KKR        MI
2            DD           CSK        DD
3            RCB          DC         RCB.....

What I wanted to calculate is how many times has a team won against another team in the tournament. Like for MI vs KKR:

MI:10

KKR:5

So I have written a function like this:

def comparator(team1):
    mt1=matches[((matches['team1']==team1)|(matches['team2']==team1))]
    teams=['MI','KKR','RCB','DC','CSK','RR','DD','GL','KXIP','SRH','RPS','KTK','PW']
    teams.remove(team1)
    opponents=teams.copy()
    for i in opponents:
        mt2=mt1[(((mt1['team1']==i)|(mt1['team2']==i)))&((mt1['team1']==team1)|(mt1['team2']==team1))].winner.value_counts()
        print(mt2)
comparator('MI')

Now in the function, the mt2 prints out the correct values for the respective wins for team1 and team2. The output is like this:

MI     13
KKR     5
Name: winner, dtype: int64
MI     11
RCB     8
Name: winner, dtype: int64

Now the output is correct but the format is not appropriate. I want to convert the following output into a dataframe.

I tried appending the the values into a list but it is not working as the lines Name: winner, dtype: int64 is also getting appended into the list.

How do I convert it into a dataframe?

You mean you simply want a df instead of a series? Call to_frame() on the result — EdChum
– EdChum, Commented Jun 29, 2017 at 7:44

jezrael · Accepted Answer · 2017-06-29 07:50:20Z

1

I think you need:

If need index as column add Series.reset_index:

mask = (((mt1['team1']==i)|(mt1['team2']==i)))&((mt1['team1']==team1)|(mt1['team2']==team1))
mt2 = mt1.loc[mask, 'winner'].value_counts().reset_index()

Or if need Series convert to one column DataFrame add Series.to_frame:

mask = (((mt1['team1']==i)|(mt1['team2']==i)))&((mt1['team1']==team1)|(mt1['team2']==team1))
mt2 = mt1.loc[mask, 'winner'].value_counts().to_frame()

Also is better use loc with boolean mask and define column.

edited Jun 29, 2017 at 7:50

answered Jun 29, 2017 at 7:45

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jezrael Over a year ago

Glad can help! And nice day!

Maarten Fabré · Accepted Answer · 2017-06-29 08:46:01Z

You can simplify your search a bit, or make it more readable anyway

def my_comp(df, team):
    matches_with_team = df[(df[['team1', 'team2']] == team).any(axis=1)]
    other_teams = (set(matches_with_team['team1']) ^ set(matches_with_team['team2'])) - {team}
    comparison_df = pd.DataFrame(index=other_teams, columns=['wins', 'losses'])
    comparison_df.index.name = 'opponent'
    for opponent in other_teams:
        matches_against_opponents = matches_with_team[(matches_with_team[['team1', 'team2']] == opponent).any(axis=1)]
        winners = matches_against_opponents['winner'].value_counts().reindex([team, opponent])
        # print(winners)
        comparison_df.loc[opponent] = [winners[team], winners[opponent]]
    return comparison_df.fillna(0).astype(int)

my_comp(df, 'MI')

    wins    losses
opponent        
KKR     1.0     0

Now you can make 1 giant DataFrame to cover all results

all_teams = sorted(set(df['team1']) ^ set(df['team2']))

all_teams

['CSK', 'DC', 'DD', 'KKR', 'MI', 'RCB']

When run with this input:

    team1   team2   winner
match           
1   MI      KKR     MI
2   DD      CSK     DD
3   RCB     DC      RCB
4   RCB     CSK     RCB

pd.concat((my_comp(df, team) for team in teams), keys=teams).groupby(level=[0, 1]).sum()

                wins    losses
    opponent        
CSK     DD      0       1
        RCB     0       1
DC      RCB     0       1
DD      CSK     1       0
KKR     MI      0       1
MI      KKR     1       0
RCB     CSK     1       0
        DC      1       0

Afshin Amiri · Accepted Answer · 2023-04-27 12:24:27Z

0

Though not exactly your answer, the way to convert valu_counts to data frame was my question too. I found the easiest way is "

pd.DataFrame(df['class'].value_counts()).reset_index()

answered Apr 27, 2023 at 12:24

Afshin Amiri

3,6231 gold badge22 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to convert the value_counts() output into a dataframe?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related