Python读取Excel日期列含多时间戳，pd.to_datetime转换报错求助

Got it, let's tackle this problem step by step. The core issue is that pd.to_datetime can't directly parse cells containing multiple comma/space-separated timestamps—it expects single, clean values. We'll break this down into three key steps: cleaning and splitting the timestamps, converting them to datetime objects, and optionally expanding multi-value rows into individual rows.

Step 1: Clean and Split Timestamps into Individual Values #

First, we need to process each cell to extract individual millisecond timestamps, regardless of whether they're single values (like 1441152000000.0), comma-separated, or space-separated. We'll handle the .0 decimal suffix and filter out any invalid entries.

import pandas as pd

def split_and_clean_timestamps(cell):
    # Convert cell to string to handle numeric and string types uniformly
    cell_str = str(cell).strip()
    
    # Return empty list for null/empty cells
    if not cell_str or cell_str == 'nan':
        return []
    
    # Replace commas with spaces to handle both separators, then split into parts
    parts = cell_str.replace(',', ' ').split()
    
    # Extract only numeric parts (removes .0 suffix from float-like timestamps)
    cleaned_ts = []
    for part in parts:
        # Extract digits only to handle cases like "1441152000000.0"
        ts_digits = ''.join([c for c in part if c.isdigit()])
        if ts_digits:
            cleaned_ts.append(int(ts_digits))
    
    return cleaned_ts

# Apply the function to your target column (replace 'timestamp_col' with your column name)
df['cleaned_timestamps'] = df['timestamp_col'].apply(split_and_clean_timestamps)

Step 2: Convert Cleaned Timestamps to Datetime Objects #

Now that we have lists of valid integer timestamps, we can convert each one to a UTC datetime object using pd.to_datetime with unit='ms'. We'll add error handling to skip any invalid timestamps that might slip through.

def convert_to_datetime(ts_list):
    converted_dates = []
    for ts in ts_list:
        try:
            # Convert millisecond timestamp to UTC datetime
            converted_dates.append(pd.to_datetime(ts, unit='ms', utc=True))
        except ValueError as e:
            print(f"Skipping invalid timestamp {ts}: {str(e)}")
            continue
    return converted_dates

# Apply conversion to the cleaned timestamps column
df['datetime_values'] = df['cleaned_timestamps'].apply(convert_to_datetime)

Step 3 (Optional): Expand Multi-Value Rows into Individual Rows #

If you prefer each timestamp to have its own row (instead of lists in cells), use pandas' explode method to expand the datetime lists:

# Explode the datetime list column into separate rows
df_exploded = df.explode('datetime_values').reset_index(drop=True)

# Clean up the dataframe (remove intermediate columns and rename if needed)
df_exploded = df_exploded.drop(['timestamp_col', 'cleaned_timestamps'], axis=1)
df_exploded = df_exploded.rename(columns={'datetime_values': 'utc_datetime'})

How This Works #

• Cleaning Step: We normalize all cell content to strings, handle both comma and space separators, and strip non-numeric characters to eliminate the .0 suffix from float-formatted timestamps.
• Conversion Step: We process each timestamp individually, so pd.to_datetime never sees multi-value strings—only single integer timestamps it can parse correctly.
• Explode Step: This transforms your data into a row-per-timestamp format, which is often easier to analyze or visualize.

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