CODEWITHSUNDEEP
pythonpandasdataframe
study.isLove
study.isLove

Reputation: 35

Pairing mutual values based on different column in Pandas

Suppose I have the next Data Set

      NAME   FRIEND
     --------------
     John    Ella
     John    Ben
     Ella    John
     Ella    Ben
     Dave    Ben
     ...
     More Values

I want to get a list of the mutual friends of John, Ella and Dave. In this example the output should be ['Ben'].

I've tried achieving this with loc but I wouldn't get the expected output, and would get 'friend's that aren't mutual. ['Ella', 'Ben', 'John', 'Ben']

Have searched for an answer for some time, couldn't find one that I might be duplicating.

Upvotes: 1

Views: 70

Answers (1)

mozway
mozway

Reputation: 260420

You can use a crosstab:

ct = pd.crosstab(df['NAME'], df['FRIEND'])

out = ct.columns[ct.all()].to_list()

Or with set operations:

s = df.groupby('FRIEND')['NAME'].agg(set)
out = s.index[s.eq(set(df['NAME']))].to_list()

Output: ['Ben']

Intermediate crosstab:

FRIEND  Ben  Ella  John
NAME                   
Dave      1     0     0
Ella      1     0     1
John      1     1     0

Intermediate s:

FRIEND
Ben     {Ella, Dave, John}
Ella                {John}
John                {Ella}
Name: NAME, dtype: object

if you want to specifically match {'Ella', 'Dave', 'John'}, even if the are other names in NAME:

target = ['Ella', 'Dave', 'John']

ct = pd.crosstab(df['NAME'], df['FRIEND'])

out = ct.columns[ct.reindex(target).all()].to_list()

Or;

target = {'Ella', 'Dave', 'John'}
s = df[df['NAME'].isin(target)].groupby(df['FRIEND'])['NAME'].agg(set)
out = s.index[s.eq(target)].to_list()

Upvotes: 2

Related Questions