[FIXED] How to read a json to a pandas MultiIndex Dataframe?

Issue

I have a json format file which looks like this.

{'accounting': [{'firstName': 'John',
   'lastName': 'De',
   'age': 29,
   'PhNumber': 253435221},
  {'firstName': 'Mary',
   'lastName': 'Smith',
   'age': 38,
   'PhNumber': 5766546221}],
 'sales': [{'firstName': 'Sally',
   'lastName': 'Green',
   'age': 29,
   'PhNumber': 63546433221},
  {'firstName': 'Jim',
   'lastName': 'Galley',
   'age': 48,
   'PhNumber': 3566648322}]}

How can I read this in to a pandas multi index dataframe with columns

(accounting, firstname), (accoutning, lastName), (accounting, age), 
(accounting, PhNumber), (sales, firstname), (sales, lastName), (sales, age), (sales, PhNumber)

Solution

Use dictionary comprehension with DataFrame constructor:

import json
with open('myJson.json') as data_file:    
    d = json.load(data_file)  

df = pd.concat({k: pd.DataFrame(v) for k, v in d.items()}).unstack(0).swaplevel(1,0, axis=1).sort_index(axis=1)
print (df)
   accounting                               sales                       
     PhNumber age firstName lastName     PhNumber age firstName lastName
0   253435221  29      John       De  63546433221  29     Sally    Green
1  5766546221  38      Mary    Smith   3566648322  48       Jim   Galley

Answered By - jezrael