Pandas Identify Rows where two columns have same strings

One option to ignore the order between two columns is to sort each row within itself, which np.sort can do. Then you can form a new dataframe with these ordered rows. duplicated with keep=False will mark all the duplicated rows as True which we can use as a mask to index the original dataframe:

rows_sorted_df = pd.DataFrame(np.sort(df))
dups = rows_sorted_df.duplicated(keep=False)
result = df[dups]

to get

>>> rows_sorted_df

     0    1
0  BOS   SF
1   LA   SF
2   LA  NYC
3  BOS   SF

>>> dups

0     True
1    False
2    False
3     True

>>> result

  Source destination
0    BOS          SF
3     SF         BOS

If you separate your columns into individual dataframes, you can then merge them to get only the rows that match.

(In this example I renamed the column to 'code' in each df; alternatively you could specify the right_on and left_on arguments in the merge call.)

s = my_df['Source'].reset_index().rename(columns={'Source':'code', 'index':'source_index'})
d = my_df['destination'].reset_index().rename(columns={'destination':'code', 'index':'dest_index'})

sd = pd.merge(s, d)


In: sd
Out: 
   source_index code  dest_index
0             0  BOS           3
1             1   SF           0
2             3   SF           0
3             2   LA           1

This is what I came up with, thinking with Joins.

    import pandas as pd
    dt = [['BOS', 'SF'], ['SF', 'LA'], ['LA', 'NYC'], ['SF', 'BOS'],
          ]
    df = pd.DataFrame(columns=['Source', 'destination'], data=dt)
    df

calculate the matches between destination -> source

    left = df.copy()
    right = df.copy().set_index("Source")
    
    dest_to_source = left.join(right, on=[left["destination"]], 
    lsuffix='_original', rsuffix="_matched", how="inner")
    # pandas joins do weird things to the column names, so renaming and reordering
    dest_to_source.columns = ["Source_matched","Source_original","destination_original","destination_matched"]
    dest_to_source = dest_to_source[["Source_original","destination_original","Source_matched","destination_matched"]]
    dest_to_source

and this is the result:

then you can do the same thing for the source -> destination (for the "vice-versa" part of the question)\

    right = df.copy().set_index("destination")
    source_to_dest = left.join(right, on=[left["Source"]], lsuffix='_original',rsuffix='_matched', how="inner")
    source_to_dest.columns = ["destination_matched","Source_original","destination_original","Source_matched"]
    source_to_dest = source_to_dest[["Source_matched","destination_matched","Source_original","destination_original"]]
    source_to_dest

IMO, the best way to do this is by using join like so:

import pandas as pd

dt = [
  ['BOS','SF'],
  ['SF','LA'],
  ['LA','NYC'],
  ['SF','BOS'],
]

my_df = pd.DataFrame(columns = ['Source','destination'], data = dt)
source_df = my_df.set_index('Source')
dest_df = my_df.set_index('destination')
joined_df = source_df.join(dest_df)

print(joined_df)

This is the output (a bit confusing at first):

    destination Source
BOS          SF     SF
LA          NYC     SF
SF           LA    BOS
SF          BOS    BOS

If we think about it a bit deeper it makes sense. Let's take these rows from the input:

  ['SF','LA'],
  ['LA','NYC'],

Our join turns these rows into "source -> middle -> dest", or "SF -> LA -> NYC". Looking at our resulting table

    destination Source
LA          NYC     SF

We can read this as "From the Source column 'SF' to the destination column 'NYC' through the index of the row 'LA'. If you would like the dataframe to be more readable, you can can add the following:

joined_df.index.name = 'middle'
joined_df = joined_df.reset_index()
print(joined_df)