如何将json转换为Excel文件？

Question

我想使用Python语言将下面的链接转换为Excel，这样它就可以在Excel文件中存储有关国家和首都的信息及其代码 你能指引我吗

https://restcountries.eu/rest/v2/all

Answer 1

最好使用pandas从您提到的URL读取JSON并将其保存到excel文件中。下面是它的代码：

import pandas as pd

# Loading the JSON from the URL to a pandas dataframe
df = pd.read_json('https://restcountries.eu/rest/v2/all')

# Selecting the columns for the country name, capital, and the country code (as mentioned in the question)
df = df[["name", "capital", "alpha2Code"]]

# Saving the data frame into an excel file named 'restcountries.xlsx', but feel free to change the name
df.to_excel('restcountries.xlsx') 

但是，在读取嵌套字段时会出现问题（如果您希望将来读取它们）。例如，数据集中名为borders和currencies的字段是列表。因此，加载后可能需要一些后处理

干杯

Answer 2

Pandas库将在这里提供帮助，不过提取嵌套json更多的是python技能。您可以按照以下步骤简单地提取所需的列：

import pandas as pd
url = 'https://restcountries.eu/rest/v2/all';

#Load json to a dataframe
df = pd.read_json(url); 

# Create DF with Country, capital and code fields. You can use df.head() to see how your data looks in table format and columns name.
df_new = df[['name', 'capital', 'alpha2Code', 'alpha3Code']].copy()

#Use pandas ExcelWriter to write the desired DataFrame to xlsx file. 
with pd.ExcelWriter('country_names.xlsx') as writer:
    df_new.to_excel(writer, sheet_name="Country List")

Sample Data from the generated Excel File

有关ExcelWriter模块的完整信息，请访问https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html

您需要改变列名并清理数据（尤其是嵌套对象），而这些应该只是一个搜索