How to convert API JSON data into pandas dataframe

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 and pandas.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.

Converting JSON data to pandas dataframe

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 requests
import pandas as pd
api_url = 'https://restcountries.com/v3/all'
response = requests.get(api_url)
response.status_code
data = response.json()
df = pd.json_normalize(data)
df.shape
df.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')
);
Code Playground

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

Copyright ©2025 Educative, Inc. All rights reserved