Python Pandas技术问询：DataFrame追加时间数据并写入.wac文件及数据修正

Hey there! Let's break this down step by step—since you already know how to load files into DataFrames, we'll focus on the critical parts: swapping out bad values, adding your datetime column, and making sure the final .wac file stays compliant with your software's requirements.

1. Align Correct Values from Excel into the WAC DataFrame #

First, you need to make sure you're replacing the wrong values in your .wac DataFrame with the correct ones from the .xlsx file. How you do this depends on how your data is structured:

Case 1: DataFrames have identical row order and columns #

If both DataFrames have the exact same number of rows, and the columns that need fixing are in the same positions/names, you can directly overwrite the bad columns:

# Replace the columns with incorrect values in df_wac with the correct ones from df_xlsx
df_wac[["column1", "column2"]] = df_xlsx[["column1", "column2"]]

Case 2: Need to match rows via a unique identifier #

If rows might be out of order, use a unique ID column (like a sample ID, timestamp, or row number) to merge the DataFrames and pull in the correct values:

# Merge the two DataFrames on your unique identifier column
merged_df = pd.merge(df_wac, df_xlsx, on="unique_id_column", suffixes=("_wac", "_xlsx"))

# Replace the bad values with the correct ones
merged_df["column1"] = merged_df["column1_xlsx"]
merged_df["column2"] = merged_df["column2_xlsx"]

# Drop the extra columns from the merge and keep the original WAC structure
df_wac = merged_df[df_wac.columns]

2. Add the Datetime Column to Your DataFrame #

Next, let's add the datetime data. The approach depends on where your datetime is coming from:

Option A: Use the current datetime #

If you need to add the current time for each row:

df_wac["datetime"] = pd.to_datetime("now")
# Or specify a timezone if needed: pd.to_datetime("now", utc=True)

Option B: Convert an existing string column to datetime #

If you have a string column in your data that represents time, convert it to a proper datetime type:

# Adjust the format string to match your date/time pattern (e.g., "%Y-%m-%d %H:%M:%S")
df_wac["datetime"] = pd.to_datetime(df_wac["time_string_column"], format="%Y-%m-%d")

Important: Match the .wac file's datetime format #

Before writing, make sure the datetime is formatted exactly how your software expects it. For example, if the software needs a string like 2024-05-20 14:30, convert the datetime column to that string format:

df_wac["datetime"] = df_wac["datetime"].dt.strftime("%Y-%m-%d %H:%M:%S")

3. Write the Modified DataFrame Back to a Compliant .wac File #

This is the trickiest part—you need to preserve the original .wac file's structure (like the skipped header rows, delimiter, and formatting) so the software accepts it. Here's how to do it:

Step 1: Save the original .wac header rows #

Since you used skiprows to load the data, those rows are critical for the software's input format. Let's read and store them first:

# Replace `num_skip_rows` with the number of rows you skipped when loading
num_skip_rows = 3
header_lines = []

with open("original_file.wac", "r") as f:
    for _ in range(num_skip_rows):
        header_lines.append(f.readline())

Step 2: Ensure column order matches the original .wac file #

Your software probably expects columns in a specific order. Make sure the datetime column is appended to the end as you mentioned:

# Get the original column order from df_wac (before adding datetime)
original_columns = df_wac.columns.tolist()
# Append the datetime column to the end
df_wac = df_wac[original_columns + ["datetime"]]

Step 3: Write everything to the new .wac file #

Now combine the header rows and your modified DataFrame, making sure to use the same delimiter as the original file (e.g., tab \t, space, or comma):

# Replace `"\t"` with your actual delimiter (match what you used in pd.read_csv)
delimiter = "\t"

with open("corrected_file.wac", "w") as f:
    # Write the saved header rows first
    f.writelines(header_lines)
    # Write the DataFrame without the index, and no extra header (since original has none in data section)
    df_wac.to_csv(f, sep=delimiter, index=False, header=False)

Bonus: Format numerical values if needed #

If your software requires specific number formatting (e.g., 2 decimal places, no scientific notation), format those columns before writing:

# Format a numerical column to 2 decimal places
df_wac["numeric_column"] = df_wac["numeric_column"].apply(lambda x: f"{x:.2f}")

4. Verify the Result #

Always double-check the output file to make sure:

• The header rows are identical to the original
• Numerical values are correct (replaced from the Excel file)
• The datetime column is present and formatted correctly
• The delimiter and overall structure match the original .wac file

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