0

Hi just started learning Python or Programming itself and trying to read DataFrame from Excel and incert it in email. There is a blank in string column where I want to replace Nan with just blank. Elsewhere I have another int-column I want to format as {:1f} but it also includes NaN sometimes because I use =IFERROR(...,""), that's when formating returns error. How can I solve this other than fixing formula in excel as =IFERROR(...,0)?

Kantumrobot
  • 97
  • 7
  • Welcome to StackOverflow! We need sample input data, expected output of the dataframe, and any errors you are coming across (paste the error message in your question). Kindly read this: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples , which help you understand how to improve your question and increase the chances that you will get a helpful answer. – David Erickson Oct 17 '20 at 06:02

1 Answers1

0

Use converters in the pd.read_excel function when you are reading the sheet.

pd.read_excel('sheet.xlsx', sheet_name='bad', 
              converters={'string_col': lambda x: '' if pd.isna(x) else x,
                          'int_col': lambda x: x if pd.isna(x) else round(x,1)
              }
)
Kay
  • 2,057
  • 3
  • 20
  • 29
  • Hi thank you for your advice. I tried this but no changes. I assume if you try to convert NaN data to ' ' at read option it just turns to NaN again. I think I need styler format that makes object as it is but with na_rep otion. I'm grateful anyway I come to know about converter. – Kantumrobot Oct 17 '20 at 13:25
  • Very strange. I made sure the dtype of 'string_coI' is Object and I also tried converters={'string_col': lambda x: 'N' if pd.isna(x)==False else x Then all turned to 'N'. If I make it ==True then NaN doesn't change to 'N'. So NaN is not detected by isna? But if you make ,keep_default_na=False It sure replaces NaN in 'string...' with blank. Anyway I come through this with set_na_rep('') after done all other styling. – Kantumrobot Oct 17 '20 at 13:53