pandas- change [ row_x , col_d ] value to [raw_y, col_a] based on comparison of [raw_x, col_b] & [raw_y, col_c]

Question

I have a table as below ( simplified one)

I want to select each raw and compare its Col_b value with all other raw's Col_c value . If they are equal, assign/append the matching raw's Col_a value to selected raw's Col_d value

Resultant table will look like below

The traditional method with multiple for/while loops I understand . Is there some efficient ways in pandas , numpy etc to solve this.

Answer 1

You can use transform and np.where:

df = pd.DataFrame({'Col_a':['A1','Z7.6','B1.1','C2','A1.3','P7.6'],
                  'Col_b':[120,-1,78,180,6,8],
                  'Col_c':[-7,45,120,32,180,120]})
def f(x):
    rowindx = np.where(df.Col_c == x)
    res = ' '.join(df.loc[rowindx[0], 'Col_a'])
    return res

df['Col_d'] = df['Col_b'].transform(f)
df

Output:

  Col_a  Col_b  Col_c      Col_d
0    A1    120     -7  B1.1 P7.6
1  Z7.6     -1     45           
2  B1.1     78    120           
3    C2    180     32       A1.3
4  A1.3      6    180           
5  P7.6      8    120           

---

You can try this using apply and np.where:

def f(x):
    rowindx = np.where(df.Col_c == x.Col_b)
    res = ' '.join(df.loc[rowindx[0], 'Col_a'])
    return res

df['Col_d'] = df.apply(f, axis=1)
print(df)

Output:

  Col_a  Col_b  Col_c      Col_d
0    A1    120     -7  B1.1 P7.6
1  Z7.6     -1     45           
2  B1.1     78    120           
3    C2    180     32       A1.3
4  A1.3      6    180           
5  P7.6      8    120