Mixed timestamps in a DataFrame cause an error when converting to int64

Question

I am merging time-indexed data from multiple sources (with some times as integer timestamps and some as UTC strings), converting the times to a pandas Timestamp for the purpose of manipulation, and then need to re-export with the times as epoch timestamps. The problem is that I'm getting an error when converting the Timestamps back to int64 when (and only when) the DataFrame contains a mix of UTC and non-UTC Timestamps.

This works:

df1 = pd.DataFrame([{'time':1617217320000}])
df1['time'] = pd.to_datetime(df1['time'], unit='ms')
df1['time'] = df1['time'].values.astype('int64') // 10**9

So does this:

df2 = pd.DataFrame([{'time':'2021-03-30T18:52:00.000Z'}])
df2['time'] = pd.to_datetime(df2['time'])
df2['time'] = df2['time'].values.astype('int64') // 10**9

But this does not:

df1 = pd.DataFrame([{'time':1617217320000}])
df1['time'] = pd.to_datetime(df1['time'], unit='ms')
df2 = pd.DataFrame([{'time':'2021-03-30T18:52:00.000Z'}])
df2['time'] = pd.to_datetime(df2['time'])
df = df1.append(df2)
df['time'] = df['time'].values.astype('int64') // 10**9

# TypeError: int() argument must be a string, a bytes-like object or a number, not 'Timestamp'

Do I need to normalize these somehow to allow the conversion to work?

user_9987623 · Answer

You can also use the .value attribute of pandas Timestamp() class (of which both of your values are instances) (https://pandas.pydata.org/docs/reference/api/pandas.Timestamp.html) to convert both types of timestamp to int.

>>> df['time'] = df.time.apply(lambda x: x.value // 10**9)

>>> df
     time
0    1617217320
1    1617130320

Mixed timestamps in a DataFrame cause an error when converting to int64

1 Answers