Pandas slow to merge and convert to datetime-CodePudding

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?

CodePudding user response：

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)