遍历CSV文件按Column A唯一值累计Column B并输出至新CSV的实现问题

Got it, let's work through this problem—since you already know how to read and write CSV files, the main piece you're missing is tracking cumulative totals for each unique entry in Column A. I'll show you two straightforward ways to do this: using Python's built-in csv module (no extra dependencies) and using pandas (for a more concise approach).

Using Python's Built-in csv Module #

This method is great if you want to stick to standard libraries and understand every step of the process.

Step-by-Step Logic: #

1. Initialize a dictionary to keep track of running totals—keys will be the unique values from Column A, values will be their cumulative sums from Column B.
2. Read the input CSV, iterate over each row, and update the dictionary with each value from Column B.
3. Write the final totals from the dictionary into a new CSV file.

Code Example: #

import csv

# Define your input and output file paths
input_csv = "target.csv"
output_csv = "running_totals.csv"

# Dictionary to store cumulative totals for each unique Column A value
running_totals = {}

# Read the input CSV
with open(input_csv, mode='r', newline='', encoding='utf-8') as infile:
    # Use DictReader to access columns by name (no need to remember indices)
    reader = csv.DictReader(infile)
    
    for row in reader:
        # Extract values from Column A and Column B
        unique_key = row['Column A']
        # Convert Column B to integer (use float() if your values are decimals)
        current_value = int(row['Column B'])
        
        # Update the running total: if the key doesn't exist, start at 0 + current value
        running_totals[unique_key] = running_totals.get(unique_key, 0) + current_value

# Write the results to a new CSV
with open(output_csv, mode='w', newline='', encoding='utf-8') as outfile:
    writer = csv.writer(outfile)
    # Write header row
    writer.writerow(["Unique Value in Column A", "Running Total"])
    # Write each unique key and its total
    for key, total in running_totals.items():
        writer.writerow([key, total])

Key Notes: #

• The running_totals.get(unique_key, 0) method is a clean way to handle new keys—no messy if/else checks needed.
• If your Column B values are floating-point numbers (e.g., 10.5), replace int() with float().
• For Python versions before 3.7, dictionaries don't preserve insertion order. If you want the output to match the order of first occurrence in the input CSV, use collections.OrderedDict instead of a regular dictionary.

Using pandas (Simpler, for Larger Datasets) #

If you're open to using a third-party library, pandas makes this task extremely concise—perfect for larger CSV files or when you need to do more data manipulation later.

Code Example: #

import pandas as pd

# Read the input CSV into a DataFrame
df = pd.read_csv("target.csv")

# Group by Column A and calculate the sum of Column B for each group
total_df = df.groupby('Column A')['Column B'].sum().reset_index()

# Optional: Rename columns for clarity in the output
total_df.columns = ["Unique Value in Column A", "Running Total"]

# Write the results to a new CSV (exclude the default index with index=False)
total_df.to_csv("running_totals.csv", index=False)

Key Notes: #

• groupby('Column A') clusters all rows with the same value in Column A together.
• .sum() calculates the cumulative total for Column B in each group.
• .reset_index() converts the grouped index back into a regular column, so your output CSV has a clean structure.

For your sample input, both methods will produce this output CSV:

Unique Value in Column A,Running Total
Report 1,25
Report 2,15
Report 3,35

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