在Python中从Excel表格生成嵌套字典

First, let's define a logical nested structure based on your data—this groups requests by department (Dep), then by error code (Code), and includes the reason, count of requests, and list of request dates for each code:

{
    "P41": {
        "0060": {
            "reason": "Data Incomplete",
            "count": 3,
            "request_dates": ["15.02.2018", "02.02.2018", "14.02.2018"]
        },
        "0030": {
            "reason": "Typing error -> technical mix-up",
            "count": 1,
            "request_dates": ["07.02.2018"]
        }
    },
    # ... other departments follow the same pattern
}

Here are two ways to implement this in your get_reasons function:

Approach 1: Row-by-Row Iteration (Easy to Follow) #

This method is straightforward and great for understanding how the nested dictionary is built step-by-step. We'll use pandas (the go-to library for Excel data handling in Python):

import pandas as pd

def get_reasons(readfilename):
    # Read the Excel file into a structured DataFrame
    df = pd.read_excel(readfilename)
    
    # Initialize the empty nested dictionary
    nested_dict = {}
    
    # Loop through each row in the data
    for _, row in df.iterrows():
        dep = row["Dep"]
        req_date = row["request date"]
        code = str(row["Code"])  # Convert code to string for consistent key types
        reason = row["Reason"]
        
        # Add the department to the top level if it doesn't exist yet
        if dep not in nested_dict:
            nested_dict[dep] = {}
        
        # Add the code entry to the department if it's new
        if code not in nested_dict[dep]:
            nested_dict[dep][code] = {
                "reason": reason,
                "count": 0,
                "request_dates": []
            }
        
        # Update the count and add the request date
        nested_dict[dep][code]["count"] += 1
        nested_dict[dep][code]["request_dates"].append(str(req_date))
    
    return nested_dict

Approach 2: Using Pandas GroupBy (Efficient for Large Datasets) #

If you're working with a large Excel file, this method is faster since it leverages pandas' optimized grouping operations:

import pandas as pd

def get_reasons(readfilename):
    df = pd.read_excel(readfilename)
    
    # Convert code and date to strings to avoid type inconsistencies
    df["Code"] = df["Code"].astype(str)
    df["request date"] = df["request date"].astype(str)
    
    # Group data by department and code, then aggregate key details
    grouped = df.groupby(["Dep", "Code"]).agg(
        reason=("Reason", "first"),  # Assumes same code has same reason (matches your sample)
        count=("Reason", "size"),
        request_dates=("request date", list)
    ).reset_index()
    
    # Build the nested dictionary from the grouped results
    nested_dict = {}
    for _, row in grouped.iterrows():
        dep = row["Dep"]
        code = row["Code"]
        
        if dep not in nested_dict:
            nested_dict[dep] = {}
        
        nested_dict[dep][code] = {
            "reason": row["reason"],
            "count": row["count"],
            "request_dates": row["request_dates"]
        }
    
    return nested_dict

Quick Notes: #

• Assumption Check: Both approaches assume each Code maps to the same Reason across all rows (which is true for your sample data). If this isn't always the case, adjust the aggregation to collect unique reasons instead of taking the first one.
• Custom Excel Readers: If your existing read_excelfile function uses libraries like openpyxl or xlrd, replace the pd.read_excel call with your function, then adapt the iteration logic to loop through your row data instead of the pandas DataFrame.

To test this, just call the function with your Excel file path:

result = get_reasons("your_excel_file.xlsx")
print(result)

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