使用Pandas实现Excel格式保留数据迁移与条件写入

Got it, let's tackle these two Excel data processing tasks using Pandas step by step. I'll break down each task with practical code examples and explanations to fit your needs.

Task 1: Read Excel Data and Write to Another File with Matching Format #

First, if you just need to transfer the data while keeping the basic structure, core Pandas functions work well. But if you need to preserve exact formatting (like cell styles, merged cells, or column widths), we'll use openpyxl to copy the workbook's structure along with the data.

Basic Data Transfer (No Formatting Preservation) #

This is the quickest way to move data between files, though it won't retain styling:

import pandas as pd

# Read the source Excel file
df = pd.read_excel("your_source_file.xlsx")

# Write to the target file (index=False removes the default Pandas index column)
df.to_excel("your_target_file.xlsx", index=False)

Preserving Exact Formatting #

If you need to keep the original file's look (styles, merged cells, etc.), use openpyxl to load the workbook and update the data in-place:

from openpyxl import load_workbook
import pandas as pd

# Load the source workbook to retain formatting
wb = load_workbook("your_source_file.xlsx")
ws = wb.active

# Read the data into a DataFrame (adjust header row if needed)
df = pd.read_excel("your_source_file.xlsx")

# Write the data back to the workbook (start from row 2 assuming header is row 1)
for row_idx, data_row in enumerate(df.values, start=2):
    for col_idx, value in enumerate(data_row, start=1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# Save to the target file
wb.save("your_target_file.xlsx")

Task 2: Process Merged Headers in sourcefile1.xlsx and Combine with sourcefile2.xlsx #

sourcefile1.xlsx uses merged multi-level headers (main headers: Pair and Field Results, secondary headers: cable_type, cable_name, cable_pair, caller_id, result). Here's how to handle that and apply your specified conditions:

Step 1: Read Files with Multi-Level Headers #

First, read sourcefile1 with its merged headers as a multi-index DataFrame:

import pandas as pd

# Read sourcefile1 using rows 0 and 1 as headers (adjust if your headers are in different rows)
df1 = pd.read_excel("sourcefile1.xlsx", header=[0, 1])

# Optional: Flatten the multi-level headers for easier access (e.g., "Pair_cable_type")
df1.columns = ['_'.join(col).strip() for col in df1.columns.values]

# Read sourcefile2 (assuming standard single-level headers)
df2 = pd.read_excel("sourcefile2.xlsx")

Step 2: Apply Conditions and Write Output #

Since you didn't specify exact conditions, here's an example workflow (adjust based on your actual requirements):

# Example 1: Merge the two DataFrames on a common column (e.g., cable_name)
merged_df = pd.merge(df1, df2, on="Pair_cable_name", how="inner")

# Example 2: Filter rows where the 'result' field is 'Pass'
filtered_df = merged_df[merged_df['Field Results_result'] == 'Pass']

# Example 3: Select specific columns to write to output
output_columns = ['Pair_cable_type', 'Pair_cable_name', 'Field Results_caller_id', 'Field Results_result']
final_df = filtered_df[output_columns]

# Write the processed data to a new Excel file
final_df.to_excel("processed_output.xlsx", index=False)

Notes: #

• If your merged headers span columns differently, adjust the header parameter in pd.read_excel() (e.g., header=[0,1] means the first two rows are headers).
• Replace the example conditions with your actual filtering/merging rules (e.g., filtering by date ranges, calculating aggregates, etc.).

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