pandas read xlsx adding .0 to text and fillna not working

by

newbie here,

Pandas is adding .0 to the end of my text and fillna is not working

Here is my code

df_GetInfo = pandas.read_excel(report, 
                       sheet_name="Report1",
                       engine="openpyxl")

# Set column types
df_DeliveriesInfo = df_DeliveriesInfo.astype({'Delivery Nr': str, 
                                             'Receiver Nr': str, 
                                             'Transport Nr': str, 
                                             'Forwarder Code': str})

# Replace na values by empty strings
df_DeliveriesInfo.fillna('', inplace=True)`

The issue is in the Transport Nr, he reads it as “989009.0” for example and adds the “.0” in the end
Also adds “nan” for empty values and the fill.na is not working

I tried replacing the str type by int but then i have an error saying the nan values cannot be read as int

I would like to have my transport number as int and then string as well as empty values instead of nan

Thanks a lot

  • 1

    You’re probably missing values so that column so is a float column not an int Change the type to Int64 – df['col'] = df['col'].astype('Int64') Please read Integer dtypes and missing data

    – 




  • 1

    Note, do not convert it to a string first or np.nan will be converted to the string 'nan' and fillna will not work.

    – 

  • @It_is_Chris I think this is the issue you’re right. so how can i work around this? the excel i am trying to read from has empty values and int values in Transport Nr Column

    – 

  • What are you ultimately trying to do? Are you trying to print the dataframe with missing values represented as the null string? Then you should ideally be using styling with na_rep=''. Because, if you’re going to be working with this int column, then missing values should be nan; if you replace them with the null string, they’re not missing anymore and the column isn’t int anymore. I agree with what Chris said: you should learn more about how Pandas handles missing values.

    – 




  • @wjandrea handling missing values is only relevant so i don’t get errors about the types. i will not need them later because i will only use transport numbers

    – 

Leave a Comment