DataEditor, JSON and set_index()

Streamlit and Snowflake
, ,
1

Hi all,
I have a problem with getting JSON with changes from data editor. I have set the ID column to be the index in dataframe, but this seems to be ignored from json dump from session state as it uses default index.
Easeier to explain with an example –
Table is as follows:

CREATE OR REPLACE TABLE TESTTBL (ID NUMBER(38,0) NOT NULL unique AUTOINCREMENT start 1 increment 1, COL1 VARCHAR(500) NOT NULL, COL2 NUMBER(38,0), constraint TEST_PK primary key (ID));
INSERT INTO TESTTBL (COL1, COL2) VALUES('aaa', 123);
INSERT INTO TESTTBL (COL1, COL2) VALUES('bbb', 456);

Here is the Streamlit code:

import streamlit as st
import pandas as pd
import json
from snowflake.snowpark.context import get_active_session
st.title("Dude wheres my id :eyes:")

session = get_active_session()

df = session.sql("SELECT * FROM TESTTBL").collect()
df = pd.DataFrame(df)
df.set_index('ID', inplace=True)
st.data_editor(df, use_container_width=True, key="first", num_rows="dynamic")

if st.button('Show JSON'):
    st.write(json.dumps(st.session_state["first"]))

After running this I edited one row and added one additional row, so table looks like this:

1 aaa 123
2 bbb test 456
7 ccc 789

Resulting JSON will be {“edited_rows”: {“1”: {“COL1”: “bbb test”}}, “added_rows”: [{“_index”: 7, “COL1”: “ccc”, “COL2”: 789}], “deleted_rows”: [ ]}
where added row has a correct value for index, but edited row has 1 as index which is not correct, as it should be 2.

Does anyone have an idea what is going on with this and how to get correct index values in JSON dumps?

2

The "1" in the edited rows corresponds to the row of the DataEditor not to the index of anything else.
To get the actual data you need save it to a variable which will be a dataframe.

Maybe this explains better what is going on:

import pandas as pd
import streamlit as st

df = pd.DataFrame(
    [
        {"ID": 1, "colone": "aaa", "coltwo": 123},
        {"ID": 7, "colone": "bbb", "coltwo": 456},
    ]
)

st.title("Dude wheres my id :eyes:")
df.set_index('ID')
edited_df = st.data_editor(df, key="da_editor", use_container_width=True, num_rows="dynamic")

"This is the data (You can save it to session state or convert to json if needed)"
st.write(pd.DataFrame.to_dict(edited_df))

"This is the editor state (Same here)"
st.write(st.session_state["da_editor"])
3

Ok, I get what is going on and thanks for the answer, but that makes entire JSON dump pretty useless for what I wanted to do. Basically, I wanted to pass the difference for further processing and updating the DB instead of saving entire dataset back every time.