Load Json data multiple values in a column using pandas

user_4014955 0
Viewed 535

My json file

{
  "Domain": "Hello",
  "Points": 0,
  "Search Name": "wonderful1 hello",
  "UserId": "wonder1",
  "UserName": "Wonderful1 Hello",
  "UserRole": "CONSUMER","CONS"
}

My code:

with open('e:\\data\\datafile.json') as f: 
    content = f.read()
df=pd.read_json('[' + content.replace('}\n', '},') + ']')

unable to load the into dataframe. If i get one more than one value in UserRole column. I was not able to load the data? Can you throw some lights on this?

pandas json
2 Answers

I believe you need json.load for convert json to dicts or list of dicts first:

import json
from pandas.io.json import json_normalize    

with open('data.json') as data_file:    
    data = json.load(data_file)

And then if necessary json_normalize:

df = json_normalize(data)
print (df)
  Domain  Points       Search Name   UserId          UserName  UserRole
0  Hello       0  wonderful1 hello  wonder1  Wonderful1 Hello  CONSUMER

Or if there is only one dictionary in data like in sample:

df = pd.DataFrame([data])
print (df)
  Domain  Points       Search Name   UserId          UserName  UserRole
0  Hello       0  wonderful1 hello  wonder1  Wonderful1 Hello  CONSUMER

Or if there are list of dictionaries:

df = pd.DataFrame(data)

EDIT:

Because not valid json file one possible solution is use yaml.load:

import yaml
from pandas.io.json import json_normalize    

with open('data.json') as data_file:    
    data = yaml.load(data_file, Loader=yaml.FullLoader)  

print (data)
{'Domain': 'Hello', 'Points': 0, 'Search Name': 'wonderful1 hello',
 'UserId': 'wonder1', 'UserName': 'Wonderful1 Hello', 
 'UserRole': 'CONSUMER', 'CONS': None}

df = json_normalize(data)
print (df)
  Domain  Points       Search Name   UserId          UserName  UserRole  CONS
0  Hello       0  wonderful1 hello  wonder1  Wonderful1 Hello  CONSUMER  None
user_29010020

Lets try a very simple way

import json
import pandas as pd

with open('data.json', 'r') as data_file:  
    content = data_file.read()

data = json.loads(content)
print(type(data)) # it should be a dict
df = pd.DataFrame.from_records([data])

I've checked the code. It works well.

user_35406830
Related