Build a Web App to Group & Plot Excel Files in Python with Streamlit
Language:
Did you know that you can quickly develop web applications in just a few lines of code using Python? Streamlit is a Python module that makes it very easy to quickly developed web apps. In this particular web app, we will be transforming & visualizing Excel files. Once the data has been group, the user can download the interactive chart and the corresponding Excel workbook.
📝 Resources:
Download the Project Folder here:
Download Here
Deploy the app for free on the internet: https://youtu.be/nJHrSvYxzjE
👩💻 Source Code:
import streamlit as st # pip install streamlit
import pandas as pd # pip install pandas
import plotly.express as px # pip install plotly-express
import base64 # Standard Python Module
from io import StringIO, BytesIO # Standard Python Module
def generate_excel_download_link(df):
# Credit Excel: https://discuss.streamlit.io/t/how-to-add-a-download-excel-csv-function-to-a-button/4474/5
towrite = BytesIO()
df.to_excel(towrite, encoding="utf-8", index=False, header=True) # write to BytesIO buffer
towrite.seek(0) # reset pointer
b64 = base64.b64encode(towrite.read()).decode()
href = f'<a href="data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,{b64}" download="data_download.xlsx">Download Excel File</a>'
return st.markdown(href, unsafe_allow_html=True)
def generate_html_download_link(fig):
# Credit Plotly: https://discuss.streamlit.io/t/download-plotly-plot-as-html/4426/2
towrite = StringIO()
fig.write_html(towrite, include_plotlyjs="cdn")
towrite = BytesIO(towrite.getvalue().encode())
b64 = base64.b64encode(towrite.read()).decode()
href = f'<a href="data:text/html;charset=utf-8;base64, {b64}" download="plot.html">Download Plot</a>'
return st.markdown(href, unsafe_allow_html=True)
st.set_page_config(page_title='Excel Plotter')
st.title('Excel Plotter 📈')
st.subheader('Feed me with your Excel file')
uploaded_file = st.file_uploader('Choose a XLSX file', type='xlsx')
if uploaded_file:
st.markdown('---')
df = pd.read_excel(uploaded_file, engine='openpyxl')
st.dataframe(df)
groupby_column = st.selectbox(
'What would you like to analyse?',
('Ship Mode', 'Segment', 'Category', 'Sub-Category'),
)
# -- GROUP DATAFRAME
output_columns = ['Sales', 'Profit']
df_grouped = df.groupby(by=[groupby_column], as_index=False)[output_columns].sum()
# -- PLOT DATAFRAME
fig = px.bar(
df_grouped,
x=groupby_column,
y='Sales',
color='Profit',
color_continuous_scale=['red', 'yellow', 'green'],
template='plotly_white',
title=f'<b>Sales & Profit by {groupby_column}</b>'
)
st.plotly_chart(fig)
# -- DOWNLOAD SECTION
st.subheader('Downloads:')
generate_excel_download_link(df_grouped)
generate_html_download_link(fig)