groupby, split-apply-combine.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GroupBy: split-apply-combine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\"group by\"操作通常涉及下面几个步骤：\n",
    "\n",
    "* Splitting 分组，将数据按照某种指标分为多组\n",
    "* Applying 对每一组数据都应用一个函数，进行转换\n",
    "* Combining 将各个组的转换结果合并"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "其中applying通常是下面三个类型的函数：\n",
    "* 集成函数：计算每组数据的统计指标，比如sum、means、size、count\n",
    "* 转换函数：对每组数据进行标准化，填充NAN值\n",
    "* 过滤函数："
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "groupby和SQL中的GROUP BY很像："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "SELECT Column1, Column2, mean(Column3), sum(Column4)\n",
    "FROM SomeTable\n",
    "GROUP BY Column1, Column2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Split：对数据分组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 默认按照axis=0分组\n",
    "grouped = obj.groupby(key)\n",
    "grouped = obj.groupby(key, axis=1)\n",
    "grouped = obj.groupby([key1, key2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'A': ['foo', 'bar', 'foo', 'bar', 'foo', 'bar', 'foo', 'foo'],\n",
    "                   'B': ['one', 'one', 'two', 'three', 'two', 'two', 'one', 'three'],\n",
    "                   'C': np.random.randn(8),\n",
    "                   'D': np.random.randn(8)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>foo</td>\n",
       "      <td>one</td>\n",
       "      <td>-0.925271</td>\n",
       "      <td>0.961582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "      <td>one</td>\n",
       "      <td>-0.365797</td>\n",
       "      <td>-0.636498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>foo</td>\n",
       "      <td>two</td>\n",
       "      <td>-1.919952</td>\n",
       "      <td>0.501423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bar</td>\n",
       "      <td>three</td>\n",
       "      <td>-0.462709</td>\n",
       "      <td>0.864412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>foo</td>\n",
       "      <td>two</td>\n",
       "      <td>-0.558642</td>\n",
       "      <td>0.964123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>bar</td>\n",
       "      <td>two</td>\n",
       "      <td>-1.277091</td>\n",
       "      <td>-0.130611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>foo</td>\n",
       "      <td>one</td>\n",
       "      <td>-1.040990</td>\n",
       "      <td>0.181257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>foo</td>\n",
       "      <td>three</td>\n",
       "      <td>-0.616584</td>\n",
       "      <td>-0.132354</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A      B         C         D\n",
       "0  foo    one -0.925271  0.961582\n",
       "1  bar    one -0.365797 -0.636498\n",
       "2  foo    two -1.919952  0.501423\n",
       "3  bar  three -0.462709  0.864412\n",
       "4  foo    two -0.558642  0.964123\n",
       "5  bar    two -1.277091 -0.130611\n",
       "6  foo    one -1.040990  0.181257\n",
       "7  foo  three -0.616584 -0.132354"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 默认按行分割"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "grouped = df.groupby('A')\n",
    "grouped = df.groupby(['A', 'B'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 按列分割\n",
    "\n",
    "   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "把DataFrame按照列分组，比如第一列为一组，第二、三列为另一组。相比于按行分组，按列分组用处不大。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_letter_type(letter):\n",
    "    if letter.lower() in 'aeiou':\n",
    "        return 'vowel'\n",
    "    else:\n",
    "        return 'consonant'\n",
    "grouped = df.groupby(get_letter_type, axis=1)\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.DataFrameGroupBy object at 0x1139deb10>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GroupBy 排序"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "groupby的结果默认会对group key排序，可以通过参数sort=False来组织排序，提高性能:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame({'X': ['B', 'B', 'A', 'A'], 'Y': [1, 2, 3, 4]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>X</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Y\n",
       "X   \n",
       "A  7\n",
       "B  3"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.groupby('X').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>X</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Y\n",
       "X   \n",
       "B  3\n",
       "A  7"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.groupby('X', sort=False).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "分组后，各个组内数据的顺序和原来DataFrame内数据顺序一致。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df3 = pd.DataFrame({'X': ['A', 'B', 'A', 'B'], 'Y': [1, 4, 3, 2]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   X  Y\n",
       "0  A  1\n",
       "2  A  3"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.groupby('X').get_group('A')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   X  Y\n",
       "1  B  4\n",
       "3  B  2"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.groupby('X').get_group('B')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GroupBy 对象属性"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "groups属性是一个字典，每个key就是group key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'bar': Int64Index([1, 3, 5], dtype='int64'),\n",
       " 'foo': Int64Index([0, 2, 4, 6, 7], dtype='int64')}"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('A').groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'consonant': Index([u'B', u'C', u'D'], dtype='object'),\n",
       " 'vowel': Index([u'A'], dtype='object')}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(get_letter_type, axis=1).groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以对grouped调用len()函数，会得到gruops这个字典的大小"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "grouped = df.groupby(['A', 'B'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{('bar', 'one'): Int64Index([1], dtype='int64'),\n",
       " ('bar', 'three'): Int64Index([3], dtype='int64'),\n",
       " ('bar', 'two'): Int64Index([5], dtype='int64'),\n",
       " ('foo', 'one'): Int64Index([0, 6], dtype='int64'),\n",
       " ('foo', 'three'): Int64Index([7], dtype='int64'),\n",
       " ('foo', 'two'): Int64Index([2, 4], dtype='int64')}"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(grouped)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "想查看groupby的属性，按TAB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gb.<TAB>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 迭代访问各组数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "grouped = df.groupby('A')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "bar\n",
      "     A      B         C         D\n",
      "1  bar    one -0.365797 -0.636498\n",
      "3  bar  three -0.462709  0.864412\n",
      "5  bar    two -1.277091 -0.130611\n",
      "foo\n",
      "     A      B         C         D\n",
      "0  foo    one -0.925271  0.961582\n",
      "2  foo    two -1.919952  0.501423\n",
      "4  foo    two -0.558642  0.964123\n",
      "6  foo    one -1.040990  0.181257\n",
      "7  foo  three -0.616584 -0.132354\n"
     ]
    }
   ],
   "source": [
    "for name, group in grouped:\n",
    "    print(name)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果根据多个key进行groupby，则name类型是tuple:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('bar', 'one')\n",
      "     A    B         C         D\n",
      "1  bar  one -0.365797 -0.636498\n",
      "('bar', 'three')\n",
      "     A      B         C         D\n",
      "3  bar  three -0.462709  0.864412\n",
      "('bar', 'two')\n",
      "     A    B         C         D\n",
      "5  bar  two -1.277091 -0.130611\n",
      "('foo', 'one')\n",
      "     A    B         C         D\n",
      "0  foo  one -0.925271  0.961582\n",
      "6  foo  one -1.040990  0.181257\n",
      "('foo', 'three')\n",
      "     A      B         C         D\n",
      "7  foo  three -0.616584 -0.132354\n",
      "('foo', 'two')\n",
      "     A    B         C         D\n",
      "2  foo  two -1.919952  0.501423\n",
      "4  foo  two -0.558642  0.964123\n"
     ]
    }
   ],
   "source": [
    "for name, group in df.groupby(['A', 'B']):\n",
    "    print(name)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 选择某个组的数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用get_group()函数:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "      <td>one</td>\n",
       "      <td>-0.365797</td>\n",
       "      <td>-0.636498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bar</td>\n",
       "      <td>three</td>\n",
       "      <td>-0.462709</td>\n",
       "      <td>0.864412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>bar</td>\n",
       "      <td>two</td>\n",
       "      <td>-1.277091</td>\n",
       "      <td>-0.130611</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A      B         C         D\n",
       "1  bar    one -0.365797 -0.636498\n",
       "3  bar  three -0.462709  0.864412\n",
       "5  bar    two -1.277091 -0.130611"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.get_group('bar')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "      <td>one</td>\n",
       "      <td>-0.365797</td>\n",
       "      <td>-0.636498</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B         C         D\n",
       "1  bar  one -0.365797 -0.636498"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['A', 'B']).get_group(('bar', 'one'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据集成 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "一旦数据分组完成，就可以对各个组的数据应用某个函数了，典型的例子是调用aggregate/agg:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "grouped = df.groupby('A')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bar</th>\n",
       "      <td>-2.105597</td>\n",
       "      <td>0.097302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>-5.061439</td>\n",
       "      <td>2.476032</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            C         D\n",
       "A                      \n",
       "bar -2.105597  0.097302\n",
       "foo -5.061439  2.476032"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.aggregate(np.sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "bar\n",
      "     A      B         C         D\n",
      "1  bar    one -0.365797 -0.636498\n",
      "3  bar  three -0.462709  0.864412\n",
      "5  bar    two -1.277091 -0.130611\n",
      "foo\n",
      "     A      B         C         D\n",
      "0  foo    one -0.925271  0.961582\n",
      "2  foo    two -1.919952  0.501423\n",
      "4  foo    two -0.558642  0.964123\n",
      "6  foo    one -1.040990  0.181257\n",
      "7  foo  three -0.616584 -0.132354\n"
     ]
    }
   ],
   "source": [
    "for name, group in grouped:\n",
    "    print(name)\n",
    "    print(group)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看到，集成后的数据包含组名，并且**组名作为行索引**。如果groupby分组时有多个group key，集成后数据默认含有层级索引，可以通过as_index=False参数来去掉索引:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "grouped = df.groupby(['A', 'B'], as_index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bar</td>\n",
       "      <td>one</td>\n",
       "      <td>-0.365797</td>\n",
       "      <td>-0.636498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "      <td>three</td>\n",
       "      <td>-0.462709</td>\n",
       "      <td>0.864412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bar</td>\n",
       "      <td>two</td>\n",
       "      <td>-1.277091</td>\n",
       "      <td>-0.130611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>foo</td>\n",
       "      <td>one</td>\n",
       "      <td>-1.966261</td>\n",
       "      <td>1.142840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>foo</td>\n",
       "      <td>three</td>\n",
       "      <td>-0.616584</td>\n",
       "      <td>-0.132354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>foo</td>\n",
       "      <td>two</td>\n",
       "      <td>-2.478594</td>\n",
       "      <td>1.465546</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A      B         C         D\n",
       "0  bar    one -0.365797 -0.636498\n",
       "1  bar  three -0.462709  0.864412\n",
       "2  bar    two -1.277091 -0.130611\n",
       "3  foo    one -1.966261  1.142840\n",
       "4  foo  three -0.616584 -0.132354\n",
       "5  foo    two -2.478594  1.465546"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.aggregate(np.sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bar</td>\n",
       "      <td>-2.105597</td>\n",
       "      <td>0.097302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>foo</td>\n",
       "      <td>-5.061439</td>\n",
       "      <td>2.476032</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A         C         D\n",
       "0  bar -2.105597  0.097302\n",
       "1  foo -5.061439  2.476032"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('A', as_index=False).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "除了使用as_index参数，也可以使用reset_index()去掉组名构成的多级索引:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bar</td>\n",
       "      <td>one</td>\n",
       "      <td>-0.365797</td>\n",
       "      <td>-0.636498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "      <td>three</td>\n",
       "      <td>-0.462709</td>\n",
       "      <td>0.864412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bar</td>\n",
       "      <td>two</td>\n",
       "      <td>-1.277091</td>\n",
       "      <td>-0.130611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>foo</td>\n",
       "      <td>one</td>\n",
       "      <td>-1.966261</td>\n",
       "      <td>1.142840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>foo</td>\n",
       "      <td>three</td>\n",
       "      <td>-0.616584</td>\n",
       "      <td>-0.132354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>foo</td>\n",
       "      <td>two</td>\n",
       "      <td>-2.478594</td>\n",
       "      <td>1.465546</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A      B         C         D\n",
       "0  bar    one -0.365797 -0.636498\n",
       "1  bar  three -0.462709  0.864412\n",
       "2  bar    two -1.277091 -0.130611\n",
       "3  foo    one -1.966261  1.142840\n",
       "4  foo  three -0.616584 -0.132354\n",
       "5  foo    two -2.478594  1.465546"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['A', 'B']).sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A    B    \n",
       "bar  one      1\n",
       "     three    1\n",
       "     two      1\n",
       "foo  one      2\n",
       "     three    1\n",
       "     two      2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.size() # 返回各个组的size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一次应用多个函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "grouped = df.groupby('A')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bar</th>\n",
       "      <td>-2.105597</td>\n",
       "      <td>-0.701866</td>\n",
       "      <td>0.500511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>-5.061439</td>\n",
       "      <td>-1.012288</td>\n",
       "      <td>0.546495</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          sum      mean       std\n",
       "A                                \n",
       "bar -2.105597 -0.701866  0.500511\n",
       "foo -5.061439 -1.012288  0.546495"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped['C'].agg([np.sum, np.mean, np.std])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">C</th>\n",
       "      <th colspan=\"3\" halign=\"left\">D</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bar</th>\n",
       "      <td>-2.105597</td>\n",
       "      <td>-0.701866</td>\n",
       "      <td>0.500511</td>\n",
       "      <td>0.097302</td>\n",
       "      <td>0.032434</td>\n",
       "      <td>0.763623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>-5.061439</td>\n",
       "      <td>-1.012288</td>\n",
       "      <td>0.546495</td>\n",
       "      <td>2.476032</td>\n",
       "      <td>0.495206</td>\n",
       "      <td>0.482137</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            C                             D                    \n",
       "          sum      mean       std       sum      mean       std\n",
       "A                                                              \n",
       "bar -2.105597 -0.701866  0.500511  0.097302  0.032434  0.763623\n",
       "foo -5.061439 -1.012288  0.546495  2.476032  0.495206  0.482137"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.agg([np.sum, np.mean, np.std])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 对DataFrame不同列应用不同的函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bar</th>\n",
       "      <td>-2.105597</td>\n",
       "      <td>0.763623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>-5.061439</td>\n",
       "      <td>0.482137</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            C         D\n",
       "A                      \n",
       "bar -2.105597  0.763623\n",
       "foo -5.061439  0.482137"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped.agg({'C': np.sum,\n",
    "             'D': lambda x: np.std(x, ddof=1)})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cython优化过的数据集成函数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "一些常用的数据集成函数，比如sum、mean、std、sem，都有优化过的Cython实现:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bar</th>\n",
       "      <td>-2.105597</td>\n",
       "      <td>0.097302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foo</th>\n",
       "      <td>-5.061439</td>\n",
       "      <td>2.476032</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            C         D\n",
       "A                      \n",
       "bar -2.105597  0.097302\n",
       "foo -5.061439  2.476032"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('A').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">bar</th>\n",
       "      <th>one</th>\n",
       "      <td>-0.365797</td>\n",
       "      <td>-0.636498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>-0.462709</td>\n",
       "      <td>0.864412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>-1.277091</td>\n",
       "      <td>-0.130611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">foo</th>\n",
       "      <th>one</th>\n",
       "      <td>-0.983130</td>\n",
       "      <td>0.571420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>-0.616584</td>\n",
       "      <td>-0.132354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>-1.239297</td>\n",
       "      <td>0.732773</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  C         D\n",
       "A   B                        \n",
       "bar one   -0.365797 -0.636498\n",
       "    three -0.462709  0.864412\n",
       "    two   -1.277091 -0.130611\n",
       "foo one   -0.983130  0.571420\n",
       "    three -0.616584 -0.132354\n",
       "    two   -1.239297  0.732773"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['A', 'B']).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据转换函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}