Search code examples
pythonpandasdataframemulti-index

While using reindex method of pandas on a data frame why the original values are lost?

This is the original Dataframetols : enter image description here What I wanted : I wanted to convert this above data-frame into this multi-indexed column data-frame : enter image description here I managed to do it by this piece of code :

# tols : original dataframe
cols =    pd.MultiIndex.from_product([['A','B'],['Y','X'] 
['P','Q']])
tols.set_axis(cols, axis = 1, inplace = False)

What I tried : I tried to do this with the reindex method like this :

cols = pd.MultiIndex.from_product([['A','B'],['Y','X'], 
['P','Q']])
tols.reindex(cols, axis = 'columns')

it resulted in an output like this : enter image description here

My problem : As you could see in the output above all my original numerical values go missing on employing the reindex method. In the documentation page it was clearly mentioned : Conform DataFrame to new index with optional filling logic, placing NA/NaN in locations having no value in the previous index. A new object is produced unless the new index is equivalent to the current one. So i don't understand:

  • Where did i particularly err in employing the reindex method to lose my original values
  • How should i have employed the reindex method correctly to get my desired output
Solution

  • You need to assign new columns names, only necessary same length of columns in original DataFrame with length of MultiIndex:

    tols.columns = pd.MultiIndex.from_product([['A','B'],['Y','X'], ['P','Q']])

    Problem with DataFrame.reindex here is pandas is looking for values of cols in original columns names and because they're not found so they're set to missing values.