CODEWITHSUNDEEP
pythonpandas
timothyylim
timothyylim

Reputation: 1527

Aggregate a dataframe and count for each category

I have the following dataframe:

time             city             
2018-05-8        sydney        
2018-05-8        sydney
2018-05-8        melbourne
2018-05-9        sydney
2018-05-9        melbourne

I am trying to get the following:

time             syndey_count  melbourne_count 
2018-05-8        2             1
2018-05-9        1             1

So far I've managed to group by the time then aggregate by the city:

df.groupby('time').agg({'city': 'count'})

but that just gives me:

time             city  
2018-05-8        3             
2018-05-9        2

Upvotes: 3

Views: 89

Answers (3)

BENY
BENY

Reputation: 323276

Just crosstab

pd.crosstab(df.time,df.city)
Out[77]: 
city       melbourne  sydney
time                        
2018-05-8          1       2
2018-05-9          1       1

Upvotes: 1

phi
phi

Reputation: 11704

Another solution

df.groupby(['time', 'city']).size().unstack().add_suffix('_count')

Upvotes: 0

llllllllll
llllllllll

Reputation: 16404

You can use:

In [53]: df.groupby('time').city.value_counts().unstack(level=1).add_suffix('_count')
    ...: 
Out[53]: 
city       melbourne_count  sydney_count
time                                    
2018-05-8                1             2
2018-05-9                1             1

Upvotes: 2

Related Questions