I have the following code that creates a DataFrame representing the data I have in my system:
import pandas as pd
data = {
"date": [
"2021-03-12 19:50:00-05:00", "2021-03-12 19:51:00-05:00", "2021-03-12 19:52:00-05:00",
"2021-03-12 19:53:00-05:00", "2021-03-12 19:54:00-05:00", "2021-03-12 19:55:00-05:00",
"2021-03-12 19:56:00-05:00", "2021-03-12 19:57:00-05:00", "2021-03-12 19:58:00-05:00",
"2021-03-12 19:59:00-05:00", "2021-03-15 04:00:00-04:00", "2021-03-15 04:01:00-04:00",
"2021-03-15 04:02:00-04:00", "2021-03-15 04:03:00-04:00", "2021-03-15 04:04:00-04:00",
"2021-03-15 04:05:00-04:00", "2021-03-15 04:06:00-04:00", "2021-03-15 04:07:00-04:00",
"2021-03-15 04:08:00-04:00", "2021-03-15 04:09:00-04:00"
],
"open": [81.15, 81.14, 81.15, 81.15, 81.15, 81.17, 81.19, 81.19, 81.20, 81.23, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05],
"high": [81.15, 81.14, 81.15, 81.15, 81.17, 81.17, 81.19, 81.19, 81.20, 81.23, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05],
"low": [81.14, 81.14, 81.14, 81.15, 81.15, 81.17, 81.19, 81.19, 81.20, 81.23, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05],
"close": [81.14, 81.14, 81.15, 81.15, 81.17, 81.17, 81.19, 81.19, 81.20, 81.23, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05, 81.05],
"volume": [300.0, 100.0, 1684.0, 0.0, 1680.0, 150.0, 448.0, 0.0, 1500.0, 380.0, 162.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0],
}
df = pd.DataFrame(data)
print(df.info())
The output is:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 20 entries, 0 to 19
Data columns (total 6 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 date 20 non-null object
1 open 20 non-null float64
2 high 20 non-null float64
3 low 20 non-null float64
4 close 20 non-null float64
5 volume 20 non-null float64
dtypes: float64(5), object(1)
memory usage: 1.1+ KB
The data type of the date column is object - it is timezone aware timestamp.
The timestamps contain timezone information that I need to remove then convert the date column to datetime64[m] (minute precision), but after applying the following conversion code:
df['date'] = df['date'].apply(lambda ts: pd.Timestamp(ts).tz_localize(None).to_numpy().astype('datetime64[m]'))
print(df.info())
The output shows that the date column has a data type of datetime64[ns] instead of datetime64[m]:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 20 entries, 0 to 19
Data columns (total 6 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 date 20 non-null datetime64[ns]
1 open 20 non-null float64
2 high 20 non-null float64
3 low 20 non-null float64
4 close 20 non-null float64
5 volume 20 non-null float64
dtypes: datetime64 , float64(5)
memory usage: 1.1 KB
How can I correctly convert the date column with timezone data to datetime64[m] in the most memory-efficient way?
Unfortunately there is no "minute" unit for pandas datetimes. You
can choose from "D,s,ms,us,n" for day, second, millisecond, microsecond, or nanosecond
respectively. This listing can be found under the "unit" argument in the docs of pandas.to_datetime
That said, you can still parse this data and convert it to a seconds unit. The
key here is understanding that pandas cannot handle having distinct timezone information (-05:00 and -04:00)
in a single series (column). It can support a timezone + differing daylight savings info (as I suspect the case is here),
but since it is ambiguous as to whether this is the case we’re going to need to
take a trip through UTC and then let the conversion to our timezone handle whether
something is in daylight savings or not.
import pandas as pd
data = {
'raw': [
'2021-03-12 19:50:00-05:00', '2021-03-12 19:51:00-05:00', '2021-03-12 19:52:00-05:00',
'2021-03-12 19:53:00-05:00', '2021-03-12 19:54:00-05:00', '2021-03-12 19:55:00-05:00',
'2021-03-12 19:56:00-05:00', '2021-03-12 19:57:00-05:00', '2021-03-12 19:58:00-05:00',
'2021-03-12 19:59:00-05:00', '2021-03-15 04:00:00-04:00', '2021-03-15 04:01:00-04:00',
'2021-03-15 04:02:00-04:00', '2021-03-15 04:03:00-04:00', '2021-03-15 04:04:00-04:00',
'2021-03-15 04:05:00-04:00', '2021-03-15 04:06:00-04:00', '2021-03-15 04:07:00-04:00',
'2021-03-15 04:08:00-04:00', '2021-03-15 04:09:00-04:00'
],
}
df = pd.DataFrame(data)
df['parsed_w_timezone'] = (
pd.to_datetime(df['raw'], format='%Y-%m-%d %H:%M:%S%z', utc=True) # 1. parse into utc
.dt.tz_convert('US/Eastern') # 2. convert to US/Eastern
.astype('datetime64[s, US/Eastern]') # 3. convert nanoseconds → seconds unit
)
df['parsed_wo_timezone'] = df['parsed_w_timezone'].dt.tz_localize(None)
print(df.dtypes)
# raw object
# parsed_w_timezone datetime64[s, US/Eastern]
# parsed_wo_timezone datetime64[s]
# dtype: object
print(df.to_string(col_space=30, index=False, justify='left'))
# raw parsed_w_timezone parsed_wo_timezone
# 2021-03-12 19:50:00-05:00 2021-03-12 19:50:00-05:00 2021-03-12 19:50:00
# 2021-03-12 19:51:00-05:00 2021-03-12 19:51:00-05:00 2021-03-12 19:51:00
# 2021-03-12 19:52:00-05:00 2021-03-12 19:52:00-05:00 2021-03-12 19:52:00
# 2021-03-12 19:53:00-05:00 2021-03-12 19:53:00-05:00 2021-03-12 19:53:00
# 2021-03-12 19:54:00-05:00 2021-03-12 19:54:00-05:00 2021-03-12 19:54:00
# 2021-03-12 19:55:00-05:00 2021-03-12 19:55:00-05:00 2021-03-12 19:55:00
# 2021-03-12 19:56:00-05:00 2021-03-12 19:56:00-05:00 2021-03-12 19:56:00
# 2021-03-12 19:57:00-05:00 2021-03-12 19:57:00-05:00 2021-03-12 19:57:00
# 2021-03-12 19:58:00-05:00 2021-03-12 19:58:00-05:00 2021-03-12 19:58:00
# 2021-03-12 19:59:00-05:00 2021-03-12 19:59:00-05:00 2021-03-12 19:59:00
# 2021-03-15 04:00:00-04:00 2021-03-15 04:00:00-04:00 2021-03-15 04:00:00
# 2021-03-15 04:01:00-04:00 2021-03-15 04:01:00-04:00 2021-03-15 04:01:00
# 2021-03-15 04:02:00-04:00 2021-03-15 04:02:00-04:00 2021-03-15 04:02:00
# 2021-03-15 04:03:00-04:00 2021-03-15 04:03:00-04:00 2021-03-15 04:03:00
# 2021-03-15 04:04:00-04:00 2021-03-15 04:04:00-04:00 2021-03-15 04:04:00
# 2021-03-15 04:05:00-04:00 2021-03-15 04:05:00-04:00 2021-03-15 04:05:00
# 2021-03-15 04:06:00-04:00 2021-03-15 04:06:00-04:00 2021-03-15 04:06:00
# 2021-03-15 04:07:00-04:00 2021-03-15 04:07:00-04:00 2021-03-15 04:07:00
# 2021-03-15 04:08:00-04:00 2021-03-15 04:08:00-04:00 2021-03-15 04:08:00
# 2021-03-15 04:09:00-04:00 2021-03-15 04:09:00-04:00 2021-03-15 04:09:00