practice3 - drinks - 数据分组.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 酒类消费数据 - 数据分组"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](images/3.jpeg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 加载数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>beer_servings</th>\n",
       "      <th>spirit_servings</th>\n",
       "      <th>wine_servings</th>\n",
       "      <th>total_litres_of_pure_alcohol</th>\n",
       "      <th>continent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>AS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>89</td>\n",
       "      <td>132</td>\n",
       "      <td>54</td>\n",
       "      <td>4.9</td>\n",
       "      <td>EU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>0.7</td>\n",
       "      <td>AF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>245</td>\n",
       "      <td>138</td>\n",
       "      <td>312</td>\n",
       "      <td>12.4</td>\n",
       "      <td>EU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>217</td>\n",
       "      <td>57</td>\n",
       "      <td>45</td>\n",
       "      <td>5.9</td>\n",
       "      <td>AF</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       country  beer_servings  spirit_servings  wine_servings  \\\n",
       "0  Afghanistan              0                0              0   \n",
       "1      Albania             89              132             54   \n",
       "2      Algeria             25                0             14   \n",
       "3      Andorra            245              138            312   \n",
       "4       Angola            217               57             45   \n",
       "\n",
       "   total_litres_of_pure_alcohol continent  \n",
       "0                           0.0        AS  \n",
       "1                           4.9        EU  \n",
       "2                           0.7        AF  \n",
       "3                          12.4        EU  \n",
       "4                           5.9        AF  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "drinks = pd.read_csv('data/drinks.csv')\n",
    "drinks.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 193 entries, 0 to 192\n",
      "Data columns (total 6 columns):\n",
      "country                         193 non-null object\n",
      "beer_servings                   193 non-null int64\n",
      "spirit_servings                 193 non-null int64\n",
      "wine_servings                   193 non-null int64\n",
      "total_litres_of_pure_alcohol    193 non-null float64\n",
      "continent                       170 non-null object\n",
      "dtypes: float64(1), int64(3), object(2)\n",
      "memory usage: 9.1+ KB\n"
     ]
    }
   ],
   "source": [
    "drinks.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 查询：哪个大陆(continent)平均消耗的啤酒(beer)更多？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "continent\n",
       "EU    193.777778\n",
       "SA    175.083333\n",
       "OC     89.687500\n",
       "AF     61.471698\n",
       "AS     37.045455\n",
       "Name: Beer servings mean, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drinks.groupby('continent').beer_servings.mean().sort_values(ascending=False).rename('Beer servings mean')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 输出每个大陆(continent)的红酒消耗(wine_servings)的描述性统计值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>continent</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>AF</th>\n",
       "      <td>53.0</td>\n",
       "      <td>16.264151</td>\n",
       "      <td>38.846419</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13.00</td>\n",
       "      <td>233.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS</th>\n",
       "      <td>44.0</td>\n",
       "      <td>9.068182</td>\n",
       "      <td>21.667034</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8.00</td>\n",
       "      <td>123.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EU</th>\n",
       "      <td>45.0</td>\n",
       "      <td>142.222222</td>\n",
       "      <td>97.421738</td>\n",
       "      <td>0.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>128.0</td>\n",
       "      <td>195.00</td>\n",
       "      <td>370.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OC</th>\n",
       "      <td>16.0</td>\n",
       "      <td>35.625000</td>\n",
       "      <td>64.555790</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>23.25</td>\n",
       "      <td>212.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SA</th>\n",
       "      <td>12.0</td>\n",
       "      <td>62.416667</td>\n",
       "      <td>88.620189</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>98.50</td>\n",
       "      <td>221.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           count        mean        std  min   25%    50%     75%    max\n",
       "continent                                                               \n",
       "AF          53.0   16.264151  38.846419  0.0   1.0    2.0   13.00  233.0\n",
       "AS          44.0    9.068182  21.667034  0.0   0.0    1.0    8.00  123.0\n",
       "EU          45.0  142.222222  97.421738  0.0  59.0  128.0  195.00  370.0\n",
       "OC          16.0   35.625000  64.555790  0.0   1.0    8.5   23.25  212.0\n",
       "SA          12.0   62.416667  88.620189  1.0   3.0   12.0   98.50  221.0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drinks.groupby('continent').wine_servings.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 打印出每个大陆每种酒类别的消耗平均值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>beer_servings</th>\n",
       "      <th>spirit_servings</th>\n",
       "      <th>wine_servings</th>\n",
       "      <th>total_litres_of_pure_alcohol</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>continent</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AF</th>\n",
       "      <td>61.471698</td>\n",
       "      <td>16.339623</td>\n",
       "      <td>16.264151</td>\n",
       "      <td>3.007547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS</th>\n",
       "      <td>37.045455</td>\n",
       "      <td>60.840909</td>\n",
       "      <td>9.068182</td>\n",
       "      <td>2.170455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EU</th>\n",
       "      <td>193.777778</td>\n",
       "      <td>132.555556</td>\n",
       "      <td>142.222222</td>\n",
       "      <td>8.617778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OC</th>\n",
       "      <td>89.687500</td>\n",
       "      <td>58.437500</td>\n",
       "      <td>35.625000</td>\n",
       "      <td>3.381250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SA</th>\n",
       "      <td>175.083333</td>\n",
       "      <td>114.750000</td>\n",
       "      <td>62.416667</td>\n",
       "      <td>6.308333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           beer_servings  spirit_servings  wine_servings  \\\n",
       "continent                                                  \n",
       "AF             61.471698        16.339623      16.264151   \n",
       "AS             37.045455        60.840909       9.068182   \n",
       "EU            193.777778       132.555556     142.222222   \n",
       "OC             89.687500        58.437500      35.625000   \n",
       "SA            175.083333       114.750000      62.416667   \n",
       "\n",
       "           total_litres_of_pure_alcohol  \n",
       "continent                                \n",
       "AF                             3.007547  \n",
       "AS                             2.170455  \n",
       "EU                             8.617778  \n",
       "OC                             3.381250  \n",
       "SA                             6.308333  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drinks.groupby('continent').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 打印出每个大陆每种酒类别的消耗中位数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>beer_servings</th>\n",
       "      <th>spirit_servings</th>\n",
       "      <th>wine_servings</th>\n",
       "      <th>total_litres_of_pure_alcohol</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>continent</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AF</th>\n",
       "      <td>32.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS</th>\n",
       "      <td>17.5</td>\n",
       "      <td>16.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EU</th>\n",
       "      <td>219.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>128.0</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OC</th>\n",
       "      <td>52.5</td>\n",
       "      <td>37.0</td>\n",
       "      <td>8.5</td>\n",
       "      <td>1.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SA</th>\n",
       "      <td>162.5</td>\n",
       "      <td>108.5</td>\n",
       "      <td>12.0</td>\n",
       "      <td>6.85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           beer_servings  spirit_servings  wine_servings  \\\n",
       "continent                                                  \n",
       "AF                  32.0              3.0            2.0   \n",
       "AS                  17.5             16.0            1.0   \n",
       "EU                 219.0            122.0          128.0   \n",
       "OC                  52.5             37.0            8.5   \n",
       "SA                 162.5            108.5           12.0   \n",
       "\n",
       "           total_litres_of_pure_alcohol  \n",
       "continent                                \n",
       "AF                                 2.30  \n",
       "AS                                 1.20  \n",
       "EU                                10.00  \n",
       "OC                                 1.75  \n",
       "SA                                 6.85  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drinks.groupby('continent').median()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 打印出每个大陆对spirit饮品消耗的平均值，最大值和最小值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>max</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>continent</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AF</th>\n",
       "      <td>16.339623</td>\n",
       "      <td>152</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS</th>\n",
       "      <td>60.840909</td>\n",
       "      <td>326</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EU</th>\n",
       "      <td>132.555556</td>\n",
       "      <td>373</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OC</th>\n",
       "      <td>58.437500</td>\n",
       "      <td>254</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SA</th>\n",
       "      <td>114.750000</td>\n",
       "      <td>302</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 mean  max  min\n",
       "continent                      \n",
       "AF          16.339623  152    0\n",
       "AS          60.840909  326    0\n",
       "EU         132.555556  373    0\n",
       "OC          58.437500  254    0\n",
       "SA         114.750000  302   25"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drinks.groupby('continent').spirit_servings.agg(['mean', 'max', 'min'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 查询：中国的啤酒消费在亚洲和全球的排名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "中国啤酒消费量:  79\n",
      "中国啤酒消费在亚洲排名:  6 / 44\n",
      "中国啤酒消费在全球排名:  91 / 193\n"
     ]
    }
   ],
   "source": [
    "china_beer = drinks[drinks.country == 'China'].beer_servings.iloc[0]\n",
    "print ('中国啤酒消费量: ', china_beer)\n",
    "print ('中国啤酒消费在亚洲排名: ', (drinks[drinks.continent == 'AS'].beer_servings > china_beer).sum()+1, '/', drinks[drinks.continent == 'AS'].beer_servings.count())\n",
    "print ('中国啤酒消费在全球排名: ', (drinks['beer_servings'] > china_beer).sum()+1 , '/', drinks['beer_servings'].count())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}