CODEWITHSUNDEEP
pythonpandasdataframepivot-table
Leo
Leo

Reputation: 265

pandas dataframe reshape after pivot

The pivot code:

result = pandas.pivot_table(result, values=['value'], index=['index'], columns=['columns'], fill_value=0)

The result: 

        value   value   value   
columns col1    col2    col3    
index
idx1    14      1       1
idx2    2       0       1
idx3    6       0       0

I tried:

result.columns = result.columns.get_level_values(1)

Then I got this:

columns col1    col2    col3
index
idx1    14      1       1
idx2    2       0       1
idx3    6       0       0

Actually what I would like is this one:

index   col1    col2    col3
idx1    14      1       1
idx2    2       0       1
idx3    6       0       0

Is there anyway to achieve this? Help really is appreciated. Thank you in advance.

Upvotes: 4

Views: 2488

Answers (2)

jezrael
jezrael

Reputation: 863531

You need remove index name by rename_axis (new in pandas 0.18.0):

df = df.rename_axis(None)

If need also remove columns name, use:

df = df.rename_axis(None, axis=1)

If use older version of pandas, use:

df.columns.name = None
df.index.name = None

Sample (if remove [] from pivot_table, you remove Multiindex from columns):

print (result)
   index columns  value
0      1    Toys      5
1      2    Toys      6
2      2    Cars      7
3      1    Toys      2
4      1    Cars      9

print (pd.pivot_table(result, index='index',columns='columns',values='value', fill_value=0)
         .rename_axis(None)
         .rename_axis(None, axis=1))

   Cars  Toys
1     9   3.5
2     7   6.0

If use [], get:

result = pd.pivot_table(result, values=['value'], index=['index'], columns=['columns'], fill_value=0)
            .rename_axis(None)
            .rename_axis((None,None), axis=1)
print (result)        
  value     
   Cars Toys
1     9  3.5
2     7  6.0

Upvotes: 5

piRSquared
piRSquared

Reputation: 294516

Consider this dataframe:

results = pd.DataFrame(
    [
        [14, 1, 1],
        [2, 0, 1],
        [6, 0, 0]
    ],
    pd.Index(['idx1', 'idx2', 'idx3'], name='index'),
    pd.MultiIndex.from_product([['value'], ['col1', 'col2', 'col3']], names=[None, 'columns'])
)

print results

        value          
columns  col1 col2 col3
index                  
idx1       14    1    1
idx2        2    0    1
idx3        6    0    0

Then all you need is:

print results.value.rename_axis(None, 1)  # <---- Solution

       col1  col2  col3
index                  
idx1     14     1     1
idx2      2     0     1
idx3      6     0     0

Upvotes: 1

Related Questions