The world’s leading publication for data science, AI, and ML professionals.

Turn Excel Into a Beautiful Web Application Using Streamlit

Present your data as an interactive dashboard web application using the python library Streamlit

The Streamlit Series

Photo by Matheus Bertelli from Pexels
Photo by Matheus Bertelli from Pexels

What is Streamlit?

Streamlit is an open-source Python library that makes it easy to create and share beautiful, custom web apps for machine learning and Data Science projects [1]

One of the main features of Streamlit is it provides you a Jupyter Notebook-like environment where your code is updated live as you save your script. This helps a lot, especially for the initial app development stage.

Image from Streamlit.io. GIF by Author
Image from Streamlit.io. GIF by Author

Problem with Excels and Data Science Projects

The problem that exists with excel and data science projects is the lack of an easy way to present the results (a.k.a production deployment). For an excel file to be presented, you will either need to link it with visualization tools such as Power BI, Tableau, or Powerpoint.

Whereas for a data science project to be implemented, you will need to implement a backend server such as Django, Flask, and a front-end UI such as React and Vue.js.

These complications make data sharing with excels and data science projects extremely BORING and TEDIOUS.

Luckily with the help of Streamlit, we can easily create an interactive web application out of Excel spreadsheets and deploy data science projects easily. 🙌

At the end of this article, you will be able to create an interactive excel dashboard web application which enable user to filter the data, visualize graph and access easily using URL. Alternatively, you can visit the web application [here](https://github.com/manfye/Streamlit-App) and the repository here

Interactive excel dashboard. Image by Author
Interactive excel dashboard. Image by Author

Getting started:

We will be using the World Happiness Report 2021 from Kaggle as our dataset for this article, feel free to download it below:

World Happiness Report 2021

Install Streamlit via pip install

pip install streamlit

Verify your install via type in Streamlit CLI in command prompt

streamlit hello

That’s it! In the next few seconds, the sample app will open in a new tab in your default browse.

To make your own apps, create a blank Python file app.py, and run it with streamlit CLI. Then, click the localhost URL to enter your first streamlit web apps!

streamlit run app.py
Starting up streamlit web app. Image by Author
Starting up streamlit web app. Image by Author

Understanding the UI

By default Streamlit already have 2 places to put your code and widget in, which are the sidebar and content. You can add elements and widget in the content area simply using:

import streamlit as st
st.[element_name]

You can add elements and widget in the sidebar simply using:

import streamlit as st
st.sidebar.[element_name]

You can put any element in the sidebar as per content area, the only elements that aren’t supported are st.echo and st.spinner at the time of writing.

Area of Streamlit App. Image by author
Area of Streamlit App. Image by author

Load the data

Loading data from Excel and CSV can be done using pandas:

import pandas as pd
#For Excel File
df = pd.read_excel("world-happiness-report-2021.xlxs")
#For CSV File
df = pd.read_csv("world-happiness-report-2021.csv")

Display Widget

Display widget is pretty straightforward, you want a Text, just write it as:

st.title("World Happiness Index 2021:")

If you want it to appear in the sidebar, just write the code as:

st.sidebar.title("World Happiness Index 2021:")

If you want an image, just write:

st.image("https://images.pexels.com/photos/573259/pexels-photo-573259.jpeg?cs=srgb&dl=pexels-matheus-bertelli-573259.jpg&fm=jpg", caption='World Happiness Dataset')

If you want to display a data frame, just write:

st.write(filtered_df)
Printed Data Frame. Image by Author
Printed Data Frame. Image by Author

That’s how simple it works in Streamlit!

Controller Widget

Streamlit has a "State-like" component function where the interaction of the user with the widget will change the state of the variable. And then, the new value of the variable will be used to rerender the components of the whole project.

In this project, we will create a select box widget that can be used to filter the country and a slider to filter the ladder score in the sidebar as an example.

#Country Select Filter
country_list = ["All","Western Europe", "South Asia", "Southeast Asia", "East Asia", "North America and ANZ","Middle East and North Africa", "Latin America and Caribbean","Central and Eastern Europe","Commonwealth of Independent States","Sub-Saharan Africa"]
select = st.sidebar.selectbox('Filter the region here:', country_list, key='1')
if select =="All":
filtered_df = df
else:
filtered_df = df[df['Regional indicator']==select]
#Ladder Score Slider
score = st.sidebar.slider('Select min Ladder Score', min_value=5, max_value=10, value = 10) # Getting the input.
df = df[df['Ladder score'] <= score] # Filtering the dataframe.

You will get the widget that can filter the data frame as below:

Controller widget. Image by Author
Controller widget. Image by Author

Visualization Widget

Streamlit supports several different charting libraries such as Matplotlib, Seaborns, Ploty, Altair charts. It also provides a few native charts such as line chart and area chart which can be called by a line of code, for example:

#Line Chart
st.line_chart(data=None, width=0, height=0, use_container_width=True)
#Area Chart
st.area_chart(data=None, width=0, height=0, use_container_width=True)

However, in this tutorial, we will be using Plotly express for the scatter chart and bar chart. Then, we use seaborn for the heatmap chart as below:

import plotly.express as px
import seaborn as sns
#Scatter Chart
fig = px.scatter(filtered_df,
x="Logged GDP per capita",
y="Healthy life expectancy",
size="Ladder score",
color="Regional indicator",
hover_name="Country name",
size_max=10)
st.write(fig)
#Bar Chart, you can write in this way too
st.write(px.bar(filtered_df, y='Ladder score', x='Country name'))
#Seaborn Heatmap
#correlate data
corr = filtered_df.corr()
#using matplotlib to define the size
plt.figure(figsize=(8, 8))
#creating the heatmap with seaborn
fig1 = plt.figure()
ax = sns.heatmap(corr,
vmin=-1, vmax=1, center=0,
cmap=sns.diverging_palette(20, 220, n=200),
square=True
)
ax.set_xticklabels(
ax.get_xticklabels(),
rotation=45,
horizontalalignment='right'
);
st.pyplot(fig1)

Note: Notice that for Seaborn it is an axes component, so you cannot directly use st.write to render the chart, whereas you must use st.pyplot to render the components.


Deployment via Streamlit Sharing

Streamlit has another unique feature called streamlit sharing, where they help you to host your streamlit app on their website. Simply prepare a requirements.txt file in the same folder as app.py will do the magic.

The requirements.txt file tells the system what python package the app will be using, in our case, it will be:

streamlit==0.83.0
numpy==1.18.5
pandas==1.2.4
matplotlib==3.4.2
plotly-express==0.4.1
seaborn==0.11.1

Click deploy and you will get the URL of your web apps. 🎉🎉

Streamlit Sharing deployment Sharing. Image by Author.
Streamlit Sharing deployment Sharing. Image by Author.

*At the time of writing, Streamlit Sharing required an invitation from Streamlit. It took around 2 working days for them to approve my account

Deployment via Heroku

Alternative to the recommended feature, you can host your apps at Heroku or any other custom host like digital ocean, AWS, or google cloud. I will show the method of hosting in Heroku as it is a Free solution.

To host in Heroku, you will need the exact same requirement.txt as above in the exact same location. Besides that, you will need 2 additional files which are:

a) Procfile:

