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!
start1. 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=": } %} ", 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].
