How to explode nested json in pandas as rows?

Question

My json looks like below,

{
   "customer_name":"john",
   "phone":{
      "mobile":000, 
      "office":111
   },
   "mail":{
      "office":"[email protected]", 
      "personal":"[email protected]"
   }
}

phone or mail can have 1 or many items. Also, the key (office, mobile, personnel) are not same always. For example, it can be Business or alternative. So I wanted to convert them to rows and not columns. Most of the questions are to convert to columns so not able to find a solution for rows.

The final data frame should look like,

customer_name   |   phone.key   |   phone.val   |  mail.key   |   mail.value 
john            |   mobile      |   000         |             | 
john            |   office      |   111         |             |         
john            |               |               |  office     |   [email protected]
john            |               |               |  personal   |   [email protected]

Can someone help me?

gtomer · Accepted Answer · 2022-10-16 07:43:38Z

4

Yiu can change format of nested dictionaries added customer_name with new keys:

d = {
   "customer_name":"john",
   "phone":{
      "mobile":000, 
      "office":111
   },
   "mail":{
      "office":"[email protected]", 
      "personal":"[email protected]"
   }
}

out = []
for k, v in d.items():
    if isinstance(v, dict):
        for k1, v1 in v.items():
            out.append({'customer_name':d['customer_name'],f'{k}.key': k1,f'{k}.value':v1})

Least comprehension alternative:

out = [{'customer_name':d['customer_name'],f'{k}.key': k1,f'{k}.value':v1} 
        for k, v in d.items() if isinstance(v, dict) for k1, v1 in v.items()]

df = pd.DataFrame(out)
print (df)
  customer_name phone.key  phone.value  mail.key       mail.value
0          john    mobile          0.0       NaN              NaN
1          john    office        111.0       NaN              NaN
2          john       NaN          NaN    office  [email protected]
3          john       NaN          NaN  personal    [email protected]

edited Oct 16, 2022 at 7:43

gtomer

6,6041 gold badge14 silver badges29 bronze badges

answered May 17, 2020 at 6:42

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Boston Over a year ago

Can you can't do this json_normalize?

jezrael Over a year ago

@ScottBoston - Ya, like first step yes, but then is necessary next processing.

Umar Aftab · Accepted Answer · 2020-05-17 06:54:56Z

4

It would be way simpler if you use json_normalize in the following way to flatten your data:

 from pandas.io.json import json_normalize
 data = {
  "customer_name":"john",
  "phone":{
     "mobile":000, 
     "office":111
   },
  "mail":{
    "office":"[email protected]", 
    "personal":"[email protected]"
   }
 }

 df = json_normalize(data)

answered May 17, 2020 at 6:54

Umar Aftab

5354 silver badges26 bronze badges

1 Comment

jezrael Over a year ago

Only output not matched what need @Selva

Collectives™ on Stack Overflow

How to explode nested json in pandas as rows?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related