Multiselect with date input not aggregating results

[Brian_P]

Summary

I have a dataframe organized by State, Membership, Admits, and Days (of the admission to a hospital). I have a sidebar w/a multiselect for State as well as date_start and date_end.

Steps to reproduce

Code snippet:

#%% Header and Filters

st.sidebar.header("Filters:")

st.markdown(f'<h1 style="font-family:sans-serif; color:white; background-color: {DarkTeal};text-align: center; font-size: 42px;"\
           >{"naviHealth Comprehensive"}</h1>', unsafe_allow_html=True)

state=st.sidebar.multiselect("State:", options=dfNavi['State'].unique(), default='North Carolina')
dfNavi=dfNavi.query("State==@state")

start_date = st.sidebar.date_input('Start Date: ')
end_date = st.sidebar.date_input('End Date: ')
dfNavi=dfNavi.query("Incurred_Month>=@start_date & Incurred_Month<=@end_date")

#%% KPI First Row

KPI1, KPI2, KPI3 = st.columns(3)

Members = sum(dfNavi['Members'])
TAT = dfNavi['TAT'].mean()
LOS = dfNavi['Days'].astype(float).divide(dfNavi['Admits'].astype(float).where(dfNavi['Admits'] != 0, np.nan))

with KPI1:
    st.markdown("*Members*")
    number1 = f"{Members:,d}"
    text = f"<h1 style='font-family:sans-serif; color:white; background-color: {Plum};\
                        text-align: center; font-size: 42px;'\
                        >{number1}</h1>"
    st.markdown(text, unsafe_allow_html=True)

with KPI2:
    st.markdown("**Turnaround Time**")
    number2 = f"{TAT:.1f}"
    text = f"<h1 style='font-family:sans-serif; color:green;\
                        text-align: center; font-size: 42px;'\
                        >{number2}</h1>"
    st.markdown(text, unsafe_allow_html=True)

with KPI3:
    st.markdown("**_Length of Stay (days)_**")
    number3 = LOS.apply(lambda x: round(x, 1))
    text = f"<h1 style='font-family:sans-serif; color:orange;\
                        text-align: left; font-size: 42px;'\
                        >{number3}</h1>"
    st.markdown(text, unsafe_allow_html=True)

When I run it, the LOS calc, which I assume is the issue since I’m a complete newbie to both python and Streamlit, it’s not aggregating the results but is instead showing each month selected in the card (or whatever the proper term of the markdown component is called).

Expected behavior:

The LOS should just be one number without the index and dtype showing. When I tried to apply the same formatting as the TAT calc it errors out with:

TypeError: unsupported format string passed to Series.format

I know I’ve got a lot of homework to do here

[Goyo]

Python doesn’t know that you want to aggregate the results, yo have to write code for things to happen. What kind of aggregation do you want?

[Brian_P]

I just want to sum up the ‘Days’ column and divide that by the sum of the ‘Admits’ column. From there I want to be able to filter by State(s) and Incurred_Month(s). It’s sort of like an average, but obviously the math is wrong if I divide admits by days then take that average. I need to aggregate the whole column first then divide.

[Goyo]

I just want to sum up the ‘Days’ column and divide that by the sum of the ‘Admits’ column.

You forgot to write the code that sums up the columns. Summing up the columns will give you two numbers and dividing them will be a number too.

dfNavi['Days'].astype(float).sum() / dfNavi['Admits'].astype(float).sum()

You don’t need to cast to float if the columns are already numeric, and they really should. If they aren’t, revisit how you are creating dfNavi.

dfNavi['Days'].sum() / dfNavi['Admits'].sum()

From there I want to be able to filter by State(s) and Incurred_Month(s). It’s sort of like an average, but obviously the math is wrong if I divide admits by days then take that average.

What do you mean by “from there”? Now you have a number, you can’t filter that.

[Brian_P]

Thanks for pointing that out, I forgot to mention I had a group-by earlier in the code that did the sums for each metric. I had to replace 0’s with nan’s and formatted it using

f"{LOS:.1f}"

then used your calc and it worked perfectly. Appreciate the help and the explanations.