问卷调查数据整理

工具

1.excel
2.python（pandas，regex)

任务

将由老程序导出的非人类易读的excel表转换为人类易读的格式

主思路

由原材料中所示，任务表是一个具有4k+rows，分多张工作表的工作簿。
我们要把表中数据提取出来，最好是一个具有一个工作表的工作簿，其中每个字段对于一个问题，每一行对应一名学生。
对于数据清洗和分析我们常常使用python的pandas库，本人希望使用dataframe类作为工作表的数据类型。对于提取字符串数据，我们常常使用正则表达式。
因为dataframe对应一张表，对应多表的工作簿必然需要遍历各表。
0.先将excel转换为dataframe对象，再获得原字段和原主数据。
1.先考虑columns，遍历各表，利用正则表达式提取表中有效数据作为新表字段作为newcol列表储存。
2.再考虑rows，我们希望为每位学生提供一行存储数据，但是dataframe函数只能一次性读取一张表，所以需要遍历各表提取数据。将一行数据作为一个list，再将该list作为元素追加到newdata中。
3.regex一次性准确提取有效信息难度太大（考虑到该excel的不规范）。clear函数多次清洗数据。
4.将newcol和newdata作为参数传递给to_excel函数，导出xls数据。

代码

import pandas as pd #引入pandas库,以pd作为别名
import re  #引入正则表达式module
from pandas import DataFrame #导入pandas库中dataframe类作为excel对象载体
def doexl():   #主处理函数，函数式module化的思想，真的很方便
    ncols=getcols()   #获取字段
    ndata=getdata()		#获取主数据
    clear(ndata) #重过滤
    DataFrame(ndata,columns=ncols).to_excel(tar) #导出到tar
def clear(data):	#数据再清洗
    for ii,iv in enumerate(data):#用enumerate函数获取遍历的index和value
        for ji,jv in enumerate(data[ii]):
            if(re.search('^\d+\.',str(jv))):	#去掉开头的1. 2.之类的
                data[ii][ji]=re.search('\d+\.(.*)',str(jv)).group(1)
            if(re.search('.*_t_$',str(jv))):  #去掉结尾_t_
                data[ii][ji]=re.search('(.*)_t_$',str(jv)).group(1)
def getcols():   
    ncols = []
    for i in sheets:#遍历各工作表
        data=pd.read_excel(location,sheet_name=i)
        cols=data.columns.values
        for j in cols:
            if(re.match('\D',j)):
                ncols.append(j)
            else:
                if(re.match('25\.您曾获得以下各类竞赛奖项各多少次',j)):
                    regex=re.search('25\.您曾获得以下各类竞赛奖项各多少次？（可多选）\[多选\]_\d\.(.+)',j)
                    if(regex.group(1) not in ncols):#防止多次添加
                        ncols.append(regex.group(1))
                    continue#结束此次，防止多次匹配
                if(re.search('其他_t_$',j)):
                    regex = re.search('(\d*\..*).\[.选\].*\.([\u4e00-\u9fa5]*)', j)
                    if(regex.group(1) not in ncols) :
                        ncols.append(regex.group(1))
                    continue
                if(re.search('(.*)\[多选\](.*)',j)):
                    regex=re.search('(.*)\[多选\](.*)',j)
                    res=regex.group(1)+regex.group(2) #用加号连接字符串
                    if(res not in ncols):
                        ncols.append(res)
                    continue
                if(re.search('\[填空\]$',j)):
                    regex=re.search('(.*)\[填空\]$',j)
                    if(regex.group(1) not in ncols) :
                        ncols.append(regex.group(1))
                    continue
                if(re.search('(\d*.*).+\[单选\][^\u4E00-\u9FFF]*\W(.*\s*)$',j)):
                    regex=re.search('(\d*.*).+\[单选\][^\u4E00-\u9FFF]*\W(.*\s*)$',j)
                    if(regex.group(1) not in ncols) :
                        ncols.append(regex.group(1))
                    continue
                if(re.search('(\d*.*)_.*\W(.*)$',j)):
                    regex=re.search('(\d*.*)_.*\W(.*)$',j)
                    if(regex.group(1) not in ncols) :
                        ncols.append(regex.group(1))
                    continue
                if(re.search('(.*你认为学校最应该为同学们的成长发展做些什么？\[简答\])',j)):
                    ncols.append(re.search('(.*你认为学校最应该为同学们的成长发展做些什么？\[简答\])',j).group(1))
    return ncols
def getdata():#获得字段列表
    ndata=[]
    data = pd.read_excel(location, sheet_name=sheets[0])
    rows=data.index.values
    for i in rows:#初始化ndata便于后续按索引访问不会越界
        ndata.append([])
    for k in sheets:
        data = pd.read_excel(location, sheet_name=k)
        cols = data.columns.values
        w=0  #row索引，很奇怪的一点，i明明遍历的是rows中元素，但是好像也能当索引使用。
        for i in rows:
            for j in cols:
                if (re.match('\D', j)):
                    ndata[w].append(data[j][i])
                else:
                    if(re.search('77\.请对我校招生工作组老师进行评价。\[单选\]_5\.请在相关方面进行改进_t_',j)):  #特殊问题特殊处理
                        if(data[j][i]!='0' and data[j][i]!=0):  #你永远也不会知道excel数据会变成什么python的ctype，建议强制类型转换
                            ndata[w].append(data[j][i])
                        continue
                    if (re.search('25\.您曾获得以下各类竞赛奖项各多少次？（可多选）\[多选\]_5\.没有获奖', j)):
                        if(data[j][i]!='0' and data[j][i]!=0):
                            ndata[w].append('是')
                        else:
                            ndata[w].append('否')
                        continue
                    if (re.search('25\.您曾获得以下各类竞赛奖项各多少次？（可多选）\[多选\]_\d\.(.+)', j)):
                        ndata[w].append(data[j][i])
                        continue
                    if(re.search('(.*)\[多选\](.*)',j)):
                        if(data[j][i]!=0 and data[j][i]!='0'):
                            ndata[w].append(data[j][i])
                        else:
                            ndata[w].append('无')
                        continue
                    if (re.search('其他.*_t_$', j) or re.search('（请注明）_t_',j)):
                        if (data[j][i] != 0 and data[j][i] != '0'):
                            ndata[w].append(data[j][i])
                        continue
                    if (re.search('\[填空\]$', j)):
                        ndata[w].append(data[j][i])
                        continue
                    if(re.search('73\.请对我校招生工作组老师进行评价。\[单选\]_5\.请在相关方面进行改进_t_',j)):
                        if(data[j][i]!=0 and data[j][i]!='0'):  
                            ndata[w].append(data[j][i])
                        continue
                    if (re.search('(\d*.*).+\[单选\][^\u4E00-\u9FFF]*[、_](.*\s*)$', j)):
                        regex = re.search('(\d*.*).+\[单选\][^\u4E00-\u9FFF]*[、_](.*\s*)$', j)
                        if (data[j][i] == 1):
                            ndata[w].append(regex.group(2))
                        continue
                    if (re.search('(\d*.*)_\d+\.(.*)$', j)):
                        regex = re.search('(\d*.*)_\d+\.(.*)$', j)
                        if (data[j][i] == 1 or data[j][i] == '1'):
                            ndata[w].append(regex.group(2))
                        continue
                    if(re.search('.*你认为学校最应该为同学们的成长发展做些什么？\[简答\]',j)):
                        ndata[w].append(data[j][i])
                    else:
                        ndata[w].append(data[j][i]) #利用else涵盖其余情况
            w=w+1
    return ndata   #def的地方没有类型，全靠return返回
#获得主数据
if __name__=='__main__':  #模块化必备
    tar = 'D:/ans.xls'  # 目标输出文档
    location = 'D:/ask2018.xls'  # 输入文档
    sheets = []
    for i in range(4):
        sheets.append('问卷调查' + str(i + 10))  # 生成处理工作表列表
    doexl()
    exit()

tips

1.记得对元字符转义
2.*?之类的匹配符，默认是贪婪的，其后加入？可取消贪婪
3.写pattern一定要抓住该模式最本质的特点，否则将所有的字符串划分为几种基本类型，分开处理（特殊情况特殊对待
4.不要妄图一次性把数据清洗干净，可多次清洗
5.先在纸上把变量的数据结构，算法，调用的Api和所需参数，自己写的函数和参数设计，理清楚，再写
6.使用enumerate可以在迭代器中访问原数据
7.写注释是为了自己好
8.函数式的编程思想，如clear
9.excel中数字常常以string类型保存，可能需要强制类型转换
10.in 和 not in关键字
11.多用xlsx格式和excel，少用wps和xls格式
12.编程里少用\,会出现转义，其实url里可以用/，只是windows喜欢用\
13.小规模的修改可以自己excel上动手,比如原始表中姓名字段无效，可手动删去，有些特例字段名不符合正则表达式规则，可手动修改字段名使符合规则。
14.对于数据过大，字段数或索引过多，python和excel都会出现错误，python出可以通过traceback功能，按着错误的抛出点寻找问题，最终会发现错误源。此处数据过大报错是to_excel函数考虑excel的性质对文件column数做了限制，仅仅修改库文件也会在excel程序报错，故建议分成小块。
15.注意可扩展性（最早的版本就是没考虑扩展性就重写了个）
16.函数化的编程模式
17.错误往往出现在可见处之前
18.分表处理时，要保证互不干扰，比如把关联的字段删掉。
19.用小数据测试，减少测试等待时间。
20.多用常量代替具体值，减少修改次数，并在主if语句中声明。
21.用+可以进行字符串拼接
22.pycharm快捷键ctrl+r可一行也可批量注释。ctrl+t是取消
shift+tap可以取消一次缩进
23.现在才知道有range函数