python多表头处理 python 多表查询

Panda实现一表数据关联查询多表数据

背景：

接到一个任务，要把下面excel里面的内容全部填写完成，而数据来自于其他的文件夹下excel表格

需要完善的Excel：

数据来源：

所以手工查找的话，量大的同时又麻烦。

手工查询

这里我们讨论一下手工查找方法：

1.在该表中，我们可以知道姓名字段是主键（每一行都有姓名，同时是唯一的），我们先记下第一行要查询人的姓名，和年级班级。

2.到根目录下根据年级查找对应年级文件夹，并进入

3.到达年级文件夹下后，就可以看见每个班的Excel表，找到对应班级进入

4.进入表中后就可以根据刚刚的姓名查找到对应记录（行），然后找对应字段下的分数。也就是图中对应德育总分的字段。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M21ZuNdS-1586862250526)(C:\Users\Administrator\Desktop\桌面杂项\小结\微信图片_20200414172717.jpg)]

5.这样子一个人的信息就查询完了，然后重复以上操作，直到结束。

We don’t repeat ourselves

我们把手工操作在脚本编写中简化为三步操作并实现

1.遍历要填写的数据表，获得一行的数据（名字、年级班级）

2.根据一行的数据年级班级进入到对应的文件下，并打开Excel

3.进入Excel后，遍历Excel的所有数据，获得每一行的数据（姓名、德育分），当名字和我们要找的名字相同时，就返回当前的行的德育分。

全部代码

import pandas as pd
import os

def changeName(name):
    if len(name)==2:
        return name[0:1]+'  '+name[1:2]
    return name
#定义根目录的字典
RootUrlDict={'2017':'17德育总汇','2018':'18德育总汇'}

def getRootUrl(classinfo):
    if classinfo in RootUrlDict.keys():
        return RootUrlDict.get(classinfo)
    else:
        return -1

# 遍历根目录下的所有excel文件，查询名字为name的score：name:查询人姓名    Rooturl:为查询的根目录
def getStuScores(name,Rooturl):
    if Rooturl==-1:
        print('外键不存在')
        return
    fileLists = os.listdir(Rooturl)
    for file in fileLists:
        try:
            fileData = pd.read_excel(Rooturl+'/'+file, header=5, usecols=[2, 6],names=['名字','分数'])
            fileDatas=fileData.values
            for fileData in fileDatas:
                if(name==str(fileData[0])):
                    return fileData[1]
        except:
            print('Error========>'+file)

data=pd.read_excel("2019—2020第二学期拟发展名单（基础条件第一轮筛选）.xlsx", header=1,usecols=[4,5,6,7,8,25,28],)
datas=data.values
newData=[]
for data in datas:
    if data[0]>89:
        break
    newRowData=[]
    newRowData.extend(data)
    score=getStuScores(data[1],getRootUrl(data[3][0:4]))
    if score is None:
        score=0
    newRowData.extend([score])
    newData.append(newRowData)

dt = pd.DataFrame(newData,columns=['序号','姓名','性别','班级','入党申请时间','第一次分数','第一次排名','读取分数'])
print(dt)
dt.to_excel("result_xlsx2.xlsx", index=0)

代码详解

（1）遍历查询数据

data=pd.read_excel("2019—2020第二学期拟发展名单（基础条件第一轮筛选）.xlsx", header=1,usecols=[4,5,6,7,8,25,28],)
datas=data.values
newData=[]
for data in datas:
    if data[0]>89:
        break
    newRowData=[]
    newRowData.extend(data)
    score=getStuScores(data[1],getRootUrl(data[3][0:4]))
    if score is None:
        score=0
    newRowData.extend([score])
    newData.append(newRowData)
#保存数据至Excel
dt = pd.DataFrame(newData,columns=['序号','姓名','性别','班级','入党申请时间','第一次分数','第一次排名','读取分数'])
dt.to_excel("result_xlsx2.xlsx", index=0)

