python将json数据转化为dataframe

系统管理员 2021-12-18 22:31 350阅读 0赞

原始数据下

{"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