Stores Case Study.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os as os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The dataset dimensions are: (9994, 21)\n",
      "Row ID             int64\n",
      "Order ID          object\n",
      "Order Date        object\n",
      "Ship Date         object\n",
      "Ship Mode         object\n",
      "Customer ID       object\n",
      "Customer Name     object\n",
      "Segment           object\n",
      "Country           object\n",
      "City              object\n",
      "State             object\n",
      "Postal Code        int64\n",
      "Region            object\n",
      "Product ID        object\n",
      "Category          object\n",
      "Sub-Category      object\n",
      "Product Name      object\n",
      "Sales            float64\n",
      "Quantity           int64\n",
      "Discount         float64\n",
      "Profit           float64\n",
      "dtype: object\n",
      "Index(['Row ID', 'Order ID', 'Order Date', 'Ship Date', 'Ship Mode',\n",
      "       'Customer ID', 'Customer Name', 'Segment', 'Country', 'City', 'State',\n",
      "       'Postal Code', 'Region', 'Product ID', 'Category', 'Sub-Category',\n",
      "       'Product Name', 'Sales', 'Quantity', 'Discount', 'Profit'],\n",
      "      dtype='object')\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>Row ID</th>\n",
       "      <th>Postal Code</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>9994.000000</td>\n",
       "      <td>9994.000000</td>\n",
       "      <td>9994.000000</td>\n",
       "      <td>9994.000000</td>\n",
       "      <td>9994.000000</td>\n",
       "      <td>9994.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>4997.500000</td>\n",
       "      <td>55190.379428</td>\n",
       "      <td>229.858001</td>\n",
       "      <td>3.789574</td>\n",
       "      <td>0.156203</td>\n",
       "      <td>28.656896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2885.163629</td>\n",
       "      <td>32063.693350</td>\n",
       "      <td>623.245101</td>\n",
       "      <td>2.225110</td>\n",
       "      <td>0.206452</td>\n",
       "      <td>234.260108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1040.000000</td>\n",
       "      <td>0.444000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-6599.978000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>2499.250000</td>\n",
       "      <td>23223.000000</td>\n",
       "      <td>17.280000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.728750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>4997.500000</td>\n",
       "      <td>56430.500000</td>\n",
       "      <td>54.490000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>8.666500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>7495.750000</td>\n",
       "      <td>90008.000000</td>\n",
       "      <td>209.940000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>29.364000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>9994.000000</td>\n",
       "      <td>99301.000000</td>\n",
       "      <td>22638.480000</td>\n",
       "      <td>14.000000</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>8399.976000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Row ID   Postal Code         Sales     Quantity     Discount  \\\n",
       "count  9994.000000   9994.000000   9994.000000  9994.000000  9994.000000   \n",
       "mean   4997.500000  55190.379428    229.858001     3.789574     0.156203   \n",
       "std    2885.163629  32063.693350    623.245101     2.225110     0.206452   \n",
       "min       1.000000   1040.000000      0.444000     1.000000     0.000000   \n",
       "25%    2499.250000  23223.000000     17.280000     2.000000     0.000000   \n",
       "50%    4997.500000  56430.500000     54.490000     3.000000     0.200000   \n",
       "75%    7495.750000  90008.000000    209.940000     5.000000     0.200000   \n",
       "max    9994.000000  99301.000000  22638.480000    14.000000     0.800000   \n",
       "\n",
       "            Profit  \n",
       "count  9994.000000  \n",
       "mean     28.656896  \n",
       "std     234.260108  \n",
       "min   -6599.978000  \n",
       "25%       1.728750  \n",
       "50%       8.666500  \n",
       "75%      29.364000  \n",
       "max    8399.976000  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reading the Data Set\n",
    "\n",
    "store = pd.read_csv('C:\\\\Users\\\\Administrator\\\\Documents\\\\Python Jupyter\\\\Python\\\\Python Assignments\\\\Store.csv',encoding=\"Latin\",sep=',')\n",
    "\n",
    "#1. Dimensions of Dataset\n",
    "print(\"The dataset dimensions are:\", store.shape)\n",
    "\n",
    "#2. Structure of Dataset\n",
    "print(store.dtypes)\n",
    "\n",
    "#3. Column Names of Dataset \n",
    "print(store.columns)\n",
    "\n",
    "#4. Summary of dataset\n",
    "store.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The no. of unique cities where orders are delivered are:  531\n",
      "['Henderson' 'Los Angeles' 'Fort Lauderdale' 'Concord' 'Seattle'\n",
      " 'Fort Worth' 'Madison' 'West Jordan' 'San Francisco' 'Fremont'\n",
      " 'Philadelphia' 'Orem' 'Houston' 'Richardson' 'Naperville' 'Melbourne'\n",
      " 'Eagan' 'Westland' 'Dover' 'New Albany' 'New York City' 'Troy' 'Chicago'\n",
      " 'Gilbert' 'Springfield' 'Jackson' 'Memphis' 'Decatur' 'Durham' 'Columbia'\n",
      " 'Rochester' 'Minneapolis' 'Portland' 'Saint Paul' 'Aurora' 'Charlotte'\n",
      " 'Orland Park' 'Urbandale' 'Columbus' 'Bristol' 'Wilmington' 'Bloomington'\n",
      " 'Phoenix' 'Roseville' 'Independence' 'Pasadena' 'Newark' 'Franklin'\n",
      " 'Scottsdale' 'San Jose' 'Edmond' 'Carlsbad' 'San Antonio' 'Monroe'\n",
      " 'Fairfield' 'Grand Prairie' 'Redlands' 'Hamilton' 'Westfield' 'Akron'\n",
      " 'Denver' 'Dallas' 'Whittier' 'Saginaw' 'Medina' 'Dublin' 'Detroit'\n",
      " 'Tampa' 'Santa Clara' 'Lakeville' 'San Diego' 'Brentwood' 'Chapel Hill'\n",
      " 'Morristown' 'Cincinnati' 'Inglewood' 'Tamarac' 'Colorado Springs'\n",
      " 'Belleville' 'Taylor' 'Lakewood' 'Arlington' 'Arvada' 'Hackensack'\n",
      " 'Saint Petersburg' 'Long Beach' 'Hesperia' 'Murfreesboro' 'Layton'\n",
      " 'Austin' 'Lowell' 'Manchester' 'Harlingen' 'Tucson' 'Quincy'\n",
      " 'Pembroke Pines' 'Des Moines' 'Peoria' 'Las Vegas' 'Warwick' 'Miami'\n",
      " 'Huntington Beach' 'Richmond' 'Louisville' 'Lawrence' 'Canton'\n",
      " 'New Rochelle' 'Gastonia' 'Jacksonville' 'Auburn' 'Norman' 'Park Ridge'\n",
      " 'Amarillo' 'Lindenhurst' 'Huntsville' 'Fayetteville' 'Costa Mesa'\n",
      " 'Parker' 'Atlanta' 'Gladstone' 'Great Falls' 'Lakeland' 'Montgomery'\n",
      " 'Mesa' 'Green Bay' 'Anaheim' 'Marysville' 'Salem' 'Laredo' 'Grove City'\n",
      " 'Dearborn' 'Warner Robins' 'Vallejo' 'Mission Viejo' 'Rochester Hills'\n",
      " 'Plainfield' 'Sierra Vista' 'Vancouver' 'Cleveland' 'Tyler' 'Burlington'\n",
      " 'Waynesboro' 'Chester' 'Cary' 'Palm Coast' 'Mount Vernon' 'Hialeah'\n",
      " 'Oceanside' 'Evanston' 'Trenton' 'Cottage Grove' 'Bossier City'\n",
      " 'Lancaster' 'Asheville' 'Lake Elsinore' 'Omaha' 'Edmonds' 'Santa Ana'\n",
      " 'Milwaukee' 'Florence' 'Lorain' 'Linden' 'Salinas' 'New Brunswick'\n",
      " 'Garland' 'Norwich' 'Alexandria' 'Toledo' 'Farmington' 'Riverside'\n",
      " 'Torrance' 'Round Rock' 'Boca Raton' 'Virginia Beach' 'Murrieta'\n",
      " 'Olympia' 'Washington' 'Jefferson City' 'Saint Peters' 'Rockford'\n",
      " 'Brownsville' 'Yonkers' 'Oakland' 'Clinton' 'Encinitas' 'Roswell'\n",
      " 'Jonesboro' 'Antioch' 'Homestead' 'La Porte' 'Lansing' 'Cuyahoga Falls'\n",
      " 'Reno' 'Harrisonburg' 'Escondido' 'Royal Oak' 'Rockville' 'Coral Springs'\n",
      " 'Buffalo' 'Boynton Beach' 'Gulfport' 'Fresno' 'Greenville' 'Macon'\n",
      " 'Cedar Rapids' 'Providence' 'Pueblo' 'Deltona' 'Murray' 'Middletown'\n",
      " 'Freeport' 'Pico Rivera' 'Provo' 'Pleasant Grove' 'Smyrna' 'Parma'\n",
      " 'Mobile' 'New Bedford' 'Irving' 'Vineland' 'Glendale' 'Niagara Falls'\n",
      " 'Thomasville' 'Westminster' 'Coppell' 'Pomona' 'North Las Vegas'\n",
      " 'Allentown' 'Tempe' 'Laguna Niguel' 'Bridgeton' 'Everett' 'Watertown'\n",
      " 'Appleton' 'Bellevue' 'Allen' 'El Paso' 'Grapevine' 'Carrollton' 'Kent'\n",
      " 'Lafayette' 'Tigard' 'Skokie' 'Plano' 'Suffolk' 'Indianapolis' 'Bayonne'\n",
      " 'Greensboro' 'Baltimore' 'Kenosha' 'Olathe' 'Tulsa' 'Redmond' 'Raleigh'\n",
      " 'Muskogee' 'Meriden' 'Bowling Green' 'South Bend' 'Spokane' 'Keller'\n",
      " 'Port Orange' 'Medford' 'Charlottesville' 'Missoula' 'Apopka' 'Reading'\n",
      " 'Broomfield' 'Paterson' 'Oklahoma City' 'Chesapeake' 'Lubbock'\n",
      " 'Johnson City' 'San Bernardino' 'Leominster' 'Bozeman' 'Perth Amboy'\n",
      " 'Ontario' 'Rancho Cucamonga' 'Moorhead' 'Mesquite' 'Stockton'\n",
      " 'Ormond Beach' 'Sunnyvale' 'York' 'College Station' 'Saint Louis'\n",
      " 'Manteca' 'San Angelo' 'Salt Lake City' 'Knoxville' 'Little Rock'\n",
      " 'Lincoln Park' 'Marion' 'Littleton' 'Bangor' 'Southaven' 'New Castle'\n",
      " 'Midland' 'Sioux Falls' 'Fort Collins' 'Clarksville' 'Sacramento'\n",
      " 'Thousand Oaks' 'Malden' 'Holyoke' 'Albuquerque' 'Sparks' 'Coachella'\n",
      " 'Elmhurst' 'Passaic' 'North Charleston' 'Newport News' 'Jamestown'\n",
      " 'Mishawaka' 'La Quinta' 'Tallahassee' 'Nashville' 'Bellingham'\n",
      " 'Woodstock' 'Haltom City' 'Wheeling' 'Summerville' 'Hot Springs'\n",
      " 'Englewood' 'Las Cruces' 'Hoover' 'Frisco' 'Vacaville' 'Waukesha'\n",
      " 'Bakersfield' 'Pompano Beach' 'Corpus Christi' 'Redondo Beach' 'Orlando'\n",
      " 'Orange' 'Lake Charles' 'Highland Park' 'Hempstead' 'Noblesville'\n",
      " 'Apple Valley' 'Mount Pleasant' 'Sterling Heights' 'Eau Claire' 'Pharr'\n",
      " 'Billings' 'Gresham' 'Chattanooga' 'Meridian' 'Bolingbrook' 'Maple Grove'\n",
      " 'Woodland' 'Missouri City' 'Pearland' 'San Mateo' 'Grand Rapids'\n",
      " 'Visalia' 'Overland Park' 'Temecula' 'Yucaipa' 'Revere' 'Conroe'\n",
      " 'Tinley Park' 'Dubuque' 'Dearborn Heights' 'Santa Fe' 'Hickory'\n",
      " 'Carol Stream' 'Saint Cloud' 'North Miami' 'Plantation'\n",
      " 'Port Saint Lucie' 'Rock Hill' 'Odessa' 'West Allis' 'Chula Vista'\n",
      " 'Manhattan' 'Altoona' 'Thornton' 'Champaign' 'Texarkana' 'Edinburg'\n",
      " 'Baytown' 'Greenwood' 'Woonsocket' 'Superior' 'Bedford' 'Covington'\n",
      " 'Broken Arrow' 'Miramar' 'Hollywood' 'Deer Park' 'Wichita' 'Mcallen'\n",
      " 'Iowa City' 'Boise' 'Cranston' 'Port Arthur' 'Citrus Heights'\n",
      " 'The Colony' 'Daytona Beach' 'Bullhead City' 'Portage' 'Fargo' 'Elkhart'\n",
      " 'San Gabriel' 'Margate' 'Sandy Springs' 'Mentor' 'Lawton' 'Hampton'\n",
      " 'Rome' 'La Crosse' 'Lewiston' 'Hattiesburg' 'Danville' 'Logan'\n",
      " 'Waterbury' 'Athens' 'Avondale' 'Marietta' 'Yuma' 'Wausau' 'Pasco'\n",
      " 'Oak Park' 'Pensacola' 'League City' 'Gaithersburg' 'Lehi' 'Tuscaloosa'\n",
      " 'Moreno Valley' 'Georgetown' 'Loveland' 'Chandler' 'Helena' 'Kirkwood'\n",
      " 'Waco' 'Frankfort' 'Bethlehem' 'Grand Island' 'Woodbury' 'Rogers'\n",
      " 'Clovis' 'Jupiter' 'Santa Barbara' 'Cedar Hill' 'Norfolk' 'Draper'\n",
      " 'Ann Arbor' 'La Mesa' 'Pocatello' 'Holland' 'Milford' 'Buffalo Grove'\n",
      " 'Lake Forest' 'Redding' 'Chico' 'Utica' 'Conway' 'Cheyenne' 'Owensboro'\n",
      " 'Caldwell' 'Kenner' 'Nashua' 'Bartlett' 'Redwood City' 'Lebanon'\n",
      " 'Santa Maria' 'Des Plaines' 'Longview' 'Hendersonville' 'Waterloo'\n",
      " 'Cambridge' 'Palatine' 'Beverly' 'Eugene' 'Oxnard' 'Renton' 'Glenview'\n",
      " 'Delray Beach' 'Commerce City' 'Texas City' 'Wilson' 'Rio Rancho'\n",
      " 'Goldsboro' 'Montebello' 'El Cajon' 'Beaumont' 'West Palm Beach'\n",
      " 'Abilene' 'Normal' 'Saint Charles' 'Camarillo' 'Hillsboro' 'Burbank'\n",
      " 'Modesto' 'Garden City' 'Atlantic City' 'Longmont' 'Davis' 'Morgan Hill'\n",
      " 'Clifton' 'Sheboygan' 'East Point' 'Rapid City' 'Andover' 'Kissimmee'\n",
      " 'Shelton' 'Danbury' 'Sanford' 'San Marcos' 'Greeley' 'Mansfield' 'Elyria'\n",
      " 'Twin Falls' 'Coral Gables' 'Romeoville' 'Marlborough' 'Laurel' 'Bryan'\n",
      " 'Pine Bluff' 'Aberdeen' 'Hagerstown' 'East Orange' 'Arlington Heights'\n",
      " 'Oswego' 'Coon Rapids' 'San Clemente' 'San Luis Obispo' 'Springdale'\n",
      " 'Lodi' 'Mason']\n"
     ]
    }
   ],
   "source": [
    "#1.How many unique cities are the orders being delivered to?\n",
    "print(\"The no. of unique cities where orders are delivered are: \", len(store.City.unique()))\n",
    "print(store.City.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Quantity    10618\n",
       "dtype: int64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2. What is the total quantity sold in the East Region? AND 3.Find the sum of the quantity sold in the East Region\n",
    "\n",
    "store.query(\"Region == 'East' \")[['Quantity']].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>Sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2697</th>\n",
       "      <td>22638.480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8488</th>\n",
       "      <td>8749.950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683</th>\n",
       "      <td>7999.980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>509</th>\n",
       "      <td>6354.950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4297</th>\n",
       "      <td>4367.896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9639</th>\n",
       "      <td>4297.644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3280</th>\n",
       "      <td>4164.050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7583</th>\n",
       "      <td>3499.930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4093</th>\n",
       "      <td>3080.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1454</th>\n",
       "      <td>3040.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5562</th>\n",
       "      <td>2999.950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8312</th>\n",
       "      <td>2939.930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8236</th>\n",
       "      <td>2803.920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>994</th>\n",
       "      <td>2715.930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2848</th>\n",
       "      <td>2690.970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4128</th>\n",
       "      <td>2624.985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7474</th>\n",
       "      <td>2573.820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3580</th>\n",
       "      <td>2430.080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5320</th>\n",
       "      <td>2314.116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5300</th>\n",
       "      <td>2275.500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4881</th>\n",
       "      <td>2249.910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1155</th>\n",
       "      <td>2244.480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8699</th>\n",
       "      <td>2239.936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8680</th>\n",
       "      <td>2104.550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5301</th>\n",
       "      <td>1979.700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8993</th>\n",
       "      <td>1875.258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7846</th>\n",
       "      <td>1871.880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1433</th>\n",
       "      <td>1819.860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5185</th>\n",
       "      <td>1801.632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9033</th>\n",
       "      <td>1793.980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3381</th>\n",
       "      <td>2.896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8850</th>\n",
       "      <td>2.892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>865</th>\n",
       "      <td>2.890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3589</th>\n",
       "      <td>2.880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7573</th>\n",
       "      <td>2.840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7805</th>\n",
       "      <td>2.816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2217</th>\n",
       "      <td>2.816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2120</th>\n",
       "      <td>2.808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4155</th>\n",
       "      <td>2.808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4300</th>\n",
       "      <td>2.784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5893</th>\n",
       "      <td>2.780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2665</th>\n",
       "      <td>2.742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506</th>\n",
       "      <td>2.740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3687</th>\n",
       "      <td>2.694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8207</th>\n",
       "      <td>2.624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8579</th>\n",
       "      <td>2.610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4913</th>\n",
       "      <td>2.508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8215</th>\n",
       "      <td>2.469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2779</th>\n",
       "      <td>2.376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4878</th>\n",
       "      <td>2.214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5440</th>\n",
       "      <td>2.064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3769</th>\n",
       "      <td>1.984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7311</th>\n",
       "      <td>1.872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5479</th>\n",
       "      <td>1.869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4005</th>\n",
       "      <td>1.780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8317</th>\n",
       "      <td>1.752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7900</th>\n",
       "      <td>1.584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6597</th>\n",
       "      <td>1.365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9382</th>\n",
       "      <td>1.344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2605</th>\n",
       "      <td>1.167</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1620 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Sales\n",
       "2697  22638.480\n",
       "8488   8749.950\n",
       "683    7999.980\n",
       "509    6354.950\n",
       "4297   4367.896\n",
       "9639   4297.644\n",
       "3280   4164.050\n",
       "7583   3499.930\n",
       "4093   3080.000\n",
       "1454   3040.000\n",
       "5562   2999.950\n",
       "8312   2939.930\n",
       "8236   2803.920\n",
       "994    2715.930\n",
       "2848   2690.970\n",
       "4128   2624.985\n",
       "7474   2573.820\n",
       "3580   2430.080\n",
       "5320   2314.116\n",
       "5300   2275.500\n",
       "4881   2249.910\n",
       "1155   2244.480\n",
       "8699   2239.936\n",
       "8680   2104.550\n",
       "5301   1979.700\n",
       "8993   1875.258\n",
       "7846   1871.880\n",
       "1433   1819.860\n",
       "5185   1801.632\n",
       "9033   1793.980\n",
       "...         ...\n",
       "3381      2.896\n",
       "8850      2.892\n",
       "865       2.890\n",
       "3589      2.880\n",
       "7573      2.840\n",
       "7805      2.816\n",
       "2217      2.816\n",
       "2120      2.808\n",
       "4155      2.808\n",
       "4300      2.784\n",
       "5893      2.780\n",
       "2665      2.742\n",
       "506       2.740\n",
       "3687      2.694\n",
       "8207      2.624\n",
       "8579      2.610\n",
       "4913      2.508\n",
       "8215      2.469\n",
       "2779      2.376\n",
       "4878      2.214\n",
       "5440      2.064\n",
       "3769      1.984\n",
       "7311      1.872\n",
       "5479      1.869\n",
       "4005      1.780\n",
       "8317      1.752\n",
       "7900      1.584\n",
       "6597      1.365\n",
       "9382      1.344\n",
       "2605      1.167\n",
       "\n",
       "[1620 rows x 1 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 4. In the south region sort the sales in decreasing order\n",
    "\n",
    "store.query(\"Region == 'South' \")[['Sales']].sort_values(by = \"Sales\",ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central</th>\n",
       "      <td>3.779595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>East</th>\n",
       "      <td>3.728230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South</th>\n",
       "      <td>3.832716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West</th>\n",
       "      <td>3.829535</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Quantity\n",
       "             mean\n",
       "Region           \n",
       "Central  3.779595\n",
       "East     3.728230\n",
       "South    3.832716\n",
       "West     3.829535"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 5. Find the mean of quantity for every region\n",
    "store.groupby(\"Region\")[[\"Quantity\"]].agg(['mean'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>Sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Category</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Furniture</th>\n",
       "      <td>349.834887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Office Supplies</th>\n",
       "      <td>119.324101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Technology</th>\n",
       "      <td>452.709276</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Sales\n",
       "                       mean\n",
       "Category                   \n",
       "Furniture        349.834887\n",
       "Office Supplies  119.324101\n",
       "Technology       452.709276"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#6. Find the mean of sales for every category\n",
    "\n",
    "store.groupby(\"Category\")[[\"Sales\"]].agg(['mean'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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\">Sales</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Profit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>max</th>\n",
       "      <th>min</th>\n",
       "      <th>sum</th>\n",
       "      <th>max</th>\n",
       "      <th>min</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Furniture</th>\n",
       "      <td>4416.174</td>\n",
       "      <td>1.892</td>\n",
       "      <td>741999.7953</td>\n",
       "      <td>1013.127</td>\n",
       "      <td>-1862.3124</td>\n",
       "      <td>18451.2728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Office Supplies</th>\n",
       "      <td>9892.740</td>\n",
       "      <td>0.444</td>\n",
       "      <td>719047.0320</td>\n",
       "      <td>4946.370</td>\n",
       "      <td>-3701.8928</td>\n",
       "      <td>122490.8008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Technology</th>\n",
       "      <td>22638.480</td>\n",
       "      <td>0.990</td>\n",
       "      <td>836154.0330</td>\n",
       "      <td>8399.976</td>\n",
       "      <td>-6599.9780</td>\n",
       "      <td>145454.9481</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Sales                        Profit             \\\n",
       "                       max    min          sum       max        min   \n",
       "Category                                                              \n",
       "Furniture         4416.174  1.892  741999.7953  1013.127 -1862.3124   \n",
       "Office Supplies   9892.740  0.444  719047.0320  4946.370 -3701.8928   \n",
       "Technology       22638.480  0.990  836154.0330  8399.976 -6599.9780   \n",
       "\n",
       "                              \n",
       "                         sum  \n",
       "Category                      \n",
       "Furniture         18451.2728  \n",
       "Office Supplies  122490.8008  \n",
       "Technology       145454.9481  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#7. Find the max, min, sum of sales and profit for every category\n",
    "\n",
    "store.groupby(\"Category\")[['Sales','Profit']].agg(['max','min','sum'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>Sales</th>\n",
       "      <th>Profit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Segment</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Consumer</th>\n",
       "      <td>1.161401e+06</td>\n",
       "      <td>25.836873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Corporate</th>\n",
       "      <td>7.061464e+05</td>\n",
       "      <td>30.456667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Home Office</th>\n",
       "      <td>4.296531e+05</td>\n",
       "      <td>33.818664</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Sales     Profit\n",
       "                      sum       mean\n",
       "Segment                             \n",
       "Consumer     1.161401e+06  25.836873\n",
       "Corporate    7.061464e+05  30.456667\n",
       "Home Office  4.296531e+05  33.818664"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#8. Find sum of sales and max profit for every segment\n",
    "\n",
    "store.groupby(\"Segment\").agg({'Sales':['sum'], 'Profit':['mean']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>Discount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Segment</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Consumer</th>\n",
       "      <td>0.158141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Corporate</th>\n",
       "      <td>0.158228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Home Office</th>\n",
       "      <td>0.147128</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Discount\n",
       "                 mean\n",
       "Segment              \n",
       "Consumer     0.158141\n",
       "Corporate    0.158228\n",
       "Home Office  0.147128"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#9. For every segment find the mean of the discount\n",
    "\n",
    "store.groupby(\"Segment\").agg({'Discount':['mean']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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=\"2\" halign=\"left\">Profit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>most_profitable</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Segment</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Consumer</th>\n",
       "      <td>Raymond Buch</td>\n",
       "      <td>6719.9808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Corporate</th>\n",
       "      <td>Tamara Chand</td>\n",
       "      <td>8399.9760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Home Office</th>\n",
       "      <td>Tom Ashbrook</td>\n",
       "      <td>3919.9888</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Profit           \n",
       "            most_profitable        max\n",
       "Segment                               \n",
       "Consumer       Raymond Buch  6719.9808\n",
       "Corporate      Tamara Chand  8399.9760\n",
       "Home Office    Tom Ashbrook  3919.9888"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 10. For every segment find the most profitable customers\n",
    "def most_profitable(x):\n",
    "    x= x.max()\n",
    "    return store[store.Profit == x]['Customer Name']\n",
    "\n",
    "store.groupby(\"Segment\").agg({'Profit':[most_profitable,'max']})\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sub-Category\n",
      "Copiers      8399.9760\n",
      "Binders      4946.3700\n",
      "Machines     2799.9840\n",
      "Phones       1228.1787\n",
      "Bookcases    1013.1270\n",
      "Name: Profit, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#11. What are the top 5 sub-categories that give maximum profit\n",
    "\n",
    "a =store.groupby(\"Sub-Category\")[['Profit']].max()['Profit'].sort_values(ascending = False).head()\n",
    "print(a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>Sales</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>Discount</th>\n",
       "      <th>Profit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>sum</th>\n",
       "      <th>sum</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>2.297201e+06</td>\n",
       "      <td>37873</td>\n",
       "      <td>1561.09</td>\n",
       "      <td>286397.0217</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Sales Quantity Discount       Profit\n",
       "                        sum      sum      sum          sum\n",
       "Country                                                   \n",
       "United States  2.297201e+06    37873  1561.09  286397.0217"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#12. What is the Total Sales, Quantity, Discount, Profit across Total US.\n",
    "\n",
    "store.groupby(\"Country\")[['Sales','Quantity','Discount','Profit']].agg(['sum'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1822"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#13. How many times has it taken more than 5 days from placing an order to shipping\n",
    "\n",
    "pd.to_datetime(store['Ship Date'])\n",
    "pd.to_datetime(store['Order Date'])\n",
    "store['days'] = pd.to_datetime(store['Ship Date'])-pd.to_datetime(store['Order Date'])\n",
    "\n",
    "\n",
    "sum(store.days > '5 days')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>days</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Category</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Furniture</th>\n",
       "      <td>1288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Office Supplies</th>\n",
       "      <td>3581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Technology</th>\n",
       "      <td>1128</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 days\n",
       "                count\n",
       "Category             \n",
       "Furniture        1288\n",
       "Office Supplies  3581\n",
       "Technology       1128"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 14. Find the total number of orders in every category which has been shipped with a duration of 5 days\n",
    "\n",
    "store[store.days < '5days'].groupby('Category')[['days']].agg(['count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "60.01"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#15. What’s the percentage of items which has been shipped within 5 days\n",
    "\n",
    "round(sum(store.days < '5 days')*100/len(store.days),2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18.23"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 16. What’s the percentage of items which has been shipped after 5 days\n",
    "round(sum(store.days>'5 days')*100/len(store.days),2)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}