2

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.

Sta1995
  • 35
  • 1
  • 6
  • Possible duplicate of [Python: get a frequency count based on two columns (variables) in pandas dataframe](https://stackoverflow.com/questions/33271098/python-get-a-frequency-count-based-on-two-columns-variables-in-pandas-datafra) – ababuji Dec 03 '17 at 16:50

1 Answers1

3

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()
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Thank for very much, this is exactly what I was looking for. Following that, say for example, I wanted to display in a graph (bar chart) the count of each type of crime per each council area. So, 'Fermanagh and Omagh' is the council area and 'theft','murder' and 'robbery' are the three crime types associated with this area. How could I make bar graph to display the count of each crime type for this council? Would it be impossible to have all these information on one chart for all councils or would I need a seperate one for each? Hope this makes sense. – Sta1995 Dec 03 '17 at 16:37
  • 1
    Yes, it is possible. Do you need `council` in `x` or `y` axis? – jezrael Dec 03 '17 at 16:39
  • Thank you, I would need it in the x axis – Sta1995 Dec 03 '17 at 16:42
  • 1
    You are welcome! you can also upvote answer. thanks. – jezrael Dec 03 '17 at 16:59