历史文章附件列表/（数据科学学习手札161）高性能数据分析利器DuckDB在Python中的使用/（数据科学学习手札161）高性能数据分析利器DuckDB在Python中的使用.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "660cd20c-04da-4033-b919-450308b4c2bc",
   "metadata": {},
   "source": [
    "# 2 DuckDB在Python中的使用"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da26549c-233f-4ffe-a375-0e7a105ecc75",
   "metadata": {},
   "source": [
    "## 2.1 数据集的导入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1530d808-deeb-4d5d-8444-f9ba903bd853",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:19:17.760718Z",
     "iopub.status.busy": "2024-06-05T10:19:17.759718Z",
     "iopub.status.idle": "2024-06-05T10:19:18.569784Z",
     "shell.execute_reply": "2024-06-05T10:19:18.568802Z",
     "shell.execute_reply.started": "2024-06-05T10:19:17.760718Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f501ec9a-be4b-4267-835a-0695909bf54f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:19:18.572179Z",
     "iopub.status.busy": "2024-06-05T10:19:18.570773Z",
     "iopub.status.idle": "2024-06-05T10:19:30.405500Z",
     "shell.execute_reply": "2024-06-05T10:19:30.404497Z",
     "shell.execute_reply.started": "2024-06-05T10:19:18.572179Z"
    }
   },
   "outputs": [],
   "source": [
    "# 利用pandas生成示例数据文件\n",
    "generated_df = pd.DataFrame(\n",
    "    {\n",
    "        '类别': np.random.choice(list('ABCDEF'), 5000000),\n",
    "        '数值': np.round(np.random.uniform(0, 1000000, 5000000), 3)\n",
    "    }\n",
    ")\n",
    "\n",
    "# 分别导出为csv、parquet、json格式\n",
    "generated_df.to_csv('./demo_data.csv', index=False)\n",
    "generated_df.to_parquet('./demo_data.parquet')\n",
    "generated_df.to_json('./demo_data.json', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "50972b33-d2b3-4a19-aca2-793e466d4170",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:19:30.410018Z",
     "iopub.status.busy": "2024-06-05T10:19:30.410018Z",
     "iopub.status.idle": "2024-06-05T10:19:30.731686Z",
     "shell.execute_reply": "2024-06-05T10:19:30.730679Z",
     "shell.execute_reply.started": "2024-06-05T10:19:30.410018Z"
    }
   },
   "outputs": [],
   "source": [
    "import duckdb\n",
    "import polars as pl"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "205b4d18-f67c-471b-a796-a8931d3c58d9",
   "metadata": {},
   "source": [
    "- `csv`格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f659f993-5f5a-46af-9abe-9238a598fc6e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:19:30.733688Z",
     "iopub.status.busy": "2024-06-05T10:19:30.733688Z",
     "iopub.status.idle": "2024-06-05T10:19:34.304541Z",
     "shell.execute_reply": "2024-06-05T10:19:34.303048Z",
     "shell.execute_reply.started": "2024-06-05T10:19:30.733688Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "44.2 ms ± 2.83 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit \n",
    "duckdb.read_csv('./demo_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "dcd3bb0e-51dc-491a-b831-a64824b9e9b6",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:19:34.306406Z",
     "iopub.status.busy": "2024-06-05T10:19:34.305897Z",
     "iopub.status.idle": "2024-06-05T10:19:47.898070Z",
     "shell.execute_reply": "2024-06-05T10:19:47.897070Z",
     "shell.execute_reply.started": "2024-06-05T10:19:34.306406Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.68 s ± 90.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit \n",
    "pd.read_csv('./demo_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "72896b45-ac90-405c-b193-304db369ff33",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:19:47.900069Z",
     "iopub.status.busy": "2024-06-05T10:19:47.899069Z",
     "iopub.status.idle": "2024-06-05T10:19:57.674289Z",
     "shell.execute_reply": "2024-06-05T10:19:57.672828Z",
     "shell.execute_reply.started": "2024-06-05T10:19:47.900069Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "120 ms ± 3.87 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit \n",
    "pl.read_csv('./demo_data.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdfdb358-caa6-411b-88e5-92ebad6fe226",
   "metadata": {},
   "source": [
    "- `parquet`格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3408b2ac-d43a-4af2-88ac-c69972292a3c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:19:57.675302Z",
     "iopub.status.busy": "2024-06-05T10:19:57.675302Z",
     "iopub.status.idle": "2024-06-05T10:20:06.006111Z",
     "shell.execute_reply": "2024-06-05T10:20:06.005107Z",
     "shell.execute_reply.started": "2024-06-05T10:19:57.675302Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.04 ms ± 28.8 µs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit \n",
    "duckdb.read_parquet('./demo_data.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "63781e00-2dfc-4f45-8011-8ab77224dc76",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:06.011111Z",
     "iopub.status.busy": "2024-06-05T10:20:06.010112Z",
     "iopub.status.idle": "2024-06-05T10:20:10.744589Z",
     "shell.execute_reply": "2024-06-05T10:20:10.743463Z",
     "shell.execute_reply.started": "2024-06-05T10:20:06.011111Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "589 ms ± 21 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit \n",
    "pd.read_parquet('./demo_data.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2a8ec866-33b6-47f5-b3cd-6b06b82b42ec",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:10.746288Z",
     "iopub.status.busy": "2024-06-05T10:20:10.746288Z",
     "iopub.status.idle": "2024-06-05T10:20:17.432701Z",
     "shell.execute_reply": "2024-06-05T10:20:17.431651Z",
     "shell.execute_reply.started": "2024-06-05T10:20:10.746288Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "82.6 ms ± 1.24 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit \n",
    "pl.read_parquet('./demo_data.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e7cf1719-ffdd-4791-a8d2-d3b8bb9e9d5d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:17.435035Z",
     "iopub.status.busy": "2024-06-05T10:20:17.434421Z",
     "iopub.status.idle": "2024-06-05T10:20:17.557152Z",
     "shell.execute_reply": "2024-06-05T10:20:17.556646Z",
     "shell.execute_reply.started": "2024-06-05T10:20:17.435035Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬────────────┐\n",
       "│  类别   │    数值    │\n",
       "│ varchar │   double   │\n",
       "├─────────┼────────────┤\n",
       "│ A       │ 921260.353 │\n",
       "│ C       │  139757.95 │\n",
       "│ C       │  775391.17 │\n",
       "└─────────┴────────────┘"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckdb.sql(\"SELECT * FROM 'demo_data.csv' LIMIT 3\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5e4f2a7a-98e6-400d-bcc9-e9c5541584bc",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:17.559163Z",
     "iopub.status.busy": "2024-06-05T10:20:17.558162Z",
     "iopub.status.idle": "2024-06-05T10:20:17.588520Z",
     "shell.execute_reply": "2024-06-05T10:20:17.588015Z",
     "shell.execute_reply.started": "2024-06-05T10:20:17.559163Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬────────────┐\n",
       "│  类别   │    数值    │\n",
       "│ varchar │   double   │\n",
       "├─────────┼────────────┤\n",
       "│ A       │ 921260.353 │\n",
       "│ C       │  139757.95 │\n",
       "│ C       │  775391.17 │\n",
       "└─────────┴────────────┘"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckdb.sql(\"SELECT * FROM 'demo_data.parquet' LIMIT 3\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e12c038-2dc0-48d4-8060-15cf682aa612",
   "metadata": {},
   "source": [
    "### 2.1.2 读取其他框架的数据对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "7bf86e82-9298-412d-a235-57b16142433d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:17.593530Z",
     "iopub.status.busy": "2024-06-05T10:20:17.593530Z",
     "iopub.status.idle": "2024-06-05T10:20:18.138347Z",
     "shell.execute_reply": "2024-06-05T10:20:18.137835Z",
     "shell.execute_reply.started": "2024-06-05T10:20:17.593530Z"
    }
   },
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>921260.353</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  类别          数值\n",
       "0  A  921260.353"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandas_df = pd.read_parquet('demo_data.parquet')\n",
    "pandas_df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "980b3641-687b-437f-adac-04e233bbfe7f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:18.140360Z",
     "iopub.status.busy": "2024-06-05T10:20:18.140360Z",
     "iopub.status.idle": "2024-06-05T10:20:18.232084Z",
     "shell.execute_reply": "2024-06-05T10:20:18.230672Z",
     "shell.execute_reply.started": "2024-06-05T10:20:18.140360Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (1, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>类别</th><th>数值</th></tr><tr><td>str</td><td>f64</td></tr></thead><tbody><tr><td>&quot;A&quot;</td><td>921260.353</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (1, 2)\n",
       "┌──────┬────────────┐\n",
       "│ 类别 ┆ 数值       │\n",
       "│ ---  ┆ ---        │\n",
       "│ str  ┆ f64        │\n",
       "╞══════╪════════════╡\n",
       "│ A    ┆ 921260.353 │\n",
       "└──────┴────────────┘"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "polars_df = pl.read_parquet('demo_data.parquet')\n",
    "polars_df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "59d027ed-fce7-4fd4-a5d6-1ee7a6c24d6c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:18.234140Z",
     "iopub.status.busy": "2024-06-05T10:20:18.233139Z",
     "iopub.status.idle": "2024-06-05T10:20:18.263410Z",
     "shell.execute_reply": "2024-06-05T10:20:18.261946Z",
     "shell.execute_reply.started": "2024-06-05T10:20:18.234140Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬────────────┐\n",
       "│  类别   │    数值    │\n",
       "│ varchar │   double   │\n",
       "├─────────┼────────────┤\n",
       "│ A       │ 921260.353 │\n",
       "└─────────┴────────────┘"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckdb.sql(\"SELECT * FROM pandas_df LIMIT 1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "b6370265-3249-48bc-85e5-eca0a2143d66",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:18.265484Z",
     "iopub.status.busy": "2024-06-05T10:20:18.264477Z",
     "iopub.status.idle": "2024-06-05T10:20:18.340817Z",
     "shell.execute_reply": "2024-06-05T10:20:18.339816Z",
     "shell.execute_reply.started": "2024-06-05T10:20:18.265484Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬────────────┐\n",
       "│  类别   │    数值    │\n",
       "│ varchar │   double   │\n",
       "├─────────┼────────────┤\n",
       "│ A       │ 921260.353 │\n",
       "└─────────┴────────────┘"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duckdb.sql(\"SELECT * FROM polars_df LIMIT 1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bb0f1fc-3dfc-4732-add0-57ce16013be6",
   "metadata": {},
   "source": [
    "## 2.2 执行分析运算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e5cb8ccb-413c-436e-921c-6007de31caba",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:18.342816Z",
     "iopub.status.busy": "2024-06-05T10:20:18.341816Z",
     "iopub.status.idle": "2024-06-05T10:20:18.355677Z",
     "shell.execute_reply": "2024-06-05T10:20:18.355169Z",
     "shell.execute_reply.started": "2024-06-05T10:20:18.342816Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "duckdb.duckdb.DuckDBPyRelation"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demo_r = duckdb.read_parquet('demo_data.parquet')\n",
    "type(demo_r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d1812aec-8d24-4305-90b9-ffeb4c399a26",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:18.358088Z",
     "iopub.status.busy": "2024-06-05T10:20:18.357078Z",
     "iopub.status.idle": "2024-06-05T10:20:18.387468Z",
     "shell.execute_reply": "2024-06-05T10:20:18.386468Z",
     "shell.execute_reply.started": "2024-06-05T10:20:18.358088Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬────────────┐\n",
       "│  类别   │    数值    │\n",
       "│ varchar │   double   │\n",
       "├─────────┼────────────┤\n",
       "│ A       │ 921260.353 │\n",
       "└─────────┴────────────┘"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看第一行\n",
    "duckdb.sql(\"SELECT * FROM demo_r LIMIT 1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "88e40f83-e893-4e51-8042-254fb8c5c6f9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:18.388936Z",
     "iopub.status.busy": "2024-06-05T10:20:18.387468Z",
     "iopub.status.idle": "2024-06-05T10:20:18.419404Z",
     "shell.execute_reply": "2024-06-05T10:20:18.418396Z",
     "shell.execute_reply.started": "2024-06-05T10:20:18.388936Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌──────────┐\n",
       "│ count(1) │\n",
       "│  int64   │\n",
       "├──────────┤\n",
       "│  5000000 │\n",
       "└──────────┘"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 统计数据记录数\n",
    "duckdb.sql(\"SELECT count(1) FROM demo_r\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "55ab6008-ae33-4d61-8525-99950d50a890",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:18.420693Z",
     "iopub.status.busy": "2024-06-05T10:20:18.420693Z",
     "iopub.status.idle": "2024-06-05T10:20:18.512756Z",
     "shell.execute_reply": "2024-06-05T10:20:18.511583Z",
     "shell.execute_reply.started": "2024-06-05T10:20:18.420693Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌────────────────────────┐\n",
       "│ count(DISTINCT \"类别\") │\n",
       "│         int64          │\n",
       "├────────────────────────┤\n",
       "│                      6 │\n",
       "└────────────────────────┘"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 计算类别字段唯一值数量\n",
    "duckdb.sql(\"SELECT count(DISTINCT 类别) FROM demo_r\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "f63fe426-f9f9-4926-9047-1eb157df6050",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:18.513872Z",
     "iopub.status.busy": "2024-06-05T10:20:18.513872Z",
     "iopub.status.idle": "2024-06-05T10:20:18.637737Z",
     "shell.execute_reply": "2024-06-05T10:20:18.636733Z",
     "shell.execute_reply.started": "2024-06-05T10:20:18.513872Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────┬────────────────────┐\n",
       "│  类别   │    mean(\"数值\")    │\n",
       "│ varchar │       double       │\n",
       "├─────────┼────────────────────┤\n",
       "│ C       │  499861.3371859329 │\n",
       "│ F       │  500054.2954633667 │\n",
       "│ D       │  500010.8334199005 │\n",
       "│ A       │  499840.9485479467 │\n",
       "│ B       │ 500181.61871720897 │\n",
       "│ E       │  500059.2490139358 │\n",
       "└─────────┴────────────────────┘"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 分组统计平均值\n",
    "duckdb.sql(\"\"\"SELECT 类别, mean(数值) FROM demo_r GROUP BY 类别\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "32ea7226-8125-4357-ab2b-9391db8fadaf",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:18.639742Z",
     "iopub.status.busy": "2024-06-05T10:20:18.638736Z",
     "iopub.status.idle": "2024-06-05T10:20:24.154197Z",
     "shell.execute_reply": "2024-06-05T10:20:24.152674Z",
     "shell.execute_reply.started": "2024-06-05T10:20:18.639742Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "673 µs ± 53.2 µs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "duckdb.sql(\"\"\"SELECT 类别, mean(数值) FROM demo_r GROUP BY 类别\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e346ca03-9a3b-49b6-be97-38180e2eadd9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:24.156196Z",
     "iopub.status.busy": "2024-06-05T10:20:24.156196Z",
     "iopub.status.idle": "2024-06-05T10:20:27.262804Z",
     "shell.execute_reply": "2024-06-05T10:20:27.260508Z",
     "shell.execute_reply.started": "2024-06-05T10:20:24.156196Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "384 ms ± 65.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "pandas_df.groupby('类别')['数值'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "49e2b12a-2401-45f3-bc6e-daa5e23aa17d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:27.264821Z",
     "iopub.status.busy": "2024-06-05T10:20:27.263820Z",
     "iopub.status.idle": "2024-06-05T10:20:31.859595Z",
     "shell.execute_reply": "2024-06-05T10:20:31.858600Z",
     "shell.execute_reply.started": "2024-06-05T10:20:27.264821Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "56.1 ms ± 3.75 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "polars_df.group_by('类别').agg(pl.col('数值').mean())  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7c614e2-c4a7-47e9-b853-438586c89500",
   "metadata": {},
   "source": [
    "## 2.3 计算结果转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "225f063a-676a-4fe7-b077-f6143f6c88f2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:31.861595Z",
     "iopub.status.busy": "2024-06-05T10:20:31.861595Z",
     "iopub.status.idle": "2024-06-05T10:20:31.890534Z",
     "shell.execute_reply": "2024-06-05T10:20:31.889957Z",
     "shell.execute_reply.started": "2024-06-05T10:20:31.861595Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('A', 921260.353)]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 转为python对象\n",
    "duckdb.sql(\"SELECT * FROM demo_r LIMIT 1\").fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "55fefc15-6170-4987-80b6-ade0a761f3d6",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:31.892580Z",
     "iopub.status.busy": "2024-06-05T10:20:31.892580Z",
     "iopub.status.idle": "2024-06-05T10:20:31.921580Z",
     "shell.execute_reply": "2024-06-05T10:20:31.920580Z",
     "shell.execute_reply.started": "2024-06-05T10:20:31.892580Z"
    }
   },
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>921260.353</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  类别          数值\n",
       "0  A  921260.353"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 转为pandas数据框\n",
    "duckdb.sql(\"SELECT * FROM demo_r LIMIT 1\").df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "e471f538-2ab3-479e-8234-d2b12946cfba",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:31.922580Z",
     "iopub.status.busy": "2024-06-05T10:20:31.922580Z",
     "iopub.status.idle": "2024-06-05T10:20:31.953136Z",
     "shell.execute_reply": "2024-06-05T10:20:31.952092Z",
     "shell.execute_reply.started": "2024-06-05T10:20:31.922580Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (1, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>类别</th><th>数值</th></tr><tr><td>str</td><td>f64</td></tr></thead><tbody><tr><td>&quot;A&quot;</td><td>921260.353</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (1, 2)\n",
       "┌──────┬────────────┐\n",
       "│ 类别 ┆ 数值       │\n",
       "│ ---  ┆ ---        │\n",
       "│ str  ┆ f64        │\n",
       "╞══════╪════════════╡\n",
       "│ A    ┆ 921260.353 │\n",
       "└──────┴────────────┘"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 转为polars数据框\n",
    "duckdb.sql(\"SELECT * FROM demo_r LIMIT 1\").pl()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "2fb404c3-016e-4607-91ef-77b07d398d0b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:31.955136Z",
     "iopub.status.busy": "2024-06-05T10:20:31.955136Z",
     "iopub.status.idle": "2024-06-05T10:20:31.983645Z",
     "shell.execute_reply": "2024-06-05T10:20:31.982645Z",
     "shell.execute_reply.started": "2024-06-05T10:20:31.955136Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'类别': array(['A'], dtype=object), '数值': array([921260.353])}"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 转为numpy数组\n",
    "duckdb.sql(\"SELECT * FROM demo_r LIMIT 1\").fetchnumpy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abc20021-421e-4d04-a9de-a0ca2d694be4",
   "metadata": {},
   "source": [
    "- `csv`格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "5c5ffa91-fd32-4af1-bb05-b38a55308278",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:31.986645Z",
     "iopub.status.busy": "2024-06-05T10:20:31.985645Z",
     "iopub.status.idle": "2024-06-05T10:20:37.568449Z",
     "shell.execute_reply": "2024-06-05T10:20:37.567449Z",
     "shell.execute_reply.started": "2024-06-05T10:20:31.986645Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "699 ms ± 18.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "demo_r.write_csv('csv_output_test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "5a354891-6894-43f3-bd70-304d93851047",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:20:37.570454Z",
     "iopub.status.busy": "2024-06-05T10:20:37.569454Z",
     "iopub.status.idle": "2024-06-05T10:21:42.990502Z",
     "shell.execute_reply": "2024-06-05T10:21:42.989493Z",
     "shell.execute_reply.started": "2024-06-05T10:20:37.570454Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8.19 s ± 90 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "pandas_df.to_csv('csv_output_test.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "0f9e87b6-f78b-451b-8b94-d57ce9289fa4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:21:42.992514Z",
     "iopub.status.busy": "2024-06-05T10:21:42.992514Z",
     "iopub.status.idle": "2024-06-05T10:21:44.537747Z",
     "shell.execute_reply": "2024-06-05T10:21:44.535748Z",
     "shell.execute_reply.started": "2024-06-05T10:21:42.992514Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "185 ms ± 12.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "polars_df.write_csv('csv_output_test.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bb19c77-2c56-4919-9535-1c852d4e30e7",
   "metadata": {},
   "source": [
    "- `parquet`格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "7e584845-c021-4553-83b4-6d0f625003aa",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:21:44.539753Z",
     "iopub.status.busy": "2024-06-05T10:21:44.539753Z",
     "iopub.status.idle": "2024-06-05T10:21:47.848146Z",
     "shell.execute_reply": "2024-06-05T10:21:47.847637Z",
     "shell.execute_reply.started": "2024-06-05T10:21:44.539753Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "411 ms ± 18.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "demo_r.write_parquet('csv_output_test.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "531ac2be-0833-457f-a125-747534cc2880",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:21:47.850161Z",
     "iopub.status.busy": "2024-06-05T10:21:47.850161Z",
     "iopub.status.idle": "2024-06-05T10:22:00.601081Z",
     "shell.execute_reply": "2024-06-05T10:22:00.600071Z",
     "shell.execute_reply.started": "2024-06-05T10:21:47.850161Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.59 s ± 47.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "pandas_df.to_parquet('csv_output_test.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "e5331fbc-b6e9-4df4-89e1-3e312c6f45dd",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-06-05T10:22:00.604202Z",
     "iopub.status.busy": "2024-06-05T10:22:00.603087Z",
     "iopub.status.idle": "2024-06-05T10:22:08.558873Z",
     "shell.execute_reply": "2024-06-05T10:22:08.557874Z",
     "shell.execute_reply.started": "2024-06-05T10:22:00.604202Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "989 ms ± 36.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "polars_df.write_parquet('csv_output_test.parquet')"
   ]
  }
 ],
 "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.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}