Pandas多索引分组求和与数据展示格式调整求助

Hey there! Let's work through this problem together—since you're new to programming, I'll break everything down step by step so it makes sense.

First, let's clarify what you need: you want to group your data by each server (Client), display all the related policy/schedule/volume entries for that server, then add a total row that sums up all the volumes for the server. You also want the visual effect of merged Client cells (which we can simulate in Markdown by leaving repeated Client names blank).

It sounds like you ran into issues with groupby—that's totally normal when you're starting out! Let's use Python's pandas library (the go-to tool for this kind of data manipulation) to fix this. Here's a complete, working solution:

Step 1: Set up your data #

First, we'll load your data into a pandas DataFrame. If you're reading this from a text file, you can use pd.read_csv() instead of the manual data list I'm using here:

import pandas as pd

# Your raw data (replace this with pd.read_csv("your_file.txt", sep=" ") if using a file)
raw_data = [
    ["server 1", "policy 1", "full2", 1086],
    ["server 1", "policy 1", "diff2", 100.5],
    ["server 3", "policy 5", "full3", 500],
    ["server 1", "policy 3", "app", 10],
    ["server 2", "policy 2", "full1", 75],
    ["server 2", "policy 2", "diff1", 62]
]

# Create a DataFrame with column names matching your desired output
df = pd.DataFrame(raw_data, columns=["Client", "policy name", "schedule name", "volume"])

Step 2: Group by Client and build the formatted tables #

We'll loop through each server group, add a total row, and format the Client column to simulate merged cells:

# Group the data by the "Client" column
for client_name, group_data in df.groupby("Client"):
    # Create a total row for the current server's volume sum
    total_row = pd.DataFrame({
        "Client": [""],
        "policy name": ["total"],
        "schedule name": [""],
        "volume": [group_data["volume"].sum()]
    })
    
    # Combine the original group data with the total row
    combined_group = pd.concat([group_data, total_row], ignore_index=True)
    
    # Blank out the Client column for all rows except the first one (simulate merged cells)
    combined_group.loc[1:, "Client"] = ""
    
    # Print the Markdown table for this server
    print("|Client |policy name |schedule name |volume|")
    print("|---|---|---|---|")
    for _, row in combined_group.iterrows():
        print(f"|{row['Client']} |{row['policy name']} |{row['schedule name']} |{row['volume']}|")
    print("\n")  # Add a blank line between tables

What this does: #

• groupby("Client") clusters all rows belonging to the same server together—no more errors here because we're iterating over each group explicitly, which is a common pitfall for new users.
• We create a total_row that calculates the sum of the volume column for the group, using pandas' built-in sum function which handles float values perfectly.
• By setting combined_group.loc[1:, "Client"] = "", we make all Client entries after the first one empty, which gives the visual effect of merged cells in Markdown (since true cell merging isn't supported in standard Markdown tables).
• Finally, we print each group as a separate Markdown table, just like you wanted.

The output will look like this: #

A quick note: I noticed in your example you had 65 for server 2's diff1 entry, but your raw data uses 62—this code uses your actual raw data values, so the total for server 2 is 137 (75+62), which matches your example's total.

If you're still confused about any part, feel free to ask—I'm happy to clarify further!

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