How to add a column to a DataFrame based on a multi-index map
I have a dataframe df
like this:
# 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 card mapp
like this:
# 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 am trying to figure it out using the following code:
idx = df.set_index(['A', 'B']).index
df['num'] = mapp.loc[idx, 'num']
But Python is throwing an exception:
Exception: cannot handle a non-unique multi-index!
How can I fix this? Or is there any other method to solve this problem? Also, the size is df
very large, I prefer not to use a loop.
+2
source to share
1 answer
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
+2
source to share