As we all know, a large part of the postgraduate entrance examination is also the ability to collect information. Every year there are often a lot of people is in this above stumble, can not correctly analyze the admission information of each big college in previous years, and then failed to choose the right college for examination.

As for the admission information of many colleges and universities, it is published in PDF form, such as the admission results of SHENZHEN Dentsu in my hand, so we need to convert PDF into Excel first.

With Excel, we can do anything we want!

start

1. Load the 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:/ School of Electronic and Information Engineering, Shenzhen University, Proposed admission list for 2020 Master of Electronic and Information Engineering.xlsx")
Load the first table
table = data.sheets[0]
Copy the code

2. Extract Excel data

tables = 

def Read_Excel(excel):
# Start reading data from line 4, because this Excel file only starts from line 4
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 candidates who are not accepted or who are not accepted in regular programs
if dict_["status"] == str("Intended") and dict_["remarks"] == str("General plan"):
tables.append(dict_)
Copy the code

Let’s print to see if we get the data correctly:

Execute the above method
Read_Excel(table)
for i in tables:
print(i)
Copy the code

You can see that everything is going well.

3. Data segmentation statistics

This pace varies from person to person, I just want to undertake each fractional paragraph alone statistic just, everybody also can do other processing according to oneself be fond of.

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 quantity of each section
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

# create two tuples for later table construction
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)
Copy the code

Draw visual graphics

1. Bar chart:

#===================== bar chart =====================
# Build the bar chart
c = (
Bar
.add_xaxis(bar_x_axis_data)
.add_yaxis("Admission candidate", bar_y_axis_data, color="#af00ff")
.set_global_opts(title_opts=opts.TitleOpts(title="The number"))
.render("C:/ admission data graph.html"))Copy the code

2. Pie chart:

# = = = = = = = = = = = = = = = = = = = = = = pie = = = = = = = = = = = = = = = = = = = = = =
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="Acceptance", subtitle='Made by Wang Hao '),
legend_opts=opts.LegendOpts(pos_left="0%", pos_top="65%"))
.render("C:/ admission pie chart.html"))Copy the code

Done!! Isn’t super intuitive haha!

The author | Waao666

The original | blog.csdn.net/weixin_4097…

Wenyuan network, only for the use of learning, if there is infringement please contact delete.

You will definitely encounter difficulties in learning Python. Don’t panic, I have a set of learning materials, including 40+ e-books, 800+ teaching videos, covering Python basics, crawlers, frameworks, data analysis, machine learning, etc. Shimo. Im/docs/JWCghr… Python Learning Materials

Follow the Python circle and get good articles delivered daily.