DataFrame迭代循环：动态创建数据字典

Got it, let's break down how to handle this repeating block structure in your Excel file. Each block starts with a "Response Comments" row, has a "Customer name" row right after, ends with a "Recommended" row, and has optional rows in between. Here's a practical, step-by-step solution using pandas:

Step 1: Read the Excel File Correctly #

First, we'll read the entire sheet without treating any row as a header (since your "headers" are part of each repeating block):

import pandas as pd

# Replace 'your_data.xlsx' with your actual file path
df = pd.read_excel('your_data.xlsx', header=None)

Step 2: Iterate and Extract Blocks #

We'll use a while loop to scan through each row, identify block boundaries, and map each customer to their full block data:

customer_blocks = {}
current_row = 0
total_rows = len(df)

while current_row < total_rows:
    # Check if we've hit the start of a new block
    if df.iloc[current_row, 0] == 'Response Comments':
        block_data = []
        # Add the "Response Comments" row to the block
        block_data.append(df.iloc[current_row].to_dict())
        current_row += 1
        
        # Grab the customer name as our dictionary key
        if current_row < total_rows and df.iloc[current_row, 0] == 'Customer name':
            customer_name = df.iloc[current_row, 1]  # Adjust column index if your value is in a different column
            block_data.append(df.iloc[current_row].to_dict())
            current_row += 1
            
            # Collect all rows until we hit the "Recommended" row
            while current_row < total_rows and df.iloc[current_row, 0] != 'Recommended':
                block_data.append(df.iloc[current_row].to_dict())
                current_row += 1
            
            # Add the final "Recommended" row to the block
            if current_row < total_rows and df.iloc[current_row, 0] == 'Recommended':
                block_data.append(df.iloc[current_row].to_dict())
                customer_blocks[customer_name] = block_data
                current_row += 1
        else:
            # Handle edge case: No Customer name row after Response Comments
            print(f"Warning: Missing Customer name row at position {current_row}")
            current_row += 1
    else:
        # Skip any rows that aren't block starters
        current_row += 1

Step 3: Use the Extracted Data #

Now customer_blocks is a dictionary where each key is a customer name, and the value is a list of dictionaries (each representing a row in their block). If you prefer working with DataFrames for each block, you can modify the final step to convert the list:

# Convert each block to a DataFrame for easier analysis
customer_dfs = {name: pd.DataFrame(block) for name, block in customer_blocks.items()}

# Example: Access a specific customer's data
print(customer_dfs["John Doe"])

Key Notes to Adjust for Your Data #

• Column Index: If the "Customer name" value isn't in the second column (index 1), change df.iloc[current_row, 1] to the correct column index.
• Missing Blocks: The code includes a warning for incomplete blocks (e.g., no Customer name or Recommended row) — you can expand this to log errors or handle missing data as needed.
• Optional Rows: Any non-required rows between Customer name and Recommended are automatically included in the block, so you don't need to modify the loop for those.

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