ENH: make read_excel dtype parameter compatible with multiple sheets (e.g sheet_name =[0,"list_of_sheets"])

[struck89]

Feature Type

• Adding new functionality to pandas

• Changing existing functionality in pandas

• Removing existing functionality in pandas

Problem Description

I would like the dtype parameter to be compatible with opening multiple sheets.

Feature Description

when a list of sheets is passed, dtype could be a dictionary with {elem_on_list: usual_dtype_parameter}
so if
sheet_name = [0,'Sheet3']
dtype could be
dtype={0:{'colA':str, 'colB': 'float64'}, 'Sheet3':{'colA':'datetime64[ns]', 'colB': str}

Alternative Solutions

Iterate yourself and create the dictionary yourself with

dfs = {}
dtypes={0:{'colA':str, 'colB': 'float64'}, 'Sheet3':{'colA':'datetime64[ns]', 'colB': str}
for sheet_name in [0,'Sheet3']:
   dfs[sheet_name] = pd.read_excel('file.xlsx',sheet_name=sheet_name,dtype=dtypes[sheet_name])

Additional Context

In my case, pandas automatic decision of what the object is, doesn't work well in my scenarios. It always assumes numbers, even if the column has explicitly been marked as "text" in Excel, so I need to specify dtypes.

This would make my life easier, and would make sense that the multiple sheet name functionality is compatible with also giving multiple dtype arguments per sheet. I haven't tested if just adding all the columns at the dictionary would work, regardless of the Sheet, but it's very obvious that one can have the same column name in two sheets, and that one might want different datatypes there. And given that I plan to use something like this for automation purposes, I can't just assume same column name will always be the same type.

[rhshadrach]

Thanks for the report!

In my case, pandas automatic decision of what the object is, doesn't work well in my scenarios. It always assumes numbers, even if the column has explicitly been marked as "text" in Excel, so I need to specify dtypes.

Can you give a reproducible example where this happens?

[DaveParr]

@rhshadrach in the absence of a repro for the column type inference, I still am interested in the core feature of:

As a user reading an excel file with multiple sheets, I want to be able to specify the dtype argument across multiple sheets and columns, where the same column name may exist across multiple sheets, and I want to have differing behaviour per sheet.

I'm not able to find any good examples or docs to support this use, though it does seem to be 'supported' in a limited way. Given a function like:

def read():
    out = pd.read_excel(
        "tmp.xlsx",
        sheet_name=["Sheet1", "Sheet2"],
        dtype={"Percent": str},
    )
    for df in out.values():
        df.info()
    return out

Where tmp.xslx has 1 column in both sheets:

That some control can be had on the data read to pandas via: dtype={"Percent": str}. However it's unclear if it's possible to specify separate behaviour for Sheet 1:Percent compared to Sheet 2: Percent. Is this possible/ supported? If so what would be the syntax?

Similarly, how is the usecols argument supposed to work when sheet_name is a list? It seems that the columns specified in usecols are enforced to be present in all sheets identically? Is there a way to set the usecols to be either specified uniquely per sheet, or permissively allowed to be missing in some sheets?

[rhshadrach]

Use ExcelFile for specifying per-sheet arguments: https://pandas.pydata.org/docs/reference/api/pandas.ExcelFile.html