Summary
Hi everyone,
my original intent was to load a dataframe into an st.data_editor where the user could overwrite the original value. The dataframe comes from an Excel file and the input from the user would then overwrite / write back the new values that the user has put in to the dataframe via the st.editor. The dataframe is associated with a forecasting process, so the values in the excel file will change at least monthly.
The first attempt, which successfully wrote back to the Excel is posted below, named “code 1”. Here, all the data was in one tab only. The problem was that once I wanted to make it filterable, it only kept the row that was filtered on and removed all other lines. which makes sense in a way, I guess it simply overwrites the entire file with what is visible in the data_editor.
Since we have quite a lot of customers, making the dataframe / st.data_editor filterable is a important.
Then I used another code snippet, here below as “code 2”. What I had done was to put each forecast for each customer in a separate tab. And depending on the input in the selection box, the code then shows in the editable dataframe only the tab that was requested. So, if the customer 1 was selected in the selection box, the code grabs the data from the “Customer 1” tab in the Excel file and displays it in the st.data_editor. The only issue is that the code doesn’t seem to want to write back the values input by the user into the file anymore. Nothing happens when the save button is pressed. So now the filter action works but not the write back functionality.
I tried to attach the file with some mock up customers but uploading Excel wasn’t allowed. But it was a very basic file where I had 4 tabs. Tab 1 was called “Customer 1”, Tab 2 was called “Customer 2” and so on.
In each tab, which started in cell A1, the first row was the header row, that read left to right: “Customer”, “Jan”, “Feb”…and all the rest of the month names (so 13 columns)
in the second row were the values. In the first column of row 2 was the customer name (ie “Customer 1” in the Customer 1 tab, “Customer 2”, in the Customer 2 tab etc). And in the columns B2 to B13 were the values, I made up some random ones using the randbetween formula in Excel. All tabs had the same structure but slightly different values.
I am happy to send over the file since it cannot be attached.
In the end, my intent is the following: an editable dataframe where the user can filter to his customer and when he inputs new values into the forecast, those write back to the datasource (the Excel file)
Steps to reproduce
Code 1
import streamlit as st
import pandas as pd
st.set_page_config(layout=‘wide’)
st.title(‘Volumes Forecast’)
Input_Vol = pd.read_excel(r’C:\Users\cenigk\OneDrive - JNJ\Desktop\Forecasting Nordics\Input Files\Volumes.xlsx’,‘Volumes Input’)
edited_data = st.experimental_data_editor(Input_Vol)
if edited_data is not None:
data = edited_data
data.to_excel(r’C:\Users\cenigk\OneDrive - JNJ\Desktop\Forecasting Nordics\Input Files\Volumes.xlsx’,‘Volumes Input’, index=False)
Code 2:
import pandas as pd
import streamlit as st
import warnings
warnings.filterwarnings(“ignore”)
Customer1 = pd.read_excel(r’C:\Users\cenigk\OneDrive - JNJ\Desktop\New folder\Documents\Volumes.xlsx’, sheet_name = ‘Customer 1’)
Customer2 = pd.read_excel(r’C:\Users\cenigk\OneDrive - JNJ\Desktop\New folder\Documents\Volumes.xlsx’, sheet_name = ‘Customer 2’)
Customer3 = pd.read_excel(r’C:\Users\cenigk\OneDrive - JNJ\Desktop\New folder\Documents\Volumes.xlsx’, sheet_name = ‘Customer 3’)
Customer4 = pd.read_excel(r’C:\Users\cenigk\OneDrive - JNJ\Desktop\New folder\Documents\Volumes.xlsx’, sheet_name = ‘Customer 4’)
#Customer5 = pd.read_excel(r’C:\Users\cenigk\OneDrive - JNJ\Desktop\New folder\Documents\Volumes.xlsx’, sheet_name = ‘Customer 5’)
Volumes = pd.concat([Customer1,Customer2,Customer3,Customer4], ignore_index=True)
Customer_selection = st.sidebar.selectbox(‘Select Customer’, options= Volumes[‘Customer’].unique())
def load_data():
data = Volumes
return data
if Customer_selection == ‘Customer 1’:
def save_data_cust_1(data):
with pd.ExcelWriter(r’C:\Users\cenigk\OneDrive - JNJ\Desktop\New folder\Documents\Volumes.xlsx’, engine=‘openpyxl’, mode=‘a’, if_sheet_exists=‘replace’) as writer:
Customer1.to_excel(writer, sheet_name=‘Customer 1’, index=False)
if Customer_selection == ‘Customer 2’:
def save_data_cust_2(data):
with pd.ExcelWriter(r’C:\Users\cenigk\OneDrive - JNJ\Desktop\New folder\Documents\Volumes.xlsx’, engine=‘openpyxl’, mode=‘a’, if_sheet_exists=‘replace’) as writer:
Customer2.to_excel(writer, sheet_name=‘Customer 2’, index=False)
if Customer_selection == ‘Customer 3’:
def save_data_cust_3(data):
with pd.ExcelWriter(r’C:\Users\cenigk\OneDrive - JNJ\Desktop\New folder\Documents\Volumes.xlsx’, engine=‘openpyxl’, mode=‘a’, if_sheet_exists=‘replace’) as writer:
Customer2.to_excel(writer, sheet_name=‘Customer 3’, index=False)
if Customer_selection == ‘Customer 4’:
def save_data_cust_4(data):
with pd.ExcelWriter(r’C:\Users\cenigk\OneDrive - JNJ\Desktop\New folder\Documents\Volumes.xlsx’, engine=‘openpyxl’, mode=‘a’, if_sheet_exists=‘replace’) as writer:
Customer2.to_excel(writer, sheet_name=‘Customer 4’, index=False)
def main():
st.title(‘Volumes Forecast’)
data = load_data()
#st.write(data)
df_filtered = data.query("Customer == @Customer_selection")
st.write('Edit Data:')
editable_data = st.data_editor(df_filtered)
if Customer_selection == 'Customer 1':
if st.button('Save Data'):
save_data_cust_1(editable_data)
if Customer_selection == 'Customer 2':
if st.button('Save Data'):
save_data_cust_2(editable_data)
if Customer_selection == 'Customer 3':
if st.button('Save Data'):
save_data_cust_3(editable_data)
if Customer_selection == 'Customer 4':
if st.button('Save Data'):
save_data_cust_4(editable_data)
if name == ‘main’:
main()
add code here
Expected behavior:
Explain what you expect to happen when you run the code above.
Actual behavior:
In the end, my intent is the following: an editable dataframe where the user can filter to his customer and when he inputs new values into the forecast, those write back to the datasource (the Excel file). I have put one tab per customer because I fear that having all in one tab overwrites everyhting else but what was filtered. But if there is a solution with only one tab, that would be great.
Debug info
Requirements file
Links
Additional information
If needed, add any other context about the problem here.