find a way in python/pandas to cross information between couple of columns-CodePudding

I have this database:
   id power_count  power_name  type    second_power   second_power_type
0 001 1            fire        attack  nan            nan
1 001 1            fire        attack  nan            nan
2 002 2            water       defense nan            nan
3 002 2            sand        attack  nan            nan
4 002 2            sand        attack  nan            nan

And I want to get to this:

   id power_count  power_name  type    second_power   second_power_type
0 001 1            fire        attack  nan            nan
1 001 1            fire        attack  nan            nan
2 002 2            water       defense sand           attack
3 002 2            sand        attack  water          defense
4 002 2            sand        attack  water          defense

So only in the rows that have 2 power count add the information on the other power

I tried to do a loop for doing it but didn't succeed.

CodePudding user response：

dict1 = {'water':'sand', 'sand':'water', 'defense':'attack', 'attack':'defense'}
df.loc[df['power_count'] == 2, ['second_power', 'second_power_type']] = df.loc[df['power_count'] == 2, ['power_name', 'type']].replace(dict1).to_numpy()
df

df

    id  power_count power_name  type    second_power    second_power_type
0   1   1           fire        attack  NaN             NaN
1   1   1           fire        attack  NaN             NaN
2   2   2           water       defense sand            attack
3   2   2           sand        attack  water           defense
4   2   2           sand        attack  water           defense