Formatting categories of data with pandas in Python

Question

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.

Answer 1

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')