Pandas-Grouping&Aggregating7.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "25720908",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "4f0115e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "08414a4f",
   "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": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q21\n",
    "pd.set_option('display.max_rows', None)\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": 4,
   "id": "ecd163d3",
   "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>purch_amt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ord_date</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012-06-30</th>\n",
       "      <td>3511.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-09-30</th>\n",
       "      <td>2857.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012-12-31</th>\n",
       "      <td>11172.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            purch_amt\n",
       "ord_date             \n",
       "2012-06-30    3511.81\n",
       "2012-09-30    2857.04\n",
       "2012-12-31   11172.33"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "35b4c0e9",
   "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": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q22\n",
    "student_data = 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",
    "student_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "dc6607c6",
   "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 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 colspan=\"3\" halign=\"left\">age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>school_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>s001</th>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>12.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s002</th>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s003</th>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s004</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            age          \n",
       "            min max  mean\n",
       "school_code              \n",
       "s001         12  13  12.5\n",
       "s002         12  14  13.0\n",
       "s003         13  13  13.0\n",
       "s004         12  12  12.0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gg = student_data.groupby('school_code').agg({'age':['min','max','mean']})\n",
    "gg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4f53a95d",
   "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>C3001</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>C3001</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>D3005</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>D3001</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>C3005</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>D3001</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>C3005</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>D3001</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>D3005</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>C3001</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>D3005</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>D3005</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       C3001         5002\n",
       "1    70009     270.65  09-10-2012       C3001         5005\n",
       "2    70002      65.26  05-10-2012       D3005         5001\n",
       "3    70004     110.50  08-17-2012       D3001         5003\n",
       "4    70007     948.50  10-09-2012       C3005         5002\n",
       "5    70005    2400.60  07-27-2012       D3001         5001\n",
       "6    70008    5760.00  10-09-2012       C3005         5001\n",
       "7    70010    1983.43  10-10-2012       D3001         5006\n",
       "8    70003    2480.40  10-10-2012       D3005         5003\n",
       "9    70012     250.45  06-17-2012       C3001         5002\n",
       "10   70011      75.29  07-08-2012       D3005         5007\n",
       "11   70013    3045.60  04-25-2012       D3005         5001"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q23\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':['C3001','C3001','D3005','D3001','C3005','D3001','C3005','D3001','D3005','C3001','D3005','D3005'],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001]})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "fe71c0a2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of customers starting with 'C': customer_id\n",
      "C3001    3\n",
      "C3005    2\n",
      "D3001    0\n",
      "D3005    0\n",
      "dtype: int64\n",
      "All products: C3001, D3005, D3001, C3005\n",
      "Difference of maximum and minimum purchase amounts:\n",
      "customer_id\n",
      "C3001     120.15\n",
      "C3005    4811.50\n",
      "D3001    2290.10\n",
      "D3005    2980.34\n",
      "Name: purch_amt, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "gp = df.groupby('customer_id')\n",
    "num_customers_starting_with_C = gp.apply(lambda x: x['customer_id'].str.startswith('C').sum())\n",
    "all_products = df['customer_id'].unique()\n",
    "purchase_diff = gp['purch_amt'].max() - gp['purch_amt'].min()\n",
    "\n",
    "# Display the results\n",
    "print(f\"Number of customers starting with 'C': {num_customers_starting_with_C}\")\n",
    "print(f\"All products: {', '.join(all_products)}\")\n",
    "print(\"Difference of maximum and minimum purchase amounts:\")\n",
    "print(purchase_diff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "3ec2009c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Number of customers  starting with ‘C’, the list of all products and the difference of maximum purchase amount and minimum purchase amount:\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>customer_id_start_C</th>\n",
       "      <th>customer_id_list</th>\n",
       "      <th>purchase_amt_gap</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>salesman_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5001</th>\n",
       "      <td>1</td>\n",
       "      <td>D3005, D3001, C3005, D3005</td>\n",
       "      <td>5694.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5002</th>\n",
       "      <td>3</td>\n",
       "      <td>C3001, C3005, C3001</td>\n",
       "      <td>798.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5003</th>\n",
       "      <td>0</td>\n",
       "      <td>D3001, D3005</td>\n",
       "      <td>2369.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5005</th>\n",
       "      <td>1</td>\n",
       "      <td>C3001</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5006</th>\n",
       "      <td>0</td>\n",
       "      <td>D3001</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5007</th>\n",
       "      <td>0</td>\n",
       "      <td>D3005</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             customer_id_start_C            customer_id_list  purchase_amt_gap\n",
       "salesman_id                                                                   \n",
       "5001                           1  D3005, D3001, C3005, D3005           5694.74\n",
       "5002                           3         C3001, C3005, C3001            798.00\n",
       "5003                           0                D3001, D3005           2369.90\n",
       "5005                           1                       C3001              0.00\n",
       "5006                           0                       D3001              0.00\n",
       "5007                           0                       D3005              0.00"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def customer_id_C(x):\n",
    "    return (x.str[0] == 'C').sum()\n",
    "result = df.groupby(['salesman_id'])\\\n",
    "  .agg(customer_id_start_C = ('customer_id', customer_id_C),\n",
    "       customer_id_list = ('customer_id', lambda x: ', '.join(x)),\n",
    "       purchase_amt_gap   = ('purch_amt', lambda x: x.max()-x.min())\n",
    "      )\n",
    "print(\"\\nNumber of customers  starting with ‘C’, the list of all products and the difference of maximum purchase amount and minimum purchase amount:\")\n",
    "display(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "121196a6",
   "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>purch_amt</th>\n",
       "      <th>% (Purch Amt.)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th>salesman_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C3001</th>\n",
       "      <th>5002</th>\n",
       "      <td>400.95</td>\n",
       "      <td>2.285764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5005</th>\n",
       "      <td>270.65</td>\n",
       "      <td>1.542941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C3005</th>\n",
       "      <th>5001</th>\n",
       "      <td>5760.00</td>\n",
       "      <td>32.837016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5002</th>\n",
       "      <td>948.50</td>\n",
       "      <td>5.407276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">D3001</th>\n",
       "      <th>5001</th>\n",
       "      <td>2400.60</td>\n",
       "      <td>13.685510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5003</th>\n",
       "      <td>110.50</td>\n",
       "      <td>0.629946</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5006</th>\n",
       "      <td>1983.43</td>\n",
       "      <td>11.307278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">D3005</th>\n",
       "      <th>5001</th>\n",
       "      <td>3110.86</td>\n",
       "      <td>17.734611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5003</th>\n",
       "      <td>2480.40</td>\n",
       "      <td>14.140440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5007</th>\n",
       "      <td>75.29</td>\n",
       "      <td>0.429219</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         purch_amt  % (Purch Amt.)\n",
       "customer_id salesman_id                           \n",
       "C3001       5002            400.95        2.285764\n",
       "            5005            270.65        1.542941\n",
       "C3005       5001           5760.00       32.837016\n",
       "            5002            948.50        5.407276\n",
       "D3001       5001           2400.60       13.685510\n",
       "            5003            110.50        0.629946\n",
       "            5006           1983.43       11.307278\n",
       "D3005       5001           3110.86       17.734611\n",
       "            5003           2480.40       14.140440\n",
       "            5007             75.29        0.429219"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q24\n",
    "gr_data = df.groupby(['customer_id','salesman_id']).agg({'purch_amt':'sum'})\n",
    "gr_data[\"% (Purch Amt.)\"] = gr_data.apply(lambda x:  100*x / x.sum())\n",
    "gr_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "b3c8da90",
   "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": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q25\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": 33,
   "id": "06a13747",
   "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 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 colspan=\"3\" halign=\"left\">age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean_age</th>\n",
       "      <th>min_age</th>\n",
       "      <th>max_age</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>school_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>s001</th>\n",
       "      <td>12.5</td>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s002</th>\n",
       "      <td>13.0</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s003</th>\n",
       "      <td>13.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>s004</th>\n",
       "      <td>12.0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 age                \n",
       "            mean_age min_age max_age\n",
       "school_code                         \n",
       "s001            12.5      12      13\n",
       "s002            13.0      12      14\n",
       "s003            13.0      13      13\n",
       "s004            12.0      12      12"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_single = df.groupby('school_code').agg({'age': [(\"mean_age\",\"mean\"), (\"min_age\", \"min\"), (\"max_age\",\"max\")]})\n",
    "grouped_single"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "4d1bc607",
   "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>C3001</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>C3001</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>D3005</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>D3001</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>C3005</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>D3001</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>C3005</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>D3001</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>D3005</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>C3001</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>D3005</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>D3005</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       C3001         5002\n",
       "1    70009     270.65  09-10-2012       C3001         5005\n",
       "2    70002      65.26  05-10-2012       D3005         5001\n",
       "3    70004     110.50  08-17-2012       D3001         5003\n",
       "4    70007     948.50  10-09-2012       C3005         5002\n",
       "5    70005    2400.60  07-27-2012       D3001         5001\n",
       "6    70008    5760.00  10-09-2012       C3005         5001\n",
       "7    70010    1983.43  10-10-2012       D3001         5006\n",
       "8    70003    2480.40  10-10-2012       D3005         5003\n",
       "9    70012     250.45  06-17-2012       C3001         5002\n",
       "10   70011      75.29  07-08-2012       D3005         5007\n",
       "11   70013    3045.60  04-25-2012       D3005         5001"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "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':['C3001','C3001','D3005','D3001','C3005','D3001','C3005','D3001','D3005','C3001','D3005','D3005'],\n",
    "'salesman_id': [5002,5005,5001,5003,5002,5001,5001,5006,5003,5002,5007,5001]})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "57000b4c",
   "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 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 colspan=\"3\" halign=\"left\">purch_amt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean_purch</th>\n",
       "      <th>min_purch</th>\n",
       "      <th>max_purch</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>C3001</th>\n",
       "      <td>223.866667</td>\n",
       "      <td>150.50</td>\n",
       "      <td>270.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C3005</th>\n",
       "      <td>3354.250000</td>\n",
       "      <td>948.50</td>\n",
       "      <td>5760.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D3001</th>\n",
       "      <td>1498.176667</td>\n",
       "      <td>110.50</td>\n",
       "      <td>2400.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D3005</th>\n",
       "      <td>1416.637500</td>\n",
       "      <td>65.26</td>\n",
       "      <td>3045.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               purch_amt                    \n",
       "              mean_purch min_purch max_purch\n",
       "customer_id                                 \n",
       "C3001         223.866667    150.50    270.65\n",
       "C3005        3354.250000    948.50   5760.00\n",
       "D3001        1498.176667    110.50   2400.60\n",
       "D3005        1416.637500     65.26   3045.60"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_single = df.groupby('customer_id').agg({'purch_amt': [(\"mean_purch\",\"mean\"), (\"min_purch\", \"min\"), (\"max_purch\",\"max\")]})\n",
    "grouped_single"
   ]
  }
 ],
 "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
}