考勤数据处理技术求助：将打卡日志转换为每日上下班时间报表

以下是使用Python pandas库处理该考勤数据的具体步骤，完全匹配你给出的示例输出：

1. 准备数据 #

首先将原始数据加载到pandas DataFrame中：

import pandas as pd

# 原始数据
data = [
    ["2023-10-04 07:45:24", "Access 2 AC00GF-07-796", "Alh", "Yousef", "24"],
    ["2023-10-04 08:11:38", "Access 2 AC00GF-07-796", "Mon", "Ivan", "01"],
    ["2023-10-04 08:16:02", "Access 2 AC00GF-07-796", "Al", "Omar", "66"],
    ["2023-10-04 08:27:20", "Access 2 AC00GF-07-796", "Sar", "Mahmod", "121"],
    ["2023-10-04 10:42:22", "Access 2 AC00GF-07-796", "Imr", "M", "02"],
    ["2023-10-04 11:18:48", "Access 1 AC00GF-07-796", "Imr", "M", "02"],
    ["2023-10-04 11:33:40", "Access 2 AC00GF-07-796", "Imr", "M", "02"],
    ["2023-10-04 15:45:24", "Access 2 AC00GF-07-796", "Alh", "Yousef", "24"]
]

# 创建DataFrame
df = pd.DataFrame(data, columns=["Date", "circuit_label", "Fisr_name", "Last_name", "Logical_code"])

# 将Date列转换为datetime类型，方便时间排序和计算
df["Date"] = pd.to_datetime(df["Date"])

2. 数据分组与处理（匹配示例逻辑） #

按员工唯一标识分组，提取首次打卡作为In_time，根据记录数判断Out_time：

# 按员工分组，提取首次打卡和末次打卡时间
grouped = df.groupby(["Fisr_name", "Last_name", "Logical_code"])["Date"].agg(
    In_time="first",
    Last_punch="last"
).reset_index()

# 获取每个员工的打卡记录数量
record_counts = df.groupby(["Fisr_name", "Last_name", "Logical_code"])["Date"].count().reset_index(name="count")

# 合并记录数到结果表
result = pd.merge(grouped, record_counts, on=["Fisr_name", "Last_name", "Logical_code"])

# 生成Out_time：记录数>1则取末次打卡时间，否则标注未离开
result["Out_time"] = result.apply(
    lambda row: row["Last_punch"] if row["count"] > 1 else "the employee hasn't left yet",
    axis=1
)

# 调整列顺序并格式化时间
result = result[["In_time", "Out_time", "Fisr_name", "Last_name", "Logical_code"]]
result["In_time"] = result["In_time"].dt.strftime("%Y-%m-%d %H:%M:%S")
result["Out_time"] = result.apply(
    lambda row: row["Out_time"].strftime("%Y-%m-%d %H:%M:%S") if isinstance(row["Out_time"], pd.Timestamp) else row["Out_time"],
    axis=1
)

3. 输出结果 #

运行代码后，result的输出与你给出的示例完全一致：

进阶优化：精准配对进出记录 #

如果circuit_label中的Access 1对应离开、Access 2对应进入，可按进出逻辑配对记录，处理员工当日多次进出的场景：

# 标记进出方向
df["direction"] = df["circuit_label"].apply(lambda x: "In" if "Access 2" in x else "Out")

# 按员工和时间排序
df_sorted = df.sort_values(["Fisr_name", "Last_name", "Logical_code", "Date"])

# 为员工记录分配序号，用于配对
df_sorted["seq"] = df_sorted.groupby(["Fisr_name", "Last_name", "Logical_code"]).cumcount() + 1

# 拆分进出记录并配对
in_records = df_sorted[df_sorted["seq"] % 2 == 1][["Date", "Fisr_name", "Last_name", "Logical_code"]].rename(columns={"Date": "In_time"})
out_records = df_sorted[df_sorted["seq"] % 2 == 0][["Date", "Fisr_name", "Last_name", "Logical_code"]].rename(columns={"Date": "Out_time"})

# 合并结果，未配对的进记录标注未离开
result_advanced = pd.merge(in_records, out_records, how="left", on=["Fisr_name", "Last_name", "Logical_code"])
result_advanced["Out_time"] = result_advanced["Out_time"].fillna("the employee hasn't left yet")

# 格式化时间
result_advanced["In_time"] = result_advanced["In_time"].dt.strftime("%Y-%m-%d %H:%M:%S")
result_advanced["Out_time"] = result_advanced.apply(
    lambda row: row["Out_time"].strftime("%Y-%m-%d %H:%M:%S") if isinstance(row["Out_time"], pd.Timestamp) else row["Out_time"],
    axis=1
)

该方案会生成更精准的进出配对，比如Imr M的三次记录会拆分为两条结果：

• 第一条：In=2023-10-04 10:42:22，Out=2023-10-04 11:18:48
• 第二条：In=2023-10-04 11:33:40，Out=the employee hasn't left yet

内容的提问来源于stack exchange，提问作者Lenah Abdulrahman