{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "e2daced3-45d5-40ba-a2d6-88b3d1c01326",
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   col1  col2  col3\n",
       "0   0.0   0.0   0.0\n",
       "1   0.0   0.0   0.0\n",
       "2   0.0   0.0   0.0\n",
       "3   0.0   0.0   0.0\n",
       "4   0.0   0.0   0.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1. Using any method covered in the class, create a dataframe with 5 rows, and 3 columns named col1, col2, col3\n",
    "# filled with zero values. \n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "df = pd.DataFrame(np.zeros((5,3)),columns=[f'col{x}' for x in [1,2,3]])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "db4e18cb-44a8-43fc-bf1a-78c8604c0792",
   "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>AMZN</th>\n",
       "      <th>FB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>1.061881</td>\n",
       "      <td>0.959968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-15</td>\n",
       "      <td>1.053240</td>\n",
       "      <td>0.970243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-22</td>\n",
       "      <td>1.140676</td>\n",
       "      <td>1.016858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-29</td>\n",
       "      <td>1.163374</td>\n",
       "      <td>1.018357</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>2019-12-02</td>\n",
       "      <td>1.425061</td>\n",
       "      <td>1.075997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2019-12-09</td>\n",
       "      <td>1.432660</td>\n",
       "      <td>1.038855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>2019-12-16</td>\n",
       "      <td>1.453455</td>\n",
       "      <td>1.104094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>2019-12-23</td>\n",
       "      <td>1.521226</td>\n",
       "      <td>1.113728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>1.503360</td>\n",
       "      <td>1.098475</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          date      AMZN        FB\n",
       "0   2018-01-01  1.000000  1.000000\n",
       "1   2018-01-08  1.061881  0.959968\n",
       "2   2018-01-15  1.053240  0.970243\n",
       "3   2018-01-22  1.140676  1.016858\n",
       "4   2018-01-29  1.163374  1.018357\n",
       "..         ...       ...       ...\n",
       "100 2019-12-02  1.425061  1.075997\n",
       "101 2019-12-09  1.432660  1.038855\n",
       "102 2019-12-16  1.453455  1.104094\n",
       "103 2019-12-23  1.521226  1.113728\n",
       "104 2019-12-30  1.503360  1.098475\n",
       "\n",
       "[105 rows x 3 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2. read stocks.csv into a dataframe using only date, AMZN, and FB columns\n",
    "df=pd.read_csv('files/stocks.csv',usecols=['date','AMZN','FB'],parse_dates=['date'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "2d70e13c-f5bc-49d7-a994-4892cc55e4d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3. use pandas to convert Sample.xlsx, Sheet1 to a csv file, excluding the index\n",
    "df = pd.read_excel('files/Sample.xlsx',sheet_name='Sheet1')\n",
    "df.to_csv('files/convert_out.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "addf673f-45ce-4b62-aacc-18877aabb5b8",
   "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>AMZN</th>\n",
       "      <th>FB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-03-05</td>\n",
       "      <td>1.284549</td>\n",
       "      <td>0.991330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2018-03-12</td>\n",
       "      <td>1.278683</td>\n",
       "      <td>0.990581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2018-04-23</td>\n",
       "      <td>1.279447</td>\n",
       "      <td>0.929034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>1.286224</td>\n",
       "      <td>0.945197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2018-05-07</td>\n",
       "      <td>1.304091</td>\n",
       "      <td>1.000749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>2019-12-02</td>\n",
       "      <td>1.425061</td>\n",
       "      <td>1.075997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>2019-12-09</td>\n",
       "      <td>1.432660</td>\n",
       "      <td>1.038855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>2019-12-16</td>\n",
       "      <td>1.453455</td>\n",
       "      <td>1.104094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>2019-12-23</td>\n",
       "      <td>1.521226</td>\n",
       "      <td>1.113728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>2019-12-30</td>\n",
       "      <td>1.503360</td>\n",
       "      <td>1.098475</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>88 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          date      AMZN        FB\n",
       "9   2018-03-05  1.284549  0.991330\n",
       "10  2018-03-12  1.278683  0.990581\n",
       "16  2018-04-23  1.279447  0.929034\n",
       "17  2018-04-30  1.286224  0.945197\n",
       "18  2018-05-07  1.304091  1.000749\n",
       "..         ...       ...       ...\n",
       "100 2019-12-02  1.425061  1.075997\n",
       "101 2019-12-09  1.432660  1.038855\n",
       "102 2019-12-16  1.453455  1.104094\n",
       "103 2019-12-23  1.521226  1.113728\n",
       "104 2019-12-30  1.503360  1.098475\n",
       "\n",
       "[88 rows x 3 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 4. Using the dataframe created in question 2, create another dataframe containing only rows where\n",
    "# the price of AMZN is greater than 1.25\n",
    "df2 = df[df.AMZN > 1.25]\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ded80d46-7411-47b5-8c2d-99cfa63abda4",
   "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>AMZN</th>\n",
       "      <th>FB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-03-05</td>\n",
       "      <td>1.284549</td>\n",
       "      <td>0.991330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2018-03-12</td>\n",
       "      <td>1.278683</td>\n",
       "      <td>0.990581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2018-04-23</td>\n",
       "      <td>1.279447</td>\n",
       "      <td>0.929034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>1.286224</td>\n",
       "      <td>0.945197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2018-05-07</td>\n",
       "      <td>1.304091</td>\n",
       "      <td>1.000749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2018-05-14</td>\n",
       "      <td>1.280871</td>\n",
       "      <td>0.977683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2018-05-21</td>\n",
       "      <td>1.309981</td>\n",
       "      <td>0.989671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2018-05-28</td>\n",
       "      <td>1.335519</td>\n",
       "      <td>1.038212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2018-06-04</td>\n",
       "      <td>1.370055</td>\n",
       "      <td>1.012042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2018-06-11</td>\n",
       "      <td>1.396074</td>\n",
       "      <td>1.048167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2018-06-18</td>\n",
       "      <td>1.395830</td>\n",
       "      <td>1.079690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2018-06-25</td>\n",
       "      <td>1.382918</td>\n",
       "      <td>1.039979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2018-07-02</td>\n",
       "      <td>1.391729</td>\n",
       "      <td>1.087664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2018-07-09</td>\n",
       "      <td>1.475039</td>\n",
       "      <td>1.109553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2018-07-16</td>\n",
       "      <td>1.475584</td>\n",
       "      <td>1.123575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2018-07-23</td>\n",
       "      <td>1.478489</td>\n",
       "      <td>0.935991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2018-07-30</td>\n",
       "      <td>1.483387</td>\n",
       "      <td>0.951458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2018-08-06</td>\n",
       "      <td>1.534650</td>\n",
       "      <td>0.964731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2018-08-13</td>\n",
       "      <td>1.531331</td>\n",
       "      <td>0.930158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2018-08-20</td>\n",
       "      <td>1.550181</td>\n",
       "      <td>0.934707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2018-08-27</td>\n",
       "      <td>1.637494</td>\n",
       "      <td>0.940487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2018-09-03</td>\n",
       "      <td>1.588159</td>\n",
       "      <td>0.872572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2018-09-10</td>\n",
       "      <td>1.602901</td>\n",
       "      <td>0.868718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2018-09-17</td>\n",
       "      <td>1.558008</td>\n",
       "      <td>0.871983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2018-09-24</td>\n",
       "      <td>1.629595</td>\n",
       "      <td>0.880171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>1.537376</td>\n",
       "      <td>0.842012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2018-10-08</td>\n",
       "      <td>1.455172</td>\n",
       "      <td>0.822799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2018-10-15</td>\n",
       "      <td>1.435174</td>\n",
       "      <td>0.824458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2018-10-22</td>\n",
       "      <td>1.336552</td>\n",
       "      <td>0.778004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2018-10-29</td>\n",
       "      <td>1.355037</td>\n",
       "      <td>0.804656</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2018-11-05</td>\n",
       "      <td>1.393194</td>\n",
       "      <td>0.775809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2018-11-12</td>\n",
       "      <td>1.296362</td>\n",
       "      <td>0.746749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2018-11-26</td>\n",
       "      <td>1.375083</td>\n",
       "      <td>0.752529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2018-12-03</td>\n",
       "      <td>1.325423</td>\n",
       "      <td>0.735456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2018-12-10</td>\n",
       "      <td>1.295141</td>\n",
       "      <td>0.770993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>2018-12-31</td>\n",
       "      <td>1.281701</td>\n",
       "      <td>0.738293</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date      AMZN        FB\n",
       "9  2018-03-05  1.284549  0.991330\n",
       "10 2018-03-12  1.278683  0.990581\n",
       "16 2018-04-23  1.279447  0.929034\n",
       "17 2018-04-30  1.286224  0.945197\n",
       "18 2018-05-07  1.304091  1.000749\n",
       "19 2018-05-14  1.280871  0.977683\n",
       "20 2018-05-21  1.309981  0.989671\n",
       "21 2018-05-28  1.335519  1.038212\n",
       "22 2018-06-04  1.370055  1.012042\n",
       "23 2018-06-11  1.396074  1.048167\n",
       "24 2018-06-18  1.395830  1.079690\n",
       "25 2018-06-25  1.382918  1.039979\n",
       "26 2018-07-02  1.391729  1.087664\n",
       "27 2018-07-09  1.475039  1.109553\n",
       "28 2018-07-16  1.475584  1.123575\n",
       "29 2018-07-23  1.478489  0.935991\n",
       "30 2018-07-30  1.483387  0.951458\n",
       "31 2018-08-06  1.534650  0.964731\n",
       "32 2018-08-13  1.531331  0.930158\n",
       "33 2018-08-20  1.550181  0.934707\n",
       "34 2018-08-27  1.637494  0.940487\n",
       "35 2018-09-03  1.588159  0.872572\n",
       "36 2018-09-10  1.602901  0.868718\n",
       "37 2018-09-17  1.558008  0.871983\n",
       "38 2018-09-24  1.629595  0.880171\n",
       "39 2018-10-01  1.537376  0.842012\n",
       "40 2018-10-08  1.455172  0.822799\n",
       "41 2018-10-15  1.435174  0.824458\n",
       "42 2018-10-22  1.336552  0.778004\n",
       "43 2018-10-29  1.355037  0.804656\n",
       "44 2018-11-05  1.393194  0.775809\n",
       "45 2018-11-12  1.296362  0.746749\n",
       "47 2018-11-26  1.375083  0.752529\n",
       "48 2018-12-03  1.325423  0.735456\n",
       "49 2018-12-10  1.295141  0.770993\n",
       "52 2018-12-31  1.281701  0.738293"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Using the dataframe created in question 4, display rows where AMZN is greater than FB and date is before 2019\n",
    "# hint: the column needs to be date type and use datetime module to compare against\n",
    "\n",
    "import datetime as dt\n",
    "df2[df2.date < dt.datetime(2019,1,1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f59d8ce6-2949-4199-9a3a-a2b6ab9e74e4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}