examples/roux_lib_df.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7e9248c4-351f-4f36-a4ae-5bc5a7439af4",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "<a href=\"https://github.com/rraadd88/roux/blob/master/examples/roux_lib_df.ipynb\"><img align=\"right\" style=\"float:right;\" src=\"https://img.shields.io/badge/-source-cccccc?style=flat-square\"></a>\n",
    "\n",
    "## ⌗ Dataframes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1eea9136-b74a-4696-bbf0-a6f7cbc31fe5",
   "metadata": {},
   "source": [
    "**Optional requirements**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a0bf2146-8e18-400a-b425-41bc84db99ec",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# to show logging messages\n",
    "import logging\n",
    "logging.getLogger().setLevel(logging.INFO)\n",
    "## to create dfs for testing\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "724df139-64e7-4089-b796-2faa9d8893d1",
   "metadata": {},
   "source": [
    "## Import `r`oux-`d`ataframe attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "101b49f9-ff08-4f1b-8411-3d79d8996ba3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import roux.lib.dfs as rd #noqa"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d7ce6de-963e-4301-810d-a4099908060d",
   "metadata": {},
   "source": [
    "## Basic data checks "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5574071-03f7-4f5e-8fb7-70a4ba1cdf5a",
   "metadata": {},
   "source": [
    "**Demo data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "227c06e5-e100-43c1-affc-6f4f06b57b34",
   "metadata": {
    "tags": [
     "False"
    ]
   },
   "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>sepal_length</th>\n",
       "      <th>sepal_width</th>\n",
       "      <th>petal_length</th>\n",
       "      <th>petal_width</th>\n",
       "      <th>species</th>\n",
       "      <th>sepal_length with missing values</th>\n",
       "      <th>species id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sepal_length  sepal_width  petal_length  petal_width species  \\\n",
       "0           5.1          3.5           1.4          0.2  setosa   \n",
       "\n",
       "   sepal_length with missing values species id  \n",
       "0                               NaN     item00  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import seaborn as sns\n",
    "import numpy as np\n",
    "data=sns.load_dataset('iris')\n",
    "data=(\n",
    "    data\n",
    "    .assign(\n",
    "    **{\n",
    "        ## insert missing values \n",
    "        \"sepal_length with missing values\":lambda df: df['sepal_length'].apply(lambda x: np.nan if x>5 else x),\n",
    "        'species id': lambda df: df.groupby('species')['species'].transform(lambda x: [f\"item{i:02d}\" for i in range(len(x))])\n",
    "    }\n",
    "    )\n",
    ")\n",
    "data.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "070ca459-64fa-4268-9f51-8a2255ffb8fe",
   "metadata": {},
   "source": [
    "### Missing values "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f77b550b-4eeb-4893-9bbc-945d5829a278",
   "metadata": {},
   "source": [
    "Check the counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b3e8e07a-618c-4473-a316-9cc48a93cf0e",
   "metadata": {
    "tags": [
     "check_na"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sepal_length with missing values    78.666667\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.rd.check_na(subset=['sepal_length with missing values'],perc=True) # returns counts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5f6950f-1b46-48e6-93b8-0813539dc448",
   "metadata": {},
   "source": [
    ".. in chained operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6bfce007-d9b6-472e-9e0d-ac06a069899f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:sepal_length with missing values = 118\n"
     ]
    },
    {
     "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>sepal_length</th>\n",
       "      <th>sepal_width</th>\n",
       "      <th>petal_length</th>\n",
       "      <th>petal_width</th>\n",
       "      <th>species</th>\n",
       "      <th>sepal_length with missing values</th>\n",
       "      <th>species id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sepal_length  sepal_width  petal_length  petal_width species  \\\n",
       "0           5.1          3.5           1.4          0.2  setosa   \n",
       "\n",
       "   sepal_length with missing values species id  \n",
       "0                               NaN     item00  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(data\n",
    "    .rd.check_na(subset=['sepal_length with missing values'],out=False) # returns the original dataframe\n",
    "    .head(1) \n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5da8b4f-f21e-4aad-a1a3-66d1f9d20226",
   "metadata": {},
   "source": [
    "### Duplicates"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2219fde1-de33-455a-acda-a1f07ca0ed72",
   "metadata": {},
   "source": [
    "Check duplicate rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c0d79ebc-320c-4b5d-ab17-9436159681f0",
   "metadata": {
    "tags": [
     "check_dups"
    ]
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:duplicate rows: 94.0% (141/150)\n"
     ]
    },
    {
     "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>sepal_length</th>\n",
       "      <th>sepal_width</th>\n",
       "      <th>petal_length</th>\n",
       "      <th>petal_width</th>\n",
       "      <th>species</th>\n",
       "      <th>sepal_length with missing values</th>\n",
       "      <th>species id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>4.4</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>4.4</td>\n",
       "      <td>item38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>4.4</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>4.4</td>\n",
       "      <td>item42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4.4</td>\n",
       "      <td>2.9</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>4.4</td>\n",
       "      <td>item08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.6</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>4.6</td>\n",
       "      <td>item03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>4.6</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>4.6</td>\n",
       "      <td>item47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    sepal_length  sepal_width  petal_length  petal_width species  \\\n",
       "38           4.4          3.0           1.3          0.2  setosa   \n",
       "42           4.4          3.2           1.3          0.2  setosa   \n",
       "8            4.4          2.9           1.4          0.2  setosa   \n",
       "3            4.6          3.1           1.5          0.2  setosa   \n",
       "47           4.6          3.2           1.4          0.2  setosa   \n",
       "\n",
       "    sepal_length with missing values species id  \n",
       "38                               4.4     item38  \n",
       "42                               4.4     item42  \n",
       "8                                4.4     item08  \n",
       "3                                4.6     item03  \n",
       "47                               4.6     item47  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.rd.check_dups(subset='sepal_length').head() # returns duplicate rows"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1074889-a384-4de2-8366-a209da4df387",
   "metadata": {},
   "source": [
    "Check counts in chained operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d7d9a856-8e8b-4f28-a29f-17f505f65615",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:duplicate rows: 94.0% (141/150)\n"
     ]
    },
    {
     "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>sepal_length</th>\n",
       "      <th>sepal_width</th>\n",
       "      <th>petal_length</th>\n",
       "      <th>petal_width</th>\n",
       "      <th>species</th>\n",
       "      <th>sepal_length with missing values</th>\n",
       "      <th>species id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>4.9</td>\n",
       "      <td>item01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>4.7</td>\n",
       "      <td>item02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.6</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>4.6</td>\n",
       "      <td>item03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>5.0</td>\n",
       "      <td>item04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sepal_length  sepal_width  petal_length  petal_width species  \\\n",
       "0           5.1          3.5           1.4          0.2  setosa   \n",
       "1           4.9          3.0           1.4          0.2  setosa   \n",
       "2           4.7          3.2           1.3          0.2  setosa   \n",
       "3           4.6          3.1           1.5          0.2  setosa   \n",
       "4           5.0          3.6           1.4          0.2  setosa   \n",
       "\n",
       "   sepal_length with missing values species id  \n",
       "0                               NaN     item00  \n",
       "1                               4.9     item01  \n",
       "2                               4.7     item02  \n",
       "3                               4.6     item03  \n",
       "4                               5.0     item04  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(data\n",
    "    .rd.check_dups(subset='sepal_length',out=False) # returns the original dataframe\n",
    "    .head()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2ce5e616-4890-4575-9621-a5232345d7aa",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert data.shape==(150, 7), data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a35c6295-2075-4ea8-b4d6-0954bd6a0722",
   "metadata": {},
   "source": [
    "### Unique values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f92ed3b5-ae8f-49ed-b56a-7a5caac5bdd6",
   "metadata": {},
   "source": [
    "Check counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4b37af5f-82da-462c-8f97-2cf9ef6f68d3",
   "metadata": {
    "tags": [
     "check_nunique"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "species    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.rd.check_nunique(subset=['species']) # returns counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a71777e9-934b-43e2-b994-22945f010327",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "species\n",
       "versicolor    21\n",
       "virginica     21\n",
       "setosa        15\n",
       "Name: sepal_length, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.rd.check_nunique(subset='sepal_length', groupby='species') # .. grouped by a column/s"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81e37cf1-e894-4121-9a02-0561f8fb0ce2",
   "metadata": {},
   "source": [
    ".. in chained operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3f0d1712-3460-4113-b368-80bffd6f3a43",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:nunique: species = 3\n"
     ]
    },
    {
     "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>sepal_length</th>\n",
       "      <th>sepal_width</th>\n",
       "      <th>petal_length</th>\n",
       "      <th>petal_width</th>\n",
       "      <th>species</th>\n",
       "      <th>sepal_length with missing values</th>\n",
       "      <th>species id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sepal_length  sepal_width  petal_length  petal_width species  \\\n",
       "0           5.1          3.5           1.4          0.2  setosa   \n",
       "\n",
       "   sepal_length with missing values species id  \n",
       "0                               NaN     item00  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(data\n",
    "    .rd.check_nunique(subset='species',out=False).head(1) # returns the original dataframe\n",
    "    .head(1)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e3542c4-d5f4-4781-ac3a-2f463be8a607",
   "metadata": {},
   "source": [
    "## Validate"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9ee59aa-73ab-4f95-930d-82ff2264306e",
   "metadata": {},
   "source": [
    "### In chained operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5e0f2e20-4e1c-44ef-9e47-e8ca8263668c",
   "metadata": {},
   "outputs": [],
   "source": [
    "_=(data\n",
    "   .drop_duplicates() # preparing demo data\n",
    "   \n",
    "   # data validations\n",
    "   .rd.assert_no_na(subset=['sepal_length']) # validate no missing values\n",
    "   .rd.assert_no_dups() # validate no duplicates\n",
    "  )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a98fed3e-42ea-44f6-8f8f-b607505685e0",
   "metadata": {
    "tags": [
     "validate_no_dups"
    ]
   },
   "outputs": [],
   "source": [
    "_=(data\n",
    "   .drop_duplicates().drop(['sepal_length with missing values'],axis=1) # preparing demo data\n",
    "   \n",
    "   # data validations\n",
    "   .rd.assert_dense() # validate both no missing values and no duplicates\n",
    "  )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d719cd12-3fdc-42db-a2a1-fb9a722b58ab",
   "metadata": {},
   "source": [
    "## Logging\n",
    "### Changes in the dataframe shapes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ab950a40-6a8a-47e1-b214-c607ca4fb10e",
   "metadata": {},
   "outputs": [],
   "source": [
    "_=data.log.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c15d4a7-307e-47f5-b7a0-4a6785be08ae",
   "metadata": {},
   "source": [
    ".. in chained operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d1f38196-1363-489e-b330-eca4ad5fc3da",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:shape = (150, 7) nunique: sepal_length = 35 \n",
      "INFO:root:shape = (150, 7) by \"species\", nunique \"sepal_length\": versicolor = 21; virginica = 21; setosa = 15 \n",
      "INFO:root:shape = (150, 7) by 'species': setosa = 50; versicolor = 50; virginica = 50 \n"
     ]
    }
   ],
   "source": [
    "_=(data\n",
    "   .log.drop_duplicates() # logs the change in the shape of the dataframe (e.g. length)\n",
    "   .log('sepal_length') # counts of unique values\n",
    "   .log('sepal_length',groupby='species') # .. grouped by a column/s\n",
    "   .log(groupby='species') # .. grouped by a column/s\n",
    "  )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f187579-0d93-4c64-af0e-960fd495f508",
   "metadata": {},
   "source": [
    "## Infer\n",
    "### Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "0f09aad4-e4cd-4b8c-9f7c-d6cf15fdbdb4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['species id', 'species']"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols_id=data.rd.infer_index()\n",
    "cols_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "54336bcd-0fcd-429a-bccf-9f589785e34f",
   "metadata": {},
   "outputs": [],
   "source": [
    "## test if ids are unique\n",
    "_=data.rd.assert_no_dups(cols_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86adf064-18db-417c-8e8c-1ab95b1b83f9",
   "metadata": {},
   "source": [
    "## Transform  \n",
    "### Bin (`pd.cut`) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "af5039a9-edb4-415f-ae2c-5490756fe2c3",
   "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>sepal_length</th>\n",
       "      <th>sepal_width</th>\n",
       "      <th>petal_length</th>\n",
       "      <th>petal_width</th>\n",
       "      <th>species</th>\n",
       "      <th>sepal_length with missing values</th>\n",
       "      <th>species id</th>\n",
       "      <th>sepal_length bin</th>\n",
       "      <th>petal_length bin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item00</td>\n",
       "      <td>$\\leq$10</td>\n",
       "      <td>$\\leq$10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sepal_length  sepal_width  petal_length  petal_width species  \\\n",
       "0           5.1          3.5           1.4          0.2  setosa   \n",
       "\n",
       "   sepal_length with missing values species id sepal_length bin  \\\n",
       "0                               NaN     item00         $\\leq$10   \n",
       "\n",
       "  petal_length bin  \n",
       "0         $\\leq$10  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(data\n",
    "    .rd.get_bins(col='sepal_length',bins=[0,10,200])\n",
    "    .rd.get_bins(col='petal_length',bins=[0,10,200])\n",
    ").head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11502f1b-dcb2-428c-8def-7883b30bacbf",
   "metadata": {},
   "source": [
    "## Filtering/Subsetting"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5211d15f-b734-4075-bd59-c0244ad40165",
   "metadata": {},
   "source": [
    "### Queries with logging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "201889f9-cb36-483e-a253-0307aebd2162",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:shape = (150, 7) (init)\n",
      "INFO:root:query: shape changed: (150, 7)->(100, 7), width constant\n",
      "INFO:root:shape = (100, 7) by \"species\", nunique \"species id\": setosa = 50; virginica = 50 (`species`.str.endswith('a'))\n",
      "INFO:root:query: shape changed: (100, 7)->(71, 7), width constant\n",
      "INFO:root:shape = (71, 7) by \"species\", nunique \"species id\": virginica = 49; setosa = 22 (`sepal_length` > 5)\n",
      "INFO:root:query: shape changed: (71, 7)->(32, 7), width constant\n",
      "INFO:root:shape = (32, 7) by \"species\", nunique \"species id\": virginica = 32 (`sepal_width` <= 3)\n",
      "INFO:root:query: shape changed: (32, 7)->(6, 7), width constant\n",
      "INFO:root:shape = (6, 7) by \"species\", nunique \"species id\": virginica = 6 (~(`petal_length` < 6))\n"
     ]
    },
    {
     "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>sepal_length</th>\n",
       "      <th>sepal_width</th>\n",
       "      <th>petal_length</th>\n",
       "      <th>petal_width</th>\n",
       "      <th>species</th>\n",
       "      <th>sepal_length with missing values</th>\n",
       "      <th>species id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>7.6</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.6</td>\n",
       "      <td>2.1</td>\n",
       "      <td>virginica</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>7.3</td>\n",
       "      <td>2.9</td>\n",
       "      <td>6.3</td>\n",
       "      <td>1.8</td>\n",
       "      <td>virginica</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>7.7</td>\n",
       "      <td>2.6</td>\n",
       "      <td>6.9</td>\n",
       "      <td>2.3</td>\n",
       "      <td>virginica</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>7.7</td>\n",
       "      <td>2.8</td>\n",
       "      <td>6.7</td>\n",
       "      <td>2.0</td>\n",
       "      <td>virginica</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>7.4</td>\n",
       "      <td>2.8</td>\n",
       "      <td>6.1</td>\n",
       "      <td>1.9</td>\n",
       "      <td>virginica</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>7.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.1</td>\n",
       "      <td>2.3</td>\n",
       "      <td>virginica</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     sepal_length  sepal_width  petal_length  petal_width    species  \\\n",
       "105           7.6          3.0           6.6          2.1  virginica   \n",
       "107           7.3          2.9           6.3          1.8  virginica   \n",
       "118           7.7          2.6           6.9          2.3  virginica   \n",
       "122           7.7          2.8           6.7          2.0  virginica   \n",
       "130           7.4          2.8           6.1          1.9  virginica   \n",
       "135           7.7          3.0           6.1          2.3  virginica   \n",
       "\n",
       "     sepal_length with missing values species id  \n",
       "105                               NaN     item05  \n",
       "107                               NaN     item07  \n",
       "118                               NaN     item18  \n",
       "122                               NaN     item22  \n",
       "130                               NaN     item30  \n",
       "135                               NaN     item35  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.rd.query(\n",
    "    # data,\n",
    "    expr=(\"`species`.str.endswith('a') & `sepal_length` > 5 & `sepal_width` <= 3 & ~(`petal_length` < 6)\"),\n",
    "    subset='species id', # nunique\n",
    "    groupby='species',\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98b67ab0-2099-469c-a843-38dc6f74f173",
   "metadata": {},
   "source": [
    "### Chunking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "35629d0b-5a76-4a49-a160-15c39ffdf939",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:shape = (150, 8) nunique: chunk = 50 \n"
     ]
    },
    {
     "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>sepal_length</th>\n",
       "      <th>sepal_width</th>\n",
       "      <th>petal_length</th>\n",
       "      <th>petal_width</th>\n",
       "      <th>species</th>\n",
       "      <th>sepal_length with missing values</th>\n",
       "      <th>species id</th>\n",
       "      <th>chunk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sepal_length  sepal_width  petal_length  petal_width species  \\\n",
       "0           5.1          3.5           1.4          0.2  setosa   \n",
       "\n",
       "   sepal_length with missing values species id  chunk  \n",
       "0                               NaN     item00      0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## chunk of (max.) size\n",
    "data.rd.get_chunks(\n",
    "    size=3,\n",
    ").head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "0267ade2-8dc5-47ba-b7b8-6c7245202a30",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:shape = (150, 8) nunique: chunk = 4 \n"
     ]
    },
    {
     "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>sepal_length</th>\n",
       "      <th>sepal_width</th>\n",
       "      <th>petal_length</th>\n",
       "      <th>petal_width</th>\n",
       "      <th>species</th>\n",
       "      <th>sepal_length with missing values</th>\n",
       "      <th>species id</th>\n",
       "      <th>chunk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item00</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sepal_length  sepal_width  petal_length  petal_width species  \\\n",
       "0           5.1          3.5           1.4          0.2  setosa   \n",
       "\n",
       "   sepal_length with missing values species id  chunk  \n",
       "0                               NaN     item00      0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## n chunks\n",
    "data.rd.get_chunks(\n",
    "    n=4,    \n",
    ").head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1041327-080c-40b7-856a-5f032bced1c3",
   "metadata": {},
   "source": [
    "### Filter using a dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e58f67cb-d0e4-48dc-8e84-6333463ebfbd",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:(150, 7)\n",
      "INFO:root:(50, 7)\n"
     ]
    },
    {
     "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>sepal_length</th>\n",
       "      <th>sepal_width</th>\n",
       "      <th>petal_length</th>\n",
       "      <th>petal_width</th>\n",
       "      <th>species</th>\n",
       "      <th>sepal_length with missing values</th>\n",
       "      <th>species id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sepal_length  sepal_width  petal_length  petal_width species  \\\n",
       "0           5.1          3.5           1.4          0.2  setosa   \n",
       "\n",
       "   sepal_length with missing values species id  \n",
       "0                               NaN     item00  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out=data.rd.filter_rows({'species':'setosa'})\n",
    "out.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "80cb27a7-81fd-44f3-9a25-7bcaa0d87480",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert out.shape==(50, 7), out.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d29d48f8-7894-401e-ae2f-486beaa6a1be",
   "metadata": {},
   "source": [
    "### Triangle of adj. mat."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "3522f867-85bc-47bd-96a0-eccdf8af44f0",
   "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>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>0.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>0.3</td>\n",
       "      <td>0.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     a    b   c\n",
       "a  NaN  NaN NaN\n",
       "b  0.5  NaN NaN\n",
       "c  0.3  0.4 NaN"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import pandas as pd\n",
    "(\n",
    "    pd.DataFrame({'a':[1,.5,.3],'b':[.5,1,.4],'c':[.3,.4,1]},index=list('abc'))\n",
    "    .rd.tri(k=-1)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ab530f2-3fdb-4501-95e3-9cbb41f43e0b",
   "metadata": {},
   "source": [
    "## Groupby"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "093abc16-1fa7-4d4a-be86-0f606b16c876",
   "metadata": {},
   "source": [
    "### Sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "6baae4dd-9817-48d6-8c80-12772805cc49",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:sampled group name: setosa\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(50, 7)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.rd.groupby_sample('species').shape ## sample a group"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c0417ba-690e-4faf-bacc-ead905987324",
   "metadata": {},
   "source": [
    "### Aggregate nested groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "0b1b9dfb-8296-4dc7-b250-df3fae36f8ab",
   "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>species</th>\n",
       "      <th>part measurement</th>\n",
       "      <th>value</th>\n",
       "      <th>part</th>\n",
       "      <th>measurement</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>setosa</td>\n",
       "      <td>sepal_length</td>\n",
       "      <td>5.1</td>\n",
       "      <td>sepal</td>\n",
       "      <td>length</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  species part measurement  value   part measurement\n",
       "0  setosa     sepal_length    5.1  sepal      length"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## demo data\n",
    "data2=(data\n",
    "    .melt(id_vars=['species'],value_vars=[\"sepal_length\",\"sepal_width\",\"petal_length\",\"petal_width\"],var_name='part measurement')\n",
    "    .assign(\n",
    "        part=lambda df: df['part measurement'].str.split('_',expand=True)[0],\n",
    "        measurement=lambda df: df['part measurement'].str.split('_',expand=True)[1],\n",
    "    )\n",
    "    )\n",
    "data2.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "629eb533-df4b-4a13-a2d2-bfa15ffce414",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:shape = (600, 5) nunique: part measurement = 4; part = 2; measurement = 2 \n",
      "/Users/rohandandage/Documents/work/code/roux/roux/lib/df.py:1543: FutureWarning: The provided callable <function mean at 0x107fd8790> is currently using SeriesGroupBy.mean. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string \"mean\" instead.\n",
      "  df2.groupby(cols_groupby)\n",
      "WARNING:root:please use label= instead of suffix= in the future.\n",
      "INFO:root:shape = (12, 5) after a round of aggregation.\n",
      "/Users/rohandandage/Documents/work/code/roux/roux/lib/df.py:1543: FutureWarning: The provided callable <function mean at 0x107fd8790> is currently using SeriesGroupBy.mean. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string \"mean\" instead.\n",
      "  df2.groupby(cols_groupby)\n",
      "WARNING:root:please use label= instead of suffix= in the future.\n",
      "INFO:root:shape = (6, 3) after a round of aggregation.\n",
      "/Users/rohandandage/Documents/work/code/roux/roux/lib/df.py:1543: FutureWarning: The provided callable <function mean at 0x107fd8790> is currently using SeriesGroupBy.mean. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string \"mean\" instead.\n",
      "  df2.groupby(cols_groupby)\n",
      "WARNING:root:please use label= instead of suffix= in the future.\n",
      "INFO:root:shape = (3, 2) after a round of aggregation.\n"
     ]
    },
    {
     "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>species</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>setosa</td>\n",
       "      <td>2.5355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>3.5730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>virginica</td>\n",
       "      <td>4.2850</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      species   value\n",
       "0      setosa  2.5355\n",
       "1  versicolor  3.5730\n",
       "2   virginica  4.2850"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## aggregate\n",
    "data2.rd.groupby_agg_nested(\n",
    "    groupby=['species'], # groupby columns\n",
    "    subset=[\"part measurement\",\"part\",\"measurement\"], ## columns with nested groups\n",
    "    func={\"value\": np.mean}, ## column with values and agg function to apply\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc5025a6-2033-4424-9832-b243be2200c8",
   "metadata": {},
   "source": [
    "### Aggregate ranges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "3cdf90c5-6bc0-4b36-a32a-d8e6dd58875d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:setting consecutive values as ranges ..\n"
     ]
    },
    {
     "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>start min</th>\n",
       "      <th>start max</th>\n",
       "      <th>values sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   start min  start max  values sum\n",
       "0          0          0           0\n",
       "1          2          2           2\n",
       "2          5          5           5\n",
       "3          7          8          15\n",
       "4         10         11          21"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import pandas as pd\n",
    "pd.DataFrame(\n",
    "    dict(\n",
    "        start=[0,2,5,7,8,10,11],\n",
    "        values=[0,2,5,7,8,10,11],\n",
    "    )\n",
    ").rd.to_ranges(\n",
    "    colindex='start',\n",
    "    agg={\n",
    "        f\"values sum\": ('values','sum'),\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "157ee620-75cc-4cef-a60c-057eb4ce6686",
   "metadata": {},
   "source": [
    "## Merge"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21e57b97-a214-4232-bbc5-192d4df59b22",
   "metadata": {},
   "source": [
    "**Demo data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "f43617b5-67fa-4960-8929-103089a5379c",
   "metadata": {},
   "outputs": [],
   "source": [
    "data2=data.groupby('species').head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39d7af63-ed1f-445d-9a7e-bcfc91ef54f6",
   "metadata": {},
   "source": [
    "### Validation of changes in table shapes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "5d37292f-faff-4d86-b635-5e9fff62a457",
   "metadata": {
    "tags": [
     "merge"
    ]
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:merge: shape changed: (150, 7)->(150, 13), length constant\n"
     ]
    },
    {
     "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>sepal_length_x</th>\n",
       "      <th>sepal_width_x</th>\n",
       "      <th>petal_length_x</th>\n",
       "      <th>petal_width_x</th>\n",
       "      <th>species</th>\n",
       "      <th>sepal_length with missing values_x</th>\n",
       "      <th>species id_x</th>\n",
       "      <th>sepal_length_y</th>\n",
       "      <th>sepal_width_y</th>\n",
       "      <th>petal_length_y</th>\n",
       "      <th>petal_width_y</th>\n",
       "      <th>sepal_length with missing values_y</th>\n",
       "      <th>species id_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item00</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>item00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sepal_length_x  sepal_width_x  petal_length_x  petal_width_x species  \\\n",
       "0             5.1            3.5             1.4            0.2  setosa   \n",
       "\n",
       "   sepal_length with missing values_x species id_x  sepal_length_y  \\\n",
       "0                                 NaN       item00             5.1   \n",
       "\n",
       "   sepal_width_y  petal_length_y  petal_width_y  \\\n",
       "0            3.5             1.4            0.2   \n",
       "\n",
       "   sepal_length with missing values_y species id_y  \n",
       "0                                 NaN       item00  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out=data.log.merge( # logs the changes in the shapes of the dataframe\n",
    "    right=data2,\n",
    "    how='inner',\n",
    "    on='species',\n",
    "    validate='m:1',\n",
    "    validate_equal_length=True,\n",
    "    # validate_no_decrease_length=True,\n",
    "    )\n",
    "out.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "5d22cc43-9a74-475e-8b57-2d5e3187e2f7",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert out.shape==(150, 13), out.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fe608dc-7377-450f-8aea-00093a71e068",
   "metadata": {},
   "source": [
    "## Melt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cdd4c2e-d6ad-45b9-8326-76270a76728c",
   "metadata": {},
   "source": [
    "**Demo data with paired IDs and values**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "5091f06d-cb67-4b5f-a75d-f6139e352e51",
   "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>id 1</th>\n",
       "      <th>id 2</th>\n",
       "      <th>value 1</th>\n",
       "      <th>value 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>135</td>\n",
       "      <td>5.8</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>98</td>\n",
       "      <td>51</td>\n",
       "      <td>5.1</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>75</td>\n",
       "      <td>74</td>\n",
       "      <td>6.6</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>133</td>\n",
       "      <td>5.4</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>131</td>\n",
       "      <td>18</td>\n",
       "      <td>7.9</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id 1  id 2  value 1  value 2\n",
       "0    14   135      5.8      4.0\n",
       "1    98    51      5.1      2.5\n",
       "2    75    74      6.6      3.0\n",
       "3    16   133      5.4      3.9\n",
       "4   131    18      7.9      3.8"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.random.seed(1)\n",
    "data=(\n",
    "    data\n",
    "    .assign(\n",
    "    **{\n",
    "        ## create paired ids\n",
    "        \"id 1\":lambda df: range(len(df)),\n",
    "        \"id 2\":lambda df: range(len(df))[::-1],\n",
    "    }\n",
    "    )\n",
    "    .rename(\n",
    "        columns={\n",
    "            \"sepal_length\": \"value 1\",\n",
    "            \"sepal_width\": \"value 2\",\n",
    "            \n",
    "        },\n",
    "        errors='raise',\n",
    "    )\n",
    "    .loc[:,['id 1','id 2','value 1','value 2']]\n",
    ").sample(5).reset_index(drop=True)\n",
    "## example order\n",
    "order=list(set(data['id 1'].tolist()+data['id 2'].tolist()))\n",
    "order=list(np.random.choice(order,len(order),replace=False))\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "200f59d6-0ac7-4d94-bfec-81af35ccdc59",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert data.shape==(5,4), data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "277ee731-27c3-4b30-a137-648e4947ce97",
   "metadata": {},
   "source": [
    "### Tables with paired IDs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "a9df2d62-b717-4ef7-af3e-9d631b598eef",
   "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>suffix</th>\n",
       "      <th>id</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>98</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>75</td>\n",
       "      <td>6.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>5.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>131</td>\n",
       "      <td>7.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>135</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>51</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>74</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>133</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>18</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  suffix   id  value\n",
       "0      1   14    5.8\n",
       "1      1   98    5.1\n",
       "2      1   75    6.6\n",
       "3      1   16    5.4\n",
       "4      1  131    7.9\n",
       "0      2  135    4.0\n",
       "1      2   51    2.5\n",
       "2      2   74    3.0\n",
       "3      2  133    3.9\n",
       "4      2   18    3.8"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out=data.rd.melt_paired(suffixes=['1','2'])\n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "fef4ea9d-1695-41f1-a0d0-03cd2405f790",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert out.shape==(10,3), out.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cce9d730-e7da-4933-8a64-695ccd9a7328",
   "metadata": {},
   "source": [
    "## Sort "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e844f71-40b9-449b-ac75-f077624e15f8",
   "metadata": {},
   "source": [
    "### Paired columns by values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "abc3ba52-7b0a-4d99-9bf2-36770f2d2da8",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:suffixes inferred: ['1', '2']\n",
      "INFO:root:(equal, sorted) items: {(False, False): 2, (False, True): 3}\n"
     ]
    },
    {
     "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>equal</th>\n",
       "      <th>sorted</th>\n",
       "      <th>id 1</th>\n",
       "      <th>id 2</th>\n",
       "      <th>value 1</th>\n",
       "      <th>value 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>14</td>\n",
       "      <td>135</td>\n",
       "      <td>5.8</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>16</td>\n",
       "      <td>133</td>\n",
       "      <td>5.4</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>51</td>\n",
       "      <td>98</td>\n",
       "      <td>2.5</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>74</td>\n",
       "      <td>75</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>18</td>\n",
       "      <td>131</td>\n",
       "      <td>3.8</td>\n",
       "      <td>7.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   equal  sorted  id 1  id 2  value 1  value 2\n",
       "0  False   False    14   135      5.8      4.0\n",
       "3  False   False    16   133      5.4      3.9\n",
       "1  False    True    51    98      2.5      5.1\n",
       "2  False    True    74    75      3.0      6.6\n",
       "4  False    True    18   131      3.8      7.9"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out=data.rd.sort_columns_by_values(['id 1','id 2'])\n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "a8f3ac94-11d8-417e-a868-4eefe9333e0b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert out['id 1'].tolist()==[14, 16, 51, 74, 18], out['id 1'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18550b2d-9f8f-4272-9174-58ad68bb2714",
   "metadata": {},
   "source": [
    "### Paired columns by order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "f17b3173-0b91-4372-8d28-77627605442e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[133, 135, 14, 51, 131, 18, 75, 16, 98, 74]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "a4a5535a-a80b-4d55-a603-aaf1f8f79cb5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:suffixes inferred: ['1', '2']\n",
      "INFO:root:(equal, sorted) items: {(False, False): 2, (False, True): 3}\n"
     ]
    },
    {
     "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>id 1</th>\n",
       "      <th>id 2</th>\n",
       "      <th>value 1</th>\n",
       "      <th>value 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>75</td>\n",
       "      <td>74</td>\n",
       "      <td>6.6</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>131</td>\n",
       "      <td>18</td>\n",
       "      <td>7.9</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>135</td>\n",
       "      <td>14</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51</td>\n",
       "      <td>98</td>\n",
       "      <td>2.5</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>133</td>\n",
       "      <td>16</td>\n",
       "      <td>3.9</td>\n",
       "      <td>5.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id 1  id 2  value 1  value 2\n",
       "2    75    74      6.6      3.0\n",
       "4   131    18      7.9      3.8\n",
       "0   135    14      4.0      5.8\n",
       "1    51    98      2.5      5.1\n",
       "3   133    16      3.9      5.4"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out=data.rd.sort_columns_by_values(\n",
    "    ['id 1','id 2'],\n",
    "    order=order, # order of the ids\n",
    "    clean=True,\n",
    ")\n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "5c3a866b-2b08-4763-b45e-5fcfb79de9a4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert out['id 1'].tolist()==[75, 131, 135, 51, 133], out['id 1'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c255b12b-ea41-4807-8c87-b61e2ecfb2d4",
   "metadata": {},
   "source": [
    "## Mapping between columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc6d75d2-e513-42c2-87d3-e9dc455b2e01",
   "metadata": {},
   "source": [
    "**Demo data**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "79e7667e-3378-4961-afe1-987ee1544ee6",
   "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>id 1</th>\n",
       "      <th>id 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>98</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>75</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>131</td>\n",
       "      <td>131</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id 1  id 2\n",
       "0    14    16\n",
       "1    98    98\n",
       "2    75    98\n",
       "3    16   131\n",
       "4   131   131"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data=(\n",
    "    data\n",
    "    .loc[:,['id 1']].head(10)\n",
    "    .assign(\n",
    "    **{\n",
    "        ## create shuffled ids\n",
    "        \"id 2\":lambda df: np.random.choice(df['id 1'],len(df)),\n",
    "    }\n",
    "    )\n",
    ")\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29b3c995-ea4e-49cc-bb4d-827910c8e67b",
   "metadata": {},
   "source": [
    "### Check counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "2a1604c7-7dbb-4f0e-9f5a-d724ccf89aa6",
   "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></th>\n",
       "      <th></th>\n",
       "      <th>mappings count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mapping</th>\n",
       "      <th>id 1 count</th>\n",
       "      <th>id 2 count</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1:1</th>\n",
       "      <th>1</th>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>m:1</th>\n",
       "      <th>2</th>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               mappings count\n",
       "mapping id 1 count id 2 count                \n",
       "1:1     1          1                        1\n",
       "m:1     2          1                        4"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out=data.rd.check_mappings(subset=['id 1','id 2'])\n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "e17f4ca7-c1f6-43d4-b5df-19b143c2317b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert out['mappings count'].tolist()==[1, 4], out['mappings count'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45b0aab8-27ad-49a3-a898-ca0853d6dfab",
   "metadata": {},
   "source": [
    "### Classify"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "ea0cf67a-9a7a-493b-94bc-e2388e6a24a9",
   "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></th>\n",
       "      <th></th>\n",
       "      <th>id 1</th>\n",
       "      <th>id 2</th>\n",
       "      <th>id 1 count</th>\n",
       "      <th>id 2 count</th>\n",
       "      <th>mapping</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>2</th>\n",
       "      <th>2</th>\n",
       "      <td>75</td>\n",
       "      <td>98</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>1</th>\n",
       "      <td>98</td>\n",
       "      <td>98</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>1</th>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>131</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <th>4</th>\n",
       "      <td>131</td>\n",
       "      <td>131</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id 1  id 2  id 1 count  id 2 count mapping\n",
       "0 0 0    14    16           1           1     1:1\n",
       "1 2 2    75    98           2           1     m:1\n",
       "  3 1    98    98           2           1     m:1\n",
       "2 1 3    16   131           2           1     m:1\n",
       "  4 4   131   131           2           1     m:1"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out=data.rd.classify_mappings(subset=['id 1','id 2'])\n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "a756bdb3-9df0-4822-bb1b-671ed87aa54d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert out['mapping'].tolist()==['1:1', 'm:1', 'm:1', 'm:1', 'm:1'], out['mapping'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2adafe5d-7dfa-480d-a549-cbef94a2ec26",
   "metadata": {},
   "source": [
    "### Filter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "e4edb3f3-2148-4022-a5b1-333ea5dee256",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:query: shape changed: (5, 5)->(4, 5), width constant\n"
     ]
    },
    {
     "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",
       "      <th>id 1</th>\n",
       "      <th>id 2</th>\n",
       "      <th>id 1 count</th>\n",
       "      <th>id 2 count</th>\n",
       "      <th>mapping</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>2</th>\n",
       "      <th>2</th>\n",
       "      <td>75</td>\n",
       "      <td>98</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>1</th>\n",
       "      <td>98</td>\n",
       "      <td>98</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>1</th>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>131</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <th>4</th>\n",
       "      <td>131</td>\n",
       "      <td>131</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id 1  id 2  id 1 count  id 2 count mapping\n",
       "1 2 2    75    98           2           1     m:1\n",
       "  3 1    98    98           2           1     m:1\n",
       "2 1 3    16   131           2           1     m:1\n",
       "  4 4   131   131           2           1     m:1"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out=data.rd.get_mappings(\n",
    "    subset=['id 1','id 2'],\n",
    "    keep=\"2:1\", # keep 3:1 mappings\n",
    "    clean=False,\n",
    ")\n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "c937c4bb-7481-4838-8f20-79250ad1accc",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert out.shape==(4,5), out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "e7d14384-51a1-43a7-939c-3949929d01aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:query: shape changed: (5, 3)->(1, 3), width constant\n"
     ]
    },
    {
     "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",
       "      <th>id 1</th>\n",
       "      <th>id 2</th>\n",
       "      <th>mapping</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>16</td>\n",
       "      <td>1:1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id 1  id 2 mapping\n",
       "0 0 0    14    16     1:1"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out=data.rd.get_mappings(\n",
    "    subset=['id 1','id 2'],\n",
    "    keep='1:1', ## keep 1:1 mappings\n",
    "    clean=True,\n",
    "    )\n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "7cbafddb-d518-4ee2-b174-85c41bf028b3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert out['mapping'].tolist()==['1:1'], out['mapping'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33e8413f-7e60-418a-a49b-6a57d38e4f4c",
   "metadata": {},
   "source": [
    "### Validate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "8c8b5c77",
   "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></th>\n",
       "      <th></th>\n",
       "      <th>id 1</th>\n",
       "      <th>id 2</th>\n",
       "      <th>id 1 count</th>\n",
       "      <th>id 2 count</th>\n",
       "      <th>mapping</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>2</th>\n",
       "      <th>2</th>\n",
       "      <td>75</td>\n",
       "      <td>98</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>1</th>\n",
       "      <td>98</td>\n",
       "      <td>98</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>1</th>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>131</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <th>4</th>\n",
       "      <td>131</td>\n",
       "      <td>131</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>m:1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id 1  id 2  id 1 count  id 2 count mapping\n",
       "0 0 0    14    16           1           1     1:1\n",
       "1 2 2    75    98           2           1     m:1\n",
       "  3 1    98    98           2           1     m:1\n",
       "2 1 3    16   131           2           1     m:1\n",
       "  4 4   131   131           2           1     m:1"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.rd.classify_mappings(subset=['id 1','id 2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "5afa85b4-9a46-4b54-949f-27bb3c40d24d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for testing\n",
    "assert len(data)==5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "7acaab5b-b03e-4b4b-b166-70f4e8fecc8f",
   "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>id 1</th>\n",
       "      <th>id 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id 1  id 2\n",
       "0    14    16"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head(1).rd.assert_1_1_mappings(subset=['id 1','id 2'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d36c595a-ab8c-413e-b6d7-e6fc08ccd0f9",
   "metadata": {},
   "source": [
    "## Documentation\n",
    "[`roux.lib.df`](https://github.com/rraadd88/roux#module-rouxlibdf)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "datasets",
   "language": "python",
   "name": "datasets"
  },
  "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}