pandas read_excel: nan values forcing others in the same column to be converted to float

Question

Let's say I have the following Excel file to be read:

What I want is a simple solution (preferrably one-line) that can read the excel so that the dates are converted to str (or at least int), and the blank values are to nan or nat or whatever can be detected by pd.isnull.

If I use df = pd.read_excel(file_path), what I get is

df
Out[8]: 
              001002.XY  600123.AB  123456.YZ   555555.GO
ipo_date     20100203.0   20150605        NaN  20090501.0
delist_date         NaN   20170801        NaN         NaN

So pandas recognised blank cells as NaN, which is fine, but the pet peeve is that all the other values are forced to float64, even if they are intended to be just str or ints. (edit: it seems that if a column, e.g. the column [1], has no nans, then the other values won't be forced to float. However, in my case most columns have delist_date blank, since most stocks have an ipo date but are not delisted yet.)

For what I know though, I tried the dtype=str keyword arg, and it gives me

df
Out[10]: 
            001002.XY 600123.AB 123456.YZ 555555.GO
ipo_date     20100203  20150605       nan  20090501
delist_date       nan  20170801       nan       nan

Looks good? True, the dates are now str, but one thing ridiculous is that the nans now become literal strings! E.g.

df.iloc[1, 0]
Out[12]: 
'nan'

which would make me have to add something weird like df.replace later on.

I didn't try the converters because it would require specifying datatype column by column, and the actual excel file I'm working with is a very long spreadsheet (3k columns approx). I don't want to transpose the spreadsheet in excel itself either.

Could anybody help? Thanks in advance.

Answer 1

Use dtype=object as the parameter.

Great explanation here: pandas distinction between str and object types