历史文章/「Python实用秘技15」pandas中基于范围条件进行表连接/「Python实用秘技15」pandas中基于范围条件进行表连接.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "75d9d9f5-4391-46b5-828e-2dd8e254361f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.5.2'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "daea1bb9-c962-4f18-a9d7-7af9e3d81acf",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 示例数据框\n",
    "demo_left = pd.DataFrame(\n",
    "    {\n",
    "        'left_id': ['a', 'b', 'c'],\n",
    "        'datetime': ['2023-01-01', '2023-02-01', '2023-03-01']\n",
    "    }\n",
    ")\n",
    "demo_right = pd.DataFrame(\n",
    "    {\n",
    "        'right_id': ['a', 'a', 'b', 'b'],\n",
    "        'datetime': ['2022-12-28', '2023-01-06', '2023-02-15', '2023-02-02']\n",
    "    }\n",
    ")\n",
    "demo_left['datetime'], demo_right['datetime'] = pd.to_datetime(demo_left['datetime']), pd.to_datetime(demo_right['datetime'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6441b59f-21df-4dc6-83ec-587c724de88c",
   "metadata": {
    "tags": []
   },
   "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>left_id</th>\n",
       "      <th>datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>2023-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>2023-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>2023-03-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  left_id   datetime\n",
       "0       a 2023-01-01\n",
       "1       b 2023-02-01\n",
       "2       c 2023-03-01"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demo_left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0ea8bd2d-7d39-41fb-9800-438b2f3de47f",
   "metadata": {
    "tags": []
   },
   "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>right_id</th>\n",
       "      <th>datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>2022-12-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>2023-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>2023-02-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>2023-02-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  right_id   datetime\n",
       "0        a 2022-12-28\n",
       "1        a 2023-01-06\n",
       "2        b 2023-02-15\n",
       "3        b 2023-02-02"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demo_right"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff150c29-ff72-40f3-8968-be9cc3750858",
   "metadata": {
    "tags": []
   },
   "source": [
    "- 通常做法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cc1e3cbe-42a2-4aaf-85fb-24eacda42b23",
   "metadata": {
    "tags": []
   },
   "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>left_id</th>\n",
       "      <th>datetime_left</th>\n",
       "      <th>right_id</th>\n",
       "      <th>datetime_right</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>a</td>\n",
       "      <td>2022-12-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>a</td>\n",
       "      <td>2023-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>2023-02-01</td>\n",
       "      <td>b</td>\n",
       "      <td>2023-02-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  left_id datetime_left right_id datetime_right\n",
       "0       a    2023-01-01        a     2022-12-28\n",
       "1       a    2023-01-01        a     2023-01-06\n",
       "3       b    2023-02-01        b     2023-02-02"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    demo_left\n",
    "    .merge(\n",
    "        demo_right,\n",
    "        left_on='left_id',\n",
    "        right_on='right_id',\n",
    "        suffixes=['_left', '_right']\n",
    "    )\n",
    "    .groupby('left_id', group_keys=False)\n",
    "    .apply(\n",
    "        lambda df: df.loc[(df.datetime_left - df.datetime_right).dt.days.abs() <= 7, :]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9138ca42-2dd8-4796-9bc0-0393b2cb0ece",
   "metadata": {},
   "source": [
    "- 特殊做法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0606531d-9b12-435d-a7de-48314c77da42",
   "metadata": {
    "tags": []
   },
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">left</th>\n",
       "      <th colspan=\"2\" halign=\"left\">right</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>left_id</th>\n",
       "      <th>datetime</th>\n",
       "      <th>datetime_past_limit</th>\n",
       "      <th>datetime_future_limit</th>\n",
       "      <th>right_id</th>\n",
       "      <th>datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>2022-12-25</td>\n",
       "      <td>2023-01-08</td>\n",
       "      <td>a</td>\n",
       "      <td>2022-12-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>2022-12-25</td>\n",
       "      <td>2023-01-08</td>\n",
       "      <td>a</td>\n",
       "      <td>2023-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>2023-02-01</td>\n",
       "      <td>2023-01-25</td>\n",
       "      <td>2023-02-08</td>\n",
       "      <td>b</td>\n",
       "      <td>2023-02-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     left                                                         right  \\\n",
       "  left_id   datetime datetime_past_limit datetime_future_limit right_id   \n",
       "0       a 2023-01-01          2022-12-25            2023-01-08        a   \n",
       "1       a 2023-01-01          2022-12-25            2023-01-08        a   \n",
       "2       b 2023-02-01          2023-01-25            2023-02-08        b   \n",
       "\n",
       "              \n",
       "    datetime  \n",
       "0 2022-12-28  \n",
       "1 2023-01-06  \n",
       "2 2023-02-02  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pip install pyjanitor\n",
    "import janitor\n",
    "\n",
    "(\n",
    "    demo_left\n",
    "    # 添加辅助范围判断用上下限字段\n",
    "    .assign(\n",
    "        datetime_past_limit=demo_left['datetime'] - pd.Timedelta(days=7),\n",
    "        datetime_future_limit=demo_left['datetime'] + pd.Timedelta(days=7)\n",
    "    )\n",
    "    # 执行条件连接\n",
    "    .conditional_join(\n",
    "        demo_right,\n",
    "        ('left_id', 'right_id', '=='),\n",
    "        ('datetime_past_limit', 'datetime', '<='),\n",
    "        ('datetime_future_limit', 'datetime', '>=')\n",
    "    )\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}