topic Re: Python Notebook: writing in existing table error due to datatype Null in Data Engineering

Python Notebook: writing in existing table error due to datatype Null

Testaaaaaa — Thu, 06 Feb 2025 01:12:37 GMT

Hi All,

I have a existing table which was created (a few days ago) after pulling the last 30 days of activityEvents from PowerBI REST API.

I am facing issues when incrementally loading new data du to the following error:

Python Notebook: writing in existing table error due to datatype Null

I have the following code which I though would take care of this issue:

df = pd.DataFrame(all_items) # Remove all columns with Null type df = df.dropna(axis=1, how='all') if not df.empty: df['Insert_Datetime'] = today[0:-6] table_path = f"{lakehouse_details_abfsPath}/Tables/{lakehouse_table}" write_deltalake(table_path, df, mode='append', schema_mode='merge', engine='rust', storage_options=storage_options) print(f" Number of records added in {lakehouse_table}: {len(df)}")

When query the data, I can't find columns with only NaN value.

I've tried to change NaN to None or other values to see if Nan was the issue but it doesn't seem so, especially after dropping columns being dropped if Nan value only.

Any tips on how I should go about?

Re: Python Notebook: writing in existing table error due to datatype Null

v-prasare — Thu, 06 Feb 2025 17:46:37 GMT

Hi @Testaaaaaa,

Thanks for reaching MS Fabric community support.

please refer to below code to ensure that Nan values are handled properly:

df = pd.DataFrame(all_items) # Replace NaN with None for all columns df = df.fillna(None) # Replace NaN in any specific column with a placeholder value (for example, 0 for integers or a default date for datetime columns) df['some_column'] = df['some_column'].fillna(0).astype(int) df['Insert_Datetime'] = pd.to_datetime(today[0:-6], errors='coerce').fillna(pd.Timestamp('2025-01-01')) # Drop columns that are completely NaN df = df.dropna(axis=1, how='all') # Check the data before writing print(df.info()) # Check for any remaining NaN or None values if not df.empty: table_path = f"{lakehouse_details_abfsPath}/Tables/{lakehouse_table}" write_deltalake(table_path, df, mode='append', schema_mode='merge', engine='rust', storage_options=storage_options) print(f"Number of records added in {lakehouse_table}: {len(df)}")

Thanks,

Prashanth Are

MS fabric community support

Re: Python Notebook: writing in existing table error due to datatype Null

v-prasare — Mon, 10 Feb 2025 12:17:59 GMT

@Testaaaaaa, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

Re: Python Notebook: writing in existing table error due to datatype Null

Testaaaaaa — Tue, 11 Feb 2025 09:14:26 GMT

Hello,

Thank you for offering your help however this hasn't fixed the issue.

I ended up concatenating the data from the existing table with the new data as a workaround.

Re: Python Notebook: writing in existing table error due to datatype Null

v-prasare — Thu, 13 Feb 2025 11:30:07 GMT

@Testaaaaaa, Thanks for confirmation.

As you resolved this with workaround, Can you please mark your post as answer so that other users who are facing similar challenges will able to resolve.

Thanks,

Prashanth Are

MS Fabric community support

Re: Python Notebook: writing in existing table error due to datatype Null

v-prasare — Mon, 17 Feb 2025 08:44:28 GMT

@Testaaaaaa, As we haven’t heard back from you, we wanted to kindly follow up to check if your still facing same issue or let me know if you need additional help here.

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

Re: Python Notebook: writing in existing table error due to datatype Null

v-prasare — Wed, 19 Feb 2025 09:10:22 GMT

@Testaaaaaa, As we haven’t heard back from you, we wanted to kindly follow up to check if your still facing same issue or let me know if you need additional help here.

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query