使用pandas标识两列之间的关系

2024-03-28 18:26:06 发布

您现在位置:Python中文网/ 问答频道 /正文

我在数据框中有两列,如下所示,即字母和数字

Dataframe

我想做以下事情

  1. 在上表中,字母A在“字母”列中重复了两次,我想在新列中将其归类为“一对多”
  2. 15在数字列中重复了两次,我想将其归类为“多对一”
  3. 字母B、C和数字5、6在每列中仅出现一次,因此应归类为一对一
  4. 对于其他项目,应按多对多分类

预期产出如下所示。 Output

  1. 我尝试通过移动列名来使用groupby函数,这有助于分别识别项目1和项目2

我想在单功能中完成它,请帮助


Tags: 数据项目函数字母分类数字事情中将
2条回答

您可以编写如下函数:

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)

这可能是你的解决方案


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

相关问题 更多 >