Search code examples
pythonpython-3.xpandasfilterdropdown

Filtering pandas based on varying conditions as inputs form the user

I am trying to filter a df based on inputs from the user.. I receive the inputs from 4 drop-down menus as you can see from the picture. Each drop down is the unique() of a column from the DF however, I added the word "All" to the top of the list for the drop down menu to disable that specific filter and display the DF without that filter.
Drop Down Menus

this is how a construct the list of each menu

def unique(df,col_nme,**kwargs):
lst_nme=df[col_nme].unique()
lst_nme=list(lst_nme)
lst_nme.insert(0,"All")
return lst_nme

and then I set them for display (using streamlit) and construct the filters for pandas

lst_rprt_status = unique(df, "Reporting Status")
rprt_status = st.sidebar.selectbox("Reporting Status", lst_rprt_status)
lst_src = unique(df, "Source")
src = st.sidebar.selectbox("Source", lst_src)
lst_cntrct_type = unique(df, "Contract Type")
cntrct_type = st.sidebar.selectbox("Contract Type", lst_cntrct_type)
lst_country = unique(df, "Country")
country = st.sidebar.selectbox("Country", lst_country)

filt_status = df["Reporting Status"] == rprt_status
filt_src = df["Source"] == src
filt_cntrct_type = df["Contract Type"] == cntrct_type
filt_country = df["Country"] == country

if all the names returns with "All", i.e, the user loaded the page, it is quite an easy if statement where i just display the complete DF however, if I start to select values to filter for from the menus while other menus still at "All" or I want to change one to "All" after it was selected, then I have a problem constructing the combined filter for the DF. I tried reading about df.query but I would run into the same problem.

so basically what I am trying to do here is to have a form of filter like bleow:

        df_filtered = df[(df["Reporting Status"] == "Pending") &
                     (df["Source"] == "All") &
                     (df["Contract Type"] == "CSA") &
                     (df["Country"] == "Egypt")]["CPM"]

with the ability to remove a specific line if its associated condition == "All", in the above example, would be the df["Source"] == "All" or add it back when it is not.
I also tried to construct the full sentence with string manipulation but eventually did not work out and I do not want to run an if statements for all the combination that would produce the results unless it is really the only hope

sorry for the long post, but I was trying to be as thorough as possible

Solution

  • Can you try this? Here i am assuming text All is reserved for selecting all rows.

    What i am doing is first check if the selected input is inside the unique list, if it is not then i select all rows by creating the True boolean.

    msk1 = df["Reporting Status"] == rprt_status if rprt_status in lst_rprt_status else True
msk2 = df["Source"] == src if src in lst_src else True
msk3 = df["Contract Type"] == cntrct_type if cntrct_type in lst_cntrct_type else True
msk4 = df["Country"] == country if country in lst_country else True

    df_filtered = (df[msk1 & msk2 & msk3 & msk4])["CPM"]