机器学习数据集TimeStamp格式批量转换：Python与Excel实现方法

Great question! Handling timestamp conversions for large datasets can be tricky, but both Excel and Python (with pandas) have solid solutions. Let's break down each method, assuming you want to set the year to 2013 (adjust if your data uses a different year).

Method 1: Excel #

Excel works well here, though the approach varies slightly depending on whether you're using Excel 365 (with modern text functions) or an older version.

Excel 365 (Simpler) #

Use TEXTBEFORE and TEXTAFTER to easily extract components:

1. In a new column (say B1), enter this formula to create a valid datetime value:

   =DATE(2013, --TEXTAFTER(TEXTBEFORE(A1, " Day"), "Month"), --TEXTBEFORE(TEXTAFTER(A1, " Day"), " ")) + TIMEVALUE(TEXTAFTER(A1, " ", 2))
   

2. Select the entire column B, right-click → Format Cells → Custom, then enter yyyy-mm-dd hh:mm:ss as the format string.
3. Drag the formula down to apply to all 100k+ rows (Excel can handle this, though it might take a few seconds).

Older Excel Versions (No TEXTBEFORE/TEXTAFTER) #

Use MID, FIND, and RIGHT to extract parts:

1. In column B1, enter:

   =DATE(2013, --MID(A1, 6, FIND(" Day", A1)-6), --MID(A1, FIND("Day", A1)+3, FIND(" ", A1, FIND("Day", A1)) - FIND("Day", A1)-3)) + TIMEVALUE(RIGHT(A1, 8))
   

2. Format the column with yyyy-mm-dd hh:mm:ss as above.

Notes for Excel:

• If your year isn't fixed (e.g., varies per entry), replace 2013 with a reference to a cell containing the year, or adjust the formula to extract the year from your timestamp (if present).
• For very large datasets, Excel might lag a bit during calculation—be patient, or consider the Python method for faster processing.

Method 2: Python (Using Pandas) #

Pandas is ideal for 100k+ rows because it's optimized for bulk data operations. Here's a step-by-step solution:

Step 1: Install Pandas (if needed) #

If you don't have pandas installed, run this in your terminal:

pip install pandas

Step 2: Code to Convert Timestamps #

import pandas as pd

# Load your dataset (replace with your file path/format)
df = pd.read_csv("your_dataset.csv")

# Extract month, day, and time components using regex
df[["month", "day", "time"]] = df["TimeStamp"].str.extract(r"Month(\d+) Day(\d+) (\d{2}:\d{2}:\d{2})")

# Convert extracted components to appropriate data types
df["month"] = df["month"].astype(int)
df["day"] = df["day"].astype(int)
time_parts = df["time"].str.split(":", expand=True).astype(int)
df[["hour", "minute", "second"]] = time_parts

# Create the formatted timestamp column
df["formatted_timestamp"] = pd.to_datetime({
    "year": 2013,
    "month": df["month"],
    "day": df["day"],
    "hour": df["hour"],
    "minute": df["minute"],
    "second": df["second"]
})

# Optional: Drop temporary columns and save the result
df.drop(["month", "day", "time", "hour", "minute", "second"], axis=1, inplace=True)
df.to_csv("formatted_dataset.csv", index=False)

Notes for Python:

• This method is much faster than Excel for 100k+ rows—processing should take just a few seconds.
• If your year varies, adjust the "year": 2013 line (e.g., extract it from another column or modify the regex to capture it if present in the original timestamp).
• The regex r"Month(\d+) Day(\d+) (\d{2}:\d{2}:\d{2})" works for both single-digit (e.g., Month9) and double-digit (e.g., Month12) months/days.

Both methods will give you the yyyy-mm-dd hh:mm:ss format you need. Choose Excel if you prefer a GUI, or Python if you want speed and automation for large datasets.

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