python - How to convert unix epoch time to datetime with timezone in pandas

Question

I have many csv files containing Unix epoch time which needs to be converted to human readable date/time. The following Python code does the job but it is very slow.

df['dt'] = pd.to_datetime(df['epoch'], unit='s')
df['dt'] = df.apply(lambda x: x['dt'].tz_localize('UTC').tz_convert('Europe/Amsterdam'), axis=1)

Actually, the second line is the bottleneck (~30 seconds for 1 million rows). So even with the aid of multiprocessing, it is not scalable as I have more than a billion records totally. How can I make it faster?

question from:https://stackoverflow.com/questions/65948018/how-to-convert-unix-epoch-time-to-datetime-with-timezone-in-pandas

Answer 1

• The question pertains to pandas, the pure python version is Converting unix timestamp string to readable date
• pandas.Series.dt.tz_localize & pandas.Series.dt.tz_convert are both vectorized functions, which don't require using .apply().
  • The vectorized implementation is 8159 times faster than .apply().
  • The .dt accessor must be used.
• It may be better to use pd.to_datetime(df['DT'], unit='s', utc=True) and remove .dt.tz_localize('UTC').

import pandas as pd

# test dataframe with 1M rows
df = pd.DataFrame({'DT': [1349720105, 1349806505, 1349892905, 1349979305, 1350065705]})
df['DT'] = pd.to_datetime(df['DT'], unit='s')
df = pd.concat([df]*200000).reset_index(drop=True)

# display(df.head()
                 DT
2012-10-08 18:15:05
2012-10-09 18:15:05
2012-10-10 18:15:05
2012-10-11 18:15:05
2012-10-12 18:15:05

# convert the column
df['DT'] = df['DT'].dt.tz_localize('UTC').dt.tz_convert('Europe/Amsterdam')

# display(df.head())
                       DT
2012-10-08 20:15:05+02:00
2012-10-09 20:15:05+02:00
2012-10-10 20:15:05+02:00
2012-10-11 20:15:05+02:00
2012-10-12 20:15:05+02:00

print(df.info())
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1000000 entries, 0 to 999999
Data columns (total 1 columns):
 #   Column  Non-Null Count    Dtype                           
---  ------  --------------    -----                           
 0   DT      1000000 non-null  datetime64[ns, Europe/Amsterdam]
dtypes: datetime64[ns, Europe/Amsterdam](1)
memory usage: 7.6 MB

Alternative

• This option is more concise and localizes to 'UTC' when converting to a datetime dtype with pandas.to_datetime().

df['DT'] = pd.to_datetime(df['DT'], unit='s', utc=True).dt.tz_convert('Europe/Amsterdam')

• The most time consuming aspect of the original implementation from the OP was x['dt'].tz_localize('UTC') within the .apply()
• The following code runs in about the same amount of time, within a few milliseconds.

df['DT_1'] = pd.to_datetime(df['DT'], unit='s', utc=True).dt.tz_convert('Europe/Amsterdam')
df['DT_2'] = pd.to_datetime(df['DT'], unit='s', utc=True).apply(lambda x: x.tz_convert('Europe/Amsterdam'))

%%timeit Testing

• 1M rows
• This tests the comparable vectorized version, against the version with .apply() from the OP, where 'DT' has already been converted to a datetime dtype.

%%timeit
df['DT'].dt.tz_localize('UTC').dt.tz_convert('Europe/Amsterdam')
[out]:
4.4 ms ± 494 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
df.apply(lambda x: x['DT'].tz_localize('UTC').tz_convert('Europe/Amsterdam'), axis=1)
[out]:
35.9 s ± 572 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)