How to assign values to a multi-index slice in pandas

Question

import pandas as pd
import numpy as np

df = pd.DataFrame(np.zeros([4,1]), index=pd.MultiIndex.from_product([[0,1], ['a','b']]))
df.loc[(slice(None), 'a'), 0] = pd.Series({1:2, 0:3})
df

gives

I found a couple of places that recommend adding .values to the right hand side, but that gives wrong results here (it gets the 2 and 3 switched because I intentionally put my right hand side series in a "wrong" order), because I do need pandas to align for me, but only based on the zeroth level. How can I get

Answer 1

• In order to update a dataframe, multi-index or not, from another dataframe or series, the indices of the two objects must match.
• Also see:
  • SO: Select rows in pandas MultiIndex DataFrame
  • pandas User Guide: MultiIndex / advanced indexing

Update Selected Slice of Multi-Index DataFrame

• Use multiindex slicing with pd.IndexSlice, which creates an object to more easily perform multi-index slicing.
• Caveats:
  • The multi-index dataframe and the Series used to update it, must be sorted on the index
  • The number of values in the Series and the multi-index dataframe, must be the same.

import pandas as pd

# dataframe
df = pd.DataFrame(np.zeros([4,1]), index=pd.MultiIndex.from_product([[0,1], ['a','b']]))

# series
s = pd.Series({1:2, 0:3})

# update the desired values where index level 1 is a
idx = pd.IndexSlice
df.loc[idx[:, ['a']], :] = s.sort_index().tolist()

# display(df)
       0
0 a  3.0
  b  0.0
1 a  2.0
  b  0.0

Add Index Level to Series and then Update

• The series index will never match the the multi-index dataframe, so updating in the manner suggested in the OP, is not an option, therefore you must update the series to match the multi-index.
• Use pandas.MultiIndex.from_tuples with a list-comprehension to create a new index for s

# dataframe
df = pd.DataFrame(np.zeros([4,1]), index=pd.MultiIndex.from_product([[0,1], ['a','b']]))

# series
s= pd.Series({1:2, 0:3})

# add another level to the series index and then update
s.index = pd.MultiIndex.from_tuples([(i, 'a') for i in s.index])

# update df from s
df.update(s)

# display(df)
       0
0 a  3.0
  b  0.0
1 a  2.0
  b  0.0

Update Multi-Index DataFrame from Multi-Index DataFrame

• To update the multi-index dataframe by index, the object being updated from, must be multi-index

# dataframe
df = pd.DataFrame(np.zeros([4,1]), index=pd.MultiIndex.from_product([[0,1], ['a','b']]))

# object used to update df
arrays = [[1, 0], ['a', 'a']]
idx = pd.MultiIndex.from_arrays(arrays)
s = pd.DataFrame({0: [2, 3]}, index=idx)

# display(s)
     0
1 a  2
0 a  3

# update df using s; this is an inplace update
df.update(s)

# display(df)
       0
0 a  3.0
  b  0.0
1 a  2.0
  b  0.0