ValueError: Index DATE invalid with pandas.read_csv on header row

Question

Trying to create a dictionary with the key's being the first row of the csv file and the value's being a dictionary of {first column: corresponding column to row}:

import pandas as pd

df = pd.read_csv('~/StockMachine/data_stocks.csv', index_col=['DATE'], sep=',\s+')

data = df.to_dict()

print(data)

However, I get this error "ValueError: Index DATE invalid".

Traceback:

  File "/Users/cs/StockMachine/stockmachine.py", line 4, in <module>
    df = pd.read_csv('~/StockMachine/data_stocks.csv', index_col=['DATE'], sep=',\s+')
  File "/Users/cs/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py", line 678, in parser_f
    return _read(filepath_or_buffer, kwds)
  File "/Users/cs/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py", line 446, in _read
    data = parser.read(nrows)
  File "/Users/cs/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py", line 1036, in read
    ret = self._engine.read(nrows)
  File "/Users/cs/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py", line 2273, in read
    index, columns = self._make_index(data, alldata, columns, indexnamerow)
  File "/Users/cs/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py", line 1425, in _make_index
    index = self._get_simple_index(alldata, columns)
  File "/Users/cs/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py", line 1457, in _get_simple_index
    i = ix(idx)
  File "/Users/cs/anaconda3/lib/python3.6/site-packages/pandas/io/parsers.py", line 1452, in ix
    raise ValueError('Index %s invalid' % col)

data_stocks.csv:

Answer 1

Similiar thing happened to me and in my case some readings of ['DATE'] were strings with empty spaces inside. Maybe if you would do something like:

import pandas as pd

df = pd.read_csv('~/StockMachine/data_stocks.csv', sep=',\s+')

df['DATE'] = df['DATE'].apply(lambda x: str(x.strip())).astype(str)

df.set_index('DATE', inplace=True)

print(df.head())

Answer 2

I had same issue then realized it is that the selected column for the Col_Index is not part of the selected Header=1 row specified in my script