实战：运用pandas，正则表达式（re），xlrd，进行多个excel数据汇总，分类

实战：运用pandas，正则表达式（re），xlrd，进行多个excel数据汇总，分类

前言

实习中的第二次实战，相比于第一次处理数据，这一次我更清晰地划分了函数功能，使得开发过程比第一次更快速了（比上次快了半天），尽管还是花了一天半（捂脸）。
第一次实战总结

Input

输入表格

文件夹中的excel文件，格式如下：

蝇类监测记录表（笼诱法）
调查日期：2030 年 4 月
调查地点： YY 省（自治区、直辖市） XX 地（市） ZZZ 县（区）
气温： 12.0 ℃； 风力： 3 级； 天气：晴√ 多云□ 阴□
诱饵种类：规定诱饵√； 其他□：
序号	乡镇(街道)	日期	环境类型	监测地点	布笼数	苍蝇品种1	苍蝇品种2	苍蝇品种3	苍蝇品种4	……	苍蝇品种10	其他	合计	蝇种列合计	备注
1	a村	1.29	农贸市场	第一百菜市场	1	1				……		1	1
2	a村	1.29	居民区	第90中学宿舍	1					……		0	0
3	a村	1.29	绿化带	自然生态公园	1					……		0	0
……										……
8	a村	1.29	餐饮外环境	最好吃餐厅	1	0				……		0	0
9										……
10										……
11										……
……										……
填报单位： zz区天气预报中心 填报人： Sky 审核人：John

Output

调查日期

调查地点

环境类型

诱饵

监测地点

苍蝇品种1

苍蝇品种2

苍蝇品种3

苍蝇品种4

……

苍蝇品种10

苍蝇品种11

苍蝇品种12

其他

经度

纬度

气温（℃）

风力（级）

天气

笼编号

监测单位

监测人

审核人

备注

2016/1/7

福建省福州市闽侯区aa街道（乡镇）

餐饮区外环境

规定诱饵

最好吃餐厅

0

0

0

0

……

0

0

0

0

20

1

晴

有或空

xx中心

Sky

John

2016/2/31

福建省福州市福州大学区bb街道（乡镇）

绿化带

规定诱饵

沧海公园

0

0

0

0

……

0

0

0

0

20

1

晴

有或空

xx中心

Sky

John

2016/3/2

福建省福州市鼓楼区uu街道（乡镇）

农贸市场

规定诱饵

第一市场

0

0

0

0

……

0

0

0

0

20

1

晴

有或空

xx中心

Sky

John

……

……

年-月-日

xx省yy市zz区aa街道（乡镇）

餐饮区外环境

规定诱饵

最好吃餐厅

0

0

0

0

……

0

0

0

0

20

1

晴

有或空

xx中心

Sky

John

对Input和Output的综合分析

结合Output推测从Input中提取的信息

第一行是表头（无实际意义）
第二行是调查日期，其实可以使用正则表达式提取年份和月份，但我直接从文件名种提取了，所以这一行对我也没有用。
第三行是调查地点，在这行需要提取省（自治区、直辖市），地（市）和县（区）
第四行需要提取气温，风力和天气
第五行需要提取诱饵种类
第7行-第8行的每一列都需要提取，不论有信息还是NaN
第9行-倒数第2行为空，需要跳过
最后一行需要提取填报单位，填报人和审核人

注意

1. 使用正则表达式提取别人整理的excel数据肯定会出现各种bug，数据极有可能不像表面上一样规范，所以需要尽可能观察并兼容多的情况
2. 在本表格中，提取出来的日期其实是小数（1.29），需要转换
3. 本表格中有的NaN需要被处理为0，有的不需要处理
4. 在Output表的列顺序与原表不同且有扩充，需要创建新列

函数功能分析

get_file_name(base_path)

比第一次实战总结做出了改进，使用了os.path.join和file.endswith这两个函数

def get_file_name(base_path): # base_path是最基本的文件夹绝对路径
    file_collection = []
    for dir, subDir, files in os.walk(base_path):
        # print(dir,'\t', subDir,'\t', files)
        for file in files:
            in_path = os.path.join(dir, file)  # 替换 in_path = dir + '/' + file
            if file.endswith('xlsx') or file.endswith('xls'):  # 筛选后缀为.xlsx和.xls的文件
                file_collection.append(in_path)
    return file_collection

get_year_month(file)

从文件名中提取年和月，file是文件名（叫file_name更好）

def get_year_month(file): 
    ym = re.findall(r'([0-9]{4}\.?[0-9]{2})', file) #文件名是包含yyyy.mm或yyyymm的格式
    if ym[0][4] == '.':  # 去掉小数点
        ym[0] = ym[0][:4] + ym[0][5:]
    ym[0] = ym[0][:4] + '-' + ym[0][4:] #将格式转为yyyy-mm
    return ym[0] 

set_df_date(value, ym):

value是Input表格中日期列的值，是小数；ym是get_year_month(file)提取的年月，格式为yyyy-mm

def set_df_date(value, ym):
    string = str(value) # 将小数强转为str
    date = string[string.find('.') + 1:]  # 找到日
    date = ym + '-' + date  # 将年月和日结合形成时间：yyyy-mm-dd
    return date

get_sheet_df(file_name, sheet_name, col_names)

file_name为传入的文件名，sheet_name为sheet名，col_names是行数，即从第几行开始是表头（所有列的列名）

def get_sheet_df(file_name, sheet_name, col_names):
    work_book = xlrd.open_workbook(file_name) # 打开excel文件
    sheet = work_book.sheet_by_name(sheet_name) # 根据sheet_name找sheet
    # print(sheet.row_values(col_names)[0])

    df = pd.DataFrame([], columns=sheet.row_values(col_names)) # 根据列名建一个空的DataFrame
    for i in range(col_names + 1, sheet.nrows):
        if sheet.cell_value(i,