Reindex 3rd level of Multiindex DataFrame where second-level index is irregular

Question

I want to reindex the third level of a DataFrame, where the second level of the index is not following any pattern. I need a Multiindex object to reindex a multiindexed DataFrame, but I am having trouble building it. I read this question, but there the Multiindex follows a pattern and can be constructed from pd.Multiindex.from_product(). In my case, the second level is dependent on the first level, but does not follow a pattern. Basically, I just want to reuse the first two levels of the Multiindex, i.e. just reindex on the third level.

An example with made-up numbers (the original DataFrame is about 10,000 lines long):

df = pd.DataFrame({'Alt':[2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000,
                          4000, 4000, 4000, 4000, 4000, 4000, 4000, 4000],
                   'Mn':[0.235, 0.235, 0.554, 0.554, 0.328, 0.328, 0.764, 0.764,
                         0.245, 0.245, 0.587, 0.587, 0.376, 0.376, 0.802, 0.802],
                   'Fact':[1.2, 1.3, 1.2, 1.3, 1.2, 1.3, 1.2, 1.3,
                           1.2, 1.3, 1.2, 1.3, 1.2, 1.3, 1.2, 1.3], 
                   'Val':[10, 12, 8, 9, 6, 7, 4, 5,
                          9, 11, 7, 8, 5, 6, 3, 4]})
df = df.set_index(['Alt', 'Mn', 'Fact'])
df
                 Val
Alt  Mn    Fact     
2000 0.235 1.2    10
           1.3    12
     0.554 1.2     8
           1.3     9
     0.328 1.2     6
           1.3     7
     0.764 1.2     4
           1.3     5
4000 0.245 1.2     9
           1.3    11
     0.587 1.2     7
           1.3     8
     0.376 1.2     5
           1.3     6
     0.802 1.2     3
           1.3     4

My desired solution looks like this:

new_facts = [1.2, 1.25, 1.3]
df = df.reindex(new_facts, level='Fact')
df
                 Val
Alt  Mn    Fact     
2000 0.235 1.2    10
           1.25   NaN
           1.3    12
     0.554 1.2     8
           1.25   NaN
           1.3     9
     0.328 1.2     6
           1.25   NaN
           1.3     7
     0.764 1.2     4
           1.25   NaN
           1.3     5
4000 0.245 1.2     9
           1.25   NaN
           1.3    11
     0.587 1.2     7
           1.25   NaN
           1.3     8
     0.376 1.2     5
           1.25   NaN
           1.3     6
     0.802 1.2     3
           1.25   NaN
           1.3     4

The goal is in the end to perform interpolation on the Val column.

EDIT

As stated in an answer to this question, the reindex function should accept a level argument (like in my "desired solution" above), but for some reason, this doesn't work and the output DataFrame remains unchanged.

Blazina · Accepted Answer · 2018-02-13 13:49:56Z

1

I had a similar question last week (see Upsample in pandas multi-index):

if you use your df as described you could do something like:

def reindex_df(x, vals):
  lvl0 = x.index.get_level_values(0)[0]
  lvl1 = x.index.get_level_values(1)[0]
  lvl2 = x.index.get_level_values(2)
  lvl2 = np.unique(np.concatenate((np.array(lvl2), np.array(vals))))

  mux = pd.MultiIndex.from_product([[lvl0], [lvl1], lvl2 ], names=x.index.names)
  return (x.reindex(mux))

df.groupby(level=[0, 1], group_keys=False).apply(reindex_df, [1.2, 1.25, 1.3])

edited Feb 13, 2018 at 13:49

answered Feb 13, 2018 at 13:40

Blazina

1,32610 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Reindex 3rd level of Multiindex DataFrame where second-level index is irregular

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related