Pandas Excel Write Issue with Boolean Values: 'True' Becomes 'TRUE'

Question

I have a DataFrame where one column contains the string values "True" and "False". However, when I export the DataFrame to Excel using pd.to_excel and open the file in Excel, these values appear as Excel logical values (TRUE/FALSE in uppercase).

As a result, comparing the value "True" from pandas with the Excel "TRUE" returns False

This issue doesn't happen when exporting to CSV; everything remains as plain text.

import pandas as pd

d = {'col1': [1, 2], 'col2': [3, 4], 'col3': ['True','False']}
df = pd.DataFrame(data=d)

writer = pd.ExcelWriter(r'H:\ExcelTest2.xlsx', engine = 'xlsxwriter')
df.to_excel(writer, index=False, sheet_name='Sheet1')
writer.save()

To reproduce:

Run the code to generate the Excel file.
Open the file with Excel
In column 4 (a new column), manually type True
Compare column 3 with 4
Notice the answer is always False

My question:

How can I export data from pandas to Excel without pandas converting "True"/"False" strings into Excel boolean values (TRUE/FALSE)? I want the values to remain text exactly as they are in the DataFrame.

Try to share an example with code.

Anton vBR
– Anton vBR

2018-11-27 13:56:36 +00:00
Commented Nov 27, 2018 at 13:56 — Anton vBR
– Anton vBR, Commented Nov 27, 2018 at 13:56

Mohsen Nosratinia · Accepted Answer · 2023-02-15 22:36:24Z

1

You can convert your strings before writing to Excel like in following example:

import pandas as pd
import numpy as np

df = pd.DataFrame({'A': ['True', 'False', 'True']})
df['A'].dtype
#dtype('O')

df['A'] = df['A'] == 'True'
df['A'].dtype
#dtype('bool')

edited Feb 15, 2023 at 22:36

Mohsen Nosratinia

9,8641 gold badge29 silver badges52 bronze badges

answered Nov 27, 2018 at 14:02

zipa

28k6 gold badges45 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Drac0 Over a year ago

Please execute my code. open xlsx file and in column 4 write regular "True" then compare Column3 (from pandas) with True you just wrote. it will be False

Drac0 Over a year ago

Well, your answer gave me some idea what could go wrong in mode code. In some point my data was str so i had to use .astype(bool) before export to excel which makes "True" equal to excel "True"

zipa Over a year ago

@Drac0 I've miss-typed the True it should be 'True'. Edited.

Collectives™ on Stack Overflow

Pandas Excel Write Issue with Boolean Values: 'True' Becomes 'TRUE'

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related