pandas.read_sql及解释按周指定的日期

Question

我有一个SQL表格，里面的数据格式是这样的，我想把它读取出来并转换成pandas的时间序列。

y (year)  w (week)   d (some data)
2009      1          10
2009      2          15
...

有没有什么好的方法可以做到这一点呢？

我知道read_sql()这个函数有一个叫parse_dates的参数，另外也可以手动用DatetimeIndex来设置索引。不过，我不太明白怎么处理周的数据。我尝试过以下的方法。谢谢。

# gives NaT for year & week:
df = pd.read_sql("SELECT y, w, d FROM t",
                 db, parse_dates={"y":"%Y", "w":"%U"})

# gives wrong dates for yw - e.g. all 2009-01-01:
df = pd.read_sql("SELECT CONCAT(y,'/',w) as yw, d FROM t",
                 db, parse_dates={"yw": "%Y/%U"})

# throws DateParseError exception:
df = pd.read_sql("SELECT CONCAT(y,'W',w) as yw, d FROM t",
                 db)
df.index = pd.DatetimeIndex(df.yw)

Answer 1

其实，我觉得最简单优雅的方法是在SQL里进行转换：

sql = "SELECT DATE_ADD(MAKEDATE(y, 1), INTERVAL w WEEK) as date, d FROM test.t"
df = pd.read_sql(sql, engine)
print(df)

这样可以得到

         date   d
0  2009-01-08  10
1  2009-01-15  15

---

如果在Python中做同样的事情，就需要写更多的代码，因为据我所知，Pandas并没有现成的功能可以直接把年份和周数转换成日期。当然，你可以使用循环和datetime模块，把这些数字一个一个转换成datetime.datetime对象。

更快的方法是使用NumPy的datetime64和timedelta64数据类型，利用NumPy数组来进行日期计算：

sql = "SELECT y, w, d FROM t"
df = pd.read_sql(sql, engine)

date = (df['y'].astype('<i8')-1970).view('<M8[Y]')
delta = (df['w'].astype('<i8')*7).view('<m8[D]')
df['date'] = date+delta
df = df[['date', 'd']]
print(df)

这样可以得到

        date   d
0 2009-01-08  10
1 2009-01-15  15

---

补充一下，基于Hadi在评论中的回答，也可以在SQL中使用MySQL的STR_TO_DATE函数来计算日期：

sql = "SELECT STR_TO_DATE(CONCAT(y,':',w,':1'), '%x:%v:%w') as date, d FROM test.t"    
df = pd.read_sql(sql, engine)

这样可以得到

         date   d
0  2008-12-29  10
1  2009-01-05  15

需要注意的是，根据MySQL文档，%v的意思是

Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x

而在同一页面的后面，模式3的意思是

Mode    First day of week   Range   Week 1 is the first week …
3       Monday              1-53    with 4 or more days this year

对于值为“今年有4天或更多”的模式，周数是根据ISO 8601:1988来编号的。

所以如果你想让周数和ISO 8601:1988保持一致，那么在使用STR_TO_DATE时应该用%v（或者%u），而不是%V（或者%U）。

注意，我上面第一个回答并没有把周数解释为ISO 8601的周数；它只是简单地从1月1日开始计算每周的7天。