Creating subplot using for loop from multiple excel sheets in python

Question

I am trying to create subplots using for loop which data is based on multiple excelsheets. You can see the script below.

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

#import the excel file
path ='F:\Backup\JN\TOR\TOR well py.xlsx'
data= pd.ExcelFile(path)

#some intro before getting into the for in loop
sheets = data.sheet_names
well = ''
totalsheets = len(sheets)
print(sheets)
print(totalsheets)

for n in range(totalsheets):
    fig, axs=plt.subplots(1, totalsheets, figsize=(20,25))
    for i in sheets:
        well=pd.read_excel(data, sheet_name=i)
        axs[n].set_xlabel('Temperature ($^o$C), Pressure (bar)')
        axs[n].set_ylabel('Elevation (masl)')
        axs[n].set_title(('Well-'+str(i)+ '\n' )+ (str(well['ket'][0])))
        axs[n].plot(well['T'], well['mdpl pt'], marker='o', color='blue', label='Temperature')
        axs[n].plot(well['P'], well['mdpl pt'], marker='o', color='crimson', label='Pressure')

Instead getting a subplot figure with 1 row and 11 columns where each subplot represents each data sheets, the script generated 11 subplot figures (1 row, 11 columns). The data that is depicted on the subplot only coming from the last sheets 'P1' which is plotted into each subplot sequentially from the first column, then second, third, fourth and so on (see pict below, I only show 3 out of 11 figures).

After seeing the result, I think I did something wrong with the for loop script. Please help, thank you very much.

['E1', 'E2', 'E3', 'E4', 'G1', 'C1', 'C2', 'A1', 'A2', 'A3', 'P1']

11

After I moved the fig outside the for loop, I succeeded to create a figure with 11 subplots. But all the 11 subplots only show the data from one sheet (only the P1) instead from 11 sheets in the excel to fill each subplot ['E1', 'E2', 'E3', 'E4', 'G1', 'C1', 'C2', 'A1', 'A2', 'A3', 'P1']. Did I miss something within the for loop script? (see the screenshot below)

yes it is. what I showed above is just three out of eleven figures. — muhammad tajul arifin

Karthik Karthik · Accepted Answer · 2020-09-15T14:41:10

Please check the snippet.

You have created fig, axs=plt.subplots(1, totalsheets, figsize=(20,25)) inside for loop. Here your totalsheets=11 hence you recieved 11 plots in 11 rows.

Just place your fig outside for loop, your issue will be solved.

import numpy as np
import matplotlib.pyplot as plt

totalsheets=11
fig, axs = plt.subplots(nrows=1, ncols=totalsheets, figsize=(12,5))
x = [1, 2, 3]
y = [4, 5, 6]
sheets=["a","b","c","d","e","f","g","h","i","j","k"]
for n in range(totalsheets):
    for i in sheets:
        axs[n].plot(x, y)
        axs[n].set_xlabel('TP')
        axs[n].set_ylabel('EL')
        axs[n].set_title('Well-'+str(i)+ '\n' )
fig.tight_layout()
plt.show()

Edit: Based on your comment, this will fetch data from separate sheets, probable the data column names should be same

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
data= pd.ExcelFile('datas.xlsx')
sheets=data.sheet_names
fig, axs = plt.subplots(nrows=1, ncols=len(sheets), figsize=(7,5))
for n in range(len(sheets)):
    well=pd.read_excel('datas.xlsx', sheet_name=n)
    axs[n].plot(well['a'], well['b'])
    axs[n].set_xlabel('TP')
    axs[n].set_ylabel('EL')
    axs[n].set_title('Well-'+str(sheets[n])+ '\n' )   
fig.tight_layout()
plt.show()

Creating subplot using for loop from multiple excel sheets in python

1 Answers