数据导入与预处理实验三---excel与python和数据库的交互

一、实验概述： 【实验目的】

1. 掌握Excel数据清理的方法；
2. 初步掌握Python处理Excel的方法
3. 掌握Excel与数据库连接方法；

【实施环境】（使用的材料、设备、软件） Linux或Windows操作系统环境，MySql数据库，Python或其他高级语言

二、实验内容
第1题 找出与指定学校同级别的学校，并统计与指定学校同级别的每个学校出现的次数
【实验过程】

1. 收集数据及数据说明。数据随实验报告给出，文件名为selected_peers.csv。该文件给出了美国2012年与某个大学同级别的大学列表。该数据来自美国政府的报告，并由高等教育编年史整理出版并对公众开放。该数据一共1686行，逗号把数据拆分为两个部分，第一列是目标大学名字，第二列是大学列表。这些大学被当做目标大学的同级别院校。这些同级别院校之间采用管道符（|）进行分隔。如：示例中第1列是目标大学，第2列表示耶鲁，普林斯顿，斯坦福被列为哈弗大学的同级别院校。
2. 抽取数据。抽取与某个特定大学（如Harvard University）同级别的院校数据，并将这些数据高亮显示并删除其余数据；
3. 数据转换。将抽取后的数据转换成若干行，每行显示一个大学；
4. 数据分析。利用高级语言找出与某个特定学校（如Harvard University）为同级别的院校在抽取后的数据中出现的次数。（提示：利用collection的counter，filter，most_commom等）

【实验步骤】（步骤、记录、数据、程序等） 请提供操作步骤及界面截图证明。

import csv
import xlsxwriter
import xlrd
import numpy as np
from collections import Counter

#创建excel
workBook1 = xlsxwriter.Workbook('./collegeData.xlsx')
#创建一个sheet
workSheet = workBook1.add_worksheet('sheet1')

with open("selected_peers.csv","r",encoding="utf-8") as fp:
    reader = csv.DictReader(fp)
    #print(reader)
    i = 0
    for row in reader:
        if((row["selected_peers_concat"].find("Yale University") != -1) | (row["orgname"] == "Harvard University")):
            #print(row)
            collegeName1 = str(row["orgname"])
            collegeName2 = str(row["selected_peers_concat"])
            workSheet.write(i, 0, collegeName1)
            i = i + 1
            #print(collegeName2.split("|"))
            for data in collegeName2.split("|"):
                workSheet.write(i,0,data)
                i = i + 1
workBook1.close()
colData = xlrd.open_workbook('collegeData.xlsx')
table = colData.sheets()[0]#通过索引顺序获取工作表
nrows = table.nrows
result1 = []
result2 = []
for i in range(nrows):
    #result1.append(table.row_values(i))
    result1 = table.row_values(i)#将每一个小列表转换为字符串类型
    result1 = ''.join([str(j) for j in result1])
    #print(type(result1))
    if result1 == 'Harvard University':#不计算Harvard University
        continue
    result2.append(result1)
print(result2)
appearNum = Counter(result2)
top = appearNum.most_common(len(appearNum))
#按从大到小顺序排序
for row in top:
    print(row[0]+"出现的次数为:"+str(row[1]))

首先从整张表中抽取含有Harvard University的行，然后将该行中每所高校转换成单独一行存到excel中

利用Counter计算每所高校在整个文件中出现的次数

