如何用Pandas动态添加列，将多份异构日志数据导出至Excel？

Got it, let's tackle this problem step by step. The key here is to first parse your log file to extract all the loss entries dynamically, then build a pandas DataFrame that adapts to the number of entries each time. Here's how to do it:

Step 1: Parse the Log File #

First, we'll use regular expressions to extract every "Loss per inch" entry from your log, regardless of how many there are. This lets us handle both 2-entry and 3-entry logs (or any number, really).

Step 2: Build a Dynamic Data Dictionary #

We'll convert the extracted entries into a dictionary where each key is the column name (e.g., Loss per inch @ 2.5 GHz) and the value is the corresponding loss value. Pandas will automatically turn these keys into columns when we create the DataFrame.

Step 3: Export to Excel #

Finally, we'll create the DataFrame and export it to Excel. If you need to combine multiple runs into one sheet, we can handle that too—pandas will fill missing columns with NaN (or you can replace them with a default value like 0).

Full Code Example #

import re
import pandas as pd

def parse_log_file(log_path):
    # Read the entire log file content
    with open(log_path, 'r') as file:
        log_content = file.read()
    
    # Regex pattern to match all loss entries: captures frequency and loss value
    # Matches both scientific notation (2.500000e+00) and plain numbers (5)
    pattern = r'Loss per inch @ ([\d.e+-]+) GHz = ([-\d.]+) dB'
    matches = re.findall(pattern, log_content)
    
    # Build a dictionary for the DataFrame
    loss_data = {}
    for freq_str, loss_str in matches:
        # Optional: Format scientific notation frequencies to readable decimals
        try:
            freq = float(freq_str)
            column_name = f'Loss per inch @ {freq} GHz'
        except ValueError:
            # Fallback to original string if conversion fails
            column_name = f'Loss per inch @ {freq_str} GHz'
        loss_data[column_name] = float(loss_str)
    
    return loss_data

# ------------------------------
# Example 1: Process a single log file
# ------------------------------
# Process first run log
run1_data = parse_log_file('run1.log')
df_run1 = pd.DataFrame([run1_data])
df_run1.to_excel('run1_results.xlsx', index=False)

# Process second run log
run2_data = parse_log_file('run2.log')
df_run2 = pd.DataFrame([run2_data])
df_run2.to_excel('run2_results.xlsx', index=False)

# ------------------------------
# Example 2: Combine multiple runs into one Excel sheet
# ------------------------------
# Collect data from all runs
all_runs = [
    parse_log_file('run1.log'),
    parse_log_file('run2.log'),
    # Add more runs here as needed
]

# Create combined DataFrame (missing columns will be filled with NaN)
combined_df = pd.DataFrame(all_runs)
# Optional: Replace NaN with 0 if you prefer
combined_df = combined_df.fillna(0)

# Export to Excel
combined_df.to_excel('all_runs_combined.xlsx', index=False)

How This Works #

• Dynamic Parsing: The regex pattern catches every "Loss per inch" entry in your log, no matter how many there are.
• Flexible Columns: When you create the DataFrame from the dictionary, pandas uses the dictionary keys as column names—so new entries in later logs will automatically become new columns.
• Combining Runs: When combining multiple runs, pandas will align columns by name. If a run doesn't have a particular frequency entry, that cell will be NaN (or 0 if you use fillna(0)).

This approach handles all your cases perfectly, whether each log has 2, 3, or more loss entries.

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