Adding new multiindex level

Question

I have a DataFrame:

d = pd.DataFrame({'i1': ['A', 'B', 'C', 'D', 'E'],
                  'i2': ['I', 'II', 'III', 'IV', 'V'],
                  'val': ["lol1", "lol2", "lol3", "lol4", "lol5"]}).set_index(["i1", "i2"])
         val
i1 i2       
A  I    lol1
B  II   lol2
C  III  lol3
D  IV   lol4
E  V    lol5

I need to add new index level i3 from values [5, 10, 15], using pandas API, so it looks like:

            val
i1 i2  i3      
A  I   5   lol1
       10  lol1
       15  lol1
B  II  5   lol2
       10  lol2
       15  lol2
C  III 5   lol3
       10  lol3
       15  lol3
D  IV  5   lol4
       10  lol4
       15  lol4
E  V   5   lol5
       10  lol5
       15  lol5

My tries(ugly):

d = np.repeat(d.reset_index().values, 3, 0)

i3 = [5, 10, 15]
r2 = np.tile(i3, 5)

r = np.concatenate([d, r2.reshape(-1, 1)], 1)

d = pd.DataFrame(r, columns=["i1", "i2", "val", "i3"])
d = d.set_index(["i1", "i2", "i3"])

Also, I've been looking towards pd.MultiIndex.from_product, but it's going to make combinations from i1 and i2 no matter what I do.

jezrael · Accepted Answer · 2019-05-21 11:08:03Z

4

Create list of tuples by values from MultiIndex, then use DataFrame.reindex with MultiIndex.from_tuples:

vals = [5, 10, 15]
tups = [x + (i,) for x in d.index for i in vals]
d = d.reindex(pd.MultiIndex.from_tuples(tups, names=['i1','i2','i3']))
print (d)
            val
i1 i2  i3      
A  I   5   lol1
       10  lol1
       15  lol1
B  II  5   lol2
       10  lol2
       15  lol2
C  III 5   lol3
       10  lol3
       15  lol3
D  IV  5   lol4
       10  lol4
       15  lol4
E  V   5   lol5
       10  lol5
       15  lol5

answered May 21, 2019 at 11:08

jezrael

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

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

2 Comments

MoorzTech Over a year ago

Whilst this works, first manually creating all of the tuple combinations seems rather cumbersome. Shouldn't there be a more efficient approach that doesn't require iterative creation of tuples?

jezrael Over a year ago

@MoorzTech - Another idea should be use cross join, but is seems more crazy for me ;)

d.reset_index().assign(tmp=1).merge(pd.DataFrame({'i3':vals, 'tmp':1}), on='tmp').drop('tmp', 1).set_index(['i1','i2','i3'])

mcsoini · Accepted Answer · 2022-04-15 05:46:58Z

0

As an alternative to reindexing we can also create a new list column and explode.

d_new = (d.assign(i3=[[5, 10, 15]] * len(d))
          .explode("i3")
          .set_index("i3", append=True))

print(df_new)

# result:
            val
i1 i2  i3      
A  I   5   lol1
       10  lol1
       15  lol1
B  II  5   lol2
       10  lol2
       15  lol2
C  III 5   lol3
       10  lol3
       15  lol3
D  IV  5   lol4
       10  lol4
       15  lol4
E  V   5   lol5
       10  lol5
       15  lol5

answered Apr 15, 2022 at 5:46

mcsoini

6,7922 gold badges21 silver badges43 bronze badges

Collectives™ on Stack Overflow

Adding new multiindex level

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related