Group and count unique values in Python pandas dataframe

Question

I have a dataframe of over 33,000 rows which I'd like to simplify:

                   Crime type
GeographyCode                              
E01006687          Burglary
E01007229          Anti-social behaviour
E01007229          Anti-social behaviour
E01007229          Anti-social behaviour
E01007229          Burglary
E01007229          Other theft
E01007229          Other theft
E01007229          Shoplifting
E01007229          Theft from the person
E01007230          Anti-social behaviour
E01007230          Anti-social behaviour
E01007230          Anti-social behaviour
E01007230          Anti-social behaviour
E01007230          Anti-social behaviour
E01007230          Anti-social behaviour
...

There are 207 unique values of 'GeographyCode' and 12 unique values of 'Crime type'.

I'd like to make a new dataframe which has 207 rows and 12 columns plus the 'GeographyCode' index column, with each column representing a crime type, and containing a count of all occurances of that crime type within the GeographyCode.

Something like this:

                Burglary   Anti-social    Theft   Shoplifting   etc...
GeographyCode
E01006687       1          3              9       5             ...
E01007229       1          3              2       1             ...
E01007230       0          6              12      5             ...
...

I've tried a few things, but because there are no numeric values I'm finding it really difficult to get what I need.

Alex Riley · Accepted Answer · 2015-11-16 10:09:43Z

6

You could use crosstab to compute this:

>>> pd.crosstab(df.index, df['Crime type'])
Crime type      Anti-social behaviour  Burglary  Other theft  Shoplifting  ...

E01006687                           0         1            0            0
E01007229                           3         1            2            1
E01007230                           6         0            0            0

answered Nov 16, 2015 at 10:09

Alex Riley

178k46 gold badges274 silver badges247 bronze badges

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

2 Comments

Cobain Over a year ago

You actual legend! This has been doing my head in all weekend and it's solved in one function. Thanks!

Alex Riley Over a year ago

No problem - glad it helped!

Collectives™ on Stack Overflow

Group and count unique values in Python pandas dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related