groupby-lambda-DataFrame.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#-------------------\n",
    "# Author: Iman Irajian\n",
    "# \t\t  iman.irajian@gmail.com\n",
    "# Subject: A closer look at groupby() and lambda on the Pandas DataFrame\n",
    "#\n",
    "# Date: Thursday, January 21, 2021\n",
    "#-------------------"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "products_raw = {\"Apple\": {\"price\":1000, \"weight\":10},\n",
    "                \"Camera\": {\"price\":80000, \"weight\":100},\n",
    "                \"Laptop\": {\"price\":901000, \"weight\":4500},\n",
    "                \"Watch\": {\"price\":70100, \"weight\":90},\n",
    "                \"Bag\": {\"price\":4000, \"weight\":50}}\n",
    "\n",
    "transaction_raw = {0: {\"product\":\"Apple\", \"count\":3, \"discount\":500, \"date\":\"01-21-2021\"},\n",
    "                   1: {\"product\":\"Camera\", \"count\":1, \"discount\":500, \"date\":\"11-20-2020\"},\n",
    "                   2: {\"product\":\"Apple\", \"count\":20, \"discount\":1000, \"date\":\"01-03-2021\"},\n",
    "                   3: {\"product\":\"Bag\", \"count\":2, \"discount\":0, \"date\":\"01-04-2020\"},\n",
    "                   4: {\"product\":\"Bag\", \"count\":2, \"discount\":100, \"date\":\"01-05-2021\"},\n",
    "                   5: {\"product\":\"Bag\", \"count\":6, \"discount\":1000, \"date\":\"01-10-2021\"},\n",
    "                   6: {\"product\":\"Watch\", \"count\":1, \"discount\":100, \"date\":\"05-18-2020\"},\n",
    "                   7: {\"product\":\"Laptop\", \"count\":1, \"discount\":0, \"date\":\"01-16-2021\"},\n",
    "                   8: {\"product\":\"Camera\", \"count\":4, \"discount\":9000, \"date\":\"08-08-2020\"}}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         price  weight\n",
      "Apple     1000      10\n",
      "Camera   80000     100\n",
      "Laptop  901000    4500\n",
      "Watch    70100      90\n",
      "Bag       4000      50\n"
     ]
    }
   ],
   "source": [
    "products = pd.DataFrame(products_raw).T\n",
    "print( products )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  product count discount        date\n",
      "0   Apple     3      500  01-21-2021\n",
      "1  Camera     1      500  11-20-2020\n",
      "2   Apple    20     1000  01-03-2021\n",
      "3     Bag     2        0  01-04-2020\n",
      "4     Bag     2      100  01-05-2021\n",
      "5     Bag     6     1000  01-10-2021\n",
      "6   Watch     1      100  05-18-2020\n",
      "7  Laptop     1        0  01-16-2021\n",
      "8  Camera     4     9000  08-08-2020\n"
     ]
    }
   ],
   "source": [
    "transactions = pd.DataFrame(transaction_raw).T\n",
    "print( transactions )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         price  weight\n",
      "Apple     1000      10\n",
      "Bag       4000      50\n",
      "Watch    70100      90\n",
      "Camera   80000     100\n",
      "Laptop  901000    4500\n"
     ]
    }
   ],
   "source": [
    "# sort products by price\n",
    "# Notes: \n",
    "#       1- By default ascending=True\n",
    "#       2- Sort is outplace, i.e., after sorting products remain unchanged\n",
    "#          You can set inplace=True if you want\n",
    "print( products.sort_values(by=\"price\") )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         price  weight\n",
      "Laptop  901000    4500\n",
      "Camera   80000     100\n",
      "Watch    70100      90\n",
      "Bag       4000      50\n",
      "Apple     1000      10\n"
     ]
    }
   ],
   "source": [
    "# sort products by price descendingly\n",
    "print( products.sort_values(by=\"price\", ascending=False) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         price  weight\n",
      "Apple     1000      10\n",
      "Bag       4000      50\n",
      "Camera   80000     100\n",
      "Laptop  901000    4500\n",
      "Watch    70100      90\n"
     ]
    }
   ],
   "source": [
    "# Lets sort by print(index\n",
    "print( products.sort_index() )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  product count discount        date\n",
      "0   Apple     3      500  01-21-2021\n",
      "1  Camera     1      500  11-20-2020\n",
      "2   Apple    20     1000  01-03-2021\n",
      "3     Bag     2        0  01-04-2020\n",
      "4     Bag     2      100  01-05-2021\n",
      "5     Bag     6     1000  01-10-2021\n",
      "6   Watch     1      100  05-18-2020\n",
      "7  Laptop     1        0  01-16-2021\n",
      "8  Camera     4     9000  08-08-2020\n"
     ]
    }
   ],
   "source": [
    "print( transactions )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5\n",
      "5\n"
     ]
    }
   ],
   "source": [
    "# How many distinct transactions are according to the names of products?\n",
    "print( len( transactions.groupby(\"product\") ) ) \n",
    "print( transactions.groupby(\"product\").ngroups )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "product\n",
      "Apple     23\n",
      "Bag       10\n",
      "Camera     5\n",
      "Laptop     1\n",
      "Watch      1\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# How many times each products have been sold?\n",
    "print( transactions.groupby(\"product\").sum()[\"count\"] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "product\n",
      "Apple     1000\n",
      "Bag       1000\n",
      "Camera    9000\n",
      "Laptop       0\n",
      "Watch      100\n",
      "Name: discount, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Maximum discount for each products?\n",
    "print( transactions.groupby(\"product\").max()[\"discount\"] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         count  discount        date\n",
      "product                             \n",
      "Apple        3       500  01-03-2021\n",
      "Bag          2         0  01-04-2020\n",
      "Camera       1       500  08-08-2020\n",
      "Laptop       1         0  01-16-2021\n",
      "Watch        1       100  05-18-2020\n",
      "         count  discount        date\n",
      "product                             \n",
      "Apple        3       500  01-03-2021\n",
      "Bag          2         0  01-04-2020\n",
      "Camera       1       500  08-08-2020\n",
      "Laptop       1         0  01-16-2021\n",
      "Watch        1       100  05-18-2020\n",
      "         count  discount        date\n",
      "product                             \n",
      "Apple        3       500  01-03-2021\n",
      "Bag          2         0  01-04-2020\n",
      "Camera       1       500  08-08-2020\n",
      "Laptop       1         0  01-16-2021\n",
      "Watch        1       100  05-18-2020\n",
      "         count  discount        date\n",
      "product                             \n",
      "Apple        3       500  01-03-2021\n",
      "Bag          2         0  01-04-2020\n",
      "Camera       1       500  08-08-2020\n",
      "Laptop       1         0  01-16-2021\n",
      "Watch        1       100  05-18-2020\n"
     ]
    }
   ],
   "source": [
    "# Minimum of each feature with respect to distinct product names\n",
    "print( transactions.groupby(\"product\").min() )\n",
    "# or\n",
    "print( transactions.groupby(\"product\").agg(np.min) ) # or min\n",
    "# or\n",
    "print( transactions.groupby(\"product\").agg(\"min\") )\n",
    "# or\n",
    "print( transactions.groupby(\"product\").aggregate(\"min\") )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  product count discount        date   price  weight\n",
      "0   Apple     3      500  01-21-2021    1000      10\n",
      "1  Camera     1      500  11-20-2020   80000     100\n",
      "2   Apple    20     1000  01-03-2021    1000      10\n",
      "3     Bag     2        0  01-04-2020    4000      50\n",
      "4     Bag     2      100  01-05-2021    4000      50\n",
      "5     Bag     6     1000  01-10-2021    4000      50\n",
      "6   Watch     1      100  05-18-2020   70100      90\n",
      "7  Laptop     1        0  01-16-2021  901000    4500\n",
      "8  Camera     4     9000  08-08-2020   80000     100\n",
      "Apple:\t 21500\n",
      "Bag:\t 38900\n",
      "Camera:\t 390500\n",
      "Laptop:\t 901000\n",
      "Watch:\t 70000\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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total price of each product\n",
    "# We need to join products to see the price of each product\n",
    "trans_prods = transactions.join(products, on=\"product\")\n",
    "print(trans_prods)\n",
    "\n",
    "# Then times each product count with price subtract discount\n",
    "trans_prods.groupby(\"product\").apply(lambda g: print( g[\"product\"].values[0] + \":\\t\", (g[\"count\"]*g[\"price\"]-g[\"discount\"]).sum() ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  product count discount        date   price  weight\n",
      "0   Apple     3      500  01-21-2021    1000      10\n",
      "1  Camera     1      500  11-20-2020   80000     100\n",
      "2   Apple    20     1000  01-03-2021    1000      10\n",
      "3     Bag     2        0  01-04-2020    4000      50\n",
      "4     Bag     2      100  01-05-2021    4000      50\n",
      "5     Bag     6     1000  01-10-2021    4000      50\n",
      "6   Watch     1      100  05-18-2020   70100      90\n",
      "7  Laptop     1        0  01-16-2021  901000    4500\n",
      "8  Camera     4     9000  08-08-2020   80000     100\n"
     ]
    }
   ],
   "source": [
    "print( transactions.join(products, on=\"product\") )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "product\n",
      "Apple      230\n",
      "Bag        500\n",
      "Camera     500\n",
      "Laptop    4500\n",
      "Watch       90\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Pass custom function to calculate total weight for each transaction\n",
    "def get_total_weight(group):\n",
    "    return np.sum(group[\"count\"])*group[\"weight\"].iloc[0]\n",
    "    \n",
    "print( transactions.join(products, on=\"product\").groupby(\"product\").apply(get_total_weight) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "product\n",
      "Apple     1000\n",
      "Bag       1000\n",
      "Camera    9000\n",
      "Laptop       0\n",
      "Watch      100\n",
      "Name: discount, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Maximum discount for each products?\n",
    "print( transactions.groupby(\"product\").max()[\"discount\"] )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count\n",
      "count\n",
      "count\n",
      "count\n",
      "count\n",
      "discount\n",
      "discount\n",
      "discount\n",
      "discount\n",
      "discount\n",
      "date\n",
      "date\n",
      "date\n",
      "date\n",
      "date\n",
      "         count  discount  date\n",
      "product                       \n",
      "Apple        5         8     4\n",
      "Bag          5         8     4\n",
      "Camera       5         8     4\n",
      "Laptop       5         8     4\n",
      "Watch        5         8     4\n"
     ]
    }
   ],
   "source": [
    "# Think about the output\n",
    "def f(column):\n",
    "    print(column.name)\n",
    "    return len(column.name)\n",
    "    \n",
    "print( transactions.groupby(\"product\").agg(f) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  product count discount        date\n",
      "0   Apple     3      500  01-21-2021\n",
      "2   Apple    20     1000  01-03-2021\n",
      "3     Bag     2        0  01-04-2020\n",
      "4     Bag     2      100  01-05-2021\n",
      "5     Bag     6     1000  01-10-2021\n",
      "1  Camera     1      500  11-20-2020\n",
      "8  Camera     4     9000  08-08-2020\n",
      "7  Laptop     1        0  01-16-2021\n",
      "6   Watch     1      100  05-18-2020\n",
      "-----\n",
      "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x0000020CD59AF1F0>\n",
      "-----\n",
      "Apple :\n",
      "  product count discount        date\n",
      "0   Apple     3      500  01-21-2021\n",
      "2   Apple    20     1000  01-03-2021\n",
      "Bag :\n",
      "  product count discount        date\n",
      "3     Bag     2        0  01-04-2020\n",
      "4     Bag     2      100  01-05-2021\n",
      "5     Bag     6     1000  01-10-2021\n",
      "Camera :\n",
      "  product count discount        date\n",
      "1  Camera     1      500  11-20-2020\n",
      "8  Camera     4     9000  08-08-2020\n",
      "Laptop :\n",
      "  product count discount        date\n",
      "7  Laptop     1        0  01-16-2021\n",
      "Watch :\n",
      "  product count discount        date\n",
      "6   Watch     1      100  05-18-2020\n",
      "\n",
      "  product count discount        date\n",
      "0   Apple     3      500  01-21-2021\n",
      "2   Apple    20     1000  01-03-2021\n",
      "  product count discount        date\n",
      "3     Bag     2        0  01-04-2020\n",
      "4     Bag     2      100  01-05-2021\n",
      "5     Bag     6     1000  01-10-2021\n",
      "  product count discount        date\n",
      "1  Camera     1      500  11-20-2020\n",
      "8  Camera     4     9000  08-08-2020\n",
      "  product count discount        date\n",
      "7  Laptop     1        0  01-16-2021\n",
      "  product count discount        date\n",
      "6   Watch     1      100  05-18-2020\n",
      "\n",
      "  product count discount        date\n",
      "0   Apple     3      500  01-21-2021\n",
      "2   Apple    20     1000  01-03-2021\n",
      "  product count discount        date\n",
      "3     Bag     2        0  01-04-2020\n",
      "4     Bag     2      100  01-05-2021\n",
      "5     Bag     6     1000  01-10-2021\n",
      "  product count discount        date\n",
      "1  Camera     1      500  11-20-2020\n",
      "8  Camera     4     9000  08-08-2020\n",
      "  product count discount        date\n",
      "7  Laptop     1        0  01-16-2021\n",
      "  product count discount        date\n",
      "6   Watch     1      100  05-18-2020\n",
      "-----\n",
      "  product count discount        date\n",
      "0   Apple     3      500  01-21-2021\n",
      "2   Apple    20     1000  01-03-2021\n",
      "3     Bag     2        0  01-04-2020\n",
      "4     Bag     2      100  01-05-2021\n",
      "5     Bag     6     1000  01-10-2021\n",
      "1  Camera     1      500  11-20-2020\n",
      "8  Camera     4     9000  08-08-2020\n",
      "7  Laptop     1        0  01-16-2021\n",
      "6   Watch     1      100  05-18-2020\n",
      "  product count discount        date\n",
      "0   Apple     3      500  01-21-2021\n",
      "2   Apple    20     1000  01-03-2021\n",
      "3     Bag     2        0  01-04-2020\n",
      "4     Bag     2      100  01-05-2021\n",
      "5     Bag     6     1000  01-10-2021\n",
      "1  Camera     1      500  11-20-2020\n",
      "8  Camera     4     9000  08-08-2020\n",
      "7  Laptop     1        0  01-16-2021\n",
      "6   Watch     1      100  05-18-2020\n"
     ]
    }
   ],
   "source": [
    "# Putting all together\n",
    "# Sort transaction by name, then (same names) by count, then (same names and count) by discount (ascendingly)\n",
    "\n",
    "# We want this result:\n",
    "target = transactions.sort_values(by=[\"product\", \"count\", \"discount\"])\n",
    "print( target )\n",
    "\n",
    "print(\"-\"*5)\n",
    "\n",
    "# Note: DataFrame().groupby() returns a \"DataFrameGroupBy\" object which is a list of tuples\n",
    "group_by_names = transactions.groupby(\"product\", group_keys=False) # By name, Search for group_keys=False effect\n",
    "print( group_by_names )\n",
    "\n",
    "print(\"-\"*5)\n",
    "\n",
    "# Lets see what is \"DataFrameGroupBy\" is\n",
    "for group_name, group_dataframe in group_by_names:\n",
    "    print(group_name, \":\")\n",
    "    print(group_dataframe)\n",
    "    \n",
    "def print_groupby(group):\n",
    "    for group_name, group_dataframe in group:\n",
    "        print(group_name, \":\")\n",
    "        print(group_dataframe)\n",
    "# or\n",
    "print()\n",
    "group_by_names.apply(lambda group_by_name: print(group_by_name))\n",
    "\n",
    "# or \n",
    "print()\n",
    "group_by_names.apply(print)\n",
    "\n",
    "# Note: DataFrame().groupby() by default sort the values, so Apple comes first and Watch goes the last\n",
    "\n",
    "print(\"-\"*5)\n",
    "\n",
    "# Now we must sort each group seperately by count, then discount\n",
    "groups_by_names_counts_discount = group_by_names.apply(\n",
    "    lambda group_by_name: group_by_name.groupby(\"count\", group_keys=False).apply(\n",
    "        lambda group_by_name_count: group_by_name_count.groupby(\"discount\", group_keys=False).apply(\n",
    "            lambda g:g\n",
    "        )\n",
    "    ) \n",
    ")\n",
    "# Lets see \n",
    "print(groups_by_names_counts_discount)\n",
    "        \n",
    "# Compare to target:\n",
    "print( target )"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}