copying a single-index DataFrame into a MultiIndex DataFrame

Question

Edit: found my answer here: Building a hierarchically indexed DataFrame from existing DataFrames

Turns out I need to create a matching MultiIndex with the higher levels fixed

Original:

I confess, I don't understand the merges and joins yet, but I'm not sure they're what I want.

I have a DataFrame that has a single index, and a DataFrame that has a MultiIndex, the last level of which is the same as the single-index DataFrame.

I am trying to copy/graft the contents in:

In [1]: import pandas as pd

In [2]: import numpy as np

In [3]: import itertools

In [4]: 

In [4]: inner = ('a','b')

In [5]: outer = ((10,20), (1,2))

In [6]: cols = ('one','two','three','four')

In [7]: 

In [7]: sngl = pd.DataFrame(np.random.randn(2,4), index=inner, columns=cols)

In [8]: 

In [8]: index_tups = list(itertools.product(*(outer + (inner,))))

In [9]: index_mult = pd.MultiIndex.from_tuples(index_tups)

In [10]: mult = pd.DataFrame(index=index_mult, columns=cols)

In [11]: 

In [11]: sngl
Out[11]: 
        one       two     three      four
a  2.946876 -0.751171  2.306766  0.323146
b  0.192558  0.928031  1.230475 -0.256739

In [12]: mult
Out[12]: 
        one  two three four
10 1 a  NaN  NaN   NaN  NaN
     b  NaN  NaN   NaN  NaN
   2 a  NaN  NaN   NaN  NaN
     b  NaN  NaN   NaN  NaN
20 1 a  NaN  NaN   NaN  NaN
     b  NaN  NaN   NaN  NaN
   2 a  NaN  NaN   NaN  NaN
     b  NaN  NaN   NaN  NaN

In [13]: 

In [13]: mult.ix[(10,1)] = sngl

In [14]: 

In [14]: mult
Out[14]: 
        one  two three four
10 1 a  NaN  NaN   NaN  NaN
     b  NaN  NaN   NaN  NaN
   2 a  NaN  NaN   NaN  NaN
     b  NaN  NaN   NaN  NaN
20 1 a  NaN  NaN   NaN  NaN
     b  NaN  NaN   NaN  NaN
   2 a  NaN  NaN   NaN  NaN
     b  NaN  NaN   NaN  NaN

In [15]:

What am I doing wrong?

Edit: it works when I do index by index, but that's not the pandas way, surely:

In [15]: mult.ix[(10,1,'a')] = sngl.ix['a']

In [16]: mult
Out[16]: 
             one        two     three       four
10 1 a  2.946876 -0.7511706  2.306766  0.3231457
     b       NaN        NaN       NaN        NaN
   2 a       NaN        NaN       NaN        NaN
     b       NaN        NaN       NaN        NaN
20 1 a       NaN        NaN       NaN        NaN
     b       NaN        NaN       NaN        NaN
   2 a       NaN        NaN       NaN        NaN
     b       NaN        NaN       NaN        NaN

in general you DO want to build these (rather than replace), much more efficient, but in case you actually want to do what you are doing (and there are valid use cases), e.g. say you just want to replace a part of the frame... — Jeff
– Jeff, Commented Jul 3, 2013 at 15:42
In this case, I have a method that returns to me a dataframe, and I call this multiple times, merging the results back into a larger dataframe I can then analyse. I know the overall size, so I create that first, and then copy in. — Tony
– Tony, Commented Jul 3, 2013 at 23:25
prob better off using concat then: pandas.pydata.org/pandas-docs/dev/merging.html; building then modifying is not very efficient — Jeff
– Jeff, Commented Jul 3, 2013 at 23:37

Jeff · Accepted Answer · 2013-07-03 15:36:06Z

.ix and .loc are equivalent in this example (just more explicit)

In [48]: nm = mult.reset_index().set_index('level_2')

In [49]: nm
Out[49]: 
         level_0  level_1  one  two three four
level_2                                       
a             10        1  NaN  NaN   NaN  NaN
b             10        1  NaN  NaN   NaN  NaN
a             10        2  NaN  NaN   NaN  NaN
b             10        2  NaN  NaN   NaN  NaN
a             20        1  NaN  NaN   NaN  NaN
b             20        1  NaN  NaN   NaN  NaN
a             20        2  NaN  NaN   NaN  NaN
b             20        2  NaN  NaN   NaN  NaN

This should probably work with a series on the rhs; this might be a buglet

In [50]: nm.loc['a',sngl.columns] = sngl.loc['a'].values

In [51]: nm
Out[51]: 
         level_0  level_1        one        two     three        four
level_2                                                              
a             10        1  0.3738456 -0.2261926 -1.205177  0.08448757
b             10        1        NaN        NaN       NaN         NaN
a             10        2  0.3738456 -0.2261926 -1.205177  0.08448757
b             10        2        NaN        NaN       NaN         NaN
a             20        1  0.3738456 -0.2261926 -1.205177  0.08448757
b             20        1        NaN        NaN       NaN         NaN
a             20        2  0.3738456 -0.2261926 -1.205177  0.08448757
b             20        2        NaN        NaN       NaN         NaN

In [52]: nm.reset_index().set_index(['level_0','level_1','level_2'])
Out[52]: 
                               one        two     three        four
level_0 level_1 level_2                                            
10      1       a        0.3738456 -0.2261926 -1.205177  0.08448757
                b              NaN        NaN       NaN         NaN
        2       a        0.3738456 -0.2261926 -1.205177  0.08448757
                b              NaN        NaN       NaN         NaN
20      1       a        0.3738456 -0.2261926 -1.205177  0.08448757
                b              NaN        NaN       NaN         NaN
        2       a        0.3738456 -0.2261926 -1.205177  0.08448757
                b              NaN        NaN       NaN         NaN

How to join all rows of sng1 to nm. nm.loc[:,sngl.columns] = sngl.loc[:].values doesn't work.

Collectives™ on Stack Overflow

copying a single-index DataFrame into a MultiIndex DataFrame

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related