使用Pandas在Python中查找基于名称的值之间的关系

Question

我想根据以下规则按名称建立值之间的关系：

1-我有一个CSV文件（超过100000行），其中包含许多值，我分享了以下示例：

    Name:
A02-father
A03-father
A04-father
A05-father
A07-father
A08-father
A09-father
A17-father
A18-father
A20-father
A02-SA-A03-SA
A02-SA-A04-SA
A03-SA-A02-SA
A03-SA-A05-SA
A03-SA-A17-SA
A04-SA-A02-SA
A04-SA-A09-SA
A05-SA-A03-SA
A09-SA-A04-SA
A09-SA-A20-SA
A17-SA-A03-SA
A17-SA-A18-SA
A18-SA-A17-SA
A20-SA-A09-SA
A05-NA
B02-Father
B04-Father
B06-Father
B02-SA-B04-SA
B04-SA-BO2-SA
B04-SA-B06-SA
B06-SA-B04-SA
B06-NA

2-现在我有另一个CSV文件，它让我知道应该从哪个值开始？在本例中，值为 A03父亲与母亲；B02父亲与。。。它们相互之间没有任何影响，它们都有各自的路径，所以对于每一条路径，我们将从所提到的起点开始。 父亲.csv A03父亲 B02父亲 ....

3-基于我想要建立关系的命名，因为A03父亲已被确定为父亲，我应该检查以A03开始的任何值。（所有这些都是A0的婴儿。） 同样由于B02是父级，我们将检查以B02开头的任何值。（B02-SA-B04-SA）

4-现在如果我发现A03-SA-A02-SA，这是A03的孩子。 我发现A03-SA-A05-SA，这是A03的宝宝。 我发现A03-SA-A17-SA，这是A03的宝宝

然后我必须检查任何以A02&；开头的节点；A05&；A17： 正如您所看到的，A02父亲存在，因此它是父亲，现在我们将搜索任何以A02开头且没有被检测为父亲的A03的字符串（必须忽略）

必须检查此项，直到CSV文件中存在的值结束。 正如您所看到的，我应该根据名称（REGEX）检查路径，并且应该一直前进到路径的末尾

预期结果：

    Father      Baby
A03-father   A03-SA-A02-SA
A03-father   A03-SA-A05-SA
A03-father   A03-SA-A17-SA
A02-father   A02-SA-A04-SA
A05-father   A05-NA
A17-father   A17-SA-A18-SA
A04-father   A04-SA-A09-SA
A02-father   A02-SA-A04-SA
A09-father   A09-SA-A20-SA
B02-father   B02-SA-B04-SA
B04-father   B04-SA-B06-SA
B06-father   B06-NA

我用熊猫将其编码如下：

import pandas as pd
import numpy as np
import re

#Read the file which consists of all Values
df = pd.read_csv("C:\\total.csv")


#Read the file which let me know who is father
Fa = pd.read_csv("C:\\Father.csv")

#Get the first part of Father which is A0
Fa['sub'] = Fa['Name'].str.extract(r'(\w+\s*)', expand=False)
r2 = []

#check in all the csv file and find anything which starts with A0 and is not Father
for f in Fa['sub']:
     baby=(df[df['Name'].str.startswith(f) & ~df['Name'].str.contains('Father')])
     baby['sub'] = bay['Name'].str.extract(r'(\w+\s*)', expand=False)
     r1= pd.merge(Fa, baby, left_on='sub', right_on='sub',suffixes=('_f', '_c'))
     r2.append(result1)
out_df = pd.concat(result2)
out_df= out_df.replace(np.nan, '', regex=True)
#find A0-N-A2-M and A0-N-A4-M
out_df.to_csv('C:\\child1.csv')



#check in all the csv file and find anything which starts with the second part of child1 which is A2 and A4
out_df["baby2"] = out_df['Name_baby'].str.extract(r'^(?:[^-]*-){2}\s*([^-]+)', expand=False)
baby3= out_df["baby2"]
r4 = []
for f in out_df["baby2"]:
    #I want to exclude A0 which has been detected.
     l = ['A0']  
     regstr = '|'.join(l)
     baby1=(df[df['Name'].str.startswith(f) & ~df['Name'].str.contains(regstr)])
     baby1['sub'] = baby1['Name'].str.extract(r'(\w+\s*)', expand=False)

     r3= pd.merge(baby3, baby1, left_on='baby2', right_on='sub',suffixes=('_f', '_c'))
     r4.append(r3)
out2_df = pd.concat(r4)
out2_df.to_csv('C:\\child2.csv')

我想把下面的代码放在一个循环中，根据命名过程检查文件，并检测其他父亲和婴儿，直到完成。然而，这段代码不是定制的，也没有我预期的准确结果。 我的问题是关于如何进行循环

