Pandas将Object类型转为Datetime触发ValueError，求解决方法

我的代码 #

import pandas as pd

# Sample data
data = {'Channel_Started': ['2013-05-18T04:46:00Z', '2018-01-16T15:55:22Z', '2016-12-12T05:00:55Z', '2020-04-29T17:18:25.574824Z', '2023-11-24T21:56:18.571502Z', '2020-06-13T05:20:37.182391Z', '2015-08-22T13:22:51Z', '2016-11-16T14:52:31Z', '2020-05-09T20:35:27.233665Z', '2022-03-16T22:09:57.246468Z', '2023-02-11T05:55:01.369504Z', '2023-03-10T12:18:40.189285Z', '2005-12-16T09:01:28Z', '2013-09-05T01:15:06Z', '2017-07-13T09:30:23Z', '2020-08-05T16:09:28.304314Z']}

# Create DataFrame
df = pd.DataFrame(data)

# Convert to datetime
df['Channel_Started'] = pd.to_datetime(df['Channel_Started'])

# Extract date
df['Channel_Started'] = df['Channel_Started'].dt.date

print(df)

错误输出 #

ValueError: 时间数据"2020-04-29T17:18:25.574824Z"与格式"%Y-%m-%dT%H:%M:%S%z"不匹配，位置为3。你可以尝试：

- 如果字符串格式一致，传入`format`参数；
- 如果所有字符串均为ISO8601格式但不完全统一，传入`format='ISO8601'`；
- 传入`format='mixed'`，将为每个元素单独推断格式，你可以搭配`dayfirst`参数使用。

问题分析 #

数据中的时间字符串存在两种ISO8601变体：一种不带毫秒（如2013-05-18T04:46:00Z），另一种带微秒级毫秒（如2020-04-29T17:18:25.574824Z），默认的pd.to_datetime()无法同时兼容两种格式，因此触发格式不匹配的报错。

解决方案 #

按照错误提示，指定format='ISO8601'参数即可兼容所有ISO8601标准的时间字符串，修改后的代码如下：

import pandas as pd

# Sample data
data = {'Channel_Started': ['2013-05-18T04:46:00Z', '2018-01-16T15:55:22Z', '2016-12-12T05:00:55Z', '2020-04-29T17:18:25.574824Z', '2023-11-24T21:56:18.571502Z', '2020-06-13T05:20:37.182391Z', '2015-08-22T13:22:51Z', '2016-11-16T14:52:31Z', '2020-05-09T20:35:27.233665Z', '2022-03-16T22:09:57.246468Z', '2023-02-11T05:55:01.369504Z', '2023-03-10T12:18:40.189285Z', '2005-12-16T09:01:28Z', '2013-09-05T01:15:06Z', '2017-07-13T09:30:23Z', '2020-08-05T16:09:28.304314Z']}

# Create DataFrame
df = pd.DataFrame(data)

# Convert to datetime with ISO8601 format
df['Channel_Started'] = pd.to_datetime(df['Channel_Started'], format='ISO8601')

# Extract date
df['Channel_Started'] = df['Channel_Started'].dt.date

print(df)

预期输出 #

Channel_Started
0       2013-05-18
1       2018-01-16
2       2016-12-12
3       2020-04-29
4       2023-11-24
5       2020-06-13
6       2015-08-22
7       2016-11-16
8       2020-05-09
9       2022-03-16
10      2023-02-11
11      2023-03-10
12      2005-12-16
13      2013-09-05
14      2017-07-13
15      2020-08-05

内容的提问来源于stack exchange，提问作者Asingh