Lecture 03 - Reading data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c25b6f61",
   "metadata": {},
   "source": [
    "# Reading Data\n",
    "\n",
    "* Data from many different resources such as CSV, excel, parquet.\n",
    "* Two main data structures: DataFrame and Series.\n",
    "* DataFrame is a two-dimensional data structure with labeled rows and columns.\n",
    "* Series is a one-dimensional array-like structure."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dd93726",
   "metadata": {},
   "source": [
    "<img src=\"Assets/read_data.png\" class=\"juno_ui_theme_light\" style=\"width:700px\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8ba5199",
   "metadata": {},
   "source": [
    "## Exercise 1 - read_csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "64cbbf40",
   "metadata": {
    "scrolled": true
   },
   "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>store</th>\n",
       "      <th>product_group</th>\n",
       "      <th>product_code</th>\n",
       "      <th>stock_qty</th>\n",
       "      <th>cost</th>\n",
       "      <th>price</th>\n",
       "      <th>last_week_sales</th>\n",
       "      <th>last_month_sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Violet</td>\n",
       "      <td>PG2</td>\n",
       "      <td>4187</td>\n",
       "      <td>498</td>\n",
       "      <td>420.76</td>\n",
       "      <td>569.91</td>\n",
       "      <td>13</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rose</td>\n",
       "      <td>PG2</td>\n",
       "      <td>4195</td>\n",
       "      <td>473</td>\n",
       "      <td>545.64</td>\n",
       "      <td>712.41</td>\n",
       "      <td>16</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Violet</td>\n",
       "      <td>PG2</td>\n",
       "      <td>4204</td>\n",
       "      <td>968</td>\n",
       "      <td>640.42</td>\n",
       "      <td>854.91</td>\n",
       "      <td>22</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Daisy</td>\n",
       "      <td>PG2</td>\n",
       "      <td>4219</td>\n",
       "      <td>241</td>\n",
       "      <td>869.69</td>\n",
       "      <td>1034.55</td>\n",
       "      <td>14</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Daisy</td>\n",
       "      <td>PG2</td>\n",
       "      <td>4718</td>\n",
       "      <td>1401</td>\n",
       "      <td>12.54</td>\n",
       "      <td>26.59</td>\n",
       "      <td>50</td>\n",
       "      <td>285</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    store product_group  product_code  stock_qty    cost    price  \\\n",
       "0  Violet           PG2          4187        498  420.76   569.91   \n",
       "1    Rose           PG2          4195        473  545.64   712.41   \n",
       "2  Violet           PG2          4204        968  640.42   854.91   \n",
       "3   Daisy           PG2          4219        241  869.69  1034.55   \n",
       "4   Daisy           PG2          4718       1401   12.54    26.59   \n",
       "\n",
       "   last_week_sales  last_month_sales  \n",
       "0               13                58  \n",
       "1               16                58  \n",
       "2               22                88  \n",
       "3               14                45  \n",
       "4               50               285  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\"Data/sales_data_with_stores.csv\")\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0e07aab",
   "metadata": {},
   "source": [
    "## Exercise 2 - dtype parameter\n",
    "\n",
    "* Specify the data types of any column in read_csv function using dtype parameter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "652c5c40",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "store                object\n",
       "product_group        object\n",
       "product_code          int64\n",
       "stock_qty             int64\n",
       "cost                float64\n",
       "price               float64\n",
       "last_week_sales       int64\n",
       "last_month_sales      int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"Data/sales_data_with_stores.csv\")\n",
    "\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e8160d33",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "store                       object\n",
       "product_group               object\n",
       "product_code        string[python]\n",
       "stock_qty                    int64\n",
       "cost                       float64\n",
       "price                      float64\n",
       "last_week_sales            float64\n",
       "last_month_sales             int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    \n",
    "    \"Data/sales_data_with_stores.csv\",\n",
    "    dtype={\"product_code\": \"string\", \"last_week_sales\":\"float\"}\n",
    "    \n",
    ")\n",
    "\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b3c0d82",
   "metadata": {},
   "source": [
    "## Exercise 3 - usecols parameter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "20faa76b",
   "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>store</th>\n",
       "      <th>product_code</th>\n",
       "      <th>cost</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Violet</td>\n",
       "      <td>4187</td>\n",
       "      <td>420.76</td>\n",
       "      <td>569.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rose</td>\n",
       "      <td>4195</td>\n",
       "      <td>545.64</td>\n",
       "      <td>712.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Violet</td>\n",
       "      <td>4204</td>\n",
       "      <td>640.42</td>\n",
       "      <td>854.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Daisy</td>\n",
       "      <td>4219</td>\n",
       "      <td>869.69</td>\n",
       "      <td>1034.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Daisy</td>\n",
       "      <td>4718</td>\n",
       "      <td>12.54</td>\n",
       "      <td>26.59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    store  product_code    cost    price\n",
       "0  Violet          4187  420.76   569.91\n",
       "1    Rose          4195  545.64   712.41\n",
       "2  Violet          4204  640.42   854.91\n",
       "3   Daisy          4219  869.69  1034.55\n",
       "4   Daisy          4718   12.54    26.59"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "column_list = [\"store\",\"product_code\",\"cost\",\"price\"]\n",
    "\n",
    "df = pd.read_csv(\n",
    "    \n",
    "    \"Data/sales_data_with_stores.csv\",\n",
    "    usecols=column_list\n",
    ")\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62488a39",
   "metadata": {},
   "source": [
    "## Exercise 4 - usecols parameter\n",
    "\n",
    "* Also accepts column indices.\n",
    "* Helpful when working with files that have a lot of column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f12f5104",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5, 10)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    \n",
    "    \"Data/file_with_many_columns.csv\",\n",
    "    usecols=range(10)\n",
    ")\n",
    "\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "97e3a1db",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   0  1  2  3  4  5  6  7  8  9\n",
       "0  8  0  2  4  4  7  0  9  7  1\n",
       "1  9  7  0  0  8  0  0  4  6  0\n",
       "2  4  7  2  9  4  5  6  5  5  8\n",
       "3  4  0  5  5  1  2  4  2  3  2\n",
       "4  0  9  4  7  0  0  5  9  5  7"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "117d94cd",
   "metadata": {},
   "source": [
    "## Exercise 5 - nrows parameter\n",
    "\n",
    "* Read the first n rows in the file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ad150fe9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000, 8)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    \n",
    "    \"Data/sales_data_with_stores.csv\"\n",
    "\n",
    ")\n",
    "\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b366bcf3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(250, 8)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    \n",
    "    \"Data/sales_data_with_stores.csv\",\n",
    "    nrows=250\n",
    ")\n",
    "\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f985953e",
   "metadata": {},
   "source": [
    "## Exercise 6 - skiprows parameter\n",
    "\n",
    "* Skip the first n rows in the file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d742e9ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(700, 8)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    \n",
    "    \"Data/sales_data_with_stores.csv\",\n",
    "    skiprows=300\n",
    ")\n",
    "\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "798d603e",
   "metadata": {},
   "source": [
    "## Exercise 7 - skiprows parameter\n",
    "\n",
    "* The skiprows parameter also accepts a lambda expression. \n",
    "* For instance, the following code will read every second line from the csv file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "9ce0f229",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(500, 8)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    \n",
    "    \"Data/sales_data_with_stores.csv\",\n",
    "    skiprows=lambda x: x % 2 == 1\n",
    ")\n",
    "\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "baf5e712",
   "metadata": {},
   "source": [
    "## Exercise 8 - index_col parameter\n",
    "\n",
    "* Use a specific column as the index in the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "954e1aa1",
   "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>date</th>\n",
       "      <th>product_id</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-10-01</td>\n",
       "      <td>100121</td>\n",
       "      <td>10.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-10-02</td>\n",
       "      <td>100121</td>\n",
       "      <td>10.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-10-03</td>\n",
       "      <td>100121</td>\n",
       "      <td>15.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-04</td>\n",
       "      <td>100121</td>\n",
       "      <td>11.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-05</td>\n",
       "      <td>100121</td>\n",
       "      <td>16.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  product_id  price\n",
       "0  2022-10-01      100121  10.76\n",
       "1  2022-10-02      100121  10.73\n",
       "2  2022-10-03      100121  15.96\n",
       "3  2022-10-04      100121  11.50\n",
       "4  2022-10-05      100121  16.49"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    \n",
    "    \"Data/prices.csv\"\n",
    ")\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6da14226",
   "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>product_id</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-10-01</th>\n",
       "      <td>100121</td>\n",
       "      <td>10.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-02</th>\n",
       "      <td>100121</td>\n",
       "      <td>10.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-03</th>\n",
       "      <td>100121</td>\n",
       "      <td>15.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-04</th>\n",
       "      <td>100121</td>\n",
       "      <td>11.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-05</th>\n",
       "      <td>100121</td>\n",
       "      <td>16.49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            product_id  price\n",
       "date                         \n",
       "2022-10-01      100121  10.76\n",
       "2022-10-02      100121  10.73\n",
       "2022-10-03      100121  15.96\n",
       "2022-10-04      100121  11.50\n",
       "2022-10-05      100121  16.49"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    \n",
    "    \"Data/prices.csv\",\n",
    "    index_col=\"date\"\n",
    ")\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c8e729e",
   "metadata": {},
   "source": [
    "## Exercise 9 - parse_dates\n",
    "\n",
    "* Can convert the data type after reading the data. \n",
    "* Another option is to handle this task while reading the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b2e1842f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date           object\n",
       "product_id      int64\n",
       "price         float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    \n",
    "    \"Data/prices.csv\"\n",
    ")\n",
    "\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d7a82b80",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date          datetime64[ns]\n",
       "product_id             int64\n",
       "price                float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\n",
    "    \n",
    "    \"Data/prices.csv\",\n",
    "    parse_dates=[\"date\"]\n",
    ")\n",
    "\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0167082",
   "metadata": {},
   "source": [
    "## Exercise 10 - na_values parameter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d6f8ee8c",
   "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>col1</th>\n",
       "      <th>col2</th>\n",
       "      <th>col3</th>\n",
       "      <th>col4</th>\n",
       "      <th>col5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>45</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>46.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2</td>\n",
       "      <td>46</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25</td>\n",
       "      <td>23</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "      <td>34</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>30.0</td>\n",
       "      <td>?</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>35.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>?</td>\n",
       "      <td>26</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>35</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>13.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>25</td>\n",
       "      <td>5</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>40.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>32</td>\n",
       "      <td>47</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   col1  col2 col3  col4  col5\n",
       "0  23.0  16.0   45    17     2\n",
       "1  46.0  16.0   24     2    31\n",
       "2   2.0  29.0    2    46    24\n",
       "3   NaN   NaN   25    23     7\n",
       "4   NaN   NaN   30    34    29\n",
       "5   NaN  30.0    ?     5     6\n",
       "6  35.0  37.0    ?    26    39\n",
       "7   9.0   5.0   35    11    41\n",
       "8  13.0  39.0   25     5    39\n",
       "9  40.0  15.0   32    47    24"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"Data/sample_dataset.csv\")\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6cec5588",
   "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>col1</th>\n",
       "      <th>col2</th>\n",
       "      <th>col3</th>\n",
       "      <th>col4</th>\n",
       "      <th>col5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>46.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>46</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.0</td>\n",
       "      <td>23</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.0</td>\n",
       "      <td>34</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>35.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>13.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>5</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>40.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>47</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   col1  col2  col3  col4  col5\n",
       "0  23.0  16.0  45.0    17     2\n",
       "1  46.0  16.0  24.0     2    31\n",
       "2   2.0  29.0   2.0    46    24\n",
       "3   NaN   NaN  25.0    23     7\n",
       "4   NaN   NaN  30.0    34    29\n",
       "5   NaN  30.0   NaN     5     6\n",
       "6  35.0  37.0   NaN    26    39\n",
       "7   9.0   5.0  35.0    11    41\n",
       "8  13.0  39.0  25.0     5    39\n",
       "9  40.0  15.0  32.0    47    24"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"Data/sample_dataset.csv\", na_values=[\"?\"])\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8742a22",
   "metadata": {},
   "source": [
    "## Exercise 11 - to_csv function\n",
    "\n",
    "* index parameter: Write row names (index) as a column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "700dfdf4",
   "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>col1</th>\n",
       "      <th>col2</th>\n",
       "      <th>col3</th>\n",
       "      <th>col4</th>\n",
       "      <th>col5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>23.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>46.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>46</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.0</td>\n",
       "      <td>23</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.0</td>\n",
       "      <td>34</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   col1  col2  col3  col4  col5\n",
       "0  23.0  16.0  45.0    17     2\n",
       "1  46.0  16.0  24.0     2    31\n",
       "2   2.0  29.0   2.0    46    24\n",
       "3   NaN   NaN  25.0    23     7\n",
       "4   NaN   NaN  30.0    34    29"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_csv(\"Data/sample_dataset_2.csv\", index=False)\n",
    "\n",
    "df = pd.read_csv(\"Data/sample_dataset_2.csv\")\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9be422e1",
   "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>Unnamed: 0</th>\n",
       "      <th>col1</th>\n",
       "      <th>col2</th>\n",
       "      <th>col3</th>\n",
       "      <th>col4</th>\n",
       "      <th>col5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>46.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>46</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.0</td>\n",
       "      <td>23</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.0</td>\n",
       "      <td>34</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  col1  col2  col3  col4  col5\n",
       "0           0  23.0  16.0  45.0    17     2\n",
       "1           1  46.0  16.0  24.0     2    31\n",
       "2           2   2.0  29.0   2.0    46    24\n",
       "3           3   NaN   NaN  25.0    23     7\n",
       "4           4   NaN   NaN  30.0    34    29"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_csv(\"Data/sample_dataset_3.csv\")\n",
    "\n",
    "df = pd.read_csv(\"Data/sample_dataset_3.csv\")\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e6fbff8",
   "metadata": {},
   "source": [
    "## Exercise 12 - read_clipboad"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "167a9efe",
   "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>{col:col.replace(\"_\",</th>\n",
       "      <th>\"\")</th>\n",
       "      <th>for</th>\n",
       "      <th>col</th>\n",
       "      <th>in</th>\n",
       "      <th>churn.columns}</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [{col:col.replace(\"_\",, \"\"), for, col, in, churn.columns}]\n",
       "Index: []"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_clipboard()\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e004da8",
   "metadata": {},
   "source": [
    "## Conclusion"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d58c248",
   "metadata": {},
   "source": [
    "* The use of the function for reading other file types is similar\n",
    "* read_excel\n",
    "* read_parquet\n",
    "* many more"
   ]
  }
 ],
 "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.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}