How To Automate Excel Using Python | Combine Files & Create Charts 🤓

hey guys in this tutorial i will show you how you can automate your excel task using python imagine you get a sales report for each month and your task is it to combine those excel files into one add additional columns to the report and create an interactive chart out of it in this tutorial i will show you how you could automate this process using python if you want to jump straight to the code then go to this timestamp before coding let me warn you frankly speaking this is a simplified example in real life your data could look way messier or will contain much more information nonetheless i hope this tutorial will give you some ideas on how to automate your specific use case but why don't you use power query or vba for this task this is definitely a valid question personally i also love vba it's pretty amazing what you could do with vba also excel power query would be an excellent tool for this task it is designed to deal with large amount of data as well as cleaning and preparing it so why buffer wing using python you may ask well it kind of depends on your use case the power of python is that you could do pretty much anything if you wanted to send out the report via whatsapp afterwards upload it to the cloud create a web application out of it or pretty much any other stuff you could think of there will be most likely a way you can do it with python or it will be much easier on python compared to vba with that out of the way let us get started and have a look on the sample files i have created there are three different excel files each file contains a sales record for the respective month for instance in the january file we have the sales record for january 1st until january 31st the other extra files are in the same structure our goal is to combine those files into one file and create an interactive bar chart which shows the sales progress by month therefore i will create a new python file and call it combine underline files.py in the first lines i will do the imports of the libraries we are going to use go ahead and import pandas spd and import datetime as dt if you have not installed those libraries yet you can easily install them in your command prompt just type pip install and then the name of the package so in our case pip install pandas and pip install date time and you might also need xlrd which is a dependency of the pandas library just go ahead and also install xlrd by typing pip install xlrd now there are many ways to achieve this task the script can be very sophisticated if you want to however i will try to keep it as simple as possible here first i will create a list which holds all the names of the excel files we want to work with make sure that the names are exactly matching the first challenge will be to combine all those excel files into one single file to do so i will create an empty data frame and call it combined a data frame simply represents a table of data with rows and columns just like an excel worksheet as a next step we will loop over our excel files so the list we have created and append each file to our combined data frame to loop over the files we can write four file in files now in the loop we are going to define the action we want to do with each file and the first action will be to create a data frame from the excel file we can do this by typing df it's equal to pd dot read underline excel and then the file in the first iteration of the loop the name file will be then replaced by january xlsx in the second iteration by february xlsx and so on and so forth now we can append those single data frames to the combined data frame we have declared above by using the append method this data frame comes already with an index which we do not need hence i am going to set ignore index to true to check if this is working so far we can export this combined data frame to excel by typing combine dot to underline excel then the name of the file for the index i will set it to false as we do not need it and for the cheat name i will call it first quarter 2020 sales after saving and running the script we should have our new combined excel file and here it is now we have the sales from january to march in one file you might have noticed that the date now also includes the time information to get rid of this i will adjust the date column with the help of the datetime module and set it equal to datetime.date in this case it will only return the year the month and day let's rerun the script and see if this worked as we can see now we got rid of the timestamp for the chart i will create later it might be a good idea to save the date information in separate columns for example that we have one column with the year one with the month and another column with the day starting off with the day i will create a new column and name it day to extract the day information from the date column we can use the built in pandas function called daytime index and then the information we are looking for is the day for the month and year it will be very similar hence i will just simply copy and paste this line of code and change it to month and year let us run the script again and see what we've got alright now we have separated the date information in separate columns for readability purpose it would be also great to have the actual month name instead of the number so for month number one i would like to have an extra column which will then translate to january therefore back in the script i will create a new column called month underline name what we can do is to tick the month column and apply a function to this column essentially we are now telling pandas take this column and do something with it in our case i will iterate through the rows with a variable called x and for each x so our rows we will use the calendar library which will then return the calendar abbreviation for a given number for instance if the first row of the month column is a 1 it will return january now in order to use the library i will also need to import calendar this is a standard python library so you do not need to install it let us rerun the script and see if this was working all right this looks good and now we have the abbreviation of the month in a separate column as the last step i would like to create a bar chart to show the sales progress over the months for this i will use the plotly library go ahead and import plotly and import plotly express as px if you do not have those libraries installed yet you can easily do this by typing pip install properly and pip install plotly express in your command prompt for this tutorial i will keep the charts very simple however if you want to have different types of charts or customize it even further then you might want to check out the playlist here on my channel where i'm showing you how to create different kinds of charts using plotly i will link the playlist in the info card above for now i will create a simple bar chart and store it in a variable called fig which will be equal to a plotly express bar chart the data is coming from our combined data frame and for the x-axis i would like to display the month name and for the y-axis it should show our sales for the title i will call it sales first quarter 2020 i personally like to save this interactive bar chart to an html file so in this case i could also send it to colleagues afterwards we can do this by typing plotly.offline.plot then our figure and for the file name i will call mine salesfirstquarter2020.html as mentioned at the beginning of the tutorial the power of python is that you could do pretty much anything perhaps you want to send the file via mail create more charts or generate a powerpoint presentation send it via whatsapp and so on and so forth it obviously depends on your specific use case for this tutorial i will leave it here and just rerun the script again and here is our simple bar chart which shows the sales progress by month additionally the chart has been saved as an html file in the same directory of the script okay guys that's it for this tutorial i hope you found this tutorial useful and got some inspiration for your own specific use case if you have any questions or issues just leave them in the comment sections below and i will be happy to help you out thank you very much for watching and see you next time [Music] you

Channel: Coding Is Fun

Views: 97,945

Keywords:

Id: daeMTChs-r4

Length: 10min 57sec (657 seconds)

Published: Mon Nov 30 2020