Vlookup in python with multiple conditions

Question

I have two dataframes loaded up from csv files in Python.

One of it contains the following type of data:

 Well   Zones   Inflow   
 E18    A       0.45
 E23    B       0.33
 E25    C       0.2
 E18    B       0.2
 E23    A       0.67
 E25    D       0.12
 E23    B       0.2
 E18    A       0.67
 E25    D       0.12

and the other:

 Well   Zones   Distance
 E18    A       5.3
 E23    B       2.1
 E25    C       4.8
 E18    B       5.4
 E23    A       2.3
 E25    D       5.5

What I need to do is to match two dataframes, based on two conditions, "Well" and "Zones", to get the first dataframe updated as following:

 Well   Zones   Inflow   Distance
 E18    A       0.45       5.3
 E23    B       0.33       2.1
 E25    C       0.2        4.8
 E18    B       0.2        5.4
 E23    A       0.67       2.3
 E25    D       0.12       5.5

It may seem trivial from above example, because I have included it here only for the description of my problem. The actual data is a really big file with the data in original file shuffled.

This is what I have used to match based only on one criterion and it works perfectly:

   df1.insert(13, 'Distance', df1['Well'].map(df2.set_index('Well')['Distance']))

***13 is the column to insert the matched data from

However, what I need is the same (or any other) way to match two dataframes, but with 2 or more conditions (because depending on zone and well the distance differs), so, not only "Well" but "Well" + "Zones" conditions.

Jondiedoop · Accepted Answer · 2019-01-19 15:45:14Z

3

Merge the dataframes:

df1.merge(df2, how='left')

The merge automatically finds the columns in both dataframes and matches where they are both equal. The how=left keyword specifies you want to keep the all the rows from the first dataframe and find matching rows from the second dataframe. See the documentation for more details.

If there are more columns that have the same name, but you only want to match on those two, use

df1.merge(df2, how='left', on=['Well', 'Zones'])

Output:

#  Well Zones  Inflow  Distance
#0  E18     A    0.45       5.3
#1  E23     B    0.33       2.1
#2  E25     C    0.20       4.8
#3  E18     B    0.20       5.4
#4  E23     A    0.67       2.3
#5  E25     D    0.12       5.5
#6  E23     B    0.20       2.1
#7  E18     A    0.67       5.3
#8  E25     D    0.12       5.5

edited Jan 19, 2019 at 15:45

answered Jan 19, 2019 at 15:42

Jondiedoop

3,38311 silver badges24 bronze badges

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

8 Comments

akkab Over a year ago

sorry for not mentioning it in the question, but the original file is much bigger, with additional rows (see the update)

akkab Over a year ago

I am new to the python, could you please elaborate

Jondiedoop Over a year ago

No worries, we'll figure it out. Just to check: does using the edited code (with how='left') give the desired result?

akkab Over a year ago

Nope, it is not proceeding further

akkab Over a year ago

actually, it proceeds, but without any changes to the original file

|

Collectives™ on Stack Overflow

Vlookup in python with multiple conditions

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related