web: sh setup.sh &amp;&amp; streamlit run app.py

b) setup.sh:

mkdir -p ~/.streamlit/
echo "
[general]n
email = "<youremail>"n
" > ~/.streamlit/credentials.toml
echo "
[server]n
headless = truen
port = $PORTn
enableCORS = falsen
n
" > ~/.streamlit/config.toml

Copy exactly the same setup as above and you will have the folder structure as below:

Project folder structure. Image by Author
Project folder structure. Image by Author

I had hosted the same project both in Heroku and Streamlit Sharing, you can check on it and compare the speed and functionality yourself. In my opinion, both ways have their pro and cons, Streamlit Sharing offers free hosting and Hosting in Heroku have a limitation of 2 Free hosting per account.


Final Thought

In this article, we had covered the basics of Streamlit which include installation, the basic concept of scripting in Streamlit, dashboard design, chart Visualization, and deployment of the web app.

Streamlit is a new paradigm of data presentation tools that have enormous potential. It solves the last-mile problem in data science which is to deliver the project easily to end-users regardless of layman or peer data scientist. It took me less than 1 hour to understand the Streamlit concept and fall in __ ❤️ with it, I hope my sharing can spark your interest in Streamlit too!

Lastly, Thank you very much for reading my article.

Side notes:

Here’s a video to introduce what is Streamlit in under 100 seconds:

If you are interested in excel automation, this article is a must-read:

Automate Excel with Python

My other articles included:

Automate Google Sheet Reporting in 5 minutes

Predicting Hepatitis Patient Survivability (UCI Dataset)

References:

[1] https://streamlit.io

[2] https://doc.streamlit.io/


Related Articles