Find and link values across a dataframe in pandas

user_6591555 0
Viewed 85

In a pandas dataframe such as:

data = [['Ben', 'Apple', 'Banana'], ['Nancy', 'Pear', 'Apple'], ['Paul', 'Banana', 'Mango'], ['Sally', 'Mango', 'Pear']] 
df = pd.DataFrame(data, columns = ['Name', 'Like', 'Dislike']) 
df 

    Name    Like    Dislike
0   Ben     Apple   Banana
1   Nancy   Pear    Apple
2   Paul    Banana  Mango
3   Sally   Mango   Pear

How would I match the fruits in the Like and Dislike columns and create a df or list of who should trade with who?

I would like to return a df of something like:

trades = [['Ben', 'Paul'], ['Paul', 'Sally'], ['Sally', 'Nancy'], ['Nancy', 'Ben']]
tradesDF = pd.DataFrame(trades, columns = ['From', 'To'])



    From    To
0   Ben     Paul
1   Paul    Sally
2   Sally   Nancy
3   Nancy   Ben

The part I am struggling with the most is the match finding, I understand how to compare rows and columns, but individual values across the DF is a struggle, and seems like there needs to be some recursive method here. Thanks.

pandas matching dataframe python
3 Answers

This is a standard join/merge operation used by relational databases. Below, I've joined the dataframe to itself on the "Like", "Dislike" columns, and renamed them:

df2 = pd.merge(left=df, right=df, left_on="Dislike", right_on="Like")
df2 = df2.rename(columns={"Name_x":"From", "Name_y":"To"})[["From", "To"]]

output:

    From     To
0    Ben   Paul
1  Nancy    Ben
2   Paul  Sally
3  Sally  Nancy
user_50329410

I guess this one will help you :

trades=[]
for i in range(len(df)):
    if df.iloc[i]["Dislike"] in df["Like"].values:
        trades.append((df.iloc[i]["Name"],(df["Name"][df.iloc[i]["Dislike"] == df.Like.values]).item()))
user_134727250

You can use a self-join/merge with on the Dislike = Like, and return the names:

df_trade = df.merge(df, left_on='Dislike', right_on='Like',suffixes=('_from', '_to'))
df_trade.loc[:,['Dislike_from','Name_from','Name_to']]

Which returns the results:

enter image description here

user_12717720
Related