Step2_Feature_Engineering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## House Plant Feature Engineering\n",
    "\n",
    "This notebook will cover the feature engineering process (step 2) on the houseplant database generated for two goals: \n",
    "- Perform dimensionality reduction (step 3) as a way to visualise similar plants (ultimate goal to make some 2D scatter like graphs doing this). \n",
    "- Build a content based recommender system with this dataset (Step 4).\n",
    "\n",
    "\n",
    "**Summary of This Notebook:**\n",
    "\n",
    "- 16 features were generated which I can use in Steps 3 and 4. Thankfully was possible to use ordinal encoding a lot to help keep the number of features under control. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3\n",
    "\n",
    "DATABASE_LOC = r\"C:\\Users\\Rory Crean\\Dropbox (lkgroup)\\Backup_HardDrive\\Postdoc\\PyForFun\\House_Plant_Recommender\\Database\\house_plants.db\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Plant_Name</th>\n",
       "      <th>Common_Names</th>\n",
       "      <th>Plant_Type</th>\n",
       "      <th>Family</th>\n",
       "      <th>Zones</th>\n",
       "      <th>Native_Range</th>\n",
       "      <th>Heights</th>\n",
       "      <th>Spreads</th>\n",
       "      <th>Bloom_Times</th>\n",
       "      <th>Bloom_Description</th>\n",
       "      <th>Sunlight</th>\n",
       "      <th>Watering</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Flowers</th>\n",
       "      <th>Leafs</th>\n",
       "      <th>Fruits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aechmea</td>\n",
       "      <td>urn plant,silver vase plant</td>\n",
       "      <td>Epiphyte</td>\n",
       "      <td>Bromeliaceae</td>\n",
       "      <td>10 to 11</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>1.00 to 3.00 feet</td>\n",
       "      <td>1.00 to 2.00 feet</td>\n",
       "      <td>Seasonal bloomer</td>\n",
       "      <td>Violet to red with pink bracts</td>\n",
       "      <td>Part shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Showy</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ardisia crenata</td>\n",
       "      <td>hen's eyes,coralberry,spiceberry,scratchthroat...</td>\n",
       "      <td>Broadleaf evergreen</td>\n",
       "      <td>Primulaceae</td>\n",
       "      <td>8 to 10</td>\n",
       "      <td>Japan to Northern India</td>\n",
       "      <td>4.00 to 5.00 feet</td>\n",
       "      <td>4.00 to 5.00 feet</td>\n",
       "      <td>May to June</td>\n",
       "      <td>Pinkish-white</td>\n",
       "      <td>Part shade to full shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Showy</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>Showy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Euphorbia milii</td>\n",
       "      <td>christplant,Christ plant,Christ thorn,crown of...</td>\n",
       "      <td>Broadleaf evergreen</td>\n",
       "      <td>Euphorbiaceae</td>\n",
       "      <td>9 to 11</td>\n",
       "      <td>Madagascar</td>\n",
       "      <td>3.00 to 6.00 feet</td>\n",
       "      <td>1.50 to 3.00 feet</td>\n",
       "      <td>Seasonal bloomer</td>\n",
       "      <td>Green subtended by red or yellow bracts</td>\n",
       "      <td>Full sun</td>\n",
       "      <td>Dry to medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Showy</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ficus elastica</td>\n",
       "      <td>Indian rubberplant,India rubber plant,rubber p...</td>\n",
       "      <td>Broadleaf evergreen</td>\n",
       "      <td>Moraceae</td>\n",
       "      <td>10 to 12</td>\n",
       "      <td>Southeastern Asia</td>\n",
       "      <td>50.00 to 100.00 feet</td>\n",
       "      <td>50.00 to 100.00 feet</td>\n",
       "      <td>Rarely flowers indoors</td>\n",
       "      <td>None</td>\n",
       "      <td>Part shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Low</td>\n",
       "      <td>Insignificant</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Woodsia obtusa</td>\n",
       "      <td>blunt-lobed woodsia,common woodsia,large woodsia</td>\n",
       "      <td>Fern</td>\n",
       "      <td>Woodsiaceae</td>\n",
       "      <td>4 to 8</td>\n",
       "      <td>North America</td>\n",
       "      <td>1.00 to 1.50 feet</td>\n",
       "      <td>2.00 to 2.50 feet</td>\n",
       "      <td>Non-flowering</td>\n",
       "      <td>Non-flowering</td>\n",
       "      <td>Part shade to full shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Plant_Name                                       Common_Names  \\\n",
       "0          Aechmea                        urn plant,silver vase plant   \n",
       "1  Ardisia crenata  hen's eyes,coralberry,spiceberry,scratchthroat...   \n",
       "2  Euphorbia milii  christplant,Christ plant,Christ thorn,crown of...   \n",
       "3   Ficus elastica  Indian rubberplant,India rubber plant,rubber p...   \n",
       "4   Woodsia obtusa   blunt-lobed woodsia,common woodsia,large woodsia   \n",
       "\n",
       "            Plant_Type         Family     Zones             Native_Range  \\\n",
       "0             Epiphyte   Bromeliaceae  10 to 11                   Brazil   \n",
       "1  Broadleaf evergreen    Primulaceae   8 to 10  Japan to Northern India   \n",
       "2  Broadleaf evergreen  Euphorbiaceae   9 to 11               Madagascar   \n",
       "3  Broadleaf evergreen       Moraceae  10 to 12        Southeastern Asia   \n",
       "4                 Fern    Woodsiaceae    4 to 8            North America   \n",
       "\n",
       "                Heights               Spreads             Bloom_Times  \\\n",
       "0     1.00 to 3.00 feet     1.00 to 2.00 feet        Seasonal bloomer   \n",
       "1     4.00 to 5.00 feet     4.00 to 5.00 feet             May to June   \n",
       "2     3.00 to 6.00 feet     1.50 to 3.00 feet        Seasonal bloomer   \n",
       "3  50.00 to 100.00 feet  50.00 to 100.00 feet  Rarely flowers indoors   \n",
       "4     1.00 to 1.50 feet     2.00 to 2.50 feet           Non-flowering   \n",
       "\n",
       "                         Bloom_Description                  Sunlight  \\\n",
       "0           Violet to red with pink bracts                Part shade   \n",
       "1                            Pinkish-white  Part shade to full shade   \n",
       "2  Green subtended by red or yellow bracts                  Full sun   \n",
       "3                                     None                Part shade   \n",
       "4                            Non-flowering  Part shade to full shade   \n",
       "\n",
       "        Watering Maintenance        Flowers      Leafs Fruits  \n",
       "0         Medium      Medium          Showy  Evergreen   None  \n",
       "1         Medium      Medium          Showy  Evergreen  Showy  \n",
       "2  Dry to medium      Medium          Showy  Evergreen   None  \n",
       "3         Medium         Low  Insignificant  Evergreen   None  \n",
       "4         Medium      Medium           None       None   None  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = sqlite3.connect(DATABASE_LOC)\n",
    "c = conn.cursor()\n",
    "plant_df = pd.read_sql_query(\"SELECT * FROM plant_raw_data\", conn)\n",
    "c.close()\n",
    "plant_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "147"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(plant_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Plant_Name', 'Common_Names', 'Plant_Type', 'Family', 'Zones',\n",
       "       'Native_Range', 'Heights', 'Spreads', 'Bloom_Times',\n",
       "       'Bloom_Description', 'Sunlight', 'Watering', 'Maintenance', 'Flowers',\n",
       "       'Leafs', 'Fruits'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "plant_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### DF Clean up \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Plant_Name</th>\n",
       "      <th>Plant_Type</th>\n",
       "      <th>Zones</th>\n",
       "      <th>Heights</th>\n",
       "      <th>Spreads</th>\n",
       "      <th>Bloom_Times</th>\n",
       "      <th>Sunlight</th>\n",
       "      <th>Watering</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Leafs</th>\n",
       "      <th>Fruits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aechmea</td>\n",
       "      <td>Epiphyte</td>\n",
       "      <td>10 to 11</td>\n",
       "      <td>1.00 to 3.00 feet</td>\n",
       "      <td>1.00 to 2.00 feet</td>\n",
       "      <td>Seasonal bloomer</td>\n",
       "      <td>Part shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ardisia crenata</td>\n",
       "      <td>Broadleaf evergreen</td>\n",
       "      <td>8 to 10</td>\n",
       "      <td>4.00 to 5.00 feet</td>\n",
       "      <td>4.00 to 5.00 feet</td>\n",
       "      <td>May to June</td>\n",
       "      <td>Part shade to full shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>Showy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Euphorbia milii</td>\n",
       "      <td>Broadleaf evergreen</td>\n",
       "      <td>9 to 11</td>\n",
       "      <td>3.00 to 6.00 feet</td>\n",
       "      <td>1.50 to 3.00 feet</td>\n",
       "      <td>Seasonal bloomer</td>\n",
       "      <td>Full sun</td>\n",
       "      <td>Dry to medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ficus elastica</td>\n",
       "      <td>Broadleaf evergreen</td>\n",
       "      <td>10 to 12</td>\n",
       "      <td>50.00 to 100.00 feet</td>\n",
       "      <td>50.00 to 100.00 feet</td>\n",
       "      <td>Rarely flowers indoors</td>\n",
       "      <td>Part shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Low</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Woodsia obtusa</td>\n",
       "      <td>Fern</td>\n",
       "      <td>4 to 8</td>\n",
       "      <td>1.00 to 1.50 feet</td>\n",
       "      <td>2.00 to 2.50 feet</td>\n",
       "      <td>Non-flowering</td>\n",
       "      <td>Part shade to full shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Plant_Name           Plant_Type     Zones               Heights  \\\n",
       "0          Aechmea             Epiphyte  10 to 11     1.00 to 3.00 feet   \n",
       "1  Ardisia crenata  Broadleaf evergreen   8 to 10     4.00 to 5.00 feet   \n",
       "2  Euphorbia milii  Broadleaf evergreen   9 to 11     3.00 to 6.00 feet   \n",
       "3   Ficus elastica  Broadleaf evergreen  10 to 12  50.00 to 100.00 feet   \n",
       "4   Woodsia obtusa                 Fern    4 to 8     1.00 to 1.50 feet   \n",
       "\n",
       "                Spreads             Bloom_Times                  Sunlight  \\\n",
       "0     1.00 to 2.00 feet        Seasonal bloomer                Part shade   \n",
       "1     4.00 to 5.00 feet             May to June  Part shade to full shade   \n",
       "2     1.50 to 3.00 feet        Seasonal bloomer                  Full sun   \n",
       "3  50.00 to 100.00 feet  Rarely flowers indoors                Part shade   \n",
       "4     2.00 to 2.50 feet           Non-flowering  Part shade to full shade   \n",
       "\n",
       "        Watering Maintenance      Leafs Fruits  \n",
       "0         Medium      Medium  Evergreen   None  \n",
       "1         Medium      Medium  Evergreen  Showy  \n",
       "2  Dry to medium      Medium  Evergreen   None  \n",
       "3         Medium         Low  Evergreen   None  \n",
       "4         Medium      Medium       None   None  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove the unwanted columns \n",
    "drop_list = [\"Common_Names\", \"Family\", \"Native_Range\", \"Flowers\", \"Bloom_Description\"]\n",
    "plant_df = plant_df.drop(drop_list, axis=1)\n",
    "plant_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Plant_Name</th>\n",
       "      <th>Plant_Type</th>\n",
       "      <th>Zones</th>\n",
       "      <th>Heights</th>\n",
       "      <th>Spreads</th>\n",
       "      <th>Bloom_Times</th>\n",
       "      <th>Sunlight</th>\n",
       "      <th>Watering</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Leafs</th>\n",
       "      <th>Fruits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Asplenium antiquum</td>\n",
       "      <td>Fern</td>\n",
       "      <td>10 to 11</td>\n",
       "      <td>1.50 to 2.00 feet</td>\n",
       "      <td>3.00 to 4.00 feet</td>\n",
       "      <td>Non-flowering</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Plant_Name Plant_Type     Zones            Heights  \\\n",
       "23  Asplenium antiquum       Fern  10 to 11  1.50 to 2.00 feet   \n",
       "\n",
       "              Spreads    Bloom_Times Sunlight Watering Maintenance Leafs  \\\n",
       "23  3.00 to 4.00 feet  Non-flowering     None     None        None  None   \n",
       "\n",
       "   Fruits  \n",
       "23   None  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Plant_Name</th>\n",
       "      <th>Plant_Type</th>\n",
       "      <th>Zones</th>\n",
       "      <th>Heights</th>\n",
       "      <th>Spreads</th>\n",
       "      <th>Bloom_Times</th>\n",
       "      <th>Sunlight</th>\n",
       "      <th>Watering</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Leafs</th>\n",
       "      <th>Fruits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>Basella alba</td>\n",
       "      <td>Vine</td>\n",
       "      <td>None</td>\n",
       "      <td>2.00 to 6.00 feet</td>\n",
       "      <td>2.00 to 3.00 feet</td>\n",
       "      <td>July to frost</td>\n",
       "      <td>Full sun</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Plant_Name Plant_Type Zones            Heights            Spreads  \\\n",
       "75  Basella alba       Vine  None  2.00 to 6.00 feet  2.00 to 3.00 feet   \n",
       "\n",
       "      Bloom_Times  Sunlight Watering Maintenance Leafs Fruits  \n",
       "75  July to frost  Full sun   Medium      Medium  None   None  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Two plants (\"Asplenium antiquum\", \"Basella alba\") had missing info, so will manually add that now as it is available in paragraph form from the database. \n",
    "plant_row = (plant_df[\"Plant_Name\"].loc[lambda x: x==\"Asplenium antiquum\"].index)\n",
    "display(plant_df.iloc[plant_row])\n",
    "\n",
    "plant_row = (plant_df[\"Plant_Name\"].loc[lambda x: x==\"Basella alba\"].index)\n",
    "display(plant_df.iloc[plant_row])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "plant_df.at[23, \"Sunlight\"] = \"Part shade\"\n",
    "plant_df.at[23, \"Watering\"] = \"Medium\"\n",
    "plant_df.at[23, \"Maintenance\"] = \"Low\"\n",
    "plant_df.at[23, \"Leafs\"] = \"None\" # Can be kept as none as this indicates non-colorful leaves which is correct. \n",
    "plant_df.at[23, \"Fruits\"] = \"None\" # As above, does not produce fruit. \n",
    "\n",
    "plant_df.at[75, \"Zones\"] = \"6 to 10\" "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Plant_Name</th>\n",
       "      <th>Plant_Type</th>\n",
       "      <th>Zones</th>\n",
       "      <th>Heights</th>\n",
       "      <th>Spreads</th>\n",
       "      <th>Bloom_Times</th>\n",
       "      <th>Sunlight</th>\n",
       "      <th>Watering</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Leafs</th>\n",
       "      <th>Fruits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>Basella alba</td>\n",
       "      <td>Vine</td>\n",
       "      <td>6 to 10</td>\n",
       "      <td>2.00 to 6.00 feet</td>\n",
       "      <td>2.00 to 3.00 feet</td>\n",
       "      <td>July to frost</td>\n",
       "      <td>Full sun</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Plant_Name Plant_Type    Zones            Heights            Spreads  \\\n",
       "75  Basella alba       Vine  6 to 10  2.00 to 6.00 feet  2.00 to 3.00 feet   \n",
       "\n",
       "      Bloom_Times  Sunlight Watering Maintenance Leafs Fruits  \n",
       "75  July to frost  Full sun   Medium      Medium  None   None  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# confirm substitution worked. \n",
    "plant_df.iloc[plant_row]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Engineering Time - Column: \"Plant_Type\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Herbaceous perennial    49\n",
       "Broadleaf evergreen     33\n",
       "Bulb                    16\n",
       "Vine                    13\n",
       "Fern                    10\n",
       "Deciduous shrub          7\n",
       "Orchid                   4\n",
       "Palm or Cycad            4\n",
       "Epiphyte                 3\n",
       "Needled evergreen        2\n",
       "Tree                     2\n",
       "Fruit                    2\n",
       "Annual                   1\n",
       "Rush or Sedge            1\n",
       "Name: Plant_Type, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "plant_df[\"Plant_Type\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Will keep the top 5 (up to Fern) and move all others into the group \"Other\". \n",
    "keep_me = [\"Herbaceous perennial\", \"Broadleaf evergreen\", \"Bulb\", \"Vine\", \"Fern\"]\n",
    "\n",
    "plant_types = list(plant_df[\"Plant_Type\"])\n",
    "\n",
    "new_plant_types = []\n",
    "for plant in plant_types:\n",
    "    if plant not in keep_me:\n",
    "        new_plant_types.append(\"Other\")\n",
    "    else: \n",
    "        new_plant_types.append(plant)\n",
    "\n",
    "plant_df[\"Plant_Type\"] = new_plant_types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Herbaceous perennial    49\n",
       "Broadleaf evergreen     33\n",
       "Other                   26\n",
       "Bulb                    16\n",
       "Vine                    13\n",
       "Fern                    10\n",
       "Name: Plant_Type, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Confirm worked\n",
    "plant_df[\"Plant_Type\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Engineering Time - Column: \"Zones\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique zone values to convert to temperatures: {2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}\n"
     ]
    }
   ],
   "source": [
    "zones = list(plant_df[\"Zones\"])\n",
    "\n",
    "min_zones = []\n",
    "for plant in zones:\n",
    "    min_zones.append(int(float(plant.split(\" \")[0])))\n",
    "\n",
    "\n",
    "print(f\"Unique zone values to convert to temperatures: {set(min_zones)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-1.1     58\n",
       "-6.7     26\n",
       " 4.4     23\n",
       "-12.2    12\n",
       "-34.4     9\n",
       "-23.3     7\n",
       "-40.0     4\n",
       "-45.6     3\n",
       "-28.9     3\n",
       " 10.0     1\n",
       "-17.8     1\n",
       "Name: Min_Temp_Degrees_C, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zone_to_temp = {2: -45.6, 3: -40.0, 4: -34.4, 5: -28.9, 6: -23.3, 7: -17.8, 8: -12.2, 9: -6.7, 10: -1.1, 11: 4.4, 12: 10}\n",
    "\n",
    "min_temp = []\n",
    "for min_zone in min_zones:\n",
    "    min_temp.append(zone_to_temp[min_zone])\n",
    "\n",
    "plant_df[\"Min_Temp_Degrees_C\"] = min_temp\n",
    "plant_df[\"Min_Temp_Degrees_C\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Engineering Time - Column: \"Heights\" and \"Spreads\"\n",
    "\n",
    "Extracting the min and max values for both columns "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get min and max height and min and max spread. \n",
    "heights, spreads = list(plant_df[\"Heights\"]), list(plant_df[\"Spreads\"]), \n",
    "\n",
    "min_heights, max_heights = [], []\n",
    "for height in heights:\n",
    "    min_heights.append(float(height.split(\" \")[0]))\n",
    "    max_heights.append(float(height.split(\" \")[2]))\n",
    "\n",
    "min_spreads, max_spreads = [], []\n",
    "for spread in spreads:\n",
    "    min_spreads.append(float(spread.split(\" \")[0]))\n",
    "    max_spreads.append(float(spread.split(\" \")[2]))\n",
    "\n",
    "\n",
    "plant_df[\"Min_Height\"] = min_heights\n",
    "plant_df[\"Max_Height\"] = max_heights\n",
    "plant_df[\"Min_Spread\"] = min_spreads\n",
    "plant_df[\"Max_Spread\"] = max_spreads"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now limit the max_height and spreads to 20 (to deal with outlier for modelling)\n",
    "\n",
    "max_heights_capped = []\n",
    "for height in max_heights:\n",
    "    if height > 20:\n",
    "        max_heights_capped.append(20)\n",
    "    else:\n",
    "        max_heights_capped.append(height) \n",
    "\n",
    "max_spreads_capped = []\n",
    "for spread in max_spreads:\n",
    "    if spread > 20:\n",
    "        max_spreads_capped.append(20)\n",
    "    else:\n",
    "        max_spreads_capped.append(spread) \n",
    "\n",
    "plant_df[\"Max_Height_Capped\"] = max_heights_capped\n",
    "plant_df[\"Max_Spread_Capped\"] = max_spreads_capped"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Engineering Time - Column: \"Bloom_Times\" \n",
    "Converting this to a simpler categorical column as I did in Step 1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Yes       114\n",
       "Rarely     20\n",
       "No         13\n",
       "Name: Flowers_Or_Not, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bloom_times = list(plant_df[\"Bloom_Times\"])\n",
    "\n",
    "non_flowering_terms = [\"Non-flowering\"]\n",
    "rarely_flowering_terms = [\"Rarely flowers indoors\", \"Rarely flowers\"]\n",
    "\n",
    "flower_or_not = []\n",
    "for descript in bloom_times:\n",
    "    if descript in non_flowering_terms:\n",
    "        flower_or_not.append(\"No\")\n",
    "    elif descript in rarely_flowering_terms:\n",
    "        flower_or_not.append(\"Rarely\")\n",
    "    else:\n",
    "        flower_or_not.append(\"Yes\")\n",
    "\n",
    "plant_df[\"Flowers_Or_Not\"] = flower_or_not\n",
    "plant_df[\"Flowers_Or_Not\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Engineering Time - Column: \"Leafs\" and \"Fruits\"\n",
    "\n",
    "Making these into 2 binary columns:  (colorful or not for leaves) and (gives fruit or does not give fruit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Evergreen              75\n",
       "None                   31\n",
       "Colorful, Evergreen    21\n",
       "Colorful               16\n",
       "Fragrant, Evergreen     2\n",
       "Fragrant                1\n",
       "Good Fall               1\n",
       "Name: Leafs, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "None             113\n",
       "Showy             24\n",
       "Showy, Edible     10\n",
       "Name: Fruits, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(plant_df[\"Leafs\"].value_counts())\n",
    "display(plant_df[\"Fruits\"].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Colorful_Leaves  Fruit_Or_Not\n",
       "Not Colorful     No Fruit        79\n",
       "Colorful         No Fruit        34\n",
       "Not Colorful     Yes             31\n",
       "Colorful         Yes              3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "leafs = list(plant_df[\"Leafs\"])\n",
    "colorful_leaves = []\n",
    "for descript in leafs:\n",
    "    if \"colorful\" in descript.lower():\n",
    "        colorful_leaves.append(\"Colorful\")\n",
    "    else: \n",
    "        colorful_leaves.append(\"Not Colorful\")\n",
    "plant_df[\"Colorful_Leaves\"] = colorful_leaves\n",
    "\n",
    "\n",
    "fruits = list(plant_df[\"Fruits\"])\n",
    "fruit_or_not = []\n",
    "for descript in fruits:\n",
    "    if descript == \"None\":\n",
    "        fruit_or_not.append(\"No Fruit\")\n",
    "    else:\n",
    "        fruit_or_not.append(\"Yes\")\n",
    "plant_df[\"Fruit_Or_Not\"] = fruit_or_not\n",
    "\n",
    "# check correct. \n",
    "plant_df[[\"Colorful_Leaves\", \"Fruit_Or_Not\"]].value_counts() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Perform Ordinal and One Hot Encoding "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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>Plant_Name</th>\n",
       "      <th>Plant_Type</th>\n",
       "      <th>Zones</th>\n",
       "      <th>Heights</th>\n",
       "      <th>Spreads</th>\n",
       "      <th>Bloom_Times</th>\n",
       "      <th>Sunlight</th>\n",
       "      <th>Watering</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Leafs</th>\n",
       "      <th>...</th>\n",
       "      <th>Max_Spread</th>\n",
       "      <th>Max_Height_Capped</th>\n",
       "      <th>Max_Spread_Capped</th>\n",
       "      <th>Flowers_Or_Not</th>\n",
       "      <th>Colorful_Leaves</th>\n",
       "      <th>Fruit_Or_Not</th>\n",
       "      <th>Sunlight_Ordinal</th>\n",
       "      <th>Watering_Ordinal</th>\n",
       "      <th>Maintenance_Ordinal</th>\n",
       "      <th>Flowers_Ordinal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aechmea</td>\n",
       "      <td>Other</td>\n",
       "      <td>10 to 11</td>\n",
       "      <td>1.00 to 3.00 feet</td>\n",
       "      <td>1.00 to 2.00 feet</td>\n",
       "      <td>Seasonal bloomer</td>\n",
       "      <td>Part shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Not Colorful</td>\n",
       "      <td>No Fruit</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ardisia crenata</td>\n",
       "      <td>Broadleaf evergreen</td>\n",
       "      <td>8 to 10</td>\n",
       "      <td>4.00 to 5.00 feet</td>\n",
       "      <td>4.00 to 5.00 feet</td>\n",
       "      <td>May to June</td>\n",
       "      <td>Part shade to full shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Not Colorful</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Plant_Name           Plant_Type     Zones            Heights  \\\n",
       "0          Aechmea                Other  10 to 11  1.00 to 3.00 feet   \n",
       "1  Ardisia crenata  Broadleaf evergreen   8 to 10  4.00 to 5.00 feet   \n",
       "\n",
       "             Spreads       Bloom_Times                  Sunlight Watering  \\\n",
       "0  1.00 to 2.00 feet  Seasonal bloomer                Part shade   Medium   \n",
       "1  4.00 to 5.00 feet       May to June  Part shade to full shade   Medium   \n",
       "\n",
       "  Maintenance      Leafs  ... Max_Spread  Max_Height_Capped  \\\n",
       "0      Medium  Evergreen  ...        2.0                3.0   \n",
       "1      Medium  Evergreen  ...        5.0                5.0   \n",
       "\n",
       "   Max_Spread_Capped  Flowers_Or_Not  Colorful_Leaves  Fruit_Or_Not  \\\n",
       "0                2.0             Yes     Not Colorful      No Fruit   \n",
       "1                5.0             Yes     Not Colorful           Yes   \n",
       "\n",
       "   Sunlight_Ordinal  Watering_Ordinal Maintenance_Ordinal Flowers_Ordinal  \n",
       "0                 1                 3                   2               3  \n",
       "1                 2                 3                   2               3  \n",
       "\n",
       "[2 rows x 25 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Ordinal encoding\n",
    "sunlight_dict = {\n",
    "    \"Part shade\" : 1, \"Part shade to full shade\" : 2, \n",
    "    \"Full sun to part shade\" : 3, \"Full sun\" : 4\n",
    "}\n",
    "watering_dict = {\n",
    "    \"Dry\" : 1, \"Dry to medium\" : 2,\n",
    "    \"Medium\" : 3, \"Medium to wet\" : 4, \"Wet\" : 5\n",
    "}\n",
    "maintenance_dict = {\"Low\" : 1, \"Medium\" : 2, \"High\" : 3}\n",
    "flowers_or_not_dict = {\"No\" : 1, \"Rarely\" : 2, \"Yes\" : 3,}\n",
    "\n",
    "plant_df[\"Sunlight_Ordinal\"] = plant_df[\"Sunlight\"].map(sunlight_dict)\n",
    "plant_df[\"Watering_Ordinal\"] = plant_df[\"Watering\"].map(watering_dict)\n",
    "plant_df[\"Maintenance_Ordinal\"] = plant_df[\"Maintenance\"].map(maintenance_dict)\n",
    "plant_df[\"Flowers_Ordinal\"] = plant_df[\"Flowers_Or_Not\"].map(flowers_or_not_dict)\n",
    "\n",
    "plant_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Plant_Name</th>\n",
       "      <th>Plant_Type</th>\n",
       "      <th>Zones</th>\n",
       "      <th>Heights</th>\n",
       "      <th>Spreads</th>\n",
       "      <th>Bloom_Times</th>\n",
       "      <th>Sunlight</th>\n",
       "      <th>Watering</th>\n",
       "      <th>Maintenance</th>\n",
       "      <th>Leafs</th>\n",
       "      <th>...</th>\n",
       "      <th>Watering_Ordinal</th>\n",
       "      <th>Maintenance_Ordinal</th>\n",
       "      <th>Flowers_Ordinal</th>\n",
       "      <th>Type_Bulb</th>\n",
       "      <th>Type_Fern</th>\n",
       "      <th>Type_Herbaceous perennial</th>\n",
       "      <th>Type_Other</th>\n",
       "      <th>Type_Vine</th>\n",
       "      <th>Color_Not Colorful</th>\n",
       "      <th>Fruit_Yes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aechmea</td>\n",
       "      <td>Other</td>\n",
       "      <td>10 to 11</td>\n",
       "      <td>1.00 to 3.00 feet</td>\n",
       "      <td>1.00 to 2.00 feet</td>\n",
       "      <td>Seasonal bloomer</td>\n",
       "      <td>Part shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ardisia crenata</td>\n",
       "      <td>Broadleaf evergreen</td>\n",
       "      <td>8 to 10</td>\n",
       "      <td>4.00 to 5.00 feet</td>\n",
       "      <td>4.00 to 5.00 feet</td>\n",
       "      <td>May to June</td>\n",
       "      <td>Part shade to full shade</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Evergreen</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Plant_Name           Plant_Type     Zones            Heights  \\\n",
       "0          Aechmea                Other  10 to 11  1.00 to 3.00 feet   \n",
       "1  Ardisia crenata  Broadleaf evergreen   8 to 10  4.00 to 5.00 feet   \n",
       "\n",
       "             Spreads       Bloom_Times                  Sunlight Watering  \\\n",
       "0  1.00 to 2.00 feet  Seasonal bloomer                Part shade   Medium   \n",
       "1  4.00 to 5.00 feet       May to June  Part shade to full shade   Medium   \n",
       "\n",
       "  Maintenance      Leafs  ... Watering_Ordinal  Maintenance_Ordinal  \\\n",
       "0      Medium  Evergreen  ...                3                    2   \n",
       "1      Medium  Evergreen  ...                3                    2   \n",
       "\n",
       "   Flowers_Ordinal  Type_Bulb  Type_Fern  Type_Herbaceous perennial  \\\n",
       "0                3          0          0                          0   \n",
       "1                3          0          0                          0   \n",
       "\n",
       "   Type_Other  Type_Vine Color_Not Colorful Fruit_Yes  \n",
       "0           1          0                  1         0  \n",
       "1           0          0                  1         1  \n",
       "\n",
       "[2 rows x 32 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# one hot encoding. \n",
    "df_onehot_encoded = pd.get_dummies(plant_df[[\"Plant_Type\", \"Colorful_Leaves\", \"Fruit_Or_Not\"]], prefix=[\"Type\", \"Color\", \"Fruit\"], drop_first=True)\n",
    "\n",
    "plant_df_complete = plant_df.join(df_onehot_encoded).copy(deep=True)\n",
    "plant_df_complete.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Store the new df in a dataframe\n",
    "- At this point I have generated all the new features I want. Of course, I will need to convert the categorical columns to numerical with ordinal or one-hot encoding, but this will be done in Steps 3 and 4 as required.  \n",
    "- Now I will save this dataframe to the database (removing those columns not needed in Step 3 or Step 4). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Plant_Name', 'Plant_Type', 'Zones', 'Heights', 'Spreads',\n",
       "       'Bloom_Times', 'Sunlight', 'Watering', 'Maintenance', 'Leafs', 'Fruits',\n",
       "       'Min_Temp_Degrees_C', 'Min_Height', 'Max_Height', 'Min_Spread',\n",
       "       'Max_Spread', 'Max_Height_Capped', 'Max_Spread_Capped',\n",
       "       'Flowers_Or_Not', 'Colorful_Leaves', 'Fruit_Or_Not', 'Sunlight_Ordinal',\n",
       "       'Watering_Ordinal', 'Maintenance_Ordinal', 'Flowers_Ordinal',\n",
       "       'Type_Bulb', 'Type_Fern', 'Type_Herbaceous_perennial', 'Type_Other',\n",
       "       'Type_Vine', 'Color_Not_Colorful', 'Fruit_Yes'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# renaming to remove spaces otherwise bad SQL practice.\n",
    "plant_df_complete = plant_df_complete.rename(columns={\n",
    "    \"Type_Herbaceous perennial\": \"Type_Herbaceous_perennial\", \n",
    "    \"Color_Not Colorful\": \"Color_Not_Colorful\"})\n",
    "\n",
    "plant_df_complete.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>Plant_Name</th>\n",
       "      <th>Min_Temp_Degrees_C</th>\n",
       "      <th>Min_Height</th>\n",
       "      <th>Max_Height_Capped</th>\n",
       "      <th>Min_Spread</th>\n",
       "      <th>Max_Spread_Capped</th>\n",
       "      <th>Sunlight_Ordinal</th>\n",
       "      <th>Watering_Ordinal</th>\n",
       "      <th>Maintenance_Ordinal</th>\n",
       "      <th>Flowers_Ordinal</th>\n",
       "      <th>Type_Bulb</th>\n",
       "      <th>Type_Fern</th>\n",
       "      <th>Type_Herbaceous_perennial</th>\n",
       "      <th>Type_Other</th>\n",
       "      <th>Type_Vine</th>\n",
       "      <th>Color_Not_Colorful</th>\n",
       "      <th>Fruit_Yes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aechmea</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ardisia crenata</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Euphorbia milii</td>\n",
       "      <td>-6.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ficus elastica</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>50.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Woodsia obtusa</td>\n",
       "      <td>-34.4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Plant_Name  Min_Temp_Degrees_C  Min_Height  Max_Height_Capped  \\\n",
       "0          Aechmea                -1.1         1.0                3.0   \n",
       "1  Ardisia crenata               -12.2         4.0                5.0   \n",
       "2  Euphorbia milii                -6.7         3.0                6.0   \n",
       "3   Ficus elastica                -1.1        50.0               20.0   \n",
       "4   Woodsia obtusa               -34.4         1.0                1.5   \n",
       "\n",
       "   Min_Spread  Max_Spread_Capped  Sunlight_Ordinal  Watering_Ordinal  \\\n",
       "0         1.0                2.0                 1                 3   \n",
       "1         4.0                5.0                 2                 3   \n",
       "2         1.5                3.0                 4                 2   \n",
       "3        50.0               20.0                 1                 3   \n",
       "4         2.0                2.5                 2                 3   \n",
       "\n",
       "   Maintenance_Ordinal  Flowers_Ordinal  Type_Bulb  Type_Fern  \\\n",
       "0                    2                3          0          0   \n",
       "1                    2                3          0          0   \n",
       "2                    2                3          0          0   \n",
       "3                    1                2          0          0   \n",
       "4                    2                1          0          1   \n",
       "\n",
       "   Type_Herbaceous_perennial  Type_Other  Type_Vine  Color_Not_Colorful  \\\n",
       "0                          0           1          0                   1   \n",
       "1                          0           0          0                   1   \n",
       "2                          0           0          0                   1   \n",
       "3                          0           0          0                   1   \n",
       "4                          0           0          0                   1   \n",
       "\n",
       "   Fruit_Yes  \n",
       "0          0  \n",
       "1          1  \n",
       "2          0  \n",
       "3          0  \n",
       "4          0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_to_keep = [\n",
    "    \"Plant_Name\",\n",
    "    \"Min_Temp_Degrees_C\", \"Min_Height\", \"Max_Height_Capped\", \n",
    "    \"Min_Spread\", \"Max_Spread_Capped\", \n",
    "    \"Sunlight_Ordinal\", \"Watering_Ordinal\", \"Maintenance_Ordinal\",\n",
    "    \"Flowers_Ordinal\", \"Type_Bulb\", \"Type_Fern\",\n",
    "    \"Type_Herbaceous_perennial\", \"Type_Other\", \"Type_Vine\",\n",
    "    \"Color_Not_Colorful\", \"Fruit_Yes\"\n",
    "]\n",
    "\n",
    "features_df = plant_df_complete[columns_to_keep].copy(deep=True) \n",
    "features_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>Min_Temp_Degrees_C</th>\n",
       "      <th>Min_Height</th>\n",
       "      <th>Max_Height_Capped</th>\n",
       "      <th>Min_Spread</th>\n",
       "      <th>Max_Spread_Capped</th>\n",
       "      <th>Sunlight_Ordinal</th>\n",
       "      <th>Watering_Ordinal</th>\n",
       "      <th>Maintenance_Ordinal</th>\n",
       "      <th>Flowers_Ordinal</th>\n",
       "      <th>Type_Bulb</th>\n",
       "      <th>Type_Fern</th>\n",
       "      <th>Type_Herbaceous_perennial</th>\n",
       "      <th>Type_Other</th>\n",
       "      <th>Type_Vine</th>\n",
       "      <th>Color_Not_Colorful</th>\n",
       "      <th>Fruit_Yes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "      <td>147.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>-7.804082</td>\n",
       "      <td>7.496599</td>\n",
       "      <td>7.370748</td>\n",
       "      <td>4.474490</td>\n",
       "      <td>5.496599</td>\n",
       "      <td>2.231293</td>\n",
       "      <td>2.952381</td>\n",
       "      <td>1.537415</td>\n",
       "      <td>2.687075</td>\n",
       "      <td>0.108844</td>\n",
       "      <td>0.068027</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.176871</td>\n",
       "      <td>0.088435</td>\n",
       "      <td>0.748299</td>\n",
       "      <td>0.231293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>12.961180</td>\n",
       "      <td>14.825382</td>\n",
       "      <td>7.185552</td>\n",
       "      <td>9.372602</td>\n",
       "      <td>5.838508</td>\n",
       "      <td>1.147171</td>\n",
       "      <td>0.514626</td>\n",
       "      <td>0.643962</td>\n",
       "      <td>0.628138</td>\n",
       "      <td>0.312508</td>\n",
       "      <td>0.252653</td>\n",
       "      <td>0.473016</td>\n",
       "      <td>0.382864</td>\n",
       "      <td>0.284898</td>\n",
       "      <td>0.435474</td>\n",
       "      <td>0.423101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-45.600000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>-12.200000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.750000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>-1.100000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>-1.100000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>6.500000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>10.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Min_Temp_Degrees_C  Min_Height  Max_Height_Capped  Min_Spread  \\\n",
       "count          147.000000  147.000000         147.000000  147.000000   \n",
       "mean            -7.804082    7.496599           7.370748    4.474490   \n",
       "std             12.961180   14.825382           7.185552    9.372602   \n",
       "min            -45.600000    0.000000           0.250000    0.250000   \n",
       "25%            -12.200000    1.000000           1.500000    1.000000   \n",
       "50%             -1.100000    2.000000           4.000000    1.500000   \n",
       "75%             -1.100000    6.000000          12.000000    3.000000   \n",
       "max             10.000000  100.000000          20.000000   60.000000   \n",
       "\n",
       "       Max_Spread_Capped  Sunlight_Ordinal  Watering_Ordinal  \\\n",
       "count         147.000000        147.000000        147.000000   \n",
       "mean            5.496599          2.231293          2.952381   \n",
       "std             5.838508          1.147171          0.514626   \n",
       "min             0.500000          1.000000          1.000000   \n",
       "25%             1.750000          1.000000          3.000000   \n",
       "50%             3.000000          2.000000          3.000000   \n",
       "75%             6.500000          3.000000          3.000000   \n",
       "max            20.000000          4.000000          5.000000   \n",
       "\n",
       "       Maintenance_Ordinal  Flowers_Ordinal   Type_Bulb   Type_Fern  \\\n",
       "count           147.000000       147.000000  147.000000  147.000000   \n",
       "mean              1.537415         2.687075    0.108844    0.068027   \n",
       "std               0.643962         0.628138    0.312508    0.252653   \n",
       "min               1.000000         1.000000    0.000000    0.000000   \n",
       "25%               1.000000         3.000000    0.000000    0.000000   \n",
       "50%               1.000000         3.000000    0.000000    0.000000   \n",
       "75%               2.000000         3.000000    0.000000    0.000000   \n",
       "max               3.000000         3.000000    1.000000    1.000000   \n",
       "\n",
       "       Type_Herbaceous_perennial  Type_Other   Type_Vine  Color_Not_Colorful  \\\n",
       "count                 147.000000  147.000000  147.000000          147.000000   \n",
       "mean                    0.333333    0.176871    0.088435            0.748299   \n",
       "std                     0.473016    0.382864    0.284898            0.435474   \n",
       "min                     0.000000    0.000000    0.000000            0.000000   \n",
       "25%                     0.000000    0.000000    0.000000            0.500000   \n",
       "50%                     0.000000    0.000000    0.000000            1.000000   \n",
       "75%                     1.000000    0.000000    0.000000            1.000000   \n",
       "max                     1.000000    1.000000    1.000000            1.000000   \n",
       "\n",
       "        Fruit_Yes  \n",
       "count  147.000000  \n",
       "mean     0.231293  \n",
       "std      0.423101  \n",
       "min      0.000000  \n",
       "25%      0.000000  \n",
       "50%      0.000000  \n",
       "75%      0.000000  \n",
       "max      1.000000  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features_df.describe()\n",
    "# looks good. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x159fcec3810>"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = sqlite3.connect(DATABASE_LOC)\n",
    "c = conn.cursor()\n",
    "c.execute(\"\"\"DROP TABLE IF EXISTS plant_features\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Plant_Name', 'Min_Temp_Degrees_C', 'Min_Height', 'Max_Height_Capped',\n",
       "       'Min_Spread', 'Max_Spread_Capped', 'Sunlight_Ordinal',\n",
       "       'Watering_Ordinal', 'Maintenance_Ordinal', 'Flowers_Ordinal',\n",
       "       'Type_Bulb', 'Type_Fern', 'Type_Herbaceous_perennial', 'Type_Other',\n",
       "       'Type_Vine', 'Color_Not_Colorful', 'Fruit_Yes'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(0, 'Plant_Name', 'TEXT', 0, None, 1),\n",
       " (1, 'Min_Temp_Degrees_C', 'REAL', 0, None, 0),\n",
       " (2, 'Min_Height', 'REAL', 0, None, 0),\n",
       " (3, 'Max_Height_Capped', 'REAL', 0, None, 0),\n",
       " (4, 'Min_Spread', 'REAL', 0, None, 0),\n",
       " (5, 'Max_Spread_Capped', 'REAL', 0, None, 0),\n",
       " (6, 'Sunlight_Ordinal', 'INTEGER', 0, None, 0),\n",
       " (7, 'Watering_Ordinal', 'INTEGER', 0, None, 0),\n",
       " (8, 'Maintenance_Ordinal', 'INTEGER', 0, None, 0),\n",
       " (9, 'Flowers_Ordinal', 'INTEGER', 0, None, 0),\n",
       " (10, 'Type_Bulb', 'INTEGER', 0, None, 0),\n",
       " (11, 'Type_Fern', 'INTEGER', 0, None, 0),\n",
       " (12, 'Type_Herbaceous_perennial', 'INTEGER', 0, None, 0),\n",
       " (13, 'Type_Other', 'INTEGER', 0, None, 0),\n",
       " (14, 'Type_Vine', 'INTEGER', 0, None, 0),\n",
       " (15, 'Color_Not_Colorful', 'INTEGER', 0, None, 0),\n",
       " (16, 'Fruit_Yes', 'INTEGER', 0, None, 0)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.execute(\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS plant_features(\n",
    "    Plant_Name TEXT PRIMARY KEY,\n",
    "    Min_Temp_Degrees_C REAL,\n",
    "    Min_Height REAL,\n",
    "    Max_Height_Capped REAL,\n",
    "    Min_Spread REAL,\n",
    "    Max_Spread_Capped REAL,\n",
    "    Sunlight_Ordinal INTEGER,\n",
    "    Watering_Ordinal INTEGER,\n",
    "    Maintenance_Ordinal INTEGER,\n",
    "    Flowers_Ordinal INTEGER,\n",
    "    Type_Bulb INTEGER,\n",
    "    Type_Fern INTEGER,\n",
    "    Type_Herbaceous_perennial INTEGER,\n",
    "    Type_Other INTEGER,\n",
    "    Type_Vine INTEGER,\n",
    "    Color_Not_Colorful INTEGER,\n",
    "    Fruit_Yes INTEGER\n",
    "    )\n",
    "\"\"\")\n",
    "conn.commit()\n",
    "c.execute(\"\"\"PRAGMA table_info(\"plant_features\")\"\"\")\n",
    "c.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Plant_Name</th>\n",
       "      <th>Min_Temp_Degrees_C</th>\n",
       "      <th>Min_Height</th>\n",
       "      <th>Max_Height_Capped</th>\n",
       "      <th>Min_Spread</th>\n",
       "      <th>Max_Spread_Capped</th>\n",
       "      <th>Sunlight_Ordinal</th>\n",
       "      <th>Watering_Ordinal</th>\n",
       "      <th>Maintenance_Ordinal</th>\n",
       "      <th>Flowers_Ordinal</th>\n",
       "      <th>Type_Bulb</th>\n",
       "      <th>Type_Fern</th>\n",
       "      <th>Type_Herbaceous_perennial</th>\n",
       "      <th>Type_Other</th>\n",
       "      <th>Type_Vine</th>\n",
       "      <th>Color_Not_Colorful</th>\n",
       "      <th>Fruit_Yes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aechmea</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ardisia crenata</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Euphorbia milii</td>\n",
       "      <td>-6.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ficus elastica</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>50.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Woodsia obtusa</td>\n",
       "      <td>-34.4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Plant_Name  Min_Temp_Degrees_C  Min_Height  Max_Height_Capped  \\\n",
       "0          Aechmea                -1.1         1.0                3.0   \n",
       "1  Ardisia crenata               -12.2         4.0                5.0   \n",
       "2  Euphorbia milii                -6.7         3.0                6.0   \n",
       "3   Ficus elastica                -1.1        50.0               20.0   \n",
       "4   Woodsia obtusa               -34.4         1.0                1.5   \n",
       "\n",
       "   Min_Spread  Max_Spread_Capped  Sunlight_Ordinal  Watering_Ordinal  \\\n",
       "0         1.0                2.0                 1                 3   \n",
       "1         4.0                5.0                 2                 3   \n",
       "2         1.5                3.0                 4                 2   \n",
       "3        50.0               20.0                 1                 3   \n",
       "4         2.0                2.5                 2                 3   \n",
       "\n",
       "   Maintenance_Ordinal  Flowers_Ordinal  Type_Bulb  Type_Fern  \\\n",
       "0                    2                3          0          0   \n",
       "1                    2                3          0          0   \n",
       "2                    2                3          0          0   \n",
       "3                    1                2          0          0   \n",
       "4                    2                1          0          1   \n",
       "\n",
       "   Type_Herbaceous_perennial  Type_Other  Type_Vine  Color_Not_Colorful  \\\n",
       "0                          0           1          0                   1   \n",
       "1                          0           0          0                   1   \n",
       "2                          0           0          0                   1   \n",
       "3                          0           0          0                   1   \n",
       "4                          0           0          0                   1   \n",
       "\n",
       "   Fruit_Yes  \n",
       "0          0  \n",
       "1          1  \n",
       "2          0  \n",
       "3          0  \n",
       "4          0  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features_df.to_sql(\"plant_features\", con=conn, if_exists=\"append\", index=False)\n",
    "test_saved_df = pd.read_sql_query(\"SELECT * FROM plant_features\", conn)\n",
    "test_saved_df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "c.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.8 ('Py_Fun_Py3_8')",
   "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.8"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "2ced150f8a2667dee2cfae26b4d2611f5ad28f0d3e6e2dea4c3c48326462f442"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}