pandas RESHAPING AND PIVOT TABLES

In [1]: dfOut[1]:  date    variable value0 2000-01-03 A 0.4691121 2000-01-04 A -0.2828632 2000-01-05 A -1.5090593 2000-01-03 B -1.1356324 2000-01-04 B 1.2121125 2000-01-05 B -0.1732156 2000-01-03 C 0.1192097 2000-01-04 C -1.0442368 2000-01-05 C -0.8618499 2000-01-03 D -2.10456910 2000-01-04 D -0.49492911 2000-01-05 D 1.071804In [3]: df.pivot(index=’date’, columns=’variable’, values=’value’)Out[3]:variable   A        B         C        Ddate2000-01-03 0.469112 -1.135632 0.119209 -2.1045692000-01-04 -0.282863 1.212112 -1.044236 -0.4949292000-01-05 -1.509059 -0.173215 -0.861849 1.071804

Reshaping by stacking and unstacking

In [12]: df2Out[12]:             A        Bfirst secondbar   one    0.721555 -0.706771      two    -1.039575 0.271860baz   one    -0.424972 0.567020      two    0.276232 -1.087401In [13]: stacked = df2.stack()In [14]: stackedOut[14]:first secondbar   one    A 0.721555             B -0.706771      two    A -1.039575             B 0.271860baz one      A -0.424972             B 0.567020    two      A 0.276232             B -1.087401dtype: float64In [15]: stacked.unstack()Out[15]:             A        Bfirst secondbar   one    0.721555 -0.706771      two    -1.039575 0.271860baz   one    -0.424972 0.567020      two    0.276232 -1.087401In [16]: stacked.unstack(1)Out[16]:second one      twofirstbar  A 0.721555 -1.039575     B -0.706771 0.271860baz  A -0.424972 0.276232     B 0.567020 -1.087401     In [17]: stacked.unstack(0)Out[17]:first    bar      bazsecondone    A 0.721555 -0.424972       B -0.706771 0.567020two    A -1.039575 0.276232       B 0.271860 -1.087401

In [23]: columns = MultiIndex.from_tuples([....: (’A’, ’cat’, ’long’), (’B’, ’cat’, ’long’),....: (’A’, ’dog’, ’short’), (’B’, ’dog’, ’short’)....: ],....: names=[’exp’, ’animal’, ’hair_length’]....: )In [24]: df = DataFrame(randn(4, 4), columns=columns)In [25]: dfOut[25]:exp        A      B         A        Banimal     cat    cat       dog      doghair_length long  long      short    short0        1.075770 -0.109050 1.643563 -1.4693881        0.357021 -0.674600 -1.776904 -0.9689142        -1.294524 0.413738 0.276662 -0.4720353        -0.013960 -0.362543 -0.006154 -0.923061In [26]: df.stack(level=[’animal’, ’hair_length’])Out[26]:exp             A     B animal hair_length0 cat   long 1.075770 -0.109050  dog   short 1.643563 -1.4693881 cat   long 0.357021 -0.674600  dog   short -1.776904 -0.9689142 cat   long -1.294524 0.413738  dog   short 0.276662 -0.4720353 cat   long -0.013960 -0.362543  dog   short -0.006154 -0.923061

In [32]: df2Out[32]:exp     A        B        Aanimal  cat      dog      cat       dogfirst secondbar one 0.895717 0.805244 -1.206412 2.565646two 1.431256 1.340309 -1.170299 -0.226169baz one 0.410835 0.813850 0.132003 -0.827317foo one -1.413681 1.607920 1.024180 0.569605two 0.875906 -2.211372 0.974466 -2.006747qux two -1.226825 0.769804 -1.281247 -0.727707In [33]: df2.stack(’exp’)Out[33]:animal cat dogfirst second expbar   one    A 0.895717 2.565646             B -1.206412 0.805244      two    A 1.431256 -0.226169             B -1.170299 1.340309baz   one    A 0.410835 -0.827317             B 0.132003 0.813850foo   one    A -1.413681 0.569605             B 1.024180 1.607920      two    A 0.875906 -2.006747             B 0.974466 -2.211372qux   two    A -1.226825 -0.727707             B -1.281247 0.769804In [34]: df2.stack(’animal’)Out[34]:exp              A        Bfirst second animalbar   one    cat 0.895717 -1.206412             dog 2.565646 0.805244      two    cat 1.431256 -1.170299             dog -0.226169 1.340309baz   one    cat 0.410835 0.132003             dog -0.827317 0.813850foo   one    cat -1.413681 1.024180             dog 0.569605 1.607920      two    cat 0.875906 0.974466             dog -2.006747 -2.211372qux   two    cat -1.226825 -1.281247             dog -0.727707 0.769804

Reshaping by Melt

In [38]: cheeseOut[38]:  first height last weight0 John   5.5   Doe  1301 Mary   6.0   Bo   150In [39]: melt(cheese, id_vars=[’first’, ’last’])Out[39]:  first last variable value0 John   Doe height   5.51 Mary   Bo  height   6.02 John   Doe weight   130.03 Mary   Bo  weight   150.0In [40]: melt(cheese, id_vars=[’first’, ’last’], var_name=’quantity’)Out[40]:  first last quantity value0 John  Doe  height   5.51 Mary  Bo   height   6.02 John  Doe  weight   130.03 Mary  Bo   weight   150.0

Combining with stats and GroupBy

In [45]: dfOut[45]:exp     A        B                  Aanimal  cat      dog      cat       dogfirst secondbar one 0.895717 0.805244 -1.206412 2.565646    two 1.431256 1.340309 -1.170299 -0.226169baz one 0.410835 0.813850 0.132003 -0.827317    two -0.076467 -1.187678 1.130127 -1.436737foo one -1.413681 1.607920 1.024180 0.569605    two 0.875906 -2.211372 0.974466 -2.006747qux one -0.410001 -0.078638 0.545952 -1.219217    two -1.226825 0.769804 -1.281247 -0.727707In [46]: df.stack().mean(1).unstack()Out[46]:animal  cat       dogfirst secondbar one -0.155347 1.685445    two 0.130479 0.557070baz one 0.271419 -0.006733    two 0.526830 -1.312207foo one -0.194750 1.088763    two 0.925186 -2.109060qux one 0.067976 -0.648927    two -1.254036 0.021048# same result, another wayIn [47]: df.groupby(level=1, axis=1).mean()Out[47]:animal  cat       dogfirst secondbar one -0.155347 1.685445    two 0.130479 0.557070baz one 0.271419 -0.006733    two 0.526830 -1.312207foo one -0.194750 1.088763    two 0.925186 -2.109060qux one 0.067976 -0.648927    two -1.254036 0.021048