CODEWITHSUNDEEP
pythonpandasdataframecount
Sta1995
Sta1995

Reputation: 45

Python Pandas: How can I count the number of times a value appears in a column based upon another column?

This is my pandas dataframe I am referring to.

Basically I would like to be able to display a count on 'crime type' based on 'council'. So for example, where 'council == Fermanagh and omagh' count the different values for each 'crime type' if that makes sense? So burgulary might be equal to 1 whereas, Anti-social behaviour' would be 3 for another 'council'? I then would like to plot these values on a bar graph.

Hope this makes some sense. Any help would be great. Thank you.

Upvotes: 3

Views: 4946

Answers (1)

jezrael
jezrael

Reputation: 862511

I believe you need groupby with size:

df1 = df.groupby(['crime type', 'council']).size().reset_index(name='Count')

EDIT:

df = pd.DataFrame({'crime type':['Anti-social behaviour','Anti-social behaviour',
                                 'Burglary','Burglary'],
                   'council':['Fermanagh and omagh','Belfast','Belfast','Belfast']})


df1 = df.groupby(['council', 'crime type']).size().unstack(fill_value=0)
print (df1)
crime type           Anti-social behaviour  Burglary
council                                             
Belfast                                  1         2
Fermanagh and omagh                      1         0

df1.plot.bar()

Upvotes: 4

Related Questions