2012年数学建模竞赛脑卒中发病环境因素分析及干预日期数据处理代码

因四个表格日期数据处理有些复杂，故作此代码一次性处理四组数据： 

import datetime
import pandas as pddef check(string, df, i, num, error_list):if is_valid(pd.to_datetime(string, errors='coerce', format='%Y/%m/%d'), error_list, i):df.iloc[i, num] = pd.to_datetime(string, errors='coerce', format='%Y/%m/%d')# 数据中空格数据的替换
def data_process(data):new_data = []for d in data:d = d.replace(" ", "")new_data.append(d)return new_data# 日期格式错误的判断与修改
def judge(df, data, i, num, date, error_list):if len(data[0]) == 4 and len(data[1]) == 2 and len(data[2]) == 2:string = data[0] + '/' + data[1] + '/' + data[2]check(string, df, i, num, error_list)elif len(data[0]) == 2 and len(data[1]) == 2 and len(data[2]) == 4:string = data[2] + '/' + data[1] + '/' + data[0]check(string, df, i, num, error_list)elif len(data[0]) == 2 and len(data[1]) == 1 and len(data[2]) == 4:string = data[2] + '/0' + data[1] + '/' + data[0]check(string, df, i, num, error_list)elif len(data[0]) == 4 and len(data[1]) == 1 and len(data[2]) == 1:string = data[0] + '/0' + data[1] + '/0' + data[2]check(string, df, i, num, error_list)elif len(data[0]) == 4 and len(data[1]) == 2 and len(data[2]) == 1:string = data[0] + '/' + data[1] + '/0' + data[2]check(string, df, i, num, error_list)elif len(data[0]) == 4 and len(data[1]) == 1 and len(data[2]) == 2:string = data[0] + '/0' + data[1] + '/' + data[2]check(string, df, i, num, error_list)elif len(data[0]) == 5 and len(data[1]) == 1 and len(data[2]) == 2:string = data[0][1:5] + '/0' + data[1] + '/' + data[2]check(string, df, i, num, error_list)elif len(data[0]) == 5 and len(data[1]) == 1 and len(data[2]) == 1:string = data[0][1:5] + '/0' + data[1] + '/0' + data[2]check(string, df, i, num, error_list)elif len(data[0]) == 5 and len(data[1]) == 2 and len(data[2]) == 1:string = data[0][1:5] + '/' + data[1] + '/0' + data[2]check(string, df, i, num, error_list)else:print("第{}行{}列异常的数据：{}".format(i + 1, num + 1, date))error_list.append(i)# 字符串年月日的处理
def str_process(df, date, i, num, error_list):string = date[0:4] + '/' + date[4:6] + '/' + date[6:8]if is_valid(pd.to_datetime(string, errors='coerce', format='%Y/%m/%d'), error_list, i):df.iloc[i, num] = pd.to_datetime(string, errors='coerce', format='%Y/%m/%d')# 判断日期是否合法 若要修改日期合法范围，直接修改函数中的(2010, 12, 31)即可
def is_valid(date, error_list, i):date1 = datetime.datetime(2007, 1, 1)date2 = datetime.datetime(2010, 12, 31)if date >= date1 and date <= date2:return Trueelse:print("第{}行异常的数据：{}".format(i + 1, date))error_list.append(i)return False# 数据处理过程
def date_process(file_path, sheet_name, new_excel, new_sheet, flag, is_delete):df = pd.read_excel(file_path, sheet_name=sheet_name)error_list = []for T in range(3, 5):col_num = df.shape[0]for i in range(col_num):try:date = df.iloc[i, T]if type(date) != datetime.datetime:try:if type(date) == int:print("第{}行{}列异常的数据：{}".format(i + 1, T + 1, date))error_list.append(i)continuedata = data_process(date.split('-'))if len(data) == 3:judge(df, data, i, T, date, error_list)elif len(data) == 2:print("第{}行{}列异常的数据：{}".format(i + 1, T + 1, date))error_list.append(i)elif len(data) == 1:data = data_process(date.split('/'))if len(data) == 3:judge(df, data, i, T, date, error_list)elif len(data) == 1:if len(date) == 8:str_process(df, date, i, T, error_list)else:print("第{}行{}列异常的数据：{}".format(i + 1, T + 1, date))error_list.append(i)else:print("第{}行{}列异常的数据：{}".format(i + 1, T + 1, date))error_list.append(i)else:print("第{}行{}列异常的数据：{}".format(i + 1, T + 1, date))error_list.append(i)except:if flag:print("第{}行{}列异常的数据：{}".format(i + 1, T + 1, date))error_list.append(i)else:is_valid(date, error_list, i)except:print("出错{} {}".format(i, col_num))continueif i >= df.shape[0] - 1:breakif is_delete:df = df.drop(df.index[error_list])df.to_excel(new_excel, index=False, sheet_name=new_sheet)# 主函数
if __name__ == '__main__':NULL_Error = True  # 空格是否算作错误，若是则为True，若不是则为Falseis_delete = True  # 错误行是否删除,若删除则为True，若不删除则为Falsefile_path = "Data/data1.xls"  # 要修改的表的文件路径（可以用绝对路径）sheet_name = "脑卒中"  # 要修改表的表名new_excel = 'data6.xls'  # 修改后数据存储文件名new_sheet = 'Sheet1'  # 修改后数据存储的表名date_process(file_path, sheet_name, new_excel, new_sheet, NULL_Error, is_delete)