使用Python转换hello123.xlsx中8万+时间戳格式的技术问询

Got it, handling 80k+ rows of timestamps doesn't have to be a headache—Python's pandas library is perfect for this kind of large-scale data processing, since it's optimized for speed and can handle big datasets without breaking a sweat. Let's walk through the steps:

Step 1: Install Required Libraries #

First, make sure you have the tools we need to read Excel files and manipulate dates:

pip install pandas openpyxl

openpyxl is required to read and write .xlsx files with pandas.

Step 2: Full Code Implementation #

Here's a complete script that reads your hello123.xlsx, converts the timestamp column, and saves the updated data (either adding a new formatted column or overwriting the original):

import pandas as pd

# 1. Read the Excel file - replace 'Timestamp_Column' with your actual column name
df = pd.read_excel('hello123.xlsx')

# 2. Define the original timestamp format and your target format
# Original format matches your example: "Tue Mar 13 14:51:04 +0000 2018"
original_format = '%a %b %d %H:%M:%S %z %Y'
# Example target format: "YYYY-MM-DD HH:MM:SS" (adjust this to your specific needs!)
target_format = '%Y-%m-%d %H:%M:%S'

# 3. Convert timestamps efficiently (way faster than manual loops)
df['Formatted_Timestamp'] = pd.to_datetime(df['Timestamp_Column'], format=original_format).dt.strftime(target_format)

# 4. Save the updated data back to Excel (index=False removes the extra pandas row numbers)
df.to_excel('hello123_formatted.xlsx', index=False)

Key Notes & Pro Tips #

• Replace Column Names: Swap 'Timestamp_Column' with the actual name of your timestamp column in the Excel file (e.g., 'Created_Time').
• Customize Target Format: Tweak target_format to match your desired output. For example:
  • '%d/%m/%Y %I:%M %p' gives "13/03/2018 02:51 PM"
  • '%Y-%m-%d' gives just the date: "2018-03-13"
• Handle Invalid Rows: If some entries have malformed timestamps, add errors='coerce' to pd.to_datetime to turn those into NaN instead of crashing the script:

  pd.to_datetime(df['Timestamp_Column'], format=original_format, errors='coerce')
  

• Performance: Pandas processes 80k rows in seconds—far faster than writing a manual loop with datetime.strptime for each row.

Quick Breakdown of the Format Codes #

• %a: Abbreviated weekday name (e.g., "Tue")
• %b: Abbreviated month name (e.g., "Mar")
• %z: UTC offset (e.g., "+0000")
• The rest match standard date/time components (like %H for 24-hour hour, %Y for 4-digit year)

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