How can I convert a JSON File as such into a dataframe to do some transformations.
For Example if the JSON file reads:
{"FirstName":"John",
"LastName":"Mark",
"MiddleName":"Lewis",
"username":"johnlewis2",
"password":"2910"}
How can I convert it to a table like such
Column -> FirstName | LastName | MiddleName | username | password
Row -----> John | Mark |Lewis | johnlewis2 |2910
Creating dataframe from dictionary object.
import pandas as pd
data = [{'name': 'vikash', 'age': 27}, {'name': 'Satyam', 'age': 14}]
df = pd.DataFrame.from_dict(data, orient='columns')
df
Out[4]:
age name
0 27 vikash
1 14 Satyam
If you have nested columns then you first need to normalize the data:
data = [
{
'name': {
'first': 'vikash',
'last': 'singh'
},
'age': 27
},
{
'name': {
'first': 'satyam',
'last': 'singh'
},
'age': 14
}
]
df = pd.DataFrame.from_dict(pd.json_normalize(data), orient='columns')
df
Out[8]:
age name.first name.last
0 27 vikash singh
1 14 satyam singh
Source:
import pandas as pd
print(pd.json_normalize(your_json))
This will Normalize semi-structured JSON data into a flat table
Output
FirstName LastName MiddleName password username
John Mark Lewis 2910 johnlewis2
jsondata = '{"0001":{"FirstName":"John","LastName":"Mark","MiddleName":"Lewis","username":"johnlewis2","password":"2910"}}'
import json
import pandas as pd
jdata = json.loads(jsondata)
df = pd.DataFrame(jdata)
print df.T
This should look like this:.
FirstName LastName MiddleName password username
0001 John Mark Lewis 2910 johnlewis2
There are 2 inputs you might have and you can also convert between them.
example: [{"":{"...
The pd.DataFrame() needs a listOfDictionaries as input.
example: '{"":{"...
If you have jsonStr, you need an extra step to listOfDictionaries first. This is obvious as it is generated like:
jsonStr = json.dumps(listOfDictionaries)
Thus, switch back from jsonStr to listOfDictionaries first:
listOfDictionaries = json.loads(jsonStr)
Using this where file1.json is the file name can read the json files directly from the json file.
import pandas as pd
df = pd.read_json("file1.json")
