Home How to convert csv date and time with milliseconds to datetime with milliseconds

Questions

How to convert csv date and time with milliseconds to datetime with milliseconds

November 26, 2022

I have a difficult time converting separated date and time columns from a csv file into a merged dataframe datetime column with milliseconds.

original data:

    Date    Time
0   2014/9/2    08:30:00.0
1   2014/9/2    08:37:39.21
2   2014/9/2    08:39:41.2
3   2014/9/2    08:41:23.9
4   2014/9/2    09:13:01.1
5   2014/9/2    09:43:02.49
6   2014/9/2    10:49:16.115
7   2014/9/2    10:58:46.39
8   2014/9/2    11:46:18.5
9   2014/9/2    12:03:43.0
10  2014/9/2    12:56:22.0
11  2014/9/2    13:13:01.0
12  2014/9/2    14:42:22.39
13  2014/9/2    14:50:00.74
14  2014/9/3    08:30:00.0
15  2014/9/3    08:30:11.57
16  2014/9/3    08:39:02.18
17  2014/9/3    08:44:31.74
18  2014/9/3    08:45:16.105
19  2014/9/3    08:47:52.57

concatenating date + time column

df['datetime'] = df.Date + str(' ') + df.Time 

0      2014/9/2 08:30:00.0
1     2014/9/2 08:37:39.21
2      2014/9/2 08:39:41.2
3      2014/9/2 08:41:23.9
4      2014/9/2 09:13:01.1
5     2014/9/2 09:43:02.49
6    2014/9/2 10:49:16.115
7     2014/9/2 10:58:46.39
8      2014/9/2 11:46:18.5
9      2014/9/2 12:03:43.0

Trying to parse the string to datetime object:

df['datetime'] = df['datetime'].apply(lambda x: datetime.strptime(x, '%Y/%m/%d %H:%M:%S.f%'))

fails:

ValueError: stray % in format '%Y/%m/%d %H:%M:%S.f%'

What is wrong with that and how to solve it?

>Solution :

The format code for microseconds is %f and not f% as per the documentation.

Try this :

df['datetime'] = df['datetime'].apply(lambda x: datetime.strptime(x, '%Y/%m/%d %H:%M:%S.%f'))

Or, in one shot :

(
    pd.read_csv("test.csv")
        .astype(str).agg(" ".join, axis=1)
        .to_frame("datetime")
        .apply(lambda _: pd.to_datetime(_, format= '%Y/%m/%d %H:%M:%S.%f'))
)

# Output :

                  datetime
0  2014-09-02 08:30:00.000
1  2014-09-02 08:37:39.210
2  2014-09-02 08:39:41.200
3  2014-09-02 08:41:23.900
4  2014-09-02 09:13:01.100
..                     ...
15 2014-09-03 08:30:11.570
16 2014-09-03 08:39:02.180
17 2014-09-03 08:44:31.740
18 2014-09-03 08:45:16.105
19 2014-09-03 08:47:52.570

[20 rows x 1 columns]

#dtypes
datetime    datetime64[ns]
dtype: object