Updating a column if condition is met with pandas

user_11919592 0
Viewed 165

I have a data frame to work on and I am performing several checks.

I am checking whether the repeating values under the columns "A", "B" and "C" present the same number but with opposite sign under column D.

A B C D E
1111 AAA 123 0.01 comment to be replaced
2222 BBB 456 5 comment to be replaced
3333 CCC 789 10 don't do anything
1111 AAA 123 -0.01 comment to be replaced
2222 BBB 456 -5 comment to be replaced
3333 CCC 789 -9 don't do anything

Please see my code below. When I try replacing the comment under the column "E", it does not work. I am sure that I am doing something wrong. I am fully aware of the fact that I haven't written the code in the most efficient way, I am still a newbie. Would you be able to help me with both a more efficient way to achieve this and, out of curiosity, how this could be achieved if I decided to keep using this "non-efficient" way?

Thank you.

for i in range(0, len(df)-1):
    for j in range(i+1, len(df)):
        if (df['A'][i] == df['A'][j]) & (df['B'][i] == df['B'][j]) & (df['C'][i] == df['C'][j]) & (df['D'][i] + df['D'][j] = 0) :
            df['E'][i] = 'it works!'
pandas dataframe python
2 Answers

We can group the dataframe on columns A, B, C along with series of absolute values in column D then transform the column D using sum (because if the pairs have opposite sign then there sum must be zero) to check for the presence of pairs having same magnitude but opposite sign

df['E'] = df.groupby(['A', 'B', 'C', df['D'].abs()])['D'].transform('sum').eq(0) 

      A    B    C      D      E
0  1111  AAA  123   0.01   True
1  2222  BBB  456   5.00   True
2  3333  CCC  789  10.00  False
3  1111  AAA  123  -0.01   True
4  2222  BBB  456  -5.00   True
5  3333  CCC  789  -9.00  False
user_128331660

This works in case you have more than one pair in E or if there is 1 positive and multiple negative and vice versa.

import pandas as pd
import numpy as np

df_1 = df[df['D'] >= 0].copy().reset_index()
df_2 = df[df['D'] < 0].copy().reset_index()
df_2['D'] = -df_2['D']

indexes = df_1.merge(df_2, on=['A', 'B', 'C', 'D'], how='inner')[['index_x', 'index_y']].values.tolist()
indexes = [item for sublist in indexes for item in sublist]

df['E_new'] = np.where(df.index.isin(indexes), 'new comment', df['E'])

print(df)

#       A    B    C      D                       E              E_new
# 0  1111  AAA  123   0.01  comment to be replaced        new comment
# 1  2222  BBB  456   5.00  comment to be replaced        new comment
# 2  3333  CCC  789  10.00       don't do anything  don't do anything
# 3  1111  AAA  123  -0.01  comment to be replaced        new comment
# 4  2222  BBB  456  -5.00  comment to be replaced        new comment
# 5  3333  CCC  789  -9.00       don't do anything  don't do anything
user_119717850
Related