python 制作xlsx 数据透视表 计数 python处理excel数据透视表

前几天，看到了一家互联网公司的一道mysql面试题，觉得在实际应用中还是挺有用的，现在分享给大家。

题目

有三名学生，他们的Linux、MySQL、Java成绩在mysql数据表中，表中字段(id)，(student name)，(course)，(score)，如下图：

id	student_name	course	score
1	张三	linux	85
2	张三	mysql	98
3	张三	python	90
4	李四	linux	72
5	李四	mysql	90
6	李四	python	100
7	王五	linux	85
8	王五	mysql	88
9	王五	python	87

请用一句sql，将上表改为下表格式：

student_name	linux	mysql	python
张三	85	98	90
李四	72	90	100
王五	85	88	87

题目解析

看了这道题，我首先想到的就是，这不就是列转行，excel表格数据处理里面的数据透视表吗，觉得这道题我应该能够拿下。

可是想了一会，通过聚合group by的形式也获取不到理想的效果，该怎么处理呢，之前处理mysql数据进行透视表的情况，还是在python里面用代码实现的，抑或是在excel表格里面直接实现。

用单独的sql语句实现，着实令人犯愁。不过，还是可以实现的哈。

下面来看看怎么实现的呢

mysql实现方法：

如下数据截图：

要想取得有数据透视表的效果，需要采用case when then end语句啦

SELECT student_name,
CASE WHEN course = 'linux' THEN score END,
CASE WHEN course = 'mysql' THEN score END,
CASE WHEN course = 'python' THEN score END
from student_score;

获取到如下的表格，小伙伴们，是不是离成功更近一步了呢，想想，还差点什么呢？

没错，到最后的一步聚合操作了

SELECT student_name,
SUM(CASE WHEN course = 'linux' THEN score END) linux,
SUM(CASE WHEN course = 'mysql' THEN score END) mysql,
SUM(CASE WHEN course = 'python' THEN score END) python
from student_score
GROUP BY student_name;

python的实现方法

分层索引在重塑数据和数组透视表等分组操作中扮演了重要角色

import pandas as pd
from Dbconnection.DataBase import *

sql = """
SELECT student_name,course,score from student_score;
"""

col, res = get_mysql_test().query(sql)
# 建立双分层索引
df = pd.Series(
    [i[2] for i in res],
    index=[[i[0] for i in res], [i[1] for i in res]]
)
df = df.unstack()
df.index.name = 'student_name'
print(df)

# DataFrame的pivot_table方法
df = pd.DataFrame(res, columns=['student_name', 'course', 'score'])
df = df.pivot_table(['score'], index=['student_name'], columns=['course'])
df = df['score'].reset_index()
df.set_index(['student_name'], inplace=True)
df.columns.name = None
print(df)

实现的效果如下图

excel表格的实现方法

点击插入，数据透视表。

出现如下图，对所需要展示的行列分别将名字拖到响应的位置即可。

希望对大家有所帮助，有问题的地方也请大家批评指正，感谢！！