notebook/09-Pandas分组聚合详解.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> 教你学会 Pandas 不是我的目的，**教你轻松玩转 Pandas 才是我的目的**。我会通过一系列实例来带入 Pandas 的知识点，让你在学习 Pandas 的路上不再枯燥。\n",
    "\n",
    "> 声明：我所写的**轻松玩转 Pandas 教程都是免费的**，如果对你有帮助，你可以持续关注我。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据分析过程中，最常见的一个场景莫过于分组统计了，这个也是学习 Pandas 的一个核心功能。我们一起来看一看吧。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.546468Z",
     "start_time": "2018-07-26T18:01:21.542459Z"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 导入相关库\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.613648Z",
     "start_time": "2018-07-26T18:01:21.557497Z"
    }
   },
   "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>age</th>\n",
       "      <th>city</th>\n",
       "      <th>income</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Tom</th>\n",
       "      <td>18.0</td>\n",
       "      <td>Bei Jing</td>\n",
       "      <td>3000</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>30.0</td>\n",
       "      <td>Shang Hai</td>\n",
       "      <td>8000</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mary</th>\n",
       "      <td>35.0</td>\n",
       "      <td>Guang Zhou</td>\n",
       "      <td>8000</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>James</th>\n",
       "      <td>18.0</td>\n",
       "      <td>Shen Zhen</td>\n",
       "      <td>4000</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andy</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alice</th>\n",
       "      <td>30.0</td>\n",
       "      <td></td>\n",
       "      <td>7000</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        age        city  income     sex\n",
       "name                                   \n",
       "Tom    18.0   Bei Jing     3000    male\n",
       "Bob    30.0  Shang Hai     8000    male\n",
       "Mary   35.0  Guang Zhou    8000  female\n",
       "James  18.0   Shen Zhen    4000    male\n",
       "Andy    NaN         NaN    6000     NaN\n",
       "Alice  30.0                7000  female"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = pd.Index(data=[\"Tom\", \"Bob\", \"Mary\", \"James\", \"Andy\", \"Alice\"], name=\"name\")\n",
    "\n",
    "data = {\n",
    "    \"age\": [18, 30, 35, 18, np.nan, 30],\n",
    "    \"city\": [\"Bei Jing \", \"Shang Hai \", \"Guang Zhou\", \"Shen Zhen\", np.nan, \" \"],\n",
    "    \"sex\": [\"male\", \"male\", \"female\", \"male\", np.nan, \"female\"],\n",
    "    \"income\": [3000, 8000, 8000, 4000, 6000, 7000]\n",
    "}\n",
    "\n",
    "user_info = pd.DataFrame(data=data, index=index)\n",
    "user_info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 将对象分割成组\n",
    "\n",
    "在进行分组统计前，首先要做的就是进行分组。既然是分组，就需要依赖于某个信息。\n",
    "\n",
    "比如，依据性别来分组。直接调用 `user_info.groupby(user_info[\"sex\"])`即可完成按照性别分组。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.632698Z",
     "start_time": "2018-07-26T18:01:21.617658Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'female': Index(['Mary', 'Alice'], dtype='object', name='name'),\n",
       " 'male': Index(['Tom', 'Bob', 'James'], dtype='object', name='name')}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped  = user_info.groupby(user_info[\"sex\"])\n",
    "grouped.groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看到，已经能够正确的按照性别来进行分组了。通常我们为了更简单，会使用这种方式来实现相同的功能：`user_info.groupby(\"sex\")` 。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.656762Z",
     "start_time": "2018-07-26T18:01:21.635706Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'female': Index(['Mary', 'Alice'], dtype='object', name='name'),\n",
       " 'male': Index(['Tom', 'Bob', 'James'], dtype='object', name='name')}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped  = user_info.groupby(\"sex\")\n",
    "grouped.groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "你可能会想，能不能先按照性别来分组，再按照年龄进一步分组呢？答案是可以的，看这里。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.691856Z",
     "start_time": "2018-07-26T18:01:21.659770Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{('female', 30.0): Index(['Alice'], dtype='object', name='name'),\n",
       " ('female', 35.0): Index(['Mary'], dtype='object', name='name'),\n",
       " ('male', 18.0): Index(['Tom', 'James'], dtype='object', name='name'),\n",
       " ('male', 30.0): Index(['Bob'], dtype='object', name='name'),\n",
       " (nan, nan): Index(['Andy'], dtype='object', name='name')}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped  = user_info.groupby([\"sex\", \"age\"])\n",
    "grouped.groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关闭排序\n",
    "\n",
    "默认情况下，`groupby` 会在操作过程中对数据进行排序。如果为了更好的性能，可以设置 `sort=False`。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.716922Z",
     "start_time": "2018-07-26T18:01:21.694864Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{('female', 30.0): Index(['Alice'], dtype='object', name='name'),\n",
       " ('female', 35.0): Index(['Mary'], dtype='object', name='name'),\n",
       " ('male', 18.0): Index(['Tom', 'James'], dtype='object', name='name'),\n",
       " ('male', 30.0): Index(['Bob'], dtype='object', name='name'),\n",
       " (nan, nan): Index(['Andy'], dtype='object', name='name')}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped  = user_info.groupby([\"sex\", \"age\"], sort=False)\n",
    "grouped.groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 选择列\n",
    "\n",
    "在使用 `groupby` 进行分组后，可以使用切片 `[]` 操作来完成对某一列的选择。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.753018Z",
     "start_time": "2018-07-26T18:01:21.722939Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.DataFrameGroupBy object at 0x000002E355D787B8>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped  = user_info.groupby(\"sex\")\n",
    "grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.795131Z",
     "start_time": "2018-07-26T18:01:21.756027Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.SeriesGroupBy object at 0x000002E355D78470>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped[\"city\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 遍历分组\n",
    "\n",
    "在对数据进行分组后，可以进行遍历。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.835238Z",
     "start_time": "2018-07-26T18:01:21.798139Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: female\n",
      "group:         age        city  income     sex\n",
      "name                                   \n",
      "Mary   35.0  Guang Zhou    8000  female\n",
      "Alice  30.0                7000  female\n",
      "--------------\n",
      "name: male\n",
      "group:         age        city  income   sex\n",
      "name                                 \n",
      "Tom    18.0   Bei Jing     3000  male\n",
      "Bob    30.0  Shang Hai     8000  male\n",
      "James  18.0   Shen Zhen    4000  male\n",
      "--------------\n"
     ]
    }
   ],
   "source": [
    "grouped  = user_info.groupby(\"sex\")\n",
    "\n",
    "for name, group in grouped:\n",
    "    print(\"name: {}\".format(name))\n",
    "    print(\"group: {}\".format(group))\n",
    "    print(\"--------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果是根据多个字段来分组的，每个组的名称是一个元组。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.882365Z",
     "start_time": "2018-07-26T18:01:21.840251Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: ('female', 30.0)\n",
      "group:         age city  income     sex\n",
      "name                            \n",
      "Alice  30.0         7000  female\n",
      "--------------\n",
      "name: ('female', 35.0)\n",
      "group:        age        city  income     sex\n",
      "name                                  \n",
      "Mary  35.0  Guang Zhou    8000  female\n",
      "--------------\n",
      "name: ('male', 18.0)\n",
      "group:         age       city  income   sex\n",
      "name                                \n",
      "Tom    18.0  Bei Jing     3000  male\n",
      "James  18.0  Shen Zhen    4000  male\n",
      "--------------\n",
      "name: ('male', 30.0)\n",
      "group:        age        city  income   sex\n",
      "name                                \n",
      "Bob   30.0  Shang Hai     8000  male\n",
      "--------------\n"
     ]
    }
   ],
   "source": [
    "grouped  = user_info.groupby([\"sex\", \"age\"])\n",
    "\n",
    "for name, group in grouped:\n",
    "    print(\"name: {}\".format(name))\n",
    "    print(\"group: {}\".format(group))\n",
    "    print(\"--------------\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 选择一个组\n",
    "\n",
    "分组后，我们可以通过 `get_group` 方法来选择其中的某一个组。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.907430Z",
     "start_time": "2018-07-26T18:01:21.885371Z"
    }
   },
   "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>age</th>\n",
       "      <th>city</th>\n",
       "      <th>income</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Tom</th>\n",
       "      <td>18.0</td>\n",
       "      <td>Bei Jing</td>\n",
       "      <td>3000</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>30.0</td>\n",
       "      <td>Shang Hai</td>\n",
       "      <td>8000</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>James</th>\n",
       "      <td>18.0</td>\n",
       "      <td>Shen Zhen</td>\n",
       "      <td>4000</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        age        city  income   sex\n",
       "name                                 \n",
       "Tom    18.0   Bei Jing     3000  male\n",
       "Bob    30.0  Shang Hai     8000  male\n",
       "James  18.0   Shen Zhen    4000  male"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped  = user_info.groupby(\"sex\")\n",
    "grouped.get_group(\"male\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.940518Z",
     "start_time": "2018-07-26T18:01:21.910438Z"
    }
   },
   "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>age</th>\n",
       "      <th>city</th>\n",
       "      <th>income</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Tom</th>\n",
       "      <td>18.0</td>\n",
       "      <td>Bei Jing</td>\n",
       "      <td>3000</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>James</th>\n",
       "      <td>18.0</td>\n",
       "      <td>Shen Zhen</td>\n",
       "      <td>4000</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        age       city  income   sex\n",
       "name                                \n",
       "Tom    18.0  Bei Jing     3000  male\n",
       "James  18.0  Shen Zhen    4000  male"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_info.groupby([\"sex\", \"age\"]).get_group((\"male\", 18))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 聚合\n",
    "\n",
    "分组的目的是为了统计，统计的时候需要聚合，所以我们需要在分完组后来看下如何进行聚合。常见的一些聚合操作有：计数、求和、最大值、最小值、平均值等。\n",
    "\n",
    "想要实现聚合操作，一种方式就是调用 `agg` 方法。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:21.998674Z",
     "start_time": "2018-07-26T18:01:21.943526Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sex\n",
       "female    2.0\n",
       "male      3.0\n",
       "Name: age, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 获取不同性别下所包含的人数\n",
    "grouped = user_info.groupby(\"sex\")\n",
    "grouped[\"age\"].agg(len)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.027751Z",
     "start_time": "2018-07-26T18:01:22.003693Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sex\n",
       "female    35.0\n",
       "male      30.0\n",
       "Name: age, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 获取不同性别下包含的最大的年龄\n",
    "grouped = user_info.groupby(\"sex\")\n",
    "grouped[\"age\"].agg(np.max)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果是根据多个键来进行聚合，默认情况下得到的结果是一个多层索引结构。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.061842Z",
     "start_time": "2018-07-26T18:01:22.030759Z"
    }
   },
   "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>city</th>\n",
       "      <th>income</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">female</th>\n",
       "      <th>30.0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35.0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">male</th>\n",
       "      <th>18.0</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30.0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             city  income\n",
       "sex    age               \n",
       "female 30.0     1       1\n",
       "       35.0     1       1\n",
       "male   18.0     2       2\n",
       "       30.0     1       1"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = user_info.groupby([\"sex\", \"age\"])\n",
    "rs = grouped.agg(len)\n",
    "rs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "有两种方式可以避免出现多层索引，先来介绍第一种。对包含多层索引的对象调用 `reset_index` 方法。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.092924Z",
     "start_time": "2018-07-26T18:01:22.064850Z"
    }
   },
   "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>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>male</td>\n",
       "      <td>18.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      sex   age  city  income\n",
       "0  female  30.0     1       1\n",
       "1  female  35.0     1       1\n",
       "2    male  18.0     2       2\n",
       "3    male  30.0     1       1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rs.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "另外一种方式是在分组时，设置参数 `as_index=False`。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.128018Z",
     "start_time": "2018-07-26T18:01:22.095933Z"
    }
   },
   "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>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "      <th>income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>male</td>\n",
       "      <td>18.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      sex   age  city  income\n",
       "0  female  30.0     1       1\n",
       "1  female  35.0     1       1\n",
       "2    male  18.0     2       2\n",
       "3    male  30.0     1       1"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = user_info.groupby([\"sex\", \"age\"], as_index=False)\n",
    "grouped.agg(len)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Series 和 DataFrame 都包含了 `describe` 方法，我们分组后一样可以使用 `describe` 方法来查看数据的情况。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.199208Z",
     "start_time": "2018-07-26T18:01:22.131026Z"
    }
   },
   "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=\"8\" halign=\"left\">age</th>\n",
       "      <th colspan=\"8\" halign=\"left\">income</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>female</th>\n",
       "      <td>2.0</td>\n",
       "      <td>32.5</td>\n",
       "      <td>3.535534</td>\n",
       "      <td>30.0</td>\n",
       "      <td>31.25</td>\n",
       "      <td>32.5</td>\n",
       "      <td>33.75</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7500.0</td>\n",
       "      <td>707.106781</td>\n",
       "      <td>7000.0</td>\n",
       "      <td>7250.0</td>\n",
       "      <td>7500.0</td>\n",
       "      <td>7750.0</td>\n",
       "      <td>8000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>3.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>6.928203</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.00</td>\n",
       "      <td>18.0</td>\n",
       "      <td>24.00</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>2645.751311</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>3500.0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>8000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         age                                                 income          \\\n",
       "       count  mean       std   min    25%   50%    75%   max  count    mean   \n",
       "sex                                                                           \n",
       "female   2.0  32.5  3.535534  30.0  31.25  32.5  33.75  35.0    2.0  7500.0   \n",
       "male     3.0  22.0  6.928203  18.0  18.00  18.0  24.00  30.0    3.0  5000.0   \n",
       "\n",
       "                                                             \n",
       "                std     min     25%     50%     75%     max  \n",
       "sex                                                          \n",
       "female   707.106781  7000.0  7250.0  7500.0  7750.0  8000.0  \n",
       "male    2645.751311  3000.0  3500.0  4000.0  6000.0  8000.0  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = user_info.groupby(\"sex\")\n",
    "grouped.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 一次应用多个聚合操作\n",
    "\n",
    "有时候进行分组后，不单单想得到一个统计结果，有可能是多个。比如想统计出不同性别下的一个收入的总和和平均值。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.230292Z",
     "start_time": "2018-07-26T18:01:22.202216Z"
    }
   },
   "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>sum</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>female</th>\n",
       "      <td>15000</td>\n",
       "      <td>7500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>15000</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          sum  mean\n",
       "sex                \n",
       "female  15000  7500\n",
       "male    15000  5000"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = user_info.groupby(\"sex\")\n",
    "grouped[\"income\"].agg([np.sum, np.mean])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "如果想将统计结果进行重命名，可以传入字典。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.262376Z",
     "start_time": "2018-07-26T18:01:22.233299Z"
    }
   },
   "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>income_sum</th>\n",
       "      <th>income_mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>female</th>\n",
       "      <td>15000</td>\n",
       "      <td>7500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>15000</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        income_sum  income_mean\n",
       "sex                            \n",
       "female       15000         7500\n",
       "male         15000         5000"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = user_info.groupby(\"sex\")\n",
    "grouped[\"income\"].agg([np.sum, np.mean]).rename(columns={\"sum\": \"income_sum\", \"mean\": \"income_mean\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 对DataFrame列应用不同的聚合操作\n",
    "\n",
    "有时候可能需要对不同的列使用不同的聚合操作。例如，想要统计不同性别下人群的年龄的均值以及收入的总和。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.295464Z",
     "start_time": "2018-07-26T18:01:22.265394Z"
    }
   },
   "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>age_mean</th>\n",
       "      <th>income_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>female</th>\n",
       "      <td>32.5</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>22.0</td>\n",
       "      <td>15000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        age_mean  income_sum\n",
       "sex                         \n",
       "female      32.5       15000\n",
       "male        22.0       15000"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped = user_info.groupby(\"sex\")\n",
    "grouped.agg({\"age\": np.mean, \"income\": np.sum}).rename(columns={\"age\": \"age_mean\", \"income\": \"income_sum\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## transform 操作\n",
    "\n",
    "前面进行聚合运算的时候，得到的结果是一个以分组名作为索引的结果对象。虽然可以指定 `as_index=False` ,但是得到的索引也并不是元数据的索引。如果我们想使用原数组的索引的话，就需要进行 merge 转换。\n",
    "\n",
    "`transform`方法简化了这个过程，它会把 func 参数应用到所有分组，然后把结果放置到原数组的索引上（如果结果是一个标量，就进行广播）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.322536Z",
     "start_time": "2018-07-26T18:01:22.297470Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sex\n",
       "female    7500\n",
       "male      5000\n",
       "Name: income, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 通过 agg 得到的结果的索引是分组名\n",
    "grouped = user_info.groupby(\"sex\")\n",
    "grouped[\"income\"].agg(np.mean)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.346601Z",
     "start_time": "2018-07-26T18:01:22.324544Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name\n",
       "Tom      5000.0\n",
       "Bob      5000.0\n",
       "Mary     7500.0\n",
       "James    5000.0\n",
       "Andy        NaN\n",
       "Alice    7500.0\n",
       "Name: income, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 通过 transform 得到的结果的索引是原始索引，它会将得到的结果自动关联上原始的索引\n",
    "grouped = user_info.groupby(\"sex\")\n",
    "grouped[\"income\"].transform(np.mean)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看到，通过 `transform` 操作得到的结果的长度与原来保持一致。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## apply 操作\n",
    "\n",
    "除了 `transform` 操作外，还有更神奇的 `apply` 操作。\n",
    "\n",
    "`apply` 会将待处理的对象拆分成多个片段，然后对各片段调用传入的函数，最后尝试用 `pd.concat()` 把结果组合起来。func 的返回值可以是 Pandas 对象或标量，并且数组对象的大小不限。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.368659Z",
     "start_time": "2018-07-26T18:01:22.348607Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sex\n",
       "female    7500.0\n",
       "male      5000.0\n",
       "Name: income, dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 使用 apply 来完成上面的聚合\n",
    "grouped = user_info.groupby(\"sex\")\n",
    "grouped[\"income\"].apply(np.mean)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "来看下 `apply` 不一样的用法吧。\n",
    "\n",
    "比如想要统计不同性别最高收入的前n个值，可以通过下面这种方式实现。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.395732Z",
     "start_time": "2018-07-26T18:01:22.371668Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sex\n",
       "female    [8000, 7000]\n",
       "male      [8000, 4000]\n",
       "Name: income, dtype: object"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def f1(ser, num=2):\n",
    "    return ser.nlargest(num).tolist()\n",
    "\n",
    "grouped[\"income\"].apply(f1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "另外，如果想要获取不同性别下的年龄的均值，通过 `apply` 可以如下实现。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-26T18:01:22.424809Z",
     "start_time": "2018-07-26T18:01:22.398741Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sex\n",
       "female    32.5\n",
       "male      22.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def f2(df):\n",
    "    return df[\"age\"].mean()\n",
    "\n",
    "grouped.apply(f2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2018-07-09T13:02:57.207715Z",
     "start_time": "2018-07-09T13:02:57.197689Z"
    }
   },
   "source": [
    "------------\n",
    "\n",
    "\n",
    "想要学习更多关于人工智能的知识，请关注公众号：**AI派**\n",
    "\n",
    "![](../image/公众号—AI派.jpg)\n",
    "\n",
    "这里我将整篇文章的内容整理成了pdf，想要pdf文件的可以在公众号后台回复关键字：**pandas**。"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}