Insert/append a DataFrame into a MultiIndex DataFrame

Question

I would like to enlarge (=add new index to) a MultiIndex DataFrame by appending another DataFrame.

Here are the two DataFrames :

DF1 - pop :

mi = pd.MultiIndex.from_tuples([('in','a'),('in','b'),('v','t')],names=['Scope','Name'])
mc = pd.MultiIndex.from_tuples([(0,1),(0,2),(0,3)],names=['Gen','N'])
pop = pd.DataFrame([[1,2,3],[4,5,6],[7,8,9]],index=mi,columns=mc)

which gives:

Gen         0
N           1  2  3
Scope Name
in    a     1  2  3
      b     4  5  6
v     t     7  8  9

DF2 - res :

mi = pd.MultiIndex.from_tuples([('in','a'),('in','b'),('res','c'),('res','d')],names=['Scope','Name'])
res = pd.DataFrame([[1,2,3],[4,5,6],[10,20,30],[11,22,33]],index=mi,columns=[1,2,3])

which gives:

             1   2   3
Scope Name
in    a      1   2   3
      b      4   5   6
res   c     10  20  30
      d     11  22  33

I want to add the 'res' of res (sorry for bad naming...) to pop (where 'res' index still does not exist). I tried the followings with no success :

pop[0].loc['res'] = res['res']
pop.loc['res',0] = res['res']

Both leading to KeyError: 'res'. I also tested something with pd.concat or append but with poor results (and I would like to not define a new DataFrame but to enlarge the original pop). Thanks in advance for help.

WORKAROUND

I succeded in obtening the DataFrame I want, but not 'inplace' :

mi_col = pd.concat([res.loc['res']],keys=[0],axis=1) #Select 'res' index and add the '0' level to column
mi_ind = pd.concat([mi_col],keys=['res']) #Re-adding the 'res' level to index (drop during the previous selection)
pop = pd.concat([pop, mi_ind]) #Concatenating the 2 DataFrame into a new one

I am still interested in a solution that does not generate a new DataFrame.

jpp · Answer 1 · 2018-10-18T14:24:11.913

I am still interested in a solution that does not generate a new DataFrame.

It's not clear why this is deemed an advantage. In-place operations aren't intrinsically better and here it may not even be possible; see, for example, this answer. As below, you can use reindex and then assignment via loc, but reindex will create a new object.

res_res = res.loc[['res']]

# adds res index with NaN values since res values not populated
pop = pop.reindex(pop.index.union(res_res.index))  

# assign values
pop.loc['res'] = res_res

# convert to int from float due to previous NaN values
pop = pop.astype(int)

print(pop)

Gen          0        
N            1   2   3
Scope Name            
in    a      1   2   3
      b      4   5   6
res   c     10  20  30
      d     11  22  33
v     t      7   8   9

Insert/append a DataFrame into a MultiIndex DataFrame

1 Answers1