Issue when header dtype differ from the rest of the rows' dtype

[rslmartins]

Checks

• I have checked that this issue has not already been reported.
• I have confirmed this bug exists on the latest version of Polars.

Reproducible example

import polars as pl
df = pl.read_excel("data.xlsx")
df

Using data.xlsx, where the header is a date and the rest of the rows for columns 1, 2 and 3 it does not support a proper the difference between their dtypes and the display for df the log below.
Can someone help me with this issue?

Log output

shape: (3, 4)
┌───────┬──────────────┬──────────────┬──────────────┐
│ Name  ┆ __UNNAMED__1 ┆ __UNNAMED__2 ┆ __UNNAMED__3 │
│ ---   ┆ ---          ┆ ---          ┆ ---          │
│ str   ┆ i64          ┆ i64          ┆ i64          │
╞═══════╪══════════════╪══════════════╪══════════════╡
│ John  ┆ 1            ┆ 2            ┆ 3            │
│ Mary  ┆ 2            ┆ 4            ┆ 6            │
│ Polly ┆ 3            ┆ 6            ┆ 9            │
└───────┴──────────────┴──────────────┴──────────────┘

Issue description

Issue when header dtype differ from the rest of the rows' dtype

Expected behavior

shape: (3, 4)
┌───────┬──────────────┬──────────────┬──────────────┐
│ Name ┆ 2024-01-01 ┆ 2024-02-01 ┆ 2024-03-01│
│ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ Date ┆ Date ┆ Date │
╞═══════╪══════════════╪══════════════╪══════════════╡
│ John ┆ 1 ┆ 2 ┆ 3 │
│ Mary ┆ 2 ┆ 4 ┆ 6 │
│ Polly ┆ 3 ┆ 6 ┆ 9 │
└───────┴──────────────┴──────────────┴──────────────┘

Installed versions

--------Version info---------
Polars:              1.7.1
Index type:          UInt32
Platform:            Linux-5.8.0-41-generic-x86_64-with-glibc2.29
Python:              3.8.10 (default, Jul 29 2024, 17:02:10) 
[GCC 9.4.0]

----Optional dependencies----
adbc_driver_manager  <not installed>
altair               <not installed>
cloudpickle          <not installed>
connectorx           <not installed>
deltalake            <not installed>
fastexcel            0.11.6
fsspec               <not installed>
gevent               <not installed>
great_tables         <not installed>
matplotlib           <not installed>
nest_asyncio         <not installed>
numpy                1.24.4
openpyxl             <not installed>
pandas               <not installed>
pyarrow              17.0.0
pydantic             <not installed>
pyiceberg            <not installed>
sqlalchemy           <not installed>
torch                <not installed>
xlsx2csv             <not installed>
xlsxwriter           <not installed>

[cmdlineluser]

It seems to be specific to the default calamine engine. (fastexcel)

import fastexcel 

(fastexcel.read_excel("data.xlsx") 
  .load_sheet(0)
  .to_polars()
)

# shape: (3, 4)
# ┌───────┬──────────────┬──────────────┬──────────────┐
# │ Name  ┆ __UNNAMED__1 ┆ __UNNAMED__2 ┆ __UNNAMED__3 │
# │ ---   ┆ ---          ┆ ---          ┆ ---          │
# │ str   ┆ f64          ┆ f64          ┆ f64          │
# ╞═══════╪══════════════╪══════════════╪══════════════╡
# │ John  ┆ 1.0          ┆ 2.0          ┆ 3.0          │
# │ Mary  ┆ 2.0          ┆ 4.0          ┆ 6.0          │
# │ Polly ┆ 3.0          ┆ 6.0          ┆ 9.0          │
# └───────┴──────────────┴──────────────┴──────────────┘

pl.read_excel("data.xlsx", engine="xlsx2csv")
# shape: (3, 4)
# ┌───────┬────────────┬────────────┬────────────┐
# │ Name  ┆ 2024-01-01 ┆ 2024-02-01 ┆ 2024-03-01 │
# │ ---   ┆ ---        ┆ ---        ┆ ---        │
# │ str   ┆ i64        ┆ i64        ┆ i64        │
# ╞═══════╪════════════╪════════════╪════════════╡
# │ John  ┆ 1          ┆ 2          ┆ 3          │
# │ Mary  ┆ 2          ┆ 4          ┆ 6          │
# │ Polly ┆ 3          ┆ 6          ┆ 9          │
# └───────┴────────────┴────────────┴────────────┘

I think it may need to be brought upstream? https://github.com/ToucanToco/fastexcel/issues

[alexander-beedie]

The dtypes are correct, as they reflect the data, which is integer (not date). The default "calamine" engine (not unreasonably) expects column headers to be strings, not dates/floats/int/other (as that looks like data, not a header).

If you ensure that you convert your date-headers to text, everything will work as expected 👌 Alternatively you can use the xlsx2csv engine, as suggested by @cmdlineluser, but you'll lose a lot of speed in doing so.

Likely worth asking the calamine folks to consider this case, perhaps providing a string-coercion method when reading headers (which you could then pass when using the Polars method).