Skip to content
  • YouTube
  • FaceBook
  • Twitter
  • Instagram

Data Analytics Ireland

Data Analytics and Video Tutorials

  • Home
  • Contact
  • About Us
    • Latest
    • Write for us
    • Learn more information about our website
  • Useful Links
  • Glossary
  • All Categories
  • Faq
  • Livestream
  • Toggle search form
  • Python tutorial: Pandas groupby ( Video 1) Python
  • IndexError: index 2 is out of bounds for axis 0 with size 2 Index Error
  • R tutorial – How to sort lists using rstudio R Programming
  • What is data profiling and its benefits? data profiling
  • Tableau Desktop versus Tableau Server data visualisation
  • Tkinter GUI tutorial python – how to clean excel data Python
  • python sort method python method
  • How to remove unwanted characters Python Data Cleansing

Python Tutorial: How to create charts in Excel

Posted on May 6, 2020July 10, 2022 By admin No Comments on Python Tutorial: How to create charts in Excel

Estimated reading time: 4 minutes

Taking it further with a python excel chart.

We have created some video content here using Python with Excel that illustrates the different ways you can leverage Python to

  • Data cleanse
  • Find unwanted characters
  • And see if the file is empty before import!

What this video is giving as output

Here we are looking to introduce charts in Excel, and how how to use Python to easily work with your data and export to an excel sheet.

Below is the final output of our two charts, this is for illustrative purposes, and is taken from the Irish Government’s website as at 1st May 2020, importing the cell ranges associated with them.

A barchart of covid cases bullt in Python
a line chart of covid cases built in Python

How we went about it

Step 1 – Importing the data

import pandas as pd
import openpyxl


importfile = openpyxl.load_workbook("C://dataanalyticsireland/Python Code/youtube/codeused/files/Covid19CountyStatisticsHPSCIreland01052020.xlsx")

#Telling the program to go to each individual sheet
Dublin = importfile["Dublin"]
Cork = importfile["Cork"]
Limerick = importfile["Limerick"]
Galway = importfile["Galway"]

#Telling the program for each sheet , create a dataframe.
dfDublin = pd.DataFrame(Dublin.values)
dfCork = pd.DataFrame(Cork.values)
dfLimerick = pd.DataFrame(Limerick.values)
dfGalway = pd.DataFrame(Galway.values)

Step 2 – Splitting the different sheets out into different data frames

### Showing to break down here, further on I cocatenate the dataframes  ###

#This brings in all the values from the sheet into a variable so they can be read.
datadublin = Dublin.values
datacork = Cork.values
datalimerick = Limerick.values
datagalway = Galway.values
#This tells the program that the headers are on row 0.
columnsdublin = next(datadublin)[0:]
columnscork = next(datacork)[0:]
columnslimerick = next(datalimerick)[0:]
columnsgalway = next(datagalway)[0:]
# The imported file has the column headings on row 0, moving them to be the actual column headings instead of numerical values.
dfDublin = pd.DataFrame(datadublin, columns=columnsdublin)
dfCork = pd.DataFrame(datacork, columns=columnscork)
dfLimerick = pd.DataFrame(datalimerick, columns=columnslimerick)
dfGalway = pd.DataFrame(datagalway, columns=columnsgalway)

Step 3 – Exports all three data frames to a separate tab each to the below file

# This starts the process of where to save the output by creating a writer variable
writer = pd.ExcelWriter('C://dataanalyticsireland/Python Code/youtube/codeused/files/Covid19output01052020.xlsx')
# Save the dataframe "Dublin" to Dublin sheet etc...
dfDublin.to_excel(writer, 'Dublin')
dfCork.to_excel(writer, 'Cork')
dfLimerick.to_excel(writer, 'Limerick')
dfGalway.to_excel(writer, 'Galway')
# save the excel file
writer.save()

Step 4 – Merging all the data to allow the charts to be created easily

mergedPDList = [dfDublin,dfCork,dfLimerick,dfGalway]  # Creating a new merged list of the dataframes, easier when doing charts below
mergeddf = pd.concat(mergedPDList) # physical merging of the dataframes

Step 5 – Creating a bar chart

import matplotlib.pyplot as plt
import matplotlib.pyplot as pltline
import seaborn as sns
#from openpyxl.drawing.image import Image
#This makes the seaborn library over ride the matplotlib properites for styling
sns.set()

