Grid component can load data from Excel at page creation time but not after button is clicked

[me21]

I have the following code:

        df = pandas.read_excel(data)  # read Excel containing datetime values
        table = ui.table({})
        table.view.load_pandas_frame(df)

It works and the data is displayed.

But if I comment the last line and run load_pandas_frame in, say, button click handler function, nothing is displayed, and even table.update() doesn't help.
Digging into JustPy sources, it seems that an error is thrown on update saying datetime objects are not JSON serializable. It is silently swallowed by NiceGUI, only "Problem with websocket update, ignoring" message is printed.
But why does it work at the page creation time?..

[me21]

Update: it actually does work with smaller (10 Kb) xls file containing only two lines inside, but doesn't work with 20 Kb xls file containing ~460 lines and Unicode characters.
The websocket throws an error in the browser:

Uncaught SyntaxError: JSON.parse: unexpected character at line 1 column 1175 of the JSON data
    <anonymous> http://127.0.0.1:8080/:2350
    EventListener.handleEvent* http://127.0.0.1:8080/:2349

But the JSON is created by json.dumps inside JustPy... Continuing to investigate. Still a question stands why does it work at the page creation time.

[me21]

There is NaN at the offending location in the resulting JSON...

[me21]

There is the following code in load_pandas_frame function in JustPy's gridcomponents.py file:

        # Change NaN and similar to None for JSON compatibility
        self.options.rowData = df.replace([np.inf, -np.inf], [sys.float_info.max, -sys.float_info.max]).where(pd.notnull(df), None).to_dict('records')

but for some reason it fails to change all nan values to None... some are left intact.

[me21]

It's because two columns in the pandas DataFrame have float64 dtype, others have object.

[me21]

Found a workaround:

        df = pandas.read_excel(form_data).replace({np.nan: None})  # This will replace nans with None even in float64 columns
        # go through each cell and convert datetimes to str using isoformat
        # otherwise object of type datetime is not json serializable
        df = df.applymap(lambda v: v.isoformat() if isinstance(v, datetime) else v)

Still unclear why does it work at page creation time without jumping through all these hoops.
It is not an issue in NiceGUI itself, though. Please feel free to investigate further or close the issue.

[bapowell]

As a side note, fairly recently JustPy added the row_data_converter parameter to AgGrid. That was meant to help with these kinds of problems. Issue#500.

[falkoschindler]

Thanks for reporting and investigating this issue. I'll close it for now since ui.table will change quite a bit in version 1.0 anyway. We will keep it in mind for the new implementation.