pandas 验证 Dataframe 日期，返回不匹配的值

zazmityj 于 2022-11-20 发布在其他

关注(0)|答案(2)|浏览(136)

这是我需要完成的工作流程：
1.验证column_1和column_2中所有日期的日期格式。
1.如果日期不是任一格式：月/日/年时：分或月/日/年时：分
1.* 需要帮助 * -打印不匹配的值。

**注：**我不知道日期的格式，有些根本不是日期。

样本输入数据CSV：

column_1    column_2
8/22/22 15:27   8/24/22 15:27
8/23/22 15:27   Tuesday, August 23, 2022
8/24/22 15:27   abc123
8/25/22 15:27   8/25/2022 15:27
8/26/22 15:27   8/26/2022 18:27
8/26/22 15:27   8/22/22

当to_datetime()函数返回ValueError时，下面的方法总是抛出一个异常。如何验证日期，然后捕获不匹配format_one或format_two的值？

df = pd.read_csv('input.csv', encoding='ISO-8859-1', dtype=str)

date_columns = ['column_1', 'column_2']

format_one = '%m/%d/%y %H:%M'
format_two = '%m/%d/%Y %H:%M'

for column in date_columns:
    for item in df[column]:
        try:
            if pd.to_datetime(df[item], format=format_one):
                print('format 1: ' + item)
            elif pd.to_datetime(df[item], format=format_two):
                print('format 2: ' + item)   
            else:
                print('unknown format: ' + item)
        except Exception as e:
            print('Exception:' )
            print(e)

输出量：

Exception:
'8/22/22 15:27'
Exception:
'8/23/22 15:27'
Exception:
'8/24/22 15:27'
Exception:
'8/25/22 15:27'
Exception:
'8/26/22 15:27'
Exception:
'8/26/22 15:27'
Exception:
'8/24/22 15:27'
Exception:
'Tuesday, August 23, 2022'
Exception:
'abc123'
Exception:
'8/25/2022 15:27'
Exception:
'8/26/2022 18:27'
Exception:
'8/22/22'

所需输出：

Exception:
'Tuesday, August 23, 2022'
Exception:
'abc123'
Exception:
'8/22/22'

谢谢你 *

pandas

来源：https://stackoverflow.com/questions/74448566/validate-dataframe-dates-return-non-matching-values

2条答案

按热度按时间

1yjd4xko1#

您需要单独测试每种允许的格式（在问题中给出的示例中，它们目前都在同一个try块中）。

import pandas as pd

allowed = ('%m/%d/%y %H:%M', '%m/%d/%Y %H:%M')

# dummy df
df = pd.DataFrame({"date": ["8/24/22 15:27", "Tuesday, August 23, 2022",
                            "abc123", "8/25/2022 15:27"]})

# this will be our mask, where the input format is invalid.
# initially, assume all invalid.
m = pd.Series([True]*df["date"].size)

# for each allowed format, test where the result is not NaT, i.e. valid.
# update the mask accordingly.
for fmt in allowed:
    m[pd.to_datetime(df["date"], format=fmt, errors="coerce").notna()] = False
    
# invalid format:
print(df["date"][m])
# 1    Tuesday, August 23, 2022
# 2                      abc123
# Name: date, dtype: object

应用于问题中的具体示例，它可能看起来像

# for reference:
df
        column_1                  column_2
0  8/22/22 15:27             8/24/22 15:27
1  8/23/22 15:27  Tuesday, August 23, 2022
2  8/24/22 15:27                    abc123
3  8/25/22 15:27           8/25/2022 15:27
4  8/26/22 15:27           8/26/2022 18:27
5  8/26/22 15:27                   8/22/22

date_columns = ['column_1', 'column_2']

for column in date_columns:
    m = pd.Series([True]*df[column].size)
    for fmt in allowed:
        m[pd.to_datetime(df[column], format=fmt, errors="coerce").notna()] = False

    print(f"{column}\n", df[column][m])

# column_1
#  Series([], Name: column_1, dtype: object)
 
# column_2
#  1    Tuesday, August 23, 2022
# 2                      abc123
# 5                     8/22/22
# Name: column_2, dtype: object

赞(0）回复(0）举报 2022-11-20

kqlmhetl2#

只是分享逻辑思维在技术上是可行的。请尝试一下。让我知道它不可行。

import pandas as pd
df = pd.DataFrame({'date': {0: '8/24/22 15:27', 1: '24/8/22 15:27', 2: 'a,b,c', 3: 'Tuesday, August 23, 2022'}})

       
mask1 = df.loc[pd.to_datetime(df['date'], errors='coerce',format='%m/%d/%y %H:%M').isnull()]
mask2 = df.loc[pd.to_datetime(df['date'], errors='coerce',format='%d/%m/%y %H:%M').isnull()]

df = pd.merge(mask1,mask2,on = ['date'],how ='inner')

print(df)

样品观察结果编号
输入df

date
0             8/24/22 15:27
1             24/8/22 15:27
2                     a,b,c
3  Tuesday, August 23, 2022

输出编号

date
0                     a,b,c
1  Tuesday, August 23, 2022

赞(0）回复(0）举报 2022-11-20

我来回答

pandas 验证 Dataframe 日期，返回不匹配的值

2条答案

相关问题

热门标签

最新问答