Python大数据分析12：高级子查询

我们准备结合scores成绩数据来查询每个学生考的最好的那门课，即每个学生成绩最高的课程号。

首先需要知道每个学生的最高成绩，然后根据这个成绩再去查询对应的课程号，显然是个子查询。

我们首先查询每位学生的最高分：

# coding:utf-8
import pandas as pd

data2 = {
    'SID': ['000001', '000001', '000002', '000003', '000003', '000003', '000004', '000004', '000005', '000006', '000006'],
    'CID': ['A01', 'A02', 'A01', 'A01', 'A02', 'B01', 'A01', 'A03', 'B01', 'A02', 'B01'],
    'score': [56, 78, 90, 74, 86, 89, 67, 80, 77, 76, 90]
}
scores = pd.DataFrame(data2)
frame1 = scores.groupby("SID")["score"].max()
print(frame1)

这里需要按照学号分组，对每组的成绩取最大值。

接下来需要根据这里得到的每个学生的SID和最高成绩，再去成绩中查询对应的课程，由于还是涉及两个DataFrame，我们再次使用连接：

frame2 = pd.merge(scores, frame1, right_on=’SID’, left_on=’SID’)

print(frame2)

可以看出连接后的结果，每位同学的第二个成绩，score_y都是该生的最高分。显然，再次直接选择哪个成绩等于该生的score_y不就得到该生的最高分对应的课程了吗？

这次直接利用该条件选择行，再选择所需显示的列：

print(frame2[frame2[‘score_x’] == frame2[‘score_y’]][[‘SID’, ‘CID’]])

从中可以看出确实每个学生都得到了最高分所对应的课程。

更为简单的做法是直接按照两个列来连接：

frame2 = pd.merge(scores, frame1, right_on=[‘SID’, ‘score’], left_on=[‘SID’, ‘score’])

print(frame2[[‘SID’, ‘CID’]])

由于使用两个列连接，需要使用列表来表示多个连接列，只有两个列都相等才连接。然后就只需要选择所需的学号和课程号即可。

细心的同学可能会发现，这次结果的索引号是连续的，这是因为连接过程已经将课程分数相等考虑进去了，无需再次选择行，所以所有行都是满足要求的，只需选择所需显示的列。

当然，这里第二步的连接操作也并非必须，巧妙的利用索引也能实现同样的效果。我们来试试第二种方法。

比如首先我们得到每个学生最大成绩行的所在索引号：

# coding:utf-8
import pandas as pd

data2 = {
    'SID': ['000001', '000001', '000002', '000003', '000003', '000003', '000004', '000004', '000005', '000006', '000006'],
    'CID': ['A01', 'A02', 'A01', 'A01', 'A02', 'B01', 'A01', 'A03', 'B01', 'A02', 'B01'],
    'score': [56, 78, 90, 74, 86, 89, 67, 80, 77, 76, 90]
}
scores = pd.DataFrame(data2)
idxMax = scores.groupby("SID")["score"].idxmax()
print(idxMax)

这里的idxmax返回每组score最大值所在的索引号。这个索引号具有唯一性，因此完全可以据此来找到相关的课程。

于是我们以这些索引号将相应的行找到，并有选择的显示其中的学号和课程号。

print(scores[scores.index.isin(idxMax)][[‘SID’, ‘CID’]])

下面我们开始使用第三种方法来尝试下，顺便了解下一些更为高级的方法。

这里的关键是排序，也就是对于每个学生的每门课成绩，如果能够按照从高到低排下序，相应课程的信息都很明显。

我们先来排下序：

# coding:utf-8
import pandas as pd

data2 = {
    'SID': ['000001', '000001', '000002', '000003', '000003', '000003', '000004', '000004', '000005', '000006', '000006'],
    'CID': ['A01', 'A02', 'A01', 'A01', 'A02', 'B01', 'A01', 'A03', 'B01', 'A02', 'B01'],
    'score': [56, 78, 90, 74, 86, 89, 67, 80, 77, 76, 90]
}
scores = pd.DataFrame(data2)
scores = scores.sort_values(by=['SID', 'score'], ascending=[True, False])
print(scores)

从中已经看出每个学生的最高分都排在所在学生记录行的第一条。那么如何把它们取出来呢？如果要取出来，就必须找到这些与其他行的不同。显然，此时最大的不同就是它们都是所在学生记录行的第一条。

为此我们尝试增加一个新的列来标记这些究竟是第几行：

scores[‘rank’] = scores.groupby([‘SID’]).cumcount()

print(scores)

这里是对当前记录按照学号来分组，对于每一组，即每一位学生，cumcount会给出积累的个数，我们将其保存到新列rank中。看的出来，每个学生第一行记录rank都为0。

因此，我们只需再次条件选择即可：

print(scores[scores[‘rank’] == 0][[‘SID’, ‘CID’]])

最后，我们尝试将这个练习改变下，再次看看子查询的方法。

查询选修了A01课程的学生A02课程成绩为多少？

我们不妨看看数据，可以发现目前有两个应该显示的结果，分别是1号和3号学生的A02课程。

我们首先使用标准子查询来做，先看看哪些学生选修A01课程：

# coding:utf-8
import pandas as pd

data2 = {
    'SID': ['000001', '000001', '000002', '000003', '000003', '000003', '000004', '000004', '000005', '000006', '000006'],
    'CID': ['A01', 'A02', 'A01', 'A01', 'A02', 'B01', 'A01', 'A03', 'B01', 'A02', 'B01'],
    'score': [56, 78, 90, 74, 86, 89, 67, 80, 77, 76, 90]
}
scores = pd.DataFrame(data2)
CIDs = scores[scores['CID'] == 'A01']['SID']
print(CIDs)

可以看出有很多学生选修了这门课，我们保存到CIDs中。

然后我们再次查询这些学生的A02课程，此时应该是两个条件的并且组合：

print(scores[(scores[‘SID’].isin(CIDs)) & (scores[‘CID’] == ‘A02’)])

我们再使用连接试一试：

frame = pd.merge(scores, scores, right_on=’SID’, left_on=’SID’)

print(frame)

其中也能发现同时选修了A01和A02的学生。

因此，我们可以再次构造条件来选择这些所需行：

print(frame[(frame[‘CID_x’] == ‘A01’) & (frame[‘CID_y’] == ‘A02’)][[‘SID’, ‘CID_y’]])

这里还是一样的选择条件，选择了行和列。

对于这种行列特别明确的子查询，我们还可以考虑使用透视图。我们先构造透视图：

frame = scores.pivot_table(values=’score’, index=’SID’, columns=’CID’, fill_value=-1)

print(frame)

这里四个参数分别表示单元格、行、列的来源数据列，以及缺失单元格默认填充的数值。

再次选择下：

print(frame.loc[frame[‘A01’] != -1][‘A02’])

表示A01课程不为-1就表示选修有成绩，同时显示A02的列，索引列正好是学号。

当然，为了只显示也选修了A02课程的学生，我们可以再次增加选择条件：

print(frame.loc[(frame[‘A01’] != -1) & (frame[‘A02’] != -1)][‘A02’])

此时的结果就是我们所要的内容。

发表评论

邮箱地址不会被公开。 必填项已用*标注