I have a timestamp column that contains some data in the formats shown below.
Could you please help explain to me how I can convert it into a consistent timestamp or date format?
2020-04-24 11:00:00 2020-03-18 09:00:00 2020-07-06 08:00:00 43851.333333333336 43878 43848.458333333336
Any help or suggestions would be helpful.
I agree with @DanielLathrop and @anon01 -- it's ideal to fix the data upstream. If that's not possible, here is a function that handles both ISO dates and Excel dates. (I'm assuming Excel 1900 format, not Excel 1904 format.)
from pandas.tseries.offsets import DateOffset
import pandas as pd
def convert(t):
''' Convert date (ISO format) or Excel 1900 format to pandas Timestamp.'''
if isinstance(t, (float, int)):
return pd.Timestamp('1899-12-30') + DateOffset(days=t)
elif isinstance(t, str):
return pd.to_datetime(t, format='%Y-%m-%d %H:%M:%S', errors='raise')
else:
raise ValueError(f'{t} not recognized')
# reference:
# https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system
# test Excel date, using Microsoft published example
d = 35_981
actual = convert(d)
expected = pd.Timestamp('1998-07-05')
assert actual == expected
# sample data
timestamps = [
'2020-04-24 11:00:00',
'2020-03-18 09:00:00',
'2020-07-06 08:00:00',
43851.333333333336, # these are Excel timestamps
43878,
43848.458333333336,
]
# sample conversions
[convert(t) for t in timestamps]
# converted dates
[Timestamp('2020-04-24 11:00:00'),
Timestamp('2020-03-18 09:00:00'),
Timestamp('2020-07-06 08:00:00'),
Timestamp('2020-01-21 08:00:00'),
Timestamp('2020-02-17 00:00:00'),
Timestamp('2020-01-18 11:00:00')]
More details on Excel dates here: https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system
Your timestamp examples are unclear,
Timestamp conversion to DateTime Conversion in Python can be done in the following way:
from datetime import datetime
result = datetime.fromtimestamp(43878)
print(result)