Pandas finding transitive relation from tuples A and B (two columns)

user_13745250 0
Viewed 145

Now hello, what I would like is showing hirarchy of likes. People from column 1 can like someone from column 2. Basically it'd be ideal having 4 columns A, B, C, D which show for every person who they like and for that person the next one etc. Basically from (a, b) tuples to (a, b), (b, c), (c, d). I only know it must be recursive but I have for example no clue how you can check in Pandas different columns and check them and that in a recursive manner. So multiple people can like someone but not everyone has to like someone. But if that's the case, it can only happen over 3 people.

So, I have a dataframe like this:

import pandas as pd

d = {'col1': ['Ben', 'Mike', 'Carla', 'Maggy', 'Josh', 'Kai', 'Maria', 'Sophie'], 'col2': ['Carla', 'Carla', 'Josh', 'Ben', 'Lena', 'Maggy', 'Mike', 'Chad']}
df = pd.DataFrame(data=d)
df

I would like an output like this:

d = {'A': ['Ben', 'Mike', 'Carla', 'Maggy', 'Josh', 'Kai', 'Maria', 'Sophie'], 'B': ['Carla', 'Carla', 'Josh', 'Ben', 'Lena', 'Maggy', 'Mike', 'Chad'], 'C': ['Josh', 'Josh', 'Lena', 'NA', 'NA', 'Ben', 'Carla', 'NA'], 'D': ['Lena', 'Lena', 'NA', 'NA', 'NA', 'NA', 'Josh', 'NA']}
df = pd.DataFrame(data=d)
df

I think the rules are like that:

  1. Someone (column B) can be liked from someone (from column A) but that somebody (column B) doesn't like anyone. (like Chad doesn't like anyone)
  2. Someone can be liked by only one person (A -> B -> NA -> NA)
  3. Someone can like somebody, that somebody likes someone else. (A -> B -> C -> NA)
  4. Someone can like someone, who likes someone else. And that someone likes someone as well. (A -> B -> C-> D -> NA)

How can I achieve this? Thank you

relation pandas recursion algorithm python
1 Answers

What you need is a couple of left-join (merge) operations.

Here's the code, broken to a couple of steps for clarity: 

step1 = pd.merge(df, df, left_on="col2", right_on="col1", how = "left")
step1 = step1[["col1_x", "col2_x", "col2_y"]]
step1.columns = ["first", "second", "third"]

step2 = pd.merge(step1, df, left_on="third", right_on= "col1", how = "left")
res = step2.drop("col1", axis=1).rename(columns={"col2": "fourth"})
print(res)

The result is: 

    first second  third fourth
0     Ben  Carla   Josh   Lena
1    Mike  Carla   Josh   Lena
2   Carla   Josh   Lena    NaN
3   Maggy    Ben  Carla   Josh
4    Josh   Lena    NaN    NaN
5     Kai  Maggy    Ben  Carla
6   Maria   Mike  Carla   Josh
7  Sophie   Chad    NaN    NaN
user_11055600
Related