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?
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
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.
from_records it is same like my answer (from_records is redundant here)