<强>我应该遍历路径，也考虑任何字符串的{{CD1>}值。< /强>

#check in all the csv file and find anything which starts with the second part of child1 which is A2 and A4

out_df["baby2"] = out_df['Name_baby'].str.extract(r'^(?:[^-]*-){2}\s*([^-]+)', expand=False)
baby3= out_df["baby2"]
r4 = []
for f in out_df["baby2"]:
    #I want to exclude A0 which has been detected. 
     l = ['A0']  
     regstr = '|'.join(l)
     baby1=(df[df['Name'].str.startswith(f) & ~df['Name'].str.contains(regstr)])
     baby1['sub'] = baby1['Name'].str.extract(r'(\w+\s*)', expand=False)

     r3= pd.merge(baby3, baby1, left_on='baby2', right_on='sub',suffixes=('_f', '_c'))
     r4.append(r3)
out2_df = pd.concat(r4)
out2_df.to_csv('C:\\child2.csv')

Answer 1

注释内联

def find(data, from_pos=0):
  fathers = {}
  skip = []

  for x in data[from_pos:]:
    tks = x.split("-")

    # Is it father ?
    if tks[1].lower() == "father":
      fathers[tks[0]] = x
    else:    
      if tks[0] in fathers and tks[-2] not in skip:
        print (fathers[tks[0]], x)
        # Skip this father appearing as child later
        skip.append(tks[0])

测试用例：

data = [
'A0-Father', 
'A0-N-A2-M',
'A0-N-A4-M',
'A2-Father',
'A2-M-A0-N',
'A2-N-A8-M',
'A8-father',
'A8-M-A11-N',
'A8-M-A2-N']

find(data, from_pos=0)

输出：

A0-Father A0-N-A2-M
A0-Father A0-N-A4-M
A2-Father A2-N-A8-M
A8-father A8-M-A11-N

编辑1:

从一些测试数据开始

data = [
'A02-father',
'A03-father',
'A04-father',
'A05-father',
'A07-father',
'A08-father',
'A09-father',
'A17-father',
'A18-father',
'A20-father',
'A02-SA-A03-SA',
'A02-SA-A04-SA',
'A03-SA-A02-SA',
'A03-SA-A05-SA',
'A03-SA-A17-SA',
'A04-SA-A02-SA',
'A04-SA-A09-SA',
'A05-SA-A03-SA',
'A09-SA-A04-SA',
'A09-SA-A20-SA',
'A17-SA-A03-SA',
'A17-SA-A18-SA',
'A18-SA-A17-SA',
'A20-SA-A09-SA',
'A05-NA',
]

father = [
  'A03-father',
]

首先，让我们创建一个数据结构，这样操作会很容易，查找关系会很快，因为您有大量数据

def make_data_structure(data):
    all_fathers, all_relations = {}, {}
    for x in data:
        tks = x.split("-")

        if tks[1].lower() == "father":
            all_fathers[tks[0]] = x
        else:
            if len(tks) == 2:
                tks.extend(['NA', 'NA'])

            if tks[0] in all_relations:
                all_relations[tks[0]][0].append(tks[-2])
                all_relations[tks[0]][1].append(x)
            else:
                all_relations[tks[0]] =[[tks[-2]], [x]]
            
    return all_fathers, all_relations

all_fathers, all_relations = make_data_structure(data)
all_fathers, all_relations

输出：

{'A02': 'A02-father',
  'A03': 'A03-father',
  'A04': 'A04-father',
  'A05': 'A05-father',
  'A07': 'A07-father',
  'A08': 'A08-father',
  'A09': 'A09-father',
  'A17': 'A17-father',
  'A18': 'A18-father',
  'A20': 'A20-father'},

{'A02': [['A03', 'A04'], ['A02-SA-A03-SA', 'A02-SA-A04-SA']],
 'A03': [['A02', 'A05', 'A17'],
  ['A03-SA-A02-SA', 'A03-SA-A05-SA', 'A03-SA-A17-SA']],
 'A04': [['A02', 'A09'], ['A04-SA-A02-SA', 'A04-SA-A09-SA']],
 'A05': [['A03', 'NA'], ['A05-SA-A03-SA', 'A05-NA']],
 'A09': [['A04', 'A20'], ['A09-SA-A04-SA', 'A09-SA-A20-SA']],
 'A17': [['A03', 'A18'], ['A17-SA-A03-SA', 'A17-SA-A18-SA']],
 'A18': [['A17'], ['A18-SA-A17-SA']],
 'A20': [['A09'], ['A20-SA-A09-SA']]}

如您所见all_fathers包含所有父项，最重要的是all_relations包含父子关系，可以使用father对父子关系进行索引，以便更快地查找

让我们如何对关系进行实际解析

def find(all_fathers, all_relations, from_father):    
    fathers = [from_father]
    skip = []

    while True:
        if len(fathers) == 0:
            break

        current_father = fathers[0]
        fathers = fathers[1:]  

        for i in range(len(all_relations[current_father][0])):
            if not all_relations[current_father][0][i] in skip:
                print (all_fathers[current_father], all_relations[current_father][1][i])            
                if all_relations[current_father][0][i] != 'NA':
                    fathers.append(all_relations[current_father][0][i])            
        skip.append(current_father)    


for x in father:    
    find(all_fathers, all_relations, x.split("-")[0])

输出：

A03-father A03-SA-A02-SA
A03-father A03-SA-A05-SA
A03-father A03-SA-A17-SA
A02-father A02-SA-A04-SA
A05-father A05-NA
A17-father A17-SA-A18-SA
A04-father A04-SA-A09-SA
A09-father A09-SA-A20-SA

编辑2:

新的测试用例；[您必须将father.csv中的值加载到名为father的列表中]

data = [
'A02-father',
'A03-father',
'A04-father',
'A05-father',
'A07-father',
'A08-father',
'A09-father',
'A17-father',
'A18-father',
'A20-father',
'A02-SA-A03-SA',
'A02-SA-A04-SA',
'A03-SA-A02-SA',
'A03-SA-A05-SA',
'A03-SA-A17-SA',
'A04-SA-A02-SA',
'A04-SA-A09-SA',
'A05-SA-A03-SA',
'A09-SA-A04-SA',
'A09-SA-A20-SA',
'A17-SA-A03-SA',
'A17-SA-A18-SA',
'A18-SA-A17-SA',
'A20-SA-A09-SA',
'A05-NA',
'B02-Father',
'B04-Father',
'B06-Father',
'B02-SA-B04-SA',
'B04-SA-B02-SA',
'B04-SA-B06-SA',
'B06-SA-B04-SA',
'B06-NA',
]

father = [
  'A03-father',
   'B02-father'
]

for x in father:
    find(all_fathers, all_relations, x.split("-")[0])

输出：

A03-father A03-SA-A02-SA
A03-father A03-SA-A05-SA
A03-father A03-SA-A17-SA
A02-father A02-SA-A04-SA
A05-father A05-NA
A17-father A17-SA-A18-SA
A04-father A04-SA-A09-SA
A09-father A09-SA-A20-SA
B02-Father B02-SA-B04-SA
B04-Father B04-SA-B06-SA
B06-Father B06-NA

Answer 2

从import collections开始（很快就会需要）

我假设您已经阅读了df和Fa数据帧

我的代码的第一部分是创建子系列（index-parent， 值（子项）：

isFather = df.Name.str.contains('-father', case=False)
dfChildren = df[~isFather]
key = []; val = []
for fath in df[isFather].Name:
    prefix = fath.split('-')[0]
    for child in dfChildren[dfChildren.Name.str.startswith(prefix)].Name:
        key.append(prefix)
        val.append(child)
children = pd.Series(val, index=key)

打印子对象以查看结果

第二部分是创建实际结果，从每个 Fa中的起点：

nodes = collections.deque()
father = []; baby = []  # Containers for source data
# Loop for each starting point
for startNode in Fa.Name.str.split('-', expand=True)[0]:
    nodes.append(startNode)
    while nodes:
        node = nodes.popleft()  # Take node name from the queue
        # Children of this node
        myChildren = children[children.index == node]
        # Process children (ind - father, val - child)
        for ind, val in myChildren.items():
            parts = val.split('-')  # Parts of child name
            # Child "actual" name (if exists)
            val_2 = parts[2] if len(parts) >= 3 else ''
            if val_2 not in father:  # val_2 not "visited" before
                # Add father / child name to containers
                father.append(ind)
                baby.append(val)
                if len(val_2) > 0:
                    nodes.append(val_2)  # Add to the queue, to be processe later
        # Drop rows for "node" from "children" (if any exists)
        if (children.index == node).sum() > 0:
            children.drop(node, inplace=True)
# Convert to a DataFrame
result = pd.DataFrame({'Father': father, 'Baby': baby})
result.Father += '-father'    # Add "-father" to "bare" names

我用小写字母“f”添加了-father，但我认为这并不多 重要的细节

对于您的数据样本，结果是：

        Father           Baby
0   A03-father  A03-SA-A02-SA
1   A03-father  A03-SA-A05-SA
2   A03-father  A03-SA-A17-SA
3   A02-father  A02-SA-A04-SA
4   A05-father         A05-NA
5   A17-father  A17-SA-A18-SA
6   A04-father  A04-SA-A09-SA
7   A09-father  A09-SA-A20-SA
8   B02-father  B02-SA-B04-SA
9   B04-father  B04-SA-B06-SA
10  B06-father         B06-NA

关于您的数据样本，请注意以下两点：

• 你写了B04-SA-B02-SA，大写字母O（一封信），而不是0 （零）。我在源数据中更正了它
• 预期结果中的行A02-father A02-SA-A04-SA加倍。 我想这应该只发生一次