45. Pandas实现模糊匹配Merge数据的方法.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas实现模糊匹配Merge数据的方法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 两份数据"
   ]
  },
  {
   "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>keyid</th>\n",
       "      <th>keyword</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>matplotlib</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>sklearn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   keyid     keyword\n",
       "0      0       numpy\n",
       "1      1      pandas\n",
       "2      2  matplotlib\n",
       "3      3     sklearn\n",
       "4      4  tensorflow"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 关键词数据\n",
    "df_keyword = pd.DataFrame({\n",
    "    \"keyid\": np.arange(5),\n",
    "    \"keyword\": [\"numpy\", \"pandas\", \"matplotlib\", \"sklearn\", \"tensorflow\"]\n",
    "})\n",
    "df_keyword"
   ]
  },
  {
   "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>senid</th>\n",
       "      <th>sentence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>怎样用Pandas实现数据的Merge？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>Python之Numpy详细教程</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>怎样使用Pandas批量拆分与合并Excel文件？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>怎样使用Pandas的map和apply函数？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14</td>\n",
       "      <td>深度学习及TensorFlow简介</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16</td>\n",
       "      <td>基于sklearn的一些机器学习的代码</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   senid                   sentence\n",
       "0     10       怎样用Pandas实现数据的Merge？\n",
       "1     11           Python之Numpy详细教程\n",
       "2     12  怎样使用Pandas批量拆分与合并Excel文件？\n",
       "3     13    怎样使用Pandas的map和apply函数？\n",
       "4     14          深度学习及TensorFlow简介\n",
       "5     15        Tensorflow和Numpy的关系\n",
       "6     16        基于sklearn的一些机器学习的代码"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 句子数据\n",
    "df_sentence = pd.DataFrame({\n",
    "    \"senid\": np.arange(10, 17),\n",
    "    \"sentence\": [\n",
    "        \"怎样用Pandas实现数据的Merge？\",\n",
    "        \"Python之Numpy详细教程\",\n",
    "        \"怎样使用Pandas批量拆分与合并Excel文件？\",\n",
    "        \"怎样使用Pandas的map和apply函数？\",\n",
    "        \"深度学习及TensorFlow简介\",\n",
    "        \"Tensorflow和Numpy的关系\",\n",
    "        \"基于sklearn的一些机器学习的代码\"\n",
    "    ]\n",
    "})\n",
    "df_sentence"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 方法1：暴力笛卡尔积 + 过滤"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 新增数字完全一样的列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_keyword[\"match\"] = 1\n",
    "df_sentence[\"match\"] = 1"
   ]
  },
  {
   "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>keyid</th>\n",
       "      <th>keyword</th>\n",
       "      <th>match</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>matplotlib</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>sklearn</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   keyid     keyword  match\n",
       "0      0       numpy      1\n",
       "1      1      pandas      1\n",
       "2      2  matplotlib      1\n",
       "3      3     sklearn      1\n",
       "4      4  tensorflow      1"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_keyword"
   ]
  },
  {
   "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>senid</th>\n",
       "      <th>sentence</th>\n",
       "      <th>match</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>怎样用Pandas实现数据的Merge？</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>Python之Numpy详细教程</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>怎样使用Pandas批量拆分与合并Excel文件？</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>怎样使用Pandas的map和apply函数？</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14</td>\n",
       "      <td>深度学习及TensorFlow简介</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16</td>\n",
       "      <td>基于sklearn的一些机器学习的代码</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   senid                   sentence  match\n",
       "0     10       怎样用Pandas实现数据的Merge？      1\n",
       "1     11           Python之Numpy详细教程      1\n",
       "2     12  怎样使用Pandas批量拆分与合并Excel文件？      1\n",
       "3     13    怎样使用Pandas的map和apply函数？      1\n",
       "4     14          深度学习及TensorFlow简介      1\n",
       "5     15        Tensorflow和Numpy的关系      1\n",
       "6     16        基于sklearn的一些机器学习的代码      1"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sentence"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 实现merge\n",
    "\n",
    "结果行数 = A表行数 * B表行数"
   ]
  },
  {
   "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>keyid</th>\n",
       "      <th>keyword</th>\n",
       "      <th>match</th>\n",
       "      <th>senid</th>\n",
       "      <th>sentence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>怎样用Pandas实现数据的Merge？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>Python之Numpy详细教程</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>怎样使用Pandas批量拆分与合并Excel文件？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>怎样使用Pandas的map和apply函数？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>深度学习及TensorFlow简介</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>基于sklearn的一些机器学习的代码</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>怎样用Pandas实现数据的Merge？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>Python之Numpy详细教程</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>怎样使用Pandas批量拆分与合并Excel文件？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>怎样使用Pandas的map和apply函数？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>深度学习及TensorFlow简介</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>基于sklearn的一些机器学习的代码</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2</td>\n",
       "      <td>matplotlib</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>怎样用Pandas实现数据的Merge？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2</td>\n",
       "      <td>matplotlib</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>Python之Numpy详细教程</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2</td>\n",
       "      <td>matplotlib</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>怎样使用Pandas批量拆分与合并Excel文件？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2</td>\n",
       "      <td>matplotlib</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>怎样使用Pandas的map和apply函数？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2</td>\n",
       "      <td>matplotlib</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>深度学习及TensorFlow简介</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2</td>\n",
       "      <td>matplotlib</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2</td>\n",
       "      <td>matplotlib</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>基于sklearn的一些机器学习的代码</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>3</td>\n",
       "      <td>sklearn</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>怎样用Pandas实现数据的Merge？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>3</td>\n",
       "      <td>sklearn</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>Python之Numpy详细教程</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>3</td>\n",
       "      <td>sklearn</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>怎样使用Pandas批量拆分与合并Excel文件？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>3</td>\n",
       "      <td>sklearn</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>怎样使用Pandas的map和apply函数？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>3</td>\n",
       "      <td>sklearn</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>深度学习及TensorFlow简介</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>3</td>\n",
       "      <td>sklearn</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>3</td>\n",
       "      <td>sklearn</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>基于sklearn的一些机器学习的代码</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>怎样用Pandas实现数据的Merge？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>Python之Numpy详细教程</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>怎样使用Pandas批量拆分与合并Excel文件？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>怎样使用Pandas的map和apply函数？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>深度学习及TensorFlow简介</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>基于sklearn的一些机器学习的代码</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    keyid     keyword  match  senid                   sentence\n",
       "0       0       numpy      1     10       怎样用Pandas实现数据的Merge？\n",
       "1       0       numpy      1     11           Python之Numpy详细教程\n",
       "2       0       numpy      1     12  怎样使用Pandas批量拆分与合并Excel文件？\n",
       "3       0       numpy      1     13    怎样使用Pandas的map和apply函数？\n",
       "4       0       numpy      1     14          深度学习及TensorFlow简介\n",
       "5       0       numpy      1     15        Tensorflow和Numpy的关系\n",
       "6       0       numpy      1     16        基于sklearn的一些机器学习的代码\n",
       "7       1      pandas      1     10       怎样用Pandas实现数据的Merge？\n",
       "8       1      pandas      1     11           Python之Numpy详细教程\n",
       "9       1      pandas      1     12  怎样使用Pandas批量拆分与合并Excel文件？\n",
       "10      1      pandas      1     13    怎样使用Pandas的map和apply函数？\n",
       "11      1      pandas      1     14          深度学习及TensorFlow简介\n",
       "12      1      pandas      1     15        Tensorflow和Numpy的关系\n",
       "13      1      pandas      1     16        基于sklearn的一些机器学习的代码\n",
       "14      2  matplotlib      1     10       怎样用Pandas实现数据的Merge？\n",
       "15      2  matplotlib      1     11           Python之Numpy详细教程\n",
       "16      2  matplotlib      1     12  怎样使用Pandas批量拆分与合并Excel文件？\n",
       "17      2  matplotlib      1     13    怎样使用Pandas的map和apply函数？\n",
       "18      2  matplotlib      1     14          深度学习及TensorFlow简介\n",
       "19      2  matplotlib      1     15        Tensorflow和Numpy的关系\n",
       "20      2  matplotlib      1     16        基于sklearn的一些机器学习的代码\n",
       "21      3     sklearn      1     10       怎样用Pandas实现数据的Merge？\n",
       "22      3     sklearn      1     11           Python之Numpy详细教程\n",
       "23      3     sklearn      1     12  怎样使用Pandas批量拆分与合并Excel文件？\n",
       "24      3     sklearn      1     13    怎样使用Pandas的map和apply函数？\n",
       "25      3     sklearn      1     14          深度学习及TensorFlow简介\n",
       "26      3     sklearn      1     15        Tensorflow和Numpy的关系\n",
       "27      3     sklearn      1     16        基于sklearn的一些机器学习的代码\n",
       "28      4  tensorflow      1     10       怎样用Pandas实现数据的Merge？\n",
       "29      4  tensorflow      1     11           Python之Numpy详细教程\n",
       "30      4  tensorflow      1     12  怎样使用Pandas批量拆分与合并Excel文件？\n",
       "31      4  tensorflow      1     13    怎样使用Pandas的map和apply函数？\n",
       "32      4  tensorflow      1     14          深度学习及TensorFlow简介\n",
       "33      4  tensorflow      1     15        Tensorflow和Numpy的关系\n",
       "34      4  tensorflow      1     16        基于sklearn的一些机器学习的代码"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge = pd.merge(df_keyword, df_sentence)\n",
    "df_merge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 过滤出结果"
   ]
  },
  {
   "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>keyid</th>\n",
       "      <th>keyword</th>\n",
       "      <th>match</th>\n",
       "      <th>senid</th>\n",
       "      <th>sentence</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>Python之Numpy详细教程</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>怎样用Pandas实现数据的Merge？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>怎样使用Pandas批量拆分与合并Excel文件？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>怎样使用Pandas的map和apply函数？</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>3</td>\n",
       "      <td>sklearn</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>基于sklearn的一些机器学习的代码</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>深度学习及TensorFlow简介</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    keyid     keyword  match  senid                   sentence\n",
       "1       0       numpy      1     11           Python之Numpy详细教程\n",
       "5       0       numpy      1     15        Tensorflow和Numpy的关系\n",
       "7       1      pandas      1     10       怎样用Pandas实现数据的Merge？\n",
       "9       1      pandas      1     12  怎样使用Pandas批量拆分与合并Excel文件？\n",
       "10      1      pandas      1     13    怎样使用Pandas的map和apply函数？\n",
       "27      3     sklearn      1     16        基于sklearn的一些机器学习的代码\n",
       "32      4  tensorflow      1     14          深度学习及TensorFlow简介\n",
       "33      4  tensorflow      1     15        Tensorflow和Numpy的关系"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def match_func(row):\n",
    "    return re.search(row[\"keyword\"], row[\"sentence\"], re.IGNORECASE) is not None\n",
    "\n",
    "df_merge[df_merge.apply(match_func, axis=1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 方法2：小表变字典做merge最后explode"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 构建要join的key:index的关系"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'numpy': 0, 'pandas': 1, 'matplotlib': 2, 'sklearn': 3, 'tensorflow': 4}"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "key_word_dict = {\n",
    "    row.keyword : row.keyid \n",
    "    for row in df_keyword.itertuples()\n",
    "}\n",
    "key_word_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 大表搜寻小表字典"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "def merge_func(row):\n",
    "    # 新增一列，表示能匹配的keyids\n",
    "    row[\"keyids\"] = [\n",
    "        keyid\n",
    "        for key_word, keyid in key_word_dict.items()\n",
    "        if re.search(key_word, row[\"sentence\"], re.IGNORECASE)\n",
    "    ]\n",
    "    return row\n",
    "\n",
    "df_merge = df_sentence.apply(merge_func, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>senid</th>\n",
       "      <th>sentence</th>\n",
       "      <th>match</th>\n",
       "      <th>keyids</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>怎样用Pandas实现数据的Merge？</td>\n",
       "      <td>1</td>\n",
       "      <td>[1]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>Python之Numpy详细教程</td>\n",
       "      <td>1</td>\n",
       "      <td>[0]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>怎样使用Pandas批量拆分与合并Excel文件？</td>\n",
       "      <td>1</td>\n",
       "      <td>[1]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>怎样使用Pandas的map和apply函数？</td>\n",
       "      <td>1</td>\n",
       "      <td>[1]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14</td>\n",
       "      <td>深度学习及TensorFlow简介</td>\n",
       "      <td>1</td>\n",
       "      <td>[4]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "      <td>1</td>\n",
       "      <td>[0, 4]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16</td>\n",
       "      <td>基于sklearn的一些机器学习的代码</td>\n",
       "      <td>1</td>\n",
       "      <td>[3]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   senid                   sentence  match  keyids\n",
       "0     10       怎样用Pandas实现数据的Merge？      1     [1]\n",
       "1     11           Python之Numpy详细教程      1     [0]\n",
       "2     12  怎样使用Pandas批量拆分与合并Excel文件？      1     [1]\n",
       "3     13    怎样使用Pandas的map和apply函数？      1     [1]\n",
       "4     14          深度学习及TensorFlow简介      1     [4]\n",
       "5     15        Tensorflow和Numpy的关系      1  [0, 4]\n",
       "6     16        基于sklearn的一些机器学习的代码      1     [3]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 展开然后做merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>senid</th>\n",
       "      <th>sentence</th>\n",
       "      <th>match</th>\n",
       "      <th>keyids</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>怎样用Pandas实现数据的Merge？</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>Python之Numpy详细教程</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>怎样使用Pandas批量拆分与合并Excel文件？</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>怎样使用Pandas的map和apply函数？</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14</td>\n",
       "      <td>深度学习及TensorFlow简介</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16</td>\n",
       "      <td>基于sklearn的一些机器学习的代码</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   senid                   sentence  match keyids\n",
       "0     10       怎样用Pandas实现数据的Merge？      1      1\n",
       "1     11           Python之Numpy详细教程      1      0\n",
       "2     12  怎样使用Pandas批量拆分与合并Excel文件？      1      1\n",
       "3     13    怎样使用Pandas的map和apply函数？      1      1\n",
       "4     14          深度学习及TensorFlow简介      1      4\n",
       "5     15        Tensorflow和Numpy的关系      1      0\n",
       "5     15        Tensorflow和Numpy的关系      1      4\n",
       "6     16        基于sklearn的一些机器学习的代码      1      3"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.explode(\"keyids\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>senid</th>\n",
       "      <th>sentence</th>\n",
       "      <th>match_x</th>\n",
       "      <th>keyids</th>\n",
       "      <th>keyid</th>\n",
       "      <th>keyword</th>\n",
       "      <th>match_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>怎样用Pandas实现数据的Merge？</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>怎样使用Pandas批量拆分与合并Excel文件？</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>怎样使用Pandas的map和apply函数？</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>pandas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>Python之Numpy详细教程</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>numpy</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>14</td>\n",
       "      <td>深度学习及TensorFlow简介</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>15</td>\n",
       "      <td>Tensorflow和Numpy的关系</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>tensorflow</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16</td>\n",
       "      <td>基于sklearn的一些机器学习的代码</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>sklearn</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   senid                   sentence  match_x keyids  keyid     keyword  \\\n",
       "0     10       怎样用Pandas实现数据的Merge？        1      1      1      pandas   \n",
       "1     12  怎样使用Pandas批量拆分与合并Excel文件？        1      1      1      pandas   \n",
       "2     13    怎样使用Pandas的map和apply函数？        1      1      1      pandas   \n",
       "3     11           Python之Numpy详细教程        1      0      0       numpy   \n",
       "4     15        Tensorflow和Numpy的关系        1      0      0       numpy   \n",
       "5     14          深度学习及TensorFlow简介        1      4      4  tensorflow   \n",
       "6     15        Tensorflow和Numpy的关系        1      4      4  tensorflow   \n",
       "7     16        基于sklearn的一些机器学习的代码        1      3      3     sklearn   \n",
       "\n",
       "   match_y  \n",
       "0        1  \n",
       "1        1  \n",
       "2        1  \n",
       "3        1  \n",
       "4        1  \n",
       "5        1  \n",
       "6        1  \n",
       "7        1  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_result = pd.merge(\n",
    "    left = df_merge.explode(\"keyids\"),\n",
    "    right = df_keyword,\n",
    "    left_on = \"keyids\",\n",
    "    right_on = \"keyid\"\n",
    ")\n",
    "df_result"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}