{ "cells": [ { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "# Filtering Rows in Datatable" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This article highlights various ways of filtering rows in python [datatable](https://datatable.readthedocs.io/en/latest/). The examples used here are based off the excellent [article](https://suzan.rbind.io/2018/02/dplyr-tutorial-3/) by [Susan Baert](https://twitter.com/SuzanBaert).\n", "\n", "The data file can be accessed [here](https://github.com/samukweku/data_files/raw/master/msleep.txt)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## **Basic Row Filters**" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "from datatable import dt, f\n", "from operator import or_, xor" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namegenusvoreorderconservationsleep_totalsleep_remsleep_cycleawakebrainwtbodywt
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0CheetahAcinonyxcarniCarnivoralc12.1NANA11.9NA50
1Owl monkeyAotusomniPrimatesNA171.8NA70.01550.48
2Mountain beaverAplodontiaherbiRodentiant14.42.4NA9.6NA1.35
3Greater short-tailed shrewBlarinaomniSoricomorphalc14.92.30.1333339.10.000290.019
4CowBosherbiArtiodactyladomesticated40.70.666667200.423600
\n", "
\n", "
5 rows × 11 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "file_path = \"Data_files/msleep.txt\"\n", "\n", "DT = dt.fread(file_path)\n", "\n", "DT.head(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Filtering Rows Based on a Numeric Variable" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can filter numeric variables based on their values. A number of commonly used operators include: >, >=, <, <=, == and !=.\n", "\n", "Note that in datatable, filtration occurs in the ``i`` section: " ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesleep_total
▪▪▪▪▪▪▪▪▪▪▪▪
0Big brown bat19.7
1Thick-tailed opposum19.4
2Little brown bat19.9
3Giant armadillo18.1
\n", "
\n", "
4 rows × 2 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "DT[f.sleep_total > 18, [\"name\", \"sleep_total\"]]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To select a range of values, you can use two logical requirements; in the example below, only rows where `sleep_total` is greater than or equal to 16, and less than or equal to 18 are selected:" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesleep_total
▪▪▪▪▪▪▪▪▪▪▪▪
0Owl monkey17
1Long-nosed armadillo17.4
2North American Opossum18
3Arctic ground squirrel16.6
\n", "
\n", "
4 rows × 2 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "DT[(f.sleep_total >= 16) & (f.sleep_total <= 18), [\"name\", \"sleep_total\"]]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Note in the code above, that each condition is wrapped in parentheses; this is to ensure that the correct output is obtained.\n", "\n", "In [Pandas](https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&cad=rja&uact=8&ved=2ahUKEwi-ybqouovvAhWRzjgGHcYtB1sQFjAAegQIAxAD&url=https%3A%2F%2Fpandas.pydata.org%2Fpandas-docs%2Fstable%2Freference%2Fapi%2Fpandas.Series.between.html&usg=AOvVaw2JuKj72awwkzd_18ykvwPx)/[dplyr](https://dplyr.tidyverse.org/reference/between.html)/[rdatatable](https://rdatatable.gitlab.io/data.table/reference/between.html), there is a `between` function that makes selection such as the above easier; at the moment, there is no equivalent function in [datatable](https://datatable.readthedocs.io/en/latest/api/index-api.html); you can create a temporary `between` function:" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesleep_total
▪▪▪▪▪▪▪▪▪▪▪▪
0Owl monkey17
1Long-nosed armadillo17.4
2North American Opossum18
3Arctic ground squirrel16.6
\n", "
\n", "
4 rows × 2 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def between(column, left, right):\n", " l = f[column]>=left\n", " r = f[column]<=right\n", " return l & r\n", "\n", "DT[between('sleep_total', 16, 18), ['name', 'sleep_total']]\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are scenarios where you may want to select rows where the value is nearly a given value. You may also want to specify a tolerance value to indicate how far the values can be. \n", "\n", "This can be replicated with the [isclose](https://datatable.readthedocs.io/en/latest/api/math/isclose.html) function in the [datatable.math](https://datatable.readthedocs.io/en/latest/api/math.html) submodule.\n", "\n", "Let's assume that the tolerance should be within one standard deviation of 17:" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesleep_total
▪▪▪▪▪▪▪▪▪▪▪▪
0Owl monkey17
1Mountain beaver14.4
2Greater short-tailed shrew14.9
3Three-toed sloth14.4
4Long-nosed armadillo17.4
5North American Opossum18
6Big brown bat19.7
7Western american chipmunk14.9
8Thick-tailed opposum19.4
9Mongolian gerbil14.2
10Golden hamster14.3
11Vole12.8
12Little brown bat19.9
13Round-tailed muskrat14.6
14Northern grasshopper mouse14.5
15Tiger15.8
16Lion13.5
17Phalanger13.7
18Giant armadillo18.1
19Laboratory rat13
20Arctic ground squirrel16.6
21Thirteen-lined ground squirrel13.8
22Golden-mantled ground squirrel15.9
23Musk shrew12.8
24Eastern american chipmunk15.8
25Tenrec15.6
\n", "
\n", "
26 rows × 2 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from datatable.math import isclose\n", "\n", "# calculate tolerance\n", "tolerance = DT['sleep_total'].sd1()\n", "\n", "DT[isclose(f.sleep_total, 17, atol = tolerance), ['name', 'sleep_total']]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Filtering based on String Matches" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can select on string matches as well; in the example below, the ``==`` comparison operator is used to select a specific group of animals:" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ordernamesleep_total
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0DidelphimorphiaNorth American Opossum18
1DidelphimorphiaThick-tailed opposum19.4
\n", "
\n", "
2 rows × 3 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "DT[f.order == \"Didelphimorphia\", [\"order\", \"name\", \"sleep_total\"]]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Other operators can be used also:" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ordernamesleep_total
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0CarnivoraCheetah12.1
1PrimatesOwl monkey17
2SoricomorphaGreater short-tailed shrew14.9
3ArtiodactylaCow4
4PilosaThree-toed sloth14.4
5CarnivoraNorthern fur seal8.7
6CarnivoraDog10.1
7ArtiodactylaRoe deer3
8ArtiodactylaGoat5.3
9PrimatesGrivet10
10SoricomorphaStar-nosed mole10.3
11SoricomorphaLesser short-tailed shrew9.1
12CingulataLong-nosed armadillo17.4
13HyracoideaTree hyrax5.3
14DidelphimorphiaNorth American Opossum18
⋮⋮⋮⋮
56ScandentiaTree shrew8.9
57CetaceaBottle-nosed dolphin5.2
58CarnivoraGenet6.3
59CarnivoraArctic fox12.5
60CarnivoraRed fox9.8
\n", "
\n", "
61 rows × 3 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "DT[f.order != 'Rodentia', ['order', 'name', 'sleep_total']]\n" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ordernamesleep_total
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0RodentiaVesper mouse7
1RodentiaWestern american chipmunk14.9
2RodentiaVole12.8
\n", "
\n", "
3 rows × 3 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "DT[f.name > 'V', ['order', 'name', 'sleep_total']]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the examples above, only one animal is used; to select more animals, you could pass a list of conditions, with the `|` (or) symbol:" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ordernamesleep_total
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0DidelphimorphiaNorth American Opossum18
1DidelphimorphiaThick-tailed opposum19.4
2DiprotodontiaPhalanger13.7
3DiprotodontiaPotoroo11.1
\n", "
\n", "
4 rows × 3 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rows = (f.order == \"Didelphimorphia\") | (f.order == \"Diprotodontia\")\n", "\n", "columns = ['order', 'name', 'sleep_total']\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "However, this can become unwieldy, as the number of animals increase. At the moment, there is no equivalent of python's [in](https://docs.python.org/3/reference/expressions.html#membership-test-operations) operator in [datatable](https://datatable.readthedocs.io/en/latest/api/index-api.html); let's create a temporary [function](https://stackoverflow.com/a/61509482/7175713) to help with this:" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ordernamesleep_total
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0DidelphimorphiaNorth American Opossum18
1DidelphimorphiaThick-tailed opposum19.4
2DiprotodontiaPhalanger13.7
3DiprotodontiaPotoroo11.1
\n", "
\n", "
4 rows × 3 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from functools import reduce\n", "\n", "def isin(column, sequence_of_labels):\n", " func = lambda x: f[column] == x\n", " return reduce(or_, map(func, sequence_of_labels))\n", "\n", "\n", "labels = (\"Didelphimorphia\", \"Diprotodontia\")\n", "\n", "columns = [\"order\", \"name\", \"sleep_total\"]\n", "\n", "DT[isin('order', labels), columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can also deselect certain groups using the `isin` function above, and combine it with the `~` symbol:" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ordernamesleep_total
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0SoricomorphaGreater short-tailed shrew14.9
1ArtiodactylaCow4
2PilosaThree-toed sloth14.4
3ArtiodactylaRoe deer3
4ArtiodactylaGoat5.3
5SoricomorphaStar-nosed mole10.3
6SoricomorphaLesser short-tailed shrew9.1
7CingulataLong-nosed armadillo17.4
8HyracoideaTree hyrax5.3
9DidelphimorphiaNorth American Opossum18
10ProboscideaAsian elephant3.9
11ChiropteraBig brown bat19.7
12PerissodactylaHorse2.9
13PerissodactylaDonkey3.1
14ErinaceomorphaEuropean hedgehog10.1
⋮⋮⋮⋮
32MonotremataShort-nosed echidna8.6
33PerissodactylaBrazilian tapir4.4
34AfrosoricidaTenrec15.6
35ScandentiaTree shrew8.9
36CetaceaBottle-nosed dolphin5.2
\n", "
\n", "
37 rows × 3 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "labels = (\"Rodentia\", \"Carnivora\", \"Primates\")\n", "\n", "columns = ['order', 'name', 'sleep_total']\n", "\n", "DT[~isin('order', labels), columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Filtering Rows Based on Regex" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are scenarios where you need to filter string columns based on partial matches; a regular expression comes in handy here.\n", "\n", "At the moment, there are very few string functions in [datatable](https://datatable.readthedocs.io/en/latest/api/index-api.html); However, we can make do with the `re_match` function, which is similar to Python's [re.match](https://docs.python.org/3/library/re.html#re.Pattern.match) to get by.\n", "\n", "Let's filter for rows where `mouse` can be found in the column `name`:" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesleep_total
▪▪▪▪▪▪▪▪▪▪▪▪
0North American Opossum18
1Thick-tailed opposum19.4
2Phalanger13.7
3Potoroo11.1
\n", "
\n", "
4 rows × 2 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "columns = ['name', 'sleep_total']\n", "\n", "# returns a boolean column \n", "row = dt.re.match(f.name, '.*mouse.*')\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Filtering Rows based on Multiple Conditions" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Select rows with a `bodywt` above 100 and either have a `sleep_total` above 15, or are not part of the `Carnivora` `order`:" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nameordersleep_totalsleep_remsleep_cycleawakebrainwtbodywt
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0CowArtiodactyla40.70.666667200.423600
1Asian elephantProboscidea3.9NANA20.14.6032547
2HorsePerissodactyla2.90.6121.10.655521
3DonkeyPerissodactyla3.10.4NA20.90.419187
4GiraffeArtiodactyla1.90.4NA22.1NA899.995
5Pilot whaleCetacea2.70.1NA21.35NA800
6African elephantProboscidea3.3NANA20.75.7126654
7TigerCarnivora15.8NANA8.2NA162.564
8Brazilian tapirPerissodactyla4.410.919.60.169207.501
9Bottle-nosed dolphinCetacea5.2NANA18.8NA173.33
\n", "
\n", "
10 rows × 8 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rows = (f.bodywt > 100) & ((f.sleep_total > 15) | (f.order != \"Carnivora\"))\n", "\n", "columns = [\"name\", \"order\", slice(\"sleep_total\", \"bodywt\")]\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Return rows where `bodywt` is either greater than 100 or `brainwt` greater than 1, but not both:" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namebodywtbrainwt
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0Cow6000.423
1Horse5210.655
2Donkey1870.419
3Giraffe899.995NA
4Pilot whale800NA
5Human621.32
6Tiger162.564NA
7Lion161.499NA
8Brazilian tapir207.5010.169
9Bottle-nosed dolphin173.33NA
\n", "
\n", "
10 rows × 3 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rows = xor((f.bodywt > 100), (f.brainwt > 1))\n", "\n", "columns = [\"name\", slice(\"bodywt\", \"brainwt\")]\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Select all rows where `brainwt` is larger than 1, but `bodywt` does not exceed 100:" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesleep_totalbrainwtbodywt
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0Human81.3262
\n", "
\n", "
1 row × 4 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rows = (f.bodywt <= 100) & (f.brainwt > 1)\n", "\n", "columns = [\"name\", \"sleep_total\", \"brainwt\", \"bodywt\"]\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Filtering out Empty Rows" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are two options for filtering out empty rows - comparing with `None`, or using the `isna` function:" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nameconservationsleep_totalsleep_remsleep_cycle
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0Cheetahlc12.1NANA
1Mountain beavernt14.42.4NA
2Greater short-tailed shrewlc14.92.30.133333
3Cowdomesticated40.70.666667
4Northern fur sealvu8.71.40.383333
5Dogdomesticated10.12.90.333333
6Roe deerlc3NANA
7Goatlc5.30.6NA
8Guinea pigdomesticated9.40.80.216667
9Grivetlc100.7NA
10Chinchilladomesticated12.51.50.116667
11Star-nosed molelc10.32.2NA
12Lesser short-tailed shrewlc9.11.40.15
13Long-nosed armadillolc17.43.10.383333
14Tree hyraxlc5.30.5NA
⋮⋮⋮⋮⋮⋮
49Arctic ground squirrellc16.6NANA
50Thirteen-lined ground squirrellc13.83.40.216667
51Golden-mantled ground squirrellc15.93NA
52Pigdomesticated9.12.40.5
53Brazilian tapirvu4.410.9
\n", "
\n", "
54 rows × 5 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rows = f.conservation != None \n", "\n", "columns = [\"name\", slice(\"conservation\", \"sleep_cycle\")]\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nameconservationsleep_totalsleep_remsleep_cycle
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0Cheetahlc12.1NANA
1Mountain beavernt14.42.4NA
2Greater short-tailed shrewlc14.92.30.133333
3Cowdomesticated40.70.666667
4Northern fur sealvu8.71.40.383333
5Dogdomesticated10.12.90.333333
6Roe deerlc3NANA
7Goatlc5.30.6NA
8Guinea pigdomesticated9.40.80.216667
9Grivetlc100.7NA
10Chinchilladomesticated12.51.50.116667
11Star-nosed molelc10.32.2NA
12Lesser short-tailed shrewlc9.11.40.15
13Long-nosed armadillolc17.43.10.383333
14Tree hyraxlc5.30.5NA
⋮⋮⋮⋮⋮⋮
49Arctic ground squirrellc16.6NANA
50Thirteen-lined ground squirrellc13.83.40.216667
51Golden-mantled ground squirrellc15.93NA
52Pigdomesticated9.12.40.5
53Brazilian tapirvu4.410.9
\n", "
\n", "
54 rows × 5 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rows = ~dt.isna(f.conservation) \n", "\n", "columns = [\"name\", slice(\"conservation\", \"sleep_cycle\")]\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Filtering across Multiple Columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Filter across all Columns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "It is possible to filter for rows based on values across columns.\n", "\n", "One thing to note, and be careful about, is that in [datatable](https://datatable.readthedocs.io/en/latest/start/quick-start.html), within the same bracket, operations in the `i` section, occur before any operation within the `j` section; as such, depending on the context, and to ensure the right output, it is better to select the columns first, then chain the row filtration via another bracket. The examples below should make this clearer.\n", "\n", "Let's filter for rows across the selected columns, keeping only rows where any column has the pattern `Ca` inside:" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namegenusordersleep_total
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0CheetahAcinonyxCarnivora12.1
1Northern fur sealCallorhinusCarnivora8.7
2Vesper mouseCalomysRodentia7
3DogCanisCarnivora10.1
4Roe deerCapreolusArtiodactyla3
5GoatCapriArtiodactyla5.3
6Guinea pigCavisRodentia9.4
7Domestic catFelisCarnivora12.5
8Gray sealHaliochoerusCarnivora6.2
9TigerPantheraCarnivora15.8
10JaguarPantheraCarnivora10.4
11LionPantheraCarnivora13.5
12Caspian sealPhocaCarnivora3.5
13GenetGenettaCarnivora6.3
14Arctic foxVulpesCarnivora12.5
15Red foxVulpesCarnivora9.8
\n", "
\n", "
16 rows × 4 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "columns = f['name':'order', 'sleep_total'].remove(f.vore)\n", "rows = dt.re.match(f[str], \".*Ca.*\").rowany()\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's look at another example, to filter for rows, across selected columns, where any column has a value less than 0.1:" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesleep_totalsleep_remsleep_cycleawakebrainwtbodywt
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0Owl monkey171.8NA70.01550.48
1Greater short-tailed shrew14.92.30.1333339.10.000290.019
2Vesper mouse7NANA17NA0.045
3Dog10.12.90.33333313.90.0714
4Roe deer3NANA210.098214.8
5Guinea pig9.40.80.21666714.60.00550.728
6Chinchilla12.51.50.11666711.50.00640.42
7Star-nosed mole10.32.2NA13.70.0010.06
8African giant pouched rat8.32NA15.70.00661
9Lesser short-tailed shrew9.11.40.1514.90.000140.005
10Long-nosed armadillo17.43.10.3833336.60.01083.5
11Tree hyrax5.30.5NA18.70.01232.95
12North American Opossum184.90.33333360.00631.7
13Big brown bat19.73.90.1166674.30.00030.023
14European hedgehog10.13.50.28333313.90.00350.77
⋮⋮⋮⋮⋮⋮⋮⋮
42Tenrec15.62.3NA8.40.00260.9
43Tree shrew8.92.60.23333315.10.00250.104
44Genet6.31.3NA17.70.01752
45Arctic fox12.5NANA11.50.04453.38
46Red fox9.82.40.3514.20.05044.23
\n", "
\n", "
47 rows × 7 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "columns = f['name', 'sleep_total':'bodywt']\n", "\n", "rows = (f[int, float] < 0.1).rowany()\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The above example only requires that at least one column has a value less than 0.1. What if the goal is to return only rows where all the columns have values above 1? " ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesleep_totalsleep_remsleep_cyclebrainwtbodywt
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0Human81.91.51.3262
\n", "
\n", "
1 row × 6 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "columns = f['name', 'sleep_total' : 'bodywt'].remove(f.awake)\n", "\n", "rows = (f[int, float] > 1).rowall()\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Note the change from [rowany](https://datatable.readthedocs.io/en/latest/api/dt/rowany.html) to [rowall](https://datatable.readthedocs.io/en/latest/api/dt/rowall.html); [rowany](https://datatable.readthedocs.io/en/latest/api/dt/rowany.html) will return `True` for rows where `ANY` column matches the condition, whereas [rowall](https://datatable.readthedocs.io/en/latest/api/dt/rowall.html) will only return `True` for rows where `ALL` columns match the condition.\n", "\n", "\n", "All the examples so far combine `i` and `j` within a single bracket; so why all the noise about context and selecting columns first before rows? The next section should shed more light." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can also limit the filtration to columns that match a particular type; the examples above show how that can be done. This can be handy in certain situations where the target is not limited to one data type.\n", "\n", "Consider the example below, where only rows that have nulls should be returned. Nulls can be both in numeric and string columns:" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namegenusvoreordersleep_totalsleep_rem
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0CheetahAcinonyxcarniCarnivora12.1NA
1Vesper mouseCalomysNARodentia7NA
2Roe deerCapreolusherbiArtiodactyla3NA
3Asian elephantElephasherbiProboscidea3.9NA
4Western american chipmunkEutamiasherbiRodentia14.9NA
5African elephantLoxodontaherbiProboscidea3.3NA
6VoleMicrotusherbiRodentia12.8NA
7Round-tailed muskratNeofiberherbiRodentia14.6NA
8Slow lorisNyctibeuscarniPrimates11NA
9Northern grasshopper mouseOnychomyscarniRodentia14.5NA
10TigerPantheracarniCarnivora15.8NA
11JaguarPantheracarniCarnivora10.4NA
12LionPantheracarniCarnivora13.5NA
13Desert hedgehogParaechinusNAErinaceomorpha10.32.7
14PottoPerodicticusomniPrimates11NA
15Deer mousePeromyscusNARodentia11.5NA
16PhalangerPhalangerNADiprotodontia13.71.8
17Common porpoisePhocoenacarniCetacea5.6NA
18Rock hyraxProcaviaNAHyracoidea5.40.5
19African striped mouseRhabdomysomniRodentia8.7NA
20Mole ratSpalaxNARodentia10.62.4
21Arctic ground squirrelSpermophilusherbiRodentia16.6NA
22Musk shrewSuncusNASoricomorpha12.82
23Short-nosed echidnaTachyglossusinsectiMonotremata8.6NA
24Eastern american chipmunkTamiasherbiRodentia15.8NA
25Bottle-nosed dolphinTursiopscarniCetacea5.2NA
26Arctic foxVulpescarniCarnivora12.5NA
\n", "
\n", "
27 rows × 6 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "columns = f['name' : 'order', 'sleep_total' : 'sleep_rem']\n", "\n", "rows = (f[:] == None).rowany()\n", "\n", "DT[:, columns][rows, :]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "However, we are only interested in rows where the string columns are null; simply modifying the selected columns in `rows` should resolve this: " ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namegenusvoreordersleep_totalsleep_rem
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0Owl monkeyAotusomniPrimates171.8
1Three-toed slothBradypusherbiPilosa14.42.2
2Vesper mouseCalomysNARodentia7NA
3African giant pouched ratCricetomysomniRodentia8.32
4Western american chipmunkEutamiasherbiRodentia14.9NA
5GalagoGalagoomniPrimates9.81.1
6HumanHomoomniPrimates81.9
7MacaqueMacacaomniPrimates10.11.2
8VoleMicrotusherbiRodentia12.8NA
9Little brown batMyotisinsectiChiroptera19.92
10Slow lorisNyctibeuscarniPrimates11NA
11ChimpanzeePanomniPrimates9.71.4
12BaboonPapioomniPrimates9.41
13Desert hedgehogParaechinusNAErinaceomorpha10.32.7
14Deer mousePeromyscusNARodentia11.5NA
⋮⋮⋮⋮⋮⋮⋮
26Tree shrewTupaiaomniScandentia8.92.6
27Bottle-nosed dolphinTursiopscarniCetacea5.2NA
28GenetGenettacarniCarnivora6.31.3
29Arctic foxVulpescarniCarnivora12.5NA
30Red foxVulpescarniCarnivora9.82.4
\n", "
\n", "
31 rows × 6 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rows = (f[str] == None).rowany()\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "That doesn't seem right. There are no missing values in rows 0 and 1, same for rows 26 and 30. What's going on?\n", "\n", "As mentioned earlier, operations in `i` occur before `j`; in the code above, ALL the string columns in the frame were filtered, and not restricted to the selected columns. The right way about it is to select the columns first, then chain the row filtration:" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namegenusvoreordersleep_totalsleep_rem
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0Vesper mouseCalomysNARodentia7NA
1Desert hedgehogParaechinusNAErinaceomorpha10.32.7
2Deer mousePeromyscusNARodentia11.5NA
3PhalangerPhalangerNADiprotodontia13.71.8
4Rock hyraxProcaviaNAHyracoidea5.40.5
5Mole ratSpalaxNARodentia10.62.4
6Musk shrewSuncusNASoricomorpha12.82
\n", "
\n", "
7 rows × 6 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "DT[:, columns][rows, :]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Again, the filtration process depend on the context, and should be adapted accordingly." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Filter at" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "It is also possible to filter rows based on specific columns:" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesleep_totalsleep_rembrainwtbodywt
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0Thick-tailed opposum19.46.6NA0.37
1Giant armadillo18.16.10.08160
\n", "
\n", "
2 rows × 5 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "columns = f['name', 'sleep_total' : 'sleep_rem', 'brainwt' : 'bodywt']\n", "\n", "rows = (f['sleep_total', 'sleep_rem'] > 5).rowall()\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Note in the example above, the `rows` and `columns` are within the same bracket, because the columns are explicitly specified; only values from those columns will be used for the filtration.\n", "\n", "Another example below that uses a different select option:" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namesleep_totalsleep_rembrainwtbodywt
▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪▪
0Thick-tailed opposum19.46.6NA0.37
1Giant armadillo18.16.10.08160
\n", "
\n", "
2 rows × 5 columns
\n", "
\n", "
\n" ], "text/plain": [ "" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "columns = f['name', 'sleep_total' : 'sleep_rem', 'brainwt' : 'bodywt']\n", "\n", "rows = [name for name in DT[:, columns].names if 'sleep' in name]\n", "\n", "rows = (f[rows]>5).rowall()\n", "\n", "DT[rows, columns]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Resources: \n", "\n", "- [datatable docs](https://datatable.readthedocs.io/en/latest/)\n", "- Based on datatable version ``1.1``" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Comments\n", "" ] } ], "metadata": { "kernelspec": { "display_name": "playground", "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.18" }, "vscode": { "interpreter": { "hash": "abb565588fdc1e382cfb58576331719bbedcb5c72ffde2229575f41d5a748868" } } }, "nbformat": 4, "nbformat_minor": 4 }