python - 值错误 : Cannot cast DatetimeIndex to dtype datetime64[us]

Question

我正在尝试为 S&P 500 ETF 创建 30 分钟数据的 PostgreSQL 表(spy30new，用于测试新插入的数据)来自具有 15 分钟数据(all15)的几只股票的表格。 all15 在“dt”(时间戳)和“instr”(股票代码)上有一个索引。我希望 spy30new 在“dt”上有一个索引。

import numpy as np
import pandas as pd
from datetime import datetime, date, time, timedelta
from dateutil import parser
from sqlalchemy import create_engine

# Query all15
engine = create_engine('postgresql://user:passwd@localhost:5432/stocks')
new15Df = (pd.read_sql_query("SELECT dt, o, h, l, c, v FROM all15 WHERE (instr = 'SPY') AND (date(dt) BETWEEN '2016-06-27' AND '2016-07-15');", engine)).sort_values('dt')
# Correct for Time Zone.
new15Df['dt'] = (new15Df['dt'].copy()).apply(lambda d: d + timedelta(hours=-4))

# spy0030Df contains the 15-minute data at 00 & 30 minute time points
# spy1545Df contains the 15-minute data at 15 & 45 minute time points
spy0030Df = (new15Df[new15Df['dt'].apply(lambda d: d.minute % 30) == 0]).reset_index(drop=True)
spy1545Df = (new15Df[new15Df['dt'].apply(lambda d: d.minute % 30) == 15]).reset_index(drop=True)

high = pd.concat([spy1545Df['h'], spy0030Df['h']], axis=1).max(axis=1)
low = pd.concat([spy1545Df['l'], spy0030Df['l']], axis=1).min(axis=1)
volume = spy1545Df['v'] + spy0030Df['v']

# spy30Df assembled and pushed to PostgreSQL as table spy30new
spy30Df = pd.concat([spy0030Df['dt'], spy1545Df['o'], high, low, spy0030Df['c'], volume], ignore_index = True, axis=1)
spy30Df.columns = ['d', 'o', 'h', 'l', 'c', 'v']
spy30Df.set_index(['dt'], inplace=True)
spy30Df.to_sql('spy30new', engine, if_exists='append', index_label='dt')

这给出了错误“ValueError: Cannot cast DatetimeIndex to dtype datetime64[us]”
到目前为止我尝试了什么(我已经使用 Pandas 成功地将 CSV 文件推送到 PG。但这里的源是一个 PG 数据库):

1. 不在 'dt' 上放置索引

   spy30Df.set_index(['dt'], inplace=True)  # Remove this line
   spy30Df.to_sql('spy30new', engine, if_exists='append')  # Delete the index_label option
   

2. 使用 to_pydatetime() 将 'dt' 从类型 pandas.tslib.Timestamp 转换为 datetime.datetime(如果 psycopg2 可以与 python dt 一起工作，但是不是 Pandas 时间戳)

   u = (spy0030Df['dt']).tolist()
   timesAsPyDt = np.asarray(map((lambda d: d.to_pydatetime()), u))
   spy30Df = pd.concat([spy1545Df['o'], high, low, spy0030Df['c'], volume], ignore_index = True, axis=1)
   newArray = np.c_[timesAsPyDt, spy30Df.values]
   colNames = ['dt', 'o', 'h', 'l', 'c', 'v']
   newDf = pd.DataFrame(newArray, columns=colNames)
   newDf.set_index(['dt'], inplace=True)
   newDf.to_sql('spy30new', engine, if_exists='append', index_label='dt')
   

3. 使用 datetime.utcfromtimestamp()

   timesAsDt = (spy0030Df['dt']).apply(lambda d: datetime.utcfromtimestamp(d.tolist()/1e9))
   

4. 使用 pd.to_datetime()

   timesAsDt = pd.to_datetime(spy0030Df['dt'])
   

Answer 1

在每个元素上使用 pd.to_datetime() 都有效。选项 4 不起作用，将 pd.to_datetime() 应用于整个系列。也许 Postgres 驱动程序理解 python datetime，但不理解 pandas 和 numpy 中的 datetime64。选项 4 产生了正确的输出，但是在将 DF 发送到 Postgres 时我得到了 ValueError(见标题)

timesAsPyDt = (spy0030Df['dt']).apply(lambda d: pd.to_datetime(str(d)))