chapter04.ipynb · silianpan/Excel_to_Python

加入 Gitee

与超过 1200万开发者一起发现、参与优秀开源项目，私有仓库也完全免费：）

该仓库未声明开源许可证文件（LICENSE），使用请关注具体项目描述及其代码上游依赖。

克隆/下载

chapter04.ipynb 118.81 KB

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 04 切配菜品-数值操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "# 一个cell输出多行语句\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、数值替换\n",
    "数值替换：将数值A替换成数值B，用在**异常值替换处理**、**缺失值填充处理**中\n",
    "* 一对一替换\n",
    "* 多对一替换\n",
    "* 多对多替换\n",
    "\n",
    "### 1.1 一对一替换\n",
    "将某区域中的一个值替换成另一个值\n",
    "\n",
    "**replace(A, B)**表示将A替换成B"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>204</td>\n",
       "      <td>2018-08-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>204</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间\n",
       "0   A1  张 通    101   31 2018-08-08\n",
       "1   A2   李谷    102   45 2018-08-09\n",
       "2   A3   孙凤    103   23 2018-08-10\n",
       "3   A4   赵恒    104  204 2018-08-11\n",
       "4   A5   赵恒    104  204 2018-08-12"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>33</td>\n",
       "      <td>2018-08-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>33</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄       成交时间\n",
       "0   A1  张 通    101  31 2018-08-08\n",
       "1   A2   李谷    102  45 2018-08-09\n",
       "2   A3   孙凤    103  23 2018-08-10\n",
       "3   A4   赵恒    104  33 2018-08-11\n",
       "4   A5   赵恒    104  33 2018-08-12"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将240岁年龄替换成33岁\n",
    "df = pd.read_excel('./data/select_condition.xlsx')\n",
    "df\n",
    "df['年龄'] = df['年龄'].replace(204, 33)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>33</td>\n",
       "      <td>2018-08-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>33</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄       成交时间\n",
       "0   A1  张 通    101  31 2018-08-08\n",
       "1   A2   李谷    102  45 2018-08-09\n",
       "2   A3   孙凤    103  23 2018-08-10\n",
       "3   A4   赵恒    104  33 2018-08-11\n",
       "4   A5   赵恒    104  33 2018-08-12"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 下面相当于fillna，np.NaN是对缺失值的一种表示方法\n",
    "df.replace(np.NaN, 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 多对一替换\n",
    "将多个值替换成某一个值\n",
    "\n",
    "Excel实现：对某一列使用if函数\n",
    "\n",
    "**replace([A, B], C)**将A、B替换成C"
   ]
  },
  {
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间\n",
       "0   A1  张 通    101   31 2018-08-08\n",
       "1   A2   李谷    102   45 2018-08-09\n",
       "2   A3   孙凤    103   23 2018-08-10\n",
       "3   A4   赵恒    104  240 2018-08-11\n",
       "4   A5   赵恒    104  260 2018-08-12\n",
       "5   A6   王丹    105  280 2018-08-12"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>33</td>\n",
       "      <td>2018-08-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>33</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>33</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄       成交时间\n",
       "0   A1  张 通    101  31 2018-08-08\n",
       "1   A2   李谷    102  45 2018-08-09\n",
       "2   A3   孙凤    103  23 2018-08-10\n",
       "3   A4   赵恒    104  33 2018-08-11\n",
       "4   A5   赵恒    104  33 2018-08-12\n",
       "5   A6   王丹    105  33 2018-08-12"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('./data/more_one_replace.xlsx')\n",
    "df\n",
    "df.replace([240, 260, 280], 33)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3 多对多替换\n",
    "某个区域中多个一对一替换，比如：将年龄240替换成平均值减一，260替换成平均值，280替换成平均值加一\n",
    "\n",
    "Excel实现：if嵌套实现\n",
    "\n",
    "**replace({'A':'a', 'B':'b'})**表示a替换A，用b替换B"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>32</td>\n",
       "      <td>2018-08-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>33</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>34</td>\n",
       "      <td>2018-08-12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄       成交时间\n",
       "0   A1  张 通    101  31 2018-08-08\n",
       "1   A2   李谷    102  45 2018-08-09\n",
       "2   A3   孙凤    103  23 2018-08-10\n",
       "3   A4   赵恒    104  32 2018-08-11\n",
       "4   A5   赵恒    104  33 2018-08-12\n",
       "5   A6   王丹    105  34 2018-08-12"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.replace({240:32, 260:33, 280:34})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二、数值排序\n",
    "按具体数值大小进行排序，有**升序**和**降序**\n",
    "\n",
    "### 2.1 按照某一列数值进行排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "0   A1  张 通    101   31 2018-08-08     1\n",
       "1   A2   李谷    102   45 2018-08-09     2\n",
       "2   A3   孙凤    103   23 2018-08-10     1\n",
       "3   A4   赵恒    104  240 2018-08-11     2\n",
       "4   A5   赵恒    104  260 2018-08-12     3\n",
       "5   A6   王丹    105  280 2018-08-12     4"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "0   A1  张 通    101   31 2018-08-08     1\n",
       "2   A3   孙凤    103   23 2018-08-10     1\n",
       "1   A2   李谷    102   45 2018-08-09     2\n",
       "3   A4   赵恒    104  240 2018-08-11     2\n",
       "4   A5   赵恒    104  260 2018-08-12     3\n",
       "5   A6   王丹    105  280 2018-08-12     4"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "5   A6   王丹    105  280 2018-08-12     4\n",
       "4   A5   赵恒    104  260 2018-08-12     3\n",
       "1   A2   李谷    102   45 2018-08-09     2\n",
       "3   A4   赵恒    104  240 2018-08-11     2\n",
       "0   A1  张 通    101   31 2018-08-08     1\n",
       "2   A3   孙凤    103   23 2018-08-10     1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sort_values(by=['col'], ascending=False)\n",
    "# by：排序的列名\n",
    "# ascending=False，降序；默认值为True表示升序\n",
    "df = pd.read_excel('./data/sort_one.xlsx')\n",
    "df\n",
    "df.sort_values(by=['销售ID'])\n",
    "df.sort_values(by=['销售ID'], ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 按照有缺失值的列进行排序\n",
    "当待排序的列中有缺失值时，可以通过设置**na_position**参数对缺失值的显示位置进行设置，默认参数值为last，可以不写，表示缺失值在最后"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "0   A1  张 通    101   31 2018-08-08   1.0\n",
       "1   A2   李谷    102   45 2018-08-09   2.0\n",
       "2   A3   孙凤    103   23 2018-08-10   1.0\n",
       "3   A4   赵恒    104  240 2018-08-11   NaN\n",
       "4   A5   赵恒    104  260 2018-08-12   3.0\n",
       "5   A6   王丹    105  280 2018-08-12   4.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "0   A1  张 通    101   31 2018-08-08   1.0\n",
       "2   A3   孙凤    103   23 2018-08-10   1.0\n",
       "1   A2   李谷    102   45 2018-08-09   2.0\n",
       "4   A5   赵恒    104  260 2018-08-12   3.0\n",
       "5   A6   王丹    105  280 2018-08-12   4.0\n",
       "3   A4   赵恒    104  240 2018-08-11   NaN"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "3   A4   赵恒    104  240 2018-08-11   NaN\n",
       "0   A1  张 通    101   31 2018-08-08   1.0\n",
       "2   A3   孙凤    103   23 2018-08-10   1.0\n",
       "1   A2   李谷    102   45 2018-08-09   2.0\n",
       "4   A5   赵恒    104  260 2018-08-12   3.0\n",
       "5   A6   王丹    105  280 2018-08-12   4.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('./data/sort_na.xlsx')\n",
    "df\n",
    "df.sort_values(by=['销售ID'])\n",
    "df.sort_values(by=['销售ID'], na_position='first')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 按照多列数值进行排序\n",
    "df.sort_values(by=['col1', 'col2'], ascending=[True, False])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "2   A3   孙凤    103   23 2018-08-10   1.0\n",
       "0   A1  张 通    101   31 2018-08-08   1.0\n",
       "1   A2   李谷    102   45 2018-08-09   2.0\n",
       "4   A5   赵恒    104  260 2018-08-12   3.0\n",
       "5   A6   王丹    105  280 2018-08-12   4.0\n",
       "3   A4   赵恒    104  240 2018-08-11   NaN"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(by=['销售ID', '成交时间'], ascending=[True, False])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 三、数值排名\n",
    "排名和排序是相对应的，排名会**新增一列**，排名从1开始\n",
    "\n",
    "Excel实现：RANK.AVG()和RANK.EQ()两个函数，没有重复值时，两个函数的效果完全一样。区别：处理重复值方式不同。\n",
    "\n",
    "**RANK.AVG(number, ref, order)**\n",
    "\n",
    "当排名的数值有重复值时，返回的重复值的平均排名\n",
    "\n",
    "* number：排名的数值\n",
    "* ref：一整列数值的范围\n",
    "* order：降序或升序\n",
    "\n",
    "<img src='./image/rank_avg.jpg' width='50%'>\n",
    "\n",
    "**RANK.EQ(number, ref, order)**\n",
    "\n",
    "当排名有重复时，RANK.EQ返回重复值的最佳排名\n",
    "\n",
    "<img src='./image/rank_eq.jpg' width='50%'>\n",
    "\n",
    "Python实现：**rank()方法**\n",
    "\n",
    "* ascending：True升序排列；False降序排列；默认为True\n",
    "* method：指明待排列值有重复值时的处理情况\n",
    "\n",
    "<img src='./image/rank_method.jpg' width='80%'>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "0   A1  张 通    101   31 2018-08-08     1\n",
       "1   A2   李谷    102   45 2018-08-09     2\n",
       "2   A3   孙凤    103   23 2018-08-10     1\n",
       "3   A4   赵恒    104  240 2018-08-11     2\n",
       "4   A5   赵恒    104  260 2018-08-12     3\n",
       "5   A6   王丹    105  280 2018-08-12     4"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "0    1.5\n",
       "1    3.5\n",
       "2    1.5\n",
       "3    3.5\n",
       "4    5.0\n",
       "5    6.0\n",
       "Name: 销售ID, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "0    1.0\n",
       "1    3.0\n",
       "2    2.0\n",
       "3    4.0\n",
       "4    5.0\n",
       "5    6.0\n",
       "Name: 销售ID, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "0    1.0\n",
       "1    3.0\n",
       "2    1.0\n",
       "3    3.0\n",
       "4    5.0\n",
       "5    6.0\n",
       "Name: 销售ID, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "0    2.0\n",
       "1    4.0\n",
       "2    2.0\n",
       "3    4.0\n",
       "4    5.0\n",
       "5    6.0\n",
       "Name: 销售ID, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('./data/sort_one.xlsx')\n",
    "df\n",
    "df['销售ID'].rank(method='average')\n",
    "df['销售ID'].rank(method='first')\n",
    "df['销售ID'].rank(method='min')\n",
    "df['销售ID'].rank(method='max')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 四、数值删除\n",
    "### 4.1 删除列\n",
    "**drop()方法**\n",
    "\n",
    "* axis=1，表示删除列\n",
    "* axis=0，表示删除行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄\n",
       "0   A1  张 通    101   31\n",
       "1   A2   李谷    102   45\n",
       "2   A3   孙凤    103   23\n",
       "3   A4   赵恒    104  240\n",
       "4   A5   赵恒    104  260\n",
       "5   A6   王丹    105  280"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop(['销售ID', '成交时间'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄\n",
       "0   A1  张 通    101   31\n",
       "1   A2   李谷    102   45\n",
       "2   A3   孙凤    103   23\n",
       "3   A4   赵恒    104  240\n",
       "4   A5   赵恒    104  260\n",
       "5   A6   王丹    105  280"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 直接传入待删除列的位置，也需要axis参数\n",
    "df.drop(df.columns[[4, 5]], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄\n",
       "0   A1  张 通    101   31\n",
       "1   A2   李谷    102   45\n",
       "2   A3   孙凤    103   23\n",
       "3   A4   赵恒    104  240\n",
       "4   A5   赵恒    104  260\n",
       "5   A6   王丹    105  280"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 列名以列表的形式传递给columns参数，此时不需要axis参数了\n",
    "df.drop(columns=['销售ID', '成交时间'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 删除行\n",
    "**drop()**函数指明axis=0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "2   A3   孙凤    103   23 2018-08-10     1\n",
       "3   A4   赵恒    104  240 2018-08-11     2\n",
       "4   A5   赵恒    104  260 2018-08-12     3\n",
       "5   A6   王丹    105  280 2018-08-12     4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "2   A3   孙凤    103   23 2018-08-10     1\n",
       "3   A4   赵恒    104  240 2018-08-11     2\n",
       "4   A5   赵恒    104  260 2018-08-12     3\n",
       "5   A6   王丹    105  280 2018-08-12     4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop([0, 1], axis=0)\n",
    "# index获取行号，需要传axis=0\n",
    "df.drop(df.index[[0, 1]], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "2   A3   孙凤    103   23 2018-08-10     1\n",
       "3   A4   赵恒    104  240 2018-08-11     2\n",
       "4   A5   赵恒    104  260 2018-08-12     3\n",
       "5   A6   王丹    105  280 2018-08-12     4"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将行名传递给index参数，可以不用传递axis\n",
    "df.drop(index = [0, 1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3 删除特定行\n",
    "删除满足某个条件的行，《淘米洗菜-数据预处理》中异常值处理算是删除特定行\n",
    "\n",
    "Excel实现：先筛选，后删除\n",
    "\n",
    "Python实现：不直接删除满足条件的值，而是把不满足条件的值筛选处理作为新的数据源，即将要删除的行过滤掉"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄       成交时间  销售ID\n",
       "0   A1  张 通    101  31 2018-08-08     1\n",
       "2   A3   孙凤    103  23 2018-08-10     1"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 删除年龄大于等于40的行，即筛选小于40的行\n",
    "df[df['年龄'] < 40]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 五、数值计数\n",
    "计算某个值在一系列数值中出现的次数\n",
    "\n",
    "Excel实现：**COUNTIF函数**：计算某个区域中满足给定条件的单元格数目\n",
    "\n",
    "**COUNTIF(range, criteria)**\n",
    "\n",
    "* range：一系列值的范围\n",
    "* criteria：表示某个值或某一条件\n",
    "\n",
    "如下图：销售ID在F2在F2:F6中出现两次，以此类推\n",
    "\n",
    "<img src='./image/countif.jpg'/>\n",
    "\n",
    "Python实现：**value_counts()**方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "0   A1  张 通    101   31 2018-08-08     1\n",
       "1   A2   李谷    102   45 2018-08-09     2\n",
       "2   A3   孙凤    103   23 2018-08-10     1\n",
       "3   A4   赵恒    104  240 2018-08-11     2\n",
       "4   A5   赵恒    104  260 2018-08-12     3\n",
       "5   A6   王丹    105  280 2018-08-12     4"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "2    2\n",
       "1    2\n",
       "4    1\n",
       "3    1\n",
       "Name: 销售ID, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df\n",
    "df['销售ID'].value_counts()\n",
    "# 结果按计数值降序排列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2    0.333333\n",
       "1    0.333333\n",
       "4    0.166667\n",
       "3    0.166667\n",
       "Name: 销售ID, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看不同值出现的占比\n",
    "df['销售ID'].value_counts(normalize=True)\n",
    "# 结果按计数值降序排列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    0.333333\n",
       "2    0.333333\n",
       "3    0.166667\n",
       "4    0.166667\n",
       "Name: 销售ID, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 不按计数值降序排列，按计数对象排序，设置sort=False\n",
    "df['销售ID'].value_counts(normalize=True, sort=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 六、唯一值获取\n",
    "把一系列的值删除重复项以后的结果\n",
    "\n",
    "Excel实现：将某一列复制粘贴出来，删除重复项，剩下就是唯一值了\n",
    "\n",
    "Python实现：（1）删除重复值，和Excel一致（2）**unique()**方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 2, 3, 4])"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['销售ID'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 七、数值查找\n",
    "**isin()**方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1    False\n",
       "2     True\n",
       "3    False\n",
       "4    False\n",
       "5    False\n",
       "Name: 年龄, dtype: bool"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    订单编号   客户姓名  唯一识别码     年龄   成交时间   销售ID\n",
       "0  False  False  False   True  False  False\n",
       "1   True  False  False  False  False  False\n",
       "2  False  False  False  False  False  False\n",
       "3  False  False  False  False  False  False\n",
       "4  False  False  False  False  False  False\n",
       "5  False  False  False  False  False  False"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 某列查询\n",
    "df['年龄'].isin([31, 23])\n",
    "# 全表查询\n",
    "df.isin(['A2', 31])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 八、区间切分\n",
    "将一系列数值分成若干份，比如：现在有10个人，你要根据这10个人的年龄将他们分成三组，这个切分过程就称为**区间切分**。\n",
    "\n",
    "Excel实现：if函数\n",
    "\n",
    "=IF(D2<4, \"<4\", IF(D2<7, \"4-6\", \">=7\"))\n",
    "\n",
    "Python实现：cut()方法，bins参数指明切分区间。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      (3, 50]\n",
       "1      (3, 50]\n",
       "2      (3, 50]\n",
       "3    (50, 280]\n",
       "4    (50, 280]\n",
       "5    (50, 280]\n",
       "Name: 年龄, dtype: category\n",
       "Categories (3, interval[int64]): [(0, 3] < (3, 50] < (50, 280]]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "0    (22.743, 108.667]\n",
       "1    (22.743, 108.667]\n",
       "2    (22.743, 108.667]\n",
       "3     (194.333, 280.0]\n",
       "4     (194.333, 280.0]\n",
       "5     (194.333, 280.0]\n",
       "Name: 年龄, dtype: category\n",
       "Categories (3, interval[float64]): [(22.743, 108.667] < (108.667, 194.333] < (194.333, 280.0]]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.cut(df['年龄'], bins=[0, 3, 50, 280])\n",
    "pd.cut(df['年龄'], 3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     (22.999, 40.333]\n",
       "1    (40.333, 246.667]\n",
       "2     (22.999, 40.333]\n",
       "3    (40.333, 246.667]\n",
       "4     (246.667, 280.0]\n",
       "5     (246.667, 280.0]\n",
       "Name: 年龄, dtype: category\n",
       "Categories (3, interval[float64]): [(22.999, 40.333] < (40.333, 246.667] < (246.667, 280.0]]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# qcut\n",
    "pd.qcut(df['年龄'], 3)\n",
    "# 在数据分布比较均匀的情况下，cut()和qcut()方法得到的区间基本一致。\n",
    "# 当数据分布不均匀时，即方差比较大时，两者得到的区间的偏差就会比较大。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 九、插入新的行或列\n",
    "在特定的位置插入行或列\n",
    "\n",
    "Python实现\n",
    "\n",
    "* 将插入的行当作一个新的表，将两个表在纵轴方向上进行拼接\n",
    "* insert(插入位置，插入后新的列名，插入的数据)方法"
   ]
  },
  {
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>商品类别</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>cat01</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>cat02</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>cat03</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>cat04</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>cat05</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>cat06</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名   商品类别  唯一识别码   年龄       成交时间  销售ID\n",
       "0   A1  张 通  cat01    101   31 2018-08-08     1\n",
       "1   A2   李谷  cat02    102   45 2018-08-09     2\n",
       "2   A3   孙凤  cat03    103   23 2018-08-10     1\n",
       "3   A4   赵恒  cat04    104  240 2018-08-11     2\n",
       "4   A5   赵恒  cat05    104  260 2018-08-12     3\n",
       "5   A6   王丹  cat06    105  280 2018-08-12     4"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df = df.drop(['商品类别'], axis=1)\n",
    "df.insert(2, '商品类别', ['cat01', 'cat02', 'cat03', 'cat04', 'cat05', 'cat06'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>商品类别</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>cat01</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>cat02</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>cat03</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>cat04</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>cat05</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>cat07</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名   商品类别  唯一识别码   年龄       成交时间  销售ID\n",
       "0   A1  张 通  cat01    101   31 2018-08-08     1\n",
       "1   A2   李谷  cat02    102   45 2018-08-09     2\n",
       "2   A3   孙凤  cat03    103   23 2018-08-10     1\n",
       "3   A4   赵恒  cat04    104  240 2018-08-11     2\n",
       "4   A5   赵恒  cat05    104  260 2018-08-12     3\n",
       "5   A6   王丹  cat07    105  280 2018-08-12     4"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['商品类别'] = ['cat01', 'cat02', 'cat03', 'cat04', 'cat05', 'cat07']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 十、行列互换\n",
    "行列互换（转置）\n",
    "\n",
    "<img src=\"./image/rcT.jpg\" width=\"60%\" />"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>订单编号</td>\n",
       "      <td>A1</td>\n",
       "      <td>A2</td>\n",
       "      <td>A3</td>\n",
       "      <td>A4</td>\n",
       "      <td>A5</td>\n",
       "      <td>A6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>客户姓名</td>\n",
       "      <td>张 通</td>\n",
       "      <td>李谷</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>王丹</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>商品类别</td>\n",
       "      <td>cat01</td>\n",
       "      <td>cat02</td>\n",
       "      <td>cat03</td>\n",
       "      <td>cat04</td>\n",
       "      <td>cat05</td>\n",
       "      <td>cat07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>唯一识别码</td>\n",
       "      <td>101</td>\n",
       "      <td>102</td>\n",
       "      <td>103</td>\n",
       "      <td>104</td>\n",
       "      <td>104</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>年龄</td>\n",
       "      <td>31</td>\n",
       "      <td>45</td>\n",
       "      <td>23</td>\n",
       "      <td>240</td>\n",
       "      <td>260</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>成交时间</td>\n",
       "      <td>2018-08-08 00:00:00</td>\n",
       "      <td>2018-08-09 00:00:00</td>\n",
       "      <td>2018-08-10 00:00:00</td>\n",
       "      <td>2018-08-11 00:00:00</td>\n",
       "      <td>2018-08-12 00:00:00</td>\n",
       "      <td>2018-08-12 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>销售ID</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         0                    1                    2  \\\n",
       "订单编号                    A1                   A2                   A3   \n",
       "客户姓名                   张 通                   李谷                   孙凤   \n",
       "商品类别                 cat01                cat02                cat03   \n",
       "唯一识别码                  101                  102                  103   \n",
       "年龄                      31                   45                   23   \n",
       "成交时间   2018-08-08 00:00:00  2018-08-09 00:00:00  2018-08-10 00:00:00   \n",
       "销售ID                     1                    2                    1   \n",
       "\n",
       "                         3                    4                    5  \n",
       "订单编号                    A4                   A5                   A6  \n",
       "客户姓名                    赵恒                   赵恒                   王丹  \n",
       "商品类别                 cat04                cat05                cat07  \n",
       "唯一识别码                  104                  104                  105  \n",
       "年龄                     240                  260                  280  \n",
       "成交时间   2018-08-11 00:00:00  2018-08-12 00:00:00  2018-08-12 00:00:00  \n",
       "销售ID                     2                    3                    4  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>商品类别</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>cat01</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>cat02</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>cat03</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>cat04</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>cat05</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>cat07</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名   商品类别 唯一识别码   年龄       成交时间 销售ID\n",
       "0   A1  张 通  cat01   101   31 2018-08-08    1\n",
       "1   A2   李谷  cat02   102   45 2018-08-09    2\n",
       "2   A3   孙凤  cat03   103   23 2018-08-10    1\n",
       "3   A4   赵恒  cat04   104  240 2018-08-11    2\n",
       "4   A5   赵恒  cat05   104  260 2018-08-12    3\n",
       "5   A6   王丹  cat07   105  280 2018-08-12    4"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.T\n",
    "df.T.T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 十一、索引重塑\n",
    "索引重塑就是要将原来的索引进行重新构造\n",
    "\n",
    "<img src=\"./image/dataframe.jpg\" width=\"60%\" />\n",
    "\n",
    "树形结构\n",
    "\n",
    "<img src=\"./image/tree.jpg\" height=\"60%\" />\n",
    "\n",
    "树形结构就是在维持表格型索引不变的情况下，把列索引也变成行索引，给表格建立层次化索引\n",
    "\n",
    "将表格型数据转换到树形数据的过程叫重塑。\n",
    "\n",
    "**stack()**方法，对应的方法**unstack()**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>C1</th>\n",
       "      <th>C2</th>\n",
       "      <th>C3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>S1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>S2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C1  C2  C3\n",
       "S1   1   2   3\n",
       "S2   4   5   6"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "S1  C1    1\n",
       "    C2    2\n",
       "    C3    3\n",
       "S2  C1    4\n",
       "    C2    5\n",
       "    C3    6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>C1</th>\n",
       "      <th>C2</th>\n",
       "      <th>C3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>S1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>S2</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C1  C2  C3\n",
       "S1   1   2   3\n",
       "S2   4   5   6"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame([[1, 2, 3], [4, 5, 6]], columns=['C1', 'C2', 'C3'], index=['S1', 'S2'])\n",
    "df2\n",
    "df2.stack()\n",
    "df2.stack().unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 十二、长宽表转换\n",
    "长宽表转换就是将比较长的表（很多行）的表转换为比较宽（很多列）的表，或者比较宽的表转化为比较长的表。\n",
    "\n",
    "<img src=\"./image/width_table.jpg\" height=\"80%\" />\n",
    "\n",
    "### 12.1 宽表转换为长表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Company</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sale2013</th>\n",
       "      <th>Sale2014</th>\n",
       "      <th>Sale2015</th>\n",
       "      <th>Sale2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>5000</td>\n",
       "      <td>5050</td>\n",
       "      <td>5050</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>3500</td>\n",
       "      <td>3800</td>\n",
       "      <td>3800</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>2300</td>\n",
       "      <td>2900</td>\n",
       "      <td>2900</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Company Name  Sale2013  Sale2014  Sale2015  Sale2016\n",
       "0     Apple   苹果      5000      5050      5050      5050\n",
       "1    Google   谷歌      3500      3800      3800      3800\n",
       "2  Facebook   脸书      2300      2900      2900      2900"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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></th>\n",
       "      <th>Sale2013</th>\n",
       "      <th>Sale2014</th>\n",
       "      <th>Sale2015</th>\n",
       "      <th>Sale2016</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company</th>\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",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>5000</td>\n",
       "      <td>5050</td>\n",
       "      <td>5050</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>3500</td>\n",
       "      <td>3800</td>\n",
       "      <td>3800</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>2300</td>\n",
       "      <td>2900</td>\n",
       "      <td>2900</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Sale2013  Sale2014  Sale2015  Sale2016\n",
       "Company  Name                                        \n",
       "Apple    苹果        5000      5050      5050      5050\n",
       "Google   谷歌        3500      3800      3800      3800\n",
       "Facebook 脸书        2300      2900      2900      2900"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('./data/stack.xlsx')\n",
    "df\n",
    "df.set_index(['Company', 'Name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Company   Name          \n",
       "Apple     苹果    Sale2013    5000\n",
       "                Sale2014    5050\n",
       "                Sale2015    5050\n",
       "                Sale2016    5050\n",
       "Google    谷歌    Sale2013    3500\n",
       "                Sale2014    3800\n",
       "                Sale2015    3800\n",
       "                Sale2016    3800\n",
       "Facebook  脸书    Sale2013    2300\n",
       "                Sale2014    2900\n",
       "                Sale2015    2900\n",
       "                Sale2016    2900\n",
       "dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. stack方法实现\n",
    "df.set_index(['Company', 'Name']).stack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>Company</th>\n",
       "      <th>Name</th>\n",
       "      <th>level_2</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>3500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Company Name   level_2     0\n",
       "0      Apple   苹果  Sale2013  5000\n",
       "1      Apple   苹果  Sale2014  5050\n",
       "2      Apple   苹果  Sale2015  5050\n",
       "3      Apple   苹果  Sale2016  5050\n",
       "4     Google   谷歌  Sale2013  3500\n",
       "5     Google   谷歌  Sale2014  3800\n",
       "6     Google   谷歌  Sale2015  3800\n",
       "7     Google   谷歌  Sale2016  3800\n",
       "8   Facebook   脸书  Sale2013  2300\n",
       "9   Facebook   脸书  Sale2014  2900\n",
       "10  Facebook   脸书  Sale2015  2900\n",
       "11  Facebook   脸书  Sale2016  2900"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index(['Company', 'Name']).stack().reset_index()\n",
    "# reset_index：重置索引，将索引列当作一个columns进行返回，变成常规的两列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>Company</th>\n",
       "      <th>Name</th>\n",
       "      <th>Year</th>\n",
       "      <th>Sale</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>3500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2013</td>\n",
       "      <td>2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2014</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2015</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>Sale2016</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Company Name      Year  Sale\n",
       "0      Apple   苹果  Sale2013  5000\n",
       "1     Google   谷歌  Sale2013  3500\n",
       "2   Facebook   脸书  Sale2013  2300\n",
       "3      Apple   苹果  Sale2014  5050\n",
       "4     Google   谷歌  Sale2014  3800\n",
       "5   Facebook   脸书  Sale2014  2900\n",
       "6      Apple   苹果  Sale2015  5050\n",
       "7     Google   谷歌  Sale2015  3800\n",
       "8   Facebook   脸书  Sale2015  2900\n",
       "9      Apple   苹果  Sale2016  5050\n",
       "10    Google   谷歌  Sale2016  3800\n",
       "11  Facebook   脸书  Sale2016  2900"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2. melt方法实现\n",
    "df = df.melt(id_vars=['Company', 'Name'], var_name='Year', value_name='Sale')\n",
    "df\n",
    "# id_vars参数：指明宽表转换为长表时保持不变的列\n",
    "# var_name参数：原来的列索引转换成“行索引”以后对应的列名\n",
    "# value_name参数：新索引对应值的列名"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 12.2 长表转换为宽表\n",
    "宽表转换为长表的逆过程，常用的方法是**数据透视表**。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>Year</th>\n",
       "      <th>Sale2013</th>\n",
       "      <th>Sale2014</th>\n",
       "      <th>Sale2015</th>\n",
       "      <th>Sale2016</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Company</th>\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",
       "      <td>Apple</td>\n",
       "      <td>苹果</td>\n",
       "      <td>5000</td>\n",
       "      <td>5050</td>\n",
       "      <td>5050</td>\n",
       "      <td>5050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Facebook</td>\n",
       "      <td>脸书</td>\n",
       "      <td>2300</td>\n",
       "      <td>2900</td>\n",
       "      <td>2900</td>\n",
       "      <td>2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Google</td>\n",
       "      <td>谷歌</td>\n",
       "      <td>3500</td>\n",
       "      <td>3800</td>\n",
       "      <td>3800</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Year           Sale2013  Sale2014  Sale2015  Sale2016\n",
       "Company  Name                                        \n",
       "Apple    苹果        5000      5050      5050      5050\n",
       "Facebook 脸书        2300      2900      2900      2900\n",
       "Google   谷歌        3500      3800      3800      3800"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# index行索引，columns列索引，values值\n",
    "df.pivot_table(index = ['Company', 'Name'], columns='Year', values='Sale')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 十三、apply()与applymap()函数\n",
    "Python中的map函数：对一个序列中的所有元素执行相同的函数操作。\n",
    "\n",
    "DataFrame中和map相似的函数：（1）**apply()函数** （2）**applaymap()函数**\n",
    "\n",
    "都需要和匿名函数**lambda**结合使用\n",
    "\n",
    "**apply()**：对DataFrame中的某一column或row的元素执行相同的操作\n",
    "\n",
    "**applymap()**：对DataFrame中的每一个元素执行相同的函数操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>C1</th>\n",
       "      <th>C2</th>\n",
       "      <th>C3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   C1  C2  C3\n",
       "0   0   1   2\n",
       "1   3   4   5\n",
       "2   6   7   8"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>C1</th>\n",
       "      <th>C2</th>\n",
       "      <th>C3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   C1  C2  C3\n",
       "0   1   2   3\n",
       "1   4   5   6\n",
       "2   7   8   9"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([[2, 3, 4], [5, 6, 7], [8, 9, 10]], columns=['C1', 'C2', 'C3'])\n",
    "df = pd.DataFrame(np.arange(9).reshape(3, 3), columns=['C1', 'C2', 'C3'])\n",
    "df\n",
    "df.applymap(lambda x: x+1)"
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}

一键复制编辑原始数据按行查看历史

提交于 2020-03-04 10:58 . chapter06-炒菜计时器-时间序列，格式调整

https://gitee.com/twofloor/Excel_to_Python.git

git@gitee.com:twofloor/Excel_to_Python.git

twofloor

Excel_to_Python

master

silianpan / Excel_to_Python

简介

发行版

贡献者

近期动态

silianpan / Excel_to_Python .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

silianpan / Excel_to_Python