求助：为多索引DataFrame添加各任务类型的完成率行

Got it, let's walk through how to add that % Completed row for each task type (A-E) in your table. I’ll cover two common scenarios: using Pandas (for data analysis workflows) and Excel (for spreadsheet users).

Option 1: Using Pandas (Python) #

First, let's assume your data is structured as a Pandas MultiIndex DataFrame. Here's how to calculate and insert the completion rate rows:

Step 1: Recreate Your Original Data (if not already in Pandas) #

import pandas as pd

# Build your original multi-index table
data = [
    ["A", "Total", 181.0, 85.0, 69.0, 15.0],
    ["A", "Complete", 33.0, 10.0, 0.0, 0.0],
    ["B", "Total", 13.0, 12.0, 5.0, 1.0],
    ["B", "Complete", 5.0, 9.0, 0.0, 1.0],
    ["C", "Total", 137.0, 89.0, 78.0, 22.0],
    ["C", "Complete", 66.0, 54.0, 27.0, 12.0],
    ["D", "Total", 629.0, 203.0, 174.0, 51.0],
    ["D", "Complete", 451.0, 127.0, 87.0, 28.0],
    ["E", "Total", 135.0, 100.0, 86.0, 24.0],
    ["E", "Complete", 46.0, 27.0, 29.0, 2.0],
]

df = pd.DataFrame(data, columns=["Task Type", "Metric", "June", "July", "August", "September"])
df = df.set_index(["Task Type", "Metric"])

Step 2: Calculate & Insert Completion Percentage Rows #

# Get all unique task types (A-E)
task_types = df.index.get_level_values("Task Type").unique()
new_rows = []

for task in task_types:
    # Grab Total and Complete values for the task
    total_values = df.loc[(task, "Total")]
    complete_values = df.loc[(task, "Complete")]
    
    # Calculate completion rate (handle division by zero by setting to 0 if needed)
    completion_pct = complete_values / total_values
    completion_pct = completion_pct.round(2)  # Round to 2 decimal places for readability
    
    # Add the new % Completed row to our list
    new_rows.append(pd.Series(completion_pct, name=(task, "% Completed")))

# Merge new rows with original DataFrame
df_with_pct = pd.concat([df] + new_rows)

# Reorder indices to keep Total → Complete → % Completed for each task
df_with_pct = df_with_pct.reindex(
    pd.MultiIndex.from_tuples(
        [(task, metric) for task in task_types for metric in ["Total", "Complete", "% Completed"]]
    )
)

# View the final table
print(df_with_pct)

Final Output Preview #

Option 2: Using Excel #

If you're working directly in a spreadsheet:

1. For Task A, click the cell below its Complete row (in the June column).
2. Enter the formula: =C3/C2 (adjust cell references to match your sheet—C2 = Total value, C3 = Complete value).
3. Drag the formula horizontally to fill in values for July, August, and September.
4. Copy this entire % Completed row, then paste it below the Complete row for Tasks B, C, D, and E.
   • To avoid broken references, use mixed references like =C3/C$2 if your Total rows are fixed in position.

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