22

I use xlrd to read data from excel files.

For integers stored in the files, let's say 63, the xlrd interprets it as 63.0 of type number.

Why can't xlrd recognize 63 as an integer?

Assume sheet.row(1)[0].value gives us 63.0. How can I convert it back to 63.

juliomalegria
  • 24,229
  • 14
  • 73
  • 89
Terry Li
  • 16,870
  • 30
  • 89
  • 134
  • It'd just be int(yourfloat), wouldn't it? http://docs.python.org/library/functions.html#int. I won't post as an answer - I feel like I may be insulting you and probably missed something. – stackuser10210 Jan 11 '12 at 19:52
  • @stackuser10210 I wonder why integer 63 in the spread sheet becomes 63.0. – Terry Li Jan 11 '12 at 19:54
  • No idea. I'm not familiar with xlrd and I've never done similar. If they're really integers in excel (ie, A1=63.0, B1=Int(A1), and you're getting 63.0 from B1, then I can understand the problem. Sorry I don't have an answer. – stackuser10210 Jan 11 '12 at 20:01
  • 2
    There are no integers in Excel. Just floating point numbers in drag. – Steven Rumbalski Jan 11 '12 at 20:49
  • Since at least [1.0.0](https://pandas.pydata.org/pandas-docs/version/1.0.0/whatsnew/v1.0.0.html#experimental-na-scalar-to-denote-missing-values) there's an [Int64 type](https://pandas.pydata.org/pandas-docs/stable/user_guide/integer_na.html#construction) (note the capital I) which can also handle missing values. – Griffith Rees Mar 11 '21 at 12:36

6 Answers6

26

Excel treats all numbers as floats. In general, it doesn't care whether your_number % 1 == 0.0 is true or not.

Example: A1 = 63.0, B1 = 63, C1 = INT(A1), A2 = TYPE(A1), B2 = TYPE(B1), C2 = TYPE(C1) You'l see that TYPE() returns 1 in each case.

From the Excel Help:

If value is   TYPE returns 
Number        1 
Text          2 
Logical value 4 
Error value   16 
Array         64

xlrd reports what it finds. xlrd doesn't mangle its input before exposing it to you. Converting a column from (62.9, 63.0, 63.1, etc) to (62.9, 63, 63.1, etc) would seem like a pointless waste of CPU time to me.

John Machin
  • 81,303
  • 11
  • 141
  • 189
15

Looking at the different cell types in the documentation, it seems that there isn't any integer type for cells, they're just floats. Hence, that's the reason you're getting floats back even when you wrote an integer.

To convert a float to an integer just use int:

>>> int(63.0)
63
>>> int(sheet.row(1)[0].value)
63
jcollado
  • 39,419
  • 8
  • 102
  • 133
12

The answer given by jcollado is alright if you have all the entries in the excel sheet as numbers which are integers. But suppose you have a number which is a float you can always put a check condition like - 

    if i==int(i): //checking for the integer:
      print int(i)      // solving your problem and printing the integer
    else:
      print i           //printing the float if present

Hope you find this useful :)

minocha
  • 1,043
  • 1
  • 12
  • 26
6

I'm reminded of this gem from the xlrd docs:

Dates in Excel spreadsheets

In reality, there are no such things. What you have are floating point numbers and pious hope.

The same is true of integers. Perhaps minus the pious hope.

Steven Rumbalski
  • 44,786
  • 9
  • 89
  • 119
2

Since 1.0.0 try convert_dtypes()

Following this useful answer to a related question: the pandas.DataFrame.convert_dtypes method tries to convert columns to other (at present experimental) dtypes in the returned DataFrame. These dtypes include Int64, boolean and StringDtype which also handle missing values. See Working with text data for motivation to convert to string columns and this section on kleene logical operators for the advantages of a boolean column.

Griffith Rees
  • 1,285
  • 2
  • 15
  • 24
0

I've written code which tries to convert numbers to strings as they are displayed by Excel. The approach works works at least for integer numbers and numbers with two digits after a comma. It should work also for many other formats.

The idea, screenshots and code are here: http://uucode.com/blog/2013/10/22/using-xlrd-and-formatting-excel-numbers/

olpa
  • 1,167
  • 10
  • 28