CODEWITHSUNDEEP
jsonpython-3.xpandas
Bharath_Raja
Bharath_Raja

Reputation: 642

How to read a json to a pandas MultiIndex Dataframe?

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)

Upvotes: 2

Views: 3808

Answers (3)

srishti k
srishti k

Reputation: 1

import pandas as pd

df = pd.read_json('my_json.json')

df = pd.concat([pd.DataFrame(df.iloc[i,1]).assign(department=df.iloc[i,0]) for i in range(len(df))])

Upvotes: 0

Mohamed BenHaddou
Mohamed BenHaddou

Reputation: 131

A simpler approach would specify the axis already at the concat level. This will help avoid the unstacking an the sorting and will keep orignal column order.

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()}, axis=1)

Upvotes: 2

jezrael
jezrael

Reputation: 862921

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

Upvotes: 1

Related Questions