Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to reindex with MultiIndex?

I've got a DataFrame like this:

import pandas as pd
df = pd.DataFrame.from_dict({'var1': {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 0.0,
  4: 0.0,
  6: 0.0,
  7: 0.0,
  8: 0.0,
  10: 0.0},
 'var2': {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 0.0,
  4: 0.0,
  6: 0.0,
  7: 0.0,
  8: 0.0,
  10: 0.0},
 'var3': {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 0.0,
  4: 0.0,
  6: 0.0,
  7: 0.0,
  8: 0.0,
  10: 0.0},
 'var4': {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 0.0,
  4: 0.0,
  6: 0.0,
  7: 0.0,
  8: 0.0,
  10: 0.0}})

And I'd like to fill the missing indices, so I used .reindex first:

df.reindex(np.arange(1, 11))

And I got:

    var1    var2    var3    var4
1   0.0     0.0     0.0     0.0
2   0.0     0.0     0.0     0.0
3   0.0     0.0     0.0     0.0
4   0.0     0.0     0.0     0.0
5   NaN     NaN     NaN     NaN
6   0.0     0.0     0.0     0.0
7   0.0     0.0     0.0     0.0
8   0.0     0.0     0.0     0.0
9   NaN     NaN     NaN     NaN
10  0.0     0.0     0.0     0.0

However, I need to keep track of multiple indices and when I tried to construct MultiIndex and pass it to .reindex it didn't work as I was expecting it to:

    df.reindex(pd.MultiIndex.from_product([["A"], np.arange(1, 11)]))

        var1    var2    var3    var4
A   1   NaN     NaN     NaN     NaN
    2   NaN     NaN     NaN     NaN
    3   NaN     NaN     NaN     NaN
    4   NaN     NaN     NaN     NaN
    5   NaN     NaN     NaN     NaN
    6   NaN     NaN     NaN     NaN
    7   NaN     NaN     NaN     NaN
    8   NaN     NaN     NaN     NaN
    9   NaN     NaN     NaN     NaN
   10   NaN     NaN     NaN     NaN

I can't really understand what's going on here and the documentation of .reindex is not quite clear to me. Can someone advise me on this and tell why MultiIndex can't be passed to .reindex or what am I doing wrong?

@Edit:

@jazrael provided a good solution when we move from 1-level to 2-level MultiIndex. However, what about a case when we want to reindex from 2-level MultiIndex to 3-level MultiIndex?

E.g.:

df.index = pd.MultiIndex.from_arrays([np.repeat([1, 2], [4, 5]), df.index])

        var1    var2    var3    var4
1   0   0.0     0.0     0.0     0.0
    1   0.0     0.0     0.0     0.0
    2   0.0     0.0     0.0     0.0
    3   0.0     0.0     0.0     0.0
2   4   0.0     0.0     0.0     0.0
    6   0.0     0.0     0.0     0.0
    7   0.0     0.0     0.0     0.0
    8   0.0     0.0     0.0     0.0
   10   0.0     0.0     0.0     0.0

And I'd like to get:

            var1    var2    var3    var4
A   1   0   0.0     0.0     0.0     0.0
        1   0.0     0.0     0.0     0.0
        2   0.0     0.0     0.0     0.0
        3   0.0     0.0     0.0     0.0
    2   4   0.0     0.0     0.0     0.0
        5   NaN     NaN     NaN     NaN
        6   0.0     0.0     0.0     0.0
        7   0.0     0.0     0.0     0.0
        8   0.0     0.0     0.0     0.0
        9   NaN     NaN     NaN     NaN
       10   0.0     0.0     0.0     0.0
like image 344
Xaume Avatar asked Oct 17 '20 07:10

Xaume


People also ask

How do I reindex a data frame?

One can reindex a single column or multiple columns by using reindex() method and by specifying the axis we want to reindex. Default values in the new index that are not present in the dataframe are assigned NaN.

How do I convert MultiIndex to single index in pandas?

To revert the index of the dataframe from multi-index to a single index using the Pandas inbuilt function reset_index(). Returns: (Data Frame or None) DataFrame with the new index or None if inplace=True.


Video Answer


1 Answers

Because want use reindex for simple, not MultiIndex index is necessary set level=1 for match second level of new MultiIndex:

df = df.reindex(pd.MultiIndex.from_product([["A"], np.arange(1, 11)]), level=1)
print (df)
      var1  var2  var3  var4
A 1    0.0   0.0   0.0   0.0
  2    0.0   0.0   0.0   0.0
  3    0.0   0.0   0.0   0.0
  4    0.0   0.0   0.0   0.0
  5    NaN   NaN   NaN   NaN
  6    0.0   0.0   0.0   0.0
  7    0.0   0.0   0.0   0.0
  8    0.0   0.0   0.0   0.0
  9    NaN   NaN   NaN   NaN
  10   0.0   0.0   0.0   0.0
  
like image 52
jezrael Avatar answered Oct 24 '22 12:10

jezrael