How to Convert JSON to Pandas DataFrame

JSON is one of the most commonly used data formats in websites and applications these days. Python pandas is a data processing library in Python that allows you to easily import, analyze and export data in Python. Often you may need to convert JSON to pandas dataframe or import JSON data into python pandas. In this article, we will learn how to do this.

How to Convert JSON to Pandas DataFrame

There are plenty of python libraries that allow you to easily load data from JSON file. You can load JSON data from URL as well as file. We will look at both these approaches.

1. Import JSON from URL

We will use urllib for our purpose. It is easy to use and popular python library. First we import Request and urlopen functions in urllib2.

from urllib2 import Request, urlopen
import json

If urllib2 is not present on your system, run the following command to install it.

$ python -m pip install urllib2

Next, import python pandas.

import pandas as pd

Next, we request the URL containing JSON data. Typically, JSON data is available as an API endpoint so we have used request module to get it. You can also use JSON file if you want.

request=Request('url_to_json')
response = urlopen(request)

Next, we load this data using json.loads() function. We pass response.read() as argument in json.loads() function.

data = json.loads(response.read())

Lastly we use json_normalize() function to load JSON data to pandas dataframe.

df = pd.json_normalize(data)

2. Load JSON file

You can also load JSON from file as shown below.

import pandas as pd  

# reading the JSON data using json.load()
file = 'data.json'
with open(file) as train_file:
    dict = json.load(train_file)

# converting json dataset from dictionary to dataframe
df = pd.DataFrame.from_dict(dict, orient='index')
df.reset_index(level=0, inplace=True)

In this case, we first open JSON file data.json using json.load(). It will return a python dictionary of key-value pairs. Then we use DataFrame.from_dict() function to load open JSON file into dataframe. We finally use reset_index() to reset its data pointer to the beginning of dataframe.

In this article, we have learnt how to convert JSON to pandas dataframe.

How to Convert JSON to Pandas DataFrame