Convert JSON to pandas DataFrame

In this article, we will discuss how to convert JSON to pandas DataFrame?

 

Introduction

JSON stands for Java Script Object Notation which is in the form of key:value pair.

Structure:

'''{
    { key:value },
    { key:value },
    -------------,
    { key:value },
} '''

DataFrame in pandas is an two dimensional data structure that will store data in two dimensional format. One dimension refers to a row and second dimension refers to a column, So It will store the data in rows and columns.

 Method 1 :  Using read_json()

Syntax:

read_json(json_data,orient)

where,

1. json_data is the input json data
2. orient is used to set the dataframe index (like columns,index etc)

We can see more about the orient with examples.

 

Example 1: convert json to pandas dataframe

In this example, We will create an json data with market data that has 5 markets such that each market has 3 attributes and converting to pandas dataframe with index orient.

#importing pandas module
import pandas

#create json data for market
json_market_data='''{ "market1":{"food-id":1,"name":"sweets","quantity":3},
                  "market2":{"food-id":1,"name":"jaggery","quantity":3},
                  "market3":{"food-id":1,"name":"vegetables","quantity":32},
                  "market4":{"food-id":1,"name":"meat","quantity":45},
                  "market5":{"food-id":1,"name":"sweets","quantity":100}}'''

      #display json data
print(json_market_data)

print()

#convert to pandas dataframe
converted_dataframe= pandas.read_json(json_market_data,orient ='index')

#display the dataframe
print(converted_dataframe)

Output:

We specified index as orient, so in the dataframe the index will be market index from json

{ "market1":{"food-id":1,"name":"sweets","quantity":3},
                  "market2":{"food-id":1,"name":"jaggery","quantity":3},
                  "market3":{"food-id":1,"name":"vegetables","quantity":32},
                  "market4":{"food-id":1,"name":"meat","quantity":45},
                  "market5":{"food-id":1,"name":"sweets","quantity":100}}

         food-id        name  quantity
market1        1      sweets         3
market2        1     jaggery         3
market3        1  vegetables        32
market4        1        meat        45
market5        1      sweets       100

Example 2:  how to convert json to pandas dataframe

In this example, We will create an json data with market data that has 5 markets such that each market has 3 attributes and converting to pandas dataframe with columns orient.

#importing pandas module
import pandas

#create json data for market
json_market_data='''{ "market1":{"food-id":1,"name":"sweets","quantity":3},
                  "market2":{"food-id":1,"name":"jaggery","quantity":3},
                  "market3":{"food-id":1,"name":"vegetables","quantity":32},
                  "market4":{"food-id":1,"name":"meat","quantity":45},
                  "market5":{"food-id":1,"name":"sweets","quantity":100}}'''

      #display json data
print(json_market_data)

print()

#convert to pandas dataframe
converted_dataframe= pandas.read_json(json_market_data,orient ='columns')

#display the dataframe
print(converted_dataframe)

Output:

We specified index as orient, so in the dataframe the index will be column names  from json.

 "market1":{"food-id":1,"name":"sweets","quantity":3},
                  "market2":{"food-id":1,"name":"jaggery","quantity":3},
                  "market3":{"food-id":1,"name":"vegetables","quantity":32},
                  "market4":{"food-id":1,"name":"meat","quantity":45},
                  "market5":{"food-id":1,"name":"sweets","quantity":100}}

         market1  market2     market3 market4 market5
food-id        1        1           1       1       1
name      sweets  jaggery  vegetables    meat  sweets
quantity       3        3          32      45     100

Lets now learn about json normalize in pandas.

 

Method 2 : Convert json to DataFrame using json_normalize()

We can done this by first loading the json string using json.loads() method and after we have to pass this to the json_normalize() function. Before that we have to import json

import json

Syntax:

 

pandas.json_normalize(json.loads(json_data))

where, json_data is the input json string.

Example: json normalize in pandas

In this json normalize in pandas example, we created the json array with list and convert to pandas dataframe.

#importing pandas and json modules
import pandas
import json

#create json data for market
json_market_data='''[ {"food-id":1,"name":"sweets","quantity":3},
                  {"food-id":2,"name":"jaggery","quantity":3},
                  {"food-id":3,"name":"vegetables","quantity":32},
                  {"food-id":4,"name":"meat","quantity":45},
                  {"food-id":5,"name":"sweets","quantity":100}]'''

      #display json data
print(json_market_data)

print()

#convert to pandas dataframe
converted_dataframe= pandas.json_normalize(json.loads(json_market_data))

#display the dataframe
print(converted_dataframe)

Output:

[ {"food-id":1,"name":"sweets","quantity":3},
                  {"food-id":2,"name":"jaggery","quantity":3},
                  {"food-id":3,"name":"vegetables","quantity":32},
                  {"food-id":4,"name":"meat","quantity":45},
                  {"food-id":5,"name":"sweets","quantity":100}]

   food-id        name  quantity
0        1      sweets         3
1        2     jaggery         3
2        3  vegetables        32
3        4        meat        45
4        5      sweets       100

This wraps up our session on how to convert json to pandas dataframe or convert json to pandas dataframe and json normalize in pandas.