You need to enable JavaScript to run this app. 基于Python的Excel道路施工年度时长统计折线图实现
优惠活动
大模型
产品
解决方案
定价
更多

基于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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.16 12:06:09