Python reading JSON in dataframe

Question

I have an SQL database which has two columns. One has the timestamp, the other holds data in JSON format

for example df:

ts                           data
'2017-12-18 02:30:20.553'   {'name':'bob','age':10, 'location':{'town':'miami','state':'florida'}}
'2017-12-18 02:30:21.101'   {'name':'dan','age':15, 'location':{'town':'new york','state':'new york'}}         
'2017-12-18 02:30:21.202'   {'name':'jay','age':11, 'location':{'town':'tampa','state':'florida'}}

If I do the following :

df = df['data'][0]
print (df['name'].encode('ascii', 'ignore'))

I get :

'bob'

Is there a way I can get all of the data correspondings to a JSON key for the whole column?

(i.e. for the df column 'data' get 'name')

'bob'

'dan'

'jay'

Essentially I would like to be able to make a new df column called 'name'

Do you want this to be a part of the SQL select statement? Or could you do for entry in df['data']: print(entry['name'].encode('ascii', 'ignore')) — user3483203
– user3483203, Commented Dec 18, 2017 at 3:19
I should have been more clear, I want the result to be a new df column called 'name' — Mustard Tiger
– Mustard Tiger, Commented Dec 18, 2017 at 3:22
To get all the values for the name column in a list: [entry['name'].encode('ascii', 'ignore') for entry in df['data']], then you have to do something like ALTER TABLE ADD column_name datatype and then insert your values — user3483203
– user3483203, Commented Dec 18, 2017 at 3:26
you can use df["data"]apply(function)to execute function for every row and get your name for every row. — furas
– furas, Commented Dec 18, 2017 at 3:36

Bharath M Shetty · Accepted Answer · 2017-12-18 04:50:19Z

2

You can use json_normalize i.e

pd.io.json.json_normalize(df['data'])['name']

0    bob
1    dan
2    jay
Name: name, dtype: object

answered Dec 18, 2017 at 4:50

Bharath M Shetty

30.6k6 gold badges65 silver badges111 bronze badges

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

Comments

Scott Boston · Accepted Answer · 2017-12-18 04:42:15Z

1

IIUC, lets use apply with lambda function to select value from dictionary by key:

df['data'].apply(lambda x: x['name'])

Output:

0    bob
1    dan
2    jay
Name: data, dtype: object

answered Dec 18, 2017 at 4:42

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

1 Comment

cs95 Over a year ago

.apply(dict.get, args=('name', )) would also work, I think.

Collectives™ on Stack Overflow

Python reading JSON in dataframe

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related