一、写在前面

终于在【简书】开始写第一篇了。

小白我呢是一个对数据分析很有兴趣的人，当然，看名字就知道是个小白啦，目前刚刚学习了一点点的python和机器学习的知识。在学习过程中，我发现遇到的很多问题都够在一个叫【简书】的平台上找到答案，并且发现了非常多别人的文章做得一些很有价值的总结和理解，小白对【简书】那是颇有好感啊，于是想将【简书】作为自己学习的一个记录平台，一方面是督促自己学习，另一方面希望能将平时遇到的一些问题记录下来。

相信不少小伙伴会和我一样，在刚开始学习python的时候有这么一个问题：学的时候感觉不难，一旦真的让你去做一个实际的数据清洗，你就发现这也不会那也不会，即使都是些让你用excel能轻松解决的简单功能。我发现大部分的教程都是讲解每一个代码功能的时候用一个单独创建的小例子来讲解，私以为这其实是非常不利于融会贯通的。在这里，我记录一下我刚学python数据清洗的时候自己用来练手的一个小例子：用python去实现sql的功能（SQL面试经典50题，这个大家都可以在网上找到，我这里做了前20题）。

二、导入数据

导入要使用的包

import pymysql              # 连接mysql数据库
import numpy as np
import pandas as pd

定义数据库查询函数：

def query(sql):
    # 连接数据库
    conn=pymysql.connect(host='localhost',user='root',passwd='xxxxxx',db='school',charset='utf8')
    # 创建游标
    cur=conn.cursor(cursor=pymysql.cursors.DictCursor)
    cur.execute(sql)
    # 获取所有数据
    result=cur.fetchall()
    # 关闭游标和连接
    cur.close()
    conn.close()
    return result

从mysql读入数据：

student=pd.DataFrame(query('select * from student'))
course=pd.DataFrame(query('select * from course'))
teacher=pd.DataFrame(query('select * from teacher'))
score=pd.DataFrame(query('select * from score'))

我们可以来看一看这四张表的内容：

# 学生信息表
student

学生信息表

# 课程信息表
course

课程信息表

# 教师信息表
teacher

教师信息表

# 成绩信息表
score

成绩信息表

生成一张学生成绩查询总信息表：

total=student.merge(score,on='s_id',how='left').merge(course,on='c_id',how='left').merge(teacher,on='t_id',how='left')
# 将成绩列改为int类型
total['score']=total['score'].fillna(0)
total['score']=total['score'].apply(lambda x:int(x))

生成学生成绩总表：

stuScore=student.merge(score.loc[score['c_id']=='01',:],on='s_id',how='left') \
          .merge(score.loc[score['c_id']=='02',:],on='s_id',how='left') \
          .merge(score.loc[score['c_id']=='03',:],on='s_id',how='left')
stuScore=stuScore.iloc[:,[0,1,2,3,5,7,9]]
stuScore.rename(columns={'score_x':'01','score_y':'02','score':'03'},inplace=True)
# 将分数列改为整型
for i in ['01','02','03']:
    stuScore[i]=stuScore[i].fillna(0)
    stuScore[i]=stuScore[i].apply(lambda x:int(x))
# 增加总分列和平均分列
stuScore=stuScore.assign(sum=stuScore['01']+stuScore['02']+stuScore['03'],avg=(stuScore['01']+stuScore['02']+stuScore['03'])/3)
stuScore

学生成绩表

三、开始做题

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

table=stuScore[stuScore['01']>stuScore['02']]
table

image.png

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

table=stuScore[stuScore['01']<stuScore['02']]
table

image.png

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

table=stuScore.copy()
table[table['avg']>=60]

image.png

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

table=stuScore.copy()
table[table['avg']<60]

image.png

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

c_num=score['score'].groupby(score['s_id']).count().to_frame().reset_index()
table=stuScore.merge(c_num,on='s_id',how='left')
table.rename(columns={'score':'c_num'},inplace=True)
table['c_num']=table['c_num'].fillna(0)
table['c_num']=table['c_num'].apply(lambda x:int(x))
table

image.png

6、查询"李"姓老师的数量

teacher['flag']=teacher['t_name'].str.find('李')
t1=teacher[teacher['flag']==0]
print('\"李"姓老师数量为：',len(t1))

image.png

7、查询学过"张三"老师授课的同学的信息

total.loc[total['t_name']=='张三',['s_id','s_name','s_sex','s_age']]

image.png

8、查询没学过"张三"老师授课的同学的信息

l1=total[total['t_name']=='张三'].drop_duplicates()['s_id'].values.tolist()
table=student[-student['s_id'].isin(l1)]
table

image.png

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

s1=score[score['c_id']=='01']['s_id'].values   # 选修了01课程的学生学号数组
s2=score[score['c_id']=='02']['s_id'].values   # 选修了02课程的学生学号数组
a=list(np.intersect1d(s1,s2))                  # 两个数组取交集，就是两门课都选修了的学生学号列表
student[student['s_id'].isin(a)]

image.png

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

b=list(np.setdiff1d(s1,np.intersect1d(s1,s2)))                  # 从s1中去掉s1、s2的交集
student[student['s_id'].isin(b)]

image.png

11、查询没有学全所有课程的同学的信息

# 对总表进行聚合，将每个学生选修的课程合并为字符串进行比较
table=total[['s_id','s_name','s_sex','s_age','c_id']]
table=table.where(table.notnull(),'')
course_id=table.groupby(['s_id','s_name','s_sex','s_age']).apply(lambda x:''.join(x['c_id'].tolist()))
table=pd.DataFrame(course_id)
table.rename(columns={0:"course_id"}, inplace = True)
table=table.reset_index()
table[-(table['course_id']=='010203')]

image.png

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

# 得到01这个同学选修课的列表
s=score.loc[score['s_id']=='01']['c_id'].values.tolist()
# 在总表中查找学过这些课程的同学信息
table=total[total['c_id'].isin(s)][['s_id','s_name','s_sex','s_age']].drop_duplicates()
table

image.png

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

# 对总表进行聚合，将每个学生选修的课程合并为字符串进行比较
table=total[['s_id','s_name','s_sex','s_age','c_id']]
table=table.where(table.notnull(),'')
course_id=table.groupby(['s_id','s_name','s_sex','s_age']).apply(lambda x:''.join(x['c_id'].tolist()))
table=pd.DataFrame(course_id)
table.rename(columns={0:"course_id"}, inplace = True)
table=table.reset_index()
table[table['course_id']=='010203'][['s_id','s_name','s_sex','s_age']]

image.png

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

l1=total[total['t_name']=='张三'].drop_duplicates()['s_id'].values.tolist()
table=student[-student['s_id'].isin(l1)]
table

image.png

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

# 将stuScore表复制一份
stuScore1=stuScore.copy()
# 将分数大于60的设为1，分数小于60的设为0
for i in ['01','02','03']:
    stuScore1[i]=np.where(stuScore1[i]>=60,1,0)
stuScore1['flag']=stuScore1['01']+stuScore1['02']+stuScore1['03']
stuScore1[stuScore1['flag']<2][['s_id','s_name','avg']]

image.png

16、检索"01"课程分数小于60，按分数降序排列的学生信息

stuScore1=stuScore.copy()
stuScore1[stuScore1['01']<60].sort_values(by='01',ascending=False).iloc[:,:5]

image.png

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

stuScore1=stuScore.copy()
stuScore1.sort_values(by='avg',ascending=False)

image.png

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

-------及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

# 定义计算及格率、中等率、优良率、优秀率的函数
def pass_rate(x):
    y=x.values
    m=len(y)
    n=len(y[np.where(y>=60)])
    return n/m
def ordinary_rate(x):
    y=x.values
    m=len(y)
    n=len(y[np.where( (y>=70) & (y<80) )])
    return n/m
def good_rate(x):
    y=x.values
    m=len(y)
    n=len(y[np.where( (y>=80) & (y<90) )])
    return n/m
def perfect_rate(x):
    y=x.values
    m=len(y)
    n=len(y[np.where(y>=90)])
    return n/m

score['score']=score['score'].apply(lambda x:int(x))
score.groupby(score['c_id']).apply(lambda x:x.sort_values(by='score',ascending=False))

image.png

score.groupby(['c_id','s_id']).max().unstack()

image.png

score['score']=score['score'].apply(lambda x:int(x))
#广播使用的函数
def make_cut(data):
    s = pd.Series(pd.cut(data['score'], np.array([0,59,69,79,89,100])))
    return pd.groupby(s, s).count()
#进行分组处理
score.groupby(score['c_id']).apply(make_cut)

image.png

smax=score['score'].groupby(score['c_id']).max()
smin=score['score'].groupby(score['c_id']).min()
ssum=score['score'].groupby(score['c_id']).sum()
smean=score['score'].groupby(score['c_id']).mean()
spass=score['score'].groupby(score['c_id']).apply(pass_rate)
sordinary=score['score'].groupby(score['c_id']).apply(ordinary_rate)
sgood=score['score'].groupby(score['c_id']).apply(good_rate)
sperfect=score['score'].groupby(score['c_id']).apply(perfect_rate)
table=pd.DataFrame({'max':smax,'min':smin,'sum':ssum,'avg':smean,\
                    'pass':spass,'ordinary':sordinary,'good':sgood,'perfect':sperfect})
table=table.reset_index()
table[['c_id','max','min','sum','avg','pass','ordinary','good','perfect']]

image.png

19、按各科成绩进行排序，并显示排名

table.sort_values(by='sum',ascending=False)[['c_id','sum']]

image.png

20、查询学生的总成绩并进行排名

stuScore.sort_values(by='sum',ascending=False)[['s_id','s_name','sum']]

image.png