Parsing character as NaN when reading Excel doesn't work as expected

NonSleeper 0
Viewed 33

My Excel sheet looks like this:

   A   B  
1 first second
2  1   -
3  2   -
4  -   5  
5  4   6

I try to parse - as NaN when reading it but this doesn't work:

df = pd.read_excel(excel_file_path, header = 0, na_values = '-')

It instead turns - to 0. How can I fix it?

pandas python
1 Answers

Here is another way to do it with the following Excel file:

enter image description here

df = pd.read_excel("file.xlsx", header=0)

df = (
    df
    .applymap(lambda x: str(x).encode("ascii", "ignore").decode("ascii"))
    .replace("", pd.NA)
)

print(df)
# Output
  first second
0     1   <NA>
1     2   <NA>
2  <NA>      5
3     4      6
userCDbL0
Related