Pandas DataFrame apply or map dictionary valueassign column to function of MultiIndex value

Question

I want to map (via dictionary) part of a MultiIndex DataFrame to a column. Is there a way to do that in a single step?

For example, with the following sample DataFrame:

i = pd.MultiIndex.from_product([['A','B','C'], np.arange(1, 11, 1)], names=['Name','Num'])
df = pd.DataFrame(np.random.randn(30), i, columns=['Vals'])

and sample map:

a = list('abcdefghijk')
m = {}
for i in range(0,11):
    m[i] = a[i]

I want to create a column X containing the letter associated with the second index level:

df.assign(X=m[df.index.get_level_values('Num').values])

But that doesn't work, and neither does:

df['X'] = df.index.map(lambda x: m[x[1]])

Answer 1

Access the second level with get_level_values, convert to a Series, and call map/replace -

df['X'] = df.index.get_level_values(1).to_series().map(m).values

Or,

df['X'] = df.index.get_level_values(1).to_series().replace(m).values

Alternatively (inspired by OP), you can call map on df.index.get_level_values, and pass a callable (in this case, it would be m.get) -

df['X'] = df.index.get_level_values(1).map(m.get)

df

              Vals  X
Name Num             
A    1    2.731237  b
     2    0.180595  c
     3   -1.428064  d
     4   -0.622806  e
     5    0.948709  f
     6   -1.383310  g
     7    0.177631  h
     8   -1.071445  i
     9   -0.183859  j
     10   1.480641  k
B    1   -1.036380  b
     2    1.031757  c
     3    0.542989  d
     4   -0.933676  e
     5   -0.540661  f
     6   -0.506969  g
     7    0.572705  h
     8   -1.363675  i
     9   -0.588765  j
     10   0.998691  k
C    1   -0.471536  b
     2   -1.361124  c
     3   -0.382200  d
     4    0.694174  e
     5    1.077779  f
     6   -0.501285  g
     7    0.961986  h
     8   -0.285009  i
     9    1.385881  j
     10   1.490152  k

Here, I've got to call .values because I want to be able to assign the result back to the dataframe without indexing alignment issues.