23. Pandas怎样实现Excel的vlookup并且在指定列后面输出？.ipynb · 654036653/ant-learn-pandas

加入 Gitee

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

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

克隆/下载

23. Pandas怎样实现Excel的vlookup并且在指定列后面输出？.ipynb 17.81 KB

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas怎样实现Excel的vlookup并且在指定列后面输出？\n",
    "\n",
    "背景：  \n",
    "1. 有两个excel，他们有相同的一个列；  \n",
    "2. 按照这个列合并成一个大的excel，即vlookup功能，要求：  \n",
    "    * 只需要第二个excel的少量的列，比如从40个列中挑选2个列  \n",
    "    * 新增的来自第二个excel的列需要放到第一个excel指定的列后面；  \n",
    "3. 将结果输出到一个新的excel;\n",
    "\n",
    "微信公众号：蚂蚁学Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤1：读取两个数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "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>C01</td>\n",
       "      <td>S001</td>\n",
       "      <td>99</td>\n",
       "      <td>84</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\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",
       "      <td>2</td>\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",
       "      <td>3</td>\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",
       "      <td>4</td>\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>"
      ],
      "text/plain": [
       "    班级    学号  语文成绩  数学成绩  英语成绩\n",
       "0  C01  S001    99    84    88\n",
       "1  C01  S002    66    95    77\n",
       "2  C01  S003    68    68    61\n",
       "3  C01  S004    63    66    82\n",
       "4  C01  S005    72    95    94"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 学生成绩表\n",
    "df_grade = pd.read_excel(\"./course_datas/c23_excel_vlookup/学生成绩表.xlsx\") \n",
    "df_grade.head()"
   ]
  },
  {
   "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>S001</td>\n",
       "      <td>怠涵</td>\n",
       "      <td>女</td>\n",
       "      <td>23</td>\n",
       "      <td>山东</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>S002</td>\n",
       "      <td>婉清</td>\n",
       "      <td>女</td>\n",
       "      <td>25</td>\n",
       "      <td>河南</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>S003</td>\n",
       "      <td>溪榕</td>\n",
       "      <td>女</td>\n",
       "      <td>23</td>\n",
       "      <td>湖北</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>S004</td>\n",
       "      <td>漠涓</td>\n",
       "      <td>女</td>\n",
       "      <td>19</td>\n",
       "      <td>陕西</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\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>"
      ],
      "text/plain": [
       "     学号  姓名 性别  年龄  籍贯\n",
       "0  S001  怠涵  女  23  山东\n",
       "1  S002  婉清  女  25  河南\n",
       "2  S003  溪榕  女  23  湖北\n",
       "3  S004  漠涓  女  19  陕西\n",
       "4  S005  祈博  女  24  山东"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 学生信息表\n",
    "df_sinfo = pd.read_excel(\"./course_datas/c23_excel_vlookup/学生信息表.xlsx\") \n",
    "df_sinfo.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***目标：怎样将第二个“学生信息表”的姓名、性别两列，添加到第一个表“学生成绩表”，并且放在第一个表的“学号”列后面？***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤2：实现两个表的关联\n",
    "\n",
    "即excel的vloopup功能"
   ]
  },
  {
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>S001</td>\n",
       "      <td>怠涵</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>S002</td>\n",
       "      <td>婉清</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>S003</td>\n",
       "      <td>溪榕</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>S004</td>\n",
       "      <td>漠涓</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>S005</td>\n",
       "      <td>祈博</td>\n",
       "      <td>女</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     学号  姓名 性别\n",
       "0  S001  怠涵  女\n",
       "1  S002  婉清  女\n",
       "2  S003  溪榕  女\n",
       "3  S004  漠涓  女\n",
       "4  S005  祈博  女"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 只筛选第二个表的少量的列\n",
    "df_sinfo = df_sinfo[[\"学号\", \"姓名\", \"性别\"]]\n",
    "df_sinfo.head()"
   ]
  },
  {
   "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",
       "      <th>姓名</th>\n",
       "      <th>性别</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\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",
       "      <td>1</td>\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",
       "      <td>2</td>\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",
       "      <td>3</td>\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",
       "      <td>4</td>\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>"
      ],
      "text/plain": [
       "    班级    学号  语文成绩  数学成绩  英语成绩  姓名 性别\n",
       "0  C01  S001    99    84    88  怠涵  女\n",
       "1  C01  S002    66    95    77  婉清  女\n",
       "2  C01  S003    68    68    61  溪榕  女\n",
       "3  C01  S004    63    66    82  漠涓  女\n",
       "4  C01  S005    72    95    94  祈博  女"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge = pd.merge(left=df_grade, right=df_sinfo, left_on=\"学号\", right_on=\"学号\")\n",
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤3：调整列的顺序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['班级', '学号', '语文成绩', '数学成绩', '英语成绩', '姓名', '性别'], dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 问题：怎样将'姓名', '性别'两列，放到'学号'的后面？\n",
    "\n",
    "接下来需要用Python的语法实现列表的处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['班级', '学号', '语文成绩', '数学成绩', '英语成绩', '姓名', '性别']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将columns变成python的列表形式\n",
    "new_columns = df_merge.columns.to_list()\n",
    "new_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按逆序insert，会将\"姓名\"，\"性别\"放到\"学号\"的后面\n",
    "for name in [\"姓名\", \"性别\"][::-1]:\n",
    "    new_columns.remove(name)\n",
    "    new_columns.insert(new_columns.index(\"学号\")+1, name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['班级', '学号', '姓名', '性别', '语文成绩', '数学成绩', '英语成绩']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_columns"
   ]
  },
  {
   "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",
       "      <th>语文成绩</th>\n",
       "      <th>数学成绩</th>\n",
       "      <th>英语成绩</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\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",
       "      <td>1</td>\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",
       "      <td>2</td>\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",
       "      <td>3</td>\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",
       "      <td>4</td>\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>"
      ],
      "text/plain": [
       "    班级    学号  姓名 性别  语文成绩  数学成绩  英语成绩\n",
       "0  C01  S001  怠涵  女    99    84    88\n",
       "1  C01  S002  婉清  女    66    95    77\n",
       "2  C01  S003  溪榕  女    68    68    61\n",
       "3  C01  S004  漠涓  女    63    66    82\n",
       "4  C01  S005  祈博  女    72    95    94"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge = df_merge.reindex(columns=new_columns)\n",
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 步骤4：输出最终的Excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge.to_excel(\"./course_datas/c23_excel_vlookup/合并后的数据表.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}

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

提交于 2019-12-04 07:48 . 'xx'

马建仓 AI 助手

尝试更多

代码解读

代码找茬

代码优化

https://gitee.com/umqll5/ant-learn-pandas.git

[email protected]:umqll5/ant-learn-pandas.git

umqll5

ant-learn-pandas

master

654036653/ant-learn-pandas

简介

发行版

贡献者

近期动态

654036653/ant-learn-pandas .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

654036653/ant-learn-pandas