python - 在 Pandas 中将索引从整数更改为日期时出现问题

Question

我在将 pandas DataFrame 索引从整数更改为日期时间时遇到问题。我想这样做，以便我可以调用 reindex 并填写表中列出的日期之间的日期。请注意，我目前必须使用 pandas 0.7.3，因为我也在使用 qstk，而 qstk 依赖于 pandas 0.7.3

首先，这是我的布局:

(Pdb) df
    AAPL  GOOG   IBM   XOM                 date
1      0     0  4000     0  2011-01-13 16:00:00
2      0  1000  4000     0  2011-01-26 16:00:00
3      0  1000  4000     0  2011-02-02 16:00:00
4      0  1000  4000  4000  2011-02-10 16:00:00
6      0     0  1800  4000  2011-03-03 16:00:00
7      0     0  3300  4000  2011-06-03 16:00:00
8      0     0     0  4000  2011-05-03 16:00:00
9   1200     0     0  4000  2011-06-10 16:00:00
11  1200     0     0  4000  2011-08-01 16:00:00
12     0     0     0  4000  2011-12-20 16:00:00

(Pdb) type(df['date'])
<class 'pandas.core.series.Series'>

(Pdb) df2 = DataFrame(index=df['date'])
(Pdb) df2
Empty DataFrame
Columns: array([], dtype=object)
Index: array([2011-01-13 16:00:00, 2011-01-26 16:00:00, 2011-02-02 16:00:00,
       2011-02-10 16:00:00, 2011-03-03 16:00:00, 2011-06-03 16:00:00,
       2011-05-03 16:00:00, 2011-06-10 16:00:00, 2011-08-01 16:00:00,
       2011-12-20 16:00:00], dtype=object)

(Pdb) df2.merge(df,left_index=True,right_on='date')
    AAPL  GOOG   IBM   XOM                 date
1      0     0  4000     0  2011-01-13 16:00:00
2      0  1000  4000     0  2011-01-26 16:00:00
3      0  1000  4000     0  2011-02-02 16:00:00
4      0  1000  4000  4000  2011-02-10 16:00:00
6      0     0  1800  4000  2011-03-03 16:00:00
8      0     0     0  4000  2011-05-03 16:00:00
7      0     0  3300  4000  2011-06-03 16:00:00
9   1200     0     0  4000  2011-06-10 16:00:00
11  1200     0     0  4000  2011-08-01 16:00:00
12     0     0     0  4000  2011-12-20 16:00:00

我尝试了多种方法来获取日期时间索引:

1.) 使用带有日期时间值列表的 reindex() 方法。这会创建一个日期时间索引，但随后会为 DataFrame 中的数据填充 NaN。我猜这是因为原始值与整数索引相关联，并且重新索引到日期时间会尝试用默认值填充新索引(如果未指示填充方法，则为 NaNs)。因此:

(Pdb) df.reindex(index=df['date'])
                     AAPL  GOOG  IBM  XOM date
date                                          
2011-01-13 16:00:00   NaN   NaN  NaN  NaN  NaN
2011-01-26 16:00:00   NaN   NaN  NaN  NaN  NaN
2011-02-02 16:00:00   NaN   NaN  NaN  NaN  NaN
2011-02-10 16:00:00   NaN   NaN  NaN  NaN  NaN
2011-03-03 16:00:00   NaN   NaN  NaN  NaN  NaN
2011-06-03 16:00:00   NaN   NaN  NaN  NaN  NaN
2011-05-03 16:00:00   NaN   NaN  NaN  NaN  NaN
2011-06-10 16:00:00   NaN   NaN  NaN  NaN  NaN
2011-08-01 16:00:00   NaN   NaN  NaN  NaN  NaN
2011-12-20 16:00:00   NaN   NaN  NaN  NaN  NaN

2.) 将 DataFrame.merge 与我的原始 df 和第二个数据框 df2 结合使用，这基本上只是一个日期时间索引，没有其他任何内容。所以我最终做了类似的事情:

(pdb) df2.merge(df,left_index=True,right_on='date')
    AAPL  GOOG   IBM   XOM                 date
1      0     0  4000     0  2011-01-13 16:00:00
2      0  1000  4000     0  2011-01-26 16:00:00
3      0  1000  4000     0  2011-02-02 16:00:00
4      0  1000  4000  4000  2011-02-10 16:00:00
6      0     0  1800  4000  2011-03-03 16:00:00
8      0     0     0  4000  2011-05-03 16:00:00
7      0     0  3300  4000  2011-06-03 16:00:00
9   1200     0     0  4000  2011-06-10 16:00:00
11  1200     0     0  4000  2011-08-01 16:00:00

(反之亦然)。但我总是以这种带有整数索引的东西结束。

3.) 从一个带有日期时间索引(从 df 的“日期”字段创建)和一堆空列的空 DataFrame 开始。然后我尝试通过设置相同的列来分配每一列名称等于 df 中的列:

(Pdb) df2['GOOG']=0
(Pdb) df2
                     GOOG
date                     
2011-01-13 16:00:00     0
2011-01-26 16:00:00     0
2011-02-02 16:00:00     0
2011-02-10 16:00:00     0
2011-03-03 16:00:00     0
2011-06-03 16:00:00     0
2011-05-03 16:00:00     0
2011-06-10 16:00:00     0
2011-08-01 16:00:00     0
2011-12-20 16:00:00     0
(Pdb) df2['GOOG'] = df['GOOG']
(Pdb) df2
                     GOOG
date                     
2011-01-13 16:00:00   NaN
2011-01-26 16:00:00   NaN
2011-02-02 16:00:00   NaN
2011-02-10 16:00:00   NaN
2011-03-03 16:00:00   NaN
2011-06-03 16:00:00   NaN
2011-05-03 16:00:00   NaN
2011-06-10 16:00:00   NaN
2011-08-01 16:00:00   NaN
2011-12-20 16:00:00   NaN

那么，在 pandas 0.7.3 中，我如何使用日期时间索引而不是整数索引重新创建 df？我错过了什么？

Answer 1

我想你在找set_index :

In [11]: df.set_index('date')
Out[11]: 
                     AAPL  GOOG   IBM   XOM
date                                  
2011-01-13 16:00:00     0     0  4000     0
2011-01-26 16:00:00     0  1000  4000     0
2011-02-02 16:00:00     0  1000  4000     0
2011-02-10 16:00:00     0  1000  4000  4000
2011-03-03 16:00:00     0     0  1800  4000
2011-06-03 16:00:00     0     0  3300  4000
2011-05-03 16:00:00     0     0     0  4000
2011-06-10 16:00:00  1200     0     0  4000
2011-08-01 16:00:00  1200     0     0  4000
2011-12-20 16:00:00     0     0     0  4000