1.读取要查询的数据：

data=pd.read_excel("2019—2020第二学期拟发展名单（基础条件第一轮筛选）.xlsx", header=1,usecols=[4,5,6,7,8,25,28],)

结合最图，我们要取的是表的4、5、6、7、8、25、28列的字段数据，表的头为1，注意：计数从小标0开始

2.我们新建一个数组存放所有数据，之后遍历每一行的数据，那么在循环体中获得的data就是一个一维数组

我们进行以下两步操作：

1.我们再新建一个用来存放每行数据的数组，然后将一维数组用extend插入到新的行数组中

2.利用一维数组的下标取到姓名和年纪班级，获得后将数据代入到查询德育分的函数中，函数返回结果后，插入到新建的行数组中

这样行数组就包含了新的查询到的德育分数据，最后再每次遍历行时，append插入到新的数组中，形成二维数组。

关于append和extend的区别

a=[1,2,3]
b=[]

a.append(b)
a.extend(b)

#a结果[1,2,3,[5,6,7]]
#b结果[1,2,3,5,6,7]

3.对文件进行保存

dt = pd.DataFrame(newData,columns=['序号','姓名','性别','班级','入党申请时间','第一次分数','第一次排名','读取分数'])
dt.to_excel("result_xlsx2.xlsx", index=0)

这里columns可以设置字段名

（2）根据年级获得Url

#定义根目录的字典
RootUrlDict={'2017':'17德育总汇','2018':'18德育总汇'}

def getRootUrl(classinfo):
    if classinfo in RootUrlDict.keys():
        return RootUrlDict.get(classinfo)
    else:
        return -1

此处定义Url我们的目录结构是这样子的（文件名字乱取的懒得改）

-test.py
-17德育总汇
--17电商1班德育量化考核成绩汇总表.xls.xls
--17电商1班德育量化考核成绩汇总表.xls.xls
--17电商1班德育量化考核成绩汇总表.xls.xls
--17电商1班德育量化考核成绩汇总表.xls.xls
--17电商1班德育量化考核成绩汇总表.xls.xls
--17电商1班德育量化考核成绩汇总表.xls.xls
-18德育总汇
--18电商1班德育量化考核成绩汇总表.xls.xls
--18电商1班德育量化考核成绩汇总表.xls.xls
--18电商1班德育量化考核成绩汇总表.xls.xls
--18电商1班德育量化考核成绩汇总表.xls.xls
--18电商1班德育量化考核成绩汇总表.xls.xls
--18电商1班德育量化考核成绩汇总表.xls.xls

在遍历要查询数据时，此处传入的是查询目标的年级班级信息，如：2017信息1班XX，所以前四个字符可以用来做索引，所以该方法实现是从事先定义的索引字典中取Url，并返回。

（3）遍历年级文件夹下所有的表查询

为什么要遍历所有而不直接根据班级访问对应班级excel呢？因为这里每个班excel的命名方式不规范，编写代码困难，而且同名学生不存在，所以可以直接遍历文件夹下所有excel

# 遍历根目录下的所有excel文件，查询名字为name的score：name:查询人姓名    Rooturl:为查询的根目录
def getStuScores(name,Rooturl):
    if Rooturl==-1:
        print('外键不存在')
        return
    fileLists = os.listdir(Rooturl)
    for file in fileLists:
        try:
            fileData = pd.read_excel(Rooturl+'/'+file, header=5, usecols=[2, 6],names=['名字','分数'])
            fileDatas=fileData.values
            for fileData in fileDatas:
                if(name==str(fileData[0] or changeName(name)==str(fileData[0]))):
                    return fileData[1]
        except:
            print('Error========>'+file)

这里说明一下changename，因为有些人的名字是两个，因为表格做的不规范，两字的名字存在两种情况

例如：李文 和 李 文 这两个对计算机来说是不一样的，所以要兼容掉。