Excel循环遍历行+查找特定单词（apple）的条件语句实现数据规整

Alright, let's work through this problem. You've already converted your text data into Excel with each word in a separate cell, section headers in column E, and variable rows under each header. Now you need to loop through all rows to detect the word "apple" and handle the logic to organize your data properly. Here are two practical approaches depending on the tool you prefer:

If you need to manipulate individual cells directly (like moving data, adding markers, or adjusting formatting), openpyxl is a great fit. This method tracks the current header as you iterate through rows:

from openpyxl import load_workbook

# Load your Excel file and target worksheet
wb = load_workbook("your_raw_data.xlsx")
ws = wb.active  # Replace with ws = wb["SheetName"] if using a specific sheet

current_header = None

# Iterate through every row (adjust min_row if your data starts later)
for row_num, row in enumerate(ws.iter_rows(min_row=1, values_only=False), start=1):
    # Check column E (index 4, since Python uses 0-based indexing) for a new header
    header_cell = row[4]
    if header_cell.value and str(header_cell.value).strip() != "":
        current_header = header_cell.value
        print(f"Switched to header: {current_header}")
    
    # Check each cell in the row for "apple"
    for cell in row:
        if cell.value and str(cell.value).strip().lower() == "apple":
            # Insert your custom logic here! Examples:
            # 1. Mark the row with the current header in column F
            ws.cell(row=row_num, column=6, value=f"Under header: {current_header}")
            # 2. Move the "apple" cell to a dedicated column under its header
            # ws.cell(row=row_num, column=7, value=cell.value)
            # cell.value = None

# Save the organized data to a new file (don't overwrite your original!)
wb.save("organized_data.xlsx")

If you're working with larger datasets and want to streamline the header-matching process, pandas is more efficient. It uses forward-filling to attach headers to every data row automatically:

import pandas as pd

# Load your Excel data into a DataFrame
df = pd.read_excel("your_raw_data.xlsx")

# Forward-fill column E headers so every row has its associated header
df["Assigned Header"] = df.iloc[:, 4].ffill()  # iloc[:,4] targets column E

# Filter rows that contain "apple" (adjust to check specific columns if needed)
apple_matches = df[
    df.apply(lambda row: row.astype(str).str.contains("apple", case=False).any(), axis=1)
]

# Example: Create a clean table of "apple" occurrences and their headers
organized_df = apple_matches[["Assigned Header"]].join(
    apple_matches.apply(
        lambda row: pd.Series([col for col in df.columns if str(row[col]).strip().lower() == "apple"]),
        axis=1
    ).rename(columns={0: "Apple Found In Column"})
)

# Save the result
organized_df.to_excel("apple_organized_summary.xlsx", index=False)

Key Notes to Customize: #

• Case Insensitivity: Both examples use .lower() to match "Apple", "APPLE", etc. Remove this if you need exact case matching.
• Partial Matches: If you want to catch words like "applepie" or "pineapple", replace the exact match with "apple" in str(cell.value).lower()
• Header Detection: If your headers have unique formatting (e.g., bold), in openpyxl you can check header_cell.font.bold instead of just cell value.

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