{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Reshaping in Pandas\n", "\n", "- https://pandas.pydata.org/pandas-docs/stable/reshaping.html\n", "- http://nikgrozev.com/2015/07/01/reshaping-in-pandas-pivot-pivot-table-stack-and-unstack-explained-with-pictures/\n", "- https://github.com/guipsamora/pandas_exercises" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Tidy data with Pandas\n", "\n", "#### `pivot_table` (cast): 值變成欄位名\n", "\n", "- **index (key)**: 識別 record 的最小單位的變數 (或變數的組合)\n", " - 如果是唯一的 => 直接填值\n", " - 不是唯一 => 需要 `aggfunc`\n", "- **columns (variables)**: 作為 *新變數* 的欄位\n", "- **values**: 作為 *值* 的欄位\n", "\n", "#### `melt`: 欄位名變成值\n", "\n", "- **id_vars (key)**: 識別 record 的最小單位的變數 (或變數的組合) => 不動\n", "- **value_vars (measure_var)**: 要折下來的欄位們 (default will use all non id_vars variables)\n", "- **var_name**: 舊欄位折下來後\n", "- **value_name**: 舊欄位的值疊成單一欄位\n", "- **col_level**: If columns are a MultiIndex then use this level to melt\n", "\n", "**Tools for reshaping dataframe:**\n", "\n", "| Packages | to long | to wide |\n", "|--------------|---------|--------------------------------|\n", "| **tidyr** | gather | spread |\n", "| reshape2 | melt | dcast |\n", "| **pandas** | melt | unstsack / pivot_table / pivot |\n", "| spreadsheets | unpivot | pivot |\n", "| databases | fold | unfold |" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "from collections import OrderedDict\n", "from pandas import DataFrame\n", "import pandas as pd\n", "import numpy as np\n", "from pprint import pprint" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ItemCTypeUSDEU
0Item0Gold1$1€
1Item0Bronze2$2€
2Item1Gold3$3€
3Item1Silver4$4€
\n", "
" ], "text/plain": [ " Item CType USD EU\n", "0 Item0 Gold 1$ 1€\n", "1 Item0 Bronze 2$ 2€\n", "2 Item1 Gold 3$ 3€\n", "3 Item1 Silver 4$ 4€" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "table = OrderedDict((\n", " (\"Item\", ['Item0', 'Item0', 'Item1', 'Item1']),\n", " ('CType',['Gold', 'Bronze', 'Gold', 'Silver']),\n", " ('USD', ['1$', '2$', '3$', '4$']),\n", " ('EU', ['1€', '2€', '3€', '4€'])\n", "))\n", "d = DataFrame(table)\n", "d" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Pivot\n", "\n", "Pivot takes 3 arguements with the following names: `index`, `columns`, and `values`." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "> **Issue**: `DataFrame.pivot()` not support on multiple columns to set as index\n", "> \n", "> https://github.com/pandas-dev/pandas/issues/21425\n", "\n", "```\n", "ValueError: Length of passed values is 8, index implies 2\n", "```" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
CTypeBronzeGoldSilver
Item
Item02$1$NaN
Item1NaN3$4$
\n", "
" ], "text/plain": [ "CType Bronze Gold Silver\n", "Item \n", "Item0 2$ 1$ NaN\n", "Item1 NaN 3$ 4$" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "d.pivot(index='Item', columns='CType', values='USD')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If the `values` argument is omitted, and the input DataFrame has __more than one column of values__ which are not used as column or index inputs to pivot, then the resulting “pivoted” DataFrame will have __hierarchical__ columns whose topmost level indicates the respective value column:" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
USDEU
CTypeBronzeGoldSilverBronzeGoldSilver
Item
Item02$1$NaN2€1€NaN
Item1NaN3$4$NaN3€4€
\n", "
" ], "text/plain": [ " USD EU \n", "CType Bronze Gold Silver Bronze Gold Silver\n", "Item \n", "Item0 2$ 1$ NaN 2€ 1€ NaN\n", "Item1 NaN 3$ 4$ NaN 3€ 4€" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "d.pivot(index='Item', columns='CType')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "![pivoting_simple1](http://nikgrozev.com/images/blog/Reshaping%20in%20Pandas%20-%20Pivot%20Pivot-Table%20Stack%20and%20Unstack%20explained%20with%20Pictures/pivoting_simple1.png)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Error pivot with duplicated index combination\n", "\n", "The `pivot()` method takes at least 2 column names as parameters - the `index` and the `columns`. When we have multiple rows with the __same values__ for these columns, the pivot method can not know what should be the value of the corresponding value in the pivoted table. Thus, it throws an exception\n", "\n", "Therefore, before calling pivot we need to ensure that our data does not have rows with duplicate values for the specified columns. If we can’t ensure this we may have to use the pivot_table method instead." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Item CType USD EU\n", "0 Item0 Gold 1$ 1€\n", "1 Item0 Bronze 2$ 2€\n", "2 Item0 Gold 3$ 3€\n", "3 Item1 Silver 4$ 4€\n", "\n", "Exception of type ValueError:\n", "('Index contains duplicate entries, cannot reshape',)\n" ] } ], "source": [ "columns = OrderedDict((\n", " (\"Item\", ['Item0', 'Item0', 'Item0', 'Item1']),\n", " ('CType',['Gold', 'Bronze', 'Gold', 'Silver']),\n", " ('USD', ['1$', '2$', '3$', '4$']),\n", " ('EU', ['1€', '2€', '3€', '4€'])\n", "))\n", "d2 = DataFrame(columns)\n", "print(d2)\n", "\n", "try:\n", " p = d2.pivot(index='Item', columns='CType', values='USD')\n", "except ValueError as e:\n", " print(f'\\nException of type {type(e).__name__}:\\n{e.args!r}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "![](http://nikgrozev.com/images/blog/Reshaping%20in%20Pandas%20-%20Pivot%20Pivot-Table%20Stack%20and%20Unstack%20explained%20with%20Pictures/pivoting_simple_error.png)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Pivoting By Multiple Values\n", "\n", "When omit the `values` parameter.\n", "Pandas will create a hierarchical column index (MultiIndex) for the new table." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
USDEU
CTypeBronzeGoldSilverBronzeGoldSilver
Item
Item02$1$NaN2€1€NaN
Item1NaN3$4$NaN3€4€
\n", "
" ], "text/plain": [ " USD EU \n", "CType Bronze Gold Silver Bronze Gold Silver\n", "Item \n", "Item0 2$ 1$ NaN 2€ 1€ NaN\n", "Item1 NaN 3$ 4$ NaN 3€ 4€" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "d.pivot(index='Item', columns='CType')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Stack/Unstack\n", "\n", "In fact pivoting a table is a special case of stacking a DataFrame:\n", "\n", "- **Stacking**: means moving (also pivoting) the __innermost column index__ to become the innermost row index.\n", "- **Unstacking**: means moving the __innermost row index__ to become the innermost column index." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
c0c1
c-00c-01c-10
r0r-00(0, 0)(0, 1)(0, 2)
r-01(1, 0)(1, 1)(1, 2)
\n", "
" ], "text/plain": [ " c0 c1\n", " c-00 c-01 c-10\n", "r0 r-00 (0, 0) (0, 1) (0, 2)\n", " r-01 (1, 0) (1, 1) (1, 2)" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Row Multi-Index\n", "row_idx_arr = list(zip(['r0', 'r0'], ['r-00', 'r-01']))\n", "row_idx = pd.MultiIndex.from_tuples(row_idx_arr)\n", "\n", "# Column Multi-Index\n", "col_idx_arr = list(zip(['c0', 'c0', 'c1'], ['c-00', 'c-01', 'c-10']))\n", "col_idx = pd.MultiIndex.from_tuples(col_idx_arr)\n", "\n", "# Create the DataFrame\n", "d3 = DataFrame(np.arange(6).reshape(2,3), index=row_idx, columns=col_idx).applymap(lambda x: (x // 3, x % 3))\n", "d3" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Stack\n", "\n", "means moving (also rotating or pivoting) the __innermost column index__ to become the innermost row index." ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
c0c1
r0r-00c-00(0, 0)NaN
c-01(0, 1)NaN
c-10NaN(0, 2)
r-01c-00(1, 0)NaN
c-01(1, 1)NaN
c-10NaN(1, 2)
\n", "
" ], "text/plain": [ " c0 c1\n", "r0 r-00 c-00 (0, 0) NaN\n", " c-01 (0, 1) NaN\n", " c-10 NaN (0, 2)\n", " r-01 c-00 (1, 0) NaN\n", " c-01 (1, 1) NaN\n", " c-10 NaN (1, 2)" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "d3.stack() # default (-1), stacking innermost (last) column index" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
c-00c-01c-10
r0r-00c0(0, 0)(0, 1)NaN
c1NaNNaN(0, 2)
r-01c0(1, 0)(1, 1)NaN
c1NaNNaN(1, 2)
\n", "
" ], "text/plain": [ " c-00 c-01 c-10\n", "r0 r-00 c0 (0, 0) (0, 1) NaN\n", " c1 NaN NaN (0, 2)\n", " r-01 c0 (1, 0) (1, 1) NaN\n", " c1 NaN NaN (1, 2)" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "d3.stack(level=0)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Unstack\n", "\n", "means moving the __innermost row index__ to become the innermost column index." ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
c0c1
c-00c-01c-10
r-00r-01r-00r-01r-00r-01
r0(0, 0)(1, 0)(0, 1)(1, 1)(0, 2)(1, 2)
\n", "
" ], "text/plain": [ " c0 c1 \n", " c-00 c-01 c-10 \n", " r-00 r-01 r-00 r-01 r-00 r-01\n", "r0 (0, 0) (1, 0) (0, 1) (1, 1) (0, 2) (1, 2)" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "d3.unstack() # default (-1), unstacking innermost (last) row index" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
c0c1
c-00c-01c-10
r0r0r0
r-00(0, 0)(0, 1)(0, 2)
r-01(1, 0)(1, 1)(1, 2)
\n", "
" ], "text/plain": [ " c0 c1\n", " c-00 c-01 c-10\n", " r0 r0 r0\n", "r-00 (0, 0) (0, 1) (0, 2)\n", "r-01 (1, 0) (1, 1) (1, 2)" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "d3.unstack(0)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "![](http://nikgrozev.com/images/blog/Reshaping%20in%20Pandas%20-%20Pivot%20Pivot-Table%20Stack%20and%20Unstack%20explained%20with%20Pictures/stack-unstack1.png)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Stack Multiple Levels" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
expABAB
animalcatcatdogdog
hair_lengthlonglongshortshort
01.059746-0.612812-0.6901510.317193
1-0.1809452.2091170.6342541.548406
2-0.4231730.0762070.610957-0.166565
3-0.037512-0.595232-0.9286580.267121
\n", "
" ], "text/plain": [ "exp A B A B\n", "animal cat cat dog dog\n", "hair_length long long short short\n", "0 1.059746 -0.612812 -0.690151 0.317193\n", "1 -0.180945 2.209117 0.634254 1.548406\n", "2 -0.423173 0.076207 0.610957 -0.166565\n", "3 -0.037512 -0.595232 -0.928658 0.267121" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "columns = pd.MultiIndex.from_tuples([\n", " ('A', 'cat', 'long'), ('B', 'cat', 'long'),\n", " ('A', 'dog', 'short'), ('B', 'dog', 'short')\n", " ],\n", " names=['exp', 'animal', 'hair_length']\n", ")\n", "df = pd.DataFrame(np.random.randn(4, 4), columns=columns)\n", "df" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
expAB
animalhair_length
0catlong1.059746-0.612812
dogshort-0.6901510.317193
1catlong-0.1809452.209117
dogshort0.6342541.548406
2catlong-0.4231730.076207
dogshort0.610957-0.166565
3catlong-0.037512-0.595232
dogshort-0.9286580.267121
\n", "
" ], "text/plain": [ "exp A B\n", " animal hair_length \n", "0 cat long 1.059746 -0.612812\n", " dog short -0.690151 0.317193\n", "1 cat long -0.180945 2.209117\n", " dog short 0.634254 1.548406\n", "2 cat long -0.423173 0.076207\n", " dog short 0.610957 -0.166565\n", "3 cat long -0.037512 -0.595232\n", " dog short -0.928658 0.267121" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.stack(level=['animal', 'hair_length'])" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
expAB
animalhair_length
0catlong1.059746-0.612812
dogshort-0.6901510.317193
1catlong-0.1809452.209117
dogshort0.6342541.548406
2catlong-0.4231730.076207
dogshort0.610957-0.166565
3catlong-0.037512-0.595232
dogshort-0.9286580.267121
\n", "
" ], "text/plain": [ "exp A B\n", " animal hair_length \n", "0 cat long 1.059746 -0.612812\n", " dog short -0.690151 0.317193\n", "1 cat long -0.180945 2.209117\n", " dog short 0.634254 1.548406\n", "2 cat long -0.423173 0.076207\n", " dog short 0.610957 -0.166565\n", "3 cat long -0.037512 -0.595232\n", " dog short -0.928658 0.267121" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# from above is equivalent to:\n", "df.stack(level=[1, 2])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Fill missing data\n", "\n", "- __Unstacking__ can result in __missing values__ if subgroups do not have the same set of labels.\n", "- By default, missing values will be replaced with the default fill value for that data type, `NaN` for float, `NaT` for datetimelike" ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
expB
animaldogcat
firstsecond
barone-0.0727710.197562
two-2.6720110.412956
fooone-0.7756870.917038
quxtwo-0.245372-0.940731
\n", "
" ], "text/plain": [ "exp B \n", "animal dog cat\n", "first second \n", "bar one -0.072771 0.197562\n", " two -2.672011 0.412956\n", "foo one -0.775687 0.917038\n", "qux two -0.245372 -0.940731" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "columns = pd.MultiIndex.from_tuples([('A', 'cat'), ('B', 'dog'),\n", " ('B', 'cat'), ('A', 'dog')],\n", " names=['exp', 'animal'])\n", "\n", "index = pd.MultiIndex.from_product([('bar', 'baz', 'foo', 'qux'),\n", " ('one', 'two')],\n", " names=['first', 'second']) \n", "\n", "df = pd.DataFrame(np.random.randn(8, 4), index=index, columns=columns)\n", "\n", "df3 = df.iloc[[0, 1, 4, 7], [1, 2]]\n", "df3" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
expB
animaldogcat
secondonetwoonetwo
first
bar-0.072771-2.6720110.1975620.412956
foo-0.775687NaN0.917038NaN
quxNaN-0.245372NaN-0.940731
\n", "
" ], "text/plain": [ "exp B \n", "animal dog cat \n", "second one two one two\n", "first \n", "bar -0.072771 -2.672011 0.197562 0.412956\n", "foo -0.775687 NaN 0.917038 NaN\n", "qux NaN -0.245372 NaN -0.940731" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df3.unstack()" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
expB
animaldogcat
secondonetwoonetwo
first
bar-7.277132e-02-2.672011e+001.975617e-014.129559e-01
foo-7.756872e-01-1.000000e+099.170382e-01-1.000000e+09
qux-1.000000e+09-2.453722e-01-1.000000e+09-9.407306e-01
\n", "
" ], "text/plain": [ "exp B \n", "animal dog cat \n", "second one two one two\n", "first \n", "bar -7.277132e-02 -2.672011e+00 1.975617e-01 4.129559e-01\n", "foo -7.756872e-01 -1.000000e+09 9.170382e-01 -1.000000e+09\n", "qux -1.000000e+09 -2.453722e-01 -1.000000e+09 -9.407306e-01" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df3.unstack(fill_value=-1e9)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Calculate margin aggregation\n", "\n", "`DataFrame.stack().mean(axis=1)`: agg of columns" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
expABA
animalcatdogcatdog
firstsecond
barone0.251463-0.0727710.197562-1.342170
two1.921913-2.6720110.4129560.231708
bazone-0.694452-0.1333432.1631491.480759
two0.487188-1.572656-1.9166790.347214
fooone0.618454-0.7756870.917038-1.068503
two-0.162572-2.6213062.3888930.356138
quxone-1.372046-0.9108670.0033292.403540
two-0.466882-0.245372-0.9407310.447336
\n", "
" ], "text/plain": [ "exp A B A\n", "animal cat dog cat dog\n", "first second \n", "bar one 0.251463 -0.072771 0.197562 -1.342170\n", " two 1.921913 -2.672011 0.412956 0.231708\n", "baz one -0.694452 -0.133343 2.163149 1.480759\n", " two 0.487188 -1.572656 -1.916679 0.347214\n", "foo one 0.618454 -0.775687 0.917038 -1.068503\n", " two -0.162572 -2.621306 2.388893 0.356138\n", "qux one -1.372046 -0.910867 0.003329 2.403540\n", " two -0.466882 -0.245372 -0.940731 0.447336" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
expAB
firstsecondanimal
baronecat0.2514630.197562
dog-1.342170-0.072771
twocat1.9219130.412956
dog0.231708-2.672011
bazonecat-0.6944522.163149
dog1.480759-0.133343
twocat0.487188-1.916679
dog0.347214-1.572656
fooonecat0.6184540.917038
dog-1.068503-0.775687
twocat-0.1625722.388893
dog0.356138-2.621306
quxonecat-1.3720460.003329
dog2.403540-0.910867
twocat-0.466882-0.940731
dog0.447336-0.245372
\n", "
" ], "text/plain": [ "exp A B\n", "first second animal \n", "bar one cat 0.251463 0.197562\n", " dog -1.342170 -0.072771\n", " two cat 1.921913 0.412956\n", " dog 0.231708 -2.672011\n", "baz one cat -0.694452 2.163149\n", " dog 1.480759 -0.133343\n", " two cat 0.487188 -1.916679\n", " dog 0.347214 -1.572656\n", "foo one cat 0.618454 0.917038\n", " dog -1.068503 -0.775687\n", " two cat -0.162572 2.388893\n", " dog 0.356138 -2.621306\n", "qux one cat -1.372046 0.003329\n", " dog 2.403540 -0.910867\n", " two cat -0.466882 -0.940731\n", " dog 0.447336 -0.245372" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.stack()" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "first second animal\n", "bar one cat 0.224512\n", " dog -0.707471\n", " two cat 1.167435\n", " dog -1.220152\n", "baz one cat 0.734348\n", " dog 0.673708\n", " two cat -0.714745\n", " dog -0.612721\n", "foo one cat 0.767746\n", " dog -0.922095\n", " two cat 1.113160\n", " dog -1.132584\n", "qux one cat -0.684359\n", " dog 0.746337\n", " two cat -0.703806\n", " dog 0.100982\n", "dtype: float64" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.stack().mean(1)" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
animalcatdog
firstsecond
barone0.224512-0.707471
two1.167435-1.220152
bazone0.7343480.673708
two-0.714745-0.612721
fooone0.767746-0.922095
two1.113160-1.132584
quxone-0.6843590.746337
two-0.7038060.100982
\n", "
" ], "text/plain": [ "animal cat dog\n", "first second \n", "bar one 0.224512 -0.707471\n", " two 1.167435 -1.220152\n", "baz one 0.734348 0.673708\n", " two -0.714745 -0.612721\n", "foo one 0.767746 -0.922095\n", " two 1.113160 -1.132584\n", "qux one -0.684359 0.746337\n", " two -0.703806 0.100982" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.stack().mean(1).unstack()" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
animalcatdog
firstsecond
barone0.224512-0.707471
two1.167435-1.220152
bazone0.7343480.673708
two-0.714745-0.612721
fooone0.767746-0.922095
two1.113160-1.132584
quxone-0.6843590.746337
two-0.7038060.100982
\n", "
" ], "text/plain": [ "animal cat dog\n", "first second \n", "bar one 0.224512 -0.707471\n", " two 1.167435 -1.220152\n", "baz one 0.734348 0.673708\n", " two -0.714745 -0.612721\n", "foo one 0.767746 -0.922095\n", " two 1.113160 -1.132584\n", "qux one -0.684359 0.746337\n", " two -0.703806 0.100982" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# same result, another way\n", "df.groupby(level=1, axis=1).mean()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Reshaping by Melt" ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
firstlastheightweight
0JohnDoe5.5130
1MaryBo6.0150
\n", "
" ], "text/plain": [ " first last height weight\n", "0 John Doe 5.5 130\n", "1 Mary Bo 6.0 150" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cheese = pd.DataFrame({'first' : ['John', 'Mary'],\n", " 'last' : ['Doe', 'Bo'],\n", " 'height' : [5.5, 6.0],\n", " 'weight' : [130, 150]})\n", "cheese" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
firstlastvariablevalue
0JohnDoeheight5.5
1MaryBoheight6.0
2JohnDoeweight130.0
3MaryBoweight150.0
\n", "
" ], "text/plain": [ " first last variable value\n", "0 John Doe height 5.5\n", "1 Mary Bo height 6.0\n", "2 John Doe weight 130.0\n", "3 Mary Bo weight 150.0" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cheese.melt(id_vars=['first', 'last'])" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
firstlastquantitymeasure
0JohnDoeheight5.5
1MaryBoheight6.0
2JohnDoeweight130.0
3MaryBoweight150.0
\n", "
" ], "text/plain": [ " first last quantity measure\n", "0 John Doe height 5.5\n", "1 Mary Bo height 6.0\n", "2 John Doe weight 130.0\n", "3 Mary Bo weight 150.0" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# custom variable names\n", "cheese.melt(id_vars=['first', 'last'], var_name='quantity', value_name='measure')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### `wide_to_long() `\n", "\n", "Panel data convenience function. It is less flexible than `melt()`, but more user-friendly." ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
A1970A1980B1970B1980Xid
0ad2.53.20.1806740
1be1.21.31.5113571
2cf0.70.1-1.5791522
\n", "
" ], "text/plain": [ " A1970 A1980 B1970 B1980 X id\n", "0 a d 2.5 3.2 0.180674 0\n", "1 b e 1.2 1.3 1.511357 1\n", "2 c f 0.7 0.1 -1.579152 2" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "dft = pd.DataFrame({\"A1970\" : {0 : \"a\", 1 : \"b\", 2 : \"c\"},\n", " \"A1980\" : {0 : \"d\", 1 : \"e\", 2 : \"f\"},\n", " \"B1970\" : {0 : 2.5, 1 : 1.2, 2 : .7},\n", " \"B1980\" : {0 : 3.2, 1 : 1.3, 2 : .1},\n", " \"X\" : dict(zip(range(3), np.random.randn(3)))\n", " })\n", "dft[\"id\"] = dft.index\n", "dft" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
XAB
idyear
019700.180674a2.5
119701.511357b1.2
21970-1.579152c0.7
019800.180674d3.2
119801.511357e1.3
21980-1.579152f0.1
\n", "
" ], "text/plain": [ " X A B\n", "id year \n", "0 1970 0.180674 a 2.5\n", "1 1970 1.511357 b 1.2\n", "2 1970 -1.579152 c 0.7\n", "0 1980 0.180674 d 3.2\n", "1 1980 1.511357 e 1.3\n", "2 1980 -1.579152 f 0.1" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# The wide format variables are assumed to start with the stub names.\n", "pd.wide_to_long(dft, stubnames=[\"A\", \"B\"], i=\"id\", j=\"year\", suffix=r'\\d+')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Tidy data by hand" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Xidvaluevaryear
00.1806740aA1970
11.5113571bA1970
2-1.5791522cA1970
30.1806740dA1980
41.5113571eA1980
5-1.5791522fA1980
60.18067402.5B1970
71.51135711.2B1970
8-1.57915220.7B1970
90.18067403.2B1980
101.51135711.3B1980
11-1.57915220.1B1980
\n", "
" ], "text/plain": [ " X id value var year\n", "0 0.180674 0 a A 1970\n", "1 1.511357 1 b A 1970\n", "2 -1.579152 2 c A 1970\n", "3 0.180674 0 d A 1980\n", "4 1.511357 1 e A 1980\n", "5 -1.579152 2 f A 1980\n", "6 0.180674 0 2.5 B 1970\n", "7 1.511357 1 1.2 B 1970\n", "8 -1.579152 2 0.7 B 1970\n", "9 0.180674 0 3.2 B 1980\n", "10 1.511357 1 1.3 B 1980\n", "11 -1.579152 2 0.1 B 1980" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " dft.melt(id_vars=['X', 'id'], var_name='_')\n", " .assign(\n", " var=lambda x: x._.str[0],\n", " year=lambda x: x._.str[1:])\n", " .loc[:, ['X', 'id', 'value', 'var', 'year']]\n", ")" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idyearXAB
0019700.180674a2.5
1019800.180674d3.2
2119701.511357b1.2
3119801.511357e1.3
421970-1.579152c0.7
521980-1.579152f0.1
\n", "
" ], "text/plain": [ " id year X A B\n", "0 0 1970 0.180674 a 2.5\n", "1 0 1980 0.180674 d 3.2\n", "2 1 1970 1.511357 b 1.2\n", "3 1 1980 1.511357 e 1.3\n", "4 2 1970 -1.579152 c 0.7\n", "5 2 1980 -1.579152 f 0.1" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(\n", " dft.melt(id_vars=['X', 'id'], var_name='_')\n", " .assign(\n", " var=lambda x: x._.str[0],\n", " year=lambda x: x._.str[1:])\n", " .loc[:, ['X', 'id', 'value', 'var', 'year']]\n", " .set_index(['id', 'year', 'X', 'var'])\n", " .unstack()\n", " .transpose().reset_index(level=0, drop=True).transpose() # drop column MultiIndex level\n", " .reset_index()\n", " .rename_axis(None, axis=1)\n", ")" ] }, { "cell_type": "code", "execution_count": 30, "metadata": { "scrolled": true }, "outputs": [ { "ename": "DataError", "evalue": "No numeric types to aggregate", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mDataError\u001b[0m Traceback (most recent call last)", "\u001b[0;32m\u001b[0m in \u001b[0;36m\u001b[0;34m()\u001b[0m\n\u001b[1;32m 6\u001b[0m year=lambda x: x._.str[1:])\n\u001b[1;32m 7\u001b[0m \u001b[0;34m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m'X'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'id'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'value'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'var'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'year'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 8\u001b[0;31m \u001b[0;34m.\u001b[0m\u001b[0mpivot_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'id'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'year'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcolumns\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'var'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalues\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'value'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 9\u001b[0m )\n", "\u001b[0;32m~/.pyenv/versions/anaconda3-5.2.0/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36mpivot_table\u001b[0;34m(self, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name)\u001b[0m\n\u001b[1;32m 5298\u001b[0m \u001b[0maggfunc\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0maggfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfill_value\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mfill_value\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 5299\u001b[0m \u001b[0mmargins\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmargins\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdropna\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdropna\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 5300\u001b[0;31m margins_name=margins_name)\n\u001b[0m\u001b[1;32m 5301\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 5302\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mstack\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlevel\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdropna\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m~/.pyenv/versions/anaconda3-5.2.0/lib/python3.6/site-packages/pandas/core/reshape/pivot.py\u001b[0m in \u001b[0;36mpivot_table\u001b[0;34m(data, values, index, columns, aggfunc, fill_value, margins, dropna, margins_name)\u001b[0m\n\u001b[1;32m 81\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 82\u001b[0m \u001b[0mgrouped\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgroupby\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkeys\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mobserved\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdropna\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 83\u001b[0;31m \u001b[0magged\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgrouped\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0magg\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0maggfunc\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 84\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 85\u001b[0m \u001b[0mtable\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0magged\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m~/.pyenv/versions/anaconda3-5.2.0/lib/python3.6/site-packages/pandas/core/groupby/groupby.py\u001b[0m in \u001b[0;36maggregate\u001b[0;34m(self, arg, *args, **kwargs)\u001b[0m\n\u001b[1;32m 4656\u001b[0m axis=''))\n\u001b[1;32m 4657\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0maggregate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0marg\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4658\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0msuper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mDataFrameGroupBy\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0maggregate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 4659\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 4660\u001b[0m \u001b[0magg\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0maggregate\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m~/.pyenv/versions/anaconda3-5.2.0/lib/python3.6/site-packages/pandas/core/groupby/groupby.py\u001b[0m in \u001b[0;36maggregate\u001b[0;34m(self, arg, *args, **kwargs)\u001b[0m\n\u001b[1;32m 4087\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 4088\u001b[0m \u001b[0m_level\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpop\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'_level'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4089\u001b[0;31m \u001b[0mresult\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mhow\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_aggregate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0m_level\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0m_level\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 4090\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mhow\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 4091\u001b[0m \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m~/.pyenv/versions/anaconda3-5.2.0/lib/python3.6/site-packages/pandas/core/base.py\u001b[0m in \u001b[0;36m_aggregate\u001b[0;34m(self, arg, *args, **kwargs)\u001b[0m\n\u001b[1;32m 346\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcompat\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstring_types\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 347\u001b[0m return self._try_aggregate_string_function(arg, *args,\n\u001b[0;32m--> 348\u001b[0;31m **kwargs), None\n\u001b[0m\u001b[1;32m 349\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 350\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdict\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m~/.pyenv/versions/anaconda3-5.2.0/lib/python3.6/site-packages/pandas/core/base.py\u001b[0m in \u001b[0;36m_try_aggregate_string_function\u001b[0;34m(self, arg, *args, **kwargs)\u001b[0m\n\u001b[1;32m 302\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mf\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 303\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 304\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 305\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 306\u001b[0m \u001b[0;31m# people may try to aggregate on a non-callable attribute\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m~/.pyenv/versions/anaconda3-5.2.0/lib/python3.6/site-packages/pandas/core/groupby/groupby.py\u001b[0m in \u001b[0;36mmean\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m 1304\u001b[0m \u001b[0mnv\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mvalidate_groupby_func\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'mean'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m'numeric_only'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1305\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1306\u001b[0;31m \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_cython_agg_general\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'mean'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 1307\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0mGroupByError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 1308\u001b[0m \u001b[0;32mraise\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m~/.pyenv/versions/anaconda3-5.2.0/lib/python3.6/site-packages/pandas/core/groupby/groupby.py\u001b[0m in \u001b[0;36m_cython_agg_general\u001b[0;34m(self, how, alt, numeric_only, min_count)\u001b[0m\n\u001b[1;32m 3972\u001b[0m min_count=-1):\n\u001b[1;32m 3973\u001b[0m new_items, new_blocks = self._cython_agg_blocks(\n\u001b[0;32m-> 3974\u001b[0;31m how, alt=alt, numeric_only=numeric_only, min_count=min_count)\n\u001b[0m\u001b[1;32m 3975\u001b[0m \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_wrap_agged_blocks\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnew_items\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnew_blocks\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 3976\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;32m~/.pyenv/versions/anaconda3-5.2.0/lib/python3.6/site-packages/pandas/core/groupby/groupby.py\u001b[0m in \u001b[0;36m_cython_agg_blocks\u001b[0;34m(self, how, alt, numeric_only, min_count)\u001b[0m\n\u001b[1;32m 4044\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 4045\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnew_blocks\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4046\u001b[0;31m \u001b[0;32mraise\u001b[0m \u001b[0mDataError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'No numeric types to aggregate'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 4047\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 4048\u001b[0m \u001b[0;31m# reset the locs in the blocks to correspond to our\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n", "\u001b[0;31mDataError\u001b[0m: No numeric types to aggregate" ] } ], "source": [ "# pivot_table only accepts numerial values\n", "# DataError: No numeric types to aggregate\n", "(\n", " dft.melt(id_vars=['X', 'id'], var_name='_')\n", " .assign(\n", " var=lambda x: x._.str[0],\n", " year=lambda x: x._.str[1:])\n", " .loc[:, ['X', 'id', 'value', 'var', 'year']]\n", " .pivot_table(index=['id', 'year'],\n", " columns='var', values=['value']) # string value\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Pivot tables\n", "\n", "While `pivot()` provides general purpose pivoting with various data types (strings, numerics, etc.), pandas also provides `pivot_table()` for pivoting with __aggregation__ of __numeric__ data.\n", "\n", "If the `values` column name is not given, the pivot table will include all of the data that can be aggregated in an additional level of hierarchy in the columns:" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ABCDEF
0oneAfoo0.6173400.6889552013-01-01
1oneBfoo0.1149910.6167822013-02-01
2twoCfoo-1.7324480.7783162013-03-01
3threeAbar-0.9636440.8345422013-04-01
4oneBbar0.235077-1.5135722013-05-01
\n", "
" ], "text/plain": [ " A B C D E F\n", "0 one A foo 0.617340 0.688955 2013-01-01\n", "1 one B foo 0.114991 0.616782 2013-02-01\n", "2 two C foo -1.732448 0.778316 2013-03-01\n", "3 three A bar -0.963644 0.834542 2013-04-01\n", "4 one B bar 0.235077 -1.513572 2013-05-01" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import datetime\n", "df = pd.DataFrame({'A': ['one', 'one', 'two', 'three'] * 6,\n", " 'B': ['A', 'B', 'C'] * 8,\n", " 'C': ['foo', 'foo', 'foo', 'bar', 'bar', 'bar'] * 4,\n", " 'D': np.random.randn(24),\n", " 'E': np.random.randn(24),\n", " 'F': [datetime.datetime(2013, i, 1) for i in range(1, 13)] +\n", " [datetime.datetime(2013, i, 15) for i in range(1, 13)]})\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Cbarfoo
AB
oneA0.869647-0.039520
B0.1039910.037492
C-0.647924-0.274917
threeA-0.835807NaN
BNaN-0.006188
C-0.327788NaN
twoANaN0.388725
B-0.207472NaN
CNaN-0.213953
\n", "
" ], "text/plain": [ "C bar foo\n", "A B \n", "one A 0.869647 -0.039520\n", " B 0.103991 0.037492\n", " C -0.647924 -0.274917\n", "three A -0.835807 NaN\n", " B NaN -0.006188\n", " C -0.327788 NaN\n", "two A NaN 0.388725\n", " B -0.207472 NaN\n", " C NaN -0.213953" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.pivot_table(df, values='D', index=['A', 'B'], columns=['C'])" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Aonethreetwo
Cbarfoobarfoobarfoo
B
A1.739294-0.079040-1.671613NaNNaN0.777451
B0.2079810.074984NaN-0.012375-0.414943NaN
C-1.295847-0.549834-0.655576NaNNaN-0.427907
\n", "
" ], "text/plain": [ "A one three two \n", "C bar foo bar foo bar foo\n", "B \n", "A 1.739294 -0.079040 -1.671613 NaN NaN 0.777451\n", "B 0.207981 0.074984 NaN -0.012375 -0.414943 NaN\n", "C -1.295847 -0.549834 -0.655576 NaN NaN -0.427907" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.pivot_table(df, values='D', index=['B'], columns=['A', 'C'], aggfunc=np.sum)" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DE
Aonethreetwoonethreetwo
Cbarfoobarfoobarfoobarfoobarfoobarfoo
B
A1.739294-0.079040-1.671613NaNNaN0.777451-1.447236-1.468236-0.607316NaNNaN0.406707
B0.2079810.074984NaN-0.012375-0.414943NaN-1.2330760.126317NaN-0.239886-1.678184NaN
C-1.295847-0.549834-0.655576NaNNaN-0.427907-0.4950082.150398-2.353556NaNNaN3.712925
\n", "
" ], "text/plain": [ " D E \\\n", "A one three two one \n", "C bar foo bar foo bar foo bar \n", "B \n", "A 1.739294 -0.079040 -1.671613 NaN NaN 0.777451 -1.447236 \n", "B 0.207981 0.074984 NaN -0.012375 -0.414943 NaN -1.233076 \n", "C -1.295847 -0.549834 -0.655576 NaN NaN -0.427907 -0.495008 \n", "\n", " \n", "A three two \n", "C foo bar foo bar foo \n", "B \n", "A -1.468236 -0.607316 NaN NaN 0.406707 \n", "B 0.126317 NaN -0.239886 -1.678184 NaN \n", "C 2.150398 -2.353556 NaN NaN 3.712925 " ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.pivot_table(df, values=['D','E'], index=['B'], columns=['A', 'C'], aggfunc=np.sum)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Add margins" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
DE
CbarfooAllbarfooAll
AB
oneA0.8765250.9289410.9051351.8928832.0125291.595137
B0.1853830.1096010.1301301.2685970.7829420.945925
C0.5272061.0829050.7279571.2412800.5834911.100118
threeA0.180789NaN0.1807891.609658NaN1.609658
BNaN1.1581561.158156NaN1.4704181.470418
C0.648783NaN0.6487830.266018NaN0.266018
twoANaN1.0142021.014202NaN1.5247671.524767
B0.454298NaN0.4542980.105855NaN0.105855
CNaN2.1474762.147476NaN1.5247301.524730
All0.7037740.9330420.7950590.9820931.3613591.250150
\n", "
" ], "text/plain": [ " D E \n", "C bar foo All bar foo All\n", "A B \n", "one A 0.876525 0.928941 0.905135 1.892883 2.012529 1.595137\n", " B 0.185383 0.109601 0.130130 1.268597 0.782942 0.945925\n", " C 0.527206 1.082905 0.727957 1.241280 0.583491 1.100118\n", "three A 0.180789 NaN 0.180789 1.609658 NaN 1.609658\n", " B NaN 1.158156 1.158156 NaN 1.470418 1.470418\n", " C 0.648783 NaN 0.648783 0.266018 NaN 0.266018\n", "two A NaN 1.014202 1.014202 NaN 1.524767 1.524767\n", " B 0.454298 NaN 0.454298 0.105855 NaN 0.105855\n", " C NaN 2.147476 2.147476 NaN 1.524730 1.524730\n", "All 0.703774 0.933042 0.795059 0.982093 1.361359 1.250150" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.pivot_table(index=['A', 'B'], columns='C', margins=True, aggfunc=np.std)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.5" } }, "nbformat": 4, "nbformat_minor": 2 }