Slidebar filter selection

[MAGDI_MOHAMED]

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" 
   

)

[Shawn_Pereira]

Hi @MAGDI_MOHAMED , you could try the following:

1. I assume that ‘Product Type’ exists as a column within your dataframe (as it is not shown in your output)
2. replace the last line of your code with:
   df_selection = df.get([“SKU”, “Product Name”, “Available Stock”, “Out of Stock”, “Prices”]) \
   .where( (df[‘Product Type’] == Model) & (df.SKU == Item_number) )
   st.dataframe(df_selection)

Cheers

[MAGDI_MOHAMED]

Hi @Shawn_Pereira, Thank you so much for your help will try that.
As for “Product type” , i’m trying to make it as my first filter to choose which sheet that I want to search for a data in. That is how I’m trying to link it to choose from all sheets by their names:

#filter 1 ( select product type)
Model = st.sidebar.selectbox(
" Select the product type:",
[stockUS, StockCA, labelUS, labelCA]

Then for filter 2, based on the selected sheet in filter 1, it should be asking to enter either column 1 “Product Name” or 2 “SKU”.

So far filter 2 works fine with selecting either column 1 or 2 as shown here:

#filter 2 (item name or SKU)
Item_number = st.sidebar.selectbox(
" Select the Item Name/SKU:",
options = df[“SKU”].unique())

but when I try to make it accept searching for both of them it doesn’t work:
options = df[“SKU” and “Product Name”].unique()

Would appreciate some recommendations for making those filters.

Thank you very much

[MAGDI_MOHAMED]

Hello @Shawn_Pereira … Do you may have any other suggestions please?