基于Python的Excel道路施工年度时长统计折线图实现
道路施工时长统计图表制作需求
现有数据基础
已通过自定义Python脚本生成宽表格式Excel文件,表格结构规则如下:
- 纵轴维度:存储所有待统计的道路名称
- 横轴维度:按时间顺序排列的采样日期(覆盖2018、2019、2020三个年份的多时间节点)
- 单元格内容:标记对应道路在特定采样日期下的施工状态、施工原因
核心实现需求
- 图表横轴按年份维度(2018、2019、2020...)做分组聚合
- 每个年份分组下需计算两类核心统计指标:
- 单条道路在对应年份内的最长连续施工时长
- 对应年份所有样本道路的平均施工时长
- 基于上述两类指标绘制可直观跨年份对比的折线统计图表
当前实操障碍
作为Excel操作新手,确认Excel内置功能可支撑该统计计算、可视化需求,但缺乏可直接落地的分步操作指引,需要可执行的实现路径。
附:已完成的数据处理Python脚本
import re import time startTime = time.time() import collections import xlsxwriter as xlswr import scipy.spatial as spy from itertools import islice from itertools import groupby from natsort import natsorted from functools import partial from collections import Counter from datetime import date as DATE from indexed import IndexedOrderedDict from multiprocessing.dummy import Pool as ThreadPool import multiprocessing as mp workBook = xlswr.Workbook("testfix.xlsx") cell_format = workBook.add_format() format1 = workBook.add_format({'num_format': 'mm/dd/yy'}) sheet = workBook.add_worksheet() def to_raw(string): return fr"{string}" def cvrt(x): ans = re.split(r'(\d+)(?!.*\d)', x) return int(ans[1]) def indexer(s): pattern = re.compile(r'I, [0-9]+, ') gm = re.split(pattern, s); values = s.rsplit(gm[1]) gm = gm[1] values[1] = gm return values def int2Date(x): string = str(x) Y = int(string[0:4]) M = int(string[4:6]) D = int(string[6:8]) return DATE(Y,M,D) def dDelta(x, y): string1 = str(x) string2 = str(y) Y1 = int(string1[0:4]) M1 = int(string1[4:6]) D1 = int(string1[6:8]) Y2 = int(string2[0:4]) M2 = int(string2[4:6]) D2 = int(string2[6:8]) f_date = DATE(Y1,M1,D1) l_date = DATE(Y2,M2,D2) delta = l_date - f_date if isinstance(y, int): return float(int((delta.days)/30.44)) else: return int((delta.days)/30.44) def Book(path): file = open(path,'r') lines = file.readlines() file.close() book = IndexedOrderedDict() for line in lines: if re.match("I", line): IDs = indexer(line)[1] if re.match(" 0.00,", line): rID = line #"GM_FINAL_AUTH,0,[1-9]" if re.search("GM_FINAL_AUTH,0,[1-9]", line): book.update({(rID, line): to_raw(IDs)}) return sort_book(book) def dUpdate(dic, key, value): return dic.update({(key[0], "GM_FINAL_AUTH,0,0"): value}) def valSplt(s): pattern = re.compile(r'(\d+)') gm = re.split(pattern, s) values = s.rsplit(gm[1]) gm = gm[1] values[1] = gm return values def sort_book(book): book = natsorted([value, key] for key, value in book.items()) book = IndexedOrderedDict((data[1], data[0]) for data in book) return book def alph_order(word1, word2): for i in range(min(len(word1), len(word2))): if ord(word1[i]) == ord(word2[i]): pass elif ord(word1[i]) > ord(word2[i]): return word2 else: return word1 return word1 def read(cpdm, date_list): sCnt = [0] * len(cpdm) lowest_number = 999999999999 terminationCondition = [True] * len(cpdm) saved_results = [0] * len(cpdm) current_prefix = None cnt = 0 while any(terminationCondition) is True: saved_results = [0] * len(cpdm) last_prefix = None lowest_number = 999999999999 for dicIdx, dicVal in enumerate(sCnt): if dicVal < len(cpdm[dicIdx]): ID = cpdm[dicIdx].values()[dicVal] # print(entry) current_prefix, road_number = valSplt(ID) road_number = int(road_number) if last_prefix is None: last_prefix = current_prefix higherOrder_prefix = alph_order(last_prefix, current_prefix) # print('check:',[higherOrder_prefix, last_prefix, current_prefix]) if current_prefix == higherOrder_prefix: if current_prefix != last_prefix: lowest_number = road_number last_prefix = current_prefix elif road_number < lowest_number: lowest_number = road_number last_prefix = current_prefix for dicIdx, dicVal in enumerate(sCnt): if dicVal < len(cpdm[dicIdx]): # print(dicIdx, dicVal, len(cpdm[dicIdx])) ID = cpdm[dicIdx].values()[dicVal] VALUE = cpdm[dicIdx].keys()[dicVal] # print(entry) road_name, road_number = valSplt(ID) road_number = int(road_number) if road_name == last_prefix and lowest_number == road_number: saved_results[dicIdx] = [ID, VALUE[1], date_list[dicIdx], VALUE[0]] if dicVal < len(cpdm[dicIdx]): sCnt[dicIdx] += 1 else: terminationCondition[dicIdx] = False else: terminationCondition[dicIdx] = False for rst in range(len(saved_results)): if saved_results[rst] == 0: pass else: sheet.write(cnt+1, 0, str(saved_results[rst][0])) sheet.write(cnt+1, rst+1, cvrt(saved_results[rst][1])) #sheet.write(cnt+1, 2*et+3, int2Date(saved_results[et][2]), format1) #sheet.write(cnt+1, 0, saved_results[rst][3]) cnt += 1 def main(): # 2018 MAPS path1 = "W:\\Scripting\\2018\\DBData_84577881.txt" path2 = "W:\\Scripting\\2018\\DBData_84639568.txt" path3 = "W:\\Scripting\\2018\\DBData_84652483.txt" path4 = "W:\\Scripting\\2018\\DBData_84670490.txt" # 2019 MAPS path5 = "W:\\Scripting\\2019\\DBData_84706383.txt" path6 = "W:\\Scripting\\2019\\DBData_84715201.txt" path7 = "W:\\Scripting\\2019\\DBData_84743195.txt" path8 = "W:\\Scripting\\2019\\DBData_84777742.txt" path9 = "W:\\Scripting\\2019\\DBData_84815446.txt" path10 = "W:\\Scripting\\2019\\DBData_84835743.txt" # 2020 MAPS path11 = "W:\\Scripting\\2020\\DBData_84882849.txt" path12 = "W:\\Scripting\\2020\\DBData_84966202.txt" path13 = "W:\\Scripting\\2020\\DBData_84988789.txt" p_list = [path1, path2, path3, path4, path5, path6, path7, path8, path9, path10, path11, path12, path13] pool = mp.Pool(mp.cpu_count()) CPDM = pool.map(Book, p_list) pool.close() #pool.join() date_list = [20180809, 20180913, 20181011, 20181204, 20190222, 20190325, 20190501, 20190628, 20190815, 20190925, 20200207, 20200501, 20200617] #CPDM = [b1, b2, b3, b4, b5, b6, b7, b8, b9, b10, b11, b12, b13] for i in CPDM: print(len(i)) #sheet.write("A1", "Lat Long") sheet.write("A1", "ID") #for i in range(len(CPDM)): cn = 0 for i in date_list: #sheet.write(0, 3*i+1, "ID" + str(i+1)) sheet.write(0, cn+1, int2Date(i), format1) cn += 1 #sheet.write(0, 2*i+3, "Date" + str(i+1)) read(CPDM, date_list) workBook.close() if __name__ == "__main__": main() executionTime = (time.time() - startTime) print('Execution time in minutes: ' + str(executionTime/60))
内容的提问来源于stack exchange,提问作者Terlumun Iortyer
相关产品推荐
相关产品推荐

