时间戳

首页 > 解决方案 > 转换日期时间熊猫

python - 转换日期时间熊猫

问题描述

以下是我的 df 示例

date                   value

0006-03-01 00:00:00    1   
0006-03-15 00:00:00    2   
0006-05-15 00:00:00    1   
0006-07-01 00:00:00    3   
0006-11-01 00:00:00    1   
2009-05-20 00:00:00    2   
2009-05-25 00:00:00    8   
2020-06-24 00:00:00    1   
2020-06-30 00:00:00    2   
2020-07-01 00:00:00    13  
2020-07-15 00:00:00    2   
2020-08-01 00:00:00    4   
2020-10-01 00:00:00    2   
2020-11-01 00:00:00    4    
2023-04-01 00:00:00    1   
2218-11-12 10:00:27    1   
4000-01-01 00:00:00    6 
5492-04-15 00:00:00    1    
5496-03-15 00:00:00    1    
5589-12-01 00:00:00    1    
7199-05-15 00:00:00    1    
9186-12-30 00:00:00    1

如您所见，数据包含一些拼写错误的日期。

问题：

最终输出应如下所示。

date                   value


20.05.2009    2   
25.05.2009     8   
26.04.2020     1   
30.06.2020     2   
01.07.2020     13  
15.07.2020     2   
01.08.2020    4   
01.10.2020    2   
01.11.2020    4    
01.01.2100    1   
01.01.2100    1      
01.01.2100    1   
01.01.2100    1   
01.01.2100    1   
01.01.2100    1      
01.01.2100    1   
01.01.2100    1

我尝试使用 to_datetime 转换列，但失败了。

df[col] = pd.to_datetime(df[col], infer_datetime_format=True)

Out of bounds nanosecond timestamp: 5-03-01 00:00:00

感谢任何人的帮助！

标签： pythonpandasdatetime

解决方案

您可以在“-”拆分后检查日期时间字符串的第一个元素，并根据其整数值进行清理/替换。对于像 '0006' 这样的小值，调用pd.to_datetimewitherrors='coerce'就可以了。它将为无效日期留下“NaT”。你可以用dropna(). 例子：

import pandas as pd

df = pd.DataFrame({'date': ['0006-03-01 00:00:00',
                            '0006-03-15 00:00:00',
                            '0006-05-15 00:00:00',
                            '0006-07-01 00:00:00',
                            '0006-11-01 00:00:00',
                            'nan',
                            '2009-05-25 00:00:00',
                            '2020-06-24 00:00:00',
                            '2020-06-30 00:00:00',
                            '2020-07-01 00:00:00',
                            '2020-07-15 00:00:00',
                            '2020-08-01 00:00:00',
                            '2020-10-01 00:00:00',
                            '2020-11-01 00:00:00',
                            '2023-04-01 00:00:00',
                            '2218-11-12 10:00:27',
                            '4000-01-01 00:00:00',
                            'NaN',
                            '5496-03-15 00:00:00',
                            '5589-12-01 00:00:00',
                            '7199-05-15 00:00:00',
                            '9186-12-30 00:00:00']})

# first, drop columns where 'date' contains 'nan' (case-insensitive):
df = df.loc[~df['date'].str.contains('nan', case=False)]

# now replace strings where the year is above a threshold:
df.loc[df['date'].str.split('-').str[0].astype(int) > 2022, 'date'] = '2100-01-01 00:00:00'

# convert to datetime, if year is too low, will result in NaT:
df['date'] = pd.to_datetime(df['date'], errors='coerce')
# df['date']
# 0           NaT
# 1           NaT
# 2           NaT
# 3           NaT
# 4           NaT
# 5    2009-05-20
# 6    2009-05-25
# ...

df = df.dropna()
# df
#          date
# 6  2009-05-25
# 7  2020-06-24
# 8  2020-06-30
# 9  2020-07-01
# 10 2020-07-15
# 11 2020-08-01
# 12 2020-10-01
# 13 2020-11-01
# 14 2100-01-01
# 15 2100-01-01
# ...

推荐阅读