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?
@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
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With