CODEWITHSUNDEEP
pythonpandas
Ele
Ele

Reputation: 553

Aggregate pandas dataframe by event

I have a dataframe:

- event,result 
- 1, 0 
- 1, 1 
- -1, 1
- -1, -1
- -1, -1

Now I want to aggregate to have a sum by event and result to see how many times the event is equal to one of the 3 results: (-1, 0, 1).

I already tried to use the groupby function, but somehow i only get half of the needed solution.

groupby('event').count()

Upvotes: 0

Views: 215

Answers (1)

Craig
Craig

Reputation: 4855

Method 1 - Groupby on both columns

One approach is to add an extra column so that you can group on the two columns of interest:

df.assign(cnt=1).groupby(['event','result']).count()

This creates a new dummy column so that you can use groupby on both the event and result columns. This added column provides the count() method values to aggregate.

The output is:

              cnt
event result     
-1    -1        2
       1        1
 1     0        1
       1        1

Method 2 - crosstab()

Another approach is to use the pandas crosstab() function:

pd.crosstab(df.event, df.result).stack()

This creates a cross-tabulation table for the two columns specified. The stack() method shifts the data so that you have one result per row.

This command returns a Series:

event  result
-1     -1        2
        0        0
        1        1
 1     -1        0
        0        1
        1        1
dtype: int64

Upvotes: 1

Related Questions