Python Pandas - Json to DataFrame

Question

I have a complicated Json File that looks like this:

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

And I want to turn it into a dataframe that looks like this:

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

Is this possible with pandas? If so, how can I manage to do it?

• If it's easier, I don't mind removing the first two columns of the User and the Obj, and just remain with the columns of the keys.

Answer 1

You can first read file to 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'}}}

And then use dict comprehension with 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

Another solution with read_json, but first need reshape by unstack and remove NaN rows by dropna. Last need 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