python - 如何将 Pandas 中的嵌套 json 分解为行？

Question

我的 json 如下所示，

{
   "customer_name":"john",
   "phone":{
      "mobile":000, 
      "office":111
   },
   "mail":{
      "office":"john@office.com", 
      "personal":"john@home.com"
   }
}

电话或邮件可以有 1 个或多个项目。此外， key (办公室、手机、人员)并不总是相同的。例如，它可以是 Business 或 alternative。所以我想将它们转换为行而不是列。大多数问题都是转换为列，因此无法找到行的解决方案。

最终的数据框应该是这样的，

customer_name   |   phone.key   |   phone.val   |  mail.key   |   mail.value 
john            |   mobile      |   000         |             | 
john            |   office      |   111         |             |         
john            |               |               |  office     |   john@office.com
john            |               |               |  personal   |   john@home.com

有人可以帮助我吗？

Answer 1

Yiu 可以使用新的 key 更改添加 customer_name 的嵌套字典的格式:

d = {
   "customer_name":"john",
   "phone":{
      "mobile":000, 
      "office":111
   },
   "mail":{
      "office":"john@office.com", 
      "personal":"john@home.com"
   }
}

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})

最不理解的替代方案:

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  john@office.com
3          john       NaN          NaN  personal    john@home.com