Pandas Groupby聚合指定列并保留全列，求大数据集优化方案

Hey there! Let's tackle this performance issue with your massive 14.6 million-row Pandas DataFrame — your current approach works, but it's doing a lot of unnecessary intermediate steps that eat up memory and time at scale. Here's a much more efficient way to get your desired result in one go:

The Core Problem with Your Current Approach #

Your existing code splits the work into multiple steps: grouping/summing, dropping duplicates, reindexing, and concatenating. Each of these creates temporary DataFrames, which is brutal for memory when dealing with millions of rows. We can condense all this into a single groupby operation.

Optimized Solution: Single Groupby with Aggregation Spec #

Since columns like typeOfSearch, price, and typeOfBuilding have identical values per _idMutation, we can use aggregation functions like first (or last, max — any will work as values are duplicate) to retain them, while summing surface and nbRoom in the same pass.

import pandas as pd
import numpy as np

# Your sample data
mre = [
    ["2018-1", "Sold", 109000.0, "Appartement", 73.0, 4.0],
    ["2018-1", "Sold", 109000.0, "Appartement", np.nan, 0.0],
    ["2018-2", "Sold", 239300.0, "House", 163.0, 4.0],
    ["2018-2", "Sold", 239300.0, "House", 51.0, 2.0],
    ["2018-2", "Sold", 239300.0, "House", 51.0, 2.0]
]
df = pd.DataFrame(mre, columns=["_idMutation", "typeOfSearch", "price", "typeOfBuilding", "surface", "nbRoom"])
df["surface"] = df["surface"].astype(float)

# Define aggregation rules for each column
aggregation_rules = {
    "typeOfSearch": "first",
    "price": "first",
    "typeOfBuilding": "first",
    "surface": "sum",
    "nbRoom": "sum"
}

# Run groupby and aggregate in one step
optimized_df = df.groupby("_idMutation", as_index=False).agg(aggregation_rules)

print(optimized_df)

Output (Matches Your Expected Result) #

_idMutation typeOfSearch     price typeOfBuilding  surface  nbRoom
0      2018-1         Sold  109000.0    Appartement     73.0     4.0
1      2018-2         Sold  239300.0          House    265.0     8.0

Why This Is Faster for Large Datasets #

• Single Pass: We only traverse the DataFrame once instead of multiple times (groupby + drop duplicates + concat).
• No Intermediate DataFrames: Avoids creating separate grouped and deduplicated DataFrames, which saves huge amounts of memory.
• Efficient Aggregation: Pandas' groupby.agg is optimized for batch operations, especially with explicit aggregation specs.

Extra Performance Tips for 14M Rows #

1. Optimize Data Types:

   • Convert _idMutation to a category type if there are far fewer unique mutations than rows:

     df["_idMutation"] = df["_idMutation"].astype("category")
     

   • Convert price to integer if possible (e.g., df["price"] = df["price"].astype(int)), as integers take less memory than floats.

2. Use Pandas 2.0+ with PyArrow:
   Enable PyArrow-backed data structures and aggregation for faster computation:

   pd.set_option("mode.copy_on_write", True)
   optimized_df = df.groupby("_idMutation", as_index=False).agg(aggregation_rules, engine="pyarrow")
   

3. Avoid inplace Operations:
   While inplace=True seems memory-efficient, it can lead to unexpected behavior and doesn't always save as much memory as you think. Assigning to new variables is safer and more predictable for large data.

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