Format Time stamp in Python

I'm getting a formatting error..

My code:

date,bid,ask = np.loadtxt('EURUSDH1_1week1.csv', unpack=True, delimiter=',', converters={0:mdates.strpdate2num('%Y%m%d%H%M%S')})

error:

ValueError: time data '20180406,00:48.9,1.22394,1.22417,,"' does not match format '%Y%m%d%H%M%S'

The data in the csv file is shown as (four columns):

20180406    00:48.9 1.22394 1.22417
20180406    00:48.9 1.22394 1.22417
20180406    00:53.3 1.2239  1.22421
20180406    00:54.6 1.22391 0
20180406    01:51.8 0       1.2241
20180406    02:19.4 1.22396 1.22404
20180406    02:49.8 1.22391 1.22399

how do i remove the colons and period from the time stamp?

1 answer

  • answered 2018-04-14 14:32 jpp

    numpy is good for many things. But for mixed type data pandas is usually more convenient.

    This is one way you can convert your data to datetime using pandas.

    import pandas as pd
    from io import StringIO
    
    mystr = StringIO("""20180406    00:48.9 1.22394 1.22417
    20180406    00:48.9 1.22394 1.22417
    20180406    00:53.3 1.2239  1.22421
    20180406    00:54.6 1.22391 0
    20180406    01:51.8 0       1.2241
    20180406    02:19.4 1.22396 1.22404
    20180406    02:49.8 1.22391 1.22399""")
    
    # replace mystr with 'file.csv'
    df = pd.read_csv(mystr, delim_whitespace=True, header=None,
                     names=['Date', 'Time', 'Bid', 'Ask'])
    
    # create datetime  column
    df['DateTime'] = pd.to_datetime(df['Date'].map(str) + ' ' + df['Time'])
    
    print(df)
    
    #        Date     Time      Bid      Ask            DateTime
    # 0  20180406  00:48.9  1.22394  1.22417 2018-04-06 00:48:53
    # 1  20180406  00:48.9  1.22394  1.22417 2018-04-06 00:48:53
    # 2  20180406  00:53.3  1.22390  1.22421 2018-04-06 00:53:17
    # 3  20180406  00:54.6  1.22391  0.00000 2018-04-06 00:54:36
    # 4  20180406  01:51.8  0.00000  1.22410 2018-04-06 01:51:47
    # 5  20180406  02:19.4  1.22396  1.22404 2018-04-06 02:19:23
    # 6  20180406  02:49.8  1.22391  1.22399 2018-04-06 02:49:47
    

    Data types in result:

    print(df.dtypes)
    
    # Date                 int64
    # Time                object
    # Bid                float64
    # Ask                float64
    # DateTime    datetime64[ns]
    # dtype: object