Home > database >  How to create a comparison plot from an Excel file with many worksheets
How to create a comparison plot from an Excel file with many worksheets

Time:05-07

I want to create a plot that will have in the x axis the months and for each country it will show the Orders per month.


Attempting to create a function that reads an excel file and is able to support any number of countries using *args as a parameter. Then, it should loop though the countries that may be given by the end user and create a comparison plot.

Example of the excel that will be imported:

Here

spain = {'Country': ['Spain', 'Spain', 'Spain', 'Spain', 'Spain', 'Spain', 'Spain', 'Spain', 'Spain', 'Spain', 'Spain', 'Spain'],
         'Month': ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'],
         'Temp': [10, 11, 13, 16, 21, 26, 29, 29, 25, 20, 16, 12],
         'Records': [7, 8, 8, 7, 7, 8, 10, 11, 8, 7, 7, 7],
         'Orders': [70, 70, 66, 60, 58, 50, 43, 43, 54, 63, 69, 70]}

Notes about the parameters of the function below:

  1. file is the path of the xls file
  2. month is the Month column of the xls that contains the months
  3. column is the Orders column of the excel
  4. *countries will be any number of countries (located in each sheet of the xls that is shown in the screenshot) that might be searched by the user and will be compared in the plot.

Note: The x_axis of the plot will be the months and each line in the plot will show the number of orders of each country for each month.

Here is what I have created, but it does not work properly.

import matplotlib.pyplot as plt
import pandas as pd

def PlotDataPerMonth(file,month,column,*countries):
    for i in range(len(countries)): # count based on the given countries that have been given
        data = pd.read_excel(file) # Using pd.read_excel() is required
        print(data)


    for name, data in data.groupby('countries'):
        plt.plot(data[month], data[column], label=countries) # month should be the 'month' column 
        plt.xlabel('Months')
        plt.ylabel('Number of Orders')
        plt.legend()
        plt.show()
    
    

The function with actual data:

PlotDataPerMonth('Book1.xlsx','Month','Orders',['Spain','Italy'])

The xls in question: enter image description here

PlotDataPerMonth('Book1.xlsx', 'Orders', ['Spain', 'Italy'])

enter image description here

  • Related