如何将以下 json 文件转换为数据框？

Question

我是 Python 新手，不知道如何将以下 JSON 数据转换为 CSV 格式。非常感谢任何可以提供帮助的人！JSON数据是：{ "Hotel": [  {   "name": "Concorde Hotel New York",   "Reviewer": [    {     "name": "Serje J",     "Title": "Didn't make it but hope to soon",     "Title_url": "https://www.tripadvisor.com/ShowUserReviews-g60763-d14040955-r751642278-Concorde_Hotel_New_York-New_York_City_New_York.html",     "Description": "I was scheduled to stay here in March which unfortunately didn't happen due to COVID-19. I was forced to cancel my arrangements, however I was so impressed by the courtesy and promptitude with which Concorde staff arranged my cancellation and refund. When all this madness is behind us I will certainly be rebooking with this hotel.",     "Location": "Melbourne, Australia",     "Date": "Date of stay: March 2020",     "Rating": "ui_bubble_rating bubble_50"    },    {     "name": "John Schueler",     "Title": "Beautiful Modern Rooms in East MidTown",     "Title_url": "https://www.tripadvisor.com/ShowUserReviews-g60763-d14040955-r751405066-Concorde_Hotel_New_York-New_York_City_New_York.html",     "Description": "The lobby is under construction but the rooms are fresh, modern and very nicely appointed. The bathrooms are large and very nice. Only complaint was the wifi was weak in the room - which could be due to the construction disruption. Great location for sites and restaurants. Room was very quiet!",     "Location": "Beaufort, South Carolina",     "Date": "Date of stay: March 2020",     "Rating": "ui_bubble_rating bubble_40"    }   ]  } ]}

Answer 1

1. json_normalize将 pandas 中的记录展平/结构化

2. 为了美化，将Hotel.name移到第一列

3. 使用 pandas 功能输出为 CSV

import pandas as pd

js = {

 "Hotel": [

  {

   "name": "Concorde Hotel New York",

   "Reviewer": [

    {

     "name": "Serje J",

     "Title": "Didn't make it but hope to soon",

     "Title_url": "https://www.tripadvisor.com/ShowUserReviews-g60763-d14040955-r751642278-Concorde_Hotel_New_York-New_York_City_New_York.html",

     "Description": "I was scheduled to stay here in March which unfortunately didn't happen due to COVID-19. I was forced to cancel my arrangements, however I was so impressed by the courtesy and promptitude with which Concorde staff arranged my cancellation and refund. When all this madness is behind us I will certainly be rebooking with this hotel.",

     "Location": "Melbourne, Australia",

     "Date": "Date of stay: March 2020",

     "Rating": "ui_bubble_rating bubble_50"

    },

    {

     "name": "John Schueler",

     "Title": "Beautiful Modern Rooms in East MidTown",

     "Title_url": "https://www.tripadvisor.com/ShowUserReviews-g60763-d14040955-r751405066-Concorde_Hotel_New_York-New_York_City_New_York.html",

     "Description": "The lobby is under construction but the rooms are fresh, modern and very nicely appointed. The bathrooms are large and very nice. Only complaint was the wifi was weak in the room - which could be due to the construction disruption. Great location for sites and restaurants. Room was very quiet!",

     "Location": "Beaufort, South Carolina",

     "Date": "Date of stay: March 2020",

     "Rating": "ui_bubble_rating bubble_40"

    }

   ]

  }

 ]

}

# use provided functionality to flatten and normalise data

df = pd.json_normalize(js, record_path=["Hotel","Reviewer"], meta=[["Hotel","name"]])

# Hotel.name logically should be 1st column, move it

df.insert(0, "Hotel.name", df.pop("Hotel.name"))

# make it a CSV....

df.to_csv(index=False)

Answer 2

首先，我建议您始终将 Json 粘贴到查看器中。这样您就可以轻松地可视化文件结构。

然后，将您提供的示例保存为file.json，只需几行代码即可获得：

import pandas as pd

file = pd.read_json("file.json")

temp = file['Hotel'][0]['Reviewer']

df = pd.DataFrame.from_dict(temp)

df['hotel'] = file['Hotel'][0]['name']

>> df

            name  ...                    hotel

0        Serje J  ...  Concorde Hotel New York

1  John Schueler  ...  Concorde Hotel New York