ENH: pd.read_excel with table parameter

[samukweku]

Is your feature request related to a problem?

Excel has tables , that makes data managing within Excel easier and offers some other features.

At the moment, pandas cannot access those tables, it simply reads in all the data. I think it would be helpful if a table parameter was added to pd.read_excel to capture table(s) defined in a sheet.

Sample Data:

Attached is a sample excel file: 016-MSPTDA-Excel.xlsx

With the current implementation of read_excel, we cannot select specific tables (dSalesReps, dProduct, dCategory, or dSupplier). Even if we read the Tables sheet, it becomes quite hard to separate the data into individual tables.

Describe the solution you'd like

pd.read_excel(io=filename, sheet=sheetname, table=tablename, ...)

The table parameter can have a default of None, in which case the entire sheet is read in; if however table is not None, then the table or lists of tables only are read in. The openpyxl library would be used to implement this.

API breaking implications

I am not aware of any API breaking implications

Describe alternatives you've considered

It could be done outside Pandas, where you read in the data first through openpyxl, before passing it to Pandas. I wrote a blog post about it; I feel, however, that it may be more convenient to provide that same functionality within Pandas and let the user worry less about the abstractions.

Additional context

I did check to see if this has been raised before, but did not find any. If it already has, kindly point me to that issue, and I will gladly close this.

Thanks.

[shraddhazpy]

Hi, if @samukweku is not working on it and this is up for grabs, I'd would want to contribute.

[samukweku]

@shraddhazpy pls go ahead

[scotscotmcc]

hi, @shraddhazpy, are you working on this? This is functionality I would definitely appreciate. I haven't contributed to any public projects before, so it may be too big for me to tackle, but I would be interested in helping if I can.

[samukweku]

@scotscotmcc I think U can just type take and it will be assigned to u

[scotscotmcc]

I'm stalling a bit on taking this; I did some playing around with the code yesterday, but today couldn't get the development environment with docker set up...

That said, I want to raise a question around how a table_name parameter would function with the existing sheet_name. I can think of three different reasonable options.

1. Require sheet_name and table_name, grab the named table(s) from the named sheet (this is what was in the opening post).
2. If table_name is used, then don't require sheet_name at all, and just read the table(s).
3. If both are used, then read the entire sheet(s) and separately read the entire table(s). (my favorite)

Excel forces each table in a workbook to have a unique name, and so any table listed in the parameter could only exist once. Therefore, we don't need to know the sheet_name when the function is ran, we can find it, and it means one less thing for the user. Providing sheet_name would mean we don't have to search every sheet to have the name of the table, but it would also mean we would have an issue if the table is one a different sheet.

Requiring sheet_name when using table_name would also make it more difficult to read multiple tables that are on different sheets. You could require that, if you wanted to read multiple tables, you provide a list for sheet_name and a list for table_name, and the nth element of each list are pairs, but that seems bad...

Further, if you wanted to read all of Sheet1 and also Table2 that is within Sheet2 (but don't want to read Sheet2), it would be great to be able to do both of these at the time. To that end, allowing a new table_name to actually not really interact with the sheet_name parameter would allow you to do just that. Providing sheet_name reads sheets, and providing table_name reads tables, and they do the same thing regardless of what is up with the other.

[scotscotmcc]

I think that this enhancement is / should be dependent on this other to allow for passing engine_kwargs to pd.read_excel(). #43053

Currently, pd.read_excel() with engine=openpyxl (default if filetype is .xlsx) will default to setting the read_only = True in the openpyxl.load_workbook() call. However, you can't get table information out of the workbook object in openpyxl then; you need to have read_only = False.

The linked enhancement is to allow passing explicit arguments into the engine. That framework could then be used for this enhancement to change the read_only argument. The alternative would be that this enhancement creates a separate process to change the engine arguments, which seems clunky, inefficient, and cumbersome.

[samukweku]

if this helps, there is an implementation for reading xlsx tables here; hopefully it can be done within Pandas (and one less library for users to install 😁

[Meadiocre]

Hello @samukweku and @mroeschke,

My project team is looking for Pandas enhancement features for our grad school semester long project. We saw this task and would like to contribute if possible!