CODEWITHSUNDEEP
pythonpandasdictionarymergevlookup
Oalvinegro
Oalvinegro

Reputation: 470

Vlookup in multiple columns at Python (pandas)

I saw a lot of posts at SO asking for a way to do something similar to VLOOKUP at Python and noticed that most of answers indicates to use merge or map , but I do not know how to apply these functions in this case.
I Have the followings two dataframes (REGISTER and WEEK)

REGISTER:

NAME   |PARTNER
Rafael |Roger
Sergio |Gabriel
Edson  |Ney
Alan   |Nelson
...    |...

WEEK:

MONDAY|PARTNER|TUESDAY|PARTNER|WEDNESDAY|PARTNER|...
Rafael| ??????|Sergio|??????  |Sergio   |????????
Edson | ??????|Edson |??????  |Alan     |????????
Alan  | ??????|Rafael|??????  |Edson    |????????

The "????" should be replaced to something similar to =VLOOKUP('name in the left', REGISTER, 2, FALSE)

Upvotes: 1

Views: 2082

Answers (1)

jezrael
jezrael

Reputation: 863146

Use Series.map with Series by zip of columns names filtered by indexing, so first column is mapped to second, third to fourth and similar for each columns:

s = REGISTER.set_index('NAME')['PARTNER']

for c1, c2 in zip( WEEK.columns[::2], WEEK.columns[1::2]):
    #print (c1, c2)
    WEEK[c2] = WEEK[c1].map(s)

print (WEEK)
   MONDAY PARTNER TUESDAY PARTNER.1 WEDNESDAY PARTNER.2
0  Rafael   Roger  Sergio   Gabriel    Sergio   Gabriel
1   Edson     Ney   Edson       Ney      Alan    Nelson
2    Alan  Nelson  Rafael     Roger     Edson       Ney

If value not matched, map create missing value. So if need original values for unmatched values use replace instead map.

Upvotes: 3

Related Questions