read_csv in combination with index_col and usecols

[floux]

Starting point:

http://pandas.pydata.org/pandas-docs/stable/io.html#index-columns-and-trailing-delimiters

If there is one more column of data than there are colum names, usecols exhibits some (at least for me) unintuitive behavior:

>>> data = 'a,b,c\n4,apple,bat,5.7\n8,orange,cow,10'
>>> pd.read_csv(StringIO(data))
        a    b     c
4   apple  bat   5.7
8  orange  cow  10.0
>>> pd.read_csv(StringIO(data), usecols=['a', 'b'])
   a       b
0  4   apple
1  8  orange
>>>

I was expecting it to be equal to

>>> pd.read_csv(StringIO(data))[['a', 'b']]
        a    b
4   apple  bat
8  orange  cow

I am not sure if my expectation is unfounded, though, and that this behavior is indeed intentional?

[wesm]

This feels buggy or at minimum not intuitive to me. I think it's just an edge case that's not addressed in the test suite. I'll have a look

[JamesRamm]

Hi
New to github so apologies if I'm out of place here. The issue has been closed, but the fix does not return expected behaviour?

When using use_cols with an implicit index column, the index column is now ignored and pandas returns it's own indexing (0, 1, 2, 3 etc). There is no way to specify that the index column should be used, as it doesn't have a header...

[jreback]

pls provide an explicity example (on a new issue) that shows your result

[laserson]

Here is a related problem where read_csv in combination with index_col and usecols is broken:

These commands works fine

>>> data = 'a,b,c\napple,bat,5.7\norange,cow,10'
        a    b     c
0   apple  bat   5.7
1  orange  cow  10.0

>>> pd.read_csv(StringIO(data), index_col=[0, 1])
               c
a      b        
apple  bat   5.7
orange cow  10.0

>>> pd.read_csv(StringIO(data), usecols=[2])
      c
0   5.7
1  10.0

But combining index_col and usecols breaks

>>> pd.read_csv(StringIO(data), index_col=[0, 1], usecols=[2])
---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
<ipython-input-95-c02d1a70fe51> in <module>()
----> 1 pd.read_csv(StringIO(data), index_col=[0, 1], usecols=[2])

~/miniconda3/lib/python3.6/site-packages/pandas/io/parsers.py in parser_f(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, escapechar, comment, encoding, dialect, tupleize_cols, error_bad_lines, warn_bad_lines, skipfooter, skip_footer, doublequote, delim_whitespace, as_recarray, compact_ints, use_unsigned, low_memory, buffer_lines, memory_map, float_precision)
    653                     skip_blank_lines=skip_blank_lines)
    654 
--> 655         return _read(filepath_or_buffer, kwds)
    656 
    657     parser_f.__name__ = name

~/miniconda3/lib/python3.6/site-packages/pandas/io/parsers.py in _read(filepath_or_buffer, kwds)
    403 
    404     # Create the parser.
--> 405     parser = TextFileReader(filepath_or_buffer, **kwds)
    406 
    407     if chunksize or iterator:

~/miniconda3/lib/python3.6/site-packages/pandas/io/parsers.py in __init__(self, f, engine, **kwds)
    762             self.options['has_index_names'] = kwds['has_index_names']
    763 
--> 764         self._make_engine(self.engine)
    765 
    766     def close(self):

~/miniconda3/lib/python3.6/site-packages/pandas/io/parsers.py in _make_engine(self, engine)
    983     def _make_engine(self, engine='c'):
    984         if engine == 'c':
--> 985             self._engine = CParserWrapper(self.f, **self.options)
    986         else:
    987             if engine == 'python':

~/miniconda3/lib/python3.6/site-packages/pandas/io/parsers.py in __init__(self, src, **kwds)
   1668                 (index_names, self.names,
   1669                  self.index_col) = _clean_index_names(self.names,
-> 1670                                                       self.index_col)
   1671 
   1672                 if self.index_names is None:

~/miniconda3/lib/python3.6/site-packages/pandas/io/parsers.py in _clean_index_names(columns, index_col)
   3081                     break
   3082         else:
-> 3083             name = cp_cols[c]
   3084             columns.remove(name)
   3085             index_names.append(name)

IndexError: list index out of range

Same is true if I do usecols=[0], usecols=[1], or usecols=['c']

[gfyoung]

That's not a bug. index_cols is relative to usecols. In this, you only have one column that you want to extract from the CSV, but you want two columns for the index.

[laserson]

I see. Could be worth clarifying in the docstring. Thanks!

[gfyoung]

Exactly. That's part of what I was proposing you do in #9098.