Combine and aggregate column values and create new column label in Pandas-CodePudding

I have a dataframe where I would like to combine and aggregate row values in Python

Data

 id type        q1 22   q2 22
 aa hey         2       1
 aa hey_plus    3       6
 aa hey_plus_1  2       1
 bb hi          1       0
 bb hi_1        3       4

Desired

id  type    q1 22   q2 22
aa  hey     7       8
bb  hi      4       4

Doing

df.groupby(['q1 22', 'q2 22']).sum()

Any suggestion is appreciated

CodePudding user response：

Just requires a bit of wrangling to get type to be what you want, using str.split.

df.groupby(["id", df["type"].str.split("_").str[0]]).sum()

         q122  q222
id type
aa hey      7     8
bb hi       4     4