Pandas数据清理，看这一篇就够了

df = pd.DataFrame()# the glob module is used to retrieve files/pathnames matching a specified patterndir_filenames = sorted(glob('./*.xlsx')) # all excel files from current directoryfor dir_file in dir_filenames:    dict_xlsx = pd.read_excel(dir_file, sheet_name=None)     workbook = pd.concat([v_df.assign(Sheet = k) for k,v_df in dict_xlsx.items()], ignore_index=True)    df = pd.concat([df,workbook],ignore_index=True)print(f'shape of merged files:{df.shape}')解析：总体思路是读取每个工作簿，再读取每个工作簿的工作表，list comprehension内循环合并表，外循环合并工作簿glob用于返回符合某个pattern的路径和文件名glob('./*.xlsx') 返回当前目录下的所有Excel文件名python的built-in function sorted()不改变原list，要赋值给新的variable才实现排序pd.read_excel(dir_file, sheet_name=None) 返回dictionary，key是sheet name， value是工作表的数据[v_df.assign(Sheet = k) for k,v_df in dict_xlsx.items()]是list comprehension，通常能简化代码的同时加快代码的运行速度df.assign()是新加一列，记录工作表名称pd.concat([])是纵向合并数据的好方法

count_null_series = df.isnull().sum() # returns seriescount_null_df = pd.DataFrame(data=count_null_series, columns=['Num_Nulls'])# what % of the null values take for that columnpct_null_df = pd.DataFrame(data=count_null_series/len(df), columns=['Pct_Nulls'])null_stats = pd.concat([count_null_df, pct_null_df],axis=1)null_stats

-  时间序列的数据常用df[col_name].fillna(method="ffill",inplace=True)，ffill表示按上一个值填充-  不同列补不同的值df.fillna(value={col1:50, col2:67, col3:100}, inplace=True)-  以当列的平均值弥补空值df.where(pd.notna(df), df.mean(), axis="columns", inplace=True)-  任意选定的列为空就删除该行df.dropna(subset=subset_list, inplace=True)-  当一半的行为空，删除该列df.dropna(thresh=len(df)*N, axis=1, inplace=True)

df.info()df.drop(df.columns[start_ind:stop_ind],axis=1,inplace=True)df.info()

df.rename(columns= {'Order_No_1':'OrderID','ItemNo':'ItemID'}, inplace=True)    # remove special characters from column namedf.columns = df.columns.str.replace('[&,#,@,(,)]', '')# remove leading/trailing space and add _ to in-between spacesdf.columns = df.columns.str.strip().str.replace(' ','_')

for c in ['OrderID','ItemID','Class']:df[c] = df[c].astype('str')

len_df = len(df)len_drop = len(df.drop_duplicates(subset = subset_list))len_diff = len_df-len_dropprint(f'difference of length:{len_diff}')if len_diff>0:    dups = df[df.duplicated(keep=False)].sort_values(by=sort_list) df_drop = df.drop_duplicates(subset=subset_list, keep='last')

# split by comma, retrieve the first columndf['date_com'] = df['date_com'].str.split(',', expand=True)[0] # format要和原日期的格式一致，最后总会返回YYYY-MM-DD HH:MM:SS格式的datetimedf['date_com'] = pd.to_datetime(df['date_com'], format='%Y-%m-%d %H:%M:%S')

 df['Date'] = df['date_com'].dt.date    dt_lst = df['date_com'].str.split(' ', n=1, expand = True)    df['Time'] = dt_lst[1]    # extract hour from Time    time_lst = df['date_com'].str.split(':', n=1, expand = True)    df['Hour'] = time_lst[0] #str