Select equal rows diagonally in a data frame?-CodePudding

I have two columns containing three rows. These rows could come in different orders.
I want to create a new Boolean column detecting which rows have equal values diagonally.

import pandas as pd

data1={'Column_1':['BR','BR','AC'],'Column_2':['AC','LF','LF']}
data2={'Column_1':['BR','AC','BR'],'Column_2':['AC','LF','LF']}
data3={'Column_1':['AC','BR','BR'],'Column_2':['LF','LF','AC']}

df1=pd.DataFrame(data1)
df2=pd.DataFrame(data2)
df3=pd.DataFrame(data3)

so as shown in the picture I want to add a new Boolean column Diagonal to the data frame that it can detect when column_1 is equal to column_2 diagonally. So for example column_1[2].eq(column_2[0]) as shown in the pictures. The code should work no matter what the order of the rows is and if possible even when I have more than 3 rows.

CodePudding user response：

IICU you want a new column Diagonal that is True if the row value of Column_1 is somewhere in Column_2 or vice versa, but not in the same row:

df['Diagonal'] = df.Column_1.isin(df.Column_2) | df.Column_2.isin(df.Column_1) & (df.Column_1 != df.Column_2)

Result:

  Column_1 Column_2  Diagonal
0       BR       AC      True
1       BR       LF     False
2       AC       LF      True

Update for additional question in comment:

If you want to do the same for individual groups instead of the whole dataframe, you can apply the operation groupwise. Example:

import pandas as pd

df=pd.DataFrame({'Column_1':['BR','BR','AC','BR','AC','AD'],'Column_2':['AC','LF','LF','AD','LF','LF'],'Column_3':['x','x','x','y','y','y']})
df['Diagonal'] = df.groupby('Column_3').apply(
    lambda x: x.Column_1.isin(x.Column_2) | x.Column_2.isin(x.Column_1) & (x.Column_1 != x.Column_2)).values

Result (note that the last but one row is False despite AC being in Column_1 because it's in another group on Column_3):

  Column_1 Column_2 Column_3  Diagonal
0       BR       AC        x      True
1       BR       LF        x     False
2       AC       LF        x      True
3       BR       AD        y      True
4       AC       LF        y     False
5       AD       LF        y      True