Read custom formatted time and date with numpy

Question

I am trying to load time series data from some files. Data have this format

04/02/2015 19:07:53.951,3195,1751,-44,-25

I use this code to download the whole file as a numpy object.

 content = np.loadtxt(filename, dtype={'names': ('timestamp', 'tick', 'ch', 'NodeI', 'Base'),
                                      'formats': ('datetime64[us]', 'i4', 'i4', 'i4', 'i4')}, delimiter=',', skiprows=27)

but I got an error in datetime format

ValueError: Error parsing datetime string "04/02/2015 19:07:53.951" at position 2

is there an easy way to determine the date and time format i'm reading? There are files with a lot of data, so I try not to walk the file more than once.

Answer 1

Use the argument convertersto apply the converter function to the data in the first column:

import datetime

def parsetime(v): 
    return np.datetime64(
        datetime.datetime.strptime(v, '%d/%m/%Y %H:%M:%S.%f')
    )

content = np.loadtxt(
    filename, 
    dtype={
        'names': ('timestamp', 'tick', 'ch', 'NodeI', 'Base'),
        'formats': ('datetime64[us]', 'i4', 'i4', 'i4', 'i4')
    }, 
    delimiter=',', 
    skiprows=27,
    converters={0: parsetime},
)

, D/M/Y, , M/D/Y.

Answer 2

pandas read_csv, parse_dates, infer_datetime_format, datetime:

import pandas as pd
a=pd.read_csv('nu.txt',parse_dates=[0],infer_datetime_format=True,sep=',',header=None)

a.iloc[:,0]



0   2015-04-02 19:07:53.951
1   2015-04-02 19:07:53.951
2   2015-04-02 19:07:53.951
3   2015-04-02 19:07:53.951
Name: 0, dtype: datetime64[ns]
# assumes file with four identical rows and no header

, numpy:

b=np.array(a)
array([[Timestamp('2015-04-02 19:07:53.951000'), 3195L, 1751L, -44L, -25L],
       [Timestamp('2015-04-02 19:07:53.951000'), 3195L, 1751L, -44L, -25L],
       [Timestamp('2015-04-02 19:07:53.951000'), 3195L, 1751L, -44L, -25L],
       [Timestamp('2015-04-02 19:07:53.951000'), 3195L, 1751L, -44L, -25L]], dtype=object)