Cannot convert numbers to strings when reading an Excel spreadsheet

[deanjones]

I'm reading some excel spreadsheets (xlsx format) into pandas using read_excel, which generally works great. The problem I have is that when a column contains numbers, pandas converts these to float64 type, and I would like them to be treated as strings. After reading them in, I can convert the column to str:

 my_frame.my_col = my_frame.my_col.astype('str') 

This works as far as assigning the right type to the column, but when I view the values in this column, the strings are formatted in scientific-format e.g. 8.027770e+14, which is not what I want. I like to be able to tell Pandas to read the columns as strings. My current solution involves dropping down to xlrd to read the spreadsheet.

See stackoverflow question: http://stackoverflow.com/q/20970483/690890

[jreback]

have you tried passing the dtype argument? (its documented in read_csv)

[deanjones]

Thanks, but the dtype argument isn't available for read_excel

[jreback]

did you try it? it passes thru other keywords thru.

[deanjones]

Yes. I get 'ValueError: dtype is not supported with python parser'

[jreback]

ahh..sorry...related to #5232 is currently an open bug

not sure if their is a work-around ATM

@jtratner ?

[jmcnamara]

I like to be able to tell Pandas to read the columns as strings. My current solution involves dropping down to xlrd to read the spreadsheet.

How exactly are you doing that?

As far as I can see from the xlrd code, when it reads a number from an xlsx file it immediately converts it to a float before returning the cell object to the user.

So unless I am missing something I don't see how Pandas can be made to read the columns as strings. It will always get a float from xlrd for an xls or xlsx file if Excel has stored them as numbers.

The CSV readers behave differently and can be type specified because they return all read data as strings regardless of whether it looks like a number of not.

Perhaps @jreback or @jtratner can suggest a different way to convert a column from float64 to formatted strings.

But apart from a workaround I don't think this issue is fixable in the way that you would like.

[jtratner]

Have you tried this in 0.13? If all your numbers are ints, pandas will read them in as ints (recent change in 0.13) and you can just do x.astype(str) to get what you want, albeit not necessarily in a particularly fast way. That said, if you're converting them to strings, they'll all be object dtype, so you're going to be using more memory and space. All numbers are stored as float in Excel, so you can't really get around this...

[jreback]

closing as fixed in 0.13

[manugarri]

why is this fixed? There is still no way of reading excel file columns as objects.

[jreback]

see @jmcnamara comment

unless their is an easy way to tell xlrd to read it as not a number (eg do not do any conversions)

[manugarri]

Would it be possible then to keep every column as a float? Because this means datetime columns in excel are very hard to handle (cannot read as text, cannot convert as text)

[jreback]

I believe that xlrd converts datetime properly (iirc you have to specify what is a date column)

is their a problem with some conversion?

[manugarri]

Well, in my case I don't know the file structure before hand, and hence i cannot specify which columns are dates at the moment of import.

Maybe there is another way I can detect the date columns before using the read_excel function?

[jmcnamara]

Maybe there is another way I can detect the date columns before using the read_excel function

Not easily.

Dates in Excel are stored as real numbers. The only thing that distinguishes them from other real numbers is the number format that is applied to them. These number formats aren't generally stored with the number in the Excel file and in general some level of heuristic is required to determine if the cell contains a date.

Xlrd does a lot of work around this so you don't have to.

However, could we take a step back and ask what you are trying and do?