Automatically populate cells in data_editor

🎈 Using Streamlit
1

Hello all,

I have a data_editor frame in my program that allows users to enter part numbers to assign them to a machine.

The first column is a dropdown with a list of parts. When a user selects a part in this column, I would like to populate the ‘part description’ and ‘category’ fields automatically.

Here is my current code:

  1. The first part uses session_state to preload a DataFrame with a query from a MySQL database. Right now it is empty, but will have parts in it once they are added.
  2. The second is the on_change callback, ‘callback_steps()’.
    This callback is supposed to check the part number against another query that contains all of the parts, their description, and their category.
  3. The last part is supposed to copy the dataframe from session_state and generate a new data_editor with the updated data, but all that gets filled in is the ‘part_number’ column (which contains the drawdown) and everything else is blank.
if "panel_part_df" not in st.session_state:
    st.session_state["panel_part_df"] = pd.DataFrame(panel_part_query)


def callback_steps():
    if "my_key2" in st.session_state:
        if len(st.session_state["my_key2"]["added_rows"]) > 0:
            df_copy = st.session_state["my_key2"].copy()
            for change_dict in df_copy["added_rows"]:
                # Populate the part_description, manufacturer_name, and category_name fields
                part_desc = all_part_query[all_part_query['manufacturer_num'] == change_dict['manufacturer_num']][
                    'part_description'].item()  # Gets the part_description for this part.
                man_name = all_part_query[all_part_query['manufacturer_num'] == change_dict['manufacturer_num']][
                    'manufacturer_name'].item()
                cat_name = all_part_query[all_part_query['manufacturer_num'] == change_dict['manufacturer_num']][
                    'category_name'].item()


                st.session_state["panel_part_df"].loc[
                    st.session_state["panel_part_df"]['manufacturer_num'] == change_dict['manufacturer_num'], [
                        'part_description']] = part_desc
                st.session_state["panel_part_df"].loc[
                    st.session_state["panel_part_df"]['manufacturer_num'] == change_dict[
                        'manufacturer_num'], 'manufacturer_name'] = man_name
                st.session_state["panel_part_df"].loc[
                    st.session_state["panel_part_df"]['manufacturer_num'] == change_dict[
                        'manufacturer_num'], 'category_name'] = cat_name

            st.write(st.session_state["panel_part_df"])


modified_df = st.session_state["panel_part_df"].copy()

st.data_editor(modified_df,
               key="my_key2",
               num_rows="dynamic",
               column_order=(
                   "manufacturer_num", "quantity", "part_description", "manufacturer_name",
                   "category_name"),
               column_config={
                   "manufacturer_num": st.column_config.SelectboxColumn(
                       "Manufacturer #",
                       width='large',
                       options=all_part_query['manufacturer_num'],
                       required=True
                   )
               },
               use_container_width=True,
               on_change=callback_steps(),
               disabled=("part_description", "manufacturer_name", "category_name"),
               )

A similar post was made about this, but I cannot figure out how they got it to work. I have been working on this for the last 8 hours and I need a second set of eyes to help me out.

Thanks,

Andrew

Also, here is the complete program for reference:

import streamlit as st
import pandas as pd
from sqlalchemy import text, Table, Column, Integer, String, MetaData, ForeignKey, insert, update

metadata_obj = MetaData()

manufacturer_table = Table(
    "manufacturers",
    metadata_obj,
    Column("manufacturer_id", Integer, primary_key=True),
    Column("manufacturer_name", String(256), nullable=False),
)

category_table = Table(
    "categories",
    metadata_obj,
    Column("category_id", Integer, primary_key=True),
    Column("category_name", String(120))
)
parts_table = Table(
    "parts",
    metadata_obj,
    Column("part_id", Integer, primary_key=True),
    Column("manufacturer_num", String(256), nullable=False),
    Column("manufacturer_id", Integer, ForeignKey("manufacturers.manufacturer_id"), nullable=False),
    Column("part_description", String(265)),
    Column("category_id", String(256), ForeignKey("categories.category_id"), nullable=False),
)

st.set_page_config(layout="wide")

if st.button("Clear session_state"):
    keys = list(st.session_state.keys())
    for key in keys:
        st.session_state.pop(key)

st.markdown("Use this page to assign parts to a panel.")

# Initialize connection.
conn = st.connection('mysql', type='sql')

