python将json数据转化为dataframe

原始数据下

{"data":[{"小明": {"中国合伙人2": 5.0, "太平轮": 3.0, "荒野猎人": 4.5, "老炮儿": 5.0, "我的少女时代": 3.0, "肖洛特烦恼": 4.5, "海王": 5.0}},
{"小红":{"小时代4": 4.0, "荒野猎人": 3.0, "我的少女时代": 5.0, "肖洛特烦恼": 5.0, "海王": 3.0, "后会无期": 3.0}},
{"小阳": {"小时代4": 2.0, "中国合伙人2": 5.0, "我的少女时代": 3.0, "老炮儿": 5.0, "肖洛特烦恼": 4.5, "速度与激情7": 5.0}},
{"小四": {"小时代4": 5.0, "中国合伙人2": 3.0, "我的少女时代": 4.0, "匆匆那年": 4.0, "速度与激情7": 3.5, "海王": 3.5, "后会无期": 4.5}},
{"六爷": {"小时代4": 2.0, "中国合伙人2": 4.0, "荒野猎人": 4.5, "老炮儿": 5.0, "我的少女时代": 2.0}},
{"小李":  {"荒野猎人": 5.0, "盗梦空间": 5.0, "我的少女时代": 3.0, "速度与激情7": 5.0, "蚁人": 4.5, "老炮儿": 4.0, "后会无期": 3.5}},
{"隔壁老王": {"荒野猎人": 5.0, "中国合伙人2": 4.0, "我的少女时代": 1.0, "Phoenix": 5.0, "甄嬛传": 4.0, "The Strokes": 5.0}},
{"邻村小芳": {"小时代4": 4.0, "我的少女时代": 4.5, "匆匆那年": 4.5, "甄嬛传": 2.5, "The Strokes": 3.0}}]}

代码如下

file = open('./data/collaborativefiltering.json', 'r')
res = file.read()
dic = json.loads(res)

user,zghhr2,tpl, hylr, lpe, wdsnsd, xltfn, hw, xsd4, hkwq, sdyjq7, ccnn, dmkj, yr, phoenix, zhz, thestrokes = [],[], [], [],[],[], [], [],[],[], [], [],[],[], [], [],[]

key  = ""

for result in dic['data']:
    for keys in result.keys():
        key = keys

    user.append(key)
    zghhr2.append(result.get(key).get('中国合伙人2'))
    tpl.append(result.get(key).get('太平轮'))
    hylr.append(result.get(key).get('荒野猎人'))
    lpe.append(result.get(key).get('老炮儿'))
    wdsnsd.append(result.get(key).get('我的少女时代'))
    xltfn.append(result.get(key).get('肖洛特烦恼'))
    hw.append(result.get(key).get('海王'))
    xsd4.append(result.get(key).get('小时代4'))
    hkwq.append(result.get(key).get('后会无期'))
    sdyjq7.append(result.get(key).get('速度与激情7'))
    ccnn.append(result.get(key).get('匆匆那年'))
    dmkj.append(result.get(key).get('盗梦空间'))
    yr.append(result.get(key).get('蚁人'))
    phoenix.append(result.get(key).get('Phoenix'))
    zhz.append(result.get(key).get('甄嬛传'))
    thestrokes.append(result.get(key).get('The Strokes'))

    # lng.append(result[u'location'][u'lng'])
    # el.append(result[u'elevation'])
df = pd.DataFrame([user,zghhr2,tpl, hylr, lpe, wdsnsd, xltfn, hw, xsd4, hkwq, sdyjq7, ccnn, dmkj, yr, phoenix, zhz, thestrokes]).T

df.rename(columns={0:'user',1:'zghhr2',2:'tpl', 3:'hylr', 4:'lpe', 5:'wdsnsd', 6:'xltfn', 7:'hw', 8:'xsd4', 9:'hkwq', 10:'sdyjq7',11:'ccnn', 12:'dmkj', 13:'yr', 14:'phoenix', 15:'zhz', 16:'thestrokes'}, inplace=True)

print(df)

运行结果

user zghhr2 tpl hylr lpe wdsnsd xltfn hw xsd4 hkwq sdyjq7 ccnn
0 小明 5 3 4.5 5 3 4.5 5 None None None None
1 小红 None None 3 None 5 5 3 4 3 None None
2 小阳 5 None None 5 3 4.5 None 2 None 5 None
3 小四 3 None None None 4 None 3.5 5 4.5 3.5 4
4 六爷 4 None 4.5 5 2 None None 2 None None None
5 小李 None None 5 4 3 None None None 3.5 5 None
6 隔壁老王 4 None 5 None 1 None None None None None None
7 邻村小芳 None None None None 4.5 None None 4 None None 4.5
dmkj yr phoenix zhz thestrokes
0 None None None None None
1 None None None None None
2 None None None None None
3 None None None None None
4 None None None None None
5 5 4.5 None None None
6 None None 5 4 5
7 None None None 2.5 3
PyDev console: starting.
Python 3.7.0 (v3.7.0:1bf9cc5093, Jun 26 2018, 23:26:24)
[Clang 6.0 (clang-600.0.57)] on darwin