如何按数据类型交换列？多日期格式下的数据修正方案问询

示例数据（虚构） #

数据格式要求 #

从表格可见，部分列的数据类型不符合规范：

• Date Death：应为日期格式
• Exact date of death：仅允许Y、N或空值
• Death Cause：应为字符串类型（ICD码，格式为字母+数字）

补充说明：日期格式不统一，01-05-2010、01 May 2010等格式也会出现在日期列中。

尝试的基础清洗代码 #

Python 代码 #

import pandas as pd

death_y_n = death['Date Death'][pd.to_datetime(death['Date Death'], \
                                                                 format='%d/%m/%Y',
                                                                 errors = 'coerce')\
                                                                 .isnull()]

death_disease_case = death['Exact date of death'][~((death['Exact date of death'].isin(['Y','N']))\
                                                      |(death['Exact date of death'].isnull()))]

death['Death Cause'][~pd.to_datetime(\
                                      death['Death Cause'], \
                                      format='%d/%m/%Y', errors = 'coerce')\
                                      .isnull()] = \
                                      death_disease_case

death['Date Death'][pd.to_datetime(\
                                      death['Date Death'], \
                                      format='%d/%m/%Y', errors = 'coerce')\
                                      .isnull()] = \
                                      death_to_date[pd.to_datetime(\
                                                                   death['Date Death'], \
                                                                   format='%d/%m/%Y', errors = 'coerce')\
                                                                   .isnull()]

death['Exact date of death'][~death['Exact date of death'].isin(['Y','N'])] = \
                                  death_y_n[~death['Exact date of death'].isin(['Y','N'])]

death['Death Cause'][pd.to_datetime(\
                                      death['Date Death'], \
                                      format='%d/%m/%Y', errors = 'coerce')\
                                      .isnull()] = \
                                       death_y_n[pd.to_datetime(\
                                      death['Date Death'], \
                                      format='mixed', errors = 'coerce')\
                                      .isnull()]

R 代码 #

library(tidyverse)
library(magrittr)
library(anytime)
library(Hmisc)

death_to_date = anytime(death$`Death Cause`) %>% as.character

death_y_n = death$`Date Death`[is.na(as_date(death$`Date Death`))]

death_disease_case = death$`Exact date of death`[death$`Exact date of death` %nin% c('Y','N')]

death$`Death Cause`[!is.na(as_date(death$`Death Cause` ))] = death_disease_case[!is.na(as_date(death$`Death Cause` ))]

death$`Date of Registered Death`[is.na(as_date(death$`Date Death`))] = death_to_date[is.na(as_date(death$`Date Death`))]

death$`Exact date of death`[death$`Exact date of death` %nin% c('Y','N')] = death_y_n[death$`Exact date of death` %nin% c('Y','N')]

问题与求助 #

由于日期格式多样，部分日期无法被to_datetime()/anytime()成功解析。想请教：有没有不依赖这类日期解析函数，就能完成列数据错位交换的方法？

另外我是Python新手，上述Python代码如果存在错误，麻烦帮忙指出！谢谢。

更新后的Python解决方案 #

import pandas as pd
import numpy as np  # 原代码未导入numpy，但最后一行用到了np.nan，需补上

# 将Date Death中属于Exact date of death的'Y'/'N'值转移到对应列
death_to_date_index_exact = (death['Date Death'].isin(['Y','N']))
death_to_date_exact = death['Date Death'][death_to_date_index_exact]

# 将Death Cause中不属于ICD码的日期值转移到Date Death
death_cause_index_date = (~death['Death Cause'].str.contains('^[A-Za-z].*[0-9]$', na=True))
death_cause_date = death['Death Cause'][death_cause_index_date]

# 将Exact date of death中属于ICD码的内容转移到Death Cause
death_exact_index_cause = (death['Exact date of death'].str.contains('^[A-Za-z].*[0-9]$', na=False))
death_exact_cause = death['Exact date of death'][death_exact_index_cause]

# 执行数据交换
death['Date Death'][death_cause_index_date] = death_cause_date
death['Exact date of death'][death_to_date_index_exact] = death_to_date_exact
death['Death Cause'][death_exact_index_cause] = death_exact_cause

# 将Death Cause中不属于ICD码的内容设为空值
death['Death Cause'][~death['Death Cause'].str.contains('^[A-Za-z].*[0-9]$', na=True)] = np.nan

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