Pandas seems to ignore first column name when reading tab-delimited data, gives KeyError
I am using pandas 0.12.0 in ipython3 on Ubuntu 13.10, in order to wrangle large tab-delimited datasets in txt files. Using read_table to create a DataFrame from the txt appears to work, and the first row is read as a header, but attempting to access the first column using its name as an index throws a KeyError. I don't understand why this happens, given that the column names all appear to have been read correctly, and every other column can be indexed in this way.
The data looks like this:
RECORDING_SESSION_LABEL LEFT_GAZE_X LEFT_GAZE_Y RIGHT_GAZE_X RIGHT_GAZE_Y VIDEO_FRAME_INDEX VIDEO_NAME
73_1 . . 395.1 302 . .
73_1 . . 395 301.9 . .
73_1 . . 394.9 301.7 . .
73_1 . . 394.8 301.5 . .
73_1 . . 394.6 301.3 . .
73_1 . . 394.7 300.9 . .
73_1 . . 394.9 301.3 . .
73_1 . . 395.2 302 1 1_1_just_act.avi
73_1 . . 395.3 302.3 1 1_1_just_act.avi
73_1 . . 395.4 301.9 1 1_1_just_act.avi
73_1 . . 395.7 301.5 1 1_1_just_act.avi
73_1 . . 395.9 301.5 1 1_1_just_act.avi
73_1 . . 396 301.5 1 1_1_just_act.avi
73_1 . . 395.9 301.5 1 1_1_just_act.avi
15_1 395.4 301.7 . . . .
The delimiter is definitely tabs, and there is no trailing or leading whitespace.
The error occurs with this minimal program:
import pandas as pd
samples = pd.read_table('~/datafile.txt')
print(samples['RECORDING_SESSION_LABEL'])
which gives the error:
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
<ipython-input-65-137d3c16b931> in <module>()
----> 1 print(samples['RECORDING_SESSION_LABEL'])
/usr/lib/python3/dist-packages/pandas/core/frame.py in __getitem__(self, key)
2001 # get column
2002 if self.columns.is_unique:
-> 2003 return self._get_item_cache(key)
2004
2005 # duplicate columns
/usr/lib/python3/dist-packages/pandas/core/generic.py in _get_item_cache(self, item)
665 return cache[item]
666 except Exception:
--> 667 values = self._data.get(item)
668 res = self._box_item_values(item, values)
669 cache[item] = res
/usr/lib/python3/dist-packages/pandas/core/internals.py in get(self, item)
1654 def get(self, item):
1655 if self.items.is_unique:
-> 1656 _, block = self._find_block(item)
1657 return block.get(item)
1658 else:
/usr/lib/python3/dist-packages/pandas/core/internals.py in _find_block(self, item)
1934
1935 def _find_block(self, item):
-> 1936 self._check_have(item)
1937 for i, block in enumerate(self.blocks):
1938 if item in block:
/usr/lib/python3/dist-packages/pandas/core/internals.py in _check_have(self, item)
1941 def _check_have(self, item):
1942 if item not in self.items:
-> 1943 raise KeyError('no item named %s' % com.pprint_thing(item))
1944
1945 def reindex_axis(self, new_axis, method=None, axis=0, copy=True):
KeyError: 'no item named RECORDING_SESSION_LABEL'
Simply doing print(samples) gives the expected output of printing the whole table, complete with the first column and its header. Trying to print any other column (ie; the exact same code, but with 'RECORDING_SESSION_LABEL' replaced with 'LEFT_GAZE_X') works as it should. Furthermore, the header seems to have been read in correctly, and pandas recognizes 'RECORDING_SESSION_LABEL' as a column name. This is evidenced by using the .info() method and viewing the .columns attribute of samples, after it's been read in:
>samples.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 28 entries, 0 to 27
Data columns (total 7 columns):
RECORDING_SESSION_LABEL 28 non-null values
LEFT_GAZE_X 28 non-null values
LEFT_GAZE_Y 28 non-null values
RIGHT_GAZE_X 28 non-null values
RIGHT_GAZE_Y 28 non-null values
VIDEO_FRAME_INDEX 28 non-null values
VIDEO_NAME 28 non-null values
dtypes: object(7)
>print(samples.columns)
Index(['RECORDING_SESSION_LABEL', 'LEFT_GAZE_X', 'LEFT_GAZE_Y', 'RIGHT_GAZE_X', 'RIGHT_GAZE_Y', 'VIDEO_FRAME_INDEX', 'VIDEO_NAME'], dtype=object)
Another error behaviour that I feel is related occurs when using ipython's tab completion, which allows me to access the columns of samples as if they were attributes. It works for every column except the first. ie; hitting the tab key with >samples.R only suggests samples.RIGHT_GAZE_X samples.RIGHT_GAZE_Y.
So why is it behaving normally when looking at the whole dataframe, but failing when trying to access the first column by its name, even though it appears to have correctly read in that name?
Answer
This seems to be (related to) a known issue, see GH #4793. Using 'utf-8-sig' as the encoding seems to work. Without it, we have:
>>> df = pd.read_table("datafile.txt")
>>> df.columns
Index([u'RECORDING_SESSION_LABEL', u'LEFT_GAZE_X', u'LEFT_GAZE_Y', u'RIGHT_GAZE_X', u'RIGHT_GAZE_Y', u'VIDEO_FRAME_INDEX', u'VIDEO_NAME'], dtype='object')
>>> df.columns[0]
'\xef\xbb\xbfRECORDING_SESSION_LABEL'
but with it, we have
>>> df = pd.read_table("datafile.txt", encoding="utf-8-sig")
>>> df.columns
Index([u'RECORDING_SESSION_LABEL', u'LEFT_GAZE_X', u'LEFT_GAZE_Y', u'RIGHT_GAZE_X', u'RIGHT_GAZE_Y', u'VIDEO_FRAME_INDEX', u'VIDEO_NAME'], dtype='object')
>>> df.columns[0]
u'RECORDING_SESSION_LABEL'
>>> df["RECORDING_SESSION_LABEL"].max()
u'73_1'
(Used Python 2 for the above, but the same happens with Python 3.)