ConceptMapping/ConceptMapping-MagnoliaMapping_correct.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# WholeTale Summer Internship 2018-- Taxonomy alignment as a key to enhance reproducibility in biodiversity research: a case study of Magnolia \n",
    "\n",
    "# Step 5: Concept Mapping\n",
    "\n",
    "## Goal of this project\n",
    "Oftentimes in biodiversity research, we expect the scientific names of species to be unique identifiers, but actually they may not be. Why is that? \n",
    "\n",
    "(1) The scientific names can vary over time\n",
    "\n",
    "(2) The names stay the same, but the semantics of the names change\n",
    "\n",
    "Other complicated issues: \n",
    "\n",
    "(1) Different people may have different perceptions to the taxonomy of a same topic \n",
    "\n",
    "(2) Species distribution datasets oftentimes only include information on a species ‘name’ without crediting the authorship of that taxonomy\n",
    "\n",
    "This is why we are in a pressing need to align diffferent taxonomies that is addressing the same topic, not to only make the names more interoperable, but also to make way for further datasets usage. \n",
    "\n",
    "## Overview of the tasks for this project\n",
    "\n",
    "**Step 1**: Decide **which species** (or genus) to examine \n",
    "\n",
    "**Step 2**: Domain experts provide a **mapping** table for the taxonomies used over time for that particular species \n",
    "\n",
    "**Step 3**: Researcher transpose domain experts’ table into **Euler/X or LeanEuler** input file  \n",
    "\n",
    "**Step 4**: Gather **species distribution dataset** from biodiverisity portals\n",
    "\n",
    "**Step 5**: **Concept mapping** of the taxonomies and create new datasets based on different taxonomies\n",
    "\n",
    "**Step 6**: **Data cleaning** - geocode missing lat-long information \n",
    "\n",
    "**Step 7**: **Visualizing** species co-occurrence distribution & synthesized taxonomy alignment distribution\n",
    "\n",
    "**Step 8**: **Niche modeling** and further analyisis\n",
    "\n",
    "## The tasks achieved in this notebook\n",
    "In this notebook, we mainly focused on **Step 5**: the **Concept Mapping** process.\n",
    "\n",
    "We have separate notebooks for the details of step 7 and step 8. \n",
    "\n",
    "## Explanation of the Concept Mapping process\n",
    "The main objective for the concept mapping process is to take the dataset that uses Taxonomy 1 and create new datasets that use Taxonomy 2, 3, 4 or more. \n",
    "\n",
    "To be more specific, in the case of Magnolia, the scientific names column in our species co-occurrence dataset is based on Weakley 2014 taxonomy. We want map the scientific names column to be based on other authority's taxonomies, for example the Magnolia taxonomy based on Chapman 1883, so that we can produce **new** co-occurrence dataset based on the Chapman 1883 taxonomy. This will further enable us to do analysis or visualization on 'what it could have been' if we model the datasets using different taxonomies. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#import all the necessary libraries we will be using \n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reads into LeanEuler output relations table\n",
    "In Step 3 we did the taoxnomy alignment on Taxonomy 1 Weakley 2014 and Taxonomy 2 Chapman 1883 using a RCC-5, logic-based tool named LeanEuler. This process gave us an output table (the **MIR** table) that show us every possible pairwise relation the concepts in taxonomy 1 can have with taoxnomy 2. We will utilze this table for our concept mapping task:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#create dataframe from LeanEuler output file \n",
    "df=pd.read_csv(\"inputFiles/rel_3.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>pw</th>\n",
       "      <th>x1</th>\n",
       "      <th>x2</th>\n",
       "      <th>x3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_acuminata\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_Clade1\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_cordata\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_grandiflora\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_umbrella\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_glauca\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_Clade2\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_macrophylla\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_fraseri\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_Clade1\"</td>\n",
       "      <td>\"1883_Magnolia_acuminata\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_Clade1\"</td>\n",
       "      <td>\"1883_Magnolia_cordata\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_Clade1\"</td>\n",
       "      <td>\"1883_Magnolia_glauca\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_Clade2\"</td>\n",
       "      <td>\"1883_Magnolia_grandiflora\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_Clade2\"</td>\n",
       "      <td>\"1883_Magnolia_umbrella\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_Clade2\"</td>\n",
       "      <td>\"1883_Magnolia_macrophylla\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_Clade2\"</td>\n",
       "      <td>\"1883_Magnolia_fraseri\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia\"</td>\n",
       "      <td>\"=\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_acuminata_var_acuminata\"</td>\n",
       "      <td>\"1883_Magnolia_acuminata\"</td>\n",
       "      <td>\"=\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_acuminata_var_subcordata\"</td>\n",
       "      <td>\"1883_Magnolia_cordata\"</td>\n",
       "      <td>\"=\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_grandiflora\"</td>\n",
       "      <td>\"1883_Magnolia_grandiflora\"</td>\n",
       "      <td>\"=\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_tripetala\"</td>\n",
       "      <td>\"1883_Magnolia_umbrella\"</td>\n",
       "      <td>\"=\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_Clade1\"</td>\n",
       "      <td>\"1883_Magnolia\"</td>\n",
       "      <td>\"&lt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_Clade2\"</td>\n",
       "      <td>\"1883_Magnolia\"</td>\n",
       "      <td>\"&lt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_acuminata_var_acuminata\"</td>\n",
       "      <td>\"1883_Magnolia\"</td>\n",
       "      <td>\"&lt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_acuminata_var_acuminata\"</td>\n",
       "      <td>\"1883_Magnolia_Clade1\"</td>\n",
       "      <td>\"&lt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_acuminata_var_subcordata\"</td>\n",
       "      <td>\"1883_Magnolia\"</td>\n",
       "      <td>\"&lt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_acuminata_var_subcordata\"</td>\n",
       "      <td>\"1883_Magnolia_Clade1\"</td>\n",
       "      <td>\"&lt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_virginiana_var_australis\"</td>\n",
       "      <td>\"1883_Magnolia\"</td>\n",
       "      <td>\"&lt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_virginiana_var_australis\"</td>\n",
       "      <td>\"1883_Magnolia_glauca\"</td>\n",
       "      <td>\"&lt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_virginiana_var_australis\"</td>\n",
       "      <td>\"1883_Magnolia_Clade2\"</td>\n",
       "      <td>\"&lt;\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_ashei\"</td>\n",
       "      <td>\"1883_Magnolia_glauca\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_ashei\"</td>\n",
       "      <td>\"1883_Magnolia_fraseri\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>102</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_macrophylla\"</td>\n",
       "      <td>\"1883_Magnolia_acuminata\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>103</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_macrophylla\"</td>\n",
       "      <td>\"1883_Magnolia_Clade1\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>104</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_macrophylla\"</td>\n",
       "      <td>\"1883_Magnolia_cordata\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>105</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_macrophylla\"</td>\n",
       "      <td>\"1883_Magnolia_grandiflora\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>106</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_macrophylla\"</td>\n",
       "      <td>\"1883_Magnolia_umbrella\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>107</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_macrophylla\"</td>\n",
       "      <td>\"1883_Magnolia_glauca\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>108</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_macrophylla\"</td>\n",
       "      <td>\"1883_Magnolia_fraseri\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>109</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_fraseri\"</td>\n",
       "      <td>\"1883_Magnolia_acuminata\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>110</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_fraseri\"</td>\n",
       "      <td>\"1883_Magnolia_Clade1\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>111</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_fraseri\"</td>\n",
       "      <td>\"1883_Magnolia_cordata\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>112</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_fraseri\"</td>\n",
       "      <td>\"1883_Magnolia_grandiflora\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>113</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_fraseri\"</td>\n",
       "      <td>\"1883_Magnolia_umbrella\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>114</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_fraseri\"</td>\n",
       "      <td>\"1883_Magnolia_glauca\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>115</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_fraseri\"</td>\n",
       "      <td>\"1883_Magnolia_macrophylla\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>116</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_pyramidata\"</td>\n",
       "      <td>\"1883_Magnolia_acuminata\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>117</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_pyramidata\"</td>\n",
       "      <td>\"1883_Magnolia_Clade1\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>118</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_pyramidata\"</td>\n",
       "      <td>\"1883_Magnolia_cordata\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>119</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_pyramidata\"</td>\n",
       "      <td>\"1883_Magnolia_grandiflora\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>120</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_pyramidata\"</td>\n",
       "      <td>\"1883_Magnolia_umbrella\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>121</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_pyramidata\"</td>\n",
       "      <td>\"1883_Magnolia_glauca\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>122</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_pyramidata\"</td>\n",
       "      <td>\"1883_Magnolia_macrophylla\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>123</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_tripetala\"</td>\n",
       "      <td>\"1883_Magnolia_acuminata\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>124</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_tripetala\"</td>\n",
       "      <td>\"1883_Magnolia_cordata\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>125</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_tripetala\"</td>\n",
       "      <td>\"1883_Magnolia_grandiflora\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>126</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_tripetala\"</td>\n",
       "      <td>\"1883_Magnolia_glauca\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>127</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_tripetala\"</td>\n",
       "      <td>\"1883_Magnolia_Clade2\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>128</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_tripetala\"</td>\n",
       "      <td>\"1883_Magnolia_macrophylla\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>129</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia_tripetala\"</td>\n",
       "      <td>\"1883_Magnolia_fraseri\"</td>\n",
       "      <td>\"!\"</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>130 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Unnamed: 0  pw                                        x1  \\\n",
       "0             0   1                           \"2014_Magnolia\"   \n",
       "1             1   1                           \"2014_Magnolia\"   \n",
       "2             2   1                           \"2014_Magnolia\"   \n",
       "3             3   1                           \"2014_Magnolia\"   \n",
       "4             4   1                           \"2014_Magnolia\"   \n",
       "5             5   1                           \"2014_Magnolia\"   \n",
       "6             6   1                           \"2014_Magnolia\"   \n",
       "7             7   1                           \"2014_Magnolia\"   \n",
       "8             8   1                           \"2014_Magnolia\"   \n",
       "9             9   1                    \"2014_Magnolia_Clade1\"   \n",
       "10           10   1                    \"2014_Magnolia_Clade1\"   \n",
       "11           11   1                    \"2014_Magnolia_Clade1\"   \n",
       "12           12   1                    \"2014_Magnolia_Clade2\"   \n",
       "13           13   1                    \"2014_Magnolia_Clade2\"   \n",
       "14           14   1                    \"2014_Magnolia_Clade2\"   \n",
       "15           15   1                    \"2014_Magnolia_Clade2\"   \n",
       "16           16   1                           \"2014_Magnolia\"   \n",
       "17           17   1   \"2014_Magnolia_acuminata_var_acuminata\"   \n",
       "18           18   1  \"2014_Magnolia_acuminata_var_subcordata\"   \n",
       "19           19   1               \"2014_Magnolia_grandiflora\"   \n",
       "20           20   1                 \"2014_Magnolia_tripetala\"   \n",
       "21           21   1                    \"2014_Magnolia_Clade1\"   \n",
       "22           22   1                    \"2014_Magnolia_Clade2\"   \n",
       "23           23   1   \"2014_Magnolia_acuminata_var_acuminata\"   \n",
       "24           24   1   \"2014_Magnolia_acuminata_var_acuminata\"   \n",
       "25           25   1  \"2014_Magnolia_acuminata_var_subcordata\"   \n",
       "26           26   1  \"2014_Magnolia_acuminata_var_subcordata\"   \n",
       "27           27   1  \"2014_Magnolia_virginiana_var_australis\"   \n",
       "28           28   1  \"2014_Magnolia_virginiana_var_australis\"   \n",
       "29           29   1  \"2014_Magnolia_virginiana_var_australis\"   \n",
       "..          ...  ..                                       ...   \n",
       "100         100   1                     \"2014_Magnolia_ashei\"   \n",
       "101         101   1                     \"2014_Magnolia_ashei\"   \n",
       "102         102   1               \"2014_Magnolia_macrophylla\"   \n",
       "103         103   1               \"2014_Magnolia_macrophylla\"   \n",
       "104         104   1               \"2014_Magnolia_macrophylla\"   \n",
       "105         105   1               \"2014_Magnolia_macrophylla\"   \n",
       "106         106   1               \"2014_Magnolia_macrophylla\"   \n",
       "107         107   1               \"2014_Magnolia_macrophylla\"   \n",
       "108         108   1               \"2014_Magnolia_macrophylla\"   \n",
       "109         109   1                   \"2014_Magnolia_fraseri\"   \n",
       "110         110   1                   \"2014_Magnolia_fraseri\"   \n",
       "111         111   1                   \"2014_Magnolia_fraseri\"   \n",
       "112         112   1                   \"2014_Magnolia_fraseri\"   \n",
       "113         113   1                   \"2014_Magnolia_fraseri\"   \n",
       "114         114   1                   \"2014_Magnolia_fraseri\"   \n",
       "115         115   1                   \"2014_Magnolia_fraseri\"   \n",
       "116         116   1                \"2014_Magnolia_pyramidata\"   \n",
       "117         117   1                \"2014_Magnolia_pyramidata\"   \n",
       "118         118   1                \"2014_Magnolia_pyramidata\"   \n",
       "119         119   1                \"2014_Magnolia_pyramidata\"   \n",
       "120         120   1                \"2014_Magnolia_pyramidata\"   \n",
       "121         121   1                \"2014_Magnolia_pyramidata\"   \n",
       "122         122   1                \"2014_Magnolia_pyramidata\"   \n",
       "123         123   1                 \"2014_Magnolia_tripetala\"   \n",
       "124         124   1                 \"2014_Magnolia_tripetala\"   \n",
       "125         125   1                 \"2014_Magnolia_tripetala\"   \n",
       "126         126   1                 \"2014_Magnolia_tripetala\"   \n",
       "127         127   1                 \"2014_Magnolia_tripetala\"   \n",
       "128         128   1                 \"2014_Magnolia_tripetala\"   \n",
       "129         129   1                 \"2014_Magnolia_tripetala\"   \n",
       "\n",
       "                              x2   x3  \n",
       "0      \"1883_Magnolia_acuminata\"  \">\"  \n",
       "1         \"1883_Magnolia_Clade1\"  \">\"  \n",
       "2        \"1883_Magnolia_cordata\"  \">\"  \n",
       "3    \"1883_Magnolia_grandiflora\"  \">\"  \n",
       "4       \"1883_Magnolia_umbrella\"  \">\"  \n",
       "5         \"1883_Magnolia_glauca\"  \">\"  \n",
       "6         \"1883_Magnolia_Clade2\"  \">\"  \n",
       "7    \"1883_Magnolia_macrophylla\"  \">\"  \n",
       "8        \"1883_Magnolia_fraseri\"  \">\"  \n",
       "9      \"1883_Magnolia_acuminata\"  \">\"  \n",
       "10       \"1883_Magnolia_cordata\"  \">\"  \n",
       "11        \"1883_Magnolia_glauca\"  \">\"  \n",
       "12   \"1883_Magnolia_grandiflora\"  \">\"  \n",
       "13      \"1883_Magnolia_umbrella\"  \">\"  \n",
       "14   \"1883_Magnolia_macrophylla\"  \">\"  \n",
       "15       \"1883_Magnolia_fraseri\"  \">\"  \n",
       "16               \"1883_Magnolia\"  \"=\"  \n",
       "17     \"1883_Magnolia_acuminata\"  \"=\"  \n",
       "18       \"1883_Magnolia_cordata\"  \"=\"  \n",
       "19   \"1883_Magnolia_grandiflora\"  \"=\"  \n",
       "20      \"1883_Magnolia_umbrella\"  \"=\"  \n",
       "21               \"1883_Magnolia\"  \"<\"  \n",
       "22               \"1883_Magnolia\"  \"<\"  \n",
       "23               \"1883_Magnolia\"  \"<\"  \n",
       "24        \"1883_Magnolia_Clade1\"  \"<\"  \n",
       "25               \"1883_Magnolia\"  \"<\"  \n",
       "26        \"1883_Magnolia_Clade1\"  \"<\"  \n",
       "27               \"1883_Magnolia\"  \"<\"  \n",
       "28        \"1883_Magnolia_glauca\"  \"<\"  \n",
       "29        \"1883_Magnolia_Clade2\"  \"<\"  \n",
       "..                           ...  ...  \n",
       "100       \"1883_Magnolia_glauca\"  \"!\"  \n",
       "101      \"1883_Magnolia_fraseri\"  \"!\"  \n",
       "102    \"1883_Magnolia_acuminata\"  \"!\"  \n",
       "103       \"1883_Magnolia_Clade1\"  \"!\"  \n",
       "104      \"1883_Magnolia_cordata\"  \"!\"  \n",
       "105  \"1883_Magnolia_grandiflora\"  \"!\"  \n",
       "106     \"1883_Magnolia_umbrella\"  \"!\"  \n",
       "107       \"1883_Magnolia_glauca\"  \"!\"  \n",
       "108      \"1883_Magnolia_fraseri\"  \"!\"  \n",
       "109    \"1883_Magnolia_acuminata\"  \"!\"  \n",
       "110       \"1883_Magnolia_Clade1\"  \"!\"  \n",
       "111      \"1883_Magnolia_cordata\"  \"!\"  \n",
       "112  \"1883_Magnolia_grandiflora\"  \"!\"  \n",
       "113     \"1883_Magnolia_umbrella\"  \"!\"  \n",
       "114       \"1883_Magnolia_glauca\"  \"!\"  \n",
       "115  \"1883_Magnolia_macrophylla\"  \"!\"  \n",
       "116    \"1883_Magnolia_acuminata\"  \"!\"  \n",
       "117       \"1883_Magnolia_Clade1\"  \"!\"  \n",
       "118      \"1883_Magnolia_cordata\"  \"!\"  \n",
       "119  \"1883_Magnolia_grandiflora\"  \"!\"  \n",
       "120     \"1883_Magnolia_umbrella\"  \"!\"  \n",
       "121       \"1883_Magnolia_glauca\"  \"!\"  \n",
       "122  \"1883_Magnolia_macrophylla\"  \"!\"  \n",
       "123    \"1883_Magnolia_acuminata\"  \"!\"  \n",
       "124      \"1883_Magnolia_cordata\"  \"!\"  \n",
       "125  \"1883_Magnolia_grandiflora\"  \"!\"  \n",
       "126       \"1883_Magnolia_glauca\"  \"!\"  \n",
       "127       \"1883_Magnolia_Clade2\"  \"!\"  \n",
       "128  \"1883_Magnolia_macrophylla\"  \"!\"  \n",
       "129      \"1883_Magnolia_fraseri\"  \"!\"  \n",
       "\n",
       "[130 rows x 5 columns]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data preprocessing\n",
    "We basically clean up the MIR table in the following steps: replace the punctuation marks and get rid of unnecessary notations using regular expressions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#copy original column to a new column and replace all the punctuation marks\n",
    "df['t1'] = df['x1'].str.replace('[^\\w\\s]','')\n",
    "df['t2'] = df['x2'].str.replace('[^\\w\\s]','')\n",
    "df['relations'] = df['x3'].str.replace('\"','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>pw</th>\n",
       "      <th>x1</th>\n",
       "      <th>x2</th>\n",
       "      <th>x3</th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_acuminata\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>2014_Magnolia</td>\n",
       "      <td>1883_Magnolia_acuminata</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_Clade1\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>2014_Magnolia</td>\n",
       "      <td>1883_Magnolia_Clade1</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_cordata\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>2014_Magnolia</td>\n",
       "      <td>1883_Magnolia_cordata</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_grandiflora\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>2014_Magnolia</td>\n",
       "      <td>1883_Magnolia_grandiflora</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_umbrella\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>2014_Magnolia</td>\n",
       "      <td>1883_Magnolia_umbrella</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  pw               x1                           x2   x3  \\\n",
       "0           0   1  \"2014_Magnolia\"    \"1883_Magnolia_acuminata\"  \">\"   \n",
       "1           1   1  \"2014_Magnolia\"       \"1883_Magnolia_Clade1\"  \">\"   \n",
       "2           2   1  \"2014_Magnolia\"      \"1883_Magnolia_cordata\"  \">\"   \n",
       "3           3   1  \"2014_Magnolia\"  \"1883_Magnolia_grandiflora\"  \">\"   \n",
       "4           4   1  \"2014_Magnolia\"     \"1883_Magnolia_umbrella\"  \">\"   \n",
       "\n",
       "              t1                         t2 relations  \n",
       "0  2014_Magnolia    1883_Magnolia_acuminata         >  \n",
       "1  2014_Magnolia       1883_Magnolia_Clade1         >  \n",
       "2  2014_Magnolia      1883_Magnolia_cordata         >  \n",
       "3  2014_Magnolia  1883_Magnolia_grandiflora         >  \n",
       "4  2014_Magnolia     1883_Magnolia_umbrella         >  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#strip the preceding taxonomy name in t1 and t2; ***NOTE: may have to revise the regex - the case 'taxon.magnolia' might not match\n",
    "df[\"t1\"] = df['t1'].str.replace('^[^_]+_','')\n",
    "df[\"t2\"] = df['t2'].str.replace('^[^_]+_','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>pw</th>\n",
       "      <th>x1</th>\n",
       "      <th>x2</th>\n",
       "      <th>x3</th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_acuminata\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia_acuminata</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_Clade1\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia_Clade1</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_cordata\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia_cordata</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_grandiflora\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia_grandiflora</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_umbrella\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia_umbrella</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  pw               x1                           x2   x3  \\\n",
       "0           0   1  \"2014_Magnolia\"    \"1883_Magnolia_acuminata\"  \">\"   \n",
       "1           1   1  \"2014_Magnolia\"       \"1883_Magnolia_Clade1\"  \">\"   \n",
       "2           2   1  \"2014_Magnolia\"      \"1883_Magnolia_cordata\"  \">\"   \n",
       "3           3   1  \"2014_Magnolia\"  \"1883_Magnolia_grandiflora\"  \">\"   \n",
       "4           4   1  \"2014_Magnolia\"     \"1883_Magnolia_umbrella\"  \">\"   \n",
       "\n",
       "         t1                    t2 relations  \n",
       "0  Magnolia    Magnolia_acuminata         >  \n",
       "1  Magnolia       Magnolia_Clade1         >  \n",
       "2  Magnolia      Magnolia_cordata         >  \n",
       "3  Magnolia  Magnolia_grandiflora         >  \n",
       "4  Magnolia     Magnolia_umbrella         >  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#replace underscores with blanks\n",
    "df[\"t1\"] = df[\"t1\"].str.replace('_',' ')\n",
    "df[\"t2\"] = df[\"t2\"].str.replace('_',' ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>pw</th>\n",
       "      <th>x1</th>\n",
       "      <th>x2</th>\n",
       "      <th>x3</th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_acuminata\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_Clade1\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia Clade1</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_cordata\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_grandiflora\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>\"2014_Magnolia\"</td>\n",
       "      <td>\"1883_Magnolia_umbrella\"</td>\n",
       "      <td>\"&gt;\"</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  pw               x1                           x2   x3  \\\n",
       "0           0   1  \"2014_Magnolia\"    \"1883_Magnolia_acuminata\"  \">\"   \n",
       "1           1   1  \"2014_Magnolia\"       \"1883_Magnolia_Clade1\"  \">\"   \n",
       "2           2   1  \"2014_Magnolia\"      \"1883_Magnolia_cordata\"  \">\"   \n",
       "3           3   1  \"2014_Magnolia\"  \"1883_Magnolia_grandiflora\"  \">\"   \n",
       "4           4   1  \"2014_Magnolia\"     \"1883_Magnolia_umbrella\"  \">\"   \n",
       "\n",
       "         t1                    t2 relations  \n",
       "0  Magnolia    Magnolia acuminata         >  \n",
       "1  Magnolia       Magnolia Clade1         >  \n",
       "2  Magnolia      Magnolia cordata         >  \n",
       "3  Magnolia  Magnolia grandiflora         >  \n",
       "4  Magnolia     Magnolia umbrella         >  "
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'pw', 'x1', 'x2', 'x3', 't1', 't2', 'relations'], dtype='object')"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#show all the column names in this table\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Drop unnecessary columns\n",
    "df.drop(['Unnamed: 0', 'pw', 'x1', 'x2', 'x3'],1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia Clade1</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         t1                    t2 relations\n",
       "0  Magnolia    Magnolia acuminata         >\n",
       "1  Magnolia       Magnolia Clade1         >\n",
       "2  Magnolia      Magnolia cordata         >\n",
       "3  Magnolia  Magnolia grandiflora         >\n",
       "4  Magnolia     Magnolia umbrella         >"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concept mapping process starts here\n",
    "Our main interest in replacing the taoxnomies are in the \"scientific names\" column - now the \"t1\" column. Therefore here we look first at all the names in this column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Magnolia', 'Magnolia Clade1', 'Magnolia Clade2',\n",
       "       'Magnolia acuminata var acuminata',\n",
       "       'Magnolia acuminata var subcordata', 'Magnolia grandiflora',\n",
       "       'Magnolia tripetala', 'Magnolia virginiana var australis',\n",
       "       'Magnolia virginiana var virginiana', 'Magnolia ashei',\n",
       "       'Magnolia macrophylla', 'Magnolia fraseri', 'Magnolia pyramidata'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#look at the unique concepts in the dataset\n",
    "df['t1'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we do concept mapping, we operate on the **leaf-node**, or the **children**, meaning that we only map the lowest level concepts in our taxonomy. In this sense, we can remove all the higher level concepts, or the **parents**. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#drop the parent level concepts - Leaving only the children in the table\n",
    "df2=df[df['t1'].str.contains(\"Magnolia Clade1|Magnolia Clade2\")==False]\n",
    "df2=df2[df2['t2'].str.contains(\"Magnolia Clade1|Magnolia Clade2\")==False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Magnolia</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>=</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>=</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>=</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Magnolia virginiana var virginiana</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Magnolia virginiana var virginiana</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>88 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     t1                    t2 relations\n",
       "0                              Magnolia    Magnolia acuminata         >\n",
       "2                              Magnolia      Magnolia cordata         >\n",
       "3                              Magnolia  Magnolia grandiflora         >\n",
       "4                              Magnolia     Magnolia umbrella         >\n",
       "5                              Magnolia       Magnolia glauca         >\n",
       "7                              Magnolia  Magnolia macrophylla         >\n",
       "8                              Magnolia      Magnolia fraseri         >\n",
       "16                             Magnolia              Magnolia         =\n",
       "17     Magnolia acuminata var acuminata    Magnolia acuminata         =\n",
       "18    Magnolia acuminata var subcordata      Magnolia cordata         =\n",
       "19                 Magnolia grandiflora  Magnolia grandiflora         =\n",
       "20                   Magnolia tripetala     Magnolia umbrella         =\n",
       "23     Magnolia acuminata var acuminata              Magnolia         <\n",
       "25    Magnolia acuminata var subcordata              Magnolia         <\n",
       "27    Magnolia virginiana var australis              Magnolia         <\n",
       "28    Magnolia virginiana var australis       Magnolia glauca         <\n",
       "30   Magnolia virginiana var virginiana              Magnolia         <\n",
       "31   Magnolia virginiana var virginiana       Magnolia glauca         <\n",
       "33                 Magnolia grandiflora              Magnolia         <\n",
       "35                       Magnolia ashei              Magnolia         <\n",
       "37                       Magnolia ashei  Magnolia macrophylla         <\n",
       "38                 Magnolia macrophylla              Magnolia         <\n",
       "40                 Magnolia macrophylla  Magnolia macrophylla         <\n",
       "41                     Magnolia fraseri              Magnolia         <\n",
       "43                     Magnolia fraseri      Magnolia fraseri         <\n",
       "44                  Magnolia pyramidata              Magnolia         <\n",
       "46                  Magnolia pyramidata      Magnolia fraseri         <\n",
       "47                   Magnolia tripetala              Magnolia         <\n",
       "60     Magnolia acuminata var acuminata      Magnolia cordata         !\n",
       "61     Magnolia acuminata var acuminata  Magnolia grandiflora         !\n",
       "..                                  ...                   ...       ...\n",
       "95                       Magnolia ashei    Magnolia acuminata         !\n",
       "97                       Magnolia ashei      Magnolia cordata         !\n",
       "98                       Magnolia ashei  Magnolia grandiflora         !\n",
       "99                       Magnolia ashei     Magnolia umbrella         !\n",
       "100                      Magnolia ashei       Magnolia glauca         !\n",
       "101                      Magnolia ashei      Magnolia fraseri         !\n",
       "102                Magnolia macrophylla    Magnolia acuminata         !\n",
       "104                Magnolia macrophylla      Magnolia cordata         !\n",
       "105                Magnolia macrophylla  Magnolia grandiflora         !\n",
       "106                Magnolia macrophylla     Magnolia umbrella         !\n",
       "107                Magnolia macrophylla       Magnolia glauca         !\n",
       "108                Magnolia macrophylla      Magnolia fraseri         !\n",
       "109                    Magnolia fraseri    Magnolia acuminata         !\n",
       "111                    Magnolia fraseri      Magnolia cordata         !\n",
       "112                    Magnolia fraseri  Magnolia grandiflora         !\n",
       "113                    Magnolia fraseri     Magnolia umbrella         !\n",
       "114                    Magnolia fraseri       Magnolia glauca         !\n",
       "115                    Magnolia fraseri  Magnolia macrophylla         !\n",
       "116                 Magnolia pyramidata    Magnolia acuminata         !\n",
       "118                 Magnolia pyramidata      Magnolia cordata         !\n",
       "119                 Magnolia pyramidata  Magnolia grandiflora         !\n",
       "120                 Magnolia pyramidata     Magnolia umbrella         !\n",
       "121                 Magnolia pyramidata       Magnolia glauca         !\n",
       "122                 Magnolia pyramidata  Magnolia macrophylla         !\n",
       "123                  Magnolia tripetala    Magnolia acuminata         !\n",
       "124                  Magnolia tripetala      Magnolia cordata         !\n",
       "125                  Magnolia tripetala  Magnolia grandiflora         !\n",
       "126                  Magnolia tripetala       Magnolia glauca         !\n",
       "128                  Magnolia tripetala  Magnolia macrophylla         !\n",
       "129                  Magnolia tripetala      Magnolia fraseri         !\n",
       "\n",
       "[88 rows x 3 columns]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Drop the top-level node \"Magnolia\" in both taxonomies\n",
    "df2=df2[df2['t1'] != \"Magnolia\"] \n",
    "df2=df2[df2['t2'] != \"Magnolia\"] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>=</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>=</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Magnolia virginiana var virginiana</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Magnolia virginiana var virginiana</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Magnolia virginiana var virginiana</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     t1                    t2 relations\n",
       "17     Magnolia acuminata var acuminata    Magnolia acuminata         =\n",
       "18    Magnolia acuminata var subcordata      Magnolia cordata         =\n",
       "19                 Magnolia grandiflora  Magnolia grandiflora         =\n",
       "20                   Magnolia tripetala     Magnolia umbrella         =\n",
       "28    Magnolia virginiana var australis       Magnolia glauca         <\n",
       "31   Magnolia virginiana var virginiana       Magnolia glauca         <\n",
       "37                       Magnolia ashei  Magnolia macrophylla         <\n",
       "40                 Magnolia macrophylla  Magnolia macrophylla         <\n",
       "43                     Magnolia fraseri      Magnolia fraseri         <\n",
       "46                  Magnolia pyramidata      Magnolia fraseri         <\n",
       "60     Magnolia acuminata var acuminata      Magnolia cordata         !\n",
       "61     Magnolia acuminata var acuminata  Magnolia grandiflora         !\n",
       "62     Magnolia acuminata var acuminata     Magnolia umbrella         !\n",
       "63     Magnolia acuminata var acuminata       Magnolia glauca         !\n",
       "65     Magnolia acuminata var acuminata  Magnolia macrophylla         !\n",
       "66     Magnolia acuminata var acuminata      Magnolia fraseri         !\n",
       "67    Magnolia acuminata var subcordata    Magnolia acuminata         !\n",
       "68    Magnolia acuminata var subcordata  Magnolia grandiflora         !\n",
       "69    Magnolia acuminata var subcordata     Magnolia umbrella         !\n",
       "70    Magnolia acuminata var subcordata       Magnolia glauca         !\n",
       "72    Magnolia acuminata var subcordata  Magnolia macrophylla         !\n",
       "73    Magnolia acuminata var subcordata      Magnolia fraseri         !\n",
       "74    Magnolia virginiana var australis    Magnolia acuminata         !\n",
       "76    Magnolia virginiana var australis      Magnolia cordata         !\n",
       "77    Magnolia virginiana var australis  Magnolia grandiflora         !\n",
       "78    Magnolia virginiana var australis     Magnolia umbrella         !\n",
       "79    Magnolia virginiana var australis  Magnolia macrophylla         !\n",
       "80    Magnolia virginiana var australis      Magnolia fraseri         !\n",
       "81   Magnolia virginiana var virginiana    Magnolia acuminata         !\n",
       "83   Magnolia virginiana var virginiana      Magnolia cordata         !\n",
       "..                                  ...                   ...       ...\n",
       "95                       Magnolia ashei    Magnolia acuminata         !\n",
       "97                       Magnolia ashei      Magnolia cordata         !\n",
       "98                       Magnolia ashei  Magnolia grandiflora         !\n",
       "99                       Magnolia ashei     Magnolia umbrella         !\n",
       "100                      Magnolia ashei       Magnolia glauca         !\n",
       "101                      Magnolia ashei      Magnolia fraseri         !\n",
       "102                Magnolia macrophylla    Magnolia acuminata         !\n",
       "104                Magnolia macrophylla      Magnolia cordata         !\n",
       "105                Magnolia macrophylla  Magnolia grandiflora         !\n",
       "106                Magnolia macrophylla     Magnolia umbrella         !\n",
       "107                Magnolia macrophylla       Magnolia glauca         !\n",
       "108                Magnolia macrophylla      Magnolia fraseri         !\n",
       "109                    Magnolia fraseri    Magnolia acuminata         !\n",
       "111                    Magnolia fraseri      Magnolia cordata         !\n",
       "112                    Magnolia fraseri  Magnolia grandiflora         !\n",
       "113                    Magnolia fraseri     Magnolia umbrella         !\n",
       "114                    Magnolia fraseri       Magnolia glauca         !\n",
       "115                    Magnolia fraseri  Magnolia macrophylla         !\n",
       "116                 Magnolia pyramidata    Magnolia acuminata         !\n",
       "118                 Magnolia pyramidata      Magnolia cordata         !\n",
       "119                 Magnolia pyramidata  Magnolia grandiflora         !\n",
       "120                 Magnolia pyramidata     Magnolia umbrella         !\n",
       "121                 Magnolia pyramidata       Magnolia glauca         !\n",
       "122                 Magnolia pyramidata  Magnolia macrophylla         !\n",
       "123                  Magnolia tripetala    Magnolia acuminata         !\n",
       "124                  Magnolia tripetala      Magnolia cordata         !\n",
       "125                  Magnolia tripetala  Magnolia grandiflora         !\n",
       "126                  Magnolia tripetala       Magnolia glauca         !\n",
       "128                  Magnolia tripetala  Magnolia macrophylla         !\n",
       "129                  Magnolia tripetala      Magnolia fraseri         !\n",
       "\n",
       "[70 rows x 3 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RCC-5 alignments notes:\n",
    "Our RCC-5 relations consist of five realtions: equals, is_included_in, includes, overlaps, and disjoints. In the concept-mapping process, since we are replacing the names in a column from one dataset to the other, the only thing that will be *equivalently mapped* are the *equals* and the *is_included_in*, for the other relations we need to do extra processing with it. If we map all five relations with equivalent mapping, it will create a lot of false alignments - we have a separate notebook to show how things can go wrong if they are not *logically* mapped. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Congruence and Inverse-Inclusion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Create new column, add the 'equivalent' and the 'is_included_in' to the new column \n",
    "a=df2['relations']==\"=\"\n",
    "b=df2['relations']==\"<\"\n",
    "df2['new']=np.where(a|b,df2['t2'],'NA')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Magnolia virginiana var virginiana</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Magnolia virginiana var virginiana</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Magnolia virginiana var virginiana</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>!</td>\n",
       "      <td>NA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     t1                    t2 relations  \\\n",
       "17     Magnolia acuminata var acuminata    Magnolia acuminata         =   \n",
       "18    Magnolia acuminata var subcordata      Magnolia cordata         =   \n",
       "19                 Magnolia grandiflora  Magnolia grandiflora         =   \n",
       "20                   Magnolia tripetala     Magnolia umbrella         =   \n",
       "28    Magnolia virginiana var australis       Magnolia glauca         <   \n",
       "31   Magnolia virginiana var virginiana       Magnolia glauca         <   \n",
       "37                       Magnolia ashei  Magnolia macrophylla         <   \n",
       "40                 Magnolia macrophylla  Magnolia macrophylla         <   \n",
       "43                     Magnolia fraseri      Magnolia fraseri         <   \n",
       "46                  Magnolia pyramidata      Magnolia fraseri         <   \n",
       "60     Magnolia acuminata var acuminata      Magnolia cordata         !   \n",
       "61     Magnolia acuminata var acuminata  Magnolia grandiflora         !   \n",
       "62     Magnolia acuminata var acuminata     Magnolia umbrella         !   \n",
       "63     Magnolia acuminata var acuminata       Magnolia glauca         !   \n",
       "65     Magnolia acuminata var acuminata  Magnolia macrophylla         !   \n",
       "66     Magnolia acuminata var acuminata      Magnolia fraseri         !   \n",
       "67    Magnolia acuminata var subcordata    Magnolia acuminata         !   \n",
       "68    Magnolia acuminata var subcordata  Magnolia grandiflora         !   \n",
       "69    Magnolia acuminata var subcordata     Magnolia umbrella         !   \n",
       "70    Magnolia acuminata var subcordata       Magnolia glauca         !   \n",
       "72    Magnolia acuminata var subcordata  Magnolia macrophylla         !   \n",
       "73    Magnolia acuminata var subcordata      Magnolia fraseri         !   \n",
       "74    Magnolia virginiana var australis    Magnolia acuminata         !   \n",
       "76    Magnolia virginiana var australis      Magnolia cordata         !   \n",
       "77    Magnolia virginiana var australis  Magnolia grandiflora         !   \n",
       "78    Magnolia virginiana var australis     Magnolia umbrella         !   \n",
       "79    Magnolia virginiana var australis  Magnolia macrophylla         !   \n",
       "80    Magnolia virginiana var australis      Magnolia fraseri         !   \n",
       "81   Magnolia virginiana var virginiana    Magnolia acuminata         !   \n",
       "83   Magnolia virginiana var virginiana      Magnolia cordata         !   \n",
       "..                                  ...                   ...       ...   \n",
       "95                       Magnolia ashei    Magnolia acuminata         !   \n",
       "97                       Magnolia ashei      Magnolia cordata         !   \n",
       "98                       Magnolia ashei  Magnolia grandiflora         !   \n",
       "99                       Magnolia ashei     Magnolia umbrella         !   \n",
       "100                      Magnolia ashei       Magnolia glauca         !   \n",
       "101                      Magnolia ashei      Magnolia fraseri         !   \n",
       "102                Magnolia macrophylla    Magnolia acuminata         !   \n",
       "104                Magnolia macrophylla      Magnolia cordata         !   \n",
       "105                Magnolia macrophylla  Magnolia grandiflora         !   \n",
       "106                Magnolia macrophylla     Magnolia umbrella         !   \n",
       "107                Magnolia macrophylla       Magnolia glauca         !   \n",
       "108                Magnolia macrophylla      Magnolia fraseri         !   \n",
       "109                    Magnolia fraseri    Magnolia acuminata         !   \n",
       "111                    Magnolia fraseri      Magnolia cordata         !   \n",
       "112                    Magnolia fraseri  Magnolia grandiflora         !   \n",
       "113                    Magnolia fraseri     Magnolia umbrella         !   \n",
       "114                    Magnolia fraseri       Magnolia glauca         !   \n",
       "115                    Magnolia fraseri  Magnolia macrophylla         !   \n",
       "116                 Magnolia pyramidata    Magnolia acuminata         !   \n",
       "118                 Magnolia pyramidata      Magnolia cordata         !   \n",
       "119                 Magnolia pyramidata  Magnolia grandiflora         !   \n",
       "120                 Magnolia pyramidata     Magnolia umbrella         !   \n",
       "121                 Magnolia pyramidata       Magnolia glauca         !   \n",
       "122                 Magnolia pyramidata  Magnolia macrophylla         !   \n",
       "123                  Magnolia tripetala    Magnolia acuminata         !   \n",
       "124                  Magnolia tripetala      Magnolia cordata         !   \n",
       "125                  Magnolia tripetala  Magnolia grandiflora         !   \n",
       "126                  Magnolia tripetala       Magnolia glauca         !   \n",
       "128                  Magnolia tripetala  Magnolia macrophylla         !   \n",
       "129                  Magnolia tripetala      Magnolia fraseri         !   \n",
       "\n",
       "                      new  \n",
       "17     Magnolia acuminata  \n",
       "18       Magnolia cordata  \n",
       "19   Magnolia grandiflora  \n",
       "20      Magnolia umbrella  \n",
       "28        Magnolia glauca  \n",
       "31        Magnolia glauca  \n",
       "37   Magnolia macrophylla  \n",
       "40   Magnolia macrophylla  \n",
       "43       Magnolia fraseri  \n",
       "46       Magnolia fraseri  \n",
       "60                     NA  \n",
       "61                     NA  \n",
       "62                     NA  \n",
       "63                     NA  \n",
       "65                     NA  \n",
       "66                     NA  \n",
       "67                     NA  \n",
       "68                     NA  \n",
       "69                     NA  \n",
       "70                     NA  \n",
       "72                     NA  \n",
       "73                     NA  \n",
       "74                     NA  \n",
       "76                     NA  \n",
       "77                     NA  \n",
       "78                     NA  \n",
       "79                     NA  \n",
       "80                     NA  \n",
       "81                     NA  \n",
       "83                     NA  \n",
       "..                    ...  \n",
       "95                     NA  \n",
       "97                     NA  \n",
       "98                     NA  \n",
       "99                     NA  \n",
       "100                    NA  \n",
       "101                    NA  \n",
       "102                    NA  \n",
       "104                    NA  \n",
       "105                    NA  \n",
       "106                    NA  \n",
       "107                    NA  \n",
       "108                    NA  \n",
       "109                    NA  \n",
       "111                    NA  \n",
       "112                    NA  \n",
       "113                    NA  \n",
       "114                    NA  \n",
       "115                    NA  \n",
       "116                    NA  \n",
       "118                    NA  \n",
       "119                    NA  \n",
       "120                    NA  \n",
       "121                    NA  \n",
       "122                    NA  \n",
       "123                    NA  \n",
       "124                    NA  \n",
       "125                    NA  \n",
       "126                    NA  \n",
       "128                    NA  \n",
       "129                    NA  \n",
       "\n",
       "[70 rows x 4 columns]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Disjointness\n",
    "The *disjoint* relations means that the two concepts in two taoxnomies are **NOT** the same. Therefore they should not even be mapped. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#get rid of all the 'disjoint' articulations \n",
    "df2=df2[df2['relations']!=\"!\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Magnolia virginiana var virginiana</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    t1                    t2 relations  \\\n",
       "17    Magnolia acuminata var acuminata    Magnolia acuminata         =   \n",
       "18   Magnolia acuminata var subcordata      Magnolia cordata         =   \n",
       "19                Magnolia grandiflora  Magnolia grandiflora         =   \n",
       "20                  Magnolia tripetala     Magnolia umbrella         =   \n",
       "28   Magnolia virginiana var australis       Magnolia glauca         <   \n",
       "31  Magnolia virginiana var virginiana       Magnolia glauca         <   \n",
       "37                      Magnolia ashei  Magnolia macrophylla         <   \n",
       "40                Magnolia macrophylla  Magnolia macrophylla         <   \n",
       "43                    Magnolia fraseri      Magnolia fraseri         <   \n",
       "46                 Magnolia pyramidata      Magnolia fraseri         <   \n",
       "\n",
       "                     new  \n",
       "17    Magnolia acuminata  \n",
       "18      Magnolia cordata  \n",
       "19  Magnolia grandiflora  \n",
       "20     Magnolia umbrella  \n",
       "28       Magnolia glauca  \n",
       "31       Magnolia glauca  \n",
       "37  Magnolia macrophylla  \n",
       "40  Magnolia macrophylla  \n",
       "43      Magnolia fraseri  \n",
       "46      Magnolia fraseri  "
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inclusion and Overlapping\n",
    "These relations will give the taxonomies more than one names to choose from, therefore we join them by using **\"OR\"**.\n",
    "Since this co-occurrence dataset does not have these two relations included, it doesn't show in the following df2 tables. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/jessicacheng/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "#Join all other articulations \"includes\", \"overlaps\" using \"OR\" on the new column\n",
    "df2['new'] = df2.groupby(['t1'])['t2'].transform(lambda x: ' OR '.join(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Magnolia virginiana var virginiana</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    t1                    t2 relations  \\\n",
       "17    Magnolia acuminata var acuminata    Magnolia acuminata         =   \n",
       "18   Magnolia acuminata var subcordata      Magnolia cordata         =   \n",
       "19                Magnolia grandiflora  Magnolia grandiflora         =   \n",
       "20                  Magnolia tripetala     Magnolia umbrella         =   \n",
       "28   Magnolia virginiana var australis       Magnolia glauca         <   \n",
       "31  Magnolia virginiana var virginiana       Magnolia glauca         <   \n",
       "37                      Magnolia ashei  Magnolia macrophylla         <   \n",
       "40                Magnolia macrophylla  Magnolia macrophylla         <   \n",
       "43                    Magnolia fraseri      Magnolia fraseri         <   \n",
       "46                 Magnolia pyramidata      Magnolia fraseri         <   \n",
       "\n",
       "                     new  \n",
       "17    Magnolia acuminata  \n",
       "18      Magnolia cordata  \n",
       "19  Magnolia grandiflora  \n",
       "20     Magnolia umbrella  \n",
       "28       Magnolia glauca  \n",
       "31       Magnolia glauca  \n",
       "37  Magnolia macrophylla  \n",
       "40  Magnolia macrophylla  \n",
       "43      Magnolia fraseri  \n",
       "46      Magnolia fraseri  "
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#drop the duplicated rows \n",
    "df2=df2.drop_duplicates('t1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Magnolia virginiana var virginiana</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Magnolia ashei</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia macrophylla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Magnolia pyramidata</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia fraseri</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    t1                    t2 relations  \\\n",
       "17    Magnolia acuminata var acuminata    Magnolia acuminata         =   \n",
       "18   Magnolia acuminata var subcordata      Magnolia cordata         =   \n",
       "19                Magnolia grandiflora  Magnolia grandiflora         =   \n",
       "20                  Magnolia tripetala     Magnolia umbrella         =   \n",
       "28   Magnolia virginiana var australis       Magnolia glauca         <   \n",
       "31  Magnolia virginiana var virginiana       Magnolia glauca         <   \n",
       "37                      Magnolia ashei  Magnolia macrophylla         <   \n",
       "40                Magnolia macrophylla  Magnolia macrophylla         <   \n",
       "43                    Magnolia fraseri      Magnolia fraseri         <   \n",
       "46                 Magnolia pyramidata      Magnolia fraseri         <   \n",
       "\n",
       "                     new  \n",
       "17    Magnolia acuminata  \n",
       "18      Magnolia cordata  \n",
       "19  Magnolia grandiflora  \n",
       "20     Magnolia umbrella  \n",
       "28       Magnolia glauca  \n",
       "31       Magnolia glauca  \n",
       "37  Magnolia macrophylla  \n",
       "40  Magnolia macrophylla  \n",
       "43      Magnolia fraseri  \n",
       "46      Magnolia fraseri  "
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Co-occurrence dataset\n",
    "This is the co-occurrence datset we got from Step 4."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#read into the co-occurrence csv file based on one taxonomy: e.g. Weakley2014's taxonomy co-occurrence datasets\n",
    "occurrences=pd.read_csv(\"inputFiles/Magnolia_secWeakley2014_geocoded.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'id', 'institutionCode', 'collectionCode',\n",
       "       'ownerInstitutionCode', 'basisOfRecord', 'occurrenceID',\n",
       "       'catalogNumber', 'otherCatalogNumbers', 'kingdom', 'phylum', 'class',\n",
       "       'order', 'family', 'scientificName', 'taxonID',\n",
       "       'scientificNameAuthorship', 'genus', 'specificEpithet', 'taxonRank',\n",
       "       'infraspecificEpithet', 'identifiedBy', 'dateIdentified',\n",
       "       'identificationReferences', 'identificationRemarks', 'taxonRemarks',\n",
       "       'identificationQualifier', 'typeStatus', 'recordedBy',\n",
       "       'associatedCollectors', 'recordNumber', 'eventDate', 'year', 'month',\n",
       "       'day', 'startDayOfYear', 'endDayOfYear', 'verbatimEventDate',\n",
       "       'occurrenceRemarks', 'habitat', 'substrate', 'verbatimAttributes',\n",
       "       'fieldNumber', 'informationWithheld', 'dataGeneralizations',\n",
       "       'dynamicProperties', 'associatedTaxa', 'reproductiveCondition',\n",
       "       'establishmentMeans', 'cultivationStatus', 'lifeStage', 'sex',\n",
       "       'individualCount', 'preparations', 'country', 'stateProvince', 'county',\n",
       "       'municipality', 'locality', 'locationRemarks', 'localitySecurity',\n",
       "       'localitySecurityReason', 'geodeticDatum',\n",
       "       'coordinateUncertaintyInMeters', 'verbatimCoordinates',\n",
       "       'georeferencedBy', 'georeferenceProtocol', 'georeferenceSources',\n",
       "       'georeferenceVerificationStatus', 'georeferenceRemarks',\n",
       "       'minimumElevationInMeters', 'maximumElevationInMeters',\n",
       "       'minimumDepthInMeters', 'maximumDepthInMeters', 'verbatimDepth',\n",
       "       'verbatimElevation', 'disposition', 'language', 'recordEnteredBy',\n",
       "       'modified', 'sourcePrimaryKey-dbpk', 'collId', 'recordId', 'references',\n",
       "       'decimalLongitude', 'decimalLatitude'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#display all the headers of the dataset\n",
    "occurrences.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#drop all the unncessary columns \n",
    "occurrences.drop(['Unnamed: 0', 'id', 'institutionCode', 'collectionCode',\n",
    "       'ownerInstitutionCode', 'basisOfRecord', 'occurrenceID',\n",
    "       'catalogNumber', 'otherCatalogNumbers', 'kingdom', 'phylum', 'class',\n",
    "       'order', 'family', 'taxonID',\n",
    "       'scientificNameAuthorship', 'genus', 'specificEpithet', 'taxonRank',\n",
    "       'infraspecificEpithet', 'identifiedBy', 'dateIdentified',\n",
    "       'identificationReferences', 'identificationRemarks', 'taxonRemarks',\n",
    "       'identificationQualifier', 'typeStatus', 'recordedBy',\n",
    "       'associatedCollectors', 'recordNumber', 'eventDate', 'year', 'month',\n",
    "       'day', 'startDayOfYear', 'endDayOfYear', 'verbatimEventDate',\n",
    "       'occurrenceRemarks', 'habitat', 'substrate', 'verbatimAttributes',\n",
    "       'fieldNumber', 'informationWithheld', 'dataGeneralizations',\n",
    "       'dynamicProperties', 'associatedTaxa', 'reproductiveCondition',\n",
    "       'establishmentMeans', 'cultivationStatus', 'lifeStage', 'sex',\n",
    "       'individualCount', 'preparations', 'country',\n",
    "       'municipality', 'locality', 'locationRemarks', 'localitySecurity',\n",
    "       'localitySecurityReason', 'geodeticDatum',\n",
    "       'coordinateUncertaintyInMeters', 'verbatimCoordinates',\n",
    "       'georeferencedBy', 'georeferenceProtocol', 'georeferenceSources',\n",
    "       'georeferenceVerificationStatus', 'georeferenceRemarks',\n",
    "       'minimumElevationInMeters', 'maximumElevationInMeters',\n",
    "       'minimumDepthInMeters', 'maximumDepthInMeters', 'verbatimDepth',\n",
    "       'verbatimElevation', 'disposition', 'language', 'recordEnteredBy',\n",
    "       'modified', 'sourcePrimaryKey-dbpk', 'collId', 'recordId', 'references'],inplace=True,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#remove all the punctuation marks in the scientificName column\n",
    "occurrences[\"scientificName\"] = occurrences['scientificName'].str.replace('[^\\w\\s]','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                   Magnolia grandiflora\n",
       "1                   Magnolia grandiflora\n",
       "2                   Magnolia grandiflora\n",
       "3                   Magnolia grandiflora\n",
       "4                   Magnolia grandiflora\n",
       "5                   Magnolia grandiflora\n",
       "6                     Magnolia tripetala\n",
       "7                   Magnolia grandiflora\n",
       "8                     Magnolia tripetala\n",
       "9                     Magnolia tripetala\n",
       "10                  Magnolia grandiflora\n",
       "11                  Magnolia grandiflora\n",
       "12                    Magnolia tripetala\n",
       "13                    Magnolia tripetala\n",
       "14                  Magnolia grandiflora\n",
       "15                    Magnolia tripetala\n",
       "16                  Magnolia grandiflora\n",
       "17                    Magnolia tripetala\n",
       "18                    Magnolia tripetala\n",
       "19                  Magnolia grandiflora\n",
       "20                    Magnolia tripetala\n",
       "21                    Magnolia tripetala\n",
       "22                    Magnolia tripetala\n",
       "23                  Magnolia grandiflora\n",
       "24                    Magnolia tripetala\n",
       "25                    Magnolia tripetala\n",
       "26                  Magnolia grandiflora\n",
       "27                  Magnolia grandiflora\n",
       "28                  Magnolia grandiflora\n",
       "29                    Magnolia tripetala\n",
       "                     ...                \n",
       "612                   Magnolia tripetala\n",
       "613                   Magnolia tripetala\n",
       "614                   Magnolia tripetala\n",
       "615                   Magnolia tripetala\n",
       "616                   Magnolia tripetala\n",
       "617                   Magnolia tripetala\n",
       "618                   Magnolia tripetala\n",
       "619                   Magnolia tripetala\n",
       "620                   Magnolia tripetala\n",
       "621                   Magnolia tripetala\n",
       "622                   Magnolia tripetala\n",
       "623                   Magnolia tripetala\n",
       "624                   Magnolia tripetala\n",
       "625                   Magnolia tripetala\n",
       "626                   Magnolia tripetala\n",
       "627                   Magnolia tripetala\n",
       "628                   Magnolia tripetala\n",
       "629                   Magnolia tripetala\n",
       "630                   Magnolia tripetala\n",
       "631                   Magnolia tripetala\n",
       "632                   Magnolia tripetala\n",
       "633                   Magnolia tripetala\n",
       "634                   Magnolia tripetala\n",
       "635                   Magnolia tripetala\n",
       "636                   Magnolia tripetala\n",
       "637                   Magnolia tripetala\n",
       "638                   Magnolia tripetala\n",
       "639                   Magnolia tripetala\n",
       "640    Magnolia acuminata var subcordata\n",
       "641    Magnolia acuminata var subcordata\n",
       "Name: scientificName, Length: 642, dtype: object"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#remove all leading or trailing whitespaces in the scientificName column\n",
    "occurrences[\"scientificName\"].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>scientificName</th>\n",
       "      <th>stateProvince</th>\n",
       "      <th>county</th>\n",
       "      <th>decimalLongitude</th>\n",
       "      <th>decimalLatitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         scientificName   stateProvince     county  decimalLongitude  \\\n",
       "0  Magnolia grandiflora  North Carolina  Brunswick        -78.244113   \n",
       "1  Magnolia grandiflora  North Carolina  Brunswick        -78.244113   \n",
       "2  Magnolia grandiflora  North Carolina  Brunswick        -78.244113   \n",
       "3  Magnolia grandiflora  North Carolina  Brunswick        -78.244113   \n",
       "4  Magnolia grandiflora  North Carolina  Brunswick        -78.244113   \n",
       "\n",
       "   decimalLatitude  \n",
       "0        34.074733  \n",
       "1        34.074733  \n",
       "2        34.074733  \n",
       "3        34.074733  \n",
       "4        34.074733  "
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "occurrences.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Magnolia acuminata var acuminata</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia acuminata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Magnolia virginiana var australis</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "      <td>&lt;</td>\n",
       "      <td>Magnolia glauca</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   t1                    t2 relations  \\\n",
       "17   Magnolia acuminata var acuminata    Magnolia acuminata         =   \n",
       "18  Magnolia acuminata var subcordata      Magnolia cordata         =   \n",
       "19               Magnolia grandiflora  Magnolia grandiflora         =   \n",
       "20                 Magnolia tripetala     Magnolia umbrella         =   \n",
       "28  Magnolia virginiana var australis       Magnolia glauca         <   \n",
       "\n",
       "                     new  \n",
       "17    Magnolia acuminata  \n",
       "18      Magnolia cordata  \n",
       "19  Magnolia grandiflora  \n",
       "20     Magnolia umbrella  \n",
       "28       Magnolia glauca  "
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge the co-occurrence dataset with the concept mapping df2 table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "result=pd.merge(occurrences,df2,left_on='scientificName',right_on='t1',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>scientificName</th>\n",
       "      <th>stateProvince</th>\n",
       "      <th>county</th>\n",
       "      <th>decimalLongitude</th>\n",
       "      <th>decimalLatitude</th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Beaufort</td>\n",
       "      <td>-76.863077</td>\n",
       "      <td>35.494745</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Beaufort</td>\n",
       "      <td>-76.863077</td>\n",
       "      <td>35.494745</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Alamance</td>\n",
       "      <td>-79.399460</td>\n",
       "      <td>36.044027</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Alamance</td>\n",
       "      <td>-79.399460</td>\n",
       "      <td>36.044027</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Alamance</td>\n",
       "      <td>-79.399460</td>\n",
       "      <td>36.044027</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>-78.827326</td>\n",
       "      <td>35.048540</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>-78.827326</td>\n",
       "      <td>35.048540</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>-78.827326</td>\n",
       "      <td>35.048540</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>-78.827326</td>\n",
       "      <td>35.048540</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Currituck</td>\n",
       "      <td>-75.918445</td>\n",
       "      <td>36.219719</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Currituck</td>\n",
       "      <td>-75.918445</td>\n",
       "      <td>36.219719</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Chatham</td>\n",
       "      <td>-79.255304</td>\n",
       "      <td>35.702755</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Chatham</td>\n",
       "      <td>-79.255304</td>\n",
       "      <td>35.702755</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Chatham</td>\n",
       "      <td>-79.255304</td>\n",
       "      <td>35.702755</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Davidson</td>\n",
       "      <td>-80.212741</td>\n",
       "      <td>35.793165</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Davidson</td>\n",
       "      <td>-80.212741</td>\n",
       "      <td>35.793165</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Davidson</td>\n",
       "      <td>-80.212741</td>\n",
       "      <td>35.793165</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Davidson</td>\n",
       "      <td>-80.212741</td>\n",
       "      <td>35.793165</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Durham</td>\n",
       "      <td>-78.876422</td>\n",
       "      <td>36.035932</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Durham</td>\n",
       "      <td>-78.876422</td>\n",
       "      <td>36.035932</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Durham</td>\n",
       "      <td>-78.876422</td>\n",
       "      <td>36.035932</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Nash</td>\n",
       "      <td>-77.986453</td>\n",
       "      <td>35.967238</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Moore</td>\n",
       "      <td>-79.481337</td>\n",
       "      <td>35.310691</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Harnett</td>\n",
       "      <td>-78.869281</td>\n",
       "      <td>35.368700</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Harnett</td>\n",
       "      <td>-78.869281</td>\n",
       "      <td>35.368700</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>612</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Breathitt</td>\n",
       "      <td>-83.324072</td>\n",
       "      <td>37.521614</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>613</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Bullitt</td>\n",
       "      <td>-85.695932</td>\n",
       "      <td>37.970027</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>614</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Edmonson</td>\n",
       "      <td>-86.238485</td>\n",
       "      <td>37.208735</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>615</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>-83.378144</td>\n",
       "      <td>38.531656</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>616</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>-83.378144</td>\n",
       "      <td>38.531656</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>617</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Menifee</td>\n",
       "      <td>-83.598898</td>\n",
       "      <td>37.941350</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>618</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Powell</td>\n",
       "      <td>-83.823853</td>\n",
       "      <td>37.831185</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>619</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Albemarle</td>\n",
       "      <td>-78.556508</td>\n",
       "      <td>38.022894</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>620</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Albemarle</td>\n",
       "      <td>-78.556508</td>\n",
       "      <td>38.022894</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>621</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Bedford</td>\n",
       "      <td>-79.524178</td>\n",
       "      <td>37.315164</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>622</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Buckingham</td>\n",
       "      <td>-78.528689</td>\n",
       "      <td>37.572200</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>623</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>-79.881042</td>\n",
       "      <td>36.991952</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>624</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>-79.881042</td>\n",
       "      <td>36.991952</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>625</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>-79.881042</td>\n",
       "      <td>36.991952</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>626</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Giles</td>\n",
       "      <td>-80.703690</td>\n",
       "      <td>37.314061</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>627</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Greene</td>\n",
       "      <td>-78.466863</td>\n",
       "      <td>38.297654</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>628</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Nelson</td>\n",
       "      <td>-78.886719</td>\n",
       "      <td>37.787412</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Nelson</td>\n",
       "      <td>-78.886719</td>\n",
       "      <td>37.787412</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Pittsylvania</td>\n",
       "      <td>-79.397119</td>\n",
       "      <td>36.821322</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>631</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Pittsylvania</td>\n",
       "      <td>-79.397119</td>\n",
       "      <td>36.821322</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>-76.644785</td>\n",
       "      <td>39.466422</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>633</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>Bartow</td>\n",
       "      <td>-84.840540</td>\n",
       "      <td>34.237864</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>634</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>-72.663766</td>\n",
       "      <td>42.340080</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>635</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>Saint Louis</td>\n",
       "      <td>-90.442979</td>\n",
       "      <td>38.640694</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>636</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>Vinton</td>\n",
       "      <td>-82.485372</td>\n",
       "      <td>39.250994</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>637</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>Le Flore</td>\n",
       "      <td>-94.703488</td>\n",
       "      <td>34.900120</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>Baltimore City</td>\n",
       "      <td>-76.575489</td>\n",
       "      <td>39.253219</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>639</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Bell</td>\n",
       "      <td>-83.674004</td>\n",
       "      <td>36.730669</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>640</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Anson</td>\n",
       "      <td>-80.102726</td>\n",
       "      <td>34.973840</td>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>641</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Anson</td>\n",
       "      <td>-80.102726</td>\n",
       "      <td>34.973840</td>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>642 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        scientificName   stateProvince          county  \\\n",
       "0                 Magnolia grandiflora  North Carolina       Brunswick   \n",
       "1                 Magnolia grandiflora  North Carolina       Brunswick   \n",
       "2                 Magnolia grandiflora  North Carolina       Brunswick   \n",
       "3                 Magnolia grandiflora  North Carolina       Brunswick   \n",
       "4                 Magnolia grandiflora  North Carolina       Brunswick   \n",
       "5                 Magnolia grandiflora  North Carolina        Beaufort   \n",
       "6                   Magnolia tripetala  North Carolina        Beaufort   \n",
       "7                 Magnolia grandiflora  North Carolina        Alamance   \n",
       "8                   Magnolia tripetala  North Carolina        Alamance   \n",
       "9                   Magnolia tripetala  North Carolina        Alamance   \n",
       "10                Magnolia grandiflora  North Carolina      Cumberland   \n",
       "11                Magnolia grandiflora  North Carolina      Cumberland   \n",
       "12                  Magnolia tripetala  North Carolina      Cumberland   \n",
       "13                  Magnolia tripetala  North Carolina      Cumberland   \n",
       "14                Magnolia grandiflora  North Carolina       Currituck   \n",
       "15                  Magnolia tripetala  North Carolina       Currituck   \n",
       "16                Magnolia grandiflora  North Carolina         Chatham   \n",
       "17                  Magnolia tripetala  North Carolina         Chatham   \n",
       "18                  Magnolia tripetala  North Carolina         Chatham   \n",
       "19                Magnolia grandiflora  North Carolina        Davidson   \n",
       "20                  Magnolia tripetala  North Carolina        Davidson   \n",
       "21                  Magnolia tripetala  North Carolina        Davidson   \n",
       "22                  Magnolia tripetala  North Carolina        Davidson   \n",
       "23                Magnolia grandiflora  North Carolina          Durham   \n",
       "24                  Magnolia tripetala  North Carolina          Durham   \n",
       "25                  Magnolia tripetala  North Carolina          Durham   \n",
       "26                Magnolia grandiflora  North Carolina            Nash   \n",
       "27                Magnolia grandiflora  North Carolina           Moore   \n",
       "28                Magnolia grandiflora  North Carolina         Harnett   \n",
       "29                  Magnolia tripetala  North Carolina         Harnett   \n",
       "..                                 ...             ...             ...   \n",
       "612                 Magnolia tripetala        Kentucky       Breathitt   \n",
       "613                 Magnolia tripetala        Kentucky         Bullitt   \n",
       "614                 Magnolia tripetala        Kentucky        Edmonson   \n",
       "615                 Magnolia tripetala        Kentucky           Lewis   \n",
       "616                 Magnolia tripetala        Kentucky           Lewis   \n",
       "617                 Magnolia tripetala        Kentucky         Menifee   \n",
       "618                 Magnolia tripetala        Kentucky          Powell   \n",
       "619                 Magnolia tripetala        Virginia       Albemarle   \n",
       "620                 Magnolia tripetala        Virginia       Albemarle   \n",
       "621                 Magnolia tripetala        Virginia         Bedford   \n",
       "622                 Magnolia tripetala        Virginia      Buckingham   \n",
       "623                 Magnolia tripetala        Virginia        Franklin   \n",
       "624                 Magnolia tripetala        Virginia        Franklin   \n",
       "625                 Magnolia tripetala        Virginia        Franklin   \n",
       "626                 Magnolia tripetala        Virginia           Giles   \n",
       "627                 Magnolia tripetala        Virginia          Greene   \n",
       "628                 Magnolia tripetala        Virginia          Nelson   \n",
       "629                 Magnolia tripetala        Virginia          Nelson   \n",
       "630                 Magnolia tripetala        Virginia    Pittsylvania   \n",
       "631                 Magnolia tripetala        Virginia    Pittsylvania   \n",
       "632                 Magnolia tripetala        Maryland       Baltimore   \n",
       "633                 Magnolia tripetala         Georgia          Bartow   \n",
       "634                 Magnolia tripetala   Massachusetts       Hampshire   \n",
       "635                 Magnolia tripetala        Missouri     Saint Louis   \n",
       "636                 Magnolia tripetala            Ohio          Vinton   \n",
       "637                 Magnolia tripetala        Oklahoma        Le Flore   \n",
       "638                 Magnolia tripetala        Maryland  Baltimore City   \n",
       "639                 Magnolia tripetala        Kentucky            Bell   \n",
       "640  Magnolia acuminata var subcordata  North Carolina           Anson   \n",
       "641  Magnolia acuminata var subcordata  North Carolina           Anson   \n",
       "\n",
       "     decimalLongitude  decimalLatitude                                 t1  \\\n",
       "0          -78.244113        34.074733               Magnolia grandiflora   \n",
       "1          -78.244113        34.074733               Magnolia grandiflora   \n",
       "2          -78.244113        34.074733               Magnolia grandiflora   \n",
       "3          -78.244113        34.074733               Magnolia grandiflora   \n",
       "4          -78.244113        34.074733               Magnolia grandiflora   \n",
       "5          -76.863077        35.494745               Magnolia grandiflora   \n",
       "6          -76.863077        35.494745                 Magnolia tripetala   \n",
       "7          -79.399460        36.044027               Magnolia grandiflora   \n",
       "8          -79.399460        36.044027                 Magnolia tripetala   \n",
       "9          -79.399460        36.044027                 Magnolia tripetala   \n",
       "10         -78.827326        35.048540               Magnolia grandiflora   \n",
       "11         -78.827326        35.048540               Magnolia grandiflora   \n",
       "12         -78.827326        35.048540                 Magnolia tripetala   \n",
       "13         -78.827326        35.048540                 Magnolia tripetala   \n",
       "14         -75.918445        36.219719               Magnolia grandiflora   \n",
       "15         -75.918445        36.219719                 Magnolia tripetala   \n",
       "16         -79.255304        35.702755               Magnolia grandiflora   \n",
       "17         -79.255304        35.702755                 Magnolia tripetala   \n",
       "18         -79.255304        35.702755                 Magnolia tripetala   \n",
       "19         -80.212741        35.793165               Magnolia grandiflora   \n",
       "20         -80.212741        35.793165                 Magnolia tripetala   \n",
       "21         -80.212741        35.793165                 Magnolia tripetala   \n",
       "22         -80.212741        35.793165                 Magnolia tripetala   \n",
       "23         -78.876422        36.035932               Magnolia grandiflora   \n",
       "24         -78.876422        36.035932                 Magnolia tripetala   \n",
       "25         -78.876422        36.035932                 Magnolia tripetala   \n",
       "26         -77.986453        35.967238               Magnolia grandiflora   \n",
       "27         -79.481337        35.310691               Magnolia grandiflora   \n",
       "28         -78.869281        35.368700               Magnolia grandiflora   \n",
       "29         -78.869281        35.368700                 Magnolia tripetala   \n",
       "..                ...              ...                                ...   \n",
       "612        -83.324072        37.521614                 Magnolia tripetala   \n",
       "613        -85.695932        37.970027                 Magnolia tripetala   \n",
       "614        -86.238485        37.208735                 Magnolia tripetala   \n",
       "615        -83.378144        38.531656                 Magnolia tripetala   \n",
       "616        -83.378144        38.531656                 Magnolia tripetala   \n",
       "617        -83.598898        37.941350                 Magnolia tripetala   \n",
       "618        -83.823853        37.831185                 Magnolia tripetala   \n",
       "619        -78.556508        38.022894                 Magnolia tripetala   \n",
       "620        -78.556508        38.022894                 Magnolia tripetala   \n",
       "621        -79.524178        37.315164                 Magnolia tripetala   \n",
       "622        -78.528689        37.572200                 Magnolia tripetala   \n",
       "623        -79.881042        36.991952                 Magnolia tripetala   \n",
       "624        -79.881042        36.991952                 Magnolia tripetala   \n",
       "625        -79.881042        36.991952                 Magnolia tripetala   \n",
       "626        -80.703690        37.314061                 Magnolia tripetala   \n",
       "627        -78.466863        38.297654                 Magnolia tripetala   \n",
       "628        -78.886719        37.787412                 Magnolia tripetala   \n",
       "629        -78.886719        37.787412                 Magnolia tripetala   \n",
       "630        -79.397119        36.821322                 Magnolia tripetala   \n",
       "631        -79.397119        36.821322                 Magnolia tripetala   \n",
       "632        -76.644785        39.466422                 Magnolia tripetala   \n",
       "633        -84.840540        34.237864                 Magnolia tripetala   \n",
       "634        -72.663766        42.340080                 Magnolia tripetala   \n",
       "635        -90.442979        38.640694                 Magnolia tripetala   \n",
       "636        -82.485372        39.250994                 Magnolia tripetala   \n",
       "637        -94.703488        34.900120                 Magnolia tripetala   \n",
       "638        -76.575489        39.253219                 Magnolia tripetala   \n",
       "639        -83.674004        36.730669                 Magnolia tripetala   \n",
       "640        -80.102726        34.973840  Magnolia acuminata var subcordata   \n",
       "641        -80.102726        34.973840  Magnolia acuminata var subcordata   \n",
       "\n",
       "                       t2 relations                   new  \n",
       "0    Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "1    Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "2    Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "3    Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "4    Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "5    Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "6       Magnolia umbrella         =     Magnolia umbrella  \n",
       "7    Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "8       Magnolia umbrella         =     Magnolia umbrella  \n",
       "9       Magnolia umbrella         =     Magnolia umbrella  \n",
       "10   Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "11   Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "12      Magnolia umbrella         =     Magnolia umbrella  \n",
       "13      Magnolia umbrella         =     Magnolia umbrella  \n",
       "14   Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "15      Magnolia umbrella         =     Magnolia umbrella  \n",
       "16   Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "17      Magnolia umbrella         =     Magnolia umbrella  \n",
       "18      Magnolia umbrella         =     Magnolia umbrella  \n",
       "19   Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "20      Magnolia umbrella         =     Magnolia umbrella  \n",
       "21      Magnolia umbrella         =     Magnolia umbrella  \n",
       "22      Magnolia umbrella         =     Magnolia umbrella  \n",
       "23   Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "24      Magnolia umbrella         =     Magnolia umbrella  \n",
       "25      Magnolia umbrella         =     Magnolia umbrella  \n",
       "26   Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "27   Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "28   Magnolia grandiflora         =  Magnolia grandiflora  \n",
       "29      Magnolia umbrella         =     Magnolia umbrella  \n",
       "..                    ...       ...                   ...  \n",
       "612     Magnolia umbrella         =     Magnolia umbrella  \n",
       "613     Magnolia umbrella         =     Magnolia umbrella  \n",
       "614     Magnolia umbrella         =     Magnolia umbrella  \n",
       "615     Magnolia umbrella         =     Magnolia umbrella  \n",
       "616     Magnolia umbrella         =     Magnolia umbrella  \n",
       "617     Magnolia umbrella         =     Magnolia umbrella  \n",
       "618     Magnolia umbrella         =     Magnolia umbrella  \n",
       "619     Magnolia umbrella         =     Magnolia umbrella  \n",
       "620     Magnolia umbrella         =     Magnolia umbrella  \n",
       "621     Magnolia umbrella         =     Magnolia umbrella  \n",
       "622     Magnolia umbrella         =     Magnolia umbrella  \n",
       "623     Magnolia umbrella         =     Magnolia umbrella  \n",
       "624     Magnolia umbrella         =     Magnolia umbrella  \n",
       "625     Magnolia umbrella         =     Magnolia umbrella  \n",
       "626     Magnolia umbrella         =     Magnolia umbrella  \n",
       "627     Magnolia umbrella         =     Magnolia umbrella  \n",
       "628     Magnolia umbrella         =     Magnolia umbrella  \n",
       "629     Magnolia umbrella         =     Magnolia umbrella  \n",
       "630     Magnolia umbrella         =     Magnolia umbrella  \n",
       "631     Magnolia umbrella         =     Magnolia umbrella  \n",
       "632     Magnolia umbrella         =     Magnolia umbrella  \n",
       "633     Magnolia umbrella         =     Magnolia umbrella  \n",
       "634     Magnolia umbrella         =     Magnolia umbrella  \n",
       "635     Magnolia umbrella         =     Magnolia umbrella  \n",
       "636     Magnolia umbrella         =     Magnolia umbrella  \n",
       "637     Magnolia umbrella         =     Magnolia umbrella  \n",
       "638     Magnolia umbrella         =     Magnolia umbrella  \n",
       "639     Magnolia umbrella         =     Magnolia umbrella  \n",
       "640      Magnolia cordata         =      Magnolia cordata  \n",
       "641      Magnolia cordata         =      Magnolia cordata  \n",
       "\n",
       "[642 rows x 9 columns]"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#add a new column \"newName\", and match the column where scientific name = t1, return t2 in the newName column\n",
    "result['newName']=np.where(result['scientificName']==result['t1'],result['new'],np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>scientificName</th>\n",
       "      <th>stateProvince</th>\n",
       "      <th>county</th>\n",
       "      <th>decimalLongitude</th>\n",
       "      <th>decimalLatitude</th>\n",
       "      <th>t1</th>\n",
       "      <th>t2</th>\n",
       "      <th>relations</th>\n",
       "      <th>new</th>\n",
       "      <th>newName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Beaufort</td>\n",
       "      <td>-76.863077</td>\n",
       "      <td>35.494745</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Beaufort</td>\n",
       "      <td>-76.863077</td>\n",
       "      <td>35.494745</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Alamance</td>\n",
       "      <td>-79.399460</td>\n",
       "      <td>36.044027</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Alamance</td>\n",
       "      <td>-79.399460</td>\n",
       "      <td>36.044027</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Alamance</td>\n",
       "      <td>-79.399460</td>\n",
       "      <td>36.044027</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>-78.827326</td>\n",
       "      <td>35.048540</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>-78.827326</td>\n",
       "      <td>35.048540</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>-78.827326</td>\n",
       "      <td>35.048540</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>-78.827326</td>\n",
       "      <td>35.048540</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Currituck</td>\n",
       "      <td>-75.918445</td>\n",
       "      <td>36.219719</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Currituck</td>\n",
       "      <td>-75.918445</td>\n",
       "      <td>36.219719</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Chatham</td>\n",
       "      <td>-79.255304</td>\n",
       "      <td>35.702755</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Chatham</td>\n",
       "      <td>-79.255304</td>\n",
       "      <td>35.702755</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Chatham</td>\n",
       "      <td>-79.255304</td>\n",
       "      <td>35.702755</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Davidson</td>\n",
       "      <td>-80.212741</td>\n",
       "      <td>35.793165</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Davidson</td>\n",
       "      <td>-80.212741</td>\n",
       "      <td>35.793165</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Davidson</td>\n",
       "      <td>-80.212741</td>\n",
       "      <td>35.793165</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Davidson</td>\n",
       "      <td>-80.212741</td>\n",
       "      <td>35.793165</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Durham</td>\n",
       "      <td>-78.876422</td>\n",
       "      <td>36.035932</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Durham</td>\n",
       "      <td>-78.876422</td>\n",
       "      <td>36.035932</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Durham</td>\n",
       "      <td>-78.876422</td>\n",
       "      <td>36.035932</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Nash</td>\n",
       "      <td>-77.986453</td>\n",
       "      <td>35.967238</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Moore</td>\n",
       "      <td>-79.481337</td>\n",
       "      <td>35.310691</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Harnett</td>\n",
       "      <td>-78.869281</td>\n",
       "      <td>35.368700</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Harnett</td>\n",
       "      <td>-78.869281</td>\n",
       "      <td>35.368700</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>612</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Breathitt</td>\n",
       "      <td>-83.324072</td>\n",
       "      <td>37.521614</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>613</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Bullitt</td>\n",
       "      <td>-85.695932</td>\n",
       "      <td>37.970027</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>614</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Edmonson</td>\n",
       "      <td>-86.238485</td>\n",
       "      <td>37.208735</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>615</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>-83.378144</td>\n",
       "      <td>38.531656</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>616</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>-83.378144</td>\n",
       "      <td>38.531656</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>617</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Menifee</td>\n",
       "      <td>-83.598898</td>\n",
       "      <td>37.941350</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>618</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Powell</td>\n",
       "      <td>-83.823853</td>\n",
       "      <td>37.831185</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>619</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Albemarle</td>\n",
       "      <td>-78.556508</td>\n",
       "      <td>38.022894</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>620</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Albemarle</td>\n",
       "      <td>-78.556508</td>\n",
       "      <td>38.022894</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>621</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Bedford</td>\n",
       "      <td>-79.524178</td>\n",
       "      <td>37.315164</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>622</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Buckingham</td>\n",
       "      <td>-78.528689</td>\n",
       "      <td>37.572200</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>623</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>-79.881042</td>\n",
       "      <td>36.991952</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>624</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>-79.881042</td>\n",
       "      <td>36.991952</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>625</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>-79.881042</td>\n",
       "      <td>36.991952</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>626</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Giles</td>\n",
       "      <td>-80.703690</td>\n",
       "      <td>37.314061</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>627</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Greene</td>\n",
       "      <td>-78.466863</td>\n",
       "      <td>38.297654</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>628</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Nelson</td>\n",
       "      <td>-78.886719</td>\n",
       "      <td>37.787412</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Nelson</td>\n",
       "      <td>-78.886719</td>\n",
       "      <td>37.787412</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Pittsylvania</td>\n",
       "      <td>-79.397119</td>\n",
       "      <td>36.821322</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>631</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>Pittsylvania</td>\n",
       "      <td>-79.397119</td>\n",
       "      <td>36.821322</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>-76.644785</td>\n",
       "      <td>39.466422</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>633</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Georgia</td>\n",
       "      <td>Bartow</td>\n",
       "      <td>-84.840540</td>\n",
       "      <td>34.237864</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>634</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>-72.663766</td>\n",
       "      <td>42.340080</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>635</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>Saint Louis</td>\n",
       "      <td>-90.442979</td>\n",
       "      <td>38.640694</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>636</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>Vinton</td>\n",
       "      <td>-82.485372</td>\n",
       "      <td>39.250994</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>637</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>Le Flore</td>\n",
       "      <td>-94.703488</td>\n",
       "      <td>34.900120</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>Baltimore City</td>\n",
       "      <td>-76.575489</td>\n",
       "      <td>39.253219</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>639</th>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Bell</td>\n",
       "      <td>-83.674004</td>\n",
       "      <td>36.730669</td>\n",
       "      <td>Magnolia tripetala</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "      <td>Magnolia umbrella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>640</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Anson</td>\n",
       "      <td>-80.102726</td>\n",
       "      <td>34.973840</td>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>641</th>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Anson</td>\n",
       "      <td>-80.102726</td>\n",
       "      <td>34.973840</td>\n",
       "      <td>Magnolia acuminata var subcordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>=</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "      <td>Magnolia cordata</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>642 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        scientificName   stateProvince          county  \\\n",
       "0                 Magnolia grandiflora  North Carolina       Brunswick   \n",
       "1                 Magnolia grandiflora  North Carolina       Brunswick   \n",
       "2                 Magnolia grandiflora  North Carolina       Brunswick   \n",
       "3                 Magnolia grandiflora  North Carolina       Brunswick   \n",
       "4                 Magnolia grandiflora  North Carolina       Brunswick   \n",
       "5                 Magnolia grandiflora  North Carolina        Beaufort   \n",
       "6                   Magnolia tripetala  North Carolina        Beaufort   \n",
       "7                 Magnolia grandiflora  North Carolina        Alamance   \n",
       "8                   Magnolia tripetala  North Carolina        Alamance   \n",
       "9                   Magnolia tripetala  North Carolina        Alamance   \n",
       "10                Magnolia grandiflora  North Carolina      Cumberland   \n",
       "11                Magnolia grandiflora  North Carolina      Cumberland   \n",
       "12                  Magnolia tripetala  North Carolina      Cumberland   \n",
       "13                  Magnolia tripetala  North Carolina      Cumberland   \n",
       "14                Magnolia grandiflora  North Carolina       Currituck   \n",
       "15                  Magnolia tripetala  North Carolina       Currituck   \n",
       "16                Magnolia grandiflora  North Carolina         Chatham   \n",
       "17                  Magnolia tripetala  North Carolina         Chatham   \n",
       "18                  Magnolia tripetala  North Carolina         Chatham   \n",
       "19                Magnolia grandiflora  North Carolina        Davidson   \n",
       "20                  Magnolia tripetala  North Carolina        Davidson   \n",
       "21                  Magnolia tripetala  North Carolina        Davidson   \n",
       "22                  Magnolia tripetala  North Carolina        Davidson   \n",
       "23                Magnolia grandiflora  North Carolina          Durham   \n",
       "24                  Magnolia tripetala  North Carolina          Durham   \n",
       "25                  Magnolia tripetala  North Carolina          Durham   \n",
       "26                Magnolia grandiflora  North Carolina            Nash   \n",
       "27                Magnolia grandiflora  North Carolina           Moore   \n",
       "28                Magnolia grandiflora  North Carolina         Harnett   \n",
       "29                  Magnolia tripetala  North Carolina         Harnett   \n",
       "..                                 ...             ...             ...   \n",
       "612                 Magnolia tripetala        Kentucky       Breathitt   \n",
       "613                 Magnolia tripetala        Kentucky         Bullitt   \n",
       "614                 Magnolia tripetala        Kentucky        Edmonson   \n",
       "615                 Magnolia tripetala        Kentucky           Lewis   \n",
       "616                 Magnolia tripetala        Kentucky           Lewis   \n",
       "617                 Magnolia tripetala        Kentucky         Menifee   \n",
       "618                 Magnolia tripetala        Kentucky          Powell   \n",
       "619                 Magnolia tripetala        Virginia       Albemarle   \n",
       "620                 Magnolia tripetala        Virginia       Albemarle   \n",
       "621                 Magnolia tripetala        Virginia         Bedford   \n",
       "622                 Magnolia tripetala        Virginia      Buckingham   \n",
       "623                 Magnolia tripetala        Virginia        Franklin   \n",
       "624                 Magnolia tripetala        Virginia        Franklin   \n",
       "625                 Magnolia tripetala        Virginia        Franklin   \n",
       "626                 Magnolia tripetala        Virginia           Giles   \n",
       "627                 Magnolia tripetala        Virginia          Greene   \n",
       "628                 Magnolia tripetala        Virginia          Nelson   \n",
       "629                 Magnolia tripetala        Virginia          Nelson   \n",
       "630                 Magnolia tripetala        Virginia    Pittsylvania   \n",
       "631                 Magnolia tripetala        Virginia    Pittsylvania   \n",
       "632                 Magnolia tripetala        Maryland       Baltimore   \n",
       "633                 Magnolia tripetala         Georgia          Bartow   \n",
       "634                 Magnolia tripetala   Massachusetts       Hampshire   \n",
       "635                 Magnolia tripetala        Missouri     Saint Louis   \n",
       "636                 Magnolia tripetala            Ohio          Vinton   \n",
       "637                 Magnolia tripetala        Oklahoma        Le Flore   \n",
       "638                 Magnolia tripetala        Maryland  Baltimore City   \n",
       "639                 Magnolia tripetala        Kentucky            Bell   \n",
       "640  Magnolia acuminata var subcordata  North Carolina           Anson   \n",
       "641  Magnolia acuminata var subcordata  North Carolina           Anson   \n",
       "\n",
       "     decimalLongitude  decimalLatitude                                 t1  \\\n",
       "0          -78.244113        34.074733               Magnolia grandiflora   \n",
       "1          -78.244113        34.074733               Magnolia grandiflora   \n",
       "2          -78.244113        34.074733               Magnolia grandiflora   \n",
       "3          -78.244113        34.074733               Magnolia grandiflora   \n",
       "4          -78.244113        34.074733               Magnolia grandiflora   \n",
       "5          -76.863077        35.494745               Magnolia grandiflora   \n",
       "6          -76.863077        35.494745                 Magnolia tripetala   \n",
       "7          -79.399460        36.044027               Magnolia grandiflora   \n",
       "8          -79.399460        36.044027                 Magnolia tripetala   \n",
       "9          -79.399460        36.044027                 Magnolia tripetala   \n",
       "10         -78.827326        35.048540               Magnolia grandiflora   \n",
       "11         -78.827326        35.048540               Magnolia grandiflora   \n",
       "12         -78.827326        35.048540                 Magnolia tripetala   \n",
       "13         -78.827326        35.048540                 Magnolia tripetala   \n",
       "14         -75.918445        36.219719               Magnolia grandiflora   \n",
       "15         -75.918445        36.219719                 Magnolia tripetala   \n",
       "16         -79.255304        35.702755               Magnolia grandiflora   \n",
       "17         -79.255304        35.702755                 Magnolia tripetala   \n",
       "18         -79.255304        35.702755                 Magnolia tripetala   \n",
       "19         -80.212741        35.793165               Magnolia grandiflora   \n",
       "20         -80.212741        35.793165                 Magnolia tripetala   \n",
       "21         -80.212741        35.793165                 Magnolia tripetala   \n",
       "22         -80.212741        35.793165                 Magnolia tripetala   \n",
       "23         -78.876422        36.035932               Magnolia grandiflora   \n",
       "24         -78.876422        36.035932                 Magnolia tripetala   \n",
       "25         -78.876422        36.035932                 Magnolia tripetala   \n",
       "26         -77.986453        35.967238               Magnolia grandiflora   \n",
       "27         -79.481337        35.310691               Magnolia grandiflora   \n",
       "28         -78.869281        35.368700               Magnolia grandiflora   \n",
       "29         -78.869281        35.368700                 Magnolia tripetala   \n",
       "..                ...              ...                                ...   \n",
       "612        -83.324072        37.521614                 Magnolia tripetala   \n",
       "613        -85.695932        37.970027                 Magnolia tripetala   \n",
       "614        -86.238485        37.208735                 Magnolia tripetala   \n",
       "615        -83.378144        38.531656                 Magnolia tripetala   \n",
       "616        -83.378144        38.531656                 Magnolia tripetala   \n",
       "617        -83.598898        37.941350                 Magnolia tripetala   \n",
       "618        -83.823853        37.831185                 Magnolia tripetala   \n",
       "619        -78.556508        38.022894                 Magnolia tripetala   \n",
       "620        -78.556508        38.022894                 Magnolia tripetala   \n",
       "621        -79.524178        37.315164                 Magnolia tripetala   \n",
       "622        -78.528689        37.572200                 Magnolia tripetala   \n",
       "623        -79.881042        36.991952                 Magnolia tripetala   \n",
       "624        -79.881042        36.991952                 Magnolia tripetala   \n",
       "625        -79.881042        36.991952                 Magnolia tripetala   \n",
       "626        -80.703690        37.314061                 Magnolia tripetala   \n",
       "627        -78.466863        38.297654                 Magnolia tripetala   \n",
       "628        -78.886719        37.787412                 Magnolia tripetala   \n",
       "629        -78.886719        37.787412                 Magnolia tripetala   \n",
       "630        -79.397119        36.821322                 Magnolia tripetala   \n",
       "631        -79.397119        36.821322                 Magnolia tripetala   \n",
       "632        -76.644785        39.466422                 Magnolia tripetala   \n",
       "633        -84.840540        34.237864                 Magnolia tripetala   \n",
       "634        -72.663766        42.340080                 Magnolia tripetala   \n",
       "635        -90.442979        38.640694                 Magnolia tripetala   \n",
       "636        -82.485372        39.250994                 Magnolia tripetala   \n",
       "637        -94.703488        34.900120                 Magnolia tripetala   \n",
       "638        -76.575489        39.253219                 Magnolia tripetala   \n",
       "639        -83.674004        36.730669                 Magnolia tripetala   \n",
       "640        -80.102726        34.973840  Magnolia acuminata var subcordata   \n",
       "641        -80.102726        34.973840  Magnolia acuminata var subcordata   \n",
       "\n",
       "                       t2 relations                   new  \\\n",
       "0    Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "1    Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "2    Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "3    Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "4    Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "5    Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "6       Magnolia umbrella         =     Magnolia umbrella   \n",
       "7    Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "8       Magnolia umbrella         =     Magnolia umbrella   \n",
       "9       Magnolia umbrella         =     Magnolia umbrella   \n",
       "10   Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "11   Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "12      Magnolia umbrella         =     Magnolia umbrella   \n",
       "13      Magnolia umbrella         =     Magnolia umbrella   \n",
       "14   Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "15      Magnolia umbrella         =     Magnolia umbrella   \n",
       "16   Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "17      Magnolia umbrella         =     Magnolia umbrella   \n",
       "18      Magnolia umbrella         =     Magnolia umbrella   \n",
       "19   Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "20      Magnolia umbrella         =     Magnolia umbrella   \n",
       "21      Magnolia umbrella         =     Magnolia umbrella   \n",
       "22      Magnolia umbrella         =     Magnolia umbrella   \n",
       "23   Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "24      Magnolia umbrella         =     Magnolia umbrella   \n",
       "25      Magnolia umbrella         =     Magnolia umbrella   \n",
       "26   Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "27   Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "28   Magnolia grandiflora         =  Magnolia grandiflora   \n",
       "29      Magnolia umbrella         =     Magnolia umbrella   \n",
       "..                    ...       ...                   ...   \n",
       "612     Magnolia umbrella         =     Magnolia umbrella   \n",
       "613     Magnolia umbrella         =     Magnolia umbrella   \n",
       "614     Magnolia umbrella         =     Magnolia umbrella   \n",
       "615     Magnolia umbrella         =     Magnolia umbrella   \n",
       "616     Magnolia umbrella         =     Magnolia umbrella   \n",
       "617     Magnolia umbrella         =     Magnolia umbrella   \n",
       "618     Magnolia umbrella         =     Magnolia umbrella   \n",
       "619     Magnolia umbrella         =     Magnolia umbrella   \n",
       "620     Magnolia umbrella         =     Magnolia umbrella   \n",
       "621     Magnolia umbrella         =     Magnolia umbrella   \n",
       "622     Magnolia umbrella         =     Magnolia umbrella   \n",
       "623     Magnolia umbrella         =     Magnolia umbrella   \n",
       "624     Magnolia umbrella         =     Magnolia umbrella   \n",
       "625     Magnolia umbrella         =     Magnolia umbrella   \n",
       "626     Magnolia umbrella         =     Magnolia umbrella   \n",
       "627     Magnolia umbrella         =     Magnolia umbrella   \n",
       "628     Magnolia umbrella         =     Magnolia umbrella   \n",
       "629     Magnolia umbrella         =     Magnolia umbrella   \n",
       "630     Magnolia umbrella         =     Magnolia umbrella   \n",
       "631     Magnolia umbrella         =     Magnolia umbrella   \n",
       "632     Magnolia umbrella         =     Magnolia umbrella   \n",
       "633     Magnolia umbrella         =     Magnolia umbrella   \n",
       "634     Magnolia umbrella         =     Magnolia umbrella   \n",
       "635     Magnolia umbrella         =     Magnolia umbrella   \n",
       "636     Magnolia umbrella         =     Magnolia umbrella   \n",
       "637     Magnolia umbrella         =     Magnolia umbrella   \n",
       "638     Magnolia umbrella         =     Magnolia umbrella   \n",
       "639     Magnolia umbrella         =     Magnolia umbrella   \n",
       "640      Magnolia cordata         =      Magnolia cordata   \n",
       "641      Magnolia cordata         =      Magnolia cordata   \n",
       "\n",
       "                  newName  \n",
       "0    Magnolia grandiflora  \n",
       "1    Magnolia grandiflora  \n",
       "2    Magnolia grandiflora  \n",
       "3    Magnolia grandiflora  \n",
       "4    Magnolia grandiflora  \n",
       "5    Magnolia grandiflora  \n",
       "6       Magnolia umbrella  \n",
       "7    Magnolia grandiflora  \n",
       "8       Magnolia umbrella  \n",
       "9       Magnolia umbrella  \n",
       "10   Magnolia grandiflora  \n",
       "11   Magnolia grandiflora  \n",
       "12      Magnolia umbrella  \n",
       "13      Magnolia umbrella  \n",
       "14   Magnolia grandiflora  \n",
       "15      Magnolia umbrella  \n",
       "16   Magnolia grandiflora  \n",
       "17      Magnolia umbrella  \n",
       "18      Magnolia umbrella  \n",
       "19   Magnolia grandiflora  \n",
       "20      Magnolia umbrella  \n",
       "21      Magnolia umbrella  \n",
       "22      Magnolia umbrella  \n",
       "23   Magnolia grandiflora  \n",
       "24      Magnolia umbrella  \n",
       "25      Magnolia umbrella  \n",
       "26   Magnolia grandiflora  \n",
       "27   Magnolia grandiflora  \n",
       "28   Magnolia grandiflora  \n",
       "29      Magnolia umbrella  \n",
       "..                    ...  \n",
       "612     Magnolia umbrella  \n",
       "613     Magnolia umbrella  \n",
       "614     Magnolia umbrella  \n",
       "615     Magnolia umbrella  \n",
       "616     Magnolia umbrella  \n",
       "617     Magnolia umbrella  \n",
       "618     Magnolia umbrella  \n",
       "619     Magnolia umbrella  \n",
       "620     Magnolia umbrella  \n",
       "621     Magnolia umbrella  \n",
       "622     Magnolia umbrella  \n",
       "623     Magnolia umbrella  \n",
       "624     Magnolia umbrella  \n",
       "625     Magnolia umbrella  \n",
       "626     Magnolia umbrella  \n",
       "627     Magnolia umbrella  \n",
       "628     Magnolia umbrella  \n",
       "629     Magnolia umbrella  \n",
       "630     Magnolia umbrella  \n",
       "631     Magnolia umbrella  \n",
       "632     Magnolia umbrella  \n",
       "633     Magnolia umbrella  \n",
       "634     Magnolia umbrella  \n",
       "635     Magnolia umbrella  \n",
       "636     Magnolia umbrella  \n",
       "637     Magnolia umbrella  \n",
       "638     Magnolia umbrella  \n",
       "639     Magnolia umbrella  \n",
       "640      Magnolia cordata  \n",
       "641      Magnolia cordata  \n",
       "\n",
       "[642 rows x 10 columns]"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#drop the unnecessary columns\n",
    "result.drop(['scientificName','t1','t2','relations','new'],inplace=True,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stateProvince</th>\n",
       "      <th>county</th>\n",
       "      <th>decimalLongitude</th>\n",
       "      <th>decimalLatitude</th>\n",
       "      <th>newName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    stateProvince     county  decimalLongitude  decimalLatitude  \\\n",
       "0  North Carolina  Brunswick        -78.244113        34.074733   \n",
       "1  North Carolina  Brunswick        -78.244113        34.074733   \n",
       "2  North Carolina  Brunswick        -78.244113        34.074733   \n",
       "3  North Carolina  Brunswick        -78.244113        34.074733   \n",
       "4  North Carolina  Brunswick        -78.244113        34.074733   \n",
       "\n",
       "                newName  \n",
       "0  Magnolia grandiflora  \n",
       "1  Magnolia grandiflora  \n",
       "2  Magnolia grandiflora  \n",
       "3  Magnolia grandiflora  \n",
       "4  Magnolia grandiflora  "
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#update the column name to scientificName\n",
    "result=result.rename(columns={'newName':'scientificName'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stateProvince</th>\n",
       "      <th>county</th>\n",
       "      <th>decimalLongitude</th>\n",
       "      <th>decimalLatitude</th>\n",
       "      <th>scientificName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Brunswick</td>\n",
       "      <td>-78.244113</td>\n",
       "      <td>34.074733</td>\n",
       "      <td>Magnolia grandiflora</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    stateProvince     county  decimalLongitude  decimalLatitude  \\\n",
       "0  North Carolina  Brunswick        -78.244113        34.074733   \n",
       "1  North Carolina  Brunswick        -78.244113        34.074733   \n",
       "2  North Carolina  Brunswick        -78.244113        34.074733   \n",
       "3  North Carolina  Brunswick        -78.244113        34.074733   \n",
       "4  North Carolina  Brunswick        -78.244113        34.074733   \n",
       "\n",
       "         scientificName  \n",
       "0  Magnolia grandiflora  \n",
       "1  Magnolia grandiflora  \n",
       "2  Magnolia grandiflora  \n",
       "3  Magnolia grandiflora  \n",
       "4  Magnolia grandiflora  "
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#save the new result as a new CSV file\n",
    "result.to_csv('MagnoliaSecChapman1883Correct.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}