Read Excel file to pandas dataframe + create a dropdown from those values


Sat Jan 14 2023 00:42:50 GMT+0000 (Coordinated Universal Time)

Saved by @tofufu #python #streamlit

import streamlit as st
import pandas as pd

def excel_init():
    # Use database like in Bending Stiffness model
    orig_database_path = "/path/to/file.xlsx"      # local copy of the Excel database used in Bending Stiffness & Permeability model
    read_orig_database = r"" + orig_database_path
    sheet_name = "Database"
    # Read in the Resin/Materials from Carol's Excel file for getting data by column
    ses["read_table"] = pd.read_excel(read_orig_database, sheet_name=sheet_name, engine='openpyxl', skiprows=3)   # skip first 3 rows since they are either empty space or extra headers

    # Excel column header names from original materials database for easily reading the database column values
    ses["col1"] = ses["read_table"].columns[0]      # "Column 1"
    ses["col3"] = ses["read_table"].columns[2]      # "Column 3"
    ses["col6"] = ses["read_table"].columns[5]      # "Column 6"
    # Setting up list of materials for dropdown
    col1_pandas = pd.DataFrame(ses["read_table"][ses["col1"]].values)  # get col1 column values as pandas dataframe
    customize_options = pd.DataFrame(["[Placeholder]"])                                              # "blank" placeholder for any item not in the database
    ses["list_streamlit"] = pd.concat([customize_options, col1_pandas], axis=0)                       # axis=0 --> concat by row, axis=1 --> concat by col

    # Assign "Excel indices" for easy data retrieval (same applies to col3, col6, etc.) after user selects a material (need to start range at 1 since Excel is 1-based index, not 0-based)
    # we access the item by
    ses["col1_prefill_index"] = {}                                                         # [material_name_key]: [index_0-based_val]
    for i in range(1, len(ses["read_table"][ses["col1"]]) + 1):      # loop through col1 pandas dataframe "list"
        ses["col1_prefill_index"][ses["read_table"][ses["col1"]][i-1]] = i-1

There is a helper function that reads the Excel database. We get the item and their item values if it exists and build pandas objects for a dropdown menu or to easily retrieve the data. We skip reading the first 3 rows of the Excel file since they are either empty or header rows. Indexing the item list helps get the item value associated with the selected item. We get the value by: 1. Accessing the "Excel database" (it really is a pandas dataframe) 2. Going to the "Column 3" (or "Column 6") column 3. Going to the selected material’s row via material index a. We achieve this by accessing the user-selected material For example, if the user selects an item, we would be able to obtain the material’s density by accessing the Excel database like this: `item_table[col1][[item_indez][form_layer[i]]]`, for the index `i`.