I want to groupby, and then, create a new column which takes a value from a different column if a co-CodePudding

I have the following dataframe. I want to create a new column col2 which takes a value from the column value after groupby ID, if the value from col1 is BX.

and another new column col3 which takes the value from value if the value from `col1 is AX.

ID value col1
A    1    BX
A    2    AX
B    3    BX
B    4    AX
C    5    BX
C    6    AX

desired df

ID value col1  col2 col3
A    1    BX    1    2
A    2    AX    1    2
B    3    AX    4    3    
B    4    BX    4    3 
C    5    BX    5    6
C    6    AX    5    6

CodePudding user response：

You can perform two merges: one where col1 = 'BX' and one where col1 = 'AX'.

df = df.merge(df.loc[df['col1'] == 'BX', ['ID', 'value']].rename({'value': 'col2'}, axis=1), on='ID')
df = df.merge(df.loc[df['col1'] == 'AX', ['ID', 'value']].rename({'value': 'col3'}, axis=1), on='ID')

Output:

  ID  value col1  col2  col3
0  A      1   BX     1     2
1  A      2   AX     1     2
2  B      3   BX     3     4
3  B      4   AX     3     4
4  C      5   BX     5     6
5  C      6   AX     5     6

CodePudding user response：

Assuming there can only be one value for ax and bx try using pivot and merge. Pivot will take all the values in col1 and create new columns for each unique value. When merged, all the unique values in col1 will be added as a new column in new_df

p = df.pivot(index='ID', columns='col1', values='value')
new_df = df.merge(p, left_on='ID', right_index=True, how='left')

  ID  value col1  AX  BX
0  A      1   BX   2   1
1  A      2   AX   2   1
2  B      3   BX   4   3
3  B      4   AX   4   3
4  C      5   BX   6   5
5  C      6   AX   6   5