Hello guys, i’m new to pandas and trying to make a web app that can display info from an Excel file.
The file consists of 4 sheets as the following: StockUS, StockCA, labelUS, labelCA. Each sheet share the same header titles except 2 more in StockUS and labelUS. And each sheet contains data for product name and SKU.
I’m trying to build a slide bar to filter the results based on two selection, first selection should be to select which sheet, and the second filter should be based on either the product name or SKU (Shown in the picture).
I created a df for each sheet, then combined them all in dfAll to use it in the query part.
So far the app displays the selection for both first and second sheet ( Even if I chose 3ed or 4th sheet in filter 1), and in the second filter it only shows the SKU, when adding product name it ignores the SKU and shows only the product name list (Line 88).
I know it should be quite easy but i’m kinda stuck at this point. So I would really appreciate your help.
import streamlit as st
import pandas as pd
import plotly.express as Px
from PIL import Image
import numpy as np
from datetime import datetime
# -- Set page config
st.set_page_config(page_title='DYMO NA')
# Title the app
st.header('DYMO NA Inventory')
#app main image
image = Image.open('F:\DYMO NA App\images\logo.svg.PNG')
st.image(image,
use_column_width=True )
st.markdown ('##')
#App title
st.title("Product information")
st.markdown('##')
#Import Excel file
excel_file = 'Inventory-1.6.xlsx'
stockUS = ' Stock US'
StockCA = 'Stock Canada'
labelUS = 'US Labels stock'
labelCA = 'CA Labels stock'
#df for sheet 1
df1 = pd.read_excel(excel_file,
sheet_name= stockUS,
usecols='A,B,C,F,H,I',
header=1)
#st.dataframe(df1)
#df for sheet 2
df2 = pd.read_excel(excel_file,
sheet_name= StockCA,
usecols='A,B,C,F',
header=1)
#st.dataframe(df2)
#df for sheet 3
df3 = pd.read_excel(excel_file,
sheet_name= labelUS,
usecols='A,B,C,F',
header=1)
#st.dataframe(df3)
#df for sheet 4
df4 = pd.read_excel(excel_file,
sheet_name= labelCA,
usecols='A,B,C,F',
header=1)
#st.dataframe(df4)
#merge all df
dfAll = [df1, df2, df3, df4]
df = pd.concat(dfAll)
#Slidebar filter
st.sidebar.header("Choose your product")
#filter 1 ( select product type)
Model = st.sidebar.selectbox(
" Select the product type:",
[stockUS, StockCA, labelUS, labelCA]
)
#filter 2 (item name or SKU)
Item_number = st.sidebar.selectbox(
" Select the Item Name/SKU:",
options = df["SKU"].unique())
**Line 88** #options = df["SKU" and "Product Name"].unique())
df_selection = df.query (
"Product Name == @Model" and "SKU == @Item_number"
)