Twin X Axis Plot And Intersection Of Indexes In Python

Introduction 

 

In this article, I would like to show how to plot two different Y axes on the same X-axis with different left and right scales. For this example, I have taken data on India’s yearly Ratio of External Debt to GDP from 1991 through 2019 and BSE Sensex closing rates for 1991 through 2019. Let’s see if there is a correlation between this data.

 

The BSE Sensex data comes from here and the external debt data comes from here.

 

Import libraries

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd

Get data in Python from Excel. Excel sheet and the ipynb file for this sample data is attached along with this article for anyone to download.

debt = pd.read_excel("India Debt and Sensex.xlsx", sheet_name="Debt Data",
                     skiprows=2,index_col=0)
sensex = pd.read_excel("India Debt and Sensex.xlsx", sheet_name="Sensex Data",
                     index_col=0)

Here Year column is made as index. After getting in the data, you will realize there is some gap in data on external debt. A few intermittent years' data is not available. For that, we can check the shape of both data.

data1 = debt.iloc[:,1]
print(data1.shape)
data2 = sensex["Close"]
print(data2.shape)

To get the data that is there in both the dataframes, you can do intersection of indexes to get common indexes from both the dataframes. In Pandas, you can use Index.intersection (otherindex) to return a new Index with elements common to the index and otherindex.

common_index = year.intersection(sensex.index)
#retrieve only common rows from sensex data since sensex is superset data in this example
data2 = sensex.loc[common_index]["Close"]

Now both data is of the same shape. Now we can plot it.

fig, ax1 = plt.subplots(figsize = (10,5))
color = 'tab:red'
ax1.set_xlabel('Year')
ax1.set_ylabel('Ratio of External Debt to GDP', color=color)
ax1.plot(year, data1, color=color)
ax1.tick_params(axis='y', labelcolor=color)

Twinx function is the trick to make both data share the same X axis. It creates a new Axes instance with an invisible x-axis and an independent y-axis positioned opposite to the original one.

ax2 = ax1.twinx()  # instantiate a second axes that shares the same x-axis
color = 'blue'
ax2.set_ylabel('BSE Sensex', color=color)  # we already handled the x-label with ax1
ax2.plot(year, data2, color=color)
ax2.tick_params(axis='y', labelcolor=color)
fig.tight_layout()
plt.show()

You will see a plot that shares the X axis, in this example - Year. Prima facie it seems there is a correlation between this data, but an economist would be the best person to analyze it.