Pandas SetIndex with DatetimeIndex

Question

I have a csv file with the following

Symbol, Date, Unix_Tick, OpenPrice, HighPrice, LowPrice, ClosePrice, volume,
AAPL, 2021-01-04 09:00:00, 1609750800, 133.31, 133.49, 133.02, 133.49, 25000
AAPL, 2021-01-04 09:01:00, 1609750860, 133.49, 133.49, 133.49, 133.49, 700
AAPL, 2021-01-04 09:02:00, 1609750920, 133.6, 133.6, 133.5, 133.5, 500

So I attempt to create a pandas index using Date like this

import pandas as pd
import numpy as np

df = pd.read_csv(csvFile)
df = df.set_index(pd.DatetimeIndex(df["Date"]))

I get KeyError: 'Date'

score 0 · Answer 1 · answered May 28 '21 at 18:31

The problem is most probably in space after ,. You can try load the data with custom sep= parameter:

df = pd.read_csv("a1.txt", sep=r",\s+", engine="python")
df = df.set_index(pd.DatetimeIndex(df["Date"]))
print(df)

Prints:

                    Symbol                 Date   Unix_Tick  OpenPrice  HighPrice  LowPrice  ClosePrice  volume,
Date                                                                                                            
2021-01-04 09:00:00   AAPL  2021-01-04 09:00:00  1609750800     133.31     133.49    133.02      133.49    25000
2021-01-04 09:01:00   AAPL  2021-01-04 09:01:00  1609750860     133.49     133.49    133.49      133.49      700
2021-01-04 09:02:00   AAPL  2021-01-04 09:02:00  1609750920     133.60     133.60    133.50      133.50      500

score 0 · Accepted Answer · answered May 28 '21 at 18:32

It's because the file isn't strictly a comma-separated one, but it is comma plus space-separated.

You can either strip the column names to remove spaces:

df = pd.read_csv(csvFile)

df.columns = df.columns.str.strip()

df = df.set_index(pd.DatetimeIndex(df["Date"]))

or read the CSV file with separator ", ":

df = pd.read_csv(csvFile, sep=", ")

df = df.set_index(pd.DatetimeIndex(df["Date"]))

Pandas SetIndex with DatetimeIndex

2 Answers2