Pandas Groupby特定列的聚合函数，显示结果中的所有列

Question

我希望有一个基于id的grouby和sum，但结果是显示所有列。

示例代码

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)

基础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

预期结果

groupby基于_idMutation，它对surface求和，对nbRoom求和，但不影响其他行。我想要显示所有列，删除重复的_idMutation并显示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

当前代码

下面的解决方案产生了预期的结果。我有1460万行，而我想出的解决方案看起来并不优化。

# 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)

Answer 1

我们可以使用GroupBy.agg，并使用字典为每列设置所需的聚合方法。在本例中，我们只需要first和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