CODEWITHSUNDEEP
pythonpandasmulti-index
prav
prav

Reputation: 33

How can I map to a new dataframe by Multi Index level?

I have a dataframe with columns A, B, C, D and the index is a time series.

I want to create a new dataframe with the same index, but many more columns in a multi index. A, B, C, D are the first level of the multi index. I want every column in the new dataframe to have the same value that A, B, C, D did, according to its multi index level.

In other words, if I have a data frame like this:

    A   B   C   D
0   2   3   4   5
1   X   Y   Z   1

I want to make a new dataframe that looks like this

    A          B        C   D
    0   1   2   3   4   5   6   7
0   2   2   2   3   3   4   5   5
1   X   X   X   Y   Y   Z   1   1

In other words - I want to do the equivalent of an "HLOOKUP" in excel, using the first level of the multi-index and looking up on the original dataframe.

The new multi-index is pre-determined.

Upvotes: 1

Views: 317

Answers (1)

DSM
DSM

Reputation: 353419

As suggested by cᴏʟᴅsᴘᴇᴇᴅ in the comments, you can use DataFrame.reindex with the columns and level arguments:

In [35]: mi
Out[35]: 
MultiIndex(levels=[['A', 'B', 'C', 'D'], ['0', '1', '2', '3', '4', '5', '6', '7']],
           labels=[[0, 0, 0, 1, 1, 2, 3, 3], [0, 1, 2, 3, 4, 5, 6, 7]])

In [36]: df
Out[36]: 
   A  B  C  D
0  2  3  4  5
1  X  Y  Z  1

In [37]: df.reindex(columns=mi, level=0)
Out[37]: 
   A        B     C  D   
   0  1  2  3  4  5  6  7
0  2  2  2  3  3  4  5  5
1  X  X  X  Y  Y  Z  1  1

Upvotes: 3

Related Questions