Pandas read_csv:正确解析时间字段

Question

我有以下原始数据

TranID,TranDate,TranTime,TranAmt
A123456,20160427,02:18,9999.53
B123457,20160426,02:48,26070.33
C123458,20160425,03:18,13779.56
A123459,20160424,03:18,18157.26
B123460,20160423,04:18,215868.15
C123461,20160422,04:18,23695.25
A123462,20160421,05:18,57
B123463,20160420,05:18,64594.24
C123464,20160419,06:18,47890.91
A123465,20160427,06:18,14119.74
B123466,20160426,07:18,2649.6
C123467,20160425,07:18,16757.38
A123468,20160424,08:18,8864.78
B123469,20160423,08:18,26254.69
C123470,20160422,09:18,13206.98
A123471,20160421,09:18,15872.45
B123472,20160420,10:18,197621.18
C123473,20160419,10:18,21048.72

我试着用pd read_csv导入原始数据

尝试1

^{pr2}$

输出1

TranID              object
TranDate    datetime64[ns]
TranTime            object
TranAmt            float64
dtype: object
Out[12]:
TranID  TranDate    TranTime    TranAmt
0   A123456 2016-04-27  02:18   9999.53
1   B123457 2016-04-26  02:48   26070.33
2   C123458 2016-04-25  03:18   13779.56
3   A123459 2016-04-24  03:18   18157.26
4   B123460 2016-04-23  04:18   215868.15

尝试2

import numpy as np
import pandas as pd

df = pd.read_csv('MyTest.csv', sep=',', header=0, parse_dates=['TranDate', 'TranTime'],
                 usecols=['TranID','TranDate','TranTime','TranAmt'],
                 engine='python')
print(df.dtypes)
df[:5]

输出2

TranID              object
TranDate    datetime64[ns]
TranTime    datetime64[ns]
TranAmt            float64
dtype: object
Out[13]:
TranID  TranDate    TranTime    TranAmt
0   A123456 2016-04-27  2016-04-27 02:18:00 9999.53
1   B123457 2016-04-26  2016-04-27 02:48:00 26070.33
2   C123458 2016-04-25  2016-04-27 03:18:00 13779.56
3   A123459 2016-04-24  2016-04-27 03:18:00 18157.26
4   B123460 2016-04-23  2016-04-27 04:18:00 215868.15

我对TranTime专栏感到困惑。在Try1中，它显示正确，但dtype是object。在Try2中，pd将当前日期添加到时间中，数据类型为datetime。在

我希望将这个transtime列视为Time，并使用pd的groupby或pivot_表执行聚合。 如果我使用Try1方法，对象数据类型是否影响聚合？ 如果我使用Try2方法，是否需要去掉日期部分才能使用时间部分？在

我精通SAS，SAS有日期、时间和日期时间信息，并且基础数据类型只是数字的格式。因此，我对Python的object和datetime数据类型感到困惑。在

谢谢， 说客

Answer 1

1. No aggregation affection, but you will lose the time part.
2. No, mostly you can access the time part by .dt accessor.

import pandas as pd

df = pd.read_csv('MyTest.csv', parse_dates=[['TranDate', 'TranTime']])
print df

TranDate_TranTime   TranID    TranAmt
0  2016-04-27 02:18:00  A123456    9999.53
1  2016-04-26 02:48:00  B123457   26070.33
2  2016-04-25 03:18:00  C123458   13779.56
3  2016-04-24 03:18:00  A123459   18157.26
4  2016-04-23 04:18:00  B123460  215868.15
5  2016-04-22 04:18:00  C123461   23695.25
6  2016-04-21 05:18:00  A123462      57.00
7  2016-04-20 05:18:00  B123463   64594.24
8  2016-04-19 06:18:00  C123464   47890.91
9  2016-04-27 06:18:00  A123465   14119.74
10 2016-04-26 07:18:00  B123466    2649.60
11 2016-04-25 07:18:00  C123467   16757.38
12 2016-04-24 08:18:00  A123468    8864.78
13 2016-04-23 08:18:00  B123469   26254.69
14 2016-04-22 09:18:00  C123470   13206.98
15 2016-04-21 09:18:00  A123471   15872.45
16 2016-04-20 10:18:00  B123472  197621.18
17 2016-04-19 10:18:00  C123473   21048.72

使用嵌套括号parse_dates=[[]]尽可能将日期/时间作为一列进行解析和管理。在

^{pr2}$

得到你想要的东西。在

您仍然可以在resampling之后进行groupby，如下所示。在

df2 = df.set_index('TranDate_TranTime').resample('60s').sum().dropna()
print df2

                       TranAmt
TranDate_TranTime             
2016-04-19 06:18:00   47890.91
2016-04-19 10:18:00   21048.72
2016-04-20 05:18:00   64594.24
2016-04-20 10:18:00  197621.18
2016-04-21 05:18:00      57.00
2016-04-21 09:18:00   15872.45
2016-04-22 04:18:00   23695.25
2016-04-22 09:18:00   13206.98
2016-04-23 04:18:00  215868.15
2016-04-23 08:18:00   26254.69
2016-04-24 03:18:00   18157.26
2016-04-24 08:18:00    8864.78
2016-04-25 03:18:00   13779.56
2016-04-25 07:18:00   16757.38
2016-04-26 02:48:00   26070.33
2016-04-26 07:18:00    2649.60
2016-04-27 02:18:00    9999.53
2016-04-27 06:18:00   14119.74

print df2.groupby(df2.index.day).sum()

      TranAmt
19   68939.63
20  262215.42
21   15929.45
22   36902.23
23  242122.84
24   27022.04
25   30536.94
26   28719.93
27   24119.27