wholesale_data_analysis_startercode.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "dc0318d6",
   "metadata": {},
   "source": [
    "## Part 1: Explore the Data\n",
    "\n",
    "Import the data and use Pandas to learn more about the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "e076f57f",
   "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>first</th>\n",
       "      <th>last</th>\n",
       "      <th>job</th>\n",
       "      <th>phone</th>\n",
       "      <th>email</th>\n",
       "      <th>client_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_week</th>\n",
       "      <th>order_year</th>\n",
       "      <th>item_id</th>\n",
       "      <th>category</th>\n",
       "      <th>subcategory</th>\n",
       "      <th>unit_price</th>\n",
       "      <th>unit_cost</th>\n",
       "      <th>unit_weight</th>\n",
       "      <th>qty</th>\n",
       "      <th>line_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Donald</td>\n",
       "      <td>Harding</td>\n",
       "      <td>Immunologist</td>\n",
       "      <td>793-904-7725x39308</td>\n",
       "      <td>harding.donald.7185@sullivan.com</td>\n",
       "      <td>58515</td>\n",
       "      <td>8953482</td>\n",
       "      <td>2023-04-28</td>\n",
       "      <td>17</td>\n",
       "      <td>2023</td>\n",
       "      <td>EUD29711-63-6U</td>\n",
       "      <td>decor</td>\n",
       "      <td>wall art</td>\n",
       "      <td>1096.80</td>\n",
       "      <td>762.71</td>\n",
       "      <td>7.50</td>\n",
       "      <td>105</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tiffany</td>\n",
       "      <td>Myers</td>\n",
       "      <td>Music therapist</td>\n",
       "      <td>201.442.4543x942</td>\n",
       "      <td>myers.t.6537@ferguson-johnson.net</td>\n",
       "      <td>37609</td>\n",
       "      <td>8069089</td>\n",
       "      <td>2023-05-19</td>\n",
       "      <td>20</td>\n",
       "      <td>2023</td>\n",
       "      <td>XDA18116-89-4A</td>\n",
       "      <td>consumables</td>\n",
       "      <td>pens</td>\n",
       "      <td>24.95</td>\n",
       "      <td>15.09</td>\n",
       "      <td>1.49</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Shannon</td>\n",
       "      <td>Watson</td>\n",
       "      <td>Immunologist</td>\n",
       "      <td>687.737.9424x8503</td>\n",
       "      <td>swatson8146@payne.net</td>\n",
       "      <td>57113</td>\n",
       "      <td>1902144</td>\n",
       "      <td>2023-01-29</td>\n",
       "      <td>4</td>\n",
       "      <td>2023</td>\n",
       "      <td>ABE59463-05-7E</td>\n",
       "      <td>software</td>\n",
       "      <td>project management</td>\n",
       "      <td>13.52</td>\n",
       "      <td>7.86</td>\n",
       "      <td>1.68</td>\n",
       "      <td>39</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Nathan</td>\n",
       "      <td>Baker</td>\n",
       "      <td>Accounting technician</td>\n",
       "      <td>827-788-8123x012</td>\n",
       "      <td>bakernathan@benson.com</td>\n",
       "      <td>46554</td>\n",
       "      <td>9031802</td>\n",
       "      <td>2023-04-25</td>\n",
       "      <td>17</td>\n",
       "      <td>2023</td>\n",
       "      <td>ZMM00836-65-0C</td>\n",
       "      <td>consumables</td>\n",
       "      <td>pens</td>\n",
       "      <td>36.42</td>\n",
       "      <td>24.85</td>\n",
       "      <td>1.23</td>\n",
       "      <td>29</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Christina</td>\n",
       "      <td>Schwartz</td>\n",
       "      <td>Chiropractor</td>\n",
       "      <td>265-829-3643</td>\n",
       "      <td>christinaschwartz9252@mcconnell.com</td>\n",
       "      <td>92089</td>\n",
       "      <td>1322274</td>\n",
       "      <td>2023-05-28</td>\n",
       "      <td>21</td>\n",
       "      <td>2023</td>\n",
       "      <td>BZX55559-12-3X</td>\n",
       "      <td>consumables</td>\n",
       "      <td>misc</td>\n",
       "      <td>195.10</td>\n",
       "      <td>108.17</td>\n",
       "      <td>46.43</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       first      last                    job               phone  \\\n",
       "0     Donald   Harding           Immunologist  793-904-7725x39308   \n",
       "1    Tiffany     Myers        Music therapist    201.442.4543x942   \n",
       "2    Shannon    Watson           Immunologist   687.737.9424x8503   \n",
       "3     Nathan     Baker  Accounting technician    827-788-8123x012   \n",
       "4  Christina  Schwartz           Chiropractor        265-829-3643   \n",
       "\n",
       "                                 email  client_id  order_id  order_date  \\\n",
       "0     harding.donald.7185@sullivan.com      58515   8953482  2023-04-28   \n",
       "1    myers.t.6537@ferguson-johnson.net      37609   8069089  2023-05-19   \n",
       "2                swatson8146@payne.net      57113   1902144  2023-01-29   \n",
       "3               bakernathan@benson.com      46554   9031802  2023-04-25   \n",
       "4  christinaschwartz9252@mcconnell.com      92089   1322274  2023-05-28   \n",
       "\n",
       "   order_week  order_year         item_id     category         subcategory  \\\n",
       "0          17        2023  EUD29711-63-6U        decor            wall art   \n",
       "1          20        2023  XDA18116-89-4A  consumables                pens   \n",
       "2           4        2023  ABE59463-05-7E     software  project management   \n",
       "3          17        2023  ZMM00836-65-0C  consumables                pens   \n",
       "4          21        2023  BZX55559-12-3X  consumables                misc   \n",
       "\n",
       "   unit_price  unit_cost  unit_weight  qty  line_number  \n",
       "0     1096.80     762.71         7.50  105            1  \n",
       "1       24.95      15.09         1.49   21            0  \n",
       "2       13.52       7.86         1.68   39            6  \n",
       "3       36.42      24.85         1.23   29            3  \n",
       "4      195.10     108.17        46.43   20            1  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('client_dataset.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "71b9bbd2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['first', 'last', 'job', 'phone', 'email', 'client_id', 'order_id',\n",
       "       'order_date', 'order_week', 'order_year', 'item_id', 'category',\n",
       "       'subcategory', 'unit_price', 'unit_cost', 'unit_weight', 'qty',\n",
       "       'line_number'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# View the column names in the data\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "d71801b3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 54639 entries, 0 to 54638\n",
      "Data columns (total 18 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   first        54639 non-null  object \n",
      " 1   last         54639 non-null  object \n",
      " 2   job          54639 non-null  object \n",
      " 3   phone        54639 non-null  object \n",
      " 4   email        54639 non-null  object \n",
      " 5   client_id    54639 non-null  int64  \n",
      " 6   order_id     54639 non-null  int64  \n",
      " 7   order_date   54639 non-null  object \n",
      " 8   order_week   54639 non-null  int64  \n",
      " 9   order_year   54639 non-null  int64  \n",
      " 10  item_id      54639 non-null  object \n",
      " 11  category     54639 non-null  object \n",
      " 12  subcategory  54639 non-null  object \n",
      " 13  unit_price   54639 non-null  float64\n",
      " 14  unit_cost    54639 non-null  float64\n",
      " 15  unit_weight  54639 non-null  float64\n",
      " 16  qty          54639 non-null  int64  \n",
      " 17  line_number  54639 non-null  int64  \n",
      "dtypes: float64(3), int64(6), object(9)\n",
      "memory usage: 7.5+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "eb960d57",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sample size for this problem is:  - 54639 rows, and 18 columns\n"
     ]
    }
   ],
   "source": [
    "print(\"The sample size for this problem is:\", \" - {} rows, and {} columns\".format(df.shape[0], df.shape[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "bc0f4172",
   "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>client_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_week</th>\n",
       "      <th>order_year</th>\n",
       "      <th>unit_price</th>\n",
       "      <th>unit_cost</th>\n",
       "      <th>unit_weight</th>\n",
       "      <th>qty</th>\n",
       "      <th>line_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>54639.000000</td>\n",
       "      <td>5.463900e+04</td>\n",
       "      <td>54639.000000</td>\n",
       "      <td>54639.000000</td>\n",
       "      <td>54639.000000</td>\n",
       "      <td>54639.000000</td>\n",
       "      <td>54639.000000</td>\n",
       "      <td>5.463900e+04</td>\n",
       "      <td>54639.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>54837.869416</td>\n",
       "      <td>5.470190e+06</td>\n",
       "      <td>11.359139</td>\n",
       "      <td>2022.993064</td>\n",
       "      <td>136.267207</td>\n",
       "      <td>99.446073</td>\n",
       "      <td>5.004116</td>\n",
       "      <td>5.702646e+02</td>\n",
       "      <td>2.979667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>25487.438231</td>\n",
       "      <td>2.599807e+06</td>\n",
       "      <td>7.023499</td>\n",
       "      <td>0.082997</td>\n",
       "      <td>183.873135</td>\n",
       "      <td>133.164267</td>\n",
       "      <td>5.326599</td>\n",
       "      <td>1.879552e+04</td>\n",
       "      <td>2.436320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>10033.000000</td>\n",
       "      <td>1.000886e+06</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2022.000000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.010000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>33593.000000</td>\n",
       "      <td>3.196372e+06</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>2023.000000</td>\n",
       "      <td>20.800000</td>\n",
       "      <td>14.840000</td>\n",
       "      <td>1.450000</td>\n",
       "      <td>3.200000e+01</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>53305.000000</td>\n",
       "      <td>5.496966e+06</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>2023.000000</td>\n",
       "      <td>68.310000</td>\n",
       "      <td>49.890000</td>\n",
       "      <td>3.240000</td>\n",
       "      <td>6.800000e+01</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>78498.000000</td>\n",
       "      <td>7.733869e+06</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>2023.000000</td>\n",
       "      <td>173.160000</td>\n",
       "      <td>125.570000</td>\n",
       "      <td>6.890000</td>\n",
       "      <td>1.700000e+02</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>99984.000000</td>\n",
       "      <td>9.998480e+06</td>\n",
       "      <td>52.000000</td>\n",
       "      <td>2023.000000</td>\n",
       "      <td>1396.230000</td>\n",
       "      <td>846.270000</td>\n",
       "      <td>46.430000</td>\n",
       "      <td>3.958244e+06</td>\n",
       "      <td>9.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          client_id      order_id    order_week    order_year    unit_price  \\\n",
       "count  54639.000000  5.463900e+04  54639.000000  54639.000000  54639.000000   \n",
       "mean   54837.869416  5.470190e+06     11.359139   2022.993064    136.267207   \n",
       "std    25487.438231  2.599807e+06      7.023499      0.082997    183.873135   \n",
       "min    10033.000000  1.000886e+06      1.000000   2022.000000      0.010000   \n",
       "25%    33593.000000  3.196372e+06      6.000000   2023.000000     20.800000   \n",
       "50%    53305.000000  5.496966e+06     11.000000   2023.000000     68.310000   \n",
       "75%    78498.000000  7.733869e+06     17.000000   2023.000000    173.160000   \n",
       "max    99984.000000  9.998480e+06     52.000000   2023.000000   1396.230000   \n",
       "\n",
       "          unit_cost   unit_weight           qty   line_number  \n",
       "count  54639.000000  54639.000000  5.463900e+04  54639.000000  \n",
       "mean      99.446073      5.004116  5.702646e+02      2.979667  \n",
       "std      133.164267      5.326599  1.879552e+04      2.436320  \n",
       "min        0.010000      0.000000  0.000000e+00      0.000000  \n",
       "25%       14.840000      1.450000  3.200000e+01      1.000000  \n",
       "50%       49.890000      3.240000  6.800000e+01      3.000000  \n",
       "75%      125.570000      6.890000  1.700000e+02      5.000000  \n",
       "max      846.270000     46.430000  3.958244e+06      9.000000  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the describe function to gather some basic statistics\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "b24b53cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "job\n",
       "Osteopath                             631\n",
       "Toxicologist                          546\n",
       "Architectural technologist            430\n",
       "Librarian, academic                   389\n",
       "Teacher, special educational needs    388\n",
       "                                     ... \n",
       "Copywriter, advertising                12\n",
       "Midwife                                10\n",
       "Insurance risk surveyor                 9\n",
       "Runner, broadcasting/film/video         9\n",
       "Advertising account planner             5\n",
       "Name: count, Length: 498, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use this space to do any additional research\n",
    "# and familiarize yourself with the data.\n",
    "df['job'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "551cb358",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Top 3 Item Categories\n",
      "category\n",
      "consumables    23538\n",
      "furniture      11915\n",
      "software        8400\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# What three item categories had the most entries?\n",
    "category_counts = (df['category'].value_counts())[0:3]\n",
    "\n",
    "print(\"Top 3 Item Categories\")\n",
    "print(category_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "8b2b4b79",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "job\n",
       "Osteopath                             631\n",
       "Toxicologist                          546\n",
       "Architectural technologist            430\n",
       "Librarian, academic                   389\n",
       "Teacher, special educational needs    388\n",
       "                                     ... \n",
       "Copywriter, advertising                12\n",
       "Midwife                                10\n",
       "Insurance risk surveyor                 9\n",
       "Runner, broadcasting/film/video         9\n",
       "Advertising account planner             5\n",
       "Name: count, Length: 498, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['job'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "9f0089e8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31158688"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['qty'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "ae38046b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "category\n",
       "consumables    23538\n",
       "furniture      11915\n",
       "software        8400\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What three item categories had the most entries?\n",
    "most_categories= df[\"category\"].value_counts()\n",
    "most_categories.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "0d937074",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "subcategory\n",
       "bathroom supplies    6424\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# For the category with the most entries, \"consumables\"\n",
    "# which subcategory had the most entries? 'bathroom supplies'\n",
    "consumables_df = df[df.category == 'consumables']\n",
    "\n",
    "consumables_subcategory = consumables_df['subcategory'].value_counts()\n",
    "\n",
    "consumables_subcategory.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "5f54e2cf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Top Sub-Category\n",
      "subcategory\n",
      "bathroom supplies    6424\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# For the category with the most entries, which subcategory had the most entries?\n",
    "sub_category_counts = df['subcategory'].value_counts()[:1]\n",
    "\n",
    "print(\"Top Sub-Category\")\n",
    "print(sub_category_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "2db94379",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Top 5 Client IDs and Their Order Total\n",
      "client_id\n",
      "33615    220\n",
      "66037    211\n",
      "46820    209\n",
      "38378    207\n",
      "24741    207\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Which five clients had the most entries in the data?\n",
    "top_five_clients = df['client_id'].value_counts()[:5]\n",
    "\n",
    "print(\"Top 5 Client IDs and Their Order Total\")\n",
    "print(top_five_clients)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "00477155",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "client_id\n",
       "33615    220\n",
       "66037    211\n",
       "46820    209\n",
       "38378    207\n",
       "24741    207\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['client_id'].value_counts().head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "a6635601",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Top 5 Client IDs\n",
      "[33615, 66037, 46820, 38378, 24741]\n"
     ]
    }
   ],
   "source": [
    "# Store the client ids of those top 5 clients in a list.\n",
    "top_five_client_ids = top_five_clients.index.tolist()\n",
    "\n",
    "# Print the client IDs\n",
    "print(\"Top 5 Client IDs\")\n",
    "print(top_five_client_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "c88b8a3e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "64313"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many total units (the qty column) did the client with the most entries order order?\n",
    "\n",
    "# Top Client ID\n",
    "top_client = top_five_client_ids[0]\n",
    "\n",
    "# Extract all of the orders placed by the top client\n",
    "top_client_orders = df[df['client_id'] == top_client]\n",
    "\n",
    "# Total number of orders placed by the top client\n",
    "top_client_orders['qty'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb429325",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "257fcd7d",
   "metadata": {},
   "source": [
    "## Part 2: Transform the Data\n",
    "Do we know that this client spent the more money than client 66037? If not, how would we find out? Transform the data using the steps below to prepare it for analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "0c25c4a8",
   "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>first</th>\n",
       "      <th>last</th>\n",
       "      <th>job</th>\n",
       "      <th>phone</th>\n",
       "      <th>email</th>\n",
       "      <th>client_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_week</th>\n",
       "      <th>order_year</th>\n",
       "      <th>item_id</th>\n",
       "      <th>category</th>\n",
       "      <th>subcategory</th>\n",
       "      <th>unit_price</th>\n",
       "      <th>unit_cost</th>\n",
       "      <th>unit_weight</th>\n",
       "      <th>qty</th>\n",
       "      <th>line_number</th>\n",
       "      <th>line_subtotal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Donald</td>\n",
       "      <td>Harding</td>\n",
       "      <td>Immunologist</td>\n",
       "      <td>793-904-7725x39308</td>\n",
       "      <td>harding.donald.7185@sullivan.com</td>\n",
       "      <td>58515</td>\n",
       "      <td>8953482</td>\n",
       "      <td>2023-04-28</td>\n",
       "      <td>17</td>\n",
       "      <td>2023</td>\n",
       "      <td>EUD29711-63-6U</td>\n",
       "      <td>decor</td>\n",
       "      <td>wall art</td>\n",
       "      <td>1096.80</td>\n",
       "      <td>762.71</td>\n",
       "      <td>7.50</td>\n",
       "      <td>105</td>\n",
       "      <td>1</td>\n",
       "      <td>115164.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tiffany</td>\n",
       "      <td>Myers</td>\n",
       "      <td>Music therapist</td>\n",
       "      <td>201.442.4543x942</td>\n",
       "      <td>myers.t.6537@ferguson-johnson.net</td>\n",
       "      <td>37609</td>\n",
       "      <td>8069089</td>\n",
       "      <td>2023-05-19</td>\n",
       "      <td>20</td>\n",
       "      <td>2023</td>\n",
       "      <td>XDA18116-89-4A</td>\n",
       "      <td>consumables</td>\n",
       "      <td>pens</td>\n",
       "      <td>24.95</td>\n",
       "      <td>15.09</td>\n",
       "      <td>1.49</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>523.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Shannon</td>\n",
       "      <td>Watson</td>\n",
       "      <td>Immunologist</td>\n",
       "      <td>687.737.9424x8503</td>\n",
       "      <td>swatson8146@payne.net</td>\n",
       "      <td>57113</td>\n",
       "      <td>1902144</td>\n",
       "      <td>2023-01-29</td>\n",
       "      <td>4</td>\n",
       "      <td>2023</td>\n",
       "      <td>ABE59463-05-7E</td>\n",
       "      <td>software</td>\n",
       "      <td>project management</td>\n",
       "      <td>13.52</td>\n",
       "      <td>7.86</td>\n",
       "      <td>1.68</td>\n",
       "      <td>39</td>\n",
       "      <td>6</td>\n",
       "      <td>527.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Nathan</td>\n",
       "      <td>Baker</td>\n",
       "      <td>Accounting technician</td>\n",
       "      <td>827-788-8123x012</td>\n",
       "      <td>bakernathan@benson.com</td>\n",
       "      <td>46554</td>\n",
       "      <td>9031802</td>\n",
       "      <td>2023-04-25</td>\n",
       "      <td>17</td>\n",
       "      <td>2023</td>\n",
       "      <td>ZMM00836-65-0C</td>\n",
       "      <td>consumables</td>\n",
       "      <td>pens</td>\n",
       "      <td>36.42</td>\n",
       "      <td>24.85</td>\n",
       "      <td>1.23</td>\n",
       "      <td>29</td>\n",
       "      <td>3</td>\n",
       "      <td>1056.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Christina</td>\n",
       "      <td>Schwartz</td>\n",
       "      <td>Chiropractor</td>\n",
       "      <td>265-829-3643</td>\n",
       "      <td>christinaschwartz9252@mcconnell.com</td>\n",
       "      <td>92089</td>\n",
       "      <td>1322274</td>\n",
       "      <td>2023-05-28</td>\n",
       "      <td>21</td>\n",
       "      <td>2023</td>\n",
       "      <td>BZX55559-12-3X</td>\n",
       "      <td>consumables</td>\n",
       "      <td>misc</td>\n",
       "      <td>195.10</td>\n",
       "      <td>108.17</td>\n",
       "      <td>46.43</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>3902.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       first      last                    job               phone  \\\n",
       "0     Donald   Harding           Immunologist  793-904-7725x39308   \n",
       "1    Tiffany     Myers        Music therapist    201.442.4543x942   \n",
       "2    Shannon    Watson           Immunologist   687.737.9424x8503   \n",
       "3     Nathan     Baker  Accounting technician    827-788-8123x012   \n",
       "4  Christina  Schwartz           Chiropractor        265-829-3643   \n",
       "\n",
       "                                 email  client_id  order_id  order_date  \\\n",
       "0     harding.donald.7185@sullivan.com      58515   8953482  2023-04-28   \n",
       "1    myers.t.6537@ferguson-johnson.net      37609   8069089  2023-05-19   \n",
       "2                swatson8146@payne.net      57113   1902144  2023-01-29   \n",
       "3               bakernathan@benson.com      46554   9031802  2023-04-25   \n",
       "4  christinaschwartz9252@mcconnell.com      92089   1322274  2023-05-28   \n",
       "\n",
       "   order_week  order_year         item_id     category         subcategory  \\\n",
       "0          17        2023  EUD29711-63-6U        decor            wall art   \n",
       "1          20        2023  XDA18116-89-4A  consumables                pens   \n",
       "2           4        2023  ABE59463-05-7E     software  project management   \n",
       "3          17        2023  ZMM00836-65-0C  consumables                pens   \n",
       "4          21        2023  BZX55559-12-3X  consumables                misc   \n",
       "\n",
       "   unit_price  unit_cost  unit_weight  qty  line_number  line_subtotal  \n",
       "0     1096.80     762.71         7.50  105            1      115164.00  \n",
       "1       24.95      15.09         1.49   21            0         523.95  \n",
       "2       13.52       7.86         1.68   39            6         527.28  \n",
       "3       36.42      24.85         1.23   29            3        1056.18  \n",
       "4      195.10     108.17        46.43   20            1        3902.00  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a column that calculates the subtotal for each line using the unit_price and the qty\n",
    "\n",
    "df['line_subtotal'] = df['unit_price'] * df['qty']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "ac9dafc8",
   "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>unit_price</th>\n",
       "      <th>unit_weight</th>\n",
       "      <th>qty</th>\n",
       "      <th>total_weight</th>\n",
       "      <th>shipping_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1096.80</td>\n",
       "      <td>7.50</td>\n",
       "      <td>105</td>\n",
       "      <td>787.50</td>\n",
       "      <td>5512.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24.95</td>\n",
       "      <td>1.49</td>\n",
       "      <td>21</td>\n",
       "      <td>31.29</td>\n",
       "      <td>312.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13.52</td>\n",
       "      <td>1.68</td>\n",
       "      <td>39</td>\n",
       "      <td>65.52</td>\n",
       "      <td>458.64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   unit_price  unit_weight  qty  total_weight  shipping_price\n",
       "0     1096.80         7.50  105        787.50         5512.50\n",
       "1       24.95         1.49   21         31.29          312.90\n",
       "2       13.52         1.68   39         65.52          458.64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a column for shipping price.\n",
    "# Assume a shipping price of $7 per pound\n",
    "# for orders over 50 pounds and $10 per\n",
    "# pound for items 50 pounds or under.\n",
    "\n",
    "# calculate total weight of order.\n",
    "total_weight = df['unit_weight'] * df['qty']\n",
    "df['total_weight'] = total_weight\n",
    "df\n",
    "\n",
    "\n",
    "df['shipping_price'] = df['total_weight'].apply(lambda x: x * 7 if x > 50 else x * 10)\n",
    "\n",
    "df[['unit_price','unit_weight','qty','total_weight','shipping_price']].head(3)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "77f42c67",
   "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>line_subtotal</th>\n",
       "      <th>shipping_price</th>\n",
       "      <th>line_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>115164.00</td>\n",
       "      <td>5512.50</td>\n",
       "      <td>131839.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>523.95</td>\n",
       "      <td>312.90</td>\n",
       "      <td>914.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>527.28</td>\n",
       "      <td>458.64</td>\n",
       "      <td>1077.12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   line_subtotal  shipping_price  line_price\n",
       "0      115164.00         5512.50   131839.08\n",
       "1         523.95          312.90      914.26\n",
       "2         527.28          458.64     1077.12"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a column for the total price\n",
    "# using the subtotal and the shipping price\n",
    "# along with a sales tax of 9.25%\n",
    "\n",
    "df['line_price'] = ((df['line_subtotal'] + df['shipping_price']) * 1.0925).round(2)\n",
    "\n",
    "df[['line_subtotal','shipping_price', 'line_price']].head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "c82ad275",
   "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>first</th>\n",
       "      <th>last</th>\n",
       "      <th>job</th>\n",
       "      <th>phone</th>\n",
       "      <th>email</th>\n",
       "      <th>client_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_week</th>\n",
       "      <th>order_year</th>\n",
       "      <th>...</th>\n",
       "      <th>unit_cost</th>\n",
       "      <th>unit_weight</th>\n",
       "      <th>qty</th>\n",
       "      <th>line_number</th>\n",
       "      <th>line_subtotal</th>\n",
       "      <th>total_weight</th>\n",
       "      <th>shipping_price</th>\n",
       "      <th>line_price</th>\n",
       "      <th>line_cost</th>\n",
       "      <th>line_profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Donald</td>\n",
       "      <td>Harding</td>\n",
       "      <td>Immunologist</td>\n",
       "      <td>793-904-7725x39308</td>\n",
       "      <td>harding.donald.7185@sullivan.com</td>\n",
       "      <td>58515</td>\n",
       "      <td>8953482</td>\n",
       "      <td>2023-04-28</td>\n",
       "      <td>17</td>\n",
       "      <td>2023</td>\n",
       "      <td>...</td>\n",
       "      <td>762.71</td>\n",
       "      <td>7.50</td>\n",
       "      <td>105</td>\n",
       "      <td>1</td>\n",
       "      <td>115164.00</td>\n",
       "      <td>787.50</td>\n",
       "      <td>5512.50</td>\n",
       "      <td>131839.08</td>\n",
       "      <td>85597.05</td>\n",
       "      <td>46242.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tiffany</td>\n",
       "      <td>Myers</td>\n",
       "      <td>Music therapist</td>\n",
       "      <td>201.442.4543x942</td>\n",
       "      <td>myers.t.6537@ferguson-johnson.net</td>\n",
       "      <td>37609</td>\n",
       "      <td>8069089</td>\n",
       "      <td>2023-05-19</td>\n",
       "      <td>20</td>\n",
       "      <td>2023</td>\n",
       "      <td>...</td>\n",
       "      <td>15.09</td>\n",
       "      <td>1.49</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>523.95</td>\n",
       "      <td>31.29</td>\n",
       "      <td>312.90</td>\n",
       "      <td>914.26</td>\n",
       "      <td>629.79</td>\n",
       "      <td>284.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Shannon</td>\n",
       "      <td>Watson</td>\n",
       "      <td>Immunologist</td>\n",
       "      <td>687.737.9424x8503</td>\n",
       "      <td>swatson8146@payne.net</td>\n",
       "      <td>57113</td>\n",
       "      <td>1902144</td>\n",
       "      <td>2023-01-29</td>\n",
       "      <td>4</td>\n",
       "      <td>2023</td>\n",
       "      <td>...</td>\n",
       "      <td>7.86</td>\n",
       "      <td>1.68</td>\n",
       "      <td>39</td>\n",
       "      <td>6</td>\n",
       "      <td>527.28</td>\n",
       "      <td>65.52</td>\n",
       "      <td>458.64</td>\n",
       "      <td>1077.12</td>\n",
       "      <td>765.18</td>\n",
       "      <td>311.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Nathan</td>\n",
       "      <td>Baker</td>\n",
       "      <td>Accounting technician</td>\n",
       "      <td>827-788-8123x012</td>\n",
       "      <td>bakernathan@benson.com</td>\n",
       "      <td>46554</td>\n",
       "      <td>9031802</td>\n",
       "      <td>2023-04-25</td>\n",
       "      <td>17</td>\n",
       "      <td>2023</td>\n",
       "      <td>...</td>\n",
       "      <td>24.85</td>\n",
       "      <td>1.23</td>\n",
       "      <td>29</td>\n",
       "      <td>3</td>\n",
       "      <td>1056.18</td>\n",
       "      <td>35.67</td>\n",
       "      <td>356.70</td>\n",
       "      <td>1543.57</td>\n",
       "      <td>1077.35</td>\n",
       "      <td>466.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Christina</td>\n",
       "      <td>Schwartz</td>\n",
       "      <td>Chiropractor</td>\n",
       "      <td>265-829-3643</td>\n",
       "      <td>christinaschwartz9252@mcconnell.com</td>\n",
       "      <td>92089</td>\n",
       "      <td>1322274</td>\n",
       "      <td>2023-05-28</td>\n",
       "      <td>21</td>\n",
       "      <td>2023</td>\n",
       "      <td>...</td>\n",
       "      <td>108.17</td>\n",
       "      <td>46.43</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>3902.00</td>\n",
       "      <td>928.60</td>\n",
       "      <td>6500.20</td>\n",
       "      <td>11364.40</td>\n",
       "      <td>8663.60</td>\n",
       "      <td>2700.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       first      last                    job               phone  \\\n",
       "0     Donald   Harding           Immunologist  793-904-7725x39308   \n",
       "1    Tiffany     Myers        Music therapist    201.442.4543x942   \n",
       "2    Shannon    Watson           Immunologist   687.737.9424x8503   \n",
       "3     Nathan     Baker  Accounting technician    827-788-8123x012   \n",
       "4  Christina  Schwartz           Chiropractor        265-829-3643   \n",
       "\n",
       "                                 email  client_id  order_id  order_date  \\\n",
       "0     harding.donald.7185@sullivan.com      58515   8953482  2023-04-28   \n",
       "1    myers.t.6537@ferguson-johnson.net      37609   8069089  2023-05-19   \n",
       "2                swatson8146@payne.net      57113   1902144  2023-01-29   \n",
       "3               bakernathan@benson.com      46554   9031802  2023-04-25   \n",
       "4  christinaschwartz9252@mcconnell.com      92089   1322274  2023-05-28   \n",
       "\n",
       "   order_week  order_year  ... unit_cost unit_weight  qty  line_number  \\\n",
       "0          17        2023  ...    762.71        7.50  105            1   \n",
       "1          20        2023  ...     15.09        1.49   21            0   \n",
       "2           4        2023  ...      7.86        1.68   39            6   \n",
       "3          17        2023  ...     24.85        1.23   29            3   \n",
       "4          21        2023  ...    108.17       46.43   20            1   \n",
       "\n",
       "   line_subtotal  total_weight  shipping_price  line_price  line_cost  \\\n",
       "0      115164.00        787.50         5512.50   131839.08   85597.05   \n",
       "1         523.95         31.29          312.90      914.26     629.79   \n",
       "2         527.28         65.52          458.64     1077.12     765.18   \n",
       "3        1056.18         35.67          356.70     1543.57    1077.35   \n",
       "4        3902.00        928.60         6500.20    11364.40    8663.60   \n",
       "\n",
       "   line_profit  \n",
       "0     46242.03  \n",
       "1       284.47  \n",
       "2       311.94  \n",
       "3       466.22  \n",
       "4      2700.80  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a column for the cost of each line using unit cost, qty, and\n",
    "# shipping price (assume the shipping cost is exactly what is charged to the client).\n",
    "\n",
    "df[\"line_cost\"] = (df[\"unit_cost\"] * df[\"qty\"]) + df[\"shipping_price\"]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "20e541e7",
   "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>first</th>\n",
       "      <th>last</th>\n",
       "      <th>job</th>\n",
       "      <th>phone</th>\n",
       "      <th>email</th>\n",
       "      <th>client_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_week</th>\n",
       "      <th>order_year</th>\n",
       "      <th>...</th>\n",
       "      <th>unit_cost</th>\n",
       "      <th>unit_weight</th>\n",
       "      <th>qty</th>\n",
       "      <th>line_number</th>\n",
       "      <th>line_subtotal</th>\n",
       "      <th>total_weight</th>\n",
       "      <th>shipping_price</th>\n",
       "      <th>line_price</th>\n",
       "      <th>line_cost</th>\n",
       "      <th>line_profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Donald</td>\n",
       "      <td>Harding</td>\n",
       "      <td>Immunologist</td>\n",
       "      <td>793-904-7725x39308</td>\n",
       "      <td>harding.donald.7185@sullivan.com</td>\n",
       "      <td>58515</td>\n",
       "      <td>8953482</td>\n",
       "      <td>2023-04-28</td>\n",
       "      <td>17</td>\n",
       "      <td>2023</td>\n",
       "      <td>...</td>\n",
       "      <td>762.71</td>\n",
       "      <td>7.50</td>\n",
       "      <td>105</td>\n",
       "      <td>1</td>\n",
       "      <td>115164.00</td>\n",
       "      <td>787.50</td>\n",
       "      <td>5512.50</td>\n",
       "      <td>131839.08</td>\n",
       "      <td>85597.05</td>\n",
       "      <td>46242.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tiffany</td>\n",
       "      <td>Myers</td>\n",
       "      <td>Music therapist</td>\n",
       "      <td>201.442.4543x942</td>\n",
       "      <td>myers.t.6537@ferguson-johnson.net</td>\n",
       "      <td>37609</td>\n",
       "      <td>8069089</td>\n",
       "      <td>2023-05-19</td>\n",
       "      <td>20</td>\n",
       "      <td>2023</td>\n",
       "      <td>...</td>\n",
       "      <td>15.09</td>\n",
       "      <td>1.49</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>523.95</td>\n",
       "      <td>31.29</td>\n",
       "      <td>312.90</td>\n",
       "      <td>914.26</td>\n",
       "      <td>629.79</td>\n",
       "      <td>284.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Shannon</td>\n",
       "      <td>Watson</td>\n",
       "      <td>Immunologist</td>\n",
       "      <td>687.737.9424x8503</td>\n",
       "      <td>swatson8146@payne.net</td>\n",
       "      <td>57113</td>\n",
       "      <td>1902144</td>\n",
       "      <td>2023-01-29</td>\n",
       "      <td>4</td>\n",
       "      <td>2023</td>\n",
       "      <td>...</td>\n",
       "      <td>7.86</td>\n",
       "      <td>1.68</td>\n",
       "      <td>39</td>\n",
       "      <td>6</td>\n",
       "      <td>527.28</td>\n",
       "      <td>65.52</td>\n",
       "      <td>458.64</td>\n",
       "      <td>1077.12</td>\n",
       "      <td>765.18</td>\n",
       "      <td>311.94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     first     last              job               phone  \\\n",
       "0   Donald  Harding     Immunologist  793-904-7725x39308   \n",
       "1  Tiffany    Myers  Music therapist    201.442.4543x942   \n",
       "2  Shannon   Watson     Immunologist   687.737.9424x8503   \n",
       "\n",
       "                               email  client_id  order_id  order_date  \\\n",
       "0   harding.donald.7185@sullivan.com      58515   8953482  2023-04-28   \n",
       "1  myers.t.6537@ferguson-johnson.net      37609   8069089  2023-05-19   \n",
       "2              swatson8146@payne.net      57113   1902144  2023-01-29   \n",
       "\n",
       "   order_week  order_year  ... unit_cost unit_weight  qty  line_number  \\\n",
       "0          17        2023  ...    762.71        7.50  105            1   \n",
       "1          20        2023  ...     15.09        1.49   21            0   \n",
       "2           4        2023  ...      7.86        1.68   39            6   \n",
       "\n",
       "   line_subtotal  total_weight  shipping_price  line_price  line_cost  \\\n",
       "0      115164.00        787.50         5512.50   131839.08   85597.05   \n",
       "1         523.95         31.29          312.90      914.26     629.79   \n",
       "2         527.28         65.52          458.64     1077.12     765.18   \n",
       "\n",
       "   line_profit  \n",
       "0     46242.03  \n",
       "1       284.47  \n",
       "2       311.94  \n",
       "\n",
       "[3 rows x 24 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a column for the cost\n",
    "# of each line using unit cost, qty, and\n",
    "# shipping price (assume the shipping cost\n",
    "# is exactly what is charged to the client).\n",
    "\n",
    "line_cost = (df['unit_cost'] * df['qty']) + df['shipping_price']\n",
    "df['line_cost'] = line_cost\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "e2389018",
   "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>first</th>\n",
       "      <th>last</th>\n",
       "      <th>job</th>\n",
       "      <th>phone</th>\n",
       "      <th>email</th>\n",
       "      <th>client_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_week</th>\n",
       "      <th>order_year</th>\n",
       "      <th>...</th>\n",
       "      <th>unit_cost</th>\n",
       "      <th>unit_weight</th>\n",
       "      <th>qty</th>\n",
       "      <th>line_number</th>\n",
       "      <th>line_subtotal</th>\n",
       "      <th>total_weight</th>\n",
       "      <th>shipping_price</th>\n",
       "      <th>line_price</th>\n",
       "      <th>line_cost</th>\n",
       "      <th>line_profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Donald</td>\n",
       "      <td>Harding</td>\n",
       "      <td>Immunologist</td>\n",
       "      <td>793-904-7725x39308</td>\n",
       "      <td>harding.donald.7185@sullivan.com</td>\n",
       "      <td>58515</td>\n",
       "      <td>8953482</td>\n",
       "      <td>2023-04-28</td>\n",
       "      <td>17</td>\n",
       "      <td>2023</td>\n",
       "      <td>...</td>\n",
       "      <td>762.71</td>\n",
       "      <td>7.50</td>\n",
       "      <td>105</td>\n",
       "      <td>1</td>\n",
       "      <td>115164.00</td>\n",
       "      <td>787.50</td>\n",
       "      <td>5512.50</td>\n",
       "      <td>131839.08</td>\n",
       "      <td>85597.05</td>\n",
       "      <td>46242.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tiffany</td>\n",
       "      <td>Myers</td>\n",
       "      <td>Music therapist</td>\n",
       "      <td>201.442.4543x942</td>\n",
       "      <td>myers.t.6537@ferguson-johnson.net</td>\n",
       "      <td>37609</td>\n",
       "      <td>8069089</td>\n",
       "      <td>2023-05-19</td>\n",
       "      <td>20</td>\n",
       "      <td>2023</td>\n",
       "      <td>...</td>\n",
       "      <td>15.09</td>\n",
       "      <td>1.49</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>523.95</td>\n",
       "      <td>31.29</td>\n",
       "      <td>312.90</td>\n",
       "      <td>914.26</td>\n",
       "      <td>629.79</td>\n",
       "      <td>284.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Shannon</td>\n",
       "      <td>Watson</td>\n",
       "      <td>Immunologist</td>\n",
       "      <td>687.737.9424x8503</td>\n",
       "      <td>swatson8146@payne.net</td>\n",
       "      <td>57113</td>\n",
       "      <td>1902144</td>\n",
       "      <td>2023-01-29</td>\n",
       "      <td>4</td>\n",
       "      <td>2023</td>\n",
       "      <td>...</td>\n",
       "      <td>7.86</td>\n",
       "      <td>1.68</td>\n",
       "      <td>39</td>\n",
       "      <td>6</td>\n",
       "      <td>527.28</td>\n",
       "      <td>65.52</td>\n",
       "      <td>458.64</td>\n",
       "      <td>1077.12</td>\n",
       "      <td>765.18</td>\n",
       "      <td>311.94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     first     last              job               phone  \\\n",
       "0   Donald  Harding     Immunologist  793-904-7725x39308   \n",
       "1  Tiffany    Myers  Music therapist    201.442.4543x942   \n",
       "2  Shannon   Watson     Immunologist   687.737.9424x8503   \n",
       "\n",
       "                               email  client_id  order_id  order_date  \\\n",
       "0   harding.donald.7185@sullivan.com      58515   8953482  2023-04-28   \n",
       "1  myers.t.6537@ferguson-johnson.net      37609   8069089  2023-05-19   \n",
       "2              swatson8146@payne.net      57113   1902144  2023-01-29   \n",
       "\n",
       "   order_week  order_year  ... unit_cost unit_weight  qty  line_number  \\\n",
       "0          17        2023  ...    762.71        7.50  105            1   \n",
       "1          20        2023  ...     15.09        1.49   21            0   \n",
       "2           4        2023  ...      7.86        1.68   39            6   \n",
       "\n",
       "   line_subtotal  total_weight  shipping_price  line_price  line_cost  \\\n",
       "0      115164.00        787.50         5512.50   131839.08   85597.05   \n",
       "1         523.95         31.29          312.90      914.26     629.79   \n",
       "2         527.28         65.52          458.64     1077.12     765.18   \n",
       "\n",
       "   line_profit  \n",
       "0     46242.03  \n",
       "1       284.47  \n",
       "2       311.94  \n",
       "\n",
       "[3 rows x 24 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a column for the profit of\n",
    "# each line using line cost and line price\n",
    "\n",
    "line_profit = df['line_price'] - df['line_cost']\n",
    "df['line_profit'] = line_profit.round(2)\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef6f62b1",
   "metadata": {},
   "source": [
    "## Part 3: Confirm your work\n",
    "You have email receipts showing that the total prices for 3 orders. Confirm that your calculations match the receipts. Remember, each order has multiple lines.\n",
    "\n",
    "Order ID 2742071 had a total price of \\$152,811.89\n",
    "\n",
    "Order ID 2173913 had a total price of \\$162,388.71\n",
    "\n",
    "Order ID 6128929 had a total price of \\$923,441.25\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "c0e5d68e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Order ID 2742071 had a total price of $152,811.89\n",
      "Order ID 2173913 had a total price of $162,388.71\n",
      "Order ID 6128929 had a total price of $923,441.25\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Check your work using the totals above\n",
    "\n",
    "order_ids = [2742071, 2173913, 6128929]\n",
    "   \n",
    "for order_id in order_ids:\n",
    "    order_id_isolated = df[df.order_id == order_id]\n",
    "    total_prices = order_id_isolated['line_price'].sum()\n",
    "    print(f\"Order ID {order_id} had a total price of ${total_prices:,.2f}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "802687a1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "152811.88999999998\n"
     ]
    }
   ],
   "source": [
    "# Check your work using the totals above\n",
    "# Total Price of Order ID: 2742071\n",
    "\n",
    "# Isolate the itms with the order id of 2742071\n",
    "order_2742071 = df[df['order_id'] == 2742071]\n",
    "\n",
    "# Calculate the sum of the line_prices for the items listed under order 2742071\n",
    "total_price_2742071 = order_2742071['line_price'].sum()\n",
    "\n",
    "\n",
    "print(total_price_2742071)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "6844d7a7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "162388.71\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Total Price of Order ID: 2173913\n",
    "\n",
    "# Isolate the itms with the order id of 2173913\n",
    "order_2173913 = df[df['order_id'] == 2173913]\n",
    "\n",
    "# Calculate the sum of the line_prices for the items listed under order 2173913\n",
    "total_price_2173913 = order_2173913['line_price'].sum()\n",
    "\n",
    "print(total_price_2173913)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "c10f0c5e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "923441.25\n"
     ]
    }
   ],
   "source": [
    "# Total Price of Order ID: 6128929\n",
    "\n",
    "# Isolate the itms with the order id of 6128929\n",
    "order_6128929 = df[df['order_id'] == 6128929]\n",
    "\n",
    "# Calculate the sum of the line_prices for the items listed under order 6128929\n",
    "total_price_6128929 = order_6128929['line_price'].sum()\n",
    "\n",
    "print(total_price_6128929)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b94d58ab",
   "metadata": {},
   "source": [
    "## Part 4: Summarize and Analyze\n",
    "Use the new columns with confirmed values to find the following information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "7c005f34",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'client_id': 33615, 'client_orders_total': 8377308.520000001}, {'client_id': 66037, 'client_orders_total': 10259514.79}, {'client_id': 46820, 'client_orders_total': 9743794.36}, {'client_id': 38378, 'client_orders_total': 12906550.869999997}, {'client_id': 24741, 'client_orders_total': 82268892.02000001}]\n"
     ]
    }
   ],
   "source": [
    "# How much did each of the top 5 clients by quantity spend? Check your work from Part 1 for client ids.\n",
    "\n",
    "top_order_totals = []\n",
    "\n",
    "for client_id in top_five_client_ids:\n",
    "\n",
    "    # Extract all of the orders placed by the top client\n",
    "    client = df[df['client_id'] == client_id]\n",
    "\n",
    "    # Total number of orders placed by the top client\n",
    "    client_orders_total = client['line_price'].sum()\n",
    "\n",
    "    # Add the client ID and total to the list\n",
    "    top_order_totals.append({\"client_id\": client_id, \"client_orders_total\": client_orders_total})\n",
    "\n",
    "print(top_order_totals)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "786fe01c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 33615 : $8,377,308.52\n",
      " 66037 : $10,259,514.79\n",
      " 46820 : $9,743,794.36\n",
      " 38378 : $12,906,550.87\n",
      " 24741 : $82,268,892.02\n"
     ]
    }
   ],
   "source": [
    "top5_clients = [33615, 66037, 46820, 38378, 24741]\n",
    "\n",
    "for client_id in top5_clients:  \n",
    "    client_id_isolated = df[df.client_id == client_id]\n",
    "    top5_total_price = client_id_isolated['line_price'].sum()\n",
    "    print(f\" {client_id} : ${top5_total_price:,.2f}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "81b6e4d2",
   "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>qty</th>\n",
       "      <th>shipping_price</th>\n",
       "      <th>line_price</th>\n",
       "      <th>line_cost</th>\n",
       "      <th>line_profit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>client_id</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>24741</th>\n",
       "      <td>239862</td>\n",
       "      <td>5126448.37</td>\n",
       "      <td>82268892.02</td>\n",
       "      <td>45688899.71</td>\n",
       "      <td>36579992.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38378</th>\n",
       "      <td>73667</td>\n",
       "      <td>3429455.40</td>\n",
       "      <td>12906550.87</td>\n",
       "      <td>9634720.98</td>\n",
       "      <td>3271829.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66037</th>\n",
       "      <td>43018</td>\n",
       "      <td>1395151.85</td>\n",
       "      <td>10259514.79</td>\n",
       "      <td>7004482.98</td>\n",
       "      <td>3255031.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46820</th>\n",
       "      <td>75768</td>\n",
       "      <td>1601448.84</td>\n",
       "      <td>9743794.36</td>\n",
       "      <td>7007191.64</td>\n",
       "      <td>2736602.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33615</th>\n",
       "      <td>64313</td>\n",
       "      <td>1828984.89</td>\n",
       "      <td>8377308.52</td>\n",
       "      <td>6175313.91</td>\n",
       "      <td>2201994.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              qty  shipping_price   line_price    line_cost  line_profit\n",
       "client_id                                                               \n",
       "24741      239862      5126448.37  82268892.02  45688899.71  36579992.31\n",
       "38378       73667      3429455.40  12906550.87   9634720.98   3271829.89\n",
       "66037       43018      1395151.85  10259514.79   7004482.98   3255031.81\n",
       "46820       75768      1601448.84   9743794.36   7007191.64   2736602.72\n",
       "33615       64313      1828984.89   8377308.52   6175313.91   2201994.61"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a summary DataFrame showing the totals for the for the top 5 clients with the following information:\n",
    "# total units purchased, total shipping price, total revenue, and total profit. \n",
    "\n",
    "top5_df = df.loc[df['client_id'].isin(top5_clients)]\n",
    "summary_top5 = top5_df[['client_id', 'qty', 'shipping_price', 'line_price', 'line_cost', 'line_profit']]\n",
    "summary_top5\n",
    "\n",
    "Summary = summary_top5.groupby(['client_id']).sum()\n",
    "Summary.sort_values('line_profit', ascending=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "7be02867",
   "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>Units</th>\n",
       "      <th>Shipping</th>\n",
       "      <th>Total Revenue</th>\n",
       "      <th>Total Cost</th>\n",
       "      <th>Total Profit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>client_id</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>24741</th>\n",
       "      <td>239862</td>\n",
       "      <td>$5.13M</td>\n",
       "      <td>$82.27M</td>\n",
       "      <td>$45.69M</td>\n",
       "      <td>$36.58M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33615</th>\n",
       "      <td>64313</td>\n",
       "      <td>$1.83M</td>\n",
       "      <td>$8.38M</td>\n",
       "      <td>$6.18M</td>\n",
       "      <td>$2.20M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38378</th>\n",
       "      <td>73667</td>\n",
       "      <td>$3.43M</td>\n",
       "      <td>$12.91M</td>\n",
       "      <td>$9.63M</td>\n",
       "      <td>$3.27M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46820</th>\n",
       "      <td>75768</td>\n",
       "      <td>$1.60M</td>\n",
       "      <td>$9.74M</td>\n",
       "      <td>$7.01M</td>\n",
       "      <td>$2.74M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66037</th>\n",
       "      <td>43018</td>\n",
       "      <td>$1.40M</td>\n",
       "      <td>$10.26M</td>\n",
       "      <td>$7.00M</td>\n",
       "      <td>$3.26M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Units Shipping Total Revenue Total Cost Total Profit\n",
       "client_id                                                       \n",
       "24741      239862   $5.13M       $82.27M    $45.69M      $36.58M\n",
       "33615       64313   $1.83M        $8.38M     $6.18M       $2.20M\n",
       "38378       73667   $3.43M       $12.91M     $9.63M       $3.27M\n",
       "46820       75768   $1.60M        $9.74M     $7.01M       $2.74M\n",
       "66037       43018   $1.40M       $10.26M     $7.00M       $3.26M"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Format the data and rename the columns to names suitable for presentation.\n",
    "\n",
    "# Define the money columns. \n",
    "\n",
    "def format_to_millions(series):\n",
    "    return series.apply(lambda x: \"${:.2f}M\".format(x / 1000000))\n",
    "\n",
    "\n",
    "Summary.rename(columns={'qty':'Units','shipping_price':'Shipping','line_price':'Total Revenue', 'line_cost':'Total Cost','line_profit':'Total Profit'}, inplace=True)\n",
    "Summary\n",
    "\n",
    "# Define a function that converts a dollar amount to millions.\n",
    "\n",
    "# Apply the currency_format_millions function to only the money columns. \n",
    "\n",
    "# Rename the columns to reflect the change in the money format. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "8ead0986",
   "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>Units</th>\n",
       "      <th>Shipping</th>\n",
       "      <th>Total Revenue</th>\n",
       "      <th>Total Cost</th>\n",
       "      <th>Total Profit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>client_id</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>24741</th>\n",
       "      <td>239862</td>\n",
       "      <td>$5.13M</td>\n",
       "      <td>$82.27M</td>\n",
       "      <td>$45.69M</td>\n",
       "      <td>$36.58M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33615</th>\n",
       "      <td>64313</td>\n",
       "      <td>$1.83M</td>\n",
       "      <td>$8.38M</td>\n",
       "      <td>$6.18M</td>\n",
       "      <td>$2.20M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38378</th>\n",
       "      <td>73667</td>\n",
       "      <td>$3.43M</td>\n",
       "      <td>$12.91M</td>\n",
       "      <td>$9.63M</td>\n",
       "      <td>$3.27M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46820</th>\n",
       "      <td>75768</td>\n",
       "      <td>$1.60M</td>\n",
       "      <td>$9.74M</td>\n",
       "      <td>$7.01M</td>\n",
       "      <td>$2.74M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66037</th>\n",
       "      <td>43018</td>\n",
       "      <td>$1.40M</td>\n",
       "      <td>$10.26M</td>\n",
       "      <td>$7.00M</td>\n",
       "      <td>$3.26M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Units Shipping Total Revenue Total Cost Total Profit\n",
       "client_id                                                       \n",
       "24741      239862   $5.13M       $82.27M    $45.69M      $36.58M\n",
       "33615       64313   $1.83M        $8.38M     $6.18M       $2.20M\n",
       "38378       73667   $3.43M       $12.91M     $9.63M       $3.27M\n",
       "46820       75768   $1.60M        $9.74M     $7.01M       $2.74M\n",
       "66037       43018   $1.40M       $10.26M     $7.00M       $3.26M"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sort the updated data by \"Total Profit (millions)\" form highest to lowest and assign the sort to a new DatFrame.\n",
    "# Currency should be in millions of dollars.\n",
    "\n",
    "def format_to_millions(series):\n",
    "    return series.apply(lambda x: \"${:.2f}M\".format(x / 1000000))\n",
    "\n",
    "\n",
    "Summary[['Shipping', 'Total Revenue', 'Total Cost', 'Total Profit']] = Summary[['Shipping', 'Total Revenue', 'Total Cost', 'Total Profit']].apply(format_to_millions)\n",
    "\n",
    "Summary.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "87e0e7f8-b60e-4e50-ab07-c36b6deed513",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Despite client 33615 leading in order volume with 220 orders, their total revenue of $8.38 million was the least compared to the other four top-ordering clients. On the other hand, client 24741 stood out as the most lucrative, purchasing a whopping 239,862 units and amassing an impressive profit of $36.58 million. This profit was over ten times greater than that of the runner-up, who earned $2.20 million. This scenario illustrates that a higher order count does not necessarily equate to increased profitability.\n"
     ]
    }
   ],
   "source": [
    "# Write a brief 2-3 sentence summary of your findings\n",
    "print('Despite client 33615 leading in order volume with 220 orders, their total revenue of $8.38 million was the least compared to the other four top-ordering clients. On the other hand, client 24741 stood out as the most lucrative, purchasing a whopping 239,862 units and amassing an impressive profit of $36.58 million. This profit was over ten times greater than that of the runner-up, who earned $2.20 million. This scenario illustrates that a higher order count does not necessarily equate to increased profitability.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7862d018",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}