利用Python分析复旦大学近五年转专业失败的情况

昨天在实验室闲着无聊，看见我旦今年的转专业名单放了出来。突发奇想想用Python试着分析一下转专业的情况。说干就干。

首先得用到处理excel文件的库，常用的是xlwt, xlrd, openpyxl。xlwt和xlrd是相对较老的版本，分别对应写入和读取，openpyxl则同时支持写入和读取。

首先安装三个包

$ pip3 install xlwt$ pip3 install xlrd$ pip3 install openpyxl

接下来直接贴代码

利用xlrd, xlwt的代码

import xlrdimport xlwtFAILED = ["生物科学", "生态学", "环境科学", "化学", "应用化学", "材料化学", "材料物理", "电子科学与技术"]def open_excel(file): try: data = xlrd.open_workbook(file) return data except Exception as e: print(e)def process(file): data = open_excel(file)  table = data.sheets()[0] # Get the first sheert nrows = table.nrows # number of rows ncols = table.ncols # number of cols out_major_dict = {} # record "转出专业" in_major_dict = {} # record "转入专业" failed = {} # 失败专业 failed.setdefault("转出", 0) failed.setdefault("转入", 0) for row in range(1, nrows): if table.row_values(row)[3] == "转出专业": # exclude head rows continue out_major = table.row_values(row)[3] # 转出专业 in_major = table.row_values(row)[4] # 转入专业 if out_major in out_major_dict: out_major_dict[out_major] += 1 else: out_major_dict.setdefault(out_major, 1) if in_major in in_major_dict: in_major_dict[in_major] += 1 else: in_major_dict.setdefault(in_major, 1) if out_major in FAILED: failed["转出"] += 1 if in_major in FAILED: failed["转入"] += 1 print("*" * 20, "转出专业情况", "*" * 20) for key, value in sorted(out_major_dict.items(), key=lambda item: item[1], reverse=True): print("{0}: {1}".format(key, value)) print("*" * 20, "转入专业情况", "*" * 20) for key,value in sorted(in_major_dict.items(), key=lambda item: item[1], reverse=True): print("{0}: {1}".format(key, value)) print("*" * 20, "失败专业情况", "*" * 20)  for key in failed.keys(): print("{0}: {1}".format(key, failed[key]))

利用openpyxl的代码

from openpyxl import load_workbookfrom openpyxl import WorkbookFAILED = ["生物科学", "生态学", "环境科学", "化学", "应用化学", "材料化学", "材料物理", "电子科学与技术"]def open_excel(file): try: wb = load_workbook(file) ws = wb.get_active_sheet() content = [] for row in ws.rows: line = [cell.value for cell in row] content.append(line) return content except Exception as e: print(e)def process(file): content = open_excel(file)  out_major_dict = {} # record "转出专业" in_major_dict = {} # record "转入专业" failed = {} # 失败专业 failed.setdefault("转出", 0) failed.setdefault("转入", 0) for line in content: if line[3] == "转出专业": # exclude head rows continue out_major = line[3] # 转出专业 in_major = line[4] # 转入专业 if out_major in out_major_dict: out_major_dict[out_major] += 1 else: out_major_dict.setdefault(out_major, 1) if in_major in in_major_dict: in_major_dict[in_major] += 1 else: in_major_dict.setdefault(in_major, 1) if out_major in FAILED: failed["转出"] += 1 if in_major in FAILED: failed["转入"] += 1 print("*" * 20, "转出专业情况", "*" * 20) for key, value in sorted(out_major_dict.items(), key=lambda item: item[1], reverse=True): print("{0}: {1}".format(key, value)) print("*" * 20, "转入专业情况", "*" * 20) for key,value in sorted(in_major_dict.items(), key=lambda item: item[1], reverse=True): print("{0}: {1}".format(key, value)) print("*" * 20, "失败专业情况", "*" * 20) for key in failed.keys(): print("{0}: {1}".format(key, failed[key])) return out_major_dict, in_major_dict, faileddef write(filename="output_2018", sheetname="2018", sheetnumber=1, *content_dict): wb = Workbook() ws = wb.get_active_sheet() ws.title = sheetname row = 1 col = 1 for dct in content_dict:  for key, value in sorted(dct.items(), key=lambda item: item[1], reverse=True): ws.cell(row, col).value = key ws.cell(row, col+1).value = value row += 1 wb.save(filename=filename)

分析结果

无需赘言，数据说明一切。

写在最后

前几天有私信小编要Python的学习资料，小编整理了一些有深度的Python教程和参考资料，从入门到高级的都有，文件已经打包好了，正在学习Python的同学可以下载学习学习。文件下载方式：点击小编头像，关注后私信回复“资料”即可下载。首先把代码撸起来！首先把代码撸起来！首先把代码撸起来！重要的事说三遍，哈哈。“编程是门手艺活”。什么意思？得练啊。