IndexError when trying to read_table with pandas

Question

Update: This is a duplicate of "usecols with parse_dates and names" but this question was answered first.

I can't get this code to work for the life of me. As soon as I take out the names parameter it works fine, but that is just silly.

From a space delimited file I want to:

skip the header section
import selected columns
name the columns
parse two columns as a date
use parsed date as index

This almost works:

import panadas as pd
columns = [4, 5, 10, 11, 15, 16, 17, 26, 28, 29]
names = ["DATE","TIME","DLAT", "DLON", "SLAT", "SLON", "SHGT", "HGT", "N", "E"]
ppp_data = pd.read_table(
    filename,
    delim_whitespace=True, # space delimited
    skiprows=8, # skip header rows
    header=None, # don't use first row as column names
    usecols=columns, # only use selected columns
    names=names, # use names for selected columns
    parse_dates=[[4,5]], # join date and time columns and parse as date
    index_col=0, # use parsed date (now column 0) as index
)
print ppp_data

But here is the stack trace I'm getting

Traceback (most recent call last):
  File "plot_squat_test_pandas.py", line 30, in <module>
    index_col=0,
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/parsers.py", line 400, in parser_f
    return _read(filepath_or_buffer, kwds)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/parsers.py", line 205, in _read
    return parser.read()
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/parsers.py", line 608, in read
    ret = self._engine.read(nrows)
  File "/usr/local/lib/python2.7/dist-packages/pandas/io/parsers.py", line 1028, in read
    data = self._reader.read(nrows)
  File "parser.pyx", line 706, in pandas.parser.TextReader.read (pandas/parser.c:6745)
  File "parser.pyx", line 728, in pandas.parser.TextReader._read_low_memory (pandas/parser.c:6964)
  File "parser.pyx", line 804, in pandas.parser.TextReader._read_rows     (pandas/parser.c:7780)
  File "parser.pyx", line 865, in pandas.parser.TextReader._convert_column_data (pandas/parser.c:8512)
  File "parser.pyx", line 1105, in pandas.parser.TextReader._get_column_name (pandas/parser.c:11684)
IndexError: list index out of range

If I comment out the names=names parameter and it works fine

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 86281 entries, 2013-10-30 00:00:00 to 2013-10-30 23:59:59
Data columns (total 8 columns):
10    86281  non-null values
11    86281  non-null values
15    86281  non-null values
16    86281  non-null values
17    86281  non-null values
26    86281  non-null values
28    86281  non-null values
29    86281  non-null values

What am I missing? Or is this an issue with panadas and I should go make a bug report?

I'm using python 2.7.3, and with pandas the stack trace above is from stable release 0.12.0. I've tried this with development version 0.13.0rc1-119-g2485e09 and got the same results (different line numbers).

score 4 · Accepted Answer · edited May 23 '17 at 12:31

This is a bug in versions of pandas prior to and including the current development version 0.13.0rc1-119-g2485e09. There are two workarounds.

Workaround 1

Including the last column of the table in both usecols and names will suppress the IndexError

from StringIO import StringIO
import pandas as pd

data = """2013-10-11 11:53:49,1,2,3,4
2013-10-11 11:53:50,1,2,3,4
2013-10-11 11:53:51,1,2,3,4"""

df = pd.read_csv(
    StringIO(data),
    header=None,
    usecols=[0,2,4],
    names=["DATE","COl2","COL4"],
    parse_dates=["DATE"],
    index_col=0,
)
print df

Workaround 2

Alternately you can rename the columns after the fact, as in this question

ppp_data.rename(columns=dict(zip(columns[2:],names)), inplace=True)

unutbu · Answer 2 · 2013-12-21T15:08:06.993

1

names has 10 elements:

In [1]: len(["DATE","TIME","DLAT", "DLON", "SLAT", "SLON", "SHGT", "HGT", "N", "E"])
Out[1]: 10

But when you omit the names parameter, read_table is parsing only 8 columns:

Data columns (total 8 columns):

Therefore, if the desired DataFrame has 8 columns and a single index, then names may have 9 (or 8) elements.

Note that

parse_dates=[[4,5]],

is combining columns 4 and 5 into one column. So even though the raw data has 10 columns, what remains is 8 columns and an index. If you make names have 9 elements, the first element is used to name the index.

edited Dec 21 '13 at 15:08

answered Dec 21 '13 at 14:54

unutbu

842,883
184
1,785
1,677

I get `ValueError: Passed header names mismatches usecols` if I switch to either 8 or 9 column names. I tried dropping columns 4 and 5 from the `usecols` parameter and just using names for the 8 data columns. Same result as before, `IndexError` – Weston Dec 21 '13 at 16:10

IndexError when trying to read_table with pandas

2 Answers2

Workaround 1

Workaround 2