如何用Pandas按特殊规则解析Excel列并直接生成聚合DataFrame

Problem Statement #

I need to use pandas to retrieve information from an Excel file. The sample data is as follows:

Time Value1 Value2
0 10 30.5 21.6
1 11 11 50.2
2 13 13 13.33
3 15 101.1 2
4 23 5 5
5 4 11.1 10

I need to generate a DataFrame containing all columns but filtered and processed: sum every two rows and put the result into a new DataFrame. The final result should look like this:

Time Value1 Value2
0 21 41.5 71.8
1 28 114.1 15.33
2 27 16.1 15

Note: Solutions that read the entire file first and then modify it are not allowed; the processed DataFrame must be generated directly.

Solution #

Since we can't load the entire file into memory first, we'll use pandas' chunked reading feature to process rows in batches of 2, summing each batch on the fly:

import pandas as pd

# Replace with your actual Excel file path
excel_file = "your_data.xlsx"

# Initialize a list to hold our summed rows
aggregated_data = []

# Read the Excel file in chunks of 2 rows at a time
for chunk in pd.read_excel(excel_file, chunksize=2):
    # Calculate the sum of the current chunk (sums across columns)
    summed_row = chunk.sum(axis=0)
    aggregated_data.append(summed_row)

# Combine all summed rows into the final DataFrame
result_df = pd.DataFrame(aggregated_data).reset_index(drop=True)

# Output the result
print(result_df)

How It Works #

• Chunked Reading: The chunksize=2 parameter tells pandas to load only 2 rows at a time, which avoids loading the entire file into memory upfront—perfect for your requirement.
• Row Summation: For each 2-row chunk, sum(axis=0) calculates the column-wise sum, giving us a single aggregated row for those two entries.
• Combine Results: We collect all summed rows into a list, then convert it to a final DataFrame. reset_index(drop=True) ensures our result has a clean, sequential index starting from 0, matching your expected output.

Verification #

Running this code with your sample Excel data will produce exactly the output you specified:

Time  Value1  Value2
0    21    41.5   71.80
1    28   114.1   15.33
2    27    16.1   15.00

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