Json Python

Json is a very popular data format and is widely used for data exchange. JSON is human readable format. It is very easy to parse Json data in Python. In Python, we can read the Json data either from a file or from an API. In this post, I will go over some of the most common commands to read, parse, structure and write Json data.

Python has Json package. Let us import that.

In [1]:
import json

Let us build a sample Json data. In the below example, I have constructed a Json data. Json is all about keys and values. In our json example below, we have two main keys - 'state' and 'citites'. Values can be one or multiple values. For multiple values, we can mention values in square brackets. In python terms values inside square brackets is called list.

One important thing to notice here is that, in the below example all the keys and values are in double quotes. You can't have single quotes. We can mix and match strings and numbers though.

Python Load Json

In [2]:
x = {"state":"california","cities":["los angeles","san francisco"]}

Ok I have created a json object x. The variable x is a dictionary. To convert in to Json. We need to first convert above dictionary to Json string format using json.dumps method.

In [3]:
type(json.dumps(x))
Out[3]:
str
In [4]:
d = json.dumps(x)

Ok as we see above, we get a str object but of type Json. To convert it back to Python dictionary, we can use json.loads() method.

In [5]:
y = json.loads(json.dumps(x))

Note y is a dictionary in Python so we can access the keys and values just like we do for regular dictionaries in Python as shown below.

In [6]:
y['state']
Out[6]:
'california'
In [7]:
type(json.loads(json.dumps(x)))
Out[7]:
dict

Python read Json from API Url

Ok in Python we can read Json directly from an API. For this we need Python Pandas. If you don't know about Pandas. Read about pandas.

Let us first import Python pandas package.

In [8]:
import pandas as pd

Let us read the options data from following yahoo finance API using pd.read_json() function.

In [9]:
jsondata = pd.read_json('https://query2.finance.yahoo.com/v7/finance/options/amzn')
In [10]:
type(jsondata)
Out[10]:
pandas.core.frame.DataFrame

Ok as we see above, by default, pandas creates a DataFrame. Json data can be quite complex and can contain multiple nested key values pairs and therefore can become very long. Dataframe is not the right data structure to analyze the json data. Therefore we need to convert this dataframe to Python dictionary first using to_dict() method as shown below.

In [11]:
data = jsondata.to_dict()

Let us take a look into our data now.

In [12]:
data.keys()
Out[12]:
dict_keys(['optionChain'])

Ok, as we see above, the main key is 'optionChain' which contains all the data. We can access and loop through above dictionary as we do normally in Python dictionaries.

In [13]:
data['optionChain']['result'][0]['expirationDates'][0]
Out[13]:
1595548800

Write Json data to file in Python

To write json data to file, we can use either Json package or Pandas package. Let us first try the Pandas way.

Pandas has to_json() method. Let us apply this to our jsondata Pandas dataframe which has options data for one stock.

In [14]:
with open('optionsdata.json', 'w') as fp:
    fp.write(jsondata.to_json())
In [15]:
ls -lrt optionsdata.json
-rw-rw-r-- 1 root root 210521 Jul 17 21:53 optionsdata.json

Ok now let us try the 2nd way which is by using the json package

In [16]:
with open('optionsdata1.json', 'w') as fp:
    fp.write(json.dumps(jsondata.to_dict()))
In [17]:
ls -lrt optionsdata1.json
-rw-rw-r-- 1 root root 235505 Jul 17 21:53 optionsdata1.json

Read Json data from a file in Python

Reading Json data from a file in Python is pretty straight forward. Let us read back the json file that we just wrote. Let us use the Pandas read_json function first.

In [18]:
jodata = pd.read_json('optionsdata.json')
In [19]:
type(jodata)
Out[19]:
pandas.core.frame.DataFrame

Then as usual, use jodata.to_dict() for further processing in Python.

We can also use json package's json.loads() to read the json file as shown below.

In [20]:
with open('optionsdata.json', 'r') as fp:
    jodata = json.loads(fp.read())
In [21]:
type(jodata)
Out[21]:
dict

Json Array or List in Python

Json List in python is a list of Json rows. Each row in itself is a valid json doc or row. Let us take above options data for couple of stocks and create a Python Json list.

In [22]:
stocks = ['nvda','intc']
jsonlist = []
In [23]:
for stock in stocks:
    jsondata = pd.read_json('https://query2.finance.yahoo.com/v7/finance/options/%s'%stock)
    jsonlist.append(jsondata)
In [24]:
len(jsonlist)
Out[24]:
2

Ok, as we see above. We have two entries of Json data in our jsonlist variable. Note, we have used pd.read_json method to read directly from the API url. If you remember, each json row or entry is a Pandas dataframe. So will have to use to_dict() method.

In [25]:
type(jsonlist[0])
Out[25]:
pandas.core.frame.DataFrame
In [26]:
json.loads(json.dumps(jsonlist))

If we run the above, we would get following error. Reason, jsonlist is a python list.

TypeError: Object of type DataFrame is not JSON serializable

Ofcourse we can do following...

In [27]:
jodata = jsonlist[0].to_dict()
In [28]:
type(jodata)
Out[28]:
dict

To convert all rows to dictionary,we can loop through the jsonlist.

How to write Json Array or List to a file in Python using Pandas

Ok let us take our jsonlist and write to a file. Remember each row or entry in the jsonlist is a Pandas Dataframe. So we need to convert each entry to json first before writing to file.

Let us try to write Ist json document.

In [29]:
with open('stocks_data.json','w') as fp:
    fp.write(jsonlist[0].to_json())
In [30]:
ls -lrt stocks_data.json
-rw-rw-r-- 1 root root 56908 Jul 17 21:53 stocks_data.json

Let us write now all the entries of jsonlist to a file now.

In [31]:
with open('stocks_data.json','w') as fp:
    for entry in jsonlist[0:2]:
        fp.write(entry.to_json())
        fp.write('\n')

Note, we have added a new line '\n' that way each line will have one json entry.

Let us try to read back the Json list or Array from the file, we just created above.

In [32]:
jsonlist = []
with open('stocks_data.json','r') as fp:
    for line in fp:
        jsonlist.append(json.loads(line))
In [33]:
len(jsonlist)
Out[33]:
2
In [34]:
jsonlist[0]['optionChain']['result'][0]['expirationDates'][0]
Out[34]:
1595548800

How to write Json Array or List to a file in Python using json package

Let us now try writing json Array to a file using json package.

We need to check whether the entries in jsonlist are Python dictionary or a json string.

In [35]:
type(jsonlist[0])
Out[35]:
dict

Ok, now we have the each entry as dictionary, so we will have to use the json.dump method as shown below.

In [36]:
with open('stocks_data.json','w') as fp:
    for entry in jsonlist:
        json.dump(entry,fp)
In [37]:
ls -lrt stocks_data.json
-rw-rw-r-- 1 root root 94683 Jul 17 21:53 stocks_data.json

Wrap Up!

I have tried to cover the basics of Json handling in Python with examples. I hope, this will help you understanding Json parsing in Python.