0

Instead of integers I'm getting floats that end with decimals .999 or .001.

I'm trying to read an Excel .xlsx file containing product data into a Pandas Dataframe with read_excel. Normally the EAN (UPC) codes work like they should, but now for some reason they show up as a float instead of integers.

I've checked the file with Excel and Numbers, all the EANs there are integers and using Increase Decimal shows .000 for every single one.

If I specify dtype as int for the read_excel, it cuts the decimals and everything with .001 shows correctly, but the ones with .999 don't. If I don't use dtype it shows the column as float64

round seems to work, but I wouldn't want to use it, as this problem shouldn't exist in the first place.

For example:

index EAN product
18492 615357122276.9999 Taylors Pure Assam musta tee 20ps
18493 615357122252.9999 Taylors hauduke 20pss lemon ginger
18494 615357119764.9999 Taylors green jasmine leaf tea 125g
18495 615357119703.0001 Taylors musta irtotee 125g English Break
sajo
  • 1
  • 1
  • Using `dtype={'EAN': object}` I get the following as `head()` of the dataframe EAN 8713913006350.000977 2875172000008 8712785002329.999023 8717662025686.000977 6430067777180 6430067777869 6430048441369 6430048446135 6430048441055 6430048441153.999023 – sajo Mar 08 '23 at 12:03
  • Have you tried setting the dtype to integer? https://stackoverflow.com/questions/32591466/python-pandas-how-to-specify-data-types-when-reading-an-excel-file – Paul Brennan Mar 08 '23 at 12:12
  • 1
    Yes, setting it as integer just cuts the decimals, so if for example 615357119764.9999 is shown as 615357119764 when it should be 615357119765. `round` seems to give the right numbers, but it still doesn't explain why it shows up like that in the first place. This has never happened before. – sajo Mar 08 '23 at 12:21
  • Null and NaN values in your xlsx file in the EAN column may force it to turn into float values when read with pandas. https://stackoverflow.com/a/65528499/18505884 This answer may help if you have Null values I'd say if it was working before, it's likely something minor changed and now it won't work. Think back and examine any small changes made to your files. – mrblue6 Mar 08 '23 at 18:20

0 Answers0