把所有的excel空值,时间空值,全部转成python的空值-None。
#=====处理各种空值转成None,暂时不支持0,0:0,00:00:00这些空数据==========
def df_format_none(data):
# to_datetime 支持日期类型有:20201230,2020-12-30 ,010201
def is_date(tmy): # 日期类型返回True
if '-' in str(tmy):
try:
tmx = pd.to_datetime(tmy)
if isinstance(tmx, datetime.datetime):
return True
except:
return False
return False
if not data.empty:
data = data.reset_index(drop=True) # 确保首行 索引是0
data = data.applymap(lambda x: None if str(x) in ['', 'null','#N/A'] else x) # 处理空字符,可往列表里面加
data = data.where(data.notnull(), None) # where只能处理NaN,不能处理 NaT和空字符串""
tmx = data.head(1)
cols = data.columns.tolist()
for col in cols:
tmy = tmx.at[0, col]
# 判断列是否为日期类型,使用read_excel将日期列读取为str时(即参数converters={'b':str}),会产生新的空值类型: 'NaN', 'nan'
if str(tmy) in ['NaT', 'NaN', 'nan'] or is_date(tmy):
data[col] = data[col].astype(str) # 日期类型不能接受None,先转换为str
data[col] = data[col].map(lambda x: None if str(x) in ['NaT', 'NaN', 'nan'] else x)
return data
对dataframe的时间进行规范化和格式化
#=====处理各种时间格式数据,暂时不支持42935,20121231类型=============
def strat_time_date(start_time1):
start_time1 = str(start_time1)
# =====针对空日期数据====
if (start_time1 == '00:00:00') or (start_time1 == 'None') or (start_time1 == '0') or (start_time1 == '0.0'):
start_time1 = ''
# =====针对%Y-%m-%d日期格式=======
if ('-' in start_time1) and (len(start_time1) == 10):
start_time1 = time.strptime(start_time1, '%Y-%m-%d')
start_time1 = time.strftime('%Y-%m-%d', start_time1)
# =====针对%Y-%m-%d %H:%M:%S日期格式=======
if ('-' in start_time1) and (len(start_time1) > 17):
start_time1 = time.strptime(start_time1, '%Y-%m-%d %H:%M:%S')
start_time1 = time.strftime('%Y-%m-%d', start_time1)
# =====针对%Y/%m/%d日期格式=======
if ('/' in start_time1) and (len(start_time1) == 10):
start_time1 = time.strptime(start_time1, '%Y/%m/%d')
start_time1 = time.strftime('%Y-%m-%d', start_time1)
# =====针对%Y/%m/%d %H:%M:%S日期格式=======
if ('/' in start_time1) and (len(start_time1) > 17):
start_time1 = time.strptime(start_time1, '%Y/%m/%d %H:%M:%S')
start_time1 = time.strftime('%Y-%m-%d', start_time1)
return str(start_time1)