如何基于多索引映射将列添加到DataFrame [英] How to add a column to a DataFrame based on a multi-index map

问题描述

我有一个数据框df，如下所示:

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  

我有一个多索引映射mapp，如下所示:

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

我想这样添加列df['num']:

          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']

但是Python抛出异常:

But Python throws an Exception:

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

我该如何解决?还是有其他方法可以解决这个问题?此外，df的大小非常大，我不想使用循环.

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.

推荐答案

使用 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

这篇关于如何基于多索引映射将列添加到DataFrame的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！