Q:

Pandas read_csv: parsing time field correctly

I have the following raw data,
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

and I tried importing the raw data using pd read_csv,
Try1
import numpy as np
import pandas as pd

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

Output1
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

Try2
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]

Output2
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

My confusions are with the TranTime column.  In Try1, it is displayed correctly but dtype is object.  In Try2, pd added current date to the time and the dtype is datetime.  
I want this TranTime column to be treated as Time and want to perform aggregations using pd's groupby or pivot_table.
If I use Try1 method, does the object dtype affect my aggregations?
If I use Try2 method, do I need to strip the Date part out in order to use the Time part?
I am proficient in SAS where SAS has date, time and datetime informats and formats where the underlying data type is just numeric.  Hence my confusion with Python's object and datetime dtypes.
Thanks,
Lobbie

A:

In Python, datetimes are generally represented as datetime.datetime objects.  These are not very efficient, which is why Pandas uses Timestamps, which are numeric.
To read the data (note the double brackets around the parse_dates arguments):
df = pd.read_csv(filename, parse_dates=[['TranDate', 'TranTime']])

>>> df.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 18 entries, 0 to 17
Data columns (total 3 columns):
TranDate_TranTime    18 non-null datetime64[ns]
TranID               18 non-null object
TranAmt              18 non-null float64
dtypes: datetime64[ns](1), float64(1), object(1)

>>> df.head()
    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

The date and time columns have been joined to just one columns.  Once you have this timestamp, it is easy to access its attributes using the dt accessor, e.g.
>>> df.groupby(df.TranDate_TranTime.dt.hour).TranAmt.sum().head()
TranDate_TranTime
2     36069.86
3     31936.82
4    239563.40
5     64651.24
6     62010.65
Name: TranAmt, dtype: float64

>>> df.groupby(df.TranDate_TranTime.dt.day).TranAmt.sum().head()
TranDate_TranTime
19     68939.63
20    262215.42
21     15929.45
22     36902.23
23    242122.84
Name: TranAmt, dtype: float64

Refer to the Pandas docs for more information regarding Pandas date functionality.