plt.figure(figsize =(9,6))
plt.bar(x = mergeddf['CountyName'],
       height = mergeddf['ConfirmedCovidCases']
       )
plt.xticks(rotation = 45)
plt.title("No of Confirmed Covid Cases in the Republic of Ireland", fontsize=25, fontweight='bold')
plt.xlabel('Area')
plt.ylabel('No of cases')
plt.width = 35
img = plt.savefig('C://dataanalyticsireland/Python Code/youtube/codeused/files/barchart.png',bbox_inches='tight')
plt.show()

Step 6 – Creating a line chart

x = dfDublin['ConfirmedCovidCases']
x1 = dfCork['ConfirmedCovidCases']
x2 = dfLimerick['ConfirmedCovidCases']
x3 = dfGalway['ConfirmedCovidCases']
#Sets the size of the output before it is created
pltline.figure(figsize=(15,15))
pltline.plot(x)
pltline.plot(x1)
pltline.plot(x2)
pltline.plot(x3)
pltline.legend(['Dublin', 'Cork','Limerick','Galway'], fontsize="x-large")
pltline.ylabel('No of cases')
pltline.xlabel('No of days elapsed')
img1 = plt.savefig('C://dataanalyticsireland/Python Code/youtube/codeused/files/linegraph.png')
pltline.show()

Step 7 – Exporting charts to the XLSX file

from openpyxl import load_workbook
#from openpyxl.drawing.image import Image
import openpyxl
#This step opens the existing file.
wsnew = load_workbook('C://dataanalyticsireland/Python Code/youtube/codeused/files/Covid19output01052020.xlsx')
#This step creates a new worksheet called Barchart
newsheet = wsnew.create_sheet("Bar Chart",4) #Creates a new sheet in fourth position
newsheet1 = wsnew.create_sheet("Line Graph",5) #Creates a new sheet in fifth position
#Adding the image to Newsheet which is called "Barchart" in the excel file
img = openpyxl.drawing.image.Image('C://dataanalyticsireland/Python Code/youtube/codeused/files/barchart.png')
img1 = openpyxl.drawing.image.Image('C://dataanalyticsireland/Python Code/youtube/codeused/files/linegraph.png')
img.anchor = 'B2' # Tells what cell to put the imahe in
img1.anchor = 'B2' # Tells what cell to put the imahe in
newsheet.add_image(img) # Adds the image
newsheet1.add_image(img1) #Adds the image
wsnew.save('C://dataanalyticsireland/Python Code/youtube/codeused/files/Covid19output01052020.xlsx')
wsnew.close()

In the below video on creating a python excel chart, we have approached this as follows:

  • Created four separate data frames, they are the four regions that will feed into the creation of the graphs.
  • Separate to this, we merged the four data frames into one, to use with the bar chart.

And to finish off

  • We created matplotlib charts and
  •  We exported the file to Excel using openpyxl.

If you like what this video has explained, please click to see our YouTube channel for more informative videos.

Data Analytics Ireland

Python Tutorial, Python working with excel Tags:Data, Data Analysis, Data Analytics, Excel Spreadsheet, Graphs, Learn python, Matplotlib, Python, Python Tutorial

Post navigation

Previous Post: How To Validate Cell Values In Excel
Next Post: Python Tutorial: How to validate data using tuples

Related Posts

  • Python Dictionary Interview Questions Python
  • how to copy/paste special a range of cells with xlwings Python
  • How to Add Formulas to Excel using Python numpy
  • Python tutorial: Create an input box in Tkinter Python
  • How to data cleanse a database table Python Data Cleansing
  • How to import data into excel Python Tutorial

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Select your language!

  • हिंदी
  • Español
  • Português
  • Français
  • Italiano
  • IndexError: single positional indexer is out-of-bounds Index Error
  • Welcome to Data Analytics Ireland Livestream
  • How to create a class in Python class
  • How to show percentage differences between files in Python CSV
  • how to create an instance of a class class
  • how to write subqueries in SQL SQL
  • Python tutorial: How to create a graphical user interface in Tkinter Python
  • What Is An Array In Python? array

Copyright © 2023 Data Analytics Ireland.

Powered by PressBook Premium theme

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT