How to search for a string in pandas dataframe and match with another?-CodePudding

I'm trying to compare 2 columns (strings) of 2 different pandas' dataframe (A and B) and if they match a piece of the string, I would like to assign the value of one column in dataframe A to dataframe B.

This is my code:


import numpy as np
import pandas as pd

A = ['DF-PI-05', 'DF-PI-09', 'DF-PI-10', 'DF-PI-15', 'DF-PI-16',
       'DF-PI-19', 'DF-PI-89', 'DF-PI-92', 'DF-PI-93', 'DF-PI-94',
       'DF-PI-95', 'DF-PI-96', 'DF-PI-25', 'DF-PI-29', 'DF-PI-30',
       'DF-PI-34', 'DF-PI-84']

B = ['PI-05', 'PI-10', 'PI-89', 'PI-90', 'PI-93', 'PI-94', 'PI-95',
       'PI-96', 'PI-09', 'PI-15', 'PI-16', 'PI-19', 'PI-91A', 'PI-91b',
       'PI-92', 'PI-25-CU', 'PI-29', 'PI-30', 'PI-34', 'PI-84-CU-S1',
       'PI-84-CU-S2']

import random
sample_size = len(A)
Group = [random.randint(0,1) for _ in range(sample_size)]

A = pd.DataFrame(list(zip(A,Group)),columns=['ID','Group'])
B = pd.DataFrame(B,columns=['Name'])

clus_tx = np.array([])
for date, row in B.iterrows():    
    for date2, row2 in A.iterrows():
        if row2['ID'] in row['Name']:
            clus = row['Group']
        else:
            clus = 999
        clus_tx = np.append(clus_tx,clus)
        
B['Group'] = clus_tx

What I would like to have is a np.array clus_tx with the length of B, where if there is an element with the string that matches in A ('PI-xx'), I would take the value of the column 'Group' from A and assign to B, if there is no string matching, I would assign the value of 999 to the column 'Group' in B. I think I'm doing the loop wrong because the size of clus_tx is not what I expected...My real dataset is huge, so I can't do this manually.

CodePudding user response：

First, the reason why the size of clus_tx is not what you want is that you put clus_tx = np.append(clus_tx,clus) in the innermost loop, which has no break. So the length of clus_tx will always be len(A) x len(B).

Second, the logic of if statement block is not what you want.

I've changed the code a bit, hope it helps:

import numpy as np
import pandas as pd

A = ['DF-PI-05', 'DF-PI-09', 'DF-PI-10', 'DF-PI-15', 'DF-PI-16',
       'DF-PI-19', 'DF-PI-89', 'DF-PI-92', 'DF-PI-93', 'DF-PI-94',
       'DF-PI-95', 'DF-PI-96', 'DF-PI-25', 'DF-PI-29', 'DF-PI-30',
       'DF-PI-34', 'DF-PI-84']

B = ['PI-05', 'PI-10', 'PI-89', 'PI-90', 'PI-93', 'PI-94', 'PI-95',
       'PI-96', 'PI-09', 'PI-15', 'PI-16', 'PI-19', 'PI-91A', 'PI-91b',
       'PI-92', 'PI-25-CU', 'PI-29', 'PI-30', 'PI-34', 'PI-84-CU-S1',
       'PI-84-CU-S2']

import random
sample_size = len(A)
Group = [random.randint(0,1) for _ in range(sample_size)]

A = pd.DataFrame(list(zip(A,Group)),columns=['ID','Group'])
B = pd.DataFrame(B,columns=['Name'])

clus_tx = np.array([])
for date, row_B in B.iterrows():
    clus = 999
    for date2, row_A in A.iterrows():
        if row_B['Name'] in row_A['ID']:
            clus = row_A['Group']
            break
    clus_tx = np.append(clus_tx,clus)
        
B['Group'] = clus_tx
print(B)

The print output of B looks like:

           Name  Group
0         PI-05    0.0
1         PI-10    0.0
2         PI-89    1.0
3         PI-90  999.0
4         PI-93    0.0
5         PI-94    1.0
6         PI-95    1.0
7         PI-96    0.0
8         PI-09    1.0
9         PI-15    0.0
10        PI-16    1.0
11        PI-19    1.0
12       PI-91A  999.0
13       PI-91b  999.0
14        PI-92    1.0
15     PI-25-CU  999.0
16        PI-29    0.0
17        PI-30    1.0
18        PI-34    0.0
19  PI-84-CU-S1  999.0
20  PI-84-CU-S2  999.0