如何高效将Pandas DataFrame中UTC时间戳转换为指定时区本地时间

Got it, let's work through this efficiently for your 2M+ row dataset—no slow loops here! The key is to use Pandas' vectorized operations to avoid performance hits with large data. Here's a step-by-step solution tailored to your needs:

First, Let's Clean Up and Prep the Data #

Your Timezone column has extra prefixes/quotes (like Timezone:"America/Anchorage"), so we'll start by stripping those to get valid timezone identifiers. We'll also ensure your UTC timestamps are properly recognized as timezone-aware datetimes.

Full Code Example (Works for Your Sample Data) #

import pandas as pd

# Load your actual dataset instead of this sample
data = {
    'Event Timestamp': ['2019-10-23 18:48:36.291', '2019-10-04 07:55:34.964'],
    'Timezone': ['Timezone:"America/Anchorage"', 'Timezone:"Asia/Jerusalem"'],
    'Local Time': ['', '']
}
df = pd.DataFrame(data)

# Step 1: Convert UTC timestamps to timezone-aware datetime objects
df['Event Timestamp'] = pd.to_datetime(df['Event Timestamp'], utc=True)

# Step 2: Clean the Timezone column to get valid identifiers (e.g., "America/Anchorage")
df['Timezone'] = df['Timezone'].str.replace(r'Timezone:"|\"', '', regex=True)

# Step 3: Convert UTC timestamps to the user's local timezone (tz-aware)
df['local_tz_aware'] = df['Event Timestamp'].dt.tz_convert(df['Timezone'])

# Step 4: Strip timezone offset to get the raw local time string/datetime
# Option 1: Get local time as a string (matches your desired format)
df['Local Time'] = df['local_tz_aware'].dt.tz_localize(None).astype(str)

# Option 2: Keep it as a datetime object (no timezone info) if you need to do further time calculations
# df['Local Time'] = df['local_tz_aware'].dt.tz_localize(None)

# View the final result
print(df[['Event Timestamp', 'Timezone', 'Local Time']])

What Each Step Does: #

1. Timezone-Aware UTC: Converting Event Timestamp to a UTC-aware datetime ensures Pandas knows the base timezone for accurate conversions.
2. Clean Timezone Strings: The regex removes the Timezone:" prefix and trailing quote, leaving valid IANA timezone identifiers (like Asia/Singapore) that Pandas recognizes.
3. Vectorized Conversion: dt.tz_convert processes all rows in bulk—way faster than using apply for 2M+ rows.
4. Strip Timezone Offset: tz_localize(None) removes the timezone metadata, turning it into a "naive" datetime. Converting to a string gives you the exact format you want (e.g., 2019-10-23 10:48:36.291000).

Performance Note #

All these operations are vectorized, so they'll handle your 2M+ rows in seconds, not minutes. Avoid using df.apply(lambda x: ..., axis=1)—it's slow for large datasets because it processes rows one by one.

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