【案例】基于Python的生源数据可视化分析：从Excel处理到动态地图展示

文章目录

需求分析
技术要点
程序流程
一些细节
- 核心代码
- 表格的一些操作
心得体会
代码汇总

需求分析

请设计一个程序，要求能够统计分析分散在不同表格中的数万条信息，以信息中的身份证号码或生源地代码字段为目标字段，统计每一年全国各省份及本省各市州的人数，还需要统计全国各个省份总的人数信息，此外，将每一年本省各市州的人数情况绘制动态时间线地图，实现数据交互。

技术要点

Excel 表格的读取、写入、单元格框线设置、统计图绘制、增加表单。（openpyxl 模块）
动态地图的绘制。（pyecharts 模块）

程序流程

一些细节

核心代码

整个项目代码汇总后虽然有四百余行代码，但是核心代码只有十几行。该代码实现了表格数据的遍历，之所以使用try-except结构，是因为有些数据并不符合字典所规定的键值，故会产生 KeyError 异常。

    # 遍历身份证号码for i in range(sheet.min_row+1, sheet.max_row + 1):id_card_value = str(sheet.cell(i, sfzh_index).value)id_card_value = id_card_value.replace(".", "")try:PROVINCE_COUNT_dict[id_card_value[:2]] += 1TOTAL_PROVINCE_COUNT_dict[id_card_value[:2]] += 1if id_card_value[:2] == "62":GANSU_CITY_COUNT_dict[id_card_value[:4]] += 1TOTAL_GANSU_CITY_COUNT_dict[id_card_value[:4]] += 1except KeyError:print("【KeyError】", id_card_value)

表格的一些操作

# 加载工作簿
workbook = load_workbook(filename=year+'.xlsx')
# 选择工作表
sheet = workbook.active
# 表格行数
sheet.max_row
# 新增表单并设置单元格宽度
ws = wb.create_sheet("string")
ws.column_dimensions['A'].width=20
# 定义边框框线
# 定义边框样式
border_style = Side(border_style="thin", color="FF000000")
border = Border(left=border_style, right=border_style, top=border_style, bottom=border_style)
cell_range = ws["A1:C63"] # 选择单元格范围并应用边框
for row in cell_range:for cell in row:cell.border = border
# 插入柱状图
bar1 = BarChart()
bar1.height = 15
bar1.width = 30
bar1.type = "col"
# bar1.style = 10
bar1.title = "全国各省份数据"
bar1.y_axis.title = "人数"
bar1.x_axis.title = "省份"
labels = Reference(ws, min_col=1, min_row=6, max_row=39)
data = Reference(ws, min_col=2, min_row=5, max_row=39)
bar1.add_data(data, titles_from_data=True)
bar1.set_categories(labels)
ws.add_chart(bar1, "F2")
# 保存表格
wb.save("output.xlsx")

心得体会

在预处数据时，统一的文件名称、文件格式及表格格式会给代码编写带来方便。例如，workbook = load_workbook(filename=year+'.xlsx')，可以在循环中直接使用年份名称的方式循环读取文件名，实现一个变量既指代了文件名，又表达了年份信息的目的。
尝试使用多个字典进行连续索引，保证每个字典仅有一个值。例如，PROVINCE = {"11": "北京市"}，TOTAL_PROVINCE_COUNT_dict = {"11": 0}，在最终需要呈现给用户 北京市:0 的信息时，通过字典的连续索引实现，相比较使用多个值的方式，程序的耦合度更低。

代码汇总

