Reading Json file and converting it to columns in python

Question

I am trying to read this json file in python using this code (I want to have all the data in a data frame):

import numpy as np
import pandas as pd
import json 
from pandas.io.json import json_normalize

df = pd.read_json('short_desc.json')
df.head()

Data frame head screenshot

using this code I am able to convert only the first row to separated columns:

json_normalize(df.short_desc.iloc[0])

First row screenshot

I want to do the same for whole df using this code:

df.apply(lambda x : json_normalize(x.iloc[0]))

but I get this error:

ValueError: If using all scalar values, you must pass an index

What I am doing wrong?

Thank you in advance

Brad Campbell · Accepted Answer · 2019-02-14 16:38:25Z

1

After reading the json file with json.load, you can use pd.DataFrame.from_records. This should create the DataFrame you are looking for.

wih open('short_desc.json') as f:
    d = json.load(f)

df = pd.DataFrame.from_records(d)

edited Feb 14, 2019 at 16:38

answered Feb 14, 2019 at 16:31

Brad Campbell

3,1112 gold badges25 silver badges25 bronze badges

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

4 Comments

Hamid Over a year ago

It gives me a dataset which has a Json column, I am trying to convert the json to columns. Look at this i.sstatic.net/aAuY6.png, I want to have seprated columns for when, what and who

Brad Campbell Over a year ago

What does your json look like?

Brad Campbell Over a year ago

If you want to use json_normalize, pass it d from my answer above. Without seeing your JSON data it's hard to say what's going on.

Hamid Over a year ago

here is the JSON file :) github.com/ansymo/msr2013-bug_dataset/blob/master/data/v02/…

Collectives™ on Stack Overflow

Reading Json file and converting it to columns in python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related