Key takeaways:
JSON is a lightweight, human-readable format for data transmission using key-value pairs, suitable for APIs.
JSON can contain nested arrays and objects, which can complicate analysis but can be effectively flattened.
Converting JSON to a pandas dataframe allows for structured data manipulation.
Use the
requests
library to fetch JSON data via an API andpandas.json_normalize()
to flatten the data into a dataframe.
JavaScript Object Notation (JSON), is an open and lightweight file format to store and transmit data in form of key-value pairs. It is a common mean of transmitting data between servers and applications or within different parts of applications.
It is language-independent, simple for humans to read and write, and easy for machines to parse and generate.
JSON has a hierarchical structure that consists of key-value pairs where values can be booleans, strings, numbers, arrays, objects, or null. The following JSON file contains an array of two objects, where each object represents a person.
Each person has attributes such as their name, age, city, student status, and a list of languages they speak.
[{"name": "Alice","age": 30,"city": "New York","is_student": false,"languages": ["English", "Spanish"]},{"name": "Bob","age": 25,"city": "San Francisco","is_student": true,"languages": ["English", "French"]}]
Converting JSON data to pandas dataframes allows to bring structure to JSON's typically hierarchical data, enabling users to gain insights, visualize data, and prepare it for further analysis or reporting.
It can also help with efficiently cleaning, transforming, and exploring data by handling missing values, reshaping data structures, integrating with other libraries, performing multiple analyses, and exporting data to various formats.
The code below reads JSON data from the Rest Countries
API, a free and public web API that provides information about countries around the world, and converts it into pandas
dataframe.
The fetched data in form of a GET request is first converted into JSON using json()
method of request
library. Later it is converted into dataframe using json_normalize()
method of pandas
. The json_normalize()
method is used to convert JSON data into a flat table or dataframe format by flattening nested JSON structures and converting them into a tabular format.
The data in tabular format is suitable for data analysis and manipulation using pandas.
import requestsimport pandas as pdapi_url = 'https://restcountries.com/v3/all'response = requests.get(api_url)response.status_codedata = response.json()df = pd.json_normalize(data)df.shapedf.head()
The code is explained below:
Line 1: Imports the Python library request
that provides built-in functions to fetch JSON data through web API.
Line 2: Imports the Python library pandas
which is used for working data analysis and manipulation.
Line 3: Defines the API endpoint URL
Line 4: Fetches the data using a GET
request.
Line 5: Finds the status_code
of the GET
request, which should be 200
for a successful request.
Line 6: Converts the fetched data into JSON.
Line 7: Converts the JSON data into pandas
dataframe.
Line 8: Displays the shape of the dataframe.
Line 9: Displays the first few rows of the dataframe.
You can practice the above code in the code playground below.
Press the "Run" button and wait for the output tab to show the Jupyter notebook. It will take some time to load the notebook. Alternatively, you can click the "link" beside the "Run" button to open the respective Jupyter Notebook in a new tab.
import React from 'react'; require('./style.css'); import ReactDOM from 'react-dom'; import App from './app.js'; ReactDOM.render( <App />, document.getElementById('root') );
In conclusion, JSON is a versatile and widely used format for storing and sharing data.
It is simple, efficient, and easy for both humans to read and machines to process. By converting JSON data into pandas dataframes, users can take advantage of pandas' powerful data analysis capabilities to transform hierarchical JSON structures into a more structured, tabular format.
Free Resources