将Excel单元格内容转换为时间并实现条件求和

Hey there! Let's fix your code and solve the time duration summation problem properly.

First, let's address the obvious issue in your current code: that nested for loop is causing each duration entry to be added to uren_lijst multiple times (once for every row in the column). You can simplify that part completely—no need to loop manually at all if you're using pandas:

uren_lijst = read_file['Duration'].tolist()
# Or if you still want a loop (though it's unnecessary here):
# uren_lijst = []
# for row in read_file['Duration']:
#     uren_lijst.append(row)

Now, onto the core problem: converting those time strings into a format you can sum, without unwanted date components. pd.to_datetime isn't the right tool here because it's designed for timestamps (date + time), not durations. Instead, use pd.to_timedelta—it's built specifically for time intervals and won't add any date baggage.

Step-by-Step Solution #

Let's rewrite your workflow to handle duration conversion and conditional summation cleanly:

1. Read your data and convert durations

   import pandas as pd
   
   # Load the Excel file
   df = pd.read_excel('test.xlsx')
   
   # Convert the Duration column to timedelta (works for formats like "HH:MM", "H:M", "HH:MM:SS")
   df['Duration'] = pd.to_timedelta(df['Duration'])
   

2. Calculate total hours (for easy numerical operations)
   If you want to work with raw hour values (e.g., 1 hour 30 minutes = 1.5), extract the total seconds and convert to hours:

   df['Total_Hours'] = df['Duration'].dt.total_seconds() / 3600
   

3. Conditional summation
   Now you can filter rows based on your specific conditions and sum the durations. For example, if you want to sum durations where a Task_Type column equals "Work":

   # Sum using the timedelta column (returns a timedelta object)
   total_work_duration = df[df['Task_Type'] == 'Work']['Duration'].sum()
   print(f"Total work duration: {total_work_duration}")
   
   # Or sum using the numerical hours column (returns a float)
   total_work_hours = df[df['Task_Type'] == 'Work']['Total_Hours'].sum()
   print(f"Total work hours: {total_work_hours:.2f}")
   

Handling Non-Standard Duration Formats #

If your Duration strings aren't in standard HH:MM format (e.g., "2小时15分" or "90 mins"), you'll need a custom function to parse them:

def parse_duration(duration_str):
    hours = 0.0
    minutes = 0.0

    if '小时' in duration_str or 'hour' in duration_str.lower():
        hours = float(duration_str.split('小时')[0] if '小时' in duration_str else duration_str.split('hour')[0])
        remaining = duration_str.split('小时')[1] if '小时' in duration_str else duration_str.split('hour')[1]
        if '分' in remaining or 'min' in remaining.lower():
            minutes = float(remaining.split('分')[0] if '分' in remaining else remaining.split('min')[0])
    elif '分' in duration_str or 'min' in duration_str.lower():
        minutes = float(duration_str.split('分')[0] if '分' in duration_str else duration_str.split('min')[0])
    
    return hours + (minutes / 60)

# Apply the function to your Duration column
df['Total_Hours'] = df['Duration'].apply(parse_duration)

# Then sum as before
total_hours = df[df['Your_Condition_Column'] == 'Your_Value']['Total_Hours'].sum()

Key Takeaways #

• Ditch pd.to_datetime for durations—pd.to_timedelta is made for this exact use case.
• Avoid unnecessary loops with pandas; use built-in methods to convert columns directly.
• Convert durations to numerical hours if you need easy arithmetic operations like summation.

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