分析熊猫中的字符串时区

数据:

import pandas as pd

    
received_historic_data = [("20230728 09:30:00 US/Eastern", 194.7, 195.26),
                          ("20230729 09:30:00 US/Eastern", 195.26, 200.45),
                          ("20230730 09:30:00 US/Eastern", 200.45, 176.34)]
df = pd.DataFrame(data=received_historic_data, columns=["time", "open", "close"])

使用.split().cat().drop()的代码:

df[["date", "time", "tz"]] = df["time"].str.split(" ", expand=True)
df = (df
      .assign(time=pd.to_datetime(df.pop("date").str.cat(df["time"], sep=" ")))
      .set_index("time")
      .drop(columns="tz"))
print(df)

使用.split().apply().join()的代码:

df["time"] = pd.to_datetime(df["time"].str.split(" ").str[:2].apply(lambda x: " ".join(x)))
df = df.set_index("time")
print(df)

输出:

                       open   close
time                           
2023-07-28 09:30:00  194.70  195.26
2023-07-29 09:30:00  195.26  200.45
2023-07-30 09:30:00  200.45  176.34

在这种情况下,显式指定的时间格式 '%Y%m%d %H:%M:%S %Z' 转换为日期时间时需要。

测试代码:

import pandas as pd
import pytz

# check if 'US/Eastern' is a recognizable time zone name 
print(f"{'US/Eastern' in pytz.all_timezones = }")

# prepare test data
received_historic_data = [('20230728 09:30:00 US/Eastern', 194.7, 195.26)]
df = pd.DataFrame(
    received_historic_data, columns=["time", "open", "close"]
).set_index("time")

# check the index before an attempt to transform it to datetime
print(f'''Before transformation: 
      {df.index.dtype = }
      {df.index[0] = !r}''')

# explicitly pass format when transforming to datetime
df.index = pd.to_datetime(df.index, format='%Y%m%d %H:%M:%S %Z')

# check the transformed index's value and type
print(f'''After: 
      {df.index.dtype = }
      {df.index[0] = !r}''')

我的输出:

'US/Eastern' in pytz.all_timezones = True
Before transformation: 
      df.index.dtype = dtype('O')
      df.index[0] = '20230728 09:30:00 US/Eastern'
After: 
      df.index.dtype = datetime64[ns, US/Eastern]
      df.index[0] = Timestamp('2023-07-28 09:30:00-0400', tz='US/Eastern')

详细信息:

• format codes
• pandas.to_datetime

所用工具的版本:

python : 3.11 
pandas : 1.5.1
pytz   : 2022.5