Pandas-Grouping&Aggregating6.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "61ddb52b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "163cb1da",
   "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>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>s001</td>\n",
       "      <td>V</td>\n",
       "      <td>Alberto Franco</td>\n",
       "      <td>15/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>173</td>\n",
       "      <td>35</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S2</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>17/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>192</td>\n",
       "      <td>32</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S3</th>\n",
       "      <td>s003</td>\n",
       "      <td>VI</td>\n",
       "      <td>Ryan Parkes</td>\n",
       "      <td>16/02/1999</td>\n",
       "      <td>13</td>\n",
       "      <td>186</td>\n",
       "      <td>33</td>\n",
       "      <td>street3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S4</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>Eesha Hinton</td>\n",
       "      <td>25/09/1998</td>\n",
       "      <td>13</td>\n",
       "      <td>167</td>\n",
       "      <td>30</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S5</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>11/05/2002</td>\n",
       "      <td>14</td>\n",
       "      <td>151</td>\n",
       "      <td>31</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S6</th>\n",
       "      <td>s004</td>\n",
       "      <td>VI</td>\n",
       "      <td>David Parkes</td>\n",
       "      <td>15/09/1997</td>\n",
       "      <td>12</td>\n",
       "      <td>159</td>\n",
       "      <td>32</td>\n",
       "      <td>street4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class            name date_Of_Birth   age  height  weight  \\\n",
       "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
       "S2        s002     V    Gino Mcneill     17/05/2002   12     192      32   \n",
       "S3        s003    VI     Ryan Parkes     16/02/1999   13     186      33   \n",
       "S4        s001    VI    Eesha Hinton     25/09/1998   13     167      30   \n",
       "S5        s002     V    Gino Mcneill     11/05/2002   14     151      31   \n",
       "S6        s004    VI    David Parkes     15/09/1997   12     159      32   \n",
       "\n",
       "    address  \n",
       "S1  street1  \n",
       "S2  street2  \n",
       "S3  street3  \n",
       "S4  street1  \n",
       "S5  street2  \n",
       "S6  street4  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q16\n",
    "pd.set_option('display.max_rows',None)\n",
    "df = pd.DataFrame({\n",
    "    'school_code': ['s001','s002','s003','s001','s002','s004'],\n",
    "    'class': ['V', 'V', 'VI', 'VI', 'V', 'VI'],\n",
    "    'name': ['Alberto Franco','Gino Mcneill','Ryan Parkes', 'Eesha Hinton', 'Gino Mcneill', 'David Parkes'],\n",
    "    'date_Of_Birth ': ['15/05/2002','17/05/2002','16/02/1999','25/09/1998','11/05/2002','15/09/1997'],\n",
    "    'age': [12, 12, 13, 13, 14, 12],\n",
    "    'height': [173, 192, 186, 167, 151, 159],\n",
    "    'weight': [35, 32, 33, 30, 31, 32],\n",
    "    'address': ['street1', 'street2', 'street3', 'street1', 'street2', 'street4']},\n",
    "    index=['S1', 'S2', 'S3', 'S4', 'S5', 'S6'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4cdc0093",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['s001', 's002', 's003', 's004'])"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gp = df.groupby('school_code')\n",
    "gp.groups.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "26b91107",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Orders DataFrame:\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>book_name</th>\n",
       "      <th>book_type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Book1</td>\n",
       "      <td>Math</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Book2</td>\n",
       "      <td>Physics</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Book3</td>\n",
       "      <td>Computer</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Book4</td>\n",
       "      <td>Science</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Book5</td>\n",
       "      <td>English</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  book_name book_type  count\n",
       "0     Book1      Math      2\n",
       "1     Book2   Physics      2\n",
       "2     Book3  Computer      2\n",
       "3     Book4   Science      1\n",
       "4     Book5   English      1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q17\n",
    "df = pd.DataFrame({\n",
    "'book_name':['Book1','Book2','Book3','Book4','Book1','Book2','Book3','Book5'],\n",
    "'book_type':['Math','Physics','Computer','Science','Math','Physics','Computer','English'],\n",
    "'book_id':[1,2,3,4,1,2,3,5]})\n",
    "print(\"Original Orders DataFrame:\")\n",
    "\n",
    "result = df.groupby(['book_name', 'book_type'])[\"book_type\"].count().reset_index(name=\"count\")\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e92aacf1",
   "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>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>sales_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>3001</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>3002</td>\n",
       "      <td>5004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>3009</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>3007</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>3002</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>3004</td>\n",
       "      <td>5007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>3009</td>\n",
       "      <td>5008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>3008</td>\n",
       "      <td>5004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>3003</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>3002</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt  customer_id  sales_id\n",
       "0    70001     150.50         3005      5002\n",
       "1    70009     270.65         3001      5003\n",
       "2    70002      65.26         3002      5004\n",
       "3    70004     110.50         3009      5003\n",
       "4    70007     948.50         3005      5002\n",
       "5    70005    2400.60         3007      5001\n",
       "6    70008    5760.00         3002      5005\n",
       "7    70010    1983.43         3004      5007\n",
       "8    70003    2480.40         3009      5008\n",
       "9    70012     250.45         3008      5004\n",
       "10   70011      75.29         3003      5005\n",
       "11   70013    3045.60         3002      5001"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q18\n",
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'customer_id':[3005,3001,3002,3009,3005,3007,3002,3004,3009,3008,3003,3002],\n",
    "'sales_id':[5002,5003,5004,5003,5002,5001,5005,5007,5008,5004,5005,5001]})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b00f5973",
   "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>sales_id</th>\n",
       "      <th>(5000.993, 5003.333]</th>\n",
       "      <th>(5003.333, 5005.667]</th>\n",
       "      <th>(5005.667, 5008.0]</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3001</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3002</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3003</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3008</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3009</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "sales_id     (5000.993, 5003.333]  (5003.333, 5005.667]  (5005.667, 5008.0]\n",
       "customer_id                                                                \n",
       "3001                            1                     0                   0\n",
       "3002                            1                     2                   0\n",
       "3003                            0                     1                   0\n",
       "3004                            0                     0                   1\n",
       "3005                            2                     0                   0\n",
       "3007                            1                     0                   0\n",
       "3008                            0                     1                   0\n",
       "3009                            1                     0                   1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups = df.groupby(['customer_id', pd.cut(df.sales_id, 3)])\n",
    "result = groups.size().unstack()\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4cb31ec3",
   "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>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th>name</th>\n",
       "      <th>date_Of_Birth</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>S1</th>\n",
       "      <td>s001</td>\n",
       "      <td>V</td>\n",
       "      <td>Alberto Franco</td>\n",
       "      <td>15/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>173</td>\n",
       "      <td>35</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S2</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>17/05/2002</td>\n",
       "      <td>12</td>\n",
       "      <td>192</td>\n",
       "      <td>32</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S3</th>\n",
       "      <td>s003</td>\n",
       "      <td>VI</td>\n",
       "      <td>Ryan Parkes</td>\n",
       "      <td>16/02/1999</td>\n",
       "      <td>13</td>\n",
       "      <td>186</td>\n",
       "      <td>33</td>\n",
       "      <td>street3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S4</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>Eesha Hinton</td>\n",
       "      <td>25/09/1998</td>\n",
       "      <td>13</td>\n",
       "      <td>167</td>\n",
       "      <td>30</td>\n",
       "      <td>street1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S5</th>\n",
       "      <td>s002</td>\n",
       "      <td>V</td>\n",
       "      <td>Gino Mcneill</td>\n",
       "      <td>11/05/2002</td>\n",
       "      <td>14</td>\n",
       "      <td>151</td>\n",
       "      <td>31</td>\n",
       "      <td>street2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S6</th>\n",
       "      <td>s001</td>\n",
       "      <td>VI</td>\n",
       "      <td>David Parkes</td>\n",
       "      <td>15/09/1997</td>\n",
       "      <td>12</td>\n",
       "      <td>159</td>\n",
       "      <td>32</td>\n",
       "      <td>street4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   school_code class            name date_Of_Birth   age  height  weight  \\\n",
       "S1        s001     V  Alberto Franco     15/05/2002   12     173      35   \n",
       "S2        s002     V    Gino Mcneill     17/05/2002   12     192      32   \n",
       "S3        s003    VI     Ryan Parkes     16/02/1999   13     186      33   \n",
       "S4        s001    VI    Eesha Hinton     25/09/1998   13     167      30   \n",
       "S5        s002     V    Gino Mcneill     11/05/2002   14     151      31   \n",
       "S6        s001    VI    David Parkes     15/09/1997   12     159      32   \n",
       "\n",
       "    address  \n",
       "S1  street1  \n",
       "S2  street2  \n",
       "S3  street3  \n",
       "S4  street1  \n",
       "S5  street2  \n",
       "S6  street4  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q19\n",
    "df = pd.DataFrame({\n",
    "    'school_code': ['s001','s002','s003','s001','s002','s001'],\n",
    "    'class': ['V', 'V', 'VI', 'VI', 'V', 'VI'],\n",
    "    'name': ['Alberto Franco','Gino Mcneill','Ryan Parkes', 'Eesha Hinton', 'Gino Mcneill', 'David Parkes'],\n",
    "    'date_Of_Birth ': ['15/05/2002','17/05/2002','16/02/1999','25/09/1998','11/05/2002','15/09/1997'],\n",
    "    'age': [12, 12, 13, 13, 14, 12],\n",
    "    'height': [173, 192, 186, 167, 151, 159],\n",
    "    'weight': [35, 32, 33, 30, 31, 32],\n",
    "    'address': ['street1', 'street2', 'street3', 'street1', 'street2', 'street4']},\n",
    "    index=['S1', 'S2', 'S3', 'S4', 'S5', 'S6'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "dd0b406b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\singh\\AppData\\Local\\Temp/ipykernel_20668/1838924942.py:1: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.\n",
      "  groups = df.groupby(['school_code','class'])['height','age'].agg(['min','max','mean'])\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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">height</th>\n",
       "      <th colspan=\"3\" halign=\"left\">age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>school_code</th>\n",
       "      <th>class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">s001</th>\n",
       "      <th>V</th>\n",
       "      <td>173</td>\n",
       "      <td>173</td>\n",
       "      <td>173.0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VI</th>\n",
       "      <td>159</td>\n",
       "      <td>167</td>\n",
       "      <td>163.0</td>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>12.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s002</th>\n",
       "      <th>V</th>\n",
       "      <td>151</td>\n",
       "      <td>192</td>\n",
       "      <td>171.5</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s003</th>\n",
       "      <th>VI</th>\n",
       "      <td>186</td>\n",
       "      <td>186</td>\n",
       "      <td>186.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  height             age          \n",
       "                     min  max   mean min max  mean\n",
       "school_code class                                 \n",
       "s001        V        173  173  173.0  12  12  12.0\n",
       "            VI       159  167  163.0  12  13  12.5\n",
       "s002        V        151  192  171.5  12  14  13.0\n",
       "s003        VI       186  186  186.0  13  13  13.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups = df.groupby(['school_code','class'])['height','age'].agg(['min','max','mean'])\n",
    "groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3d3af33f",
   "metadata": {
    "scrolled": true
   },
   "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</th>\n",
       "      <th>type</th>\n",
       "      <th>book</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>Math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>Math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "      <td>Math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>B</td>\n",
       "      <td>2</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  type     book\n",
       "0   A     1     Math\n",
       "1   A     1     Math\n",
       "2   A     1  English\n",
       "3   A     1  Physics\n",
       "4   A     2     Math\n",
       "5   A     2  English\n",
       "6   B     1  Physics\n",
       "7   B     1  English\n",
       "8   B     1  Physics\n",
       "9   B     2  English\n",
       "10  B     2  English"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q20\n",
    "df = pd.DataFrame( {'id' : ['A','A','A','A','A','A','B','B','B','B','B'], \n",
    "                    'type' : [1,1,1,1,2,2,1,1,1,2,2], \n",
    "                    'book' : ['Math','Math','English','Physics','Math','English','Physics','English','Physics','English','English']})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "738062c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "List all unique values in a group:\n",
      "  id  type                  book\n",
      "0  A     1  Math,English,Physics\n",
      "1  A     2          Math,English\n",
      "2  B     1       Physics,English\n",
      "3  B     2               English\n"
     ]
    }
   ],
   "source": [
    "new_df = df[['id', 'type', 'book']].drop_duplicates()\\\n",
    "                         .groupby(['id','type'])['book']\\\n",
    "                         .apply(list)\\\n",
    "                         .reset_index()\n",
    "\n",
    "new_df['book'] = new_df.apply(lambda x: (','.join([str(s) for s in x['book']])), axis = 1)\n",
    "print(\"\\nList all unique values in a group:\")\n",
    "print(new_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "5b6c29d8",
   "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>ord_no</th>\n",
       "      <th>purch_amt</th>\n",
       "      <th>ord_date</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70001</td>\n",
       "      <td>150.50</td>\n",
       "      <td>05-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70009</td>\n",
       "      <td>270.65</td>\n",
       "      <td>09-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>70002</td>\n",
       "      <td>65.26</td>\n",
       "      <td>05-10-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70004</td>\n",
       "      <td>110.50</td>\n",
       "      <td>08-17-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70007</td>\n",
       "      <td>948.50</td>\n",
       "      <td>10-09-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>70005</td>\n",
       "      <td>2400.60</td>\n",
       "      <td>07-27-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>70008</td>\n",
       "      <td>5760.00</td>\n",
       "      <td>10-09-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>70010</td>\n",
       "      <td>1983.43</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70003</td>\n",
       "      <td>2480.40</td>\n",
       "      <td>10-10-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>70012</td>\n",
       "      <td>250.45</td>\n",
       "      <td>06-17-2012</td>\n",
       "      <td>3001</td>\n",
       "      <td>5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>70011</td>\n",
       "      <td>75.29</td>\n",
       "      <td>07-08-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>70013</td>\n",
       "      <td>3045.60</td>\n",
       "      <td>04-25-2012</td>\n",
       "      <td>3005</td>\n",
       "      <td>5001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ord_no  purch_amt    ord_date  customer_id  salesman_id\n",
       "0    70001     150.50  05-10-2012         3001         5002\n",
       "1    70009     270.65  09-10-2012         3001         5005\n",
       "2    70002      65.26  05-10-2012         3005         5001\n",
       "3    70004     110.50  08-17-2012         3001         5003\n",
       "4    70007     948.50  10-09-2012         3005         5002\n",
       "5    70005    2400.60  07-27-2012         3001         5001\n",
       "6    70008    5760.00  10-09-2012         3005         5001\n",
       "7    70010    1983.43  10-10-2012         3001         5006\n",
       "8    70003    2480.40  10-10-2012         3005         5003\n",
       "9    70012     250.45  06-17-2012         3001         5002\n",
       "10   70011      75.29  07-08-2012         3005         5007\n",
       "11   70013    3045.60  04-25-2012         3005         5001"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q21\n",
    "df = pd.DataFrame({\n",
    "'ord_no':[70001,70009,70002,70004,70007,70005,70008,70010,70003,70012,70011,70013],\n",
    "'purch_amt':[150.5,270.65,65.26,110.5,948.5,2400.6,5760,1983.43,2480.4,250.45, 75.29,3045.6],\n",
    "'ord_date': ['05-10-2012','09-10-2012','05-10-2012','08-17-2012','10-09-2012','07-27-2012','10-09-2012','10-10-2012','10-10-2012','06-17-2012','07-08-2012','04-25-2012'],\n",
    "'customer_id':[3001,3001,3005,3001,3005,3001,3005,3001,3005,3001,3005,3005],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001]})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "3d87c0d5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            purch_amt\n",
      "ord_date             \n",
      "2012-06-30    3511.81\n",
      "2012-09-30    2857.04\n",
      "2012-12-31   11172.33\n"
     ]
    }
   ],
   "source": [
    "df['ord_date']= pd.to_datetime(df['ord_date']) \n",
    "result = df.set_index('ord_date').groupby(pd.Grouper(freq='Q')).agg({'purch_amt':sum})\n",
    "print(result)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}