Concatenate Pandas Dataframe on rows based on group and column value

May 9, 2023

I have a Pandas DataFrame like this:

df = pd.DataFrame({'GROUP':['A1', 'A1', 'B2', 'B2', 'B2', 'C3', 'C3'], 'X':[1, -1, -1, 1, -1, -1, 1], 'Y': [-1, 0, 1, -1, -1, -1, -1], 'Z': [-1, -1, -1, -1, 0, 1, -1]})
df
      GROUP     X     Y     Z
0        A1     1    -1    -1
1        A1    -1     0    -1   
2        B2    -1     1    -1
3        B2     1    -1    -1
4        B2    -1    -1     0  
5        C3    -1    -1     1
6        C3     1    -1    -1

I want to conditionally concatenate the df for each groupby GROUP, such that it concatenates the value in the column to either 1 or 0 (both same priority) if they are there for the group, else to -1.

The expected result:

      GROUP     X     Y     Z
0        A1     1     0    -1
1        B2     1     1     0
2        C3     1    -1     1

>Solution :

If there is always only one 0 or 1 per groups is possible aggregate max:

out = df.groupby('GROUP', as_index=False).max()
print (out)
  GROUP  X  Y  Z
0    A1  1  0 -1
1    B2  1  1  0
2    C3  1 -1  1

If both 0 and 1 per group and 0 has higher priority, you can use:

df = pd.DataFrame({'GROUP':['A1', 'A1', 'B2', 'B2', 'B2', 'C3', 'C3'],
                   'X':[1, 0, -1, 1, -1, -1, 1],
                   'Y': [-1, 0, 1, -1, -1, -1, -1], 
                   'Z': [-1, -1, -1, -1, 0, 1, -1]})
print (df)
  GROUP  X  Y  Z
0    A1  1 -1 -1
1    A1  0  0 -1
2    B2 -1  1 -1
3    B2  1 -1 -1
4    B2 -1 -1  0
5    C3 -1 -1  1
6    C3  1 -1 -1

df1 = df.groupby('GROUP').max().mask(df.groupby('GROUP').min().eq(0), 0).reset_index()
print (df1)
  GROUP  X  Y  Z
0    A1  0  0 -1
1    B2  1  1  0
2    C3  1 -1  1