Context
When dealing with dates, I’ve sometimes problems because the source is not clean, or not all the rows have the same format. Additionally, dates can be simple (year-month-day) or really complicate like a timestamp with timezone.
Here are some code snippets for several use cases.
How to read dates with python and pandas
# import pandas import pandas as pd # df is a dataframe with a column 'column_with_date' with a date like this '19.01.2023 16:45:46 +01:00' # convert date string to datetime with pd.to_datetime pd.to_datetime(df['column_with_date']) # sometimes the format is a bit weird and pandas cannot recognize it. In this case we give the date format as argument. # for this format 19.01.2023 16:45:46 +01:00 we can use: pd.to_datetime(df['column_with_date'], format='%Y-%m-%d %H:%M:%S%z') # if your string has timezone, use utr=True pd.to_datetime(df['column_with_date'], format='%Y-%m-%d %H:%M:%S%z', utc=True) # sometimes your columns are as objects (strings) and numbers (floats, ints) and to_datetime cannot process it. # You can force the type string to the whole column before giving it to to_datetime. pd.to_datetime(df['column_with_date'].astype(str), format='%Y-%m-%d %H:%M:%S%z', utc=True) # pd.to_datetime does not modify the column values in place, so you have to assign it to the same column. df['column_with_date'] = pd.to_datetime(df['column_with_date'].astype(str), format='%Y-%m-%d %H:%M:%S%z', utc=True) # or save it to another (new) column if you want to save the original value df['column_with_date_2'] = pd.to_datetime(df['column_with_date'].astype(str), format='%Y-%m-%d %H:%M:%S%z', utc=True) How to save dates with UTC (tiemzone) to an Excel-File with Pandas
Saving datetime columns with timezones in pandas is not supported, and you will get the following error if you try:
ValueError: Excel does not support datetimes with timezones. Please ensure that datetimes are timezone unaware before writing to Excel. To remove the timezone from a datefield (column dtype datetime64[ns, UTC]) you can use .dt.date()
# remove timezones for excel df['column_with_date'] = df['column_with_date'].apply(lambda d: pd.to_datetime(d).date()) # save te file as usual df.to_excel('filename.xlsx') References
- https://stackoverflow.com/a/63238008/624088
- https://docs.python.org/3/library/datetime.html#strftime-and-strptime-format-codes -> format codes for datetime strings
- try and error :)
Top comments (0)