create a new column depend on matching string in columns in two different dataframes

Question

I have tow data frames A and B, and I want to match between names columns in tow data frames if the name is existing in data set B I need to create a new column in data set A with the Id of data set B if not existing return 0

here is the code I wrote

#data B
    email              name        id
    [email protected]       amal call     6
    [email protected]      amal        6
    [email protected]        AMAL boy    6
    [email protected]          boy         7
    [email protected]      boy         7
    [email protected]     call AMAL       9
    [email protected]      boy         7
    [email protected]          dog         8
    [email protected]      dog         8
    [email protected]        dog         8



#data A

    id  name
    1   amal
    1   AMAL
    2   call
    4   dog
    3   boy

first I create contains function

A.name.str.contains('|'.join(B.name))

then I tried to create a column

A["new"] = np.where(A.name.str.contains('|'.join(B.name))==True, B.id, 0)

but I get this error

ValueError: operands could not be broadcast together with shapes (5,) (10,) ()

what I expected is

    id  name  new
    1   amal  6
    1   AMAL  0
    2   call  0
    4   dog   7
    3   boy   8

any help?

pd.merge(A,B, on='name', how='left') did a job, but is there any way to pass a list with words in contains function if i want to match words not an exact match — Fatima
– Fatima, Commented Jan 21, 2020 at 7:29

jezrael · Accepted Answer · 2020-01-21 08:06:57Z

1

Use Series.map by Series with removed duplicated rows by DataFrame.drop_duplicates, then replace missing values by Series.fillna and convert to integers:

A["new"] = A.name.map(B.drop_duplicates('name').set_index('name')['id']).fillna(0).astype(int)
print (A)
   id  name  new
0   1  amal    6
1   1  AMAL    0
2   2  call    0
3   4   dog    8
4   3   boy    7

edited Jan 21, 2020 at 8:06

answered Jan 21, 2020 at 7:43

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

create a new column depend on matching string in columns in two different dataframes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related