Python Pandas 0.13:从具有特定属性的dataframe中规范化日期和选择时间

Question

我使用的是Anaconda自带的Pandas库，使用python 2.7.9。

我的问题是双重的。

我有几个数据集有日期和时间字段，但不幸的是，创建它们的仪器没有一致地标记日期，因此它们都是DD/MM/YYYY格式，但仪器似乎随机地在大约一半的日期中留下了月和日的前导零。Pandas在正确读取它们(从excel文件)时遇到了麻烦，并且由于数据集从4月10日开始，它一直从2014-10-04开始，中间有未转换的日期(当一天超过12时)，然后在考虑到输入日期有意义时再次开始将它们读取为YYYY-MM-DD。是否有一种方法可以强制Pandas正确读取这些日期，并将日期和时间字段连接起来并将其用作索引，而不是分配数字?我试图为Date字段创建并插入一个转换器函数，以便正确地格式化日期，但由于某种原因，它在 Pandas已经错误地读取日期之后应用了，因此格式不正确。

因为我想索引这些数据作为一个时间序列，我所做的只是简单地创建一个日期/时间范围，然后设置为DataFrame的索引，这工作得很好。除了，对于这个数据集，有两天的数据，仪器显然开始以每分钟一个样本的频率采集数据，而不是每10分钟一个样本。是否有一种方法可以分配索引并强制它只保留匹配的记录?如果做不到这一点，我一直试图只在分钟以0结尾的时候查询DataFrame，或者专门删除这些记录，但根本没有成功。我真不知道该怎么办。

这是一个csv文件的链接，其中包含示例日期。

在其他事情中，我试过:

In[168]: ddata = ddata[str(ddata[' Time'])[:5].endswith('0')]
Traceback (most recent call last):
  File "C:UsersTomAnacondalibsite-packagesIPythoncoreinteractiveshell.py", line 2883, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-156-098b3e02871f>", line 1, in <module>
    ddata = ddata[str(ddata[' Time'])[:5].endswith('0')]
  File "C:UsersTomAnacondalibsite-packagespandascoreframe.py", line 1678, in __getitem__
    return self._getitem_column(key)
  File "C:UsersTomAnacondalibsite-packagespandascoreframe.py", line 1685, in _getitem_column
    return self._get_item_cache(key)
  File "C:UsersTomAnacondalibsite-packagespandascoregeneric.py", line 1052, in _get_item_cache
    values = self._data.get(item)
  File "C:UsersTomAnacondalibsite-packagespandascoreinternals.py", line 2565, in get
    loc = self.items.get_loc(item)
  File "C:UsersTomAnacondalibsite-packagespandascoreindex.py", line 1181, in get_loc
    return self._engine.get_loc(_values_from_object(key))
  File "index.pyx", line 129, in pandas.index.IndexEngine.get_loc (pandasindex.c:3656)
  File "index.pyx", line 149, in pandas.index.IndexEngine.get_loc (pandasindex.c:3534)
  File "hashtable.pyx", line 696, in pandas.hashtable.PyObjectHashTable.get_item (pandashashtable.c:11911)
  File "hashtable.pyx", line 704, in pandas.hashtable.PyObjectHashTable.get_item (pandashashtable.c:11864)
KeyError: False
In[169]: ddata1 = ddata.query('Time[4] == 0')
Traceback (most recent call last):
  File "C:UsersTomAnacondalibsite-packagesIPythoncoreinteractiveshell.py", line 2883, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-166-48cd98cf78bd>", line 1, in <module>
    ddata1 = ddata.query('Time[4] == 0')
  File "C:UsersTomAnacondalibsite-packagespandascoreframe.py", line 1816, in query
    res = self.eval(expr, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascoreframe.py", line 1868, in eval
    return _eval(expr, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascomputationeval.py", line 235, in eval
    ret = eng_inst.evaluate()
  File "C:UsersTomAnacondalibsite-packagespandascomputationengines.py", line 69, in evaluate
    self.result_type, self.aligned_axes = _align(self.expr.terms)
  File "C:UsersTomAnacondalibsite-packagespandascomputationalign.py", line 136, in _align
    typ, axes = _align_core(terms)
  File "C:UsersTomAnacondalibsite-packagespandascomputationalign.py", line 54, in wrapper
    return _result_type_many(*term_values), None
  File "C:UsersTomAnacondalibsite-packagespandascomputationcommon.py", line 17, in _result_type_many
    return np.result_type(*arrays_and_dtypes)
TypeError: data type not understood
In[170]: ddata1 = ddata.query('str(Time)[4] == 0')
Traceback (most recent call last):
  File "C:UsersTomAnacondalibsite-packagesIPythoncoreinteractiveshell.py", line 2883, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-167-452d91f45daf>", line 1, in <module>
    ddata1 = ddata.query('str(Time)[4] == 0')
  File "C:UsersTomAnacondalibsite-packagespandascoreframe.py", line 1816, in query
    res = self.eval(expr, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascoreframe.py", line 1868, in eval
    return _eval(expr, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascomputationeval.py", line 230, in eval
    truediv=truediv)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 635, in __init__
    self.terms = self.parse()
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 652, in parse
    return self._visitor.visit(self.expr)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 314, in visit
    return visitor(node, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 320, in visit_Module
    return self.visit(expr, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 314, in visit
    return visitor(node, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 323, in visit_Expr
    return self.visit(node.value, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 314, in visit
    return visitor(node, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 560, in visit_Compare
    return self.visit(binop)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 314, in visit
    return visitor(node, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 404, in visit_BinOp
    op, op_class, left, right = self._possibly_transform_eq_ne(node)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 355, in _possibly_transform_eq_ne
    left = self.visit(node.left, side='left')
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 314, in visit
    return visitor(node, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 440, in visit_Subscript
    value = self.visit(node.value)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 314, in visit
    return visitor(node, **kwargs)
  File "C:UsersTomAnacondalibsite-packagespandascomputationexpr.py", line 205, in f
    "implemented".format(node_name))
NotImplementedError: 'Call' nodes are not implemented

Answer 1

我在你链接到的csv上尝试了这个，它似乎对我有效:

df.Date = pd.datetools.to_datetime(df.Date)
df.Date.head()
Out[972]: 
0   2014-05-31
1   2014-05-31
2   2014-05-31
3   2014-05-31
4   2014-05-31
Name: Date, dtype: datetime64[ns]

对于你问题的第二部分，你可以这样分割你的数据框架:

df[df.Time.map(lambda x: x.minute % 10 == 0)]