How To Automate Excel Using Python | Combine Files & Create Charts 🤓
Language:
In this tutorial, I will show you, how you can automate your excel tasks using python. In particular, I am going to show you how you can combine excel files & create charts out of them.
📝 Resources:
Download the Python Code and files used in the tutorial:
Download HerePandas Cheat Sheet: https://pandas.pydata.org/Pandas_Cheat_Sheet.pdf
👩💻 Source Code:
import pandas as pd
import datetime as dt
import calendar
import plotly
import plotly.express as px
# Specify Files Names [ff the files are NOT in the same directory as the script, please use the absolute path to the files]
files = ['January.xlsx', 'February.xlsx', 'March.xlsx']
# // Get File Names dynamically
# import os
# import glob
# extension = 'xlsx'
# files = glob.glob('*.{}'.format(extension))
combined = pd.DataFrame()
for file in files:
df = pd.read_excel(file)
df['Date'] = df['Date'].dt.date
df['Day'] = pd.DatetimeIndex(df['Date']).day
df['Month'] = pd.DatetimeIndex(df['Date']).month
df['Year'] = pd.DatetimeIndex(df['Date']).year
df['Month_Name'] = df['Month'].apply(lambda x: calendar.month_abbr[x])
combined = combined.append(df,ignore_index = True)
# Create Bar Chart
fig = px.bar(combined, x='Month_Name', y='Sales', title='Sales 1Q 2020')
#Save Bar Chart and Export to HTML
plotly.offline.plot(fig, filename='Sales_1Q_2020.html')
# Send Report as attached mail
# Create more charts
# ...
# Export combined files to Excel
combined.to_excel('Sales_1Q2020.xlsx', index = False, sheet_name='1Q 2020 Sales')