英文:
Date conversion error in DataFrame in pandas, can anyone point why this issue is happening and how to fix it
问题
我正在尝试转换我的数据框中的两个日期列。但是，其中一些日期是使用"%d/%m/%Y"转换的，而另一些日期是使用"%m/%d/%Y"转换的。问题仅发生在从2023年5月1日到2023年5月12日之间。从5月13日开始，又开始使用"%d/%m/%Y"。
我正在使用以下代码来转换我的数据：
columns_to_convert_to_date = ['Date', 'Value Dt']
regex_pattern = r'\d{2}/\d{2}/\d{4}'
for column in columns_to_convert_to_date:
full_bank_df_hdfc[column] = full_bank_df_hdfc[column].apply(lambda x: pd.to_datetime(x, format='%d/%m/%Y', errors='coerce') if re.match(regex_pattern, str(x)) else pd.to_datetime(x, errors='coerce'))
数据转换前的截图：
点击此处查看图片
数据转换后的截图：
点击此处查看图片
我尝试强制指定日期格式，但没有成功。只使用以下代码时：
pd.to_datetime(full_bank_df_hdfc['Date'], format='%d/%m/%y', errors='coerce')
在列中得到了所需的输出，但导致我已经存在的日期时间格式行变成了NaT。
1043 2023-04-30
1044 2023-05-01
1045 2023-05-01
1046 2023-05-01
1047 2023-05-02
1048 2023-05-02
1049 2023-05-03
1050 2023-05-03
1051 2023-05-03
1052 2023-05-04
1053 2023-05-04
1054 2023-05-06
1055 2023-05-06
1056 2023-05-07
1057 2023-05-08
英文:
i am trying to convert my two date columns in my dataframe. however some of the date is converted using "%d/%m/%Y" and a few of the data is getting converted using "%m/%d/%Y" . the issue is happening only from 01-May-2023 to 12-May-2023. from 13 may it is again reverting to using "%d/%m/%Y".
I am using the following to convert my data:
columns_to_convert_to_date= ['Date','Value Dt']
regex_pattern = r'\d{2}/\d{2}/\d{4}'
for column in columns_to_convert_to_date:
full_bank_df_hdfc[column] = full_bank_df_hdfc[column].apply(lambda x: pd.to_datetime(x, format='%d/%m/%Y', errors='coerce') if re.match(regex_pattern, str(x)) else pd.to_datetime(x, errors='coerce'))
screenshot
data before transformation:
enter image description here
data after transformation:
enter image description here
i have tried to force it to use the format to no avail. when using only
pd.to_datetime(full_bank_df_hdfc['Date'],format='%d/%m/%y', errors='coerce')
am i getting the desired output in the column , however that is resulting in my already existing datetime format roes to be NaT
1043 2023-04-30
1044 2023-05-01
1045 2023-05-01
1046 2023-05-01
1047 2023-05-02
1048 2023-05-02
1049 2023-05-03
1050 2023-05-03
1051 2023-05-03
1052 2023-05-04
1053 2023-05-04
1054 2023-05-06
1055 2023-05-06
1056 2023-05-07
1057 2023-05-08
答案1
得分: 0
你尝试匹配年份部分的4位数字，但你的截图只显示年份的2位数字？
尝试：
columns_to_convert_to_date= ['Date','Value Dt']
regex_pattern = r'(\d{2})/(\d{2})/(\d{2})'
for column in columns_to_convert_to_date:
dt = df[column].str.replace(regex_pattern, r'20-- 00:00:00', regex=True)
df[column] = pd.to_datetime(dt)
输出：
# 在处理前
>>> df
Date Value Dt
0 2022-04-01 00:00:00 2022-04-01 00:00:00
1 2022-04-01 00:00:00 2022-04-01 00:00:00
2 2022-04-02 00:00:00 2022-04-02 00:00:00
3 2022-04-02 00:00:00 2022-04-02 00:00:00
4 2022-04-02 00:00:00 2022-04-02 00:00:00
1084 24/05/23 24/05/23
1085 24/05/23 24/05/23
# 处理后
>>> df
Date Value Dt
0 2022-04-01 2022-04-01
1 2022-04-01 2022-04-01
2 2022-04-02 2022-04-02
3 2022-04-02 2022-04-02
4 2022-04-02 2022-04-02
1084 2023-05-24 2023-05-24
1085 2023-05-24 2023-05-24
英文:
You try to match the year part on 4 digits but your screenshot show only 2 digits for year?
Try:
columns_to_convert_to_date= ['Date','Value Dt']
regex_pattern = r'(\d{2})/(\d{2})/(\d{2})'
for column in columns_to_convert_to_date:
dt = df[column].str.replace(regex_pattern, r'20\3-\2-\1 00:00:00', regex=True)
df[column] = pd.to_datetime(dt)
Output:
# Before
>>> df
Date Value Dt
0 2022-04-01 00:00:00 2022-04-01 00:00:00
1 2022-04-01 00:00:00 2022-04-01 00:00:00
2 2022-04-02 00:00:00 2022-04-02 00:00:00
3 2022-04-02 00:00:00 2022-04-02 00:00:00
4 2022-04-02 00:00:00 2022-04-02 00:00:00
1084 24/05/23 24/05/23
1085 24/05/23 24/05/23
# After
>>> df
Date Value Dt
0 2022-04-01 2022-04-01
1 2022-04-01 2022-04-01
2 2022-04-02 2022-04-02
3 2022-04-02 2022-04-02
4 2022-04-02 2022-04-02
1084 2023-05-24 2023-05-24
1085 2023-05-24 2023-05-24
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论