Converting a nested array into a pandas dataframe in python

Question

I'm attempting to convert several dictionaries contained in an array to a pandas dataframe. The dicts are saved as such:

[[{u'category': u'anti-social-behaviour',u'location': {u'latitude': u'52.309886',
u'longitude': u'0.496902'},u'month': u'2015-01'},{u'category': u'anti-social-behaviour',u'location': {u'latitude': u'52.306209',
u'longitude': u'0.490475'},u'month': u'2015-02'}]]

I'm trying to format my data to the format below:

     Category      Latitude   Longitude
0    anti-social   524498.597 175181.644
1    anti-social   524498.597 175181.644
2    anti-social   524498.597 175181.644
.    ...           ...
.    ...           ...
.    ...           ...

I've tried to force the data into a dataframe with the below code but it doesn't produce the intended output.

for i in crimes:
    for x in i:
        print pd.DataFrame([x['category'], x['location']['latitude'], x['location']['longitude']])

I'm very new to Python so any links/tips to help me build this dataframe would be highly appreciated!

brianpck · Accepted Answer · 2016-10-11 16:13:55Z

You are on the right track, but you are creating a new dataframe for each row and not giving the proper columns. The following snippet should work:

import pandas as pd
import numpy as np

crimes = [[{u'category': u'anti-social-behaviour',u'location': {u'latitude': u'52.309886',
u'longitude': u'0.496902'},u'month': u'2015-01'},{u'category': u'anti-social-behaviour',u'location': {u'latitude': u'52.306209',
u'longitude': u'0.490475'},u'month': u'2015-02'}]]

# format into a flat list
formatted_crimes = [[x['category'], x['location']['latitude'], x['location']['longitude']] for i in crimes for x in i]

# now pass the formatted list to DataFrame and label the columns
df = pd.DataFrame(formatted_crimes, columns=['Category', 'Latitude', 'Longitude'])

The result is:

                Category   Latitude Longitude
0  anti-social-behaviour  52.309886  0.496902
1  anti-social-behaviour  52.306209  0.490475

Collectives™ on Stack Overflow

Converting a nested array into a pandas dataframe in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related