CODEWITHSUNDEEP
pythonpandasdataframe
J Huang
J Huang

Reputation: 87

How to add a column to a DataFrame based on a multi-index map

I have a dataframe df as follows:

# df.head(10)
          TYPE               A                  B  
0            0               5                 25     
1            1               7                 23     
2            5              10                 43     
3            1               5                 37     
4            2               4                 61     
5            3               1                 17   
6            0               8                 39     
7            2               4                 59  
8            4               2                  6  
9            0               3                 31

And I have a multi-index map mapp as follows:

# mapp.head(10)
                  num  
AA      BB             
1        1          1 
         4          2 
         5          3 
        10          4 
        17          5 
        18          6 
2        3          7
         6          8
         9          9
3        3         10

I want to add a column df['num'] like this:

          TYPE               A                  B           num
0            0               5                 25            74
1            1               7                 23            89
2            5              10                 43           129
3            1               5                 37            77
4            2               4                 61            62
5            3               1                 17             5
6            0               8                 39            98
7            2               4                 59            61
8            4               2                  6             8
9            0               3                 31            40

I try to realize it by using the following code:

idx = df.set_index(['A', 'B']).index
df['num'] = mapp.loc[idx, 'num']

But Python throws an Exception:

Exception: cannot handle a non-unique multi-index!

How can I fix it? Or is there any other method to solve this problem? Besides, the size of df is very large, I prefer not to use the loop.

Upvotes: 2

Views: 60

Answers (1)

jezrael
jezrael

Reputation: 862481

Use DataFrame.join:

df1 = df.join(mapp, on=['A','B'])
print (df1)
   TYPE   A   B  num
0     0   5  25  NaN
1     1   7  23  NaN
2     5  10  43  NaN
3     1   5  37  NaN
4     2   4  61  NaN
5     3   1  17  5.0
6     0   8  39  NaN
7     2   4  59  NaN
8     4   2   6  8.0
9     0   3  31  NaN

Upvotes: 2

Related Questions