1.5_week5_assignment.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "# Pandas Ecommerce Purchases Exercise (by Jose Portila)\n",
    "\n",
    "In this Exercise you will be given some Fake Data about some purchases done through Amazon! Just go ahead and follow the directions and try your best to answer the questions and complete the tasks. Feel free to reference the solutions. Most of the tasks can be solved in different ways. For the most part, the questions get progressively harder.\n",
    "\n",
    "Please excuse anything that doesn't make \"Real-World\" sense in the dataframe, all the data is fake and made-up.\n",
    "\n",
    "Also note that all of these questions can be answered with one line of code.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Import pandas and read in the Ecommerce Purchases csv file and set it to a DataFrame called ecom. **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pip install pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os #for establishing working directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\hussainsarfraz\\\\0_Oxford_Course'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = 'C://Users//hussainsarfraz//0_Oxford_Course'\n",
    "os. chdir(path) #path would be the new working directory\n",
    "os. getcwd() #getting current working directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "ecom = pd.read_csv('Ecommerce Purchases.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 1\n",
    "**Check the head of the DataFrame.**"
   ]
  },
  {
   "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 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>Address</th>\n",
       "      <th>Lot</th>\n",
       "      <th>AM or PM</th>\n",
       "      <th>Browser Info</th>\n",
       "      <th>Company</th>\n",
       "      <th>Credit Card</th>\n",
       "      <th>CC Exp Date</th>\n",
       "      <th>CC Security Code</th>\n",
       "      <th>CC Provider</th>\n",
       "      <th>Email</th>\n",
       "      <th>Job</th>\n",
       "      <th>IP Address</th>\n",
       "      <th>Language</th>\n",
       "      <th>Purchase Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16629 Pace Camp Apt. 448\\nAlexisborough, NE 77...</td>\n",
       "      <td>46 in</td>\n",
       "      <td>PM</td>\n",
       "      <td>Opera/9.56.(X11; Linux x86_64; sl-SI) Presto/2...</td>\n",
       "      <td>Martinez-Herman</td>\n",
       "      <td>6.011930e+15</td>\n",
       "      <td>20-Feb</td>\n",
       "      <td>900</td>\n",
       "      <td>JCB 16 digit</td>\n",
       "      <td>pdunlap@yahoo.com</td>\n",
       "      <td>Scientist, product/process development</td>\n",
       "      <td>149.146.147.205</td>\n",
       "      <td>el</td>\n",
       "      <td>98.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9374 Jasmine Spurs Suite 508\\nSouth John, TN 8...</td>\n",
       "      <td>28 rn</td>\n",
       "      <td>PM</td>\n",
       "      <td>Opera/8.93.(Windows 98; Win 9x 4.90; en-US) Pr...</td>\n",
       "      <td>Fletcher, Richards and Whitaker</td>\n",
       "      <td>3.337760e+15</td>\n",
       "      <td>18-Nov</td>\n",
       "      <td>561</td>\n",
       "      <td>Mastercard</td>\n",
       "      <td>anthony41@reed.com</td>\n",
       "      <td>Drilling engineer</td>\n",
       "      <td>15.160.41.51</td>\n",
       "      <td>fr</td>\n",
       "      <td>70.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Unit 0065 Box 5052\\nDPO AP 27450</td>\n",
       "      <td>94 vE</td>\n",
       "      <td>PM</td>\n",
       "      <td>Mozilla/5.0 (compatible; MSIE 9.0; Windows NT ...</td>\n",
       "      <td>Simpson, Williams and Pham</td>\n",
       "      <td>6.759580e+11</td>\n",
       "      <td>19-Aug</td>\n",
       "      <td>699</td>\n",
       "      <td>JCB 16 digit</td>\n",
       "      <td>amymiller@morales-harrison.com</td>\n",
       "      <td>Customer service manager</td>\n",
       "      <td>132.207.160.22</td>\n",
       "      <td>de</td>\n",
       "      <td>0.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7780 Julia Fords\\nNew Stacy, WA 45798</td>\n",
       "      <td>36 vm</td>\n",
       "      <td>PM</td>\n",
       "      <td>Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_0 ...</td>\n",
       "      <td>Williams, Marshall and Buchanan</td>\n",
       "      <td>6.011580e+15</td>\n",
       "      <td>24-Feb</td>\n",
       "      <td>384</td>\n",
       "      <td>Discover</td>\n",
       "      <td>brent16@olson-robinson.info</td>\n",
       "      <td>Drilling engineer</td>\n",
       "      <td>30.250.74.19</td>\n",
       "      <td>es</td>\n",
       "      <td>78.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23012 Munoz Drive Suite 337\\nNew Cynthia, TX 5...</td>\n",
       "      <td>20 IE</td>\n",
       "      <td>AM</td>\n",
       "      <td>Opera/9.58.(X11; Linux x86_64; it-IT) Presto/2...</td>\n",
       "      <td>Brown, Watson and Andrews</td>\n",
       "      <td>6.011460e+15</td>\n",
       "      <td>25-Oct</td>\n",
       "      <td>678</td>\n",
       "      <td>Diners Club / Carte Blanche</td>\n",
       "      <td>christopherwright@gmail.com</td>\n",
       "      <td>Fine artist</td>\n",
       "      <td>24.140.33.94</td>\n",
       "      <td>es</td>\n",
       "      <td>77.82</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Address    Lot AM or PM  \\\n",
       "0  16629 Pace Camp Apt. 448\\nAlexisborough, NE 77...  46 in       PM   \n",
       "1  9374 Jasmine Spurs Suite 508\\nSouth John, TN 8...  28 rn       PM   \n",
       "2                   Unit 0065 Box 5052\\nDPO AP 27450  94 vE       PM   \n",
       "3              7780 Julia Fords\\nNew Stacy, WA 45798  36 vm       PM   \n",
       "4  23012 Munoz Drive Suite 337\\nNew Cynthia, TX 5...  20 IE       AM   \n",
       "\n",
       "                                        Browser Info  \\\n",
       "0  Opera/9.56.(X11; Linux x86_64; sl-SI) Presto/2...   \n",
       "1  Opera/8.93.(Windows 98; Win 9x 4.90; en-US) Pr...   \n",
       "2  Mozilla/5.0 (compatible; MSIE 9.0; Windows NT ...   \n",
       "3  Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_0 ...   \n",
       "4  Opera/9.58.(X11; Linux x86_64; it-IT) Presto/2...   \n",
       "\n",
       "                           Company   Credit Card CC Exp Date  \\\n",
       "0                  Martinez-Herman  6.011930e+15      20-Feb   \n",
       "1  Fletcher, Richards and Whitaker  3.337760e+15      18-Nov   \n",
       "2       Simpson, Williams and Pham  6.759580e+11      19-Aug   \n",
       "3  Williams, Marshall and Buchanan  6.011580e+15      24-Feb   \n",
       "4        Brown, Watson and Andrews  6.011460e+15      25-Oct   \n",
       "\n",
       "   CC Security Code                  CC Provider  \\\n",
       "0               900                 JCB 16 digit   \n",
       "1               561                   Mastercard   \n",
       "2               699                 JCB 16 digit   \n",
       "3               384                     Discover   \n",
       "4               678  Diners Club / Carte Blanche   \n",
       "\n",
       "                            Email                                     Job  \\\n",
       "0               pdunlap@yahoo.com  Scientist, product/process development   \n",
       "1              anthony41@reed.com                       Drilling engineer   \n",
       "2  amymiller@morales-harrison.com                Customer service manager   \n",
       "3     brent16@olson-robinson.info                       Drilling engineer   \n",
       "4     christopherwright@gmail.com                             Fine artist   \n",
       "\n",
       "        IP Address Language  Purchase Price  \n",
       "0  149.146.147.205       el           98.14  \n",
       "1     15.160.41.51       fr           70.73  \n",
       "2   132.207.160.22       de            0.95  \n",
       "3     30.250.74.19       es           78.04  \n",
       "4     24.140.33.94       es           77.82  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ecom.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 2\n",
    "** How many rows and columns are there? **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10000 entries, 0 to 9999\n",
      "Data columns (total 14 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   Address           10000 non-null  object \n",
      " 1   Lot               10000 non-null  object \n",
      " 2   AM or PM          10000 non-null  object \n",
      " 3   Browser Info      10000 non-null  object \n",
      " 4   Company           10000 non-null  object \n",
      " 5   Credit Card       10000 non-null  float64\n",
      " 6   CC Exp Date       10000 non-null  object \n",
      " 7   CC Security Code  10000 non-null  int64  \n",
      " 8   CC Provider       10000 non-null  object \n",
      " 9   Email             10000 non-null  object \n",
      " 10  Job               10000 non-null  object \n",
      " 11  IP Address        10000 non-null  object \n",
      " 12  Language          10000 non-null  object \n",
      " 13  Purchase Price    10000 non-null  float64\n",
      "dtypes: float64(2), int64(1), object(11)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "ecom.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 3\n",
    "** What is the average Purchase Price? **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "50.347302\n"
     ]
    }
   ],
   "source": [
    "price_mean = ecom.loc[:,'Purchase Price'].mean() #1st method\n",
    "# price_mean = ecom['Purchase Price'].mean() #2nd method\n",
    "\n",
    "print(price_mean)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 4\n",
    "** What were the highest and lowest purchase prices? **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "99.99\n"
     ]
    }
   ],
   "source": [
    "high_purchase_price = ecom['Purchase Price'].max()\n",
    "print(high_purchase_price)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.0\n"
     ]
    }
   ],
   "source": [
    "min_purchase_price = ecom['Purchase Price'].min()\n",
    "print(min_purchase_price)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>Credit Card</th>\n",
       "      <th>CC Security Code</th>\n",
       "      <th>Purchase Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.000000e+04</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2.341374e+15</td>\n",
       "      <td>907.217800</td>\n",
       "      <td>50.347302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.256103e+15</td>\n",
       "      <td>1589.693035</td>\n",
       "      <td>29.015836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>6.040186e+10</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.056322e+13</td>\n",
       "      <td>280.000000</td>\n",
       "      <td>25.150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>8.699940e+14</td>\n",
       "      <td>548.000000</td>\n",
       "      <td>50.505000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.492295e+15</td>\n",
       "      <td>816.000000</td>\n",
       "      <td>75.770000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>6.012000e+15</td>\n",
       "      <td>9993.000000</td>\n",
       "      <td>99.990000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Credit Card  CC Security Code  Purchase Price\n",
       "count  1.000000e+04      10000.000000    10000.000000\n",
       "mean   2.341374e+15        907.217800       50.347302\n",
       "std    2.256103e+15       1589.693035       29.015836\n",
       "min    6.040186e+10          0.000000        0.000000\n",
       "25%    3.056322e+13        280.000000       25.150000\n",
       "50%    8.699940e+14        548.000000       50.505000\n",
       "75%    4.492295e+15        816.000000       75.770000\n",
       "max    6.012000e+15       9993.000000       99.990000"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ecom.describe() #only including columns that are floats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 5\n",
    "** How many people have English 'en' as their Language of choice on the website? **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1098\n"
     ]
    }
   ],
   "source": [
    "value_count = ecom['Language'].value_counts()['en']\n",
    "print(value_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 1098 entries, 11 to 9979\n",
      "Data columns (total 14 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   Address           1098 non-null   object \n",
      " 1   Lot               1098 non-null   object \n",
      " 2   AM or PM          1098 non-null   object \n",
      " 3   Browser Info      1098 non-null   object \n",
      " 4   Company           1098 non-null   object \n",
      " 5   Credit Card       1098 non-null   float64\n",
      " 6   CC Exp Date       1098 non-null   object \n",
      " 7   CC Security Code  1098 non-null   int64  \n",
      " 8   CC Provider       1098 non-null   object \n",
      " 9   Email             1098 non-null   object \n",
      " 10  Job               1098 non-null   object \n",
      " 11  IP Address        1098 non-null   object \n",
      " 12  Language          1098 non-null   object \n",
      " 13  Purchase Price    1098 non-null   float64\n",
      "dtypes: float64(2), int64(1), object(11)\n",
      "memory usage: 128.7+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "dataset_of_TF_values = ecom.Language.str.contains('en') #this command creates a column of boolean True/False values\n",
    "dataset_rows_that_contain_en = ecom[dataset_of_TF_values]\n",
    "\n",
    "print(dataset_rows_that_contain_en.info()) #method 1\n",
    "#print(dataset_rows_that_contain_en.count()) #method 2\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 6\n",
    "** How many people have the job title of \"Lawyer\" ? **\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Address             30\n",
       "Lot                 30\n",
       "AM or PM            30\n",
       "Browser Info        30\n",
       "Company             30\n",
       "Credit Card         30\n",
       "CC Exp Date         30\n",
       "CC Security Code    30\n",
       "CC Provider         30\n",
       "Email               30\n",
       "Job                 30\n",
       "IP Address          30\n",
       "Language            30\n",
       "Purchase Price      30\n",
       "dtype: int64"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TF_dataset_of_lawyer_jobs = ecom.Job.str.contains('Lawyer')\n",
    "\n",
    "# ecom[TF_dataset_of_lawyer_jobs].info() #method 1\n",
    "ecom[TF_dataset_of_lawyer_jobs].count() #method 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "30\n"
     ]
    }
   ],
   "source": [
    "print(ecom.Job.str.contains('Lawyer').sum()) #method 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 7\n",
    "** How many people made the purchase during the AM and how many people made the purchase during PM ? **\n",
    "\n",
    "**(Hint: Check out [value_counts()](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.value_counts.html) ) **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AM or PM\n",
      "PM    5068\n",
      "AM    4932\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(ecom['AM or PM'].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 8\n",
    "** What are the 5 most common Job Titles? **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Job\n",
       "Interior and spatial designer        31\n",
       "Lawyer                               30\n",
       "Social researcher                    28\n",
       "Research officer, political party    27\n",
       "Designer, jewellery                  27\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ecom['Job'].value_counts().head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 9\n",
    "** Someone made a purchase that came from Lot: \"90 WT\" , what was the Purchase Price for this transaction? **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "513    75.1\n",
       "Name: Purchase Price, dtype: float64"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TF_dataset = ecom['Lot'] == '90 WT'\n",
    "ecom[TF_dataset]['Purchase Price'] #method 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "513    75.1\n",
       "Name: Purchase Price, dtype: float64"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ecom[ecom.Lot.str.contains(\"90 WT\")]['Purchase Price'] #method 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 10\n",
    "** What is the email of the person with the following Credit Card Number: 4926535242672853 **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1234    bondellen@williams-garza.com\n",
       "Name: Email, dtype: object"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scientific_notation_to_int = ecom['Credit Card'].astype('int64')\n",
    "#(scientific_notation_to_int > 4926535242672840) & (scientific_notation_to_int < 4926535242672800)\n",
    "ecom[(scientific_notation_to_int > 4926535242672853) & (scientific_notation_to_int < 4926555242672853)]['Email'] #method 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1234    4926540000000000\n",
       "Name: converted CC num, dtype: int64"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ecom['converted CC num'] = ecom['Credit Card'].astype('int64')\n",
    "ecom[(scientific_notation_to_int > 4926535242672853) & (scientific_notation_to_int < 4926555242672853)]['converted CC num']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 11\n",
    "** How many people have American Express as their Credit Card Provider *and* made a purchase above $95 ?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    9961\n",
       "True       39\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TF_dataset = (ecom['CC Provider'] == 'American Express') & (ecom['Purchase Price'] > 95)\n",
    "TF_dataset.value_counts() #method 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Address             39\n",
       "Lot                 39\n",
       "AM or PM            39\n",
       "Browser Info        39\n",
       "Company             39\n",
       "Credit Card         39\n",
       "CC Exp Date         39\n",
       "CC Security Code    39\n",
       "CC Provider         39\n",
       "Email               39\n",
       "Job                 39\n",
       "IP Address          39\n",
       "Language            39\n",
       "Purchase Price      39\n",
       "converted CC num    39\n",
       "dtype: int64"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ecom[TF_dataset].count() #method 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 12\n",
    "** Hard: How many people have a credit card that expires in 2025? **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [],
   "source": [
    "ecom['Credit Card Expiry'] = ecom['CC Exp Date'].astype(str).str[:2].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Credit Card Expiry\n",
       "False    8967\n",
       "True     1033\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ecom['Credit Card Expiry'] == 25).value_counts() #method 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.int64(10000)"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TF_dataset = ecom['Credit Card Expiry'] == 25\n",
    "(TF_dataset == True).count() #method 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 13\n",
    "** Hard: What are the top 5 most popular email providers/hosts (e.g. gmail.com, yahoo.com, etc...) **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "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>Address</th>\n",
       "      <th>Lot</th>\n",
       "      <th>AM or PM</th>\n",
       "      <th>Browser Info</th>\n",
       "      <th>Company</th>\n",
       "      <th>Credit Card</th>\n",
       "      <th>CC Exp Date</th>\n",
       "      <th>CC Security Code</th>\n",
       "      <th>CC Provider</th>\n",
       "      <th>Email</th>\n",
       "      <th>Job</th>\n",
       "      <th>IP Address</th>\n",
       "      <th>Language</th>\n",
       "      <th>Purchase Price</th>\n",
       "      <th>converted CC num</th>\n",
       "      <th>Credit Card Expiry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16629 Pace Camp Apt. 448\\nAlexisborough, NE 77...</td>\n",
       "      <td>46 in</td>\n",
       "      <td>PM</td>\n",
       "      <td>Opera/9.56.(X11; Linux x86_64; sl-SI) Presto/2...</td>\n",
       "      <td>Martinez-Herman</td>\n",
       "      <td>6.011930e+15</td>\n",
       "      <td>20-Feb</td>\n",
       "      <td>900</td>\n",
       "      <td>JCB 16 digit</td>\n",
       "      <td>pdunlap@yahoo.com</td>\n",
       "      <td>Scientist, product/process development</td>\n",
       "      <td>149.146.147.205</td>\n",
       "      <td>el</td>\n",
       "      <td>98.14</td>\n",
       "      <td>6011930000000000</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9374 Jasmine Spurs Suite 508\\nSouth John, TN 8...</td>\n",
       "      <td>28 rn</td>\n",
       "      <td>PM</td>\n",
       "      <td>Opera/8.93.(Windows 98; Win 9x 4.90; en-US) Pr...</td>\n",
       "      <td>Fletcher, Richards and Whitaker</td>\n",
       "      <td>3.337760e+15</td>\n",
       "      <td>18-Nov</td>\n",
       "      <td>561</td>\n",
       "      <td>Mastercard</td>\n",
       "      <td>anthony41@reed.com</td>\n",
       "      <td>Drilling engineer</td>\n",
       "      <td>15.160.41.51</td>\n",
       "      <td>fr</td>\n",
       "      <td>70.73</td>\n",
       "      <td>3337760000000000</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Unit 0065 Box 5052\\nDPO AP 27450</td>\n",
       "      <td>94 vE</td>\n",
       "      <td>PM</td>\n",
       "      <td>Mozilla/5.0 (compatible; MSIE 9.0; Windows NT ...</td>\n",
       "      <td>Simpson, Williams and Pham</td>\n",
       "      <td>6.759580e+11</td>\n",
       "      <td>19-Aug</td>\n",
       "      <td>699</td>\n",
       "      <td>JCB 16 digit</td>\n",
       "      <td>amymiller@morales-harrison.com</td>\n",
       "      <td>Customer service manager</td>\n",
       "      <td>132.207.160.22</td>\n",
       "      <td>de</td>\n",
       "      <td>0.95</td>\n",
       "      <td>675958000000</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7780 Julia Fords\\nNew Stacy, WA 45798</td>\n",
       "      <td>36 vm</td>\n",
       "      <td>PM</td>\n",
       "      <td>Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_0 ...</td>\n",
       "      <td>Williams, Marshall and Buchanan</td>\n",
       "      <td>6.011580e+15</td>\n",
       "      <td>24-Feb</td>\n",
       "      <td>384</td>\n",
       "      <td>Discover</td>\n",
       "      <td>brent16@olson-robinson.info</td>\n",
       "      <td>Drilling engineer</td>\n",
       "      <td>30.250.74.19</td>\n",
       "      <td>es</td>\n",
       "      <td>78.04</td>\n",
       "      <td>6011580000000000</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23012 Munoz Drive Suite 337\\nNew Cynthia, TX 5...</td>\n",
       "      <td>20 IE</td>\n",
       "      <td>AM</td>\n",
       "      <td>Opera/9.58.(X11; Linux x86_64; it-IT) Presto/2...</td>\n",
       "      <td>Brown, Watson and Andrews</td>\n",
       "      <td>6.011460e+15</td>\n",
       "      <td>25-Oct</td>\n",
       "      <td>678</td>\n",
       "      <td>Diners Club / Carte Blanche</td>\n",
       "      <td>christopherwright@gmail.com</td>\n",
       "      <td>Fine artist</td>\n",
       "      <td>24.140.33.94</td>\n",
       "      <td>es</td>\n",
       "      <td>77.82</td>\n",
       "      <td>6011460000000000</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Address    Lot AM or PM  \\\n",
       "0  16629 Pace Camp Apt. 448\\nAlexisborough, NE 77...  46 in       PM   \n",
       "1  9374 Jasmine Spurs Suite 508\\nSouth John, TN 8...  28 rn       PM   \n",
       "2                   Unit 0065 Box 5052\\nDPO AP 27450  94 vE       PM   \n",
       "3              7780 Julia Fords\\nNew Stacy, WA 45798  36 vm       PM   \n",
       "4  23012 Munoz Drive Suite 337\\nNew Cynthia, TX 5...  20 IE       AM   \n",
       "\n",
       "                                        Browser Info  \\\n",
       "0  Opera/9.56.(X11; Linux x86_64; sl-SI) Presto/2...   \n",
       "1  Opera/8.93.(Windows 98; Win 9x 4.90; en-US) Pr...   \n",
       "2  Mozilla/5.0 (compatible; MSIE 9.0; Windows NT ...   \n",
       "3  Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_0 ...   \n",
       "4  Opera/9.58.(X11; Linux x86_64; it-IT) Presto/2...   \n",
       "\n",
       "                           Company   Credit Card CC Exp Date  \\\n",
       "0                  Martinez-Herman  6.011930e+15      20-Feb   \n",
       "1  Fletcher, Richards and Whitaker  3.337760e+15      18-Nov   \n",
       "2       Simpson, Williams and Pham  6.759580e+11      19-Aug   \n",
       "3  Williams, Marshall and Buchanan  6.011580e+15      24-Feb   \n",
       "4        Brown, Watson and Andrews  6.011460e+15      25-Oct   \n",
       "\n",
       "   CC Security Code                  CC Provider  \\\n",
       "0               900                 JCB 16 digit   \n",
       "1               561                   Mastercard   \n",
       "2               699                 JCB 16 digit   \n",
       "3               384                     Discover   \n",
       "4               678  Diners Club / Carte Blanche   \n",
       "\n",
       "                            Email                                     Job  \\\n",
       "0               pdunlap@yahoo.com  Scientist, product/process development   \n",
       "1              anthony41@reed.com                       Drilling engineer   \n",
       "2  amymiller@morales-harrison.com                Customer service manager   \n",
       "3     brent16@olson-robinson.info                       Drilling engineer   \n",
       "4     christopherwright@gmail.com                             Fine artist   \n",
       "\n",
       "        IP Address Language  Purchase Price  converted CC num  \\\n",
       "0  149.146.147.205       el           98.14  6011930000000000   \n",
       "1     15.160.41.51       fr           70.73  3337760000000000   \n",
       "2   132.207.160.22       de            0.95      675958000000   \n",
       "3     30.250.74.19       es           78.04  6011580000000000   \n",
       "4     24.140.33.94       es           77.82  6011460000000000   \n",
       "\n",
       "   Credit Card Expiry  \n",
       "0                  20  \n",
       "1                  18  \n",
       "2                  19  \n",
       "3                  24  \n",
       "4                  25  "
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ecom.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "ecom[['Email ID','Email Provider']] = ecom['Email'].str.split('@', expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Email Provider\n",
       "hotmail.com     1638\n",
       "yahoo.com       1616\n",
       "gmail.com       1605\n",
       "smith.com         42\n",
       "williams.com      37\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ecom['Email Provider'].value_counts().head(5) #method 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                 [pdunlap, yahoo.com]\n",
       "1                [anthony41, reed.com]\n",
       "2    [amymiller, morales-harrison.com]\n",
       "3       [brent16, olson-robinson.info]\n",
       "4       [christopherwright, gmail.com]\n",
       "Name: Email, dtype: object"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ecom['Email'].apply(lambda x: x.split('@')).head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Email\n",
       "hotmail.com     1638\n",
       "yahoo.com       1616\n",
       "gmail.com       1605\n",
       "smith.com         42\n",
       "williams.com      37\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "email_provider_dataset = ecom['Email'].apply(lambda x: x.split('@')[1]) #the lamda x is the value of each row\n",
    "email_provider_dataset.value_counts().head(5) #method 2"
   ]
  }
 ],
 "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": 1
}