CODEWITHSUNDEEP
pythonpandas
user11953618
user11953618

Reputation: 69

Identify Relationship between two columns using pandas

I have two columns in a dataframe as follows, namely Letter and Number

Dataframe

I want to do following

  1. In the above table letter A is repeated two times in column "Letter" which I want to classify as "One to Many" in a new column.
  2. 15 is repeated two times in number column which i want to classify as "many to one".
  3. Letter B, C and Number 5, 6 occurred only one time in each column therefore should be classified as one to one.
  4. For other should be classified as many to many.

Expected output is shown below. Output

  1. I tried using groupby function by shifting the column name, it helped to identify item 1 and item 2 separately.

I want to do it in single function, Please help.....

Upvotes: 0

Views: 1568

Answers (2)

Akash senta
Akash senta

Reputation: 493

This could be your solution


import pandas as pd

d1 = ['A','A','B','C','D','E','F','G','F','G']
d2 = [10,11,5,6,15,15,20,20,25,28]

df = pd.DataFrame(list(zip(d1,d2)), columns = ['col1', 'col2'])


df['one to one'] = (df.groupby('col2')['col1'].transform(lambda x:x.nunique()==1) & df.groupby('col1')['col2'].transform(lambda x:x.nunique()==1))


df['many to one'] = (df.groupby('col2')['col1'].transform(lambda x:x.nunique()>1) & df.groupby('col1')['col2'].transform(lambda x:x.nunique()==1))


df['one to many'] = (df.groupby('col1')['col2'].transform(lambda x:x.nunique()>1) & df.groupby('col2')['col1'].transform(lambda x:x.nunique()==1))



df['many to many'] = (df.groupby('col1')['col2'].transform(lambda x:x.nunique()>1) & df.groupby('col2')['col1'].transform(lambda x:x.nunique()>1))


import numpy as np

conditions = [
    (df['one to one'] == True), (df['one to many'] == True),(df['many to one'] == True),(df['many to many'] == True)]
choices = ['one to one', 'one to many', 'many to one','many to many']
df['relation'] = np.select(conditions, choices)


df.drop(['one to one', 'one to many', 'many to one','many to many'], axis = 1)

output

Upvotes: 1

Jonas
Jonas

Reputation: 1769

You could write a function like this:

import pandas as pd

letter = ['A', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'F', 'G']
number = [10,11,5,6,15,15,20,20,25,28]
data = {'letter': letter, 'number': number}    
df = pd.DataFrame(data)

def relationship(letter, number):
    number_of_letters = {}
    number_of_numbers = {}
    relationship = [] 

    for i in letter:
        if i in number_of_letters:
            number_of_letters[i] += 1
        else:
            number_of_letters[i] = 1    
    for i in number:
        if i in number_of_numbers:
            number_of_numbers[i] += 1
        else:
            number_of_numbers[i] = 1    
    for i in range(len(letter)):
        if number_of_letters[letter[i]] == 1 and number_of_numbers[number[i]] == 1:
            relationship.append('One to One')
        elif number_of_letters[letter[i]] > 1 and number_of_numbers[number[i]] == 1:
            relationship.append('One to Many')
        elif number_of_letters[letter[i]] == 1 and number_of_numbers[number[i]] > 1:
            relationship.append('Many to One') 
        elif number_of_letters[letter[i]] > 1 and number_of_numbers[number[i]] > 1:
            relationship.append('Many to Many') 

    return relationship 

df['relationship'] = relationship(letter, number)

Upvotes: 1

Related Questions