CODEWITHSUNDEEP
pythonpandaspivot
8one6
8one6

Reputation: 13768

Reshaping data in Pandas (multi index in the columns, maybe in the rows too)

I have a dataframe with 6 columns. The first 5 uniquely identify an observation. The 6th is the value of that observation. I would like to pivot the data so that, of the 5 identifying columns, 3 become a hierarchical row index while the other 2 become a hierarchical column index.

Specifically, with the following setup:

import numpy as np
import pandas as pd
from itertools import product

np.random.seed(1)

team_names = ['Yankees', 'Mets', 'Dodgers']
jersey_numbers = [35, 71, 84]
game_numbers = [1, 2]
observer_names = ['Bill', 'John']
observation_types = ['Speed', 'Strength']

row_indices = list(product(team_names, jersey_numbers, game_numbers, observer_names, observation_types))
observation_values = np.random.randn(len(row_indices))

tns, jns, gns, ons, ots = zip(*row_indices)

data = pd.DataFrame({'team': tns, 'jersey': jns, 'game': gns, 'observer': ons, 'obstype': ots, 'value': observation_values})

I would like to reshape the data so that the rows are team, jersey, and game while the columns are observer and obstype. The following seems to get the job done:

pd.pivot_table(data, values='value', cols=['observer', 'obstype'], rows=['team', 'jersey', 'game'])

Are there any other ways to do this kind of thing? I had initially tried making all the columns except for value into an index and then using unstack(['observer', 'obstype']). But this gave me an unnecessary extra level in my column hierarchy: an unnamed level whose only entry was value (i.e. the name of the column whose data I actually wanted in the guts of my table).

What's the right way to handle a situation like this? Is it just to use pivot_table as I did above? Or is there a better general strategy?

Upvotes: 3

Views: 2934

Answers (1)

joris
joris

Reputation: 139162

I also think both are good and valuable options.
And in the case of unstack to get rid of the extra level, you can use droplevel:

>>> data = data.unstack(['observer', 'obstype'])
>>> data.columns = data.columns.droplevel(0)
>>> data
observer                 Bill                John          
obstype                 Speed  Strength     Speed  Strength
game jersey team                                           
1    35     Dodgers -0.110447 -0.617362  0.562761  0.240737
            Mets    -0.517094 -0.997027  0.248799 -0.296641
            Yankees  0.520576 -1.144341  0.801861  0.046567
     71     Dodgers  1.904659  1.111057  0.659050 -1.627438
            Mets     2.190700 -1.896361 -0.646917  0.901487
            Yankees  0.529465  0.137701  0.077821  0.618380
     84     Dodgers -0.400878  0.824006 -0.562305  1.954878
            Mets     1.331457 -0.287308  0.680070 -0.319802
            Yankees  1.038825  2.186980  0.441364 -0.100155
2    35     Dodgers  0.280665 -0.073113  1.160339  0.369493
            Mets     0.495211 -0.174703  0.986335  0.213534
            Yankees -0.186570 -0.101746  0.868886  0.750412
     71     Dodgers  0.602319  0.420282  0.810952  1.044442
            Mets     2.528326 -0.248635  0.043669 -0.226314
            Yankees  0.232495  0.682551 -0.310117 -2.434838
     84     Dodgers -1.331952 -1.760689 -1.650721 -0.890556
            Mets    -1.272559  0.313548  0.503185  1.293226
            Yankees -0.136445 -0.119054  0.017409 -1.122019

[18 rows x 4 columns]

Upvotes: 4

Related Questions