<commit_before>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating and Inserting Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "!curl -LsS -o $TEMPDIR/geography.db 'https://storage.googleapis.com/ibis-tutorial-data/geography.db'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import tempfile\n",
    "\n",
    "import ibis\n",
    "\n",
    "ibis.options.interactive = True\n",
    "\n",
    "connection = ibis.sqlite.connect(\n",
    "    os.path.join(tempfile.gettempdir(), 'geography.db')\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating new tables from Ibis expressions\n",
    "\n",
    "\n",
    "Suppose you have an Ibis expression that produces a table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = connection.table('countries')\n",
    "\n",
    "continent_name = (\n",
    "    countries.continent.case()\n",
    "    .when('AF', 'Africa')\n",
    "    .when('AN', 'Antarctica')\n",
    "    .when('AS', 'Asia')\n",
    "    .when('EU', 'Europe')\n",
    "    .when('NA', 'North America')\n",
    "    .when('OC', 'Oceania')\n",
    "    .when('SA', 'South America')\n",
    "    .else_(countries.continent)\n",
    "    .end()\n",
    "    .name('continent_name')\n",
    ")\n",
    "\n",
    "expr = countries[countries.continent, continent_name].distinct()\n",
    "expr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create a table in the database from the results of this expression, use the connection's `create_table` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.create_table('continents', expr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "continents = connection.table('continents')\n",
    "continents"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tables can be similarly dropped with `drop_table`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.drop_table('continents')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inserting data into existing tables\n",
    "\n",
    "\n",
    "Some backends support inserting data into existing tables from expressions. This can be done using `connection.insert('table_name', expr)`."
   ]
  }
 ],
 "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}<commit_msg>docs: document how to create a table from a pandas dataframe using ibis.memtable<commit_after>{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating and Inserting Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import tempfile\n",
    "from pathlib import Path\n",
    "from urllib.request import urlretrieve\n",
    "\n",
    "tempdir = Path(tempfile.gettempdir())\n",
    "geography_db_file = tempdir / 'geography.db'\n",
    "\n",
    "if geography_db_file.exists():\n",
    "    geography_db_file.unlink()\n",
    "\n",
    "_ = urlretrieve(\n",
    "    'https://storage.googleapis.com/ibis-tutorial-data/geography.db',\n",
    "    geography_db_file,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import ibis\n",
    "\n",
    "ibis.options.interactive = True\n",
    "\n",
    "connection = ibis.sqlite.connect(geography_db_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating new tables from Ibis expressions\n",
    "\n",
    "\n",
    "Suppose you have an Ibis expression that produces a table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = connection.table('countries')\n",
    "\n",
    "continent_name = (\n",
    "    countries.continent.case()\n",
    "    .when('AF', 'Africa')\n",
    "    .when('AN', 'Antarctica')\n",
    "    .when('AS', 'Asia')\n",
    "    .when('EU', 'Europe')\n",
    "    .when('NA', 'North America')\n",
    "    .when('OC', 'Oceania')\n",
    "    .when('SA', 'South America')\n",
    "    .else_(countries.continent)\n",
    "    .end()\n",
    "    .name('continent_name')\n",
    ")\n",
    "\n",
    "expr = countries[countries.continent, continent_name].distinct()\n",
    "expr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create a table in the database from the results of this expression, use the connection's `create_table` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.create_table('continents', expr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "continents = connection.table('continents')\n",
    "continents"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tables can be similarly dropped with `drop_table`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.drop_table('continents')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating new tables from in-memory Pandas dataframes\n",
    "\n",
    "Pandas and NumPy are convenient to create test data in memory as a dataframe. This can then be turned into an Ibis expression using `ibis.memtable`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "def make_students_df(num_records, random_seed=None):\n",
    "    rng = np.random.default_rng(random_seed)\n",
    "    return pd.DataFrame(\n",
    "        {\n",
    "            \"firstname\": rng.choice([\"Alice\", \"Bob\", \"Jane\", \"John\"], size=num_records),\n",
    "            \"birth_date\": (\n",
    "                pd.to_datetime(\"2021-01-01\")\n",
    "                + pd.to_timedelta(rng.integers(0, 365, size=num_records), unit=\"D\")\n",
    "            ),\n",
    "            \"math_grade\": rng.normal(55, 10, size=num_records).clip(0, 100).round(1),\n",
    "        }\n",
    "    )\n",
    "\n",
    "students_df = make_students_df(21, random_seed=42)\n",
    "students_memtable = ibis.memtable(students_df)\n",
    "students_memtable\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default `ibis.memtable` uses the `duckdb` in-memory backend to execute queries against the Pandas dataframe data efficiently.\n",
    "\n",
    "We can then materialize it as a physical table for a specific backend if necessary:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "students_db_file = tempdir / \"ibis_tutorial_students.duckdb\"\n",
    "if students_db_file.exists():\n",
    "    students_db_file.unlink()\n",
    "\n",
    "connection = ibis.duckdb.connect(students_db_file)\n",
    "connection.create_table('students', students_memtable)\n",
    "students = connection.table('students')\n",
    "students.group_by(students.birth_date.month()).aggregate(\n",
    "    count=students.count(),\n",
    "    avg_math_grade=students.math_grade.mean(),\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that NumPy, Pandas and `ibis.memtable` are only suitable to generate data that fits in memory. To generate data larger than memory, we can generate data in chunks and iteratively insert the chunks using `connection.insert(tablename, pandas_dataframe)`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.insert(students.get_name(), make_students_df(10_000, random_seed=43))\n",
    "students.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.insert(students.get_name(), make_students_df(10_000, random_seed=44))\n",
    "students.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "students.group_by(students.birth_date.month()).aggregate(\n",
    "    count=students.count(),\n",
    "    avg_math_grade=students.math_grade.mean(),\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inserting data into existing tables\n",
    "\n",
    "\n",
    "Some backends support inserting data into existing tables from expressions. This can be done using `connection.insert('table_name', expr)`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.6 ('base')",
   "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.10.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "c91744e846ab1fb46a81a92b1fa828c0e6b1381e7e12fd7b2bb300d813000458"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
{'fraction_non_alphanumeric': 0.2058377558756634, 'fraction_numerical': 0.009950720242608036, 'mean_word_length': 2.7382217499114416, 'pattern_counts': {'":': 177, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2555262', 'n_tokens_mistral': 4020, 'n_tokens_neox': 3860, 'n_words': 718}