使用Openpyxl将Excel转为Python字典以实现数据过滤的技术求助

Hey there! Let's work through this together to fix your data reading issue and get that date interval calculation sorted out. I'll break it down step by step so it's easy to follow even if you're new to Python.

First, let's look at why your original code wasn't working right:

• You were overwriting the SalesFunnel[row] value every time you looped through a column (A/D/E/F), so only the last column's data got saved
• The line SalesFunnel[row].append(SalesFunnel[row]) was creating a messy nested structure by adding the list to itself
• You weren't mapping the columns to meaningful field names (like linking column A to "Index", column D to "Email", etc.)

Here's a revised version that reads your data into structured dictionaries, matching the JSON format you want:

from collections import defaultdict
import openpyxl
from datetime import datetime

# Map Excel columns to your desired field names (adjust if your columns are different!)
column_mapping = {
    'A': 'Index',
    'D': 'Email',
    'E': 'LeadStatus',
    'F': 'CreatedDate'
}

# Store all your lead data as a list of dictionaries
lead_data = []

# Load the workbook - use data_only=True to read cell values instead of formulas
workbook = openpyxl.load_workbook('Report.xlsx', data_only=True)
sheet_names = workbook.sheetnames

print(f"All sheet names in the workbook: {sheet_names}")
for sheet_name in sheet_names:
    print(f"Processing sheet: {sheet_name}")
    current_sheet = workbook[sheet_name]
    
    # Loop through rows - start at 2 if your first row is a header!
    for row_num in range(1, current_sheet.max_row + 1):
        row_dict = {}
        for col_letter, field_name in column_mapping.items():
            cell_value = current_sheet[f"{col_letter}{row_num}"].value
            
            # Convert CreatedDate to a datetime object for easy date math later
            if field_name == 'CreatedDate' and cell_value is not None:
                # Adjust the date format string if your dates use a different pattern (e.g. "%d/%m/%Y" for DD/MM/YYYY)
                row_dict[field_name] = datetime.strptime(str(cell_value), "%m/%d/%Y")
            else:
                row_dict[field_name] = cell_value
        
        lead_data.append(row_dict)

# Print a sample of your structured data to verify
print("\nSample of your loaded data:")
for item in lead_data[:5]:
    print(item)

Key Notes for This Code: #

• The column_mapping dict makes it easy to adjust which columns map to which fields if your Excel structure changes
• We convert CreatedDate to a datetime object so we can easily calculate date differences later
• If your Excel has a header row (first row is column names), change range(1, ...) to range(2, ...) to skip it

Now that we have clean, structured data, we can group rows by Email + LeadStatus and calculate the time between the earliest and latest CreatedDate for each group:

# Group dates by (Email, LeadStatus)
grouped_dates = defaultdict(list)
for lead in lead_data:
    # Skip rows missing critical data to avoid errors
    if not all(key in lead and lead[key] is not None for key in ['Email', 'LeadStatus', 'CreatedDate']):
        continue
    # Use a tuple (Email, LeadStatus) as the group key
    group_key = (lead['Email'], lead['LeadStatus'])
    grouped_dates[group_key].append(lead['CreatedDate'])

# Calculate min/max dates and interval days for each group
group_results = []
for (email, status), dates in grouped_dates.items():
    if len(dates) < 2:
        # Not enough dates to calculate an interval
        interval_days = None
    else:
        earliest_date = min(dates)
        latest_date = max(dates)
        interval_days = (latest_date - earliest_date).days
    
    # Format results into a readable dict
    group_results.append({
        'Email': email,
        'LeadStatus': status,
        'EarliestCreatedDate': earliest_date.strftime("%m/%d/%Y"),
        'LatestCreatedDate': latest_date.strftime("%m/%d/%Y"),
        'IntervalDays': interval_days
    })

# Print the final results
print("\nDate Intervals by Email + LeadStatus:")
for result in group_results:
    print(result)

What This Does: #

• Uses a defaultdict to collect all dates for each unique Email + LeadStatus pair
• Skips rows with missing data to prevent crashes
• Calculates the number of days between the oldest and newest date in each group
• Formats dates back to a human-readable string for the final output

If you need to export your lead_data to the exact JSON format you showed, you can use the json module:

import json

# Convert datetime objects to strings for JSON serialization
json_data = json.dumps(lead_data, indent=2, default=str)
print("\nJSON formatted data:")
print(json_data)

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