python统计excel数据，停车系统有效时长

前言

手上有个停车系统的计时记录表excel，需要对车辆的有效时长进行计算，但是有效时间只有晚上20点到第二天10点，所以使用excel的自带计算公式，感觉无从下手。于是就想到用python做这个数据统计。

准备工作

准备使用xlrd,xlwt, xlutils来对excel数据进行操作。
xlrd用来读取excel数据；
xlwt用来写入excel数据（后来发现无法做到追加）；
xlutils用来写入（追加）excel数据。

使用pandas做数据分析整理
使用datetime做时间转换
使用openpyxl, shutil处理excel

预览excel关键信息

获取关键信息, 比如第八列, 第九列分别是进场时间和出场时间, 这是用于计算时长的关键数据, 第十列的停车时长是系统自带的, 无法过滤免费时长可以忽略. excel表格具体如下：

读取excel表格

1. 定义find_xlsx函数，用于在当前工作目录中查找第一个以.xlsx结尾的文件, 我这里写的比较简单, 我就只处理一个excel表格, 要同时处理多个也简单, 我就不细讲了.

def find_xlsx():
    for f in os.listdir(os.getcwd()):  # 遍历当前工作目录下的所有文件。
        if f.endswith(".xlsx"):  # 如果文件以.xlsx结尾。
            return f  # 返回该文件名。
    return None  # 如果没有找到，返回None。

先backup一个excel, 避免在操作的时候损坏到源文件, 然后再backup的文件上操作

backup_filename = f"backup_{datetime.datetime.now().strftime('%Y-%m-%d_%H-%M-%S')}.xlsx"  # 创建备份文件名，包含当前时间。
shutil.copy(filename, backup_filename)  # 复制原文件到备份文件。

使用pandas读取备份文件的第一个工作表, 新增2个结构分别用来存储停车有效时长, 和实际费用(假定按照1 元/小时).
calc_charge 函数用来计算实际有效时长, 后面再讲.

df = pd.read_excel(backup_filename, sheet_name=0, engine='openpyxl')  # 使用pandas读取备份文件的第一个工作表。
results = df.apply(lambda row: calc_charge(row[8], row[9]), axis=1)  # 对每行应用calc_charge函数，计算停车时间和费用。
df['Parking Time'], df['Charge'] = zip(*results)  # 将结果分别保存到'Parking Time'和'Charge'列。

计算停车有效时长

这个函数主要就是计算每辆车的实际有效停车时长, 具体已经备注代码上了

def calc_charge(enter, exit):
    try:
        enter = datetime.datetime.strptime(enter, "%Y-%m-%d %H:%M:%S")  # 尝试将进入时间字符串转换为datetime对象。
        exit = datetime.datetime.strptime(exit, "%Y-%m-%d %H:%M:%S")  # 尝试将退出时间字符串转换为datetime对象。
    except (ValueError, TypeError):  # 如果转换失败（格式错误或其他问题）。
        return pd.NaT, 0  # 返回pandas的NaT（Not a Time）和0费用。

    free_start, free_end = datetime.time(10, 0), datetime.time(20, 0)  # 定义免费停车的开始和结束时间。
    charge_time = datetime.timedelta(0)  # 初始化计费时间为0。
    current = enter  # 从进入时间开始计算。
    while current < exit:  # 当当前时间小于退出时间时循环。
        if current.time() < free_start or current.time() >= free_end:  # 如果当前时间在收费时间段内。
            if current.time() < free_start:  # 如果当前时间早于免费开始时间。
                next_time = datetime.datetime.combine(current.date(), free_start)  # 设置下一个时间点为免费开始时间。
            elif current.time() >= free_end:  # 如果当前时间晚于或等于免费结束时间。
                next_time = datetime.datetime.combine(current.date(), free_end) + datetime.timedelta(days=1)  # 设置下一个时间点为下一天的免费结束时间。
                if next_time.time() == free_end:  # 如果下一个时间点的时间等于免费结束时间。
                    next_time = datetime.datetime.combine(next_time.date(), free_start)  # 将下一个时间点设置为免费开始时间。
            if next_time > exit:  # 如果下一个时间点晚于退出时间。
                next_time = exit  # 将下一个时间点设置为退出时间。
            charge_time += next_time - current  # 累加计费时间。
            current = next_time  # 更新当前时间为下一个时间点。
        else:  # 如果当前时间在免费时间段内。
            current = datetime.datetime.combine(current.date(), free_end)  # 将当前时间设置为免费结束时间。

    hours = round(charge_time.total_seconds() / 3600, 2)  # 将计费时间转换为小时数，并四舍五入到两位小数。
    charge = int(hours) + (1 if hours - int(hours) >= 0.5 else 0)  # 计算费用，每小时整数部分收费，超过半小时额外收费。
    return charge_time, charge  # 返回计费时间和费用。

计算总费用

对每辆车的数据进行累计, 得到总费用

    valid_times = [time for time in df['Parking Time'] if not pd.isna(time)]  # 过滤出有效的停车时间。
    total_time = sum(valid_times, datetime.timedelta(0))  # 计算总停车时间。
    total_charge = df['Charge'].sum()  # 计算总费用。
    print(f"Total charge: {total_charge}, Total time: {total_time}")  # 打印总费用和总时间。

数据简单处理

需要注意的在写入下引入我们使用的pandas, 默认是按照day单位来存储的, 对于查看数据不是很友好, 因为通常停车实际都是几小时, 而不是几天, 导致看上去就是都是0, 所以需要做一个预处理.

    df['Parking Time'] = df['Parking Time'].apply(lambda x: x.total_seconds() / 3600 if not pd.isna(x) else 0)  # 将停车时间转换为小时数。
    df['Parking Time'] = df['Parking Time'].apply(hours_to_hhmmss)  # 将停车时间转换为HH:MM:SS格式。

把结果写入excel表格

使用openpyxl加载备份文件, 使用追加模式, 把数据写回到源文件

book = load_workbook(backup_filename)  # 使用openpyxl加载备份文件。
writer = pd.ExcelWriter(backup_filename, engine='openpyxl', mode='a')  # 创建一个ExcelWriter对象，用于将数据写回同一个文件。
if 'Processed' in book.sheetnames:  # 如果已经存在名为'Processed'的工作表。
    std = book['Processed']  # 获取该工作表。
    book.remove(std)  # 从工作簿中移除该工作表。
    book.save(backup_filename)  # 保存更改。
df.to_excel(writer, "Processed", index=False)  # 将处理后的DataFrame写入名为'Processed'的新工作表中。
writer.close()  # 关闭ExcelWriter对象。

效果展示

到此数据的计算结束, 稍微有点缺陷, 就是我没直接在原先的sheet里插入, 而是新加了一个sheet, 然后也没有保留原先的主题文字背景, 这个有空后面我再优化下.
具体如下: