如何用Python/Pandas计算Excel中00:00:00格式登录时间列的总和

Got it, let's walk through exactly how to calculate the total login time by summing your time1 and time2 columns (stored as HH:MM:SS strings) using Python and Pandas. Here's a step-by-step solution that works reliably:

Step 1: Import Required Libraries #

First, make sure you have Pandas installed (if not, run pip install pandas in your terminal), then import it:

import pandas as pd

Step 2: Load Your Excel Data #

Read your Excel file into a Pandas DataFrame. Replace 'your_login_data.xlsx' with your actual file path:

df = pd.read_excel('your_login_data.xlsx')

Step 3: Convert String Times to Timedelta Objects #

Pandas needs to recognize your HH:MM:SS strings as time durations (not just plain text). Use pd.to_timedelta() to convert the columns into usable time objects:

# Convert time columns from string to timedelta
df['time1'] = pd.to_timedelta(df['time1'])
df['time2'] = pd.to_timedelta(df['time2'])

Step 4: Calculate Total Login Time #

Now you can simply add the two timedelta columns to populate the total-time column:

df['total-time'] = df['time1'] + df['time2']

Optional: Format Total Time to Clean HH:MM:SS #

By default, Pandas will display timedeltas like 0 days 02:00:00. If you want to strip the "0 days " prefix and keep only the HH:MM:SS portion, use this lambda function:

df['total-time'] = df['total-time'].apply(lambda x: str(x).split()[-1])

Step 5: Save the Updated Data Back to Excel #

Write the modified DataFrame to a new Excel file (or overwrite the original if you're confident):

df.to_excel('updated_login_data.xlsx', index=False)

Example Output #

After running the code, your processed DataFrame will look like this:

Notes for Edge Cases #

• If your Excel file has missing values (empty cells) in time1 or time2, add .fillna(pd.to_timedelta('00:00:00')) during conversion to avoid NaT (Not a Time) errors:

  df['time1'] = pd.to_timedelta(df['time1']).fillna(pd.to_timedelta('00:00:00'))
  

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