03.04 Aggregation  and groupby in pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Агрегирование в pandas\n",
    "Pandas поддерживает все возможности по агрегированию, которые есть в NumPy. Кроме простых агрегирующих функций в pandas также есть возможность группировки и трансформации данных, которая поволяет выполнять очень сложные вычисления над данными.\n",
    "\n",
    "Создадим небольшой набор данных для простых примеров с агрегирующими функциями"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.681810Z",
     "start_time": "2017-12-20T16:52:20.179810Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "np.random.seed(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.710820Z",
     "start_time": "2017-12-20T16:52:20.684311Z"
    },
    "code_folding": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>area</th>\n",
       "      <th>gdp</th>\n",
       "      <th>gini</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Afghanistan</th>\n",
       "      <td>652864</td>\n",
       "      <td>21.000</td>\n",
       "      <td>29.0</td>\n",
       "      <td>34656032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kazakhstan</th>\n",
       "      <td>2724900</td>\n",
       "      <td>156.189</td>\n",
       "      <td>26.4</td>\n",
       "      <td>17987736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kyrgyzstan</th>\n",
       "      <td>199951</td>\n",
       "      <td>7.061</td>\n",
       "      <td>27.4</td>\n",
       "      <td>6019480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tajikistan</th>\n",
       "      <td>143100</td>\n",
       "      <td>27.802</td>\n",
       "      <td>30.8</td>\n",
       "      <td>8734951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Turkmenistan</th>\n",
       "      <td>491210</td>\n",
       "      <td>42.355</td>\n",
       "      <td>40.8</td>\n",
       "      <td>5662544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uzbekistan</th>\n",
       "      <td>448978</td>\n",
       "      <td>68.324</td>\n",
       "      <td>36.7</td>\n",
       "      <td>32979000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 area      gdp  gini  population\n",
       "Afghanistan    652864   21.000  29.0    34656032\n",
       "Kazakhstan    2724900  156.189  26.4    17987736\n",
       "Kyrgyzstan     199951    7.061  27.4     6019480\n",
       "Tajikistan     143100   27.802  30.8     8734951\n",
       "Turkmenistan   491210   42.355  40.8     5662544\n",
       "Uzbekistan     448978   68.324  36.7    32979000"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries = ['Afghanistan', 'Kazakhstan', 'Kyrgyzstan', 'Tajikistan', 'Turkmenistan', 'Uzbekistan']\n",
    "area = [652864, 2724900, 199951, 143100, 491210, 448978]\n",
    "population = [34656032, 17987736, 6019480, 8734951, 5662544, 32979000]\n",
    "gdp = [21, 156.189, 7.061, 27.802, 42.355, 68.324]\n",
    "gini = [29, 26.4, 27.4, 30.8, 40.8, 36.7]\n",
    "central_asia = pd.DataFrame(data={'area': area, \n",
    "                                  'population': population,\n",
    "                                  'gdp': gdp,\n",
    "                                  'gini': gini}, \n",
    "                            index=countries)\n",
    "central_asia"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Простые агрегирующие функции такие как `min`, `max`, `mean`, `sum` и т.д. можно вызывать напрямую как методы `Series` или `DataFrame ` объектов"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.735310Z",
     "start_time": "2017-12-20T16:52:20.712814Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "min population: 5662544\n",
      "max population: 34656032\n",
      "mean population: 17673290.5\n",
      "total population: 106039743\n"
     ]
    }
   ],
   "source": [
    "print('min population:', central_asia['population'].min())\n",
    "print('max population:', central_asia['population'].max())\n",
    "print('mean population:', central_asia['population'].mean())\n",
    "print('total population:', central_asia['population'].sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Если вызвать агрегирующую функцию у объекта `DataFrame`, то соответствующая функция будет вычислена по всем колонкам `DataFrame`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.757809Z",
     "start_time": "2017-12-20T16:52:20.737310Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "area          4.661003e+06\n",
       "gdp           3.227310e+02\n",
       "gini          1.911000e+02\n",
       "population    1.060397e+08\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "central_asia.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "В pandas есть метод `describe`, который вычисляет основные агрегирующие функции по всем данным"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.790813Z",
     "start_time": "2017-12-20T16:52:20.759309Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>area</th>\n",
       "      <th>gdp</th>\n",
       "      <th>gini</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>6.00000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>6.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>7.768338e+05</td>\n",
       "      <td>53.78850</td>\n",
       "      <td>31.850000</td>\n",
       "      <td>1.767329e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>9.730173e+05</td>\n",
       "      <td>54.33546</td>\n",
       "      <td>5.698333</td>\n",
       "      <td>1.328714e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.431000e+05</td>\n",
       "      <td>7.06100</td>\n",
       "      <td>26.400000</td>\n",
       "      <td>5.662544e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2.622078e+05</td>\n",
       "      <td>22.70050</td>\n",
       "      <td>27.800000</td>\n",
       "      <td>6.698348e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>4.700940e+05</td>\n",
       "      <td>35.07850</td>\n",
       "      <td>29.900000</td>\n",
       "      <td>1.336134e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>6.124505e+05</td>\n",
       "      <td>61.83175</td>\n",
       "      <td>35.225000</td>\n",
       "      <td>2.923118e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2.724900e+06</td>\n",
       "      <td>156.18900</td>\n",
       "      <td>40.800000</td>\n",
       "      <td>3.465603e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               area        gdp       gini    population\n",
       "count  6.000000e+00    6.00000   6.000000  6.000000e+00\n",
       "mean   7.768338e+05   53.78850  31.850000  1.767329e+07\n",
       "std    9.730173e+05   54.33546   5.698333  1.328714e+07\n",
       "min    1.431000e+05    7.06100  26.400000  5.662544e+06\n",
       "25%    2.622078e+05   22.70050  27.800000  6.698348e+06\n",
       "50%    4.700940e+05   35.07850  29.900000  1.336134e+07\n",
       "75%    6.124505e+05   61.83175  35.225000  2.923118e+07\n",
       "max    2.724900e+06  156.18900  40.800000  3.465603e+07"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "central_asia.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Group by - группировка данных в pandas\n",
    "До сих пор мы видели агрегирующие функции, которые работают со всем набором данных. С помощью group by мы можем вычислять  функции над подмножеством набора данных. Процесс группировки состоит из трех шагов:\n",
    "1. Разбиение данных на группы на основе заданной логики (Splitting)\n",
    "2. Применение функции к каждой группе (Applying)\n",
    "3. Комбинирование результата применененных функций в одну общую структуру (Combining)\n",
    "\n",
    "## [Группировка](https://pandas.pydata.org/pandas-docs/stable/groupby.html)\n",
    "Для работы с группировками создадим набор данных содержищий информацию о процессе создания ПО. Мы уже видели этот набор при изучении иерархических индексов. Здесь мы не будем создавать иерархический индекс, вместо этого будем напрямую работать с данными в простом табличном виде."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.815312Z",
     "start_time": "2017-12-20T16:52:20.792811Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team</th>\n",
       "      <th>task_type</th>\n",
       "      <th>stage</th>\n",
       "      <th>task_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Development</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>QA</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Release</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Development</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>QA</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Release</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>Development</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>QA</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>Release</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Development</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>QA</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Release</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Development</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>QA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Release</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>Development</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>QA</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>Release</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Development</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Bug</td>\n",
       "      <td>QA</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Release</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Development</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>QA</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Release</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Feature</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Feature</td>\n",
       "      <td>Development</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Feature</td>\n",
       "      <td>QA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Feature</td>\n",
       "      <td>Release</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        team    task_type        stage  task_count\n",
       "0    Backend          Bug      Backlog           4\n",
       "1    Backend          Bug  Development           0\n",
       "2    Backend          Bug           QA           3\n",
       "3    Backend          Bug      Release           3\n",
       "4    Backend  Improvement      Backlog           3\n",
       "5    Backend  Improvement  Development           1\n",
       "6    Backend  Improvement           QA           3\n",
       "7    Backend  Improvement      Release           2\n",
       "8    Backend      Feature      Backlog           4\n",
       "9    Backend      Feature  Development           0\n",
       "10   Backend      Feature           QA           0\n",
       "11   Backend      Feature      Release           4\n",
       "12  Frontend          Bug      Backlog           2\n",
       "13  Frontend          Bug  Development           1\n",
       "14  Frontend          Bug           QA           0\n",
       "15  Frontend          Bug      Release           1\n",
       "16  Frontend  Improvement      Backlog           1\n",
       "17  Frontend  Improvement  Development           0\n",
       "18  Frontend  Improvement           QA           1\n",
       "19  Frontend  Improvement      Release           4\n",
       "20  Frontend      Feature      Backlog           3\n",
       "21  Frontend      Feature  Development           0\n",
       "22  Frontend      Feature           QA           3\n",
       "23  Frontend      Feature      Release           0\n",
       "24    Mobile          Bug      Backlog           2\n",
       "25    Mobile          Bug  Development           3\n",
       "26    Mobile          Bug           QA           0\n",
       "27    Mobile          Bug      Release           1\n",
       "28    Mobile  Improvement      Backlog           3\n",
       "29    Mobile  Improvement  Development           3\n",
       "30    Mobile  Improvement           QA           3\n",
       "31    Mobile  Improvement      Release           0\n",
       "32    Mobile      Feature      Backlog           1\n",
       "33    Mobile      Feature  Development           1\n",
       "34    Mobile      Feature           QA           1\n",
       "35    Mobile      Feature      Release           0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from itertools import product\n",
    "teams = ['Backend', 'Frontend', 'Mobile']\n",
    "task_types = ['Bug', 'Improvement', 'Feature']\n",
    "stages = ['Backlog', 'Development', 'QA', 'Release']\n",
    "\n",
    "process_board_df = pd.DataFrame(data=list(product(teams, task_types, stages)), \n",
    "                                columns=['team', 'task_type', 'stage'])\n",
    "process_board_df['task_count'] = np.random.randint(0, 5, size=36)\n",
    "process_board_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Для создания группировки используется метод `groupby`, куда нужно передать как минимум название одной колонки для группировки"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.829310Z",
     "start_time": "2017-12-20T16:52:20.816809Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.DataFrameGroupBy object at 0x000001D2902C4198>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df.groupby('team')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "В результате мы получаем объект `DataFrameGroupBy`, к которому можно применять различные функции. Чаще всего группировка и применение функции выполняется как цепочка вызовов. Например, мы можем посчитать общее количество задач в каждой из комманд"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.846810Z",
     "start_time": "2017-12-20T16:52:20.831310Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>task_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Backend</th>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Frontend</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          task_count\n",
       "team                \n",
       "Backend           27\n",
       "Frontend          16\n",
       "Mobile            18"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df.groupby('team').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Мы можем передать более, чем одну колонку для группировки. Например, можно вычислить количество текущих задач на каждом этопе в каждой из комманд следующим образом"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.866810Z",
     "start_time": "2017-12-20T16:52:20.848811Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>task_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th>stage</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Backend</th>\n",
       "      <th>Backlog</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Development</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QA</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Release</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Frontend</th>\n",
       "      <th>Backlog</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Development</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QA</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Release</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Mobile</th>\n",
       "      <th>Backlog</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Development</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QA</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Release</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      task_count\n",
       "team     stage                  \n",
       "Backend  Backlog              11\n",
       "         Development           1\n",
       "         QA                    6\n",
       "         Release               9\n",
       "Frontend Backlog               6\n",
       "         Development           1\n",
       "         QA                    4\n",
       "         Release               5\n",
       "Mobile   Backlog               6\n",
       "         Development           7\n",
       "         QA                    4\n",
       "         Release               1"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df.groupby(by=['team', 'stage']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Group by - очень гибкая конструкция. Мы можем группировать не только по названию колонки, но и с помощью другой последовательности такого же размера. При этом элементы последовательности с одинаковым значением попадут в одну группу. \n",
    "\n",
    "Рассмотрим следующий пример. Часто, команды, которые используют гибкие методологии внедрения (например, Kanban) определяют для себя лимиты по максимальному количеству текущих задач, которые можно выполнять (WIP limit, work in progress limit). Это делается для того, чтобы быстрее завершать начатые задачи, не тратя времени и сил на постоянное переключение фокуса из одной задачи в другую. Допустим, что в рассматриваемой нами команде этот лимит на всех этапах равен трем. В таком случае мы можем определить количество этапов, где этот порог превышен следующим образом. Сначала создадим булевую маску"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.880808Z",
     "start_time": "2017-12-20T16:52:20.868313Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      True\n",
       "1     False\n",
       "2     False\n",
       "3     False\n",
       "4     False\n",
       "5     False\n",
       "6     False\n",
       "7     False\n",
       "8      True\n",
       "9     False\n",
       "10    False\n",
       "11     True\n",
       "12    False\n",
       "13    False\n",
       "14    False\n",
       "15    False\n",
       "16    False\n",
       "17    False\n",
       "18    False\n",
       "19     True\n",
       "20    False\n",
       "21    False\n",
       "22    False\n",
       "23    False\n",
       "24    False\n",
       "25    False\n",
       "26    False\n",
       "27    False\n",
       "28    False\n",
       "29    False\n",
       "30    False\n",
       "31    False\n",
       "32    False\n",
       "33    False\n",
       "34    False\n",
       "35    False\n",
       "Name: task_count, dtype: bool"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wip_limit_exceeded = process_board_df['task_count'] > 3\n",
    "wip_limit_exceeded"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Затем используем эту маску в качестве ключа группировки. При этом этапы, где порог превышен и не превышен, попадут в разные группы. Такое возможно благодаря выравниванию индексов"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.897309Z",
     "start_time": "2017-12-20T16:52:20.882812Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "task_count\n",
       "False    32\n",
       "True      4\n",
       "Name: task_count, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df.groupby(wip_limit_exceeded).count()['task_count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Мы можем комбинировать ключи и получить количество задач с превышенным порогом в разрезе каждой комманды"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.915311Z",
     "start_time": "2017-12-20T16:52:20.898810Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "team      task_count\n",
       "Backend   False          9\n",
       "          True           3\n",
       "Frontend  False         11\n",
       "          True           1\n",
       "Mobile    False         12\n",
       "Name: task_count, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df.groupby(by=['team', wip_limit_exceeded]).count()['task_count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "В качестве ключа для группировки можно использовать функцию. Эта функция будет получать индекс каждой строки и должна вернуть значение, которое будет использовано, чтобы отнести текущую строку к данной группе. Например, мы можем определить превышение порога задач для всех этапов кроме Backlog, так как в Backlog добавляются задачи, которые будут выполнены в будущем"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.937309Z",
     "start_time": "2017-12-20T16:52:20.916811Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    34\n",
       "True      2\n",
       "Name: task_count, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def wip_limit(idx):\n",
    "    row = process_board_df.iloc[idx]\n",
    "    return (row['stage'] != 'Backlog') and (row['task_count'] > 3)\n",
    "\n",
    "process_board_df.groupby(wip_limit).count()['task_count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "С помощью атрибута `groups` можно получить доступ к каждой из групп"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.951812Z",
     "start_time": "2017-12-20T16:52:20.939310Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Backlog': Int64Index([0, 4, 8, 12, 16, 20, 24, 28, 32], dtype='int64'),\n",
       " 'Development': Int64Index([1, 5, 9, 13, 17, 21, 25, 29, 33], dtype='int64'),\n",
       " 'QA': Int64Index([2, 6, 10, 14, 18, 22, 26, 30, 34], dtype='int64'),\n",
       " 'Release': Int64Index([3, 7, 11, 15, 19, 23, 27, 31, 35], dtype='int64')}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr = process_board_df.groupby(by='stage')\n",
    "gr.groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "С помощью метода `get_group` можно получить отдельную группу по названию группы"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.975312Z",
     "start_time": "2017-12-20T16:52:20.953311Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team</th>\n",
       "      <th>task_type</th>\n",
       "      <th>stage</th>\n",
       "      <th>task_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>QA</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>QA</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>QA</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>QA</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>QA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>QA</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Bug</td>\n",
       "      <td>QA</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>QA</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Feature</td>\n",
       "      <td>QA</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        team    task_type stage  task_count\n",
       "2    Backend          Bug    QA           3\n",
       "6    Backend  Improvement    QA           3\n",
       "10   Backend      Feature    QA           0\n",
       "14  Frontend          Bug    QA           0\n",
       "18  Frontend  Improvement    QA           1\n",
       "22  Frontend      Feature    QA           3\n",
       "26    Mobile          Bug    QA           0\n",
       "30    Mobile  Improvement    QA           3\n",
       "34    Mobile      Feature    QA           1"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gr.get_group('QA')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Группировку можно использовать для итерации по группам. Таким образом можно обработать каждую группу по отдельности. Это может быть полезно, например, для разбиения очень большого набора данных на группы и обработки каждой группы параллельно в отдельном процессе"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:20.990310Z",
     "start_time": "2017-12-20T16:52:20.976810Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Backlog (9, 4)\n",
      "Development (9, 4)\n",
      "QA (9, 4)\n",
      "Release (9, 4)\n"
     ]
    }
   ],
   "source": [
    "for name, group in gr:\n",
    "    print(name, group.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Группировка очень естественно сочетается с иерархическим индексом. Можно передать номер уровня в иерархическом индексе для группировки. Например, ниже в качестве уровней иерархического индекса используются название команд и типы задач. Мы можем сгруппировать эти данные по второму уровню, который соответствует типу задачи"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.007812Z",
     "start_time": "2017-12-20T16:52:20.992311Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>task_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>task_type</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bug</th>\n",
       "      <td>1.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Feature</th>\n",
       "      <td>1.416667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Improvement</th>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             task_count\n",
       "task_type              \n",
       "Bug            1.666667\n",
       "Feature        1.416667\n",
       "Improvement    2.000000"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "multi_index_df = process_board_df.set_index(['team', 'task_type'])\n",
    "multi_index_df.groupby(level=1).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Если у уровня индекса есть название, то можно использовать при группировке"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.023309Z",
     "start_time": "2017-12-20T16:52:21.009809Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>task_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>task_type</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bug</th>\n",
       "      <td>1.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Feature</th>\n",
       "      <td>1.416667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Improvement</th>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             task_count\n",
       "task_type              \n",
       "Bug            1.666667\n",
       "Feature        1.416667\n",
       "Improvement    2.000000"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "multi_index_df.index.names = ['team', 'task_type']\n",
    "multi_index_df.groupby(level='task_type').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## [Агрегирование по группам](https://pandas.pydata.org/pandas-docs/stable/groupby.html#aggregation)\n",
    "До сих пор мы рассматривали простые встроенные функции для агрегации по группам. Однако у группировки есть общий метод `agg`, который принимает различные аргументы и с помощью них позволяет выполнять значительно более широкий набор действий.\n",
    "\n",
    "В `agg` можно передать свою функцию для агрегации. Например, мы можем использовать метод `np.sum`, чтобы суммировать данные"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.043309Z",
     "start_time": "2017-12-20T16:52:21.024811Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>task_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th>stage</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Backend</th>\n",
       "      <th>Backlog</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Development</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QA</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Release</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Frontend</th>\n",
       "      <th>Backlog</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Development</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QA</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Release</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Mobile</th>\n",
       "      <th>Backlog</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Development</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QA</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Release</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      task_count\n",
       "team     stage                  \n",
       "Backend  Backlog              11\n",
       "         Development           1\n",
       "         QA                    6\n",
       "         Release               9\n",
       "Frontend Backlog               6\n",
       "         Development           1\n",
       "         QA                    4\n",
       "         Release               5\n",
       "Mobile   Backlog               6\n",
       "         Development           7\n",
       "         QA                    4\n",
       "         Release               1"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df.groupby(by=['team', 'stage']).agg(np.sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Можно использовать  `lambda` выражение в качестве функции агрегирования. Например, следующий пример возвращает разницу между средним количеством задач в каждой команде со средним количеством задач во всех группах"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.065809Z",
     "start_time": "2017-12-20T16:52:21.045309Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mean task_count:  1.6944444444444444\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>task_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Backend</th>\n",
       "      <td>0.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Frontend</th>\n",
       "      <td>-0.361111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile</th>\n",
       "      <td>-0.194444</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          task_count\n",
       "team                \n",
       "Backend     0.555556\n",
       "Frontend   -0.361111\n",
       "Mobile     -0.194444"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_task_count = process_board_df['task_count'].mean()\n",
    "print('mean task_count: ', mean_task_count)\n",
    "process_board_df.groupby('team').agg(lambda x: x.mean() - mean_task_count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Агрегирование по нескольким функциям\n",
    "Метод `agg` позволяет агрегировать сразу по нескольким колонкам. Для этого достаточно передать список список агрегирующих функций"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.089809Z",
     "start_time": "2017-12-20T16:52:21.067812Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">task_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Backend</th>\n",
       "      <td>27</td>\n",
       "      <td>2.250000</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Frontend</th>\n",
       "      <td>16</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile</th>\n",
       "      <td>18</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         task_count              \n",
       "                sum      mean max\n",
       "team                             \n",
       "Backend          27  2.250000   4\n",
       "Frontend         16  1.333333   4\n",
       "Mobile           18  1.500000   3"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df.groupby('team').agg(['sum', 'mean', 'max'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Можно комбинировать названия стандартных функций с другими функциями в списке для агрегирования"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.115811Z",
     "start_time": "2017-12-20T16:52:21.091812Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">task_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>&lt;lambda&gt;</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Backend</th>\n",
       "      <td>27</td>\n",
       "      <td>2.250000</td>\n",
       "      <td>0.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Frontend</th>\n",
       "      <td>16</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>-0.361111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile</th>\n",
       "      <td>18</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>-0.194444</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         task_count                    \n",
       "                sum      mean  <lambda>\n",
       "team                                   \n",
       "Backend          27  2.250000  0.555556\n",
       "Frontend         16  1.333333 -0.361111\n",
       "Mobile           18  1.500000 -0.194444"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df.groupby('team').agg([np.sum, 'mean', lambda x: x.mean() - mean_task_count])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Для разных колонок можно вычислить разный набор агрегирующих функций. Для этого в метод `agg` необходимо передать `dict` с названием колонки в качестве ключа и со списком функций, которые нудно применить к этой колонке. Для демонстрации этой возможности добавим в наш `DataFrame` дополнительную колонку со средним временем выполнения задачи на каждом этапе"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.133810Z",
     "start_time": "2017-12-20T16:52:21.117311Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team</th>\n",
       "      <th>task_type</th>\n",
       "      <th>stage</th>\n",
       "      <th>task_count</th>\n",
       "      <th>task_mean_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>4</td>\n",
       "      <td>4.043697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Development</td>\n",
       "      <td>0</td>\n",
       "      <td>3.829324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>QA</td>\n",
       "      <td>3</td>\n",
       "      <td>3.529111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Release</td>\n",
       "      <td>3</td>\n",
       "      <td>4.548467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>3</td>\n",
       "      <td>3.912301</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      team    task_type        stage  task_count  task_mean_time\n",
       "0  Backend          Bug      Backlog           4        4.043697\n",
       "1  Backend          Bug  Development           0        3.829324\n",
       "2  Backend          Bug           QA           3        3.529111\n",
       "3  Backend          Bug      Release           3        4.548467\n",
       "4  Backend  Improvement      Backlog           3        3.912301"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df['task_mean_time'] = 2 * np.random.random(process_board_df.shape[0]) + 3\n",
    "process_board_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Теперь можно вычислить общее и среднее количество задач, а также макисмальное и минимальное среднее время выполнения задач в каждой команде следующим образом"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.155809Z",
     "start_time": "2017-12-20T16:52:21.135312Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">task_count</th>\n",
       "      <th colspan=\"2\" halign=\"left\">task_mean_time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>amax</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Backend</th>\n",
       "      <td>27</td>\n",
       "      <td>2.250000</td>\n",
       "      <td>4.887496</td>\n",
       "      <td>3.037580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Frontend</th>\n",
       "      <td>16</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>4.395262</td>\n",
       "      <td>3.120451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile</th>\n",
       "      <td>18</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>4.976748</td>\n",
       "      <td>3.204090</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         task_count           task_mean_time          \n",
       "                sum      mean           amax       min\n",
       "team                                                  \n",
       "Backend          27  2.250000       4.887496  3.037580\n",
       "Frontend         16  1.333333       4.395262  3.120451\n",
       "Mobile           18  1.500000       4.976748  3.204090"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df.groupby('team').agg({'task_count': ['sum', 'mean'], 'task_mean_time': [np.max, 'min']})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Обратите внимание, что названия функций и сами функции агрегации можно смешивать в списке. Pandas при этом использует имя этой функции в качестве имени колонки. Можно задать свое название для колонки агрегации. Для этого в списке нужно передать кортежи с названием колонки в первом значении и с самой функцией во втором"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.177809Z",
     "start_time": "2017-12-20T16:52:21.157313Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">task_count</th>\n",
       "      <th colspan=\"2\" halign=\"left\">task_mean_time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>total_task_count</th>\n",
       "      <th>mean_task_count</th>\n",
       "      <th>max_mean_time</th>\n",
       "      <th>min_mean_time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Backend</th>\n",
       "      <td>27</td>\n",
       "      <td>2.250000</td>\n",
       "      <td>4.887496</td>\n",
       "      <td>3.037580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Frontend</th>\n",
       "      <td>16</td>\n",
       "      <td>1.333333</td>\n",
       "      <td>4.395262</td>\n",
       "      <td>3.120451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile</th>\n",
       "      <td>18</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>4.976748</td>\n",
       "      <td>3.204090</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               task_count                 task_mean_time              \n",
       "         total_task_count mean_task_count  max_mean_time min_mean_time\n",
       "team                                                                  \n",
       "Backend                27        2.250000       4.887496      3.037580\n",
       "Frontend               16        1.333333       4.395262      3.120451\n",
       "Mobile                 18        1.500000       4.976748      3.204090"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df.groupby('team').agg({'task_count': [('total_task_count', 'sum'), ('mean_task_count', 'mean')], \n",
    "                                      'task_mean_time': [('max_mean_time', np.max), ('min_mean_time', 'min')]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## [Трансформация](https://pandas.pydata.org/pandas-docs/stable/groupby.html#transformation)\n",
    "Метод `transform` возвращает агрегированные результаты, которые имеют такой же размер и такие же индексы, что и оригинальный набор данных. Это очень полезно, когда нужно посчитать агрегирующую функцию по колонке и присвоить результат как новую колонку в оригинальный `DataFrame`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.199809Z",
     "start_time": "2017-12-20T16:52:21.179310Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team</th>\n",
       "      <th>task_type</th>\n",
       "      <th>stage</th>\n",
       "      <th>task_count</th>\n",
       "      <th>task_mean_time</th>\n",
       "      <th>total_task_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>4</td>\n",
       "      <td>4.043697</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Development</td>\n",
       "      <td>0</td>\n",
       "      <td>3.829324</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>QA</td>\n",
       "      <td>3</td>\n",
       "      <td>3.529111</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Release</td>\n",
       "      <td>3</td>\n",
       "      <td>4.548467</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>3</td>\n",
       "      <td>3.912301</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      team    task_type        stage  task_count  task_mean_time  \\\n",
       "0  Backend          Bug      Backlog           4        4.043697   \n",
       "1  Backend          Bug  Development           0        3.829324   \n",
       "2  Backend          Bug           QA           3        3.529111   \n",
       "3  Backend          Bug      Release           3        4.548467   \n",
       "4  Backend  Improvement      Backlog           3        3.912301   \n",
       "\n",
       "   total_task_count  \n",
       "0                27  \n",
       "1                27  \n",
       "2                27  \n",
       "3                27  \n",
       "4                27  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df['total_task_count'] = process_board_df.groupby('team')['task_count'].transform('sum')\n",
    "process_board_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Часто перед тем, как передать данные в алгоритмы машинного обучения требуется отмасштабировать данные, чтобы значения всех данных попадало в один диапазон, например, в диапазон [0, 1] или [-1, 1]. Один из способов масштабирования данных называется стандартизацией, когда из оригинального значения вычитается среднее и затем делится на стандартное отклонение: \n",
    "$$x'=\\frac{x-\\bar{x}}{\\sigma}$$\n",
    "Полученные данные будут иметь среднее значение 0 и стандартное отклонение 1. Мы можем создать колонку со стандартизированным значением с помощью метода `transform` следующим образом"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.226309Z",
     "start_time": "2017-12-20T16:52:21.201310Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team</th>\n",
       "      <th>task_type</th>\n",
       "      <th>stage</th>\n",
       "      <th>task_count</th>\n",
       "      <th>task_mean_time</th>\n",
       "      <th>total_task_count</th>\n",
       "      <th>task_count_standard</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>4</td>\n",
       "      <td>4.043697</td>\n",
       "      <td>27</td>\n",
       "      <td>1.092006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Development</td>\n",
       "      <td>0</td>\n",
       "      <td>3.829324</td>\n",
       "      <td>27</td>\n",
       "      <td>-1.404008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>QA</td>\n",
       "      <td>3</td>\n",
       "      <td>3.529111</td>\n",
       "      <td>27</td>\n",
       "      <td>0.468003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Release</td>\n",
       "      <td>3</td>\n",
       "      <td>4.548467</td>\n",
       "      <td>27</td>\n",
       "      <td>0.468003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>3</td>\n",
       "      <td>3.912301</td>\n",
       "      <td>27</td>\n",
       "      <td>0.468003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      team    task_type        stage  task_count  task_mean_time  \\\n",
       "0  Backend          Bug      Backlog           4        4.043697   \n",
       "1  Backend          Bug  Development           0        3.829324   \n",
       "2  Backend          Bug           QA           3        3.529111   \n",
       "3  Backend          Bug      Release           3        4.548467   \n",
       "4  Backend  Improvement      Backlog           3        3.912301   \n",
       "\n",
       "   total_task_count  task_count_standard  \n",
       "0                27             1.092006  \n",
       "1                27            -1.404008  \n",
       "2                27             0.468003  \n",
       "3                27             0.468003  \n",
       "4                27             0.468003  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df['task_count_standard'] = process_board_df.groupby('team')['task_count'] \\\n",
    "                                                          .transform(lambda x: (x - x.mean()) / x.std())\n",
    "process_board_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Когда мы рассматривали работу с пропусками было показано, что один из методов заполнения пропущенных данных это замена их со средним значением. При этом среднее значение было вычислено по всему набору данных. Вместо этого, более корректно может быть использование среднего значения по группе. Например, рассмотрим следующий набор данных с пропусками"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.245309Z",
     "start_time": "2017-12-20T16:52:21.228313Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1\n",
       "0  NaN  1.0\n",
       "1  2.0  3.0\n",
       "2  4.0  5.0\n",
       "3  6.0  NaN\n",
       "4  8.0  9.0"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(np.arange(10).reshape((5, 2)))\n",
    "df.iloc[0, 0] = np.nan\n",
    "df.iloc[3, 1] = np.nan\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Мы сгруппируем данные по четным и нечетным индексам и заменим пропуски средними значениями в этих группах"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.268310Z",
     "start_time": "2017-12-20T16:52:21.247309Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1\n",
       "0  6.0  1.0\n",
       "1  2.0  3.0\n",
       "2  4.0  5.0\n",
       "3  6.0  3.0\n",
       "4  8.0  9.0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(by=lambda i: i % 2).transform(lambda x: x.fillna(x.mean()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## [Сводная таблица (pivot tables)](https://pandas.pydata.org/pandas-docs/stable/reshaping.html#pivot-tables)\n",
    "Сводная таблица - это таблица которая обобщает данные агрегируя их по различным осям. Сводная таблица часто используется в электронных таблицах (spreadsheets). При создании сводной таблицы колонки `DataFrame` используются в качестве осей, а в их пересечении данные агрегируются. Рассмотрим опять пример процесса создания ПО. Однако в данном случае не по всем комбинациям команд, задач и процессов будут значения"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.286310Z",
     "start_time": "2017-12-20T16:52:21.270311Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team</th>\n",
       "      <th>task_type</th>\n",
       "      <th>stages</th>\n",
       "      <th>task_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Bug</td>\n",
       "      <td>Backlog</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Backend</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Development</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>Development</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Frontend</td>\n",
       "      <td>Feature</td>\n",
       "      <td>Release</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Development</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Mobile</td>\n",
       "      <td>Improvement</td>\n",
       "      <td>Development</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       team    task_type       stages  task_count\n",
       "0   Backend          Bug      Backlog           1\n",
       "1   Backend  Improvement  Development           3\n",
       "2  Frontend      Feature  Development           0\n",
       "3  Frontend      Feature      Release           0\n",
       "4    Mobile  Improvement  Development           1\n",
       "5    Mobile  Improvement  Development           2"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "process_board_df = pd.DataFrame(data={'team': ['Backend', 'Backend', 'Frontend', 'Frontend', 'Mobile', 'Mobile'],\n",
    "                                      'task_type': ['Bug', 'Improvement', 'Feature', 'Feature', 'Improvement', 'Improvement'],\n",
    "                                      'stages': ['Backlog', 'Development', 'Development', 'Release', 'Development', 'Development']}, \n",
    "                               columns=('team', 'task_type', 'stages'))\n",
    "process_board_df['task_count'] = np.random.randint(0, 5, size=6)\n",
    "process_board_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Мы можем создать из этого набора данных сводную таблицу с помощью метода `pd.pivot_table`. В этот метод можно передать параметр `index` со списком колонок, значения которых будут использованы в качестве индексов сводной таблицы, параметр `columns` со списком колонок, которые будут использованы в качестве колонок сводной таблицы и параметр `values` со списком колонок, значения которых будут агрегированы и использованы в качестве значений в сводной таблице."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.315809Z",
     "start_time": "2017-12-20T16:52:21.287811Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stages</th>\n",
       "      <th>Backlog</th>\n",
       "      <th>Development</th>\n",
       "      <th>Release</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th>task_type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Backend</th>\n",
       "      <th>Bug</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Improvement</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Frontend</th>\n",
       "      <th>Feature</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile</th>\n",
       "      <th>Improvement</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "stages                Backlog  Development  Release\n",
       "team     task_type                                 \n",
       "Backend  Bug              1.0          NaN      NaN\n",
       "         Improvement      NaN          3.0      NaN\n",
       "Frontend Feature          NaN          0.0      0.0\n",
       "Mobile   Improvement      NaN          1.5      NaN"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(process_board_df, index=['team', 'task_type'], columns=['stages'], values='task_count')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Обратите внимание, что четвертая и пятая строки имеют одинаковые команды, типы задач и этапы. Поэтому в сводной таблице мы получили среднее значение колонки `task_count`. Арифметическое среднее используется в качестве агрегирующей функции по умолчанию. Вместо среднего значения мы можем использовать другую функцию, передав её название или ссылку на функцию в параметр `aggfunc`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.344312Z",
     "start_time": "2017-12-20T16:52:21.317310Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stages</th>\n",
       "      <th>Backlog</th>\n",
       "      <th>Development</th>\n",
       "      <th>Release</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th>task_type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Backend</th>\n",
       "      <th>Bug</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Improvement</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Frontend</th>\n",
       "      <th>Feature</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile</th>\n",
       "      <th>Improvement</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "stages                Backlog  Development  Release\n",
       "team     task_type                                 \n",
       "Backend  Bug              1.0          NaN      NaN\n",
       "         Improvement      NaN          3.0      NaN\n",
       "Frontend Feature          NaN          0.0      0.0\n",
       "Mobile   Improvement      NaN          3.0      NaN"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(process_board_df, index=['team', 'task_type'], columns=['stages'], values='task_count', aggfunc='sum')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Сводная таблица также может включать в себя итоговые колонку и строку, где будут выведеные агрегированные значения по всем строкам и колонкам. Для этого в метод `pd.pivot_table` нужно передать параметр `margins=True`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-12-20T16:52:21.389310Z",
     "start_time": "2017-12-20T16:52:21.345811Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stages</th>\n",
       "      <th>Backlog</th>\n",
       "      <th>Development</th>\n",
       "      <th>Release</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team</th>\n",
       "      <th>task_type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Backend</th>\n",
       "      <th>Bug</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Improvement</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Frontend</th>\n",
       "      <th>Feature</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mobile</th>\n",
       "      <th>Improvement</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <th></th>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "stages                Backlog  Development  Release  All\n",
       "team     task_type                                      \n",
       "Backend  Bug              1.0          NaN      NaN  1.0\n",
       "         Improvement      NaN          3.0      NaN  3.0\n",
       "Frontend Feature          NaN          0.0      0.0  0.0\n",
       "Mobile   Improvement      NaN          3.0      NaN  3.0\n",
       "All                       1.0          6.0      0.0  7.0"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(process_board_df, index=['team', 'task_type'], columns=['stages'], values='task_count', aggfunc='sum', \n",
    "               margins=True)"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "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.1"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}