1

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!

Improve this question

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.