How to prevent Calamine from auto-guessing data types when reading Excel files

Question

I’m working with an Excel file and need to read its contents into a DataFrame. When I use pandas (with default engine), I can specify the data type of the columns to be strings, which works perfectly:

import pandas as pd

df = pd.read_excel(fp, dtype=str, nrows=10)
print(df[col])

This gives me a column with values like:

4200000000

However, when I use Calamine to read the same file, the values in the same column end up with a .0 suffix:

from calamine import CalamineWorkbook

wb = CalamineWorkbook.from_path(fp)
row_list = wb.get_sheet_by_name(wb.sheet_names[0]).to_python()
print(row_list)

This results in: 4200000000.0

How can I stop Calamine from auto-guessing the data types? In pandas, I would use dtype=str, but my version of pandas does not support Calamine as an engine, and I cannot update it.

I have the same problem. I looked through the Rust codebase but did not find an answer there. Did you find a solution in the meantime? — EdGaere
– EdGaere, Commented Oct 31, 2024 at 10:15
No, for now I just believe this tool is unsafe to use because of dtype inference — euh
– euh, Commented Nov 4, 2024 at 10:01

RedAero · Accepted Answer · 2024-11-29 14:15:49Z

0

Excel stores all numbers internally as floats, reading 10 as anything but 10.0 is in and of itself a data type conversion, which calamine does not perform. This is the cost of its speed.

answered Nov 29, 2024 at 14:15

RedAero

1095 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

euh Nov 29, 2024 at 18:21

What about strings ? I noticed calamine changed strings into numbers after I read it

Collectives™ on Stack Overflow

How to prevent Calamine from auto-guessing data types when reading Excel files

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related