I have a detailed code here. I want to retrieve the original rows from the DataFrame before pivoting when I click on a value in the pivot table, but it’s not easy.
It seems like cell selection is not working with the code below, so an error occurs when calling it.
I sent the code via mobile since I’m outside right now, so it might look a bit messy. Sorry about that. I’ll fix it when I get home.
import streamlit as st
import pandas as pd
from st_aggrid import AgGrid, GridOptionsBuilder, GridUpdateMode, DataReturnMode
Create dataset
data = {
‘A’: [‘foo’, ‘foo’, ‘foo’, ‘bar’, ‘bar’, ‘bar’, ‘baz’, ‘baz’, ‘baz’, ‘qux’, ‘qux’, ‘qux’],
‘B’: [‘one’, ‘two’, ‘three’, ‘one’, ‘two’, ‘three’, ‘one’, ‘two’, ‘three’, ‘one’, ‘two’, ‘three’],
‘C’: [‘small’, ‘large’, ‘small’, ‘large’, ‘small’, ‘large’, ‘small’, ‘large’, ‘small’, ‘large’, ‘small’, ‘large’],
‘D’: [‘red’, ‘blue’, ‘green’, ‘red’, ‘blue’, ‘green’, ‘red’, ‘blue’, ‘green’, ‘red’, ‘blue’, ‘green’],
‘E’: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],
‘F’: [13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24],
‘G’: [25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36],
‘H’: [37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48],
‘I’: [49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60],
‘J’: [61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72]
}
df = pd.DataFrame(data)
Create pivot table
pivot_table = pd.pivot_table(df, values=‘E’, index=[‘A’, ‘B’], columns=[‘C’, ‘D’], aggfunc=‘sum’)
pivot_table = pivot_table.reset_index() # Reset index of pivot table to convert it back to a regular DataFrame
Create Streamlit app
st.title(‘Streamlit with AgGrid’)
Convert column names of pivot table to strings for use
pivot_table.columns = [f"{col[0]}_{col[1]}" if isinstance(col, tuple) else col for col in pivot_table.columns]
Set grid options using GridOptionsBuilder
gb = GridOptionsBuilder.from_dataframe(pivot_table)
gb.configure_default_column(editable=True)
gb.configure_selection(‘single’) # Set single selection mode
gridOptions = gb.build()
Render AgGrid component
response = AgGrid(
pivot_table,
gridOptions=gridOptions,
update_mode=GridUpdateMode.SELECTION_CHANGED,
data_return_mode=DataReturnMode.AS_INPUT,
allow_unsafe_jscode=True
)
Get selected row data
selected = response.get(‘selected_rows’, )
Output for debugging
st.write(“Selected Rows:”, selected)
st.write(“Pivot Table Columns:”, pivot_table.columns)
if selected and len(selected) > 0:
selected_values = selected[0]
st.write(“Selected row data:”, selected_values)
if isinstance(selected_values, dict):
a_value = selected_values['A']
b_value = selected_values['B']
# Extract C and D values from the column name of the selected cell
c_value = None
d_value = None
for col in selected_values:
if col not in ['A', 'B'] and selected_values[col] is not None:
c_value, d_value = col.split('_')
break
if c_value and d_value:
st.write(f"Filtering with A: {a_value}, B: {b_value}, C: {c_value}, D: {d_value}")
filtered_df = df[
(df['A'] == a_value) &
(df['B'] == b_value) &
(df['C'] == c_value) &
(df['D'] == d_value)
]
st.write("Filtered DataFrame:")
st.write(filtered_df)
else:
st.write("No valid cell selected.")
else:
st.write("Selected data is not a dictionary.")
else:
st.write(“No row selected or selected data is None”)
