pythonjson中list操作_python - 提取Python中的嵌套json / list - SO中文参考 - www.soinside.com...

import pandas as pd

import re

data = [{u'week': 45, u'value': {u'team': u'accounts', u'KPI': 4, u'Mgr': 1, u'change': 0, u'risk': 1000, u'subGroups': [{u'team': u'HR', u'KPI': 4, u'Mgr': 1, u'change': 0, u'risk': 2000, u'subGroups': [{u'team': u'Marketing', u'KPI': 4, u'Mgr': 1, u'change': 0, u'risk': 3000, u'subGroups': []}]}]}},

{u'week': 44, u'value': {u'team': u'accounts', u'KPI': 4, u'Mgr': 1, u'change': 0, u'risk': 4000, u'subGroups': [{u'team': u'HR', u'KPI': 4, u'Mgr': 1, u'change': 0, u'risk': 5000, u'subGroups': [{u'team': u'Marketing', u'KPI': 4, u'Mgr': 1, u'change': 0, u'risk': 6000, u'subGroups': []}]}]}},

{u'week': 34, u'value': {u'team': u'accounts', u'KPI': 29, u'Mgr': 1, u'change': 0, u'risk': 20000, u'subGroups': [{u'team': u'HR', u'KPI': 29, u'Mgr': 1, u'change': 0, u'risk': 20000, u'subGroups': [{u'team': u'Marketing', u'KPI': 29, u'Mgr': 1, u'change': 0, u'risk': 20000, u'subGroups': []}]}]}}]

def flatten_json(y):

out = {}

def flatten(x, name=''):

if type(x) is dict:

for a in x:

flatten(x[a], name + a + '_')

elif type(x) is list:

i = 0

for a in x:

flatten(a, name + str(i) + '_')

i += 1

else:

out[name[:-1]] = x

flatten(y)

return out

flat = flatten_json(data)

columns_list = list(flat.keys())

rows = {}

for item in columns_list:

row_idx = re.findall(r'(\d+)\_', item )[0]

column = re.findall(r'\d+\_(.*)', item )[0]

row_idx = int(row_idx)

value = flat[item]

if row_idx in rows:

rows[row_idx][column] = value

else:

rows[row_idx] = {}

rows[row_idx][column] = value

results = pd.DataFrame()

for idx, row in rows.items():

results = results.append(pd.DataFrame(row, index=[idx]), sort=True)

输出：

print (results.to_string())

value_KPI value_Mgr value_change value_risk value_subGroups_0_KPI value_subGroups_0_Mgr value_subGroups_0_change value_subGroups_0_risk value_subGroups_0_subGroups_0_KPI value_subGroups_0_subGroups_0_Mgr value_subGroups_0_subGroups_0_change value_subGroups_0_subGroups_0_risk value_subGroups_0_subGroups_0_team value_subGroups_0_team value_team week

0 4 1 0 1000 4 1 0 2000 4 1 0 3000 Marketing HR accounts 45

1 4 1 0 4000 4 1 0 5000 4 1 0 6000 Marketing HR accounts 44

2 29 1 0 20000 29 1 0 20000 29 1 0 20000 Marketing HR accounts 34