将具有命名列表的词典转换为DataFram

Question

我有来自API的数据，格式如下，我想把它转换成一个数据帧。你知道吗

sample = '''{"rowHeaders":["Target","Month","Brand (TVEye)"],
                "colHeaders":["Units","Values"],
                "items":[["Adult",
                          "2019m1",
                          "1&1",
                          ["1+ (Item Reach)","8,8"],
                          ["2+ (Item Reach)","6,8"],
                          ["3+ (Item Reach)","2,6"],
                          ["4+ (Item Reach)","1,6"],
                          ["5+ (Item Reach)","0,9"],
                          ["6+ (Item Reach)","0,9"],
                          ["7+ (Item Reach)","0,1"],
                          ["8+ (Item Reach)","0,1"],
                          ["9+ (Item Reach)","0,0"],
                          ["10+ (Item Reach)","0,0"],
                          ["TVR (U/W)","21,8"]],
                        ["Adult",
                        "2019m2",
                        "1&1",
                        ["1+ (Item Reach)","11,1"],
                        ["2+ (Item Reach)","1,7"],
                        ["3+ (Item Reach)","0,4"],
                        ["4+ (Item Reach)","0,0"],
                        ["5+ (Item Reach)","0,0"],
                        ["6+ (Item Reach)","0,0"],
                        ["7+ (Item Reach)","0,0"],
                        ["8+ (Item Reach)","0,0"],
                        ["9+ (Item Reach)","0,0"],
                        ["10+ (Item Reach)","0,0"],
                        ["TVR (U/W)","13,2"]],
                        ["Adult",
                        "2019m3",
                        "1&1",
                        ["1+ (Item Reach)","5,3"],
                        ["2+ (Item Reach)","2,0"],
                        ["3+ (Item Reach)","0,0"],
                        ["4+ (Item Reach)","0,0"],
                        ["5+ (Item Reach)","0,0"],
                        ["6+ (Item Reach)","0,0"],
                        ["7+ (Item Reach)","0,0"],
                        ["8+ (Item Reach)","0,0"],
                        ["9+ (Item Reach)","0,0"],
                        ["10+ (Item Reach)","0,0"],
                        ["TVR (U/W)","7,3"]]]}'''

然而，由于其奇怪的格式，没有一个标准的功能工作，我一直未能取得几乎任何进展。你知道吗

如何将此字典转换为如下所示的数据帧（抱歉，数字排列不正确，但这是一个表）？你知道吗

Target  Month   Brand (TVEye)   1+ (Item Reach) 2+ (Item Reach) 3+ (Item Reach) 4+ (Item Reach) 5+ (Item Reach) 6+ (Item Reach) 7+ (Item Reach) 8+ (Item Reach) 9+ (Item Reach) 10+ (Item Reach)    TVR (U/W)
Adult   2019m1  1&1 8,8 6,8 2,6 1,6 0,9 0,9 0,1 0,1 0,0 0,0 21,8
Adult   2019m2  1&1 11,1 1,7 0,4 0,0 0,0 0,0 0,0 0,0 0,0 0,0 13,2
Adult   2019m3  1&1 5,3 2,0 0,0 0,0 0,0 0,0 0,0 0,0 0,0 0,0 7,3

Answer 1

import pandas as pd
import json

sample = '''{"rowHeaders":["Target","Month","Brand (TVEye)"],
                "colHeaders":["Units","Values"],
                "items":[["Adult",
                          "2019m1",
                          "1&1",
                          ["1+ (Item Reach)","8,8"],
                          ["2+ (Item Reach)","6,8"],
                          ["3+ (Item Reach)","2,6"],
                          ["4+ (Item Reach)","1,6"],
                          ["5+ (Item Reach)","0,9"],
                          ["6+ (Item Reach)","0,9"],
                          ["7+ (Item Reach)","0,1"],
                          ["8+ (Item Reach)","0,1"],
                          ["9+ (Item Reach)","0,0"],
                          ["10+ (Item Reach)","0,0"],
                          ["TVR (U/W)","21,8"]],
                        ["Adult",
                        "2019m2",
                        "1&1",
                        ["1+ (Item Reach)","11,1"],
                        ["2+ (Item Reach)","1,7"],
                        ["3+ (Item Reach)","0,4"],
                        ["4+ (Item Reach)","0,0"],
                        ["5+ (Item Reach)","0,0"],
                        ["6+ (Item Reach)","0,0"],
                        ["7+ (Item Reach)","0,0"],
                        ["8+ (Item Reach)","0,0"],
                        ["9+ (Item Reach)","0,0"],
                        ["10+ (Item Reach)","0,0"],
                        ["TVR (U/W)","13,2"]],
                        ["Adult",
                        "2019m3",
                        "1&1",
                        ["1+ (Item Reach)","5,3"],
                        ["2+ (Item Reach)","2,0"],
                        ["3+ (Item Reach)","0,0"],
                        ["4+ (Item Reach)","0,0"],
                        ["5+ (Item Reach)","0,0"],
                        ["6+ (Item Reach)","0,0"],
                        ["7+ (Item Reach)","0,0"],
                        ["8+ (Item Reach)","0,0"],
                        ["9+ (Item Reach)","0,0"],
                        ["10+ (Item Reach)","0,0"],
                        ["TVR (U/W)","7,3"]]]}'''

jsample = json.loads(sample)
df = pd.DataFrame.from_dict(jsample['items'])
df.columns = jsample['rowHeaders'] + df.iloc[0,3:].map(lambda x: x[0]).to_list()
df.iloc[:,3:] = df.iloc[:,3:].applymap(lambda x: x[1])
print(df)

输出：

  Target   Month Brand (TVEye) 1+ (Item Reach) 2+ (Item Reach) 3+ (Item Reach) 4+ (Item Reach) 5+ (Item Reach) 6+ (Item Reach) 7+ (Item Reach) 8+ (Item Reach) 9+ (Item Reach) 10+ (Item Reach) TVR (U/W)
0  Adult  2019m1           1&1             8,8             6,8             2,6             1,6             0,9             0,9             0,1             0,1             0,0              0,0      21,8
1  Adult  2019m2           1&1            11,1             1,7             0,4             0,0             0,0             0,0             0,0             0,0             0,0              0,0      13,2
2  Adult  2019m3           1&1             5,3             2,0             0,0             0,0             0,0             0,0             0,0             0,0             0,0              0,0       7,3