Excel Python Automation with Streamlit Dashboard

steps

1. Initialize the environment: Import necessary libraries and configure the Streamlit app.
2. Load and process Excel data: Use pandas to read and clean the dataset.
3. Create the interactive dashboard:
   • Add Streamlit widgets for filtering data.
   • Develop visualizations using Plotly.
   • Display calculated KPIs and charts.
4. Style and deploy the app: Optimize appearance and deploy using Streamlit sharing.

steps

1. Import pandas and read your Excel file using pd.read_excel. For example:
   pythonCopy

   import pandas as pd
   
   df = pd.read_excel("supermarket_sales.xlsx")

2. Clean the data as needed. Example operations include:
   • Parsing a date column:
     pythonCopy

     import pandas as pd
     
     df = pd.read_excel("supermarket_sales.xlsx")
     df['Date'] = pd.to_datetime(df['Date'])

   • Dropping unnecessary columns:
     pythonCopy

     import pandas as pd
     
     df = pd.read_excel("supermarket_sales.xlsx")
     df = df.drop(['Column_to_Remove'], axis=1)

3. Use Streamlit to display data for verification:
   pythonCopy

   import streamlit as st
   import pandas as pd
   
   df = pd.read_excel("supermarket_sales.xlsx")
   st.dataframe(df)

steps

1. Set up filtering options:
   pythonCopy

   import streamlit as st
   
   cities = df['City'].unique()
   selected_cities = st.sidebar.multiselect("Select Cities", options=cities, default=cities)
   df_filtered = df[df['City'].isin(selected_cities)]

2. Create a Plotly visualization:
   pythonCopy

   import plotly.express as px
   import streamlit as st
   
   fig = px.bar(df_filtered, x="Product line", y="Total", color="Gender", title="Sales by Product Line")
   st.plotly_chart(fig)

3. Update charts dynamically based on user inputs.

steps

1. Create a requirements.txt file with all the dependencies:
   Copy

   pandas
   openpyxl
   streamlit
   plotly-express

2. Use Streamlit sharing for deployment:
   • Push your project to a GitHub repository.
   • Log in at Streamlit Sharing.
   • Deploy your app with the repository URL.
3. Verify functionality once deployed by testing various filters and interactions.