Pandas读取Excel时12小时制时间格式异常处理求助

Looks like your problem stems from using the wrong format string when parsing 12-hour time with AM/PM markers. Let's break down how to fix this step by step:

The Root Cause #

Your original code uses %H:%M:%S, which is designed for 24-hour time formats (hours 00-23). Since your source data uses 12-hour time with AM/PM tags, Pandas can't recognize the PM marker and incorrectly interprets 12:45:00 PM as 00:45:00 (treating the 12 as an invalid 24-hour hour value and defaulting to 0).

Step-by-Step Solution #

Here's how to correctly parse the time, preserve AM/PM context, and extract the values you need:

1. Parse the 12-hour time correctly
   Use the format string %I:%M:%S %p where:

   • %I = 12-hour format hour (01-12)
   • %p = AM/PM marker (works case-insensitively for 'PM'/'pm' or 'AM'/'am')

   # Correctly parse the time column
   df2_all_rows['Time_conv'] = pd.to_datetime(df2_all_rows['Time'], format='%I:%M:%S %p')
   

2. Extract the 24-hour hour value
   With the datetime parsed correctly, you can directly pull the hour in 24-hour format:

   df2_all_rows['hour'] = df2_all_rows['Time_conv'].dt.hour
   

   This will give you accurate values like:

   • 12:45:00 PM → 12
   • 1:30:00 PM → 13
   • 12:00:00 AM → 0

3. Split out the AM/PM marker
   To create a separate column for the AM/PM identifier, use strftime('%p'):

   df2_all_rows['period'] = df2_all_rows['Time_conv'].dt.strftime('%p')
   

   This will populate the column with 'AM' or 'PM' matching the original time entry.

Bonus: Handling Excel Native Time Formats #

If your Excel file stores time as a native Excel time value (not a string), you can simplify the process by telling Pandas to parse the column on load:

df2_all_rows = pd.read_excel('your_file.xlsx', parse_dates=['Time'])

Skip the manual pd.to_datetime step and proceed to extract hours and periods as above.

Example Output #

For a row with Time = '12:45:00 PM', your DataFrame will now have:

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