Python实现Excel风格Datetime转数值方法求助

问题背景 #

在Excel中，日期时间值10/21/2023 2:00:49 PM转换为数值后是45220.583900463。我在Python项目中需要将DataFrame的df1_data["Call start Timestamp"]列转换成这种格式用于拼接，但使用to_datetime()+.timestamp()方法得到的结果不符合预期。

测试代码与结果 #

测试代码：

import datetime
import pandas as pd

a = "10/21/2023 2:00:49 PM"
b = pd.to_datetime(a)
timestamp = b.timestamp()
print("1234" + str(timestamp))

实际输出：12341697896849.0
预期输出：123445220.583900463

项目相关代码 #

Main.py #

from Repeat215 import REPEAT
Repeat = REPEAT()
Repeat.repeat_processing()

Repeat215.py #

import xlwings as xw
import pandas as pd
import os

class REPEAT:
    def __init__(self):
        self.raw_Dir = "C:/Users/Kunal.Khaire/Desktop/My Python Projects/Report_Automation/Reports/215 Repeat/Raw"
        self.raw1 = None
        self.raw2 = None
        self.working1 = "C:/Users/Kunal.Khaire/Desktop/My Python Projects/Report_Automation/Reports/215 Repeat/Repeat Working 1.xlsb"
        self.working2 = "C:/Users/Kunal.Khaire/Desktop/My Python Projects/Report_Automation/Reports/215 Repeat/Repeat Working 2.xlsb"
        self.mtd = "C:/Users/Kunal.Khaire/Desktop/My Python Projects/Report_Automation/Reports/215 Repeat/Repeat Report MTD.xlsb"

    def get_raw_files(self):
        """获取目录中最新的两个文件"""
        full_paths = [os.path.join(self.raw_Dir, file) for file in os.listdir(self.raw_Dir)]
        sorted_files = sorted(full_paths, key=os.path.getmtime, reverse=True)
        latest_two_files = sorted_files[:2]
        self.raw1 = latest_two_files[1].replace("\\", "/")
        self.raw2 = latest_two_files[0].replace("\\", "/")

    def repeat_processing(self):
        """处理Working1、Working2文件并更新MTD文件"""
        wb_W1 = xw.books.open(self.working1)
        sheet = wb_W1.sheets("Previous Day")
        df1 = pd.read_csv(self.raw1, encoding="utf-16", sep="\t")
        if len(df1.iloc[-1, 0]) <= 20:
            df1_data = df1.iloc[1:]
            df1_data = df1_data.sort_values(by=["Mobile_Number", "Call start Timestamp"], ascending=[True, True])
            df1_data.insert(0, "Combined", df1_data["Mobile_Number"].astype(str) + df1_data["Agent BPID"].astype(str) + df1_data["Call start Timestamp"].astype(str))
            df1_data.insert(1, "Flag", df1_data["Combined"].astype(float).diff().fillna(0))
            sheet["C2"].value = df1_data.values
            wb_W1.save()
        else:
            df1 = df1.iloc[:-1]
            df1_data = df1.iloc[1:]
            df1_data = df1_data.sort_values(by=["Mobile_Number", "Call start Timestamp"], ascending=[True, True])
            df1_data.insert(0, "Combined", df1_data["Mobile_Number"].astype(str) + df1_data["Agent BPID"].astype(str) + df1_data["Call start Timestamp"].astype(str))
            df1_data.insert(1, "Flag", df1_data["Combined"].astype(float).diff().fillna(0))
            sheet["C2"].value = df1_data.values
            wb_W1.save()

解决方案 #

原理说明 #

Excel的日期数值是从1899-12-30开始计算的天数（含小数，小数部分代表当天时间占比），而Python的timestamp()是从1970-01-01 UTC开始的秒数，两者基准不同，需手动转换。

转换函数 #

编写一个函数将datetime对象转换为Excel风格的数值：

import pandas as pd

def to_excel_date(dt):
    # Excel的起始基准日期
    excel_epoch = pd.Timestamp('1899-12-30')
    # 计算时间差的总秒数，再转换为天数
    return (dt - excel_epoch).total_seconds() / (24 * 60 * 60)

应用到项目中 #

在Repeat215.py的repeat_processing方法中，替换原有的拼接逻辑：

1. 先将日期列转换为Excel数值格式
2. 用转换后的数值进行拼接

修改后的部分代码：

# 新增转换函数（可放在类内部或全局）
def to_excel_date(dt):
    excel_epoch = pd.Timestamp('1899-12-30')
    return (dt - excel_epoch).total_seconds() / (24 * 60 * 60)

def repeat_processing(self):
    # ... 原有代码 ...
    df1_data = df1.iloc[1:]
    df1_data = df1_data.sort_values(by=["Mobile_Number", "Call start Timestamp"], ascending=[True, True])
    
    # 转换日期列为Excel数值
    df1_data["Call start Excel Timestamp"] = df1_data["Call start Timestamp"].apply(lambda x: to_excel_date(pd.to_datetime(x)))
    
    # 使用转换后的数值拼接
    df1_data.insert(0, "Combined", 
                    df1_data["Mobile_Number"].astype(str) + 
                    df1_data["Agent BPID"].astype(str) + 
                    df1_data["Call start Excel Timestamp"].astype(str))
    
    df1_data.insert(1, "Flag", df1_data["Combined"].astype(float).diff().fillna(0))
    # ... 原有代码 ...

测试验证 #

用之前的测试数据验证：

a = "10/21/2023 2:00:49 PM"
b = pd.to_datetime(a)
excel_val = to_excel_date(b)
print("1234" + str(excel_val))
# 输出：123445220.58390046，与预期一致

内容的提问来源于stack exchange，提问作者Kunal Khaire