# Perform queries.
model_query = conn.query('SELECT model_num FROM panels;', ttl=0)
all_part_query = conn.query('SELECT parts.manufacturer_num, manufacturers.manufacturer_name, parts.part_description, categories.category_name\
                             FROM parts\
                             JOIN manufacturers ON parts.manufacturer_id=manufacturers.manufacturer_id\
                             JOIN categories ON parts.category_id=categories.category_id;', ttl=0)

category_query = conn.query('SELECT category_id, category_name FROM categories;', ttl=0)
manufacturer_query = conn.query('SELECT manufacturer_id, manufacturer_name FROM manufacturers;', ttl=0)

st.header("Select panel:", anchor=False, divider="orange")
panel_option = st.selectbox('Panel', model_query)
# part_option = st.selectbox('Part', all_part_query)
# quantity = st.text_input('Quantity')

panel_part_query = conn.query("SELECT parts.part_description, parts.manufacturer_num, manufacturers.manufacturer_name, categories.category_name, part_panel.quantity\
                               FROM parts\
                               JOIN manufacturers ON parts.manufacturer_id = manufacturers.manufacturer_id\
                               JOIN categories ON parts.category_id = categories.category_id\
                               JOIN part_panel ON parts.part_id = part_panel.part_id\
                               JOIN panels ON part_panel.panel_id = panels.panel_id\
                               WHERE panels.model_num = '{x}';".format(x=panel_option), ttl=0)

df = pd.DataFrame(
    all_part_query)  # , columns=("part_id", "Part #", "manufacturer", "manufacturer_num", "part_description"))

empty_df = pd.DataFrame(columns=['Manufacturer #', 'Quantity', 'Description'])


if "panel_part_df" not in st.session_state:
    st.session_state["panel_part_df"] = pd.DataFrame(panel_part_query)


def callback_steps():
    if "my_key2" in st.session_state:
        if len(st.session_state["my_key2"]["added_rows"]) > 0:
            df_copy = st.session_state["my_key2"].copy()
            for change_dict in df_copy["added_rows"]:
                # Populate the part_description, manufacturer_name, and category_name fields
                part_desc = all_part_query[all_part_query['manufacturer_num'] == change_dict['manufacturer_num']][
                    'part_description'].item()  # Gets the part_description for this part.
                man_name = all_part_query[all_part_query['manufacturer_num'] == change_dict['manufacturer_num']][
                    'manufacturer_name'].item()
                cat_name = all_part_query[all_part_query['manufacturer_num'] == change_dict['manufacturer_num']][
                    'category_name'].item()


                st.session_state["panel_part_df"].loc[
                    st.session_state["panel_part_df"]['manufacturer_num'] == change_dict['manufacturer_num'], [
                        'part_description']] = part_desc
                st.session_state["panel_part_df"].loc[
                    st.session_state["panel_part_df"]['manufacturer_num'] == change_dict[
                        'manufacturer_num'], 'manufacturer_name'] = man_name
                st.session_state["panel_part_df"].loc[
                    st.session_state["panel_part_df"]['manufacturer_num'] == change_dict[
                        'manufacturer_num'], 'category_name'] = cat_name

            st.write(st.session_state["panel_part_df"])


modified_df = st.session_state["panel_part_df"].copy()

st.data_editor(modified_df,
               key="my_key2",
               num_rows="dynamic",
               column_order=(
                   "manufacturer_num", "quantity", "part_description", "manufacturer_name",
                   "category_name"),
               column_config={
                   "manufacturer_num": st.column_config.SelectboxColumn(
                       "Manufacturer #",
                       width='large',
                       options=all_part_query['manufacturer_num'],
                       required=True
                   )
               },
               use_container_width=True,
               on_change=callback_steps(),
               disabled=("part_description", "manufacturer_name", "category_name"),
               )

# st.write(st.session_state["my_key2"])


if st.button("Save changes"):
    if len(st.session_state["my_key2"]["added_rows"]) > 0:
        df_copy = st.session_state["my_key2"].copy()
        indexList = []  # get old value
        for change_dict in df_copy["added_rows"]:
            if 'quantity' in change_dict:
                st.write(change_dict['manufacturer_num'])
                part_desc = all_part_query[all_part_query['manufacturer_num'] == change_dict['manufacturer_num']][
                    'part_description'].item()  # Gets the part_description for this part.
                cat_name = all_part_query[all_part_query['manufacturer_num'] == change_dict['manufacturer_num']][
                    'category_name'].item()
                man_name = all_part_query[all_part_query['manufacturer_num'] == change_dict['manufacturer_num']][
                    'manufacturer_name'].item()
                # find category_id from categories_table using category_name
                cat_id = category_query[category_query['category_name'] == cat_name]['category_id'].item()
                man_id = manufacturer_query[manufacturer_query['manufacturer_name'] == man_name][
                    'manufacturer_id'].item()
                change_dict['part_description'] = part_desc
                change_dict['manufacturer_id'] = man_id
                change_dict['category_id'] = cat_id
                st.write(change_dict)
                with conn.session as session:
                    session.execute(insert(parts_table).values(change_dict))
                    session.commit()
            else:
                st.write("Please enter a quantity for all new rows")
            # with conn.session as session:
            # session.execute(insert(parts_table).values(change_dict))
            # session.commit()

    if len(st.session_state["my_key2"]["edited_rows"]) > 0:
        df_copy = st.session_state["my_key2"].copy()
        indexList = []  # get old value
        for index, change_dict in df_copy["edited_rows"].items():
            for changed_column, new_value in change_dict.items():
                panel_id = df.iloc[index, 0]
            indexList.append(index)
        # st.write(indexList)
        for i in indexList:
            del st.session_state["my_key2"]["edited_rows"][i]

        st.rerun()

if st.button('Assign'):
    st.write("TODO")
    # panel_id = conn.query('SELECT panel_id from panels WHERE model_num = "{x}";'.format(x = panel_option), ttl=0)
    # panel_id = panel_id.at[0,'panel_id']
    # part_id = conn.query('SELECT part_id from parts WHERE manufacturer_num = "{x}";'.format(x = part_option), ttl=0)
    # part_id = part_id.at[0,'part_id']
    # t = text("INSERT INTO part_panel (panel_id, part_id, quantity) VALUES (:panel_id2, :part_id2, :quantity2);")
    # with conn.session as session:
    # session.execute(t, {"panel_id2": panel_id, "part_id2": part_id, "quantity2": quantity})
    # session.commit()

st.header("Parts assigned to {x}:".format(x=panel_option), anchor=False, divider="orange")
st.button("Refresh")

# st.dataframe(df2, hide_index=True, use_container_width=True, )

st.header("All Parts:", anchor=False, divider="orange")
st.dataframe(df, hide_index=True,
             use_container_width=True, )  # column_order=("part_number", "manufacturer", "manufacturer_num", "part_description"))

# vendor_query = conn.query('SELECT vendor_name FROM vendors;', ttl=10)
# vendor
# vendor_option = st.selectbox('Vendor', vendor_query)
# vendor_num
# vendor_num = st.text_input('Vendor #', '')
1 Like
2

Hello @bears0,

Can you please try this approach:

  1. Define a Function to Update DataFrame Based on Selection
def update_dataframe_with_selection(df, selection, all_parts_df):
    selected_part_info = all_parts_df[all_parts_df['manufacturer_num'] == selection]

    if not selected_part_info.empty:
        part_desc = selected_part_info['part_description'].iloc[0]
        man_name = selected_part_info['manufacturer_name'].iloc[0]
        cat_name = selected_part_info['category_name'].iloc[0]

        df.loc[df['manufacturer_num'] == selection, 'part_description'] = part_desc
        df.loc[df['manufacturer_num'] == selection, 'manufacturer_name'] = man_name
        df.loc[df['manufacturer_num'] == selection, 'category_name'] = cat_name
    return df
  1. Your callback function needs to be structured properly to use the update_dataframe_with_selection function.
def callback_steps():
    if "my_key2" in st.session_state:
        df_copy = st.session_state["panel_part_df"].copy()

        for change in st.session_state["my_key2"].get("added_rows", []):
            selected_manufacturer_num = change.get('manufacturer_num')
            if selected_manufacturer_num:
                df_copy = update_dataframe_with_selection(df_copy, selected_manufacturer_num, all_part_query)

        st.session_state["panel_part_df"] = df_copy
  1. Create the Data Editor with Correct Parameters
modified_df = st.session_state["panel_part_df"].copy()

st.data_editor(modified_df,
               key="my_key2",
               num_rows="dynamic",
               column_order=("manufacturer_num", "quantity", "part_description", "manufacturer_name", "category_name"),
               column_config={
                   "manufacturer_num": st.column_config.SelectboxColumn(
                       "Manufacturer #",
                       width='large',
                       options=all_part_query['manufacturer_num'],
                       required=True
                   )
               },
               use_container_width=True,
               on_change=callback_steps, 
               disabled=("part_description", "manufacturer_name", "category_name"),
               )

Hope this helps :slight_smile: Let me know if you might require any further assistance!

Kind Regards,
Sahir

P.S. Lets connect on LinkedIn!

3 Likes