Adding a upper level index to a MultiIndex pandas df

Question

If I have a df:

   VLA 
0   40 
1  200

and then a multi-index that looks like this:

                stuff1                         ...  stuff2                    
                   p1            p1       p2  ...       p3       p4       p4
dates                                          ...                              
2019-12-01  596.973988  1268.966237  0.969522  ...  2.344248  1.365735  2.903094
2019-12-02    1.100081     2.338402  1.249667  ...  2.815914  1.387209  2.948740

How would I create a dataframe that takes VLA as an upper index on the second dataframe so it would look like this:

VLA                             40            ...              200
                stuff1                         ...  stuff2                    
                   p1            p1       p2  ...       p3       p4       p4
dates                                          ...                              
2019-12-01       596            1268 0.969522  ...  2.344248  1.365735  2.903094
2019-12-02    1.100081     2.338402  1.249667  ...  2.815914  1.387209  2.948740

print (df.columns.levels[0])

Index(['stuff1', 'stuff2', 'stuff3', 'stuff4', 'stuff5', 'stuff6', 'stuff7'], dtype='object')

print (lvl0.map(d)

Float64Index([ 43.0,  43.0,  43.0,  43.0,  43.0,  43.0,  43.0,  43.0, 210.0,
              210.0, 210.0, 210.0, 210.0, 210.0, 210.0, 210.0,   nan,   nan,
                nan,   nan,   nan,   nan,   nan,   nan,   nan,   nan,   nan,
                nan,   nan,   nan,   nan,   nan,   nan,   nan,   nan,   nan,
                nan,   nan,   nan,   nan,   nan,   nan,   nan,   nan,   nan,
                nan,   nan,   nan,   nan,   nan,   nan,   nan,   nan,   nan,
                nan,   nan],
             dtype='float64')

@jezrael : Index(['stuff1', 'stuff2', 'stuff3', 'stuff4', 'stuff5', 'stuff6', 'stuff7'], dtype='object') - its worth noting that all these are on a VLA specific level, i.e: if there is 2 entries in VLA then there are 2 lots of stuff1-7 — Bob
– Bob, Commented Feb 16, 2020 at 12:52

jezrael · Accepted Answer · 2020-02-17 13:19:52Z

2

Use Index.get_level_values:

mux = pd.MultiIndex.from_tuples([('hh1', '43'),
('hh2', '43'),
('hh3', '43'),
('hh4', '43'),
('hh1', '210'),
('hh2', '210'),
('hh3', '210'),
('hh4', '210')],
)
final2 = pd.DataFrame(columns=mux)

mux = pd.MultiIndex.from_product([final2.columns.get_level_values(0).unique(), ['43', '210']])
print (mux)
MultiIndex([('hh1',  '43'),
            ('hh1', '210'),
            ('hh2',  '43'),
            ('hh2', '210'),
            ('hh3',  '43'),
            ('hh3', '210'),
            ('hh4',  '43'),
            ('hh4', '210')],
           )

edited Feb 17, 2020 at 13:19

answered Feb 16, 2020 at 13:07

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bob Over a year ago

Oo wow this is cool. Only issue is, 43 pulls through, but NaN appears for the second index

jezrael Over a year ago

@Bob - Do you think instead 200 ?

Collectives™ on Stack Overflow

Adding a upper level index to a MultiIndex pandas df

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related