从Pandas多时区时间戳中提取日期

Question

我有一个Pandas数据帧，其中我基于time_zone列将hour转换为local_hour。我现在想将日期从local_hour提取为local_date，但是我得到了一个错误Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True。我该怎么做？你知道吗

# Create dataframe
import pandas as pd
df = pd.DataFrame({
   'hour': ['2019-01-01 05:00:00', '2019-01-01 07:00:00', '2019-01-01 08:00:00'],
   'time_zone': ['US/Eastern', 'US/Central', 'US/Mountain']
})

# Convert hour to datetime and localize to UTC
df['hour'] = pd.to_datetime(df['hour']).dt.tz_localize('UTC')

# Get local_hour
df['local_hour'] = df.apply(lambda row: row['hour'].tz_convert(row['time_zone']), axis=1)

# Try to get local_date from local_hour
df['local_date'] = pd.to_datetime(df['local_hour'].dt.date)
ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True

Answer 1

似乎只有当local_hour列包含不同的时区时问题才会发生。如果所有的东西都在同一个时区，这会起作用：

# Work: the whole column in a single timezone
df['local_hour'] = df['hour'].dt.tz_convert('America/New_York')
df['local_hour'].dt.date

# Not work: column contains a mix of timezones
df['local_hour'] = df.apply(lambda row: row['hour'].tz_convert(row['time_zone']), axis=1)
df['local_hour'].dt.date

ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True

我建议你向熊猫队提出问题。同时，您可以使用apply，它本质上是一个循环：

tmp = df['local_hour'].apply(lambda t: pd.Series({
    'date': t.date(),
    'hour': t.hour
}))
df = pd.concat([df, tmp], axis=1)