4

I’m using xlwings to read an Excel sheet into a pandas DataFrame with the built-in pd.DataFrame converter. Some of my columns contain mixed data (e.g. IDs or codes like 123, 00123, ABCD). When I read the sheet, xlwings converts all numeric cells to floats (123 → 123.0), which I want to avoid.

df = sht.range("A1").expand().options(pd.DataFrame, header=1, index=False).value

I would like only specific columns to be treated as text/strings — similar to using dtype={'col': str} in pandas.read_excel — while leaving the other columns default.

Is there a way to specify column-specific converters or options when using .options(pd.DataFrame, ...) in xlwings, so that I can prevent only selected columns from being read as floats?

If not, what’s the recommended pattern for handling this (e.g., reading the full DataFrame once and re-reading specific columns as pd.Series with custom numbers= converters)?

Improve this question
5
  • What stops you from using pandas.read_excel? Commented Oct 31 at 11:58
  • Two reasons: I use Excel as a user interface, so I need to dynamically load data into pandas. The files are very large, and xlwings loads the data much faster. Commented Oct 31 at 12:03
  • 1
    What you want to know, the reason and how to fix it is in the Xlwings documentation in the Numbers section. Commented Nov 1 at 0:10
  • If I understand the documentation correctly, it will read integers for all numeric values. I only need that for specific columns that are keys. Commented Nov 3 at 21:43
  • All numbers in that range. Probably you could read dataframes based on the Columns included in the range and set the number format to suit, then combine the dataframes into one. Commented Nov 4 at 3:49
Related questions
2191
How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
135
Pandas read_csv dtype read all columns but few as string
565
How do I create test and train samples from one dataframe with pandas?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.