求助：560万条特定格式字符串时间转datetime问题

Hey there! Handling 5.6 million timestamp entries can feel a bit overwhelming, but we’ve got efficient, straightforward ways to convert those strings to datetime objects and compute the time differences you need. Let’s break it down step by step:

1. Using Python’s Built-in datetime Module (Great for Smaller Batches) #

If you want a pure Python approach without external libraries, datetime.strptime() works perfectly for your timestamp format. Here’s how to parse a single entry:

from datetime import datetime

# Example timestamp string
timestamp_str = "2016-10-17 15:00:40.739"
# Parse into a datetime object
dt_obj = datetime.strptime(timestamp_str, "%Y-%m-%d %H:%M:%S.%f")

Format Code Breakdown: #

• %Y: 4-digit year (e.g., 2016)
• %m: 2-digit month (e.g., 10)
• %d: 2-digit day (e.g., 17)
• %H: 24-hour format hour (e.g., 15)
• %M: 2-digit minute (e.g., 00)
• %S: 2-digit second (e.g., 40)
• %f: Microseconds (your 3-decimal timestamp is fully supported here—no extra adjustments needed!)

⚠️ Heads up: Looping through 5.6 million rows with this method will be slow. For your large dataset, we’ll use a much faster approach below.

2. Using Pandas (Best for Large-Scale Data) #

Pandas uses vectorized operations, which are exponentially faster for big datasets like yours. This is the go-to solution for 5.6M entries.

Option A: Parse Dates While Loading Data #

If your timestamps are stored in a CSV file, you can parse them directly when reading the file to save time:

import pandas as pd

# Replace "timestamp_column" with your actual column name
df = pd.read_csv("your_data_file.csv", parse_dates=["timestamp_column"])

Option B: Convert an Existing String Column #

If your timestamps are already loaded as a string column in a DataFrame, use pd.to_datetime() with the explicit format (this skips pandas’ auto-inference and speeds up parsing):

# Convert the string column to datetime
df["datetime_stamp"] = pd.to_datetime(df["timestamp_str"], format="%Y-%m-%d %H:%M:%S.%f")

3. Calculating Time Differences #

Once your timestamps are datetime objects, computing differences is super simple. Just subtract the columns directly to get a Timedelta object, which you can format into your desired units:

# Calculate the difference between stamp2 and stamp1
df["time_diff"] = df["stamp2"] - df["stamp1"]

# Convert to total hours (if you need a numeric value)
df["total_hours"] = df["time_diff"].dt.total_seconds() / 3600

# Or extract separate components (days, hours, minutes, etc.)
diff_components = df["time_diff"].dt.components
# This gives you columns like 'days', 'hours', 'minutes', 'seconds', 'milliseconds'

Quick Performance Tip #

For maximum speed with your large dataset, make sure you’re using the latest version of pandas—they’ve made huge optimizations to datetime parsing over time. Also, always specify the format parameter in pd.to_datetime(); it cuts down on inference time drastically.

内容的提问来源于stack exchange，提问作者c.timothy