pandas versus Excel 笔记4（复杂操作）

多表联合查询，数据校验， 一列数据拆分两列，求和，求平均，统计导引，删除重复数据，旋转数据， 制作透视表

多表联合查询

在excel中实现此功能需要VLOOKUP函数

在pandas实现该功能需要merge函数:merge()函数类似于SQL中join的用法，可以将不同数据集依照某些字段（属性）进行合并操作，得到一个新的数据集。
merge()方法介绍

import pandas as pd
import xlrd
students=pd.read_excel('E:/ruanjianDM/pycharm+DM/pandas-Excel/Student_Score.xlsx',sheet_name='Students')
scores=pd.read_excel('E:/ruanjianDM/pycharm+DM/pandas-Excel/Student_Score.xlsx',sheet_name='Scores')
table=students.merge(scores,on='ID')#on表示根据某个字段进行连接
print(table)

不设置on=‘ID’,默认内连接，寻找相同数据列名。

数据校验

excel中数据校验步骤：数据-数据验证-（设置验证条件）-圈释无效数据

apply()函数介绍

import pandas as pd
#try except 对语句 assert 0<=row.Score<=100捕获异常
#\t制表符，为了id对齐
def score_validation(row):
    try:
        assert 0<=row.Score<=100
    except:
        print(f'#{row.ID}\tstudent{row.Name}\thas an invalid score{row.Score}')#f  ！！才能显示{row.ID}
students=pd.read_excel('017/Students.xlsx')
students.apply(score_validation,axis=1)#axis=1表示从左到右，=0表示从上到下

一列数据拆分两列

str是Series的一个类，里面包含很多方法。

import pandas as pd
import xlrd
students=pd.read_excel('E:/ruanjianDM/pycharm+DM/pandas-Excel//Students2.xlsx')
df=students['Name'].str.split("_")
"""
df=students['Name'].str.split('_',expand=True)#加上expand=True
students['First name']=df[0]
students['Last name']=df[1]
students
"""
df

求和，求平均，统计导引

import pandas as pd
import xlrd
students=pd.read_excel('E:/ruanjianDM/pycharm+DM/pandas-Excel//Students3.xlsx')
temp=students[['Test_1','Test_2','Test_3']]
row_sum=temp.sum(axis=1)#axis=1表示从左向右,求每行的和，均值
row_mean=temp.mean(axis=1)
students['Sum']=row_sum
students['Average']=row_mean
col_mean=students[['Test_1','Test_2','Test_3','Sum','Average']].mean()#求每列的均值
col_mean['Name']='Summary'
students=students.append(col_mean,ignore_index=True)
students

删除重复数据，旋转数据

删除重复数据

import pandas as pd
import xlrd
students=pd.read_excel('E:/ruanjianDM/pycharm+DM/pandas-Excel/Students_Duplicates.xlsx')
students.drop_duplicates(subset='Name',inplace=True,keep='first')#keep选择表示删除前面或后面的数据
print(students)

#如果基于多列删除，subset=[‘xx’,‘yy’],给一个list

**注意：**print(dupe.any())是否存在重复数据

获取重复数据

students=pd.read_excel('020/Students_Duplicates.xlsx')#注意不能设置index
dupe=students.duplicated(subset='Name')
dupe1=dupe[dupe=True]#筛选为True的内容，因为dupe本来就是bool值所以代码可以写为dupe=dupe[dupe]
print(students.iloc[dupe1.index])#iloc表示定位

第一个图是dupe，第二个图是筛选出的重复数据

旋转数据表（行列互换）

import pandas as pd
import xlrd
pd.options.display.max_columns=5#5代表显示5列
students=pd.read_excel('E:/ruanjianDM/pycharm+DM/pandas-Excel/Students_Duplicates.xlsx')
students=students[['ID','Name']]
table=students.transpose()
print(table)

制作透视表

方法一

import pandas as pd
import numpy as np
import xlrd
pd.options.display.max_columns=999
orders=pd.read_excel('E:/ruanjianDM/pycharm+DM/pandas-Excel/Orders.xlsx')
orders['Year']=pd.DatetimeIndex(orders['Date']).year#orders增加一列Year
pt1=orders.pivot_table(index='Category',columns='Year',values='Total',aggfunc=np.sum)
#aggregation function用来聚合的函数
print(pt1)

#index 透视的行，columns透视的列，values对原来的需透视操作的列，aggfunc对该列透视的同时执行的操作

方法二

orders['Year']=pd.DatetimeIndex(orders['Date']).year
groups=orders.groupby(['Category','Year'])
s=groups['Total'].sum()
c=groups['ID'].count()
pt2=pd.DataFrame({'sum':s,'count':c})
print(pt2)