I have two columns of data in a DataFrame containing a date and a time. Both start as strings. I want them to end up merged as a single column in datetime format.
The head of the DataFrame is:
Date variable value 0 '04/10/2020' '00:30' 81.310 1 '05/10/2020' '00:30' 121.245 2 '06/10/2020' '00:30' 77.020 3 '07/10/2020' '00:30' 100.705 4 '08/10/2020' '00:30' 114.370
They are in a DF called df_flattened and has about 20k rows and the code I am currently using is:
df_flattened['DateTime'] = df_flattened.apply(lambda x: x['Date'] + ' ' + x['variable'], axis=1) df_flattened['DateTime'] = pd.to_datetime(df_flattened['DateTime'])
However, this takes about 2.6s to run and the dataset is going to get a lot bigger in the future. Can anyone suggest a fast way of doing this?
Advertisement
Answer
You can use + for join columns instead apply:
df_flattened['DateTime'] = pd.to_datetime(df_flattened['Date'] + ' ' + df_flattened['variable'])
Also is possible specify format of joined datetimes:
df_flattened['DateTime'] = pd.to_datetime(df_flattened['Date'] + ' ' + df_flattened['variable'], format='%d/%m/%Y %H:%M')
Performance for 20k rows:
#20k rows
df_flattened = pd.concat([df_flattened] * 4000, ignore_index=True)
In [44]: %%timeit
...: df_flattened['DateTime'] = df_flattened.apply(lambda x: x['Date'] + ' ' + x['variable'], axis=1)
...: df_flattened['DateTime'] = pd.to_datetime(df_flattened['DateTime'])
...:
...:
325 ms ± 26.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
In [45]: %timeit df_flattened['DateTime'] = pd.to_datetime(df_flattened['Date'] + ' ' + df_flattened['variable'])
11.9 ms ± 1.51 ms per loop (mean ± std. dev. of 7 runs, 100 loops each)
In [46]: %timeit df_flattened['DateTime'] = pd.to_datetime(df_flattened['Date'] + ' ' + df_flattened['variable'], format='%d/%m/%Y %H:%M')
9.55 ms ± 96.9 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)