# Formatting categories of data with pandas in Python

I have around 9500 cities that I want to put in a pandas dataframe, then store in a file for later use. For example, below, these are 3 cities that I have data for. Some range in size based on year. Lovell_Wyoming only has 9 years of data points, corresponding to the years, as opposed to the 15 that Wheatland and Worland have.

My original idea was putting the quantitative data (Arson..Year) in a map, then putting the city name, as a key, into a larger map, with that quantitative data. Building the larger map that way. Then, converting the map to a dataframe, then to a csv. I am a bit inexperienced with pandas so I am not sure how to do this correctly, if this is even the best way to do it.

At the end of the day, I would like this data in a csv file that is easily accessible by loading it into a dataframe and calling on whatever value I need.

City 'Lovell_Wyoming'
Arson [0, 0, 0, 0, 0, 0, 0, 1, 0]
Assaults [6, 6, 3, 4, 3, 28, 3, 2, 2]
Auto_thefts [1, 1, 1, 0, 0, 1, 2, 0, 1]
Burglaries [6, 11, 5, 2, 0, 15, 11, 7, 7]
Murders [0, 0, 0, 0, 1, 0, 0, 0, 0]
Rapes [0, 0, 3, 0, 0, 1, 1, 0, 1]
Robberies [0, 0, 0, 0, 0, 0, 0, 1, 0]
Thefts [23, 49, 35, 39, 28, 37, 54, 35, 10]
Year [2002, 2003, 2005, 2006, 2007, 2008, 2009, 2010, 2014]

City 'Wheatland_Wyoming'
Arson [0, 0, 0, 0, 0, 1, 2, 0, 0, 0, 0, 0, 0, 0, 0]
Assaults [9, 2, 6, 5, 6, 6, 2, 4, 2, 4, 3, 11, 5, 4, 8]
Auto_thefts [4, 8, 3, 3, 4, 4, 5, 3, 4, 6, 4, 8, 12, 7, 3]
Burglaries [17, 17, 14, 9, 10, 17, 12, 26, 51, 12, 15, 21, 32, 31, 13]
Murders [1, 0, 0, 0, 0, 0, 0, 0, 0, 5, 0, 0, 0, 0, 0]
Rapes [0, 0, 0, 4, 2, 1, 2, 0, 2, 1, 1, 0, 2, 0, 0]
Robberies [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
Thefts [109, 95, 146, 81, 108, 100, 82, 85, 106, 128, 48, 85, 66, 56, 47]
Year [2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016]

City 'Worland_Wyoming'
Arson [0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
Assaults [12, 17, 19, 17, 11, 15, 16, 2, 9, 1, 4, 7, 2, 1, 3]
Auto_thefts [2, 1, 2, 1, 1, 8, 1, 1, 1, 0, 1, 0, 0, 0, 1]
Burglaries [6, 10, 10, 10, 9, 10, 10, 0, 6, 1, 0, 2, 0, 11, 18]
Murders [0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
Rapes [1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 3]
Robberies [0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
Thefts [44, 41, 47, 29, 30, 25, 27, 27, 23, 30, 36, 45, 54, 46, 43]
Year [2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016]


I apologize in advance if the way I formatted this is a bit weird! Please let me know if you would like any more information.

• How you structure you dataframe really depends on the kinds of queries you expect to do. Looking at this data I would use the rows as the crimes, and the columns as the city and year. What do you think? – JahKnows Apr 18 at 3:44
• I do like that approach, a lot. I would just like to be able to touch every single data point. I will most likely be doing queries like calling every single city, year, and crime, ex: df.Wheatland_Wyoming.2016.Assault - to retrieve the assault value of 8, and so on. – navon Apr 18 at 3:53

This is how I would do it. However, a DataFrame can be structured in a number of way which best suits your needs. I believe that this method allows for the greatest flexibility because you can easily use grouping functions to restructure this format on the go.

First you need to set up your data in a way that is compatible with Python. I use a dictionary of dictionaries

cities = {'Lovell_Wyoming':
{'Crimes':
{
'Arson': [0, 0, 0, 0, 0, 0, 0, 1, 0],
'Assaults': [6, 6, 3, 4, 3, 28, 3, 2, 2] ,
'Auto_thefts': [1, 1, 1, 0, 0, 1, 2, 0, 1] ,
'Burglaries': [6, 11, 5, 2, 0, 15, 11, 7, 7] ,
'Murders': [0, 0, 0, 0, 1, 0, 0, 0, 0] ,
'Rapes': [0, 0, 3, 0, 0, 1, 1, 0, 1] ,
'Robberies': [0, 0, 0, 0, 0, 0, 0, 1, 0] ,
'Thefts': [23, 49, 35, 39, 28, 37, 54, 35, 10]
},
'Years': [2002, 2003, 2005, 2006, 2007, 2008, 2009, 2010, 2014]
},

'Wheatland_Wyoming':
{'Crimes':
{
'Arson': [0, 0, 0, 0, 0, 1, 2, 0, 0, 0, 0, 0, 0, 0, 0] ,
'Assaults': [9, 2, 6, 5, 6, 6, 2, 4, 2, 4, 3, 11, 5, 4, 8] ,
'Auto_thefts': [4, 8, 3, 3, 4, 4, 5, 3, 4, 6, 4, 8, 12, 7, 3] ,
'Burglaries': [17, 17, 14, 9, 10, 17, 12, 26, 51, 12, 15, 21, 32, 31, 13] ,
'Murders': [1, 0, 0, 0, 0, 0, 0, 0, 0, 5, 0, 0, 0, 0, 0] ,
'Rapes': [0, 0, 0, 4, 2, 1, 2, 0, 2, 1, 1, 0, 2, 0, 0] ,
'Robberies': [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,
'Thefts': [109, 95, 146, 81, 108, 100, 82, 85, 106, 128, 48, 85, 66, 56, 47]
},
'Years': [2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016]
}
}


Then compile this data into rows of your DataFrame

data = []
for city in cities:
for ix, year in enumerate(cities[city]['Years']):
for crime in cities[city]['Crimes']:
temp = {'City':city,
'Crime':crime,
'Year':year,
'Count':cities[city]['Crimes'][crime][ix]}
data.append(temp)


Then into the DataFrame structure as.

import pandas as pd
df = pd.DataFrame(data=data)
df


# Queries

You can then query this DataFrame in a number of ways for example if you want to know arsons in 2002 you would do

df[(df['Crime']=='Arson') & (df['Year']==2002)]


# Counting

You can count the number of arsons throughout the years as

df[(df['Crime']=='Arson')].groupby(['City'])['Count'].agg('sum')


City
Lovell_Wyoming 1
Wheatland_Wyoming 3
Name: Count, dtype: int64

# Writing your DataFrame to a CSV file

This can be done directly as

df.to_csv('filename.csv')

• I will elaborate further. – JahKnows Apr 18 at 4:47
• very nice dude! +1 from my side – Toros91 Apr 18 at 5:53
• Amazing work! Just implemented this, worked like a charm! I did have to initially build my data how you did, with the nested dictionaries, after that it turned out beautifully. Thank you for the visualizations! It really helped me understand it way better. – navon Apr 18 at 6:09
• @navon, my pleasure, good luck! – JahKnows Apr 18 at 6:11
• Awesome explanation (+1) just to add, you can use sdf.query also in the queries section. Would look cleaner.. – Aditya Apr 18 at 6:50