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