将 Json 嵌套到具有特定格式的 Pandas DataFrame [英] Nested Json to pandas DataFrame with specific format

问题描述

我需要在pandas DataFrame中以某种格式格式化Json文件的内容，以便我可以运行pandassql来转换数据并通过评分模型运行它.

i need to format the contents of a Json file in a certain format in a pandas DataFrame so that i can run pandassql to transform the data and run it through a scoring model.

file = C:scoring_modeljson.js('file'的内容如下)

file = C:scoring_modeljson.js (contents of 'file' are below)

{
"response":{
  "version":"1.1",
  "token":"dsfgf",
   "body":{
     "customer":{
         "customer_id":"1234567",
         "verified":"true"
       },
     "contact":{
         "email":"[e-mail]",
         "mobile_number":"0123456789"
      },
     "personal":{
         "gender": "m",
         "title":"Dr.",
         "last_name":"Muster",
         "first_name":"Max",
         "family_status":"single",
         "dob":"1985-12-23",
     }
   }
 }

我需要数据框看起来像这样(显然所有值都在同一行上，尝试为这个问题尽可能地格式化它):

I need the dataframe to look like this (obviously all values on same row, tried to format it best as possible for this question):

version | token | customer_id | verified | email      | mobile_number | gender |
1.1     | dsfgf | 1234567     | true     | [e-mail] | 0123456789    | m      |

title | last_name | first_name |family_status | dob
Dr.   | Muster    | Max        | single       | 23.12.1985

我已经查看了有关此主题的所有其他问题，并尝试了各种方法将 Json 文件加载到 Pandas 中

I have looked at all the other questions on this topic, have tried various ways to load Json file into pandas

`with open(r'C:scoring_modeljson.js', 'r') as f:`
    c = pd.read_json(f.read())

 `with open(r'C:scoring_modeljson.js', 'r') as f:`
    c = f.readlines()

在这个解决方案中尝试了 pd.Panel() Python Pandas:如何在数据框的列中拆分已排序的字典

tried pd.Panel() in this solution Python Pandas: How to split a sorted dictionary in a column of a dataframe

来自 [yo = f.readlines()] 的数据帧结果考虑尝试根据 ("") 拆分每个单元格的内容，并找到一种方法将拆分的内容放入不同的列中，但到目前为止还没有运气.非常感谢您的专业知识.提前致谢.

with dataframe results from [yo = f.readlines()] thought about trying to split contents of each cell based on ("") and find a way to put the split contents into different columns but no luck so far. Your expertise is greatly appreciated. Thank you in advance.

推荐答案

如果您将整个 json 作为 dict(或列表)加载，例如使用 json.load，你可以使用 json_normalize:

If you load in the entire json as a dict (or list) e.g. using json.load, you can use json_normalize:

In [11]: d = {"response": {"body": {"contact": {"email": "[e-mail]", "mobile_number": "0123456789"}, "personal": {"last_name": "Muster", "gender": "m", "first_name": "Max", "dob": "1985-12-23", "family_status": "single", "title": "Dr."}, "customer": {"verified": "true", "customer_id": "1234567"}}, "token": "dsfgf", "version": "1.1"}}

In [12]: df = pd.json_normalize(d)

In [13]: df.columns = df.columns.map(lambda x: x.split(".")[-1])

In [14]: df
Out[14]:
        email mobile_number customer_id verified         dob family_status first_name gender last_name title  token version
0  [e-mail]    0123456789     1234567     true  1985-12-23        single        Max      m    Muster   Dr.  dsfgf     1.1

这篇关于将 Json 嵌套到具有特定格式的 Pandas DataFrame的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！