Extracting JSON in pandas column to separate columns while handling rows with None

Question

I have a pandas dataframe called df that contains twitter tweets created by taking the twitter json and loading it into the dataframe. I am trying to extract the interesting information. The coordinates column is mostly None, but sometimes it contains GeoJSON in this format:

{'coordinates': [21.425775, 8.906141], 'type': 'Point'}

Here 21.425775 refers to the longitude and 8.906141 refers to the latitude. I would like to extract the latitude and longitude into separate columns. Unfortunately my pandas skills are more towards the beginner level, so I am not sure how to do find and substring; also there seems to be better ways as suggested in this question which I don't fully understand.

An example of the dataframe is:

  coordinates
0 None
1 {'coordinates': [21.425775, 8.906141], 'type': 'Point'}

How can I extract the information in the nested JSON column into separate pandas columns while gracefully handling the None values in the other rows?

{'coordinates': [21.425775, 8.906141], 'type': 'Point'} is a sample, another sample would be None — Superdooperhero
– Superdooperhero, Commented Jul 20, 2018 at 7:29
Can you show a print of df? I am not able to understand how the column is... — Rakesh
– Rakesh, Commented Jul 20, 2018 at 7:31

Rakesh · Accepted Answer · 2018-07-20 07:41:26Z

1

If your 'coordinates' is a list then you can use tolist() with pd.DataFrame

Ex:

import pandas as pd
import numpy as np

df = pd.DataFrame({'coordinates': [{'coordinates': [21.425775, 8.906141], 'type': 'Point'}, None]})
df['temp'] = df['coordinates'].apply(lambda x: x.get("coordinates") if x else [np.nan, np.nan]).dropna()
df[['longitude','latitude']] = pd.DataFrame(df.temp.values.tolist(), index= df.index)
df.drop('temp', axis=1, inplace=True)
print(df)

Output:

                                         coordinates  longitude  latitude
0  {u'type': u'Point', u'coordinates': [21.425775...  21.425775  8.906141
1                                               None        NaN       NaN

edited Jul 20, 2018 at 7:41

answered Jul 20, 2018 at 7:07

Rakesh

82.9k17 gold badges85 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Superdooperhero Over a year ago

Gives me ValueError: Columns must be same length as key, presumably because of the 'type': 'Point' part.

Superdooperhero Over a year ago

Or possibly the None part

Rakesh Over a year ago

Updated snippet

Superdooperhero Over a year ago

Thanks! Works nice. Why does it need the dropna part?

Rakesh Over a year ago

You are welcome :) and you are correct...you do not need dropna()

Collectives™ on Stack Overflow

Extracting JSON in pandas column to separate columns while handling rows with None

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related