How to convert json to Excel fule? [closed]-CodePudding

I want to convert the following link to Excel using Python language so that it stores information about the country and the capital and their code in an Excel file Can you please guide me?

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

CodePudding user response：

Pandas library will come to rescue here, though extracting your nested json is more of a python skills. You can follow the following to simply extract desired columns:

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

Full info on ExcelWriter module can be read at https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html

You will need to play around to change the columns names and clean up the data (especially the nested objects) and these should be just a search away.

CodePudding user response：

Your best bet would be to use pandas to read the JSON from the URL that you have mentioned and save it to an excel file. Here's the code for it:

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

However, there will be an issue with reading nested fields (if you want to read them in the future). For example, the fields named borders and currencies in your dataset are lists. So, you might need some post-processing after you load it.

Cheers!