2021 W1 Preppin' Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "civic-washer",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "ongoing-omaha",
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlrd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "brilliant-warehouse",
   "metadata": {},
   "source": [
    "Reads the Excel file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "documentary-front",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('Path where you want to read the Excel file/PD 2021 Wk 1 Input.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "retired-franchise",
   "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>Order ID</th>\n",
       "      <th>Customer Age</th>\n",
       "      <th>Bike Value</th>\n",
       "      <th>Existing Customer?</th>\n",
       "      <th>Date</th>\n",
       "      <th>Store - Bike</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>481</td>\n",
       "      <td>No</td>\n",
       "      <td>2021-04-25</td>\n",
       "      <td>York - Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>28</td>\n",
       "      <td>1825</td>\n",
       "      <td>No</td>\n",
       "      <td>2021-01-23</td>\n",
       "      <td>York - Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>51</td>\n",
       "      <td>1903</td>\n",
       "      <td>No</td>\n",
       "      <td>2021-07-03</td>\n",
       "      <td>York - Rood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>59</td>\n",
       "      <td>1059</td>\n",
       "      <td>No</td>\n",
       "      <td>2021-01-24</td>\n",
       "      <td>York - Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>44</td>\n",
       "      <td>1764</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2021-08-12</td>\n",
       "      <td>York - Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>996</td>\n",
       "      <td>42</td>\n",
       "      <td>3460</td>\n",
       "      <td>No</td>\n",
       "      <td>2021-09-03</td>\n",
       "      <td>London - Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>997</td>\n",
       "      <td>48</td>\n",
       "      <td>3409</td>\n",
       "      <td>No</td>\n",
       "      <td>2021-01-17</td>\n",
       "      <td>London - Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>998</td>\n",
       "      <td>81</td>\n",
       "      <td>2534</td>\n",
       "      <td>No</td>\n",
       "      <td>2021-03-26</td>\n",
       "      <td>London - Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>999</td>\n",
       "      <td>37</td>\n",
       "      <td>4312</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2021-05-25</td>\n",
       "      <td>London - Gravel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>1000</td>\n",
       "      <td>35</td>\n",
       "      <td>1200</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2021-02-18</td>\n",
       "      <td>Leeds - Graval</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Order ID  Customer Age  Bike Value Existing Customer?       Date  \\\n",
       "0           1            22         481                 No 2021-04-25   \n",
       "1           2            28        1825                 No 2021-01-23   \n",
       "2           3            51        1903                 No 2021-07-03   \n",
       "3           4            59        1059                 No 2021-01-24   \n",
       "4           5            44        1764                Yes 2021-08-12   \n",
       "..        ...           ...         ...                ...        ...   \n",
       "995       996            42        3460                 No 2021-09-03   \n",
       "996       997            48        3409                 No 2021-01-17   \n",
       "997       998            81        2534                 No 2021-03-26   \n",
       "998       999            37        4312                Yes 2021-05-25   \n",
       "999      1000            35        1200                Yes 2021-02-18   \n",
       "\n",
       "          Store - Bike  \n",
       "0          York - Road  \n",
       "1          York - Road  \n",
       "2          York - Rood  \n",
       "3          York - Road  \n",
       "4      York - Mountain  \n",
       "..                 ...  \n",
       "995  London - Mountain  \n",
       "996  London - Mountain  \n",
       "997  London - Mountain  \n",
       "998    London - Gravel  \n",
       "999     Leeds - Graval  \n",
       "\n",
       "[1000 rows x 6 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ranking-christmas",
   "metadata": {},
   "source": [
    "Split the 'Store-Bike' field into 'Store'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "assigned-baltimore",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Store'] = df['Store - Bike'].str.split('-').str[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "afraid-cricket",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        York \n",
       "1        York \n",
       "2        York \n",
       "3        York \n",
       "4        York \n",
       "        ...   \n",
       "995    London \n",
       "996    London \n",
       "997    London \n",
       "998    London \n",
       "999     Leeds \n",
       "Name: Store, Length: 1000, dtype: object"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Store']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "wireless-portuguese",
   "metadata": {},
   "source": [
    "Split the 'Store-Bike' field into 'Bike'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "transsexual-guard",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Bike'] = df['Store - Bike'].str.split('-').str[-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "prime-interview",
   "metadata": {},
   "source": [
    "Find unique values in a Bike column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "going-tooth",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([' Road', ' Rood', ' Mountain', ' Gravel', ' Mountaen', ' Graval',\n",
       "       ' Rowd', ' Gravle'], dtype=object)"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Bike'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "following-division",
   "metadata": {},
   "source": [
    "Clean up the 'Bike' field to leave just three values in the 'Bike' field (Mountain, Gravel, Road)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "hawaiian-premiere",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Bike (new)'] = df['Bike'].replace({' Rood':'Road', ' Rowd':'Road', ' Mountaen':'Mountain', ' Graval':'Gravel', ' Gravle':'Gravel'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "latest-marketing",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0           Road\n",
       "1           Road\n",
       "2           Road\n",
       "3           Road\n",
       "4       Mountain\n",
       "         ...    \n",
       "995     Mountain\n",
       "996     Mountain\n",
       "997     Mountain\n",
       "998       Gravel\n",
       "999       Gravel\n",
       "Name: Bike (new), Length: 1000, dtype: object"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Bike (new)']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "described-halifax",
   "metadata": {},
   "source": [
    "Delete old \"bike\" field"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "reverse-fundamental",
   "metadata": {},
   "outputs": [],
   "source": [
    "del df['Bike']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "several-lingerie",
   "metadata": {},
   "source": [
    "Creates new Day of Month and Quarter columns from Date field"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "composed-macro",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Day of Month'] = pd.DatetimeIndex(df['Date']).month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "economic-naples",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Quarter'] = df['Date'].dt.quarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "global-advertising",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Bike'] = df['Bike (new)']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "higher-bleeding",
   "metadata": {},
   "source": [
    "Drops unneeded columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "processed-conversion",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df = df.drop(columns=['Date', 'Month', 'Store - Bike', 'Bike (new)'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "decreased-musical",
   "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>Order ID</th>\n",
       "      <th>Customer Age</th>\n",
       "      <th>Bike Value</th>\n",
       "      <th>Existing Customer?</th>\n",
       "      <th>Store</th>\n",
       "      <th>Day of Month</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Bike</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>481</td>\n",
       "      <td>No</td>\n",
       "      <td>York</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>28</td>\n",
       "      <td>1825</td>\n",
       "      <td>No</td>\n",
       "      <td>York</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>51</td>\n",
       "      <td>1903</td>\n",
       "      <td>No</td>\n",
       "      <td>York</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>59</td>\n",
       "      <td>1059</td>\n",
       "      <td>No</td>\n",
       "      <td>York</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>44</td>\n",
       "      <td>1764</td>\n",
       "      <td>Yes</td>\n",
       "      <td>York</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>996</td>\n",
       "      <td>42</td>\n",
       "      <td>3460</td>\n",
       "      <td>No</td>\n",
       "      <td>London</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>997</td>\n",
       "      <td>48</td>\n",
       "      <td>3409</td>\n",
       "      <td>No</td>\n",
       "      <td>London</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>998</td>\n",
       "      <td>81</td>\n",
       "      <td>2534</td>\n",
       "      <td>No</td>\n",
       "      <td>London</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>999</td>\n",
       "      <td>37</td>\n",
       "      <td>4312</td>\n",
       "      <td>Yes</td>\n",
       "      <td>London</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>Gravel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>1000</td>\n",
       "      <td>35</td>\n",
       "      <td>1200</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Leeds</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Gravel</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Order ID  Customer Age  Bike Value Existing Customer?    Store  \\\n",
       "0           1            22         481                 No    York    \n",
       "1           2            28        1825                 No    York    \n",
       "2           3            51        1903                 No    York    \n",
       "3           4            59        1059                 No    York    \n",
       "4           5            44        1764                Yes    York    \n",
       "..        ...           ...         ...                ...      ...   \n",
       "995       996            42        3460                 No  London    \n",
       "996       997            48        3409                 No  London    \n",
       "997       998            81        2534                 No  London    \n",
       "998       999            37        4312                Yes  London    \n",
       "999      1000            35        1200                Yes   Leeds    \n",
       "\n",
       "     Day of Month  Quarter       Bike  \n",
       "0               4        2       Road  \n",
       "1               1        1       Road  \n",
       "2               7        3       Road  \n",
       "3               1        1       Road  \n",
       "4               8        3   Mountain  \n",
       "..            ...      ...        ...  \n",
       "995             9        3   Mountain  \n",
       "996             1        1   Mountain  \n",
       "997             3        1   Mountain  \n",
       "998             5        2     Gravel  \n",
       "999             2        1     Gravel  \n",
       "\n",
       "[1000 rows x 8 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "informed-render",
   "metadata": {},
   "source": [
    "Drop first 10 rows in the new dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "female-range",
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 10\n",
    "new_df = new_df.tail(df.shape[0] -N)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "international-scene",
   "metadata": {},
   "source": [
    "Check new dataframe output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "retired-airfare",
   "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>Order ID</th>\n",
       "      <th>Customer Age</th>\n",
       "      <th>Bike Value</th>\n",
       "      <th>Existing Customer?</th>\n",
       "      <th>Store</th>\n",
       "      <th>Day of Month</th>\n",
       "      <th>Quarter</th>\n",
       "      <th>Bike</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>57</td>\n",
       "      <td>902</td>\n",
       "      <td>No</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>946</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Leeds</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>17</td>\n",
       "      <td>1296</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Birmingham</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>59</td>\n",
       "      <td>1166</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Manchester</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>24</td>\n",
       "      <td>1781</td>\n",
       "      <td>No</td>\n",
       "      <td>Manchester</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>996</td>\n",
       "      <td>42</td>\n",
       "      <td>3460</td>\n",
       "      <td>No</td>\n",
       "      <td>London</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>997</td>\n",
       "      <td>48</td>\n",
       "      <td>3409</td>\n",
       "      <td>No</td>\n",
       "      <td>London</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>998</td>\n",
       "      <td>81</td>\n",
       "      <td>2534</td>\n",
       "      <td>No</td>\n",
       "      <td>London</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Mountain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>999</td>\n",
       "      <td>37</td>\n",
       "      <td>4312</td>\n",
       "      <td>Yes</td>\n",
       "      <td>London</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>Gravel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>1000</td>\n",
       "      <td>35</td>\n",
       "      <td>1200</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Leeds</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Gravel</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>990 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Order ID  Customer Age  Bike Value Existing Customer?        Store  \\\n",
       "10         11            57         902                 No  Birmingham    \n",
       "11         12            31         946                Yes       Leeds    \n",
       "12         13            17        1296                Yes  Birmingham    \n",
       "13         14            59        1166                Yes  Manchester    \n",
       "14         15            24        1781                 No  Manchester    \n",
       "..        ...           ...         ...                ...          ...   \n",
       "995       996            42        3460                 No      London    \n",
       "996       997            48        3409                 No      London    \n",
       "997       998            81        2534                 No      London    \n",
       "998       999            37        4312                Yes      London    \n",
       "999      1000            35        1200                Yes       Leeds    \n",
       "\n",
       "     Day of Month  Quarter       Bike  \n",
       "10             10        4       Road  \n",
       "11              1        1       Road  \n",
       "12             10        4       Road  \n",
       "13              7        3       Road  \n",
       "14             10        4   Mountain  \n",
       "..            ...      ...        ...  \n",
       "995             9        3   Mountain  \n",
       "996             1        1   Mountain  \n",
       "997             3        1   Mountain  \n",
       "998             5        2     Gravel  \n",
       "999             2        1     Gravel  \n",
       "\n",
       "[990 rows x 8 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "removable-undergraduate",
   "metadata": {},
   "source": [
    "Exports clean dataframe as csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "nutritional-knowing",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_df.to_csv('Path where you want to store the exported CSV file\\PreppinDataChallenge 2021 W1.csv', index = False)"
   ]
  }
 ],
 "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}