第2题 Excel与数据库的数据交互
【实验过程】
1.背景描述：即使当今时代我们拥有了类似微软水晶报表之类的强大报表工具和其他一些灵活的客户管理应用工具，众多企业在分析诸如销售统计和收入信息的时候，微软的Excel依然是最常用的工具。这当然不是没有理由的。Excel以其强大丰富的各种功能，已经成为办公环境中不可或缺的工具。然而，现在公司正在逐渐地将数据开始存储在远程数据库中，这样可以供企业员工从不同的地方来阅读和修改数据。但是，以前固有的工作流程习惯是很难打破的。当你的老板需要从远端使用Excel以饼图的形势来看一下最近的销售数据的时候，你该怎么办？或许你没有想到，你可以将Excel连接到一个数据库，而且不仅仅限于微软的SQLServer。实际上Excel可以连接到所有的主流数据库，诸如MySQL、PostgreSQL和Oracle等等，只要该数据库提供ODBC驱动就可以。本实验将以MySQL为例，来实现将Excel连接到MySQL。我们将连接Excel到一个名为stock的MySQL表，其中包含日期，开盘，最高，最低，收盘，成交量，成交额信息。
2. 主要步骤：（详细步骤见PPT） a.安装MySql ODBC（注意是32位）。 b.配置ODBC，并测试是否连接成功。 c.在MySQL创建一张表，表的模式为： stock（日期，开盘，最高，最低，收盘，成交量，成交额信息）
d.在表中增加股票代码和股票名称两列 e. 修正原始数据文件，将股票编码，股票名称做为一个列信息，方便把多个股票的信息集成在一个表中;
f.将数据导入MySQL中，（如果中文出现问题，股票名称可用拼音代替） e. 连接Excel到MySQL:
打开Excel，转到菜单上的“数据”，然后选择导入外部数据，导入 数据。 从出现的窗口中，选择“连接到新数据源.odc”。
一个标题为“欢迎使用数据连接向导”的窗口出现，从这个窗口中选择“ODBC DSN”。从标题为“连接到ODBC数据源”的窗口中，选择你刚才创建的MySQL ODBC数据源。从标题为“选择数据库和表”的窗口中，选择相应的你要连接的数据库和表，如图3所示，选择好表后，点击下一步。在标题为“保存数据连接文件并结束”的最后一个窗口中，选择结束按钮。
你将被询问在什么地方存储这个数据，你可以选择一个已有的工作表或者指定创建一个新的工作表中。选择后点击确认（OK）按钮。 g.在连接属性中输入SQL语句对数据进行操作。

【实验过程】（步骤、记录、数据、程序等） 请提供操作步骤及界面截图证明。
1.navicat中创建数据库和表结构，编码方式选择gb2312

create table stock 
(rowid int not null auto_increment, 
cdate varchar(10) not null,
copen decimal not null,
chigh decimal not null,
clow decimal not null,
cclose decimal not null,
camount long not null,
camountmoney long not null,
stocknum VARCHAR(45) not null,
stockname VARCHAR(45) null,
primary key(rowid));

2.将export文件夹下所有txt内容处理后增添到同一个csv文件中

import os
import csv
#创建文件对象
f = open('stockData.csv','w',encoding='gb2312',newline="")
#基于文件对象构建 csv写入对象
csv_writer = csv.writer(f)
#构建列表头
csv_writer.writerow(["日期","开盘","最高","最低","收盘","成交量","成交额","股票代码","股票名称"])
path = "./export/"
files=os.listdir(path)
row_value = []
for file in files:
    position = path+file
    #print(position)
    with open(position,"r",encoding="gbk") as fp:
        lines = fp.readlines()
        x = lines[0].split(" ")
        #print(x[0]+x[1])
        # print(lines[0])
        # 写入文件内容
        # csv_writer.writerow(["w", '22', '女'])
        for line in lines[2:len(lines)-1]:#去掉前两行和最后两行的无关数据
            #print(line)
            newList = "\t"+x[0]+"\t"+x[1]
            #print(newList)
            newLine = line.replace('\n',newList)
            #print(newLine)
            row_value.append(newLine)
#print(row_value)
for row in row_value:
    #print(row.split("\t"))
    csv_writer.writerow(row.split("\t"))

#关闭文件
f.close()

3.将该csv文件导入到数据库中，注意选择编码为gb2312



4.本地设置ODBC数据源

由于我的office为32位，所以选择对应位数的ODBC