Hamid
Hamid

Reputation: 25

Reading Json file and converting it to columns in python

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

Upvotes: 1

Views: 1159

Answers (1)

Brad Campbell
Brad Campbell

Reputation: 3091

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)

Upvotes: 1

Related Questions