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 Write List to File in Python
How to Fix Invalid Use of Group Function Error
How to Insert Current Datetime in PostgreSQL
How to Insert Current Datetime in MySQL
How to Uninstall MySQL Completely in Ubuntu