Updating column value based on multiple conditions across multiple dataframes

Question

I have two dataframes and am trying to update Name in DF1 based on two conditions Tool and Location-

Dataframes -

first one

DF1

NAME    Tool    Location
-       tool_1  location_1
-       tool_15 location_2 
-       tool_19 location_3

and second one -

DF2

NAME    Tool    Location
name51  tool_1  location_1
name42  tool_15 location_2 
name33  tool_19 location_3

I've tried using a numpy where condition checking for both values however am getting error that states -

ValueError: Can only compare identically-labeled Series objects

I understand the problem is different row numbers in both of my dataframes. I've tried some solutions with resetting indexes without any success.

Here is my attempted query -

DF1['NAME'] = np.where((DF1.Tool == DF2.Tool) & (DF1.Location== DF2.Location), DF2.Name)

What could be a work around for this problem ? I am unable to match both dataframes with the exact length of rows.

Expected result for DF1 would be -

DF1

NAME    Tool    Location
name51  tool_1  location_1
name42  tool_15 location_2 
name33  tool_19 location_3

thank you,

anky · Accepted Answer · 2019-05-28 15:28:41Z

1

Merge and reindex():

DF1=DF1.merge(DF2,on=['Tool','Location'],suffixes=('_x','')).reindex(DF1.columns,axis=1)

     NAME     Tool    Location
0  name51   tool_1  location_1
1  name42  tool_15  location_2
2  name33  tool_19  location_3

answered May 28, 2019 at 15:28

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

3 Comments

Paul Girtavic Over a year ago

Thanks a lot for the input! How would you work around if lets say my query would look like this - DF1['NAME'] = np.where((DF1.Tool == DF2.Tool) & (DF1.Location.isin(DF2.Location)), DF2.Name) . I have more string columns that need to be matched but they are not always exactly the same and only first 2 characters match

anky Over a year ago

@PaulGirtavic the problem is this will look index wise, lets say tool15 is over tool_1, this will give false, so merge is better option here

anky Over a year ago

@PaulGirtavic you can also try

df1['NAME']=np.where(df1.Tool.eq(df2.Tool)&(df1.Location.isin(df2.Location)),df1.Tool.map(df2.set_index('Tool')['NAME']),df1.NAME)

but i will still prefer merge solution here

Collectives™ on Stack Overflow

Updating column value based on multiple conditions across multiple dataframes

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related