Python Pandas日期计算求助：处理员工服务记录Excel表格

Hey there! Let's work through this task together—since you're new to Python, I'll keep things straightforward and explain each step so you understand what's happening.

First, let's recap your needs clearly:

• Read an Excel file (earnedleaves.xls) with employee service records that have Day from and Day to columns
• Replace empty Day to values (especially the last row) with the current date
• Perform date calculations (like total service duration)

Step 1: Set up your libraries #

You started importing pandas, but we'll use the standard alias pd for brevity, plus the datetime module for handling current dates. Here's the correct import block:

import pandas as pd
from datetime import date

Step 2: Load the Excel file #

Use pandas to read your Excel sheet. If your data is in the first sheet (default), this works:

# Load the Excel file into a DataFrame
df = pd.read_excel('earnedleaves.xls')

Step 3: Clean up the Day to column #

First, we need to make sure pandas recognizes your date columns as actual date objects (not strings or numbers). Then we'll replace any empty values with today's date:

# Convert date columns to datetime type (handles different Excel date formats)
df['Day from'] = pd.to_datetime(df['Day from'], dayfirst=True)  # Use dayfirst=True since your dates are DD/MM/YY
df['Day to'] = pd.to_datetime(df['Day to'], dayfirst=True)

# Replace empty (NaN) values in 'Day to' with today's date
today = date.today()
df['Day to'] = df['Day to'].fillna(pd.Timestamp(today))

Note: The dayfirst=True is crucial here because your dates are in DD/MM/YY format—without it, pandas might misinterpret them as MM/DD/YY!

Step 4: Calculate service duration #

Let's add a new column to calculate total service days (you can adjust this to years or months if needed):

# Calculate total service days (subtract 'Day from' from 'Day to')
df['Service Duration (Days)'] = (df['Day to'] - df['Day from']).dt.days

# If you want years (approximate, using 365 days):
df['Service Duration (Years)'] = df['Service Duration (Days)'] / 365

Step 5: View or save your results #

You can print the cleaned DataFrame to check:

print(df)

# Or save the updated data back to a new Excel file
df.to_excel('processed_earnedleaves.xls', index=False)

Troubleshooting tips for beginners #

• If pandas throws an error about missing libraries, run pip install pandas openpyxl in your terminal (openpyxl is needed for reading/writing Excel files)
• Double-check your column names match exactly what's in your Excel sheet (case-sensitive!)
• If dates still aren't parsing correctly, try specifying the format parameter in pd.to_datetime, e.g., format="%d/%m/%y"

内容的提问来源于stack exchange，提问作者Joel G Mathew