无法在 pandas 数据框中按时间戳编制索引 [英] Can't index by timestamp in pandas dataframe

问题描述

我拿了一张有日期和一些值的Excel工作表，想将它们转换为pandas数据框，只选择某些日期之间的行。

I took an excel sheet which has dates and some values and want to convert them to pandas dataframe and select only rows which are between certain dates.

由于某种原因我无法按日期索引选择行

For some reason I cannot select a row by date index

Excel文件中的原始数据

Raw Data in Excel file

MCU                         
Timestamp   50D 10P1    10P2    10P3    10P6    10P9    10P12
12-Feb-15   25.17   5.88    5.92    5.98    6.18    6.23    6.33
11-Feb-15   25.9    6.05    6.09    6.15    6.28    6.31    6.39
10-Feb-15   26.38   5.94    6.05    6.15    6.33    6.39    6.46

代码

xls = pd.ExcelFile('e:/Data.xlsx')
vols = xls.parse(asset.upper()+'VOL',header=1)
vols.set_index('Timestamp',inplace=True)

set_index之前的数据

Data before set_index

      Timestamp    50D  10P1  10P2  10P3  10P6  10P9  10P12  25P1  25P2  \
0    2015-02-12  25.17  5.88  5.92  5.98  6.18  6.23   6.33  2.98  3.08   
1    2015-02-11  25.90  6.05  6.09  6.15  6.28  6.31   6.39  3.12  3.17   
2    2015-02-10  26.38  5.94  6.05  6.15  6.33  6.39   6.46  3.01  3.16  

set_index之后的数据

Data after set_index

              50D  10P1  10P2  10P3  10P6  10P9  10P12  25P1  25P2  25P3  \
Timestamp                                                                  
2015-02-12  25.17  5.88  5.92  5.98  6.18  6.23   6.33  2.98  3.08  3.21   
2015-02-11  25.90  6.05  6.09  6.15  6.28  6.31   6.39  3.12  3.17  3.32   
2015-02-10  26.38  5.94  6.05  6.15  6.33  6.39   6.46  3.01  3.16  3.31  

输出

>>> vols.index
<class 'pandas.tseries.index.DatetimeIndex'>
[2015-02-12, ..., NaT]
Length: 1478, Freq: None, Timezone: None

>>> vols[date(2015,2,12)]
*** KeyError: datetime.date(2015, 2, 12)

我希望这不会失败，而且我应该能够选择日期范围。尝试了很多组合但没有得到。

I would expect this not to fail, and also I should be able to select a range of dates. Tried so many combinations but not getting it.

推荐答案

使用 datetime.date 实例尝试检索索引将不起作用，您只需要日期的字符串表示即可，例如'2015-02-12'或'2015/02/14'。

Using a datetime.date instance to try to retrieve the index won't work, you just need a string representation of the date, e.g. '2015-02-12' or '2015/02/14'.

其次， vols [date（2015,2,12）] 实际上是在DataFrame的列标题中查找，而不是索引。您可以使用 loc 来获取行索引标签。例如，您可以编写 vols.loc ['2015-02-12']

Secondly, vols[date(2015,2,12)] is actually looking in your DataFrame's column headings, not the index. You can use loc to fetch row index labels instead. For example you could write vols.loc['2015-02-12']

这篇关于无法在 pandas 数据框中按时间戳编制索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！