BUG: read_excel return empty dataframe when using usecols
State: closed

- [x] closes #18273
- [x] tests added / passed
- [x] passes git diff master --name-only -- "*.py" | grep "pandas/" | xargs -r flake8
- [x] whatsnew entry

As mentioned read_excel returns an empty DataFrame when usecols argument is a list of strings.
Now lists of strings are correctly interpreted by read_excel function.

Labels:
name: API Design
name: IO Excel
description: read_excel, to_excel

Comments:
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/20480?src=pr&el=h1) Report
> Merging [#20480](https://codecov.io/gh/pandas-dev/pandas/pull/20480?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/415012f4f38ca0cf41717c51e49bd2349cba09a8?src=pr&el=desc) will **not change** coverage.
> The diff coverage is `n/a`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/20480/graphs/tree.svg?token=eZ4WkYLtcO&src=pr&height=150&width=650)](https://codecov.io/gh/pandas-dev/pandas/pull/20480?src=pr&el=tree)

```diff
@@           Coverage Diff           @@
##           master   #20480   +/-   ##
=======================================
  Coverage   91.89%   91.89%           
=======================================
  Files         153      153           
  Lines       49596    49596           
=======================================
  Hits        45576    45576           
  Misses       4020     4020
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.29% <ø> (ø)` | :arrow_up: |
| #single | `41.86% <ø> (ø)` | :arrow_up: |


------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/20480?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/20480?src=pr&el=footer). Last update [415012f...e257100](https://codecov.io/gh/pandas-dev/pandas/pull/20480?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

This looks OK, but re-reading it, #18273 is actually two somewhat separate problems and we have a bit of an API tangle here

1.  passing a list of Excel column _letters_ doesn't work (this fixes that)
2.  passing a list column _names_ (e.g. column title `'foo'` in the spreadsheet) to `usecols` doesn't work anymore

Number 2 wasn't part of the `read_excel` documented args, but once worked, because this param used to be called `parse_cols` (#17774), and if you passed something to `usecols` it would get passed down to the TextParser logic and do the right thing
https://github.com/pandas-dev/pandas/blob/1915ffc53ea60494f24d83844bbff00efa392c82/pandas/io/excel.py#L529

Not sure what the solution is, there could be ambiguous cases (column titled`'A'` in spreadsheet column `B`), that would be easiest to handle with separate kwargs.  Or could just have usecols do everything and warn in cases of ambiguity.

Not saying you need to address item 2 in this PR, just need a new issue if not.
Hi @chris-b1, thank you for your comment.

I tried to test what you said, but I don't get it.

```markdown
| Day      | Number | Animal |
|----------|--------|--------|
| 10/12/18 | 5      | Tatu   |
| 10/13/18 | 4      | Paca   |
```
I tried the following:
```
df_not_ok = pd.read_excel("mock.xlsx", ["Day"])
>> XLRDError: No sheet named <'Day'>

df_not_ok = pd.read_excel("mock.xlsx", usecols=["Day"])
>> Empty DataFrame
>> Columns: []
>> Index: []
```

```
pd.show_versions()

INSTALLED VERSIONS
------------------
commit: None
python: 3.5.3.final.0
python-bits: 64
OS: Linux
OS-release: 4.9.0-6-amd64
machine: x86_64
processor: 
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.22.0
pytest: None
pip: 9.0.1
setuptools: 39.0.1
Cython: None
numpy: 1.14.2
scipy: None
pyarrow: None
xarray: None
IPython: 6.2.1
sphinx: None
patsy: None
dateutil: 2.7.1
pytz: 2018.3
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: 1.1.0
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 0.999999999
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
```

If someone create feature or issue I may can work to make possible to pass col names as arguments to read just them.

Right, that doesn't work on `0.22`, but it used to, which is what #18273 is about
```python
print(pd.__version__)
# 0.20.3
df = pd.DataFrame({'foo': [1, 2, 3], 'bar': [4, 5, 6]})
df.to_excel('tmp.xlsx', index=False)
pd.read_excel('tmp.xlsx', usecols=['bar'])
   bar
0    4
1    5
2    6
```
Hello @jacksonjos! Thanks for updating the PR.





Cheers ! There are no PEP8 issues in this Pull Request. :beers: 

##### Comment last updated on June 09, 2018 at 22:45 Hours UTC
Ok, I got it, @chris-b1. It isn't better to create another issue to implement the possibility to choose columns you want to load by column name using another named argument? I think that might be confusing use `usecols` for a lot of different possibilities.
Is there anything wrong with the requested changes that I implemented to make the pull requested fulfill the requirements to be accepted? I'm asking because I don't understand why this pull requested it wasn't accepted yet.
Yeah, I've been a bit stuck on what to do here.  Current (0.22) behavior where `usecols` refers only to excel names for regions is bad, it broke with (untested/undocumented behavior but working!) 0.20.3 and is inconsistent with, e.g. `read_csv`.

I suppose the only reasonable solution is two keywords.  `usecols_labels` and `usecols_?`

- `usecols_excel`
- `usecols_a1`  (very Excel specific, but "A1 notation" is a somewhat common term)
- `excel_columns`
Hi,

I prefer to use `usecols_lables` and `usecols_excel` because it's possible refer columns by A1 notation and by numbers. If we decide to use `usecols_a1` the named argument would cause misunderstanding about the arguments can be passed to it.

What do you think @chris-b1 and @jreback ?
``usecols`` should refer to names just like it does in all other parsers. no problem adding ``usecols_a1`` (or similar).
OK, @jacksonjos here's what I propose.  Most painful part will be backwards compat & testing.  If you don't want to take it all the way, just let me know and I'll finish, would like to get this in for 0.23.

1.  Add a new kwarg `usecols_excel` which replicates the current `usecols` + your fix here
2.  Since we're breaking compat, inspect `usecols` and if it is string AND parses to an excel region, issue a warning that the arg was renamed.  This could raise a false positive for a single letter (e.g. `'A'`) but don't see a way around it.
3.  `usecols` should get passed to the `TextParser` object, which will use it in the manner @jreback suggests.
https://github.com/pandas-dev/pandas/blob/1915ffc53ea60494f24d83844bbff00efa392c82/pandas/io/excel.py#L529

Hi @chris-b1 and @jreback.

Thank you for your answers. i'm already working on this issue and I'll
update the pull request in the coming days following your suggestions.
If I have some trouble I ask for help.

Best regards

2018-04-16 16:56 GMT-03:00 chris-b1 <notifications@github.com>:

> OK, @jacksonjos <https://github.com/jacksonjos> here's what I propose.
> Most painful part will be backwards compat & testing. If you don't want to
> take it all the way, just let me know and I'll finish, would like to get
> this in for 0.23.
>
>    1. Add a new kwarg usecols_excel which replicates the current usecols
>    + your fix here
>    2. Since we're breaking compat, inspect usecols and if it is string
>    AND parses to an excel region, issue a warning that the arg was renamed.
>    This could raise a false positive for a single letter (e.g. 'A') but
>    don't see a way around it.
>    3. usecols should get passed to the TextParser object, which will use
>    it in the manner @jreback <https://github.com/jreback> suggests.
>    https://github.com/pandas-dev/pandas/blob/
>    1915ffc53ea60494f24d83844bbff00efa392c82/pandas/io/excel.py#L529
>    <https://github.com/pandas-dev/pandas/blob/1915ffc53ea60494f24d83844bbff00efa392c82/pandas/io/excel.py#L529>
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/20480#issuecomment-381728831>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/AANuQu9txp5CMJXR_WSI3THEn5GK_RKXks5tpPdngaJpZM4S6AzV>
> .
>

Hi @jreback and @chris-b1, I'm about to finish the new version of this pull request and I would like to know your opinion about what to do when someone pass both arguments, usecols_excel and usecols, to read_excel function?

Should I raise an exception, print some warning or do nothing?

Thanks in advance.
I would raise.

On Sun, Apr 22, 2018, 8:16 PM Jackson Souza <notifications@github.com>
wrote:

> Hi @jreback <https://github.com/jreback> and @chris-b1
> <https://github.com/chris-b1>, I'm about to finish the new version of
> this pull request and I would like to know your opinion about what to do
> when someone pass both arguments, usecols_excel and usecols, to read_excel
> function?
>
> Should I raise an exception, print some warning or do nothing?
>
> Thanks in advance.
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/20480#issuecomment-383442898>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/AB1b_HG6hbf_x2bJTX9HfXQf7H6ieEtnks5trUdwgaJpZM4S6AzV>
> .
>

Hey, guys! I did a new push.

All requested changes are in the commit.
I'm sorry for doing this too late, but last two weeks were really had at my job and I was so tired to work on it.

I didn't understand why one of CI tests didn't pass because I do not see any relationship between the tests that did not pass and the feature I implemented/corrected.

Do you have any idea?
If there is something else I need to do to completed the implementation just tell me that this week I have time to complete the jog if there is anything else to do about this issue.

Thanks!
Hi, @chris-b1, may you take a look at @jreback observations in the last changes I've made?
I would like to know if after his observations and the comments I wrote at https://github.com/pandas-dev/pandas/pull/20480#discussion_r185143157 you have something else to add. So, i can push all suggested changes at one time.
Sorry for the delay, see comments in-line, lmk if you have other questions.
Guys (@chris-b1 and @jreback), I've made a new push. You may have any questions about changes in the tests involving 'index_col'. If you don't understand something feel free o ask.
I did more changes. Please, @jreback and @chris-b1 take a look.
@jreback and @chris-b1 Is there some problem with the code, yet?
@chris-b1 and @jreback, please take a look to the changes I've made and have in mind what I wrote in https://github.com/pandas-dev/pandas/pull/20480#discussion_r187803587


Hi again, @chris-b1 and @jreback , please, take a look at the changes I've made now.

I've removed backward compatibility to the behavior of `index_col` keyword in `read_excel` as I've explained above.

Please, may anyone review the updates and check if there is something else I have to do?

@chris-b1, @jreback 
@jreback and @chris-b1 I've just updated the commit. Take another look, please.
@jreback and @chris-b1, may you take a look ate the updated commit?
@jacksonjos, sorry I'm having a bit of trouble wrapping my head around exactly how the changes to `index_col` / `usecols` interact - could you show a before and after example of where behavior will be different?  If we are going to change would want that in the docs anyways - otherwise this looks good to me.
@chris-b1 and @jreback 

The issue is related to spreadsheets whose first column does not have a column name. So, when reading the `DataFrame` from `read_excel` without selecting it to be read, it is interpreted as the index in the current behavior of `read_excel`.

Just as a reminder, this is necessary because now when `usecols` receives a list of ints, it is interpreted by `TextParser` instead of `_parse_excel` as before.

There is an example that is used in some tests of pandas whose spreadsheet can be seen at `pandas/tests/io/data/test1.xlsx`.

|                | A              | B               | C                | D                |
|----------------|-----------------|------------------|------------------|----------------|
| 1/3/2000 0:00  | 0.980268513777  | 3.68573087906    | -0.364216805298  | -1.15973806169 |
| 1/4/2000 0:00  | 1.04791624281   | -0.0412318367011 | -0.16181208307   | 0.212549316967 |
| 1/5/2000 0:00  | 0.498580885705  | 0.731167677815   | -0.537677223318  | 1.34627041952  |
| 1/6/2000 0:00  | 1.12020151869   | 1.56762092543    | 0.00364077397681 | 0.67525259227  |
| 1/7/2000 0:00  | -0.487094399463 | 0.571454623474   | -1.6116394093    | 0.103468562917 |
| 1/10/2000 0:00 | 0.836648671666  | 0.246461918642   | 0.588542635376   | 1.0627820613   |
| 1/11/2000 0:00 | -0.157160753327 | 1.34030689438    | 1.19577795622    | -1.09700699751 |

So, below there is a comparison of the change from the current behavior of `index_col` to the new one I have written.

```
> df_current = pd.read_excel("pandas/tests/io/data/test1.xlsx", usecols=[0, 2, 3], index_col=0)

> df_current

            B         C         
2000-01-03  3.685731 -0.364217
2000-01-04 -0.041232 -0.161812
2000-01-05  0.731168 -0.537677
2000-01-06  1.567621  0.003641
2000-01-07  0.571455 -1.611639
2000-01-10  0.246462  0.588543
2000-01-11  1.340307  1.195778

> df_new_one = pd.read_excel("/pandas/tests/io/data/test1.xlsx", usecols=[1, 2], index_col=0)

> df_new_one

            B         C
2000-01-03  3.685731 -0.364217
2000-01-04 -0.041232 -0.161812
2000-01-05  0.731168 -0.537677
2000-01-06  1.567621  0.003641
2000-01-07  0.571455 -1.611639
2000-01-10  0.246462  0.588543
2000-01-11  1.340307  1.195778
```
I re-read your explanation but I'm honestly still not following why the change to `usecols` with numerics is necessary, sorry I'm missing something.  `read_csv` is consistent with the old behavior and with `engine='python'` also uses the `TextParser` class, why can't we do the same here?

```python
In [6]: pd.read_excel('pandas/pandas/tests/io/data/test1.xlsx').to_csv('tmp.csv')

In [7]: !head tmp.csv
,A,B,C,D
2000-01-03,0.980268513777,3.68573087906,-0.364216805298,-1.15973806169
2000-01-04,1.04791624281,-0.0412318367011,-0.16181208307,0.212549316967
2000-01-05,0.498580885705,0.731167677815,-0.537677223318,1.34627041952
2000-01-06,1.12020151869,1.56762092543,0.00364077397681,0.67525259227
2000-01-07,-0.487094399463,0.571454623474,-1.6116394093,0.103468562917
2000-01-10,0.836648671666,0.246461918642,0.588542635376,1.0627820613
2000-01-11,-0.157160753327,1.34030689438,1.19577795622,-1.09700699751

In [8]: pd.read_csv('tmp.csv', usecols=[0, 2, 3], index_col=0)
Out[8]:
                   B         C
2000-01-03  3.685731 -0.364217
2000-01-04 -0.041232 -0.161812
2000-01-05  0.731168 -0.537677
2000-01-06  1.567621  0.003641
2000-01-07  0.571455 -1.611639
2000-01-10  0.246462  0.588543
2000-01-11  1.340307  1.195778

In [9]: pd.read_csv('tmp.csv', usecols=[0, 2, 3], index_col=0, engine='python')
Out[9]:
                   B         C
2000-01-03  3.685731 -0.364217
2000-01-04 -0.041232 -0.161812
2000-01-05  0.731168 -0.537677
2000-01-06  1.567621  0.003641
2000-01-07  0.571455 -1.611639
2000-01-10  0.246462  0.588543
2000-01-11  1.340307  1.195778
```

#@chris-b1,

Take a look at the examples below comparing the current pandas behavior to the new one,
so maybe is easier to understand.
@jreback, do you get it what is happening here?

```
# CURRENT BEHAVIOR
In [5]: pd.read_excel('pandas-jackson/pandas/tests/io/data/test1.xlsx', usecols=[0, 2, 3])
Out[5]: 
                   B         C
2000-01-03  3.685731 -0.364217
2000-01-04 -0.041232 -0.161812
2000-01-05  0.731168 -0.537677
2000-01-06  1.567621  0.003641
2000-01-07  0.571455 -1.611639
2000-01-10  0.246462  0.588543
2000-01-11  1.340307  1.195778

In [6]: pd.read_excel('pandas-jackson/pandas/tests/io/data/test1.xlsx', usecols=[0, 2, 3],
...   index_col=0)
Out[6]: 
                   B         C
2000-01-03  3.685731 -0.364217
2000-01-04 -0.041232 -0.161812
2000-01-05  0.731168 -0.537677
2000-01-06  1.567621  0.003641
2000-01-07  0.571455 -1.611639
2000-01-10  0.246462  0.588543
2000-01-11  1.340307  1.195778

# Index 0 is not in used_cols, so it's not possible to use it in the index
In [7]: pd.read_excel('pandas-jackson/pandas/tests/io/data/test1.xlsx', usecols=[1, 2])
Out[7]: 
          A         B
0  0.980269  3.685731
1  1.047916 -0.041232
2  0.498581  0.731168
3  1.120202  1.567621
4 -0.487094  0.571455
5  0.836649  0.246462
6 -0.157161  1.340307

# index_col is related to the 0-th col in the cols selected in usecols. In this case, [1, 2].
# So, the date column is not the index_col
In [8]: pd.read_excel('pandas-jackson/pandas/tests/io/data/test1.xlsx', usecols=[1, 2],
...     index_col=0)
Out[8]: 
                  B
A                  
 0.980269  3.685731
 1.047916 -0.041232
 0.498581  0.731168
 1.120202  1.567621
-0.487094  0.571455
 0.836649  0.246462
-0.157161  1.340307

In [9]: pd.read_excel('pandas-jackson/pandas/tests/io/data/test1.xlsx', usecols=[0, 2, 3],
...    index_col=1)
Out[9]: 
                   B         C
 3.685731 2000-01-03 -0.364217
-0.041232 2000-01-04 -0.161812
 0.731168 2000-01-05 -0.537677
 1.567621 2000-01-06  0.003641
 0.571455 2000-01-07 -1.611639
 0.246462 2000-01-10  0.588543
 1.340307 2000-01-11  1.195778

#PROPOSED BEHAVIOR

In [1]: pd.read_excel('pandas/tests/io/data/test1.xlsx', usecols=[0, 2, 3])
Out[1]: 
                   A         C         D
2000-01-03  0.980269 -0.364217 -1.159738
2000-01-04  1.047916 -0.161812  0.212549
2000-01-05  0.498581 -0.537677  1.346270
2000-01-06  1.120202  0.003641  0.675253
2000-01-07 -0.487094 -1.611639  0.103469
2000-01-10  0.836649  0.588543  1.062782
2000-01-11 -0.157161  1.195778 -1.097007

In [2]: pd.read_excel('pandas/tests/io/data/test1.xlsx', usecols=[0, 2, 3], index_col=0)
Out[2]: 
                   A         C         D
2000-01-03  0.980269 -0.364217 -1.159738
2000-01-04  1.047916 -0.161812  0.212549
2000-01-05  0.498581 -0.537677  1.346270
2000-01-06  1.120202  0.003641  0.675253
2000-01-07 -0.487094 -1.611639  0.103469
2000-01-10  0.836649  0.588543  1.062782
2000-01-11 -0.157161  1.195778 -1.097007

# Index 0 is not in used_cols, but TextParser receives all columns. So, the default index
# is the first column because it does not have a column name (1st column header is
# empty).
In [3]: pd.read_excel('pandas/tests/io/data/test1.xlsx', usecols=[1, 2])
Out[3]: 
                   B         C
2000-01-03  3.685731 -0.364217
2000-01-04 -0.041232 -0.161812
2000-01-05  0.731168 -0.537677
2000-01-06  1.567621  0.003641
2000-01-07  0.571455 -1.611639
2000-01-10  0.246462  0.588543
2000-01-11  1.340307  1.195778

# index_col is the 0-th col in the DataFrame, not the 0-th in usecols. In this case, [1, 2].
# So, the date column is selected to be the index because is the first column in the
# DataFrame.
In [4]: pd.read_excel('pandas/tests/io/data/test1.xlsx', usecols=[1, 2], index_col=0)
Out[4]: 
                   B         C
2000-01-03  3.685731 -0.364217
2000-01-04 -0.041232 -0.161812
2000-01-05  0.731168 -0.537677
2000-01-06  1.567621  0.003641
2000-01-07  0.571455 -1.611639
2000-01-10  0.246462  0.588543
2000-01-11  1.340307  1.195778

In [5]: pd.read_excel('pandas/tests/io/data/test1.xlsx', usecols=[1, 2], index_col=1)
Out[5]: 
                   B         C
 3.685731 2000-01-03 -0.364217
-0.041232 2000-01-04 -0.161812
 0.731168 2000-01-05 -0.537677
 1.567621 2000-01-06  0.003641
 0.571455 2000-01-07 -1.611639
 0.246462 2000-01-10  0.588543
 1.340307 2000-01-11  1.195778


I'm also dumping the behavior of csv you used as an example because I saw that it
diverges from current and proposed behavior of read_excel. So, maybe we can settle,
or agree about how read_excel is supposed to behave.

In [11]: pd.read_csv('tmp.csv', usecols=[0, 2, 3])
Out[11]: 
   Unnamed: 0         B         C
0  2000-01-03  3.685731 -0.364217
1  2000-01-04 -0.041232 -0.161812
2  2000-01-05  0.731168 -0.537677
3  2000-01-06  1.567621  0.003641
4  2000-01-07  0.571455 -1.611639
5  2000-01-10  0.246462  0.588543
6  2000-01-11  1.340307  1.195778

In [12]: pd.read_csv('tmp.csv', usecols=[0, 2, 3], index_col=0)
Out[12]: 
                   B         C
2000-01-03  3.685731 -0.364217
2000-01-04 -0.041232 -0.161812
2000-01-05  0.731168 -0.537677
2000-01-06  1.567621  0.003641
2000-01-07  0.571455 -1.611639
2000-01-10  0.246462  0.588543
2000-01-11  1.340307  1.195778

In [13]: pd.read_csv('tmp.csv', usecols=[1, 2])
Out[13]: 
          A         B
0  0.980269  3.685731
1  1.047916 -0.041232
2  0.498581  0.731168
3  1.120202  1.567621
4 -0.487094  0.571455
5  0.836649  0.246462
6 -0.157161  1.340307

In [14]: pd.read_csv('tmp.csv', usecols=[1, 2], index_col=0)
Out[14]: 
                  B
A                  
 0.980269  3.685731
 1.047916 -0.041232
 0.498581  0.731168
 1.120202  1.567621
-0.487094  0.571455
 0.836649  0.246462
-0.157161  1.340307

What do you think?

```
@jacksonjos can you show the proposed right next to the existing. and use separators between so its clear.