Converting Excel tables to visual graphics in Python

As you know, a large part of postgraduate entrance examination is also the ability to collect information. Every year, many people fail to correctly analyze the admission information of each university in the past years, and then fail to choose the right college.

As for the enrollment information of many colleges and universities, it is published in PDF form. For example, the enrollment results of Shenzhen University Telecom in my hand require us to convert PDF into Excel first.

(1)PDF

(2)Excel

With Excel, we can do whatever we want!

start

1. Load Excel table

#coding=utf8
import xlrd
import numpy as np
from pyecharts.charts import Bar
from pyecharts.charts import Pie, Grid
from pyecharts import options as opts

#====================Prepare data====================
# Import Excel file
data = xlrd.open_workbook("C:/List of candidates for master's degree in electronic information in 2020 from school of electronic and information engineering, Shenzhen University.xlsx")
# Load first table
table = data.sheets[0]

2. Extract Excel data

tables = 

def Read_Excel(excel):
# Read the data from line 4, because it is the examinee information from line 4 in this Excel file
for rows in range(3, excel.nrows-1):
dict_ = {"id":"", "name":"", "status":"", "preliminary_score":"", "retest_score":"", "total_score":"", "ranking":""}
dict_["id"] = table.cell_value(rows, 1)
dict_["name"] = table.cell_value(rows, 2)
dict_["status"] = table.cell_value(rows, 3)
dict_["remarks"] = table.cell_value(rows, 4)
dict_["preliminary_score"] = table.cell_value(rows, 5)
dict_["retest_score"] = table.cell_value(rows, 6)
dict_["total_score"] = table.cell_value(rows, 7)
dict_["ranking"] = table.cell_value(rows, 8)
# Filter out candidates who have not been admitted or who have not been admitted to the general scheme
if dict_["status"] == str("Proposed admission") and dict_["remarks"] == str("General plan"):
tables.append(dict_)

Let's print to see if the data is retrieved correctly:

# Perform the above method
Read_Excel(table)
for i in tables:
print(i)

You can see everything goes well.

3. Data segmentation statistics

This step is different from person to person. I just want to make separate statistics for each score segment. You can also do other processing according to your own preferences.

num_score_300_310 = 0
num_score_310_320 = 0
num_score_320_330 = 0
num_score_330_340 = 0
num_score_340_350 = 0
num_score_350_360 = 0
num_score_360_370 = 0
num_score_370_380 = 0
num_score_380_390 = 0
num_score_390_400 = 0
num_score_400_410 = 0
min_score = 999
max_score = 0

# Count the number of each segment
for i in tables:
score = i["preliminary_score"]
if score > max_score:
max_score = score
if score < min_score:
min_score = score

if score in range(300, 310):
num_score_300_310 = num_score_300_310 + 1
elif score in range(310, 320):
num_score_310_320 = num_score_310_320 + 1
elif score in range(320, 330):
num_score_320_330 = num_score_320_330 + 1
elif score in range(330, 340):
num_score_330_340 = num_score_330_340 + 1
elif score in range(340, 350):
num_score_340_350 = num_score_340_350 + 1
elif score in range(350, 360):
num_score_350_360 = num_score_350_360 + 1
elif score in range(360, 370):
num_score_360_370 = num_score_360_370 + 1
elif score in range(370, 380):
num_score_370_380 = num_score_370_380 + 1
elif score in range(380, 390):
num_score_380_390 = num_score_380_390 + 1
elif score in range(390, 400):
num_score_390_400 = num_score_390_400 + 1
elif score in range(400, 410):
num_score_400_410 = num_score_400_410 + 1

# It is convenient to build two tuples for later table building
bar_x_axis_data = ("300-310", "310-320", "320-330", "330-340", "340-350", "350-360", "360-370", "370-380", "380-390", "390-400", "400-410")
bar_y_axis_data = (num_score_300_310, num_score_310_320, num_score_320_330,\
num_score_330_340, num_score_340_350, num_score_350_360,\
num_score_360_370, num_score_370_380, num_score_380_390,\
num_score_390_400, num_score_400_410)

Drawing visual graphics

1. Histogram:

#=====================Histogram=====================
# Build histogram
c = (
Bar
.add_xaxis(bar_x_axis_data)
.add_yaxis("Admission of candidates", bar_y_axis_data, color="#af00ff")
.set_global_opts(title_opts=opts.TitleOpts(title="number"))
.render("C:/Admission data chart.html")
)

2. Pie chart:

#======================Pie chart======================
c = (
Pie(init_opts=opts.InitOpts(height="800px", width="1200px"))
.add("Admission score overview",
[list(z) for z in zip(bar_x_axis_data, bar_y_axis_data)],
center=["35%", "38%"],
radius="40%",
label_opts=opts.LabelOpts(
formatter="{b|{b}: }{c} {per|{d}%} ",
rich={
"b": {"fontSize": 16, "lineHeight": 33},
"per": {
"color": "#eee",
"backgroundColor": "#334455",
"padding": [2, 4],
"borderRadius": 2,
},
}
))
.set_global_opts(title_opts=opts.TitleOpts(title="Admission", subtitle='Made by Wang Hao'),
legend_opts=opts.LegendOpts(pos_left="0%", pos_top="65%"))
.render("C:/Admission pie chart.html")
)

be accomplished!! Is it super intuitive!

By Waao666

Original| https://blog.csdn.net/weixin_40973138/article/details/106190092

Source network, only for learning, if there is infringement, please contact delete.

Don't panic. I have a set of learning materials, including 40 + E-books, 800 + teaching videos, involving Python foundation, reptile, framework, data analysis, machine learning, etc. I'm not afraid you won't learn! https://shimo.im/docs/JWCghr8prjCVCxxK/ Python learning materials

Pay attention to the official account [Python circle].

file

Tags: Programming Excel Python network

Posted on Tue, 16 Jun 2020 23:37:48 -0400 by cybtec