Mapping index values from one dataframe to another in pandas

Question

I have a dataframe df_in like so:

import pandas as pd
import numpy as np
dic_in = {'A':['A1','A1','A1','L3','A3','A3','B1','B1','B1','B2','A2'],
       'B':['xxx','ttt','qqq','nnn','lll','nnn','eee','xxx','qqq','bbb','sss'],
       'C':['fas','efe','pfo','scs','grj','rpo','cbb','asf','asc','wq3','mls']}
df_in = pd.DataFrame(dic_in)

I also have a another dataframe which is called df_map:

dic_map = {'X':['A1' ,'A1' ,'A1' ,'A2' ,'A3' ,'B1' ,'B1' ,'B1' ,'B1' ,'B2' ,'B3' ,'B3' ,'L1', 'L3' ,'L3'],
           'Y':['qqq','ttt','xxx','sss','lll','eee','qqq','xxx','zzz','bbb','mmm','ooo','kkk','nnn','ttt']}
df_map = pd.DataFrame(dic_map)

My goal is to study every single row[['A','B']] of df_in; if the couple of items is identified within df_map, then I extract the value of the corresponding index and I set it to another column in the first dataframe.

Ex: the couple A1 - xxx is found in map in the 0; therefore I will place a 0 next to the couple A1 - xxx. If a couple is not found then I will place NaN.

The result should look like this:

    Idx   A    B    C
0     2  A1  xxx  fas
1     1  A1  ttt  efe
2     0  A1  qqq  pfo
3    13  L3  nnn  scs
4     4  A3  lll  grj
5   NaN  A3  nnn  rpo
6     5  B1  eee  cbb
7     7  B1  xxx  asf
8     6  B1  qqq  asc
9     9  B2  bbb  wq3
10    3  A2  sss  mls

Can you suggest me a smart and efficient way to reach my goal?

Community · Accepted Answer · 2017-05-23 12:08:41Z

4

You can use merge with reset_index, last remove columns by drop:

print (pd.merge(df_in, 
                df_map.reset_index(), 
                left_on=['A','B'], 
                right_on=['X','Y'], 
                how='left').drop(['X','Y'], axis=1))

     A    B    C  index
0   A1  xxx  fas    2.0
1   A1  ttt  efe    1.0
2   A1  qqq  pfo    0.0
3   L3  nnn  scs   13.0
4   A3  lll  grj    4.0
5   A3  nnn  rpo    NaN
6   B1  eee  cbb    5.0
7   B1  xxx  asf    7.0
8   B1  qqq  asc    6.0
9   B2  bbb  wq3    9.0
10  A2  sss  mls    3.0

Another solution, thank you Julien Marrec:

df_in.merge(df_map.reset_index()
                  .set_index(['X','Y']), 
            left_on=['A','B'], 
            right_index=True, 
            how='left')

Last if want change order of columns:

df = pd.merge(df_in, 
              df_map.reset_index(), 
              left_on=['A','B'], 
              right_on=['X','Y'], 
              how='left').drop(['X','Y'], axis=1)
cols = df.columns[-1:].tolist() + df.columns[:-1].tolist()
print (cols)
['index', 'A', 'B', 'C']

df = df[cols]
print (df)
    index   A    B    C
0     2.0  A1  xxx  fas
1     1.0  A1  ttt  efe
2     0.0  A1  qqq  pfo
3    13.0  L3  nnn  scs
4     4.0  A3  lll  grj
5     NaN  A3  nnn  rpo
6     5.0  B1  eee  cbb
7     7.0  B1  xxx  asf
8     6.0  B1  qqq  asc
9     9.0  B2  bbb  wq3
10    3.0  A2  sss  mls

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Dec 5, 2016 at 15:57

jezrael

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

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

6 Comments

Julien Marrec Over a year ago

You beat me to it, I came up with df_in.merge(df_map.reset_index().set_index(['X','Y']), left_on=['A','B'], right_index=True, how='left')

Federico Gentile Over a year ago

What if I want to shift the index by 1 during the operation in such a way that the mapped indexes values are all +1?

Julien Marrec Over a year ago

By shift the index, do you mean after the merge, df['index'] += 1?

jezrael Over a year ago

Or before merge? like shifted = df_map.reset_index() shifted['index'] = shifted['index'].shift() and then

df = pd.merge(df_in,                shifted,                left_on=['A','B'],                right_on=['X','Y'],                how='left').drop(['X','Y'], axis=1)

?

Federico Gentile Over a year ago

well I was thinking to do it during the merge possibly... if it is not possible then i believe doing it before is better

|

Collectives™ on Stack Overflow

Mapping index values from one dataframe to another in pandas

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related