Merge text into datetime column

Question

I have a dataframe with 2 columns. col1 is date and col2 is bigint. There are dummy values 1970-01-01 00:00:00 and 19700101000000

col1                col2
2012-01-12 18:09:42 19700101000000
1970-01-01 00:00:00 20140701000001

I am looking for a way to merge these 2 columns into a single datetime column like this...

col3
2012-01-12 18:09:42
2014-07-01 00:00:01

Or is there any way to merge text from column col2 into col1.

Answer 1

You need first to_datetime and then to_timedelta, last add to col1:

print (pd.to_datetime(df.col2, format='%Y%m%d%H%M%S'))
0   1970-01-01 00:00:00
1   2014-07-01 00:00:01
Name: col2, dtype: datetime64[ns]

print (pd.to_timedelta(pd.to_datetime(df.col2, format='%Y%m%d%H%M%S')))
0       0 days 00:00:00
1   16252 days 00:00:01
Name: col2, dtype: timedelta64[ns]

df.col1 = pd.to_datetime(df.col1)
df['col3'] = pd.to_timedelta(pd.to_datetime(df.col2, format='%Y%m%d%H%M%S')) + df.col1
print (df)
                 col1            col2                col3
0 2012-01-12 18:09:42  19700101000000 2012-01-12 18:09:42
1 1970-01-01 00:00:00  20140701000001 2014-07-01 00:00:01

Parameter unit can be used too:

df['col3'] = pd.to_timedelta(pd.to_datetime(df.col2, format='%Y%m%d%H%M%S'), unit='ns') + 
             df.col1
print (df)
                 col1            col2                col3
0 2012-01-12 18:09:42  19700101000000 2012-01-12 18:09:42
1 1970-01-01 00:00:00  20140701000001 2014-07-01 00:00:01