CODEWITHSUNDEEP
pythonpandasexcelnullexport
Arkasz
Arkasz

Reputation: 3

I want to write out "NULL" values from 1 column into an excel file from a DataFrame

I have multiple excel files. I import the data into 1 DataFrame in Python. Then I sort the information by an Index field, and afterwards I want to export the Dataframe into an excel file. All goes well, but unfortunately in the column "Priority" the cell value is either an integer or the string "NULL", and I want to return these values into the exported file. Unfortunately I receive only the numbers back in the exported file in the "Priority" column. This column can be in different positions in the files. How can I export into an excel file the Dataframe with "NULL" strings as well as the integers in the "Priority" column?

My working solution is: dataframe["Priority"].fillna("NULL", inplace = True)

Unfortunately this will be deprecated in a future release, as: Setting an item of incompatible dtype is deprecated and will raise in a future error of pandas. Value 'NULL' has dtype incompatible with float64, please explicitly cast to a compatible dtype first.

Upvotes: 0

Views: 562

Answers (2)

SagarNikam
SagarNikam

Reputation: 55

Solution Approach 1: If you want to keep NULL fields as they are in the DataFrame when writing to an Excel file using to_excel(), you can use the na_rep parameter. The na_rep parameter allows you to specify the representation of NaN values (NULL in the DataFrame) in the output file. It will work on all the NULL values across dataframe

df = pd.DataFrame(data)
# Specify na_rep to represent NULL values in the Excel file
output_file = 'output.xlsx'
df.to_excel(output_file, na_rep='NULL', index=False)

Solution Approach 2: The replace method is used to replace pd.NA (representing NULL values) with the specified null_repalcement_value ('NULL' in your case) only in that column. The rest of the DataFrame remains unchanged.

# value by which NaN/NULL going to replace
null_repalcement_value = 'NULL' 

# Update a specific column to replace NaN with NULL
column_to_keep_null = 'Priority'
df[column_to_keep_null] = df[column_to_keep_null].replace({pd.NA: 
null_repalcement_value})

# Write the modified DataFrame to an Excel file
output_file = 'output.xlsx'
df.to_excel(output_file, index=False)

Upvotes: 0

Triky
Triky

Reputation: 441

Try changing the dtype of the column to strings and use fillna after that.

df['Priority'] = df['Priority'].astype('object')

Upvotes: 0

Related Questions