# encoding: utf-8
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Border, Side
from openpyxl.chart import BarChart, LineChart, PieChart, Reference, ProjectedPieChart
from openpyxl.chart.series import DataPointfrom pyecharts.charts import Map, Geo, Timeline
from pyecharts import options as opts
from pyecharts.globals import ThemeTypeYEAR_RANGE = [  "91", "92", "93", "94", "95","96", "97", "98", "99", "00","01", "02", "03", "04", "05","06", "07", "08", "09", "10","11", "12", "13", "14", "15","16", "17", "18", "19", "20","21", "22", "23", "24", "25"]PROVINCE = {"11": "北京市",  "12": "天津市",  "13": "河北省",  "14": "山西省",  "15": "内蒙古自治区",  "21": "辽宁省",  "22": "吉林省",  "23": "黑龙江省",  "31": "上海市",  "32": "江苏省",  "33": "浙江省",  "34": "安徽省",  "35": "福建省",  "36": "江西省",  "37": "山东省",  "41": "河南省",  "42": "湖北省",  "43": "湖南省",  "44": "广东省",  "45": "广西壮族自治区",  "46": "海南省",  "50": "重庆市",  "51": "四川省",  "52": "贵州省",  "53": "云南省",  "54": "西藏自治区",  "61": "陕西省",  "62": "甘肃省",  "63": "青海省",  "64": "宁夏回族自治区",  "65": "新疆维吾尔自治区",  "71": "台湾省",  "81": "香港特别行政区",  "82": "澳门特别行政区"
}GANSU_CITY = {'6201': '兰州市',  '6202': '嘉峪关市',  '6203': '金昌市',  '6204': '白银市',  '6205': '天水市',  '6206': '武威市',  '6207': '张掖市',  '6208': '平凉市',  '6209': '酒泉市',  '6210': '庆阳市',  '6211': '定西市',  '6212': '陇南市',  '6221': '酒泉市',  '6222': '张掖市',  '6223': '武威市',  '6224': '定西市',  '6226': '陇南市',  '6227': '平凉市',  '6228': '庆阳市',  '6229': '临夏回族自治州',  '6230': '甘南藏族自治州'
}TOTAL_PROVINCE_COUNT_dict = {"11": 0,  "12": 0,  "13": 0,  "14": 0,  "15": 0,  "21": 0,  "22": 0,  "23": 0,  "31": 0,  "32": 0,  "33": 0,  "34": 0,  "35": 0,  "36": 0,  "37": 0,  "41": 0,  "42": 0,  "43": 0,  "44": 0,  "45": 0,  "46": 0,  "50": 0,  "51": 0,  "52": 0,  "53": 0,  "54": 0,  "61": 0,  "62": 0,  "63": 0,  "64": 0,  "65": 0,  "71": 0,  "81": 0,  "82": 0}TOTAL_GANSU_CITY_COUNT_dict = {'6201': 0,  '6202': 0,  '6203': 0,  '6204': 0,  '6205': 0,  '6206': 0,  '6207': 0,  '6208': 0,  '6209': 0,  '6210': 0,  '6211': 0,  '6212': 0,  '6221': 0,  '6222': 0,  '6223': 0,  '6224': 0,  '6226': 0,  '6227': 0,  '6228': 0,  '6229': 0,  '6230': 0}# 保存毕业生总数
count_stdudent_1 = 0
count_stdudent_1_list = []
# 明晰生源地的毕业生总数
count_stdudent_2 = 0
# 绘制MAP
t = Timeline(init_opts=opts.InitOpts(width="2000px", height="1000px"))# 保存输出结果
wb = Workbook()# 将每年数据视为sheet，独立统计
for year in YEAR_RANGE:print()print("【年份】:", year)print("===")# 加载工作簿workbook = load_workbook(filename=year+'.xlsx')# 选择工作表sheet = workbook.activeprint("毕业生总数:{}\n".format(sheet.max_row-1))count_stdudent_1 += sheet.max_row-1count_stdudent_1_list.append(sheet.max_row-1)# 获取最小和最大行数# print(year, sheet.min_column, sheet.max_column, sheet.min_row, sheet.max_row)# 跳过如下没有身份证号码的年份if year in ["91", "92", "93", "94", "95", "96", "97", "98", "99", "00", "01"]:continuecount_stdudent_2 += sheet.max_row-1header_list = []sfzh_index = -1# 遍历表头for j in range(sheet.min_column, sheet.max_column + 1):cell_value = sheet.cell(sheet.min_row, j).valueheader_list.append(cell_value)if cell_value == "sfzh" or cell_value == "Syszddm":sfzh_index = j# print("年份：{}\t, 表头长度：{}\t, 表头：{}".format(year, len(header_list), header_list))PROVINCE_COUNT_dict = {"11": 0,  "12": 0,  "13": 0,  "14": 0,  "15": 0,  "21": 0,  "22": 0,  "23": 0,  "31": 0,  "32": 0,  "33": 0,  "34": 0,  "35": 0,  "36": 0,  "37": 0,  "41": 0,  "42": 0,  "43": 0,  "44": 0,  "45": 0,  "46": 0,  "50": 0,  "51": 0,  "52": 0,  "53": 0,  "54": 0,  "61": 0,  "62": 0,  "63": 0,  "64": 0,  "65": 0,  "71": 0,  "81": 0,  "82": 0}GANSU_CITY_COUNT_dict = {'6201': 0,  '6202': 0,  '6203': 0,  '6204': 0,  '6205': 0,  '6206': 0,  '6207': 0,  '6208': 0,  '6209': 0,  '6210': 0,  '6211': 0,  '6212': 0,  '6221': 0,  '6222': 0,  '6223': 0,  '6224': 0,  '6226': 0,  '6227': 0,  '6228': 0,  '6229': 0,  '6230': 0}# 遍历身份证号码for i in range(sheet.min_row+1, sheet.max_row + 1):id_card_value = str(sheet.cell(i, sfzh_index).value)id_card_value = id_card_value.replace(".", "")try:PROVINCE_COUNT_dict[id_card_value[:2]] += 1TOTAL_PROVINCE_COUNT_dict[id_card_value[:2]] += 1if id_card_value[:2] == "62":GANSU_CITY_COUNT_dict[id_card_value[:4]] += 1TOTAL_GANSU_CITY_COUNT_dict[id_card_value[:4]] += 1except KeyError:print("【KeyError】", id_card_value)ws = wb.create_sheet(year)ws.column_dimensions['A'].width=20ws.column_dimensions['B'].width=14ws.append(list(("年份：", year)))ws.append(list(("毕业生总数：", sheet.max_row-1)))ws.append(list(()))print("\n全国各省份数据：")ws.append(list(("全国各省份数据：", "")))ws.append(list(("省份名称", "生源地人数", "人数占比")))print("---")for k, v in PROVINCE_COUNT_dict.items():print("{}\t{}\t{:.2f}".format(PROVINCE[k], v, v/(sheet.max_row-1)))ws.append(list((PROVINCE[k], v, v/(sheet.max_row-1))))ws.append(list(()))print("\n甘肃省内数据：")ws.append(list(("甘肃省内数据：", "")))ws.append(list(("市州名称", "生源地人数", "人数占比")))print("---")MAP_GANSU_PROVINCE_COUNT_dict = {}for k, v in GANSU_CITY_COUNT_dict.items():print("{}\t{}\t{:.2f}".format(GANSU_CITY[k], v, v/PROVINCE_COUNT_dict["62"]))ws.append(list((GANSU_CITY[k], v, v/PROVINCE_COUNT_dict["62"])))MAP_GANSU_PROVINCE_COUNT_dict[GANSU_CITY[k]] = vmap0 = (Map().add("年份", list(MAP_GANSU_PROVINCE_COUNT_dict.items()), "甘肃").set_global_opts(title_opts=opts.TitleOpts(title="甘肃各市州生源地人数"),visualmap_opts=opts.VisualMapOpts(max_=400, min_=0)))t.add(map0, "{} 年".format(year))# 定义边框样式border_style = Side(border_style="thin", color="FF000000")border = Border(left=border_style, right=border_style, top=border_style, bottom=border_style)# 选择单元格范围并应用边框cell_range = ws["A1:C63"]for row in cell_range:for cell in row:cell.border = borderbar1 = BarChart()bar1.height = 15bar1.width = 30bar1.type = "col"# bar1.style = 10bar1.title = "全国各省份数据"bar1.y_axis.title = "人数"bar1.x_axis.title = "省份"labels = Reference(ws, min_col=1, min_row=6, max_row=39)data = Reference(ws, min_col=2, min_row=5, max_row=39)bar1.add_data(data, titles_from_data=True)bar1.set_categories(labels)ws.add_chart(bar1, "F2")bar2 = BarChart()bar2.height = 15bar2.width = 30bar2.type = "col"# bar2.style = 10bar2.title = "甘肃省内数据"bar2.y_axis.title = "人数"bar2.x_axis.title = "市州"labels = Reference(ws, min_col=1, min_row=43, max_row=63)data = Reference(ws, min_col=2, min_row=42, max_row=63)bar2.add_data(data, titles_from_data=True)bar2.set_categories(labels)ws.add_chart(bar2, "F35")# 以下为汇总数据
ws = wb.create_sheet("汇总")
ws.column_dimensions['A'].width=26
ws.column_dimensions['B'].width=14
ws.append(list(("汇总", "")))
ws.append(list(("毕业生总数（02-25）：", count_stdudent_2)))
ws.append(list(()))print("\n全国各省份数据（汇总）")
ws.append(list(("全国各省份毕业生总数：", count_stdudent_2-TOTAL_PROVINCE_COUNT_dict["62"])))
ws.append(list(("省份名称", "生源地人数", "人数占比")))
print("---")
for k, v in TOTAL_PROVINCE_COUNT_dict.items():print("{}\t{}\t{:.2f}".format(PROVINCE[k], v, v/count_stdudent_2))ws.append(list((PROVINCE[k], v, v/count_stdudent_2)))
ws.append(list(()))print("\n甘肃省内数据（汇总）")
ws.append(list(("甘肃省内毕业生总数：", TOTAL_PROVINCE_COUNT_dict["62"])))
ws.append(list(("市州名称", "生源地人数", "人数占比")))
print("---")
for k, v in TOTAL_GANSU_CITY_COUNT_dict.items():print("{}\t{}\t{:.2f}".format(GANSU_CITY[k], v, v/TOTAL_PROVINCE_COUNT_dict["62"]))ws.append(list((GANSU_CITY[k], v, v/TOTAL_PROVINCE_COUNT_dict["62"])))# 定义边框样式
border_style = Side(border_style="thin", color="FF000000")
border = Border(left=border_style, right=border_style, top=border_style, bottom=border_style)
# 选择单元格范围并应用边框
cell_range = ws["A1:C63"]
for row in cell_range:for cell in row:cell.border = borderbar1 = BarChart()
bar1.height = 15
bar1.width = 30
bar1.type = "col"
# bar1.style = 10
bar1.title = "全国各省份数据"
bar1.y_axis.title = "人数"
bar1.x_axis.title = "省份"
labels = Reference(ws, min_col=1, min_row=6, max_row=39)
data = Reference(ws, min_col=2, min_row=5, max_row=39)
bar1.add_data(data, titles_from_data=True)
bar1.set_categories(labels)
ws.add_chart(bar1, "F2")bar2 = BarChart()
bar2.height = 15
bar2.width = 30
bar2.type = "col"
# bar2.style = 10
bar2.title = "甘肃省内数据"
bar2.y_axis.title = "人数"
bar2.x_axis.title = "市州"
labels = Reference(ws, min_col=1, min_row=43, max_row=63)
data = Reference(ws, min_col=2, min_row=42, max_row=63)
bar2.add_data(data, titles_from_data=True)
bar2.set_categories(labels)
ws.add_chart(bar2, "F35")# 逐年人数统计
ws = wb.create_sheet("逐年人数统计")
ws.append(list(("年份", "人数")))
for i in range(len(YEAR_RANGE)):ws.append((YEAR_RANGE[i], count_stdudent_1_list[i]))
line = LineChart() # 折线图
line.x_axis.title = "年份"
line.y_axis.title = "人数"
line.title = "每年毕业生数量"
xlabel = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
line.add_data(data, titles_from_data=True)
line.set_categories(xlabel)
ws.add_chart(line, "D3")wb.save("output.xlsx")
print("毕业生总数: ", count_stdudent_1)
print("明晰生源地的毕业生总数: ", count_stdudent_2)
print("逐年人数：", count_stdudent_1_list)# data=[("广东省",10430.03),("山东省",9579.31),("河南省",9402.36),("四川省",8041.82),("江苏省",7865.99),("河北省",7185.42),("湖南省",6568.37),("安徽省",5950.1),("浙江省",5442),("湖北省",5723.77),("广西壮族自治区",4602.66),("云南省",4596.6),("江西省",4456.74),("辽宁省",4374.63),("黑龙江省",3831.22),("陕西省",3732.74),("山西省",3571.21),("福建省",3552),("重庆市",2884),("贵州省",3476.65),("吉林省",2746.22),("甘肃省",2557.53),("内蒙古自治区",2470.63),("上海市",2301.391),("台湾省",2316.2),("新疆维吾尔自治区",2181.33),("北京市",1961.2),("天津市",1293.82),("海南省",867.15),("香港特别行政区",709.76),("青海省",562.67),("宁夏回族自治区",630.14),("西藏自治区",300.21),("澳门特别行政区",55.23)]t.add_schema(is_auto_play=True, play_interval=1000) # 自动播放1000ms
t.render("gansu_map.html")