Need help with Streamlit Dropdown Behavior

[adamasbaihalifa]

Hi Streamlit community,

I’m currently working on a Streamlit application where I’m encountering an issue with dropdown behavior that I’m struggling to resolve. Here’s a brief overview of the problem:

Issue: When selecting a circulariteitscode value, the dropdown should only display the corresponding materials for that specific code in that row. However, it currently displays materials from all circulariteitscode values that are available in the dataframe.

Background: In my MySQL database, I have three circulariteitscode values: 603, 409, and 407. Each circulariteitscode should have its own set of materials. For example, circulariteitscode 603 should only display three materials, but it’s displaying all nine materials from 603, 409, and 407 combined.

Approach: I’ve attempted to fetch data from the database for each circulariteitscode value and update the DataFrame accordingly. However, despite my efforts, the dropdown still shows all materials.

Code: Here’s a simplified version of my code:

import streamlit as st
from modules.db_con import db
import pandas as pd

st.title("🧪 Test Dropdown")

# Define DataFrame with dropdown options (simplified)
df_dropdown = pd.DataFrame([
    {"circulariteitscode": 603, "materiaal": "beton", "productnaam": ""},
    {"circulariteitscode": 409, "materiaal": "hout", "productnaam": ""},
    {"circulariteitscode": 407, "materiaal": "staal", "productnaam": ""}
])

def fetch_data_from_mysql(abc_value):
    """
    Fetch data from MySQL database based on the given 'abc' value.
    Args:
        abc_value (int): The 'abc' value to fetch data for.
    Returns:
        list: A list of 'productnaam' values fetched from the database.
    """
    cursor = db.cursor()
    cursor.execute("SELECT productnaam FROM materiaal WHERE circulariteitscode = %s", (int(abc_value),))
    results = cursor.fetchall()
    cursor.close()
    return [result[0] for result in results]

# Iterate through unique 'abc' values in the DataFrame
unique_abc_values = df_dropdown["circulariteitscode"].unique()

# Fetch data from MySQL for each 'abc' value
all_custom_cat = []
for abc_value in unique_abc_values:
    custom_cat = fetch_data_from_mysql(abc_value)
    all_custom_cat.extend(custom_cat)

# Remove duplicates and None values using set and list comprehension
unique_custom_cat = list(set(x for x in all_custom_cat if x is not None))

# Update the DataFrame with the fetched data
df_dropdown["productnaam"] = (
    df_dropdown["materiaal"]
    .astype("category")
    .cat.remove_categories(df_dropdown["materiaal"])
    .cat.add_categories(unique_custom_cat)
)

# If the session state does not have 'edited_df_dropdown', add it with the initial DataFrame
if "edited_df_dropdown" not in st.session_state:
    st.session_state.edited_df_dropdown = df_dropdown

# Display the DataFrame with the data editor
edited_df_dropdown = st.data_editor(st.session_state.edited_df_dropdown, hide_index=True)

# Update the session state with the edited DataFrame
st.session_state.edited_df_dropdown = edited_df_dropdown

Request for Help: I’m seeking assistance to resolve this issue. How can I ensure that the dropdown only displays materials corresponding to the selected circulariteitscode in that row?

Any insights, suggestions, or guidance would be greatly appreciated. Thank you in advance for your help!

Regards,

Adam

[ferdy]

Could you post a minimal code that can reproduce the issue?

[adamasbaihalifa]

Hi ferdy,

Thank you for your response. Sure:

import streamlit as st
import pandas as pd

# Mocking DataFrame with dropdown options (simplified)
df_dropdown = pd.DataFrame([
    {"circulariteitscode": 603, "materiaal": "beton", "productnaam": ""},
    {"circulariteitscode": 409, "materiaal": "hout", "productnaam": ""},
    {"circulariteitscode": 407, "materiaal": "staal", "productnaam": ""}
])

def fetch_data_from_mysql(abc_value):
    """
    Mocking fetch data from MySQL database based on the given 'abc' value.
    """
    # Pretend to fetch data from MySQL
    if abc_value == 603:
        return ["product1", "product2", "product3"]
    elif abc_value == 409:
        return ["product4", "product5", "product6"]
    elif abc_value == 407:
        return ["product7", "product8", "product9"]
    else:
        return []

# Define a simple Streamlit app
st.title("Dropdown Issue Reproduction")

selected_code = st.selectbox("Select circulariteitscode", df_dropdown["circulariteitscode"].unique())

# Fetch data from MySQL based on selected_code
materials_from_mysql = fetch_data_from_mysql(selected_code)

# Update DataFrame with fetched data
df_dropdown.loc[df_dropdown["circulariteitscode"] == selected_code, "productnaam"] = ", ".join(materials_from_mysql)

filtered_materials = df_dropdown[df_dropdown["circulariteitscode"] == selected_code]["productnaam"]

st.write("Materials for selected circulariteitscode:", filtered_materials.tolist())

st.write("Updated DataFrame:")
st.dataframe(df_dropdown)  # Display the updated DataFrame with dropdown

I hope this clears things up.

[ferdy]

This is the output if 603 is selected…

So what do you want?

What should be the correct output if 603 is selected?

[adamasbaihalifa]

Exactly! However, instead of using a st.selectbox, the options should be displayed within the DataFrame’s dropdown menu for better clarity and user interaction

This code snippet reproduces the identical results to the first code by simulating the outcomes of executing the SQL query.

But the problem that I am experiencing is the following: The dropdown in each row should only display materials corresponding to the selected circulariteitscode for that specific row. However, it currently displays materials from all circulariteitscode values in the dataframe.

import streamlit as st
import pandas as pd

df = pd.DataFrame(
    [
    {"circulariteitscode": 603, "materiaal": "beton", "productnaam": ""},
    {"circulariteitscode": 409, "materiaal": "hout", "productnaam": ""},
    {"circulariteitscode": 407, "materiaal": "staal", "productnaam": ""}
    ]
)

custom_cat = ['Metisse isolatie', 'Luchtkanalen', ' Luchtkanalen Greenweave/Multiweave Fabric Duct ', 'Ecococon gevelpaneel', 'Hennep Bouwblokken', 'test', 'Fundering met 20% puingranulaat', 'Beton; gewapend [massief dragende binnenwanden]', 'wdwdwd' ]

# Comment for better understanding:
# In my MySQL table (materiaal), I have the following data with these columns (productnaam & circulariteitscode):
# Metisse isolatie,     407
# Luchtkanalen,         407
# Luchtkanalen Greenweave/Multiweave Fabric Duct,     407
# Ecococon gevelpaneel,     409
# Hennep Bouwblokken,        409
# test,           409
# Fundering met 20% puingranulaat,    603
# Beton; gewapend [massief dragende binnenwanden],       603
# wdwdwd,        603


df["productnaam"] = (
    df["circulariteitscode"].astype("category").cat.remove_categories(df['circulariteitscode']).cat.add_categories(custom_cat)
)

edited_df = st.data_editor(df, hide_index=True)

[ferdy]

Did you mean this.

And if the row is 407, the options are cat_407 = ['Metisse isolatie', 'Luchtkanalen', 'Luchtkanalen Greenweave/Multiweave Fabric Duct']?

[adamasbaihalifa]

Yes! That’s exactly what I need. But this has to be dynamically linked to the sql query. The problem that I have now is that the drop down shows all the values instead of in the case of circulariteitscode 603 (wdwdwd, Beton; gewapend [massief dragende binnenwanden], Fundering met 20% puingranulaat).

[ferdy]

Unfortunately, this is not possible. The column only supported a fix list or iterable of str.

st.column_config.SelectboxColumn - Streamlit Docs

The column selectbox option.

[ferdy]

The workaround is to list everything but put a prefix with value from the code, so that the user can clearly tag the appropriate product.

[adamasbaihalifa]

Ah what a pity. Nevertheless I appreciate the help and attention. Enjoy your day Ferdy.