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
up vote 4 down vote accepted

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

enter image description here


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

enter image description here

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
  • 3
    very nice dude! +1 from my side – Toros91 Apr 18 at 5:53
  • 3
    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

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.