如何在Python或Power BI中清洗CSV文件中格式混乱的工时数据列？

老兄，这种格式五花八门的文本清洗确实够头疼的，尤其是1万多行的量，手动处理根本不现实。我之前做项目也碰到过类似的糟心情况，给你分享下我当时用Python和Power BI分别处理的思路，应该能帮你搞定大部分场景。

Python 处理方案：用正则表达式全覆盖匹配 #

Python的正则表达式是处理这种混乱文本的利器，核心思路是先梳理所有可能的时间格式，然后写一个函数逐个匹配转换，最后批量应用到整个列。

具体步骤和代码 #

1. 先梳理你遇到的格式类型：

   • 带小时/分钟的写法：比如1 hour. 25 minutes、2h. 22m、2hours
   • 时间段写法：比如12:00 - 13:40、from 12 till 15
   • 多技师分摊写法：比如1 hour for technician x and 2 hours for technician y

2. 写一个清洗函数，逐个匹配这些格式：

import re
import pandas as pd

def clean_work_hours(text):
    # 处理空值
    if pd.isna(text):
        return 0.0
    
    # 先处理时间段格式：计算结束时间减开始时间
    time_range_match = re.search(r'(\d{1,2}(?::\d{2})?)\s*(?:-|till)\s*(\d{1,2}(?::\d{2})?)', text, re.IGNORECASE)
    if time_range_match:
        start_str, end_str = time_range_match.groups()
        
        # 解析时间为小时数（带分钟的转成小数）
        def parse_time(t):
            if ':' in t:
                h, m = map(int, t.split(':'))
                return h + m/60
            else:
                return int(t)
        
        start_h = parse_time(start_str)
        end_h = parse_time(end_str)
        # 确保不会出现负数（比如跨天的情况，这里暂时按当天处理）
        return max(end_h - start_h, 0)
    
    # 提取所有小时和分钟的数字，累加计算总工时
    hours = re.findall(r'(\d+)\s*(?:hour|hours|h\.?)\b', text, re.IGNORECASE)
    minutes = re.findall(r'(\d+)\s*(?:minute|minutes|m\.?)\b', text, re.IGNORECASE)
    
    total_h = sum(map(int, hours))
    total_m = sum(map(int, minutes))
    total_hours = total_h + total_m / 60
    
    # 如果上面没匹配到，尝试提取文本里的数字（兜底处理）
    if total_hours == 0:
        num_match = re.search(r'(\d+\.?\d*)', text)
        if num_match:
            total_hours = float(num_match.group())
    
    # 保留四位小数，方便后续统计
    return round(total_hours, 4)

# 读取CSV并应用清洗函数
df = pd.read_csv('你的工时文件.csv')
# 替换成你实际的工时列名
df['清洗后工时(小时)'] = df['原始工时列'].apply(clean_work_hours)

3. 调整和验证：
   • 先抽100行左右的样本测试函数，看看有没有漏匹配的格式，比如如果有3hrs这种写法，就把正则里的hour|hours|h\.?改成hour|hours|h\.?|hrs
   • 多技师的情况，上面的函数会把所有小时数累加（比如1小时+2小时得到3小时），如果需要拆分每个技师的工时，你可以再写个函数匹配(\d+) hour.*technician (\w+)这种模式，单独提取每个技师的时间。

Power BI 处理方案：用Power Query自定义函数 #

如果你更熟悉Power BI，用Power Query的M语言也能实现同样的效果，适合不想写Python的场景。

具体步骤 #

1. 把CSV导入Power BI，进入Power Query编辑器
2. 点击主页->自定义函数，粘贴下面的M语言代码：

let
    CleanWorkHours = (text as text) as number =>
    let
        // 处理空值
        NullCheck = if text = null then 0 else
        // 处理时间段格式
        TimeRangeCheck = Text.PositionOfAny(text, {"-", "till"}, Occurrence.First),
        TimeResult = if TimeRangeCheck > 0 then
            let
                SplitParts = Text.SplitAny(text, "-till"),
                StartText = Text.Trim(SplitParts{0}),
                EndText = Text.Trim(SplitParts{1}),
                // 解析开始时间
                ParseStart = if Text.Contains(StartText, ":") then
                    let
                        TimeParts = Text.Split(StartText, ":"),
                        Hour = Number.From(TimeParts{0}),
                        Minute = Number.From(TimeParts{1})
                    in Hour + Minute/60
                else Number.From(StartText),
                // 解析结束时间
                ParseEnd = if Text.Contains(EndText, ":") then
                    let
                        TimeParts = Text.Split(EndText, ":"),
                        Hour = Number.From(TimeParts{0}),
                        Minute = Number.From(TimeParts{1})
                    in Hour + Minute/60
                else Number.From(EndText)
            in ParseEnd - ParseStart
        else 0,
        // 提取小时和分钟数字
        ExtractHours = List.Sum(List.Transform(Text.Split(text, " "), each if Text.Contains(_, "hour") or Text.Contains(_, "h") then try Number.From(Text.Remove(_, {"h", "o", "u", "r", ".", "s"})) otherwise 0 else 0)),
        ExtractMinutes = List.Sum(List.Transform(Text.Split(text, " "), each if Text.Contains(_, "minute") or Text.Contains(_, "m") then try Number.From(Text.Remove(_, {"m", "i", "n", "u", "t", "e", ".", "s"})) otherwise 0 else 0)),
        TotalFromHM = ExtractHours + ExtractMinutes/60,
        // 取有效结果（时间段优先，其次是小时分钟累加）
        Total = if TimeResult > 0 then TimeResult else TotalFromHM,
        // 兜底：提取文本中的数字
        FinalTotal = if Total = 0 then try Number.From(Text.Select(text, {"0".."9", "."})) otherwise 0 else Total
    in FinalTotal
in
    CleanWorkHours

3. 回到数据视图，点击添加列->调用自定义函数，选择你刚才创建的CleanWorkHours函数，选择原始工时列作为输入，就能得到清洗后的工时列了。

通用建议 #

• 先抽样梳理格式：从1万行里抽200行左右，把所有不同的格式列出来，确保你的正则或M函数覆盖了90%以上的情况，剩下的特殊格式可以单独处理。
• 验证结果：清洗完后，随机抽一些行对比原数据和清洗结果，比如12:00 - 13:40应该转换成1.6667小时，1 hour.25 minutes应该转换成1.4167小时，确保没有错误。
• 特殊场景单独处理：比如2hours for each technician这种，如果需要计算总工时，得结合技师数量列（如果有的话），或者和业务方确认是按单技师还是总工时统计。

备注：内容来源于stack exchange，提问作者Abdul