Python Pandas - Json 到 DataFrame

Question

我有一个复杂的 Json 文件，如下所示:

{
  "User A" : {
     "Obj1" : {
        "key1": "val1",
        "key2": "val2",
        "key3": "val3",
     }
     "Obj2" : {
        "key1": "val1",
        "key2": "val2",
        "key3": "val3"
     }
  }
  "User B" : {
     "Obj1" : {
        "key1": "val1",
        "key2": "val2",
        "key3": "val3",
        "key4": "val4"
     }
  }
}

我想把它变成一个看起来像这样的数据框:

                key1   key2   key3   key4
User A   Obj1   val1   val2   val3    NaN
         Obj2   val1   val2   val3    NaN
User B   Obj1   val1   val2   val3    val4

这对 Pandas 来说可能吗？如果是这样，我该如何设法做到这一点？

• 如果更简单，我不介意删除 User 和 Obj 的前两列，只保留键的列。

Answer 1

您可以先将文件读入dict:

with open('file.json') as data_file:    
    dd = json.load(data_file)

print(dd)
{'User B': {'Obj1': {'key2': 'val2', 'key4': 'val4', 'key1': 'val1', 'key3': 'val3'}}, 
'User A': {'Obj1': {'key2': 'val2', 'key1': 'val1', 'key3': 'val3'}, 
'Obj2': {'key2': 'val2', 'key1': 'val1', 'key3': 'val3'}}}

然后使用 dict comprehension 和 concat :

df = pd.concat({key:pd.DataFrame(dd[key]).T for key in dd.keys()})
print (df)
             key1  key2  key3  key4
User A Obj1  val1  val2  val3   NaN
       Obj2  val1  val2  val3   NaN
User B Obj1  val1  val2  val3  val4

另一种解决方案 read_json , 但首先需要通过 unstack 进行整形并通过 dropna 删除 NaN 行.最后需要DataFrame.from_records :

df = pd.read_json('file.json').unstack().dropna()
print (df)
User A  Obj1     {'key2': 'val2', 'key1': 'val1', 'key3': 'val3'}
        Obj2     {'key2': 'val2', 'key1': 'val1', 'key3': 'val3'}
User B  Obj1    {'key2': 'val2', 'key4': 'val4', 'key1': 'val1...
dtype: object

df1 = pd.DataFrame.from_records(df.values.tolist())
print (df1)
   key1  key2  key3  key4
0  val1  val2  val3   NaN
1  val1  val2  val3   NaN
2  val1  val2  val3  val4

---

df1 = pd.DataFrame.from_records(df.values.tolist(), index = df.index)
print (df1)
             key1  key2  key3  key4
User A Obj1  val1  val2  val3   NaN
       Obj2  val1  val2  val3   NaN
User B Obj1  val1  val2  val3  val4