python怎么将json文件转为xls文件_Python-如何将JSON文件转换为数据框

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:

from pandas.io.json import json_normalize

data = [

{

'name': {

'first': 'vikash',

'last': 'singh'

},

'age': 27

},

{

'name': {

'first': 'satyam',

'last': 'singh'

},

'age': 14

}

]

df = pd.DataFrame.from_dict(json_normalize(data), orient='columns')

df

Out[8]:

age name.first name.last

0 27 vikash singh

1 14 satyam singh