CODEWITHSUNDEEP
pythonpandasopenpyxl
Normanie
Normanie

Reputation: 21

Pandas df showing up as 'NaN' once I write data to an excel

To give a quick background, I am downloading a table online and writing it into 'tab2' of an excel sheet. 'Tab1' then acts as a summary tab which compares the current table to the previous version I last downloaded.

Now, to tell the difference between the two tables, I've set up an IF function which shows "True" if there's no movement, and "False" if there's a movement.

I've then used Pandas to filter the dataframe for any instances in the "Overall" column which are "True".

However, this only works if I use it in isolation without the automated file download. As soon as I run the whole script including the download + write, it shows the "True/False" column as "NaN" - Which means it then filters out all the lines.

The filter code is as follows:

# Open File
file_read = pd.read_excel(file, sheet_name='Summary')
print(file_read)
    
# Filter by 'Overall' = TRUE
Movement = file_read[(file_read['Overall'] == True )]
print(Movement)
    
Movement.to_excel('Output.xlsx')

The Download + Writer code is as follows:

book = load_workbook('file.xlsx')
writer = pd.ExcelWriter('file.xlsx', engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, 'TAB NAME', index=False)
writer.save()

Upvotes: 0

Views: 1319

Answers (1)

ValentinB
ValentinB

Reputation: 79

try to use the following line in your second to last line:

df.to_excel(writer, sheet_name='TAB NAME', index=False, na_rep='')

na_rep will define what will be displayed in emtpy cells.

Take care, friend

Upvotes: 2

Related Questions