Pandas Groupby aggregation function on specific columns, display all columns in results

user_11701439 0
Viewed 470

I'd like to have an grouby and sum based on an id, but display all columns as a result.

Example code

import pandas as pd
import numpy as np

mre = [
    ["2018-1", "Sold", 109000.0, "Appartement", 73.0, 4.0],
    ["2018-1", "Sold", 109000.0, "Appartement", "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)

# Rename columns
df.columns = ["_idMutation", "typeOfSearch",
              "price", "typeOfBuilding", "surface", "nbRoom"]

df["surface"] = df["surface"].astype(float)

print(df)

Base DataFrame

  _idMutation typeOfSearch     price typeOfBuilding  surface  nbRoom
0      2018-1         Sold  109000.0    Appartement     73.0     4.0
1      2018-1         Sold  109000.0    Appartement      NaN     0.0
2      2018-2         Sold  239300.0          House    163.0     4.0
3      2018-2         Sold  239300.0          House     51.0     2.0
4      2018-2         Sold  239300.0          House     51.0     2.0

Expected results

The groupby is based on _idMutation, it sums surface and sums nbRoom, but does not affect the other rows. I'd like to display all columns, deleting duplicates _idMutation and showing results of groupby

  _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

Current code

The following solution produces the expected result. I have 14.6 millions rows and the solution I came up with doesn't look optimized.

# Groupby on _idMutation & sum ["surface", "nbRoom"]
gb_df = df[["surface", "nbRoom"]].groupby(df["_idMutation"]).sum()

# Delete duplicates _idMutation
df.drop_duplicates(subset=["_idMutation"], inplace=True)

# Set _idMutation as df index
df.set_index("_idMutation", inplace=True)

# Concat df with gb_df
df = pd.concat(
    [df[["typeOfSearch", "price", "typeOfBuilding"]], gb_df], axis=1)
pandas-groupby pandas dataframe python
1 Answers

We can use GroupBy.agg and set our desired aggregation method per column using a dict. In this case we only need first and sum:

dfg = df.groupby("_idMutation", as_index=False).agg({
    "typeOfSearch": "first",
    "price": "first",
    "typeOfBuilding": "first",
    "surface": "sum",
    "nbRoom": "sum"
})

  _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
user_90812670
Related