Transform Excel Data into Interactive Dashboards with Python

Table of Contents

1. Introduction
2. Converting Excel Data into an Interactive Dashboard
   1. Using Pandas
   2. Using Plotly
   3. Using Streamlit
3. Understanding the Dataset
   1. Supermarket Sales for Different Locations
   2. Customer Information
   3. Product Lines and Sales
   4. Time and Customer Satisfaction
4. Transforming Excel Data into a Pandas Dataframe
   1. Using the 'My Tool Belt' Add-in
   2. Reading Excel Data with Pandas
5. Setting Up the Streamlit Web App
   1. Installing Required Libraries
   2. Importing Libraries in Python
   3. Configuring the Streamlit Web App
6. Creating the Sidebar Section
   1. Filtering the Dataset Based on User Criteria
   2. Using Streamlit Multiselect for Filters
7. Displaying Key Performance Indicators (KPIs)
   1. Calculating Total Sales, Average Rating, and Average Sales per Transaction
   2. Formatting and Displaying KPIs
8. Plotting Sales by Product Line
   1. Aggregating Sales Data by Product Line
   2. Creating a Bar Chart with Plotly
   3. Updating the Bar Chart with Filters
   4. Enhancing the Visualization
9. Plotting Sales by Hours
   1. Extracting Hour Information from Time Column
   2. Grouping Sales Data by Hour
   3. Creating a Bar Chart for Hourly Sales
   4. Placing Bar Charts Side by Side
10. Styling the Web App
    1. Removing Unnecessary Elements
    2. Applying Custom CSS
    3. Customizing the Streamlit Theme
11. Conclusion

Converting Excel Data into an Interactive Dashboard Using Python, Pandas, Plotly & Streamlit

In this tutorial, we will explore how to convert a simple Excel sheet into an interactive dashboard using Python. Specifically, we will be using the Pandas, Plotly, and Streamlit libraries. The AdVantage of using Streamlit is that You can Create web applications directly in Python without needing to know HTML, CSS, or JavaScript.

The dashboard will allow the user to filter the dataset and see the updated Key Performance Indicators (KPIs) and charts accordingly. Throughout the coding process, you will learn useful tips and tricks for data manipulation with Pandas, creating interactive visualizations with Plotly, and building a dashboard with Streamlit.

But before we dive into the coding part, let's take a quick look at the data. We have supermarket sales data for three different locations. The dataset includes information about whether the customer was a member, the customer's gender, the product lines, and the total sales including taxes. Additionally, we have the time of the purchase and the customer satisfaction rating. These are the columns and information we will Visualize in our dashboard.

To work with the data in Python, we will convert the Excel data into a Pandas dataframe. There are different ways to do this, but we will be using an add-in called 'My Tool Belt'. Although it saves time, you don't necessarily need this add-in to follow along with the tutorial. We will also be using the 'pandas' and 'openpyxl' libraries to Interact with Excel. Make sure you have these libraries installed before proceeding.

To create the web app, we will use the Streamlit library, which can be installed via pip. We also need the Plotly Express library for creating visualizations, which can be installed in the same way.

Once we have all the required libraries, we will import them into our Python script. First, we will set up some basic configurations for our web app using the st.set_page_config function. This includes setting the page title and favicon. We will also specify the layout of the page, opting for the 'wide' layout to utilize the entire screen.

After the initial setup, we can start displaying the data on our web app. Initially, we will display the dataframe instead of printing it to the console. To do this, we will use the st.dataframe function.

Next, we will build the sidebar section where the user can select filter criteria. We will start with filtering by city and use the Streamlit multiselect component to create the dropdown menu. We will also include filters for customer types and gender. The selected options will be stored in variables that we will use to filter the dataframe later.

With the filters in place, we can now Apply them to the dataframe using the query method. By querying the columns based on the user's selection, we will create a filtered dataframe called 'df_selection'. To test the selection, we will return the filtered dataframe to the web app instead of the original dataframe.

In addition to filtering the data, we will also display some KPIs on the main page of our web app. These KPIs will include the total sales, the average rating, and the average sales per transaction. After calculating these values, we will display them in three separate columns using the st.columns function.

Moving on, we will plot sales by product line using a bar chart. To do this, we need to aggregate the sales data by product line. We will use the Pandas groupby method to group the dataframe by product line and calculate the sum of the values. Once we have the aggregated data, we will create the bar chart using the Plotly Express library. The chart will have product lines on the y-axis and total sales on the x-axis. We will plot the chart using the st.plotly_chart function.

Similarly, we will plot sales by hours. To do this, we first need to extract the hour information from the time column. We will convert the time column to a DateTime object and then extract the hour. After that, we will group the sales data by hour and create a bar chart. The chart will have hours on the x-axis and total sales on the y-axis. Like before, we will plot the chart using the st.plotly_chart function.

To enhance the appearance of our web app, we will apply some styling. We will remove unnecessary elements such as the hamburger menu icon, the "Made with Streamlit" footer note, and the colorful header. We will achieve this by injecting custom CSS into our web app. Additionally, we will customize the overall theme of the Streamlit app by creating a .streamlit/config.toml file in the root folder of our application. This will allow us to set parameters like the primary color and background color.

In conclusion, this tutorial has demonstrated how to convert an Excel sheet into an interactive dashboard using Python, Pandas, Plotly, and Streamlit. By following the step-by-step process, you can build your own interactive dashboards and customize them to meet your specific requirements. Whether you want to analyze sales data, visualize trends, or present information in a user-friendly manner, this tutorial provides the tools and techniques to achieve that.

Thank you for watching this tutorial, and if you have any questions, please leave them in the comments.