learn_pandas_20200909_vlookup.ipynb

{
 "metadata": {
  "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-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python37464bit996bc1b053e443969e72b61be9e433e9",
   "display_name": "Python 3.7.4 64-bit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "# 读取学生信息表\n",
    "df_grade = pd.read_excel('./course_datas/c23_excel_vlookup/学生成绩表.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "    班级    学号  语文成绩  数学成绩  英语成绩\n0  C01  S001    99    84    88\n1  C01  S002    66    95    77\n2  C01  S003    68    68    61\n3  C01  S004    63    66    82\n4  C01  S005    72    95    94",
      "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      <th>0</th>\n      <td>C01</td>\n      <td>S001</td>\n      <td>99</td>\n      <td>84</td>\n      <td>88</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>C01</td>\n      <td>S002</td>\n      <td>66</td>\n      <td>95</td>\n      <td>77</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>C01</td>\n      <td>S003</td>\n      <td>68</td>\n      <td>68</td>\n      <td>61</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>C01</td>\n      <td>S004</td>\n      <td>63</td>\n      <td>66</td>\n      <td>82</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>C01</td>\n      <td>S005</td>\n      <td>72</td>\n      <td>95</td>\n      <td>94</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "source": [
    "df_grade.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取学生成绩表\n",
    "df_sinfo = pd.read_excel('./course_datas/c23_excel_vlookup/学生信息表.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "     学号  姓名 性别  年龄  籍贯\n0  S001  怠涵  女  23  山东\n1  S002  婉清  女  25  河南\n2  S003  溪榕  女  23  湖北\n3  S004  漠涓  女  19  陕西\n4  S005  祈博  女  24  山东",
      "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      <th>0</th>\n      <td>S001</td>\n      <td>怠涵</td>\n      <td>女</td>\n      <td>23</td>\n      <td>山东</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>S002</td>\n      <td>婉清</td>\n      <td>女</td>\n      <td>25</td>\n      <td>河南</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>S003</td>\n      <td>溪榕</td>\n      <td>女</td>\n      <td>23</td>\n      <td>湖北</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>S004</td>\n      <td>漠涓</td>\n      <td>女</td>\n      <td>19</td>\n      <td>陕西</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>S005</td>\n      <td>祈博</td>\n      <td>女</td>\n      <td>24</td>\n      <td>山东</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "df_sinfo.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "     学号  姓名 性别\n0  S001  怠涵  女\n1  S002  婉清  女\n2  S003  溪榕  女\n3  S004  漠涓  女\n4  S005  祈博  女",
      "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    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>S001</td>\n      <td>怠涵</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>S002</td>\n      <td>婉清</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>S003</td>\n      <td>溪榕</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>S004</td>\n      <td>漠涓</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>S005</td>\n      <td>祈博</td>\n      <td>女</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "df_sinfo = df_sinfo[['学号', '姓名', '性别']]\n",
    "df_sinfo.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "    班级    学号  语文成绩  数学成绩  英语成绩  姓名 性别\n0  C01  S001    99    84    88  怠涵  女\n1  C01  S002    66    95    77  婉清  女\n2  C01  S003    68    68    61  溪榕  女\n3  C01  S004    63    66    82  漠涓  女\n4  C01  S005    72    95    94  祈博  女",
      "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>性别</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>C01</td>\n      <td>S001</td>\n      <td>99</td>\n      <td>84</td>\n      <td>88</td>\n      <td>怠涵</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>C01</td>\n      <td>S002</td>\n      <td>66</td>\n      <td>95</td>\n      <td>77</td>\n      <td>婉清</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>C01</td>\n      <td>S003</td>\n      <td>68</td>\n      <td>68</td>\n      <td>61</td>\n      <td>溪榕</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>C01</td>\n      <td>S004</td>\n      <td>63</td>\n      <td>66</td>\n      <td>82</td>\n      <td>漠涓</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>C01</td>\n      <td>S005</td>\n      <td>72</td>\n      <td>95</td>\n      <td>94</td>\n      <td>祈博</td>\n      <td>女</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 24
    }
   ],
   "source": [
    "df_merge = pd.merge(left = df_grade, right = df_sinfo, left_on = '学号', right_on = '学号')\n",
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 如何调整列表的顺序？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_columns = df_merge.columns.to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "['班级', '学号', '语文成绩', '数学成绩', '英语成绩', '姓名', '性别']"
     },
     "metadata": {},
     "execution_count": 26
    }
   ],
   "source": [
    "new_columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 把'姓名'、'性别'两列放在'学号'的后面"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "for name in ['姓名', '性别'][::-1]:\n",
    "    new_columns.remove(name)\n",
    "    new_columns.insert(new_columns.index('学号')+1, name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "['班级', '学号', '姓名', '性别', '语文成绩', '数学成绩', '英语成绩']"
     },
     "metadata": {},
     "execution_count": 28
    }
   ],
   "source": [
    "new_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "    班级    学号  语文成绩  数学成绩  英语成绩  姓名 性别\n0  C01  S001    99    84    88  怠涵  女\n1  C01  S002    66    95    77  婉清  女\n2  C01  S003    68    68    61  溪榕  女\n3  C01  S004    63    66    82  漠涓  女\n4  C01  S005    72    95    94  祈博  女",
      "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>性别</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>C01</td>\n      <td>S001</td>\n      <td>99</td>\n      <td>84</td>\n      <td>88</td>\n      <td>怠涵</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>C01</td>\n      <td>S002</td>\n      <td>66</td>\n      <td>95</td>\n      <td>77</td>\n      <td>婉清</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>C01</td>\n      <td>S003</td>\n      <td>68</td>\n      <td>68</td>\n      <td>61</td>\n      <td>溪榕</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>C01</td>\n      <td>S004</td>\n      <td>63</td>\n      <td>66</td>\n      <td>82</td>\n      <td>漠涓</td>\n      <td>女</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>C01</td>\n      <td>S005</td>\n      <td>72</td>\n      <td>95</td>\n      <td>94</td>\n      <td>祈博</td>\n      <td>女</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "source": [
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge = df_merge.reindex(columns = new_columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "    班级    学号  姓名 性别  语文成绩  数学成绩  英语成绩\n0  C01  S001  怠涵  女    99    84    88\n1  C01  S002  婉清  女    66    95    77\n2  C01  S003  溪榕  女    68    68    61\n3  C01  S004  漠涓  女    63    66    82\n4  C01  S005  祈博  女    72    95    94",
      "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>英语成绩</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>C01</td>\n      <td>S001</td>\n      <td>怠涵</td>\n      <td>女</td>\n      <td>99</td>\n      <td>84</td>\n      <td>88</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>C01</td>\n      <td>S002</td>\n      <td>婉清</td>\n      <td>女</td>\n      <td>66</td>\n      <td>95</td>\n      <td>77</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>C01</td>\n      <td>S003</td>\n      <td>溪榕</td>\n      <td>女</td>\n      <td>68</td>\n      <td>68</td>\n      <td>61</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>C01</td>\n      <td>S004</td>\n      <td>漠涓</td>\n      <td>女</td>\n      <td>63</td>\n      <td>66</td>\n      <td>82</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>C01</td>\n      <td>S005</td>\n      <td>祈博</td>\n      <td>女</td>\n      <td>72</td>\n      <td>95</td>\n      <td>94</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 31
    }
   ],
   "source": [
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ]
}