Text Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Text analysis module difflib comes with Python 3.\n",
    "If you don't already have pandas, install by running either \"pip install pandas\" or \"conda install pandas\" (if you're installing with Anaconda) in the command line.  See [here](https://docs.python.org/3/installing/index.html) for more info."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from difflib import SequenceMatcher\n",
    "import difflib\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Display all outputs from a chunk, not just last ouptut\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Import cleaned text descriptions\n",
    "proj_descs = pd.read_csv(\"Text descriptions differ.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Create variable with match ratio of two strings\n",
    "\n",
    "match_ratio = [] # empty list\n",
    "\n",
    "for i in range(len(proj_descs)):\n",
    "    # Fill empty list with dictionary containing Project ID and match ratio between two descriptions\n",
    "    match_ratio.append({SequenceMatcher(None, proj_descs['Draft_desc'][i], proj_descs['Final_desc'][i]).ratio(), proj_descs['Projnum'][i]})\n",
    "    \n",
    "    # Convert dictionary to dataframe, with columns \"Match_ratio\" and \"Projnum\"\n",
    "    ratio_df = pd.DataFrame(match_ratio,\n",
    "                           columns = ['Match_ratio',\n",
    "                                     'Projnum'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Match_ratio</th>\n",
       "      <th>Projnum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.970874</td>\n",
       "      <td>880M-CA-20A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.970874</td>\n",
       "      <td>880M-CA-21A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.970874</td>\n",
       "      <td>880M-CA-22A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.660714</td>\n",
       "      <td>071-070-040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.889908</td>\n",
       "      <td>086-619-034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.855615</td>\n",
       "      <td>7102-135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.805970</td>\n",
       "      <td>2710-47B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.960000</td>\n",
       "      <td>002-614-044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.970190</td>\n",
       "      <td>027-681-035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.980392</td>\n",
       "      <td>2750-89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.954296</td>\n",
       "      <td>1012-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0.939394</td>\n",
       "      <td>090-080-017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0.990476</td>\n",
       "      <td>010-090-008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0.988506</td>\n",
       "      <td>2782-330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0.992233</td>\n",
       "      <td>6284-180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0.569588</td>\n",
       "      <td>8286-81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0.812261</td>\n",
       "      <td>2781-498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0.937888</td>\n",
       "      <td>6282-212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0.718121</td>\n",
       "      <td>8282-128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0.993377</td>\n",
       "      <td>1901-184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0.874172</td>\n",
       "      <td>6216-136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0.668293</td>\n",
       "      <td>1925-56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0.912621</td>\n",
       "      <td>1380-86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0.981413</td>\n",
       "      <td>2714-144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0.912281</td>\n",
       "      <td>2714-145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0.886076</td>\n",
       "      <td>163-291-008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0.875000</td>\n",
       "      <td>107-090-010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0.970874</td>\n",
       "      <td>1985-148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0.972477</td>\n",
       "      <td>1985-150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0.960526</td>\n",
       "      <td>6282-236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>0.825607</td>\n",
       "      <td>7002-48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>0.864865</td>\n",
       "      <td>2732-105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>0.924925</td>\n",
       "      <td>8209-111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>0.846154</td>\n",
       "      <td>880M-TE-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>0.974747</td>\n",
       "      <td>141-030-047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>0.831683</td>\n",
       "      <td>110-129-006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>0.956522</td>\n",
       "      <td>6282-190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>0.984615</td>\n",
       "      <td>1926-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>0.963504</td>\n",
       "      <td>1008-86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>0.969325</td>\n",
       "      <td>0215-77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>0.738806</td>\n",
       "      <td>2713-123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>0.989071</td>\n",
       "      <td>1928-71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>0.985251</td>\n",
       "      <td>1982-203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>0.980263</td>\n",
       "      <td>070-596-015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>0.979695</td>\n",
       "      <td>2785-408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>0.966443</td>\n",
       "      <td>6285-160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>0.985294</td>\n",
       "      <td>0207-110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>0.805970</td>\n",
       "      <td>2710-47B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>0.989305</td>\n",
       "      <td>2758-77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Match_ratio      Projnum\n",
       "0      0.970874  880M-CA-20A\n",
       "1      0.970874  880M-CA-21A\n",
       "2      0.970874  880M-CA-22A\n",
       "3      0.660714  071-070-040\n",
       "4      0.889908  086-619-034\n",
       "5      0.855615     7102-135\n",
       "6      0.805970     2710-47B\n",
       "7      0.960000  002-614-044\n",
       "8      0.970190  027-681-035\n",
       "9      0.980392      2750-89\n",
       "10     0.954296      1012-24\n",
       "11     0.939394  090-080-017\n",
       "12     0.990476  010-090-008\n",
       "13     0.988506     2782-330\n",
       "14     0.992233     6284-180\n",
       "15     0.569588      8286-81\n",
       "16     0.812261     2781-498\n",
       "17     0.937888     6282-212\n",
       "18     0.718121     8282-128\n",
       "19     0.993377     1901-184\n",
       "20     0.874172     6216-136\n",
       "21     0.668293      1925-56\n",
       "22     0.912621      1380-86\n",
       "23     0.981413     2714-144\n",
       "24     0.912281     2714-145\n",
       "25     0.886076  163-291-008\n",
       "26     0.875000  107-090-010\n",
       "27     0.970874     1985-148\n",
       "28     0.972477     1985-150\n",
       "29     0.960526     6282-236\n",
       "30     0.825607      7002-48\n",
       "31     0.864865     2732-105\n",
       "32     0.924925     8209-111\n",
       "33     0.846154   880M-TE-20\n",
       "34     0.974747  141-030-047\n",
       "35     0.831683  110-129-006\n",
       "36     0.956522     6282-190\n",
       "37     0.984615      1926-22\n",
       "38     0.963504      1008-86\n",
       "39     0.969325      0215-77\n",
       "40     0.738806     2713-123\n",
       "41     0.989071      1928-71\n",
       "42     0.985251     1982-203\n",
       "43     0.980263  070-596-015\n",
       "44     0.979695     2785-408\n",
       "45     0.966443     6285-160\n",
       "46     0.985294     0207-110\n",
       "47     0.805970     2710-47B\n",
       "48     0.989305      2758-77"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check outputs\n",
    "ratio_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using a similar method to 'mutate' is preferable, since we want the original dataframe with the additional variable.  Do this with lambda."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "proj_descs['match_ratio'] = proj_descs.apply(lambda proj_descs:\n",
    "                                             SequenceMatcher(None,\n",
    "                                                             proj_descs['Draft_desc'],\n",
    "                                                             proj_descs['Final_desc']).ratio(), axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Projnum</th>\n",
       "      <th>Draft_desc</th>\n",
       "      <th>Final_desc</th>\n",
       "      <th>Draft_total</th>\n",
       "      <th>Final_total</th>\n",
       "      <th>match_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>880M-CA-20A</td>\n",
       "      <td>17new districtwide setaside project delivery ...</td>\n",
       "      <td>17new districtwide setaside project delivery ...</td>\n",
       "      <td>5800000</td>\n",
       "      <td>5800000</td>\n",
       "      <td>0.970874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>880M-CA-21A</td>\n",
       "      <td>17new districtwide setaside project delivery ...</td>\n",
       "      <td>17new districtwide setaside project delivery ...</td>\n",
       "      <td>3050000</td>\n",
       "      <td>3050000</td>\n",
       "      <td>0.970874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>880M-CA-22A</td>\n",
       "      <td>17new districtwide setaside project delivery ...</td>\n",
       "      <td>17new districtwide setaside project delivery ...</td>\n",
       "      <td>3150000</td>\n",
       "      <td>3150000</td>\n",
       "      <td>0.970874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>071-070-040</td>\n",
       "      <td>sherburne csah 13, construct roundabout at cr ...</td>\n",
       "      <td>ac sherburne csah 13, construct roundabout at...</td>\n",
       "      <td>1000000</td>\n",
       "      <td>2250000</td>\n",
       "      <td>0.660714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>086-619-034</td>\n",
       "      <td>wright county csah 19, from lamplight dr to n ...</td>\n",
       "      <td>ac wright county csah 19, from lamplight dr t...</td>\n",
       "      <td>5000000</td>\n",
       "      <td>5000000</td>\n",
       "      <td>0.889908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7102-135</td>\n",
       "      <td>us 10, from xenia ave st to norfolk ave in elk...</td>\n",
       "      <td>us 10, from xenia ave st to norfolk ave in elk...</td>\n",
       "      <td>8750000</td>\n",
       "      <td>8750000</td>\n",
       "      <td>0.855615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2710-47B</td>\n",
       "      <td>cocii mn65, at bridge #2440 (3rd ave s) over ...</td>\n",
       "      <td>cmgc mn65, at bridge #2440 (3rd ave s) over m...</td>\n",
       "      <td>701552</td>\n",
       "      <td>32100000</td>\n",
       "      <td>0.805970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>002-614-044</td>\n",
       "      <td>ac csah 14, 0.15 mi e of csah 18, bridge 0201...</td>\n",
       "      <td>ac csah 14, 0.15 mi e of csah 18, bridge 0201...</td>\n",
       "      <td>800000</td>\n",
       "      <td>1500000</td>\n",
       "      <td>0.960000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>027-681-035</td>\n",
       "      <td>csah 81, 0.04 mile n of 71st ave (csah 8) to 0...</td>\n",
       "      <td>csah 81, 0.04 mile n of 71st ave (csah 8) to 0...</td>\n",
       "      <td>20421000</td>\n",
       "      <td>20421000</td>\n",
       "      <td>0.970190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2750-89</td>\n",
       "      <td>ada us169, at hennepin csah 130 (77th ave n/e...</td>\n",
       "      <td>us169, at hennepin csah 130 (77th ave n/elm cr...</td>\n",
       "      <td>977000</td>\n",
       "      <td>977000</td>\n",
       "      <td>0.980392</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Projnum                                         Draft_desc  \\\n",
       "0  880M-CA-20A   17new districtwide setaside project delivery ...   \n",
       "1  880M-CA-21A   17new districtwide setaside project delivery ...   \n",
       "2  880M-CA-22A   17new districtwide setaside project delivery ...   \n",
       "3  071-070-040  sherburne csah 13, construct roundabout at cr ...   \n",
       "4  086-619-034  wright county csah 19, from lamplight dr to n ...   \n",
       "5     7102-135  us 10, from xenia ave st to norfolk ave in elk...   \n",
       "6     2710-47B   cocii mn65, at bridge #2440 (3rd ave s) over ...   \n",
       "7  002-614-044   ac csah 14, 0.15 mi e of csah 18, bridge 0201...   \n",
       "8  027-681-035  csah 81, 0.04 mile n of 71st ave (csah 8) to 0...   \n",
       "9      2750-89   ada us169, at hennepin csah 130 (77th ave n/e...   \n",
       "\n",
       "                                          Final_desc  Draft_total  \\\n",
       "0   17new districtwide setaside project delivery ...      5800000   \n",
       "1   17new districtwide setaside project delivery ...      3050000   \n",
       "2   17new districtwide setaside project delivery ...      3150000   \n",
       "3   ac sherburne csah 13, construct roundabout at...      1000000   \n",
       "4   ac wright county csah 19, from lamplight dr t...      5000000   \n",
       "5  us 10, from xenia ave st to norfolk ave in elk...      8750000   \n",
       "6   cmgc mn65, at bridge #2440 (3rd ave s) over m...       701552   \n",
       "7   ac csah 14, 0.15 mi e of csah 18, bridge 0201...       800000   \n",
       "8  csah 81, 0.04 mile n of 71st ave (csah 8) to 0...     20421000   \n",
       "9  us169, at hennepin csah 130 (77th ave n/elm cr...       977000   \n",
       "\n",
       "   Final_total  match_ratio  \n",
       "0      5800000     0.970874  \n",
       "1      3050000     0.970874  \n",
       "2      3150000     0.970874  \n",
       "3      2250000     0.660714  \n",
       "4      5000000     0.889908  \n",
       "5      8750000     0.855615  \n",
       "6     32100000     0.805970  \n",
       "7      1500000     0.960000  \n",
       "8     20421000     0.970190  \n",
       "9       977000     0.980392  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "proj_descs.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Is it possible to simply look at the differences between the two strings?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-  17new districtwide setaside project delivery fy20\n",
      "+  17new districtwide setaside project delivery fy 2020\n",
      "?                                                 +  ++\n",
      "\n",
      "-  17new districtwide setaside project delivery fy21\n",
      "+  17new districtwide setaside project delivery fy 2021\n",
      "?                                                 +++\n",
      "\n",
      "-  17new districtwide setaside project delivery fy22\n",
      "+  17new districtwide setaside project delivery fy 2022\n",
      "?                                                 +++\n",
      "\n",
      "- sherburne csah 13, construct roundabout at cr 40 intersection in elk river\n",
      "+  ac sherburne csah 13, construct roundabout at sherburne cr 40 intersection and construct roundabout at sherburne co csah 33 intersection in elk river\n",
      "- wright county csah 19, from lamplight dr to n of 70th st in albertville, extend multilane roadway\n",
      "+  ac wright county csah 19, from lamplight dr to n of 70th st in albertville, extend multilane roadway(ac payback in 2020)\n",
      "? ++++                                                                                                 ++++++++++++++++++++\n",
      "\n",
      "- us 10, from xenia ave st to norfolk ave in elk river (ebl & wbl), reconstruction\n",
      "+ us 10, from xenia ave st to norfolk ave in elk river (ebl & wbl), reconstruction (including bike/ped trail)\n",
      "?                                                                                 +++++++++++++++++++++++++++\n",
      "\n",
      "+  cmgc mn65, at bridge #2440 (3rd ave s) over mississippi river in mpls rehab bridge 2440\n",
      "+  ac csah 14, 0.15 mi e of csah 18, bridge 02015 over coon creek in coon rapids rehab pier caps, replace deck panels (ac project, payback in fy20)\n",
      "+ csah 81, 0.04 mile n of 71st ave (csah 8) to 0.1 mile n of 85th ave in brooklyn park reconstruct from four lane divided rural roadway to six lane divided urban roadway, multi use trail\n",
      "+ us169, at hennepin csah 130 (77th ave n/elm creek blvd n) in brooklyn park/maple grove signal and lighting system replacement\n",
      "+  spp us212, from 0.10 mi w of the w jct mn 5/cr 131 to 0.10 mi w of csah 36 bituminous mill and overlay, cold in place recycling, pavement reconstruction, signal replacements, reduced conflict intersections at morse st and csah 34, ada improvements and drainage in norwood young america\n",
      "+ apple valley transit station modernization. expand capacity by 330 spaces at cedar ave and garret ave\n",
      "+ along mn 5 from minnewashta pkwy in victoria to century blvd in chanhassen construct mn 5 regional trail\n",
      "+  spp ada i35w, from portland ave to washington ave in mpls bituminous mill and overlay, concrete pavement rehabilitation, repair drainage structures and pipe, ada upgrades, tms\n",
      "+  ac podi spp 17new chap3 chap117 ada i35w, from co rd b2 in roseville to 0.1 mi n sunset ave (anoka cr 53) in lino lakes, construct mnpass lane from cr c to lexington ave (anoka csah 17), conc ovly from cr c to cr 53, misc pavement reconstruct & bit m&o, rehab 17 bridges and replace 5 bridges, add auxiliary lanes at multiple locations, noise walls and on us10, from n jct i35w to 0.7 mi e csah j, construct wb auxiliary lane, eb conc ovly, noise wall (associated to 6243 18) (ac project, paybacks in fy20, fy21, fy22)\n",
      "+  spp i694 in oakdale 10th st (csah10) to jct i694/i494/i94 and i494, 0.1 mi s tamarack rd to jct i694/i494/i94 – concrete overlay; auxiliary lane sb from 10th st to i94 and i94 to tamarack rd; replace and widen bridges 82831 and 82832; rehab bridge 82817 and install center pier strut, reconstruct all loops at i94 interchange and widen se ramp, overlay ramps and loops at tamarack rd and overlay se and sw ramps at 10th st interchange\n",
      "+  17new chap3 i94 bridges 27909 eb and 27925 wb and i694 bridge 27926 eb at jct shingle creek in brooklyn center install pier pile wraps for three piers\n",
      "+  spp 17new ada i94, from mn280 to 0.1 mi w of western ave in st paul bituminous mill and overlay, erosion control, drainage repair, signal replacement and ada improvements\n",
      "+ adjacent to i94 from manning ave to midwest trail n in afton reconstruct bituminous bike trail and drainage\n",
      "+  17new chap3 mn13 at mn77 in eagan replace high mast tower lighting systems\n",
      "+  ada mn51, at hoyt ave in falcon hgts/st paul signal system replacement, ada upgrades\n",
      "+  ada mn77, at dakota csah 32 (cliff rd) over mn77 and at dakota cr30 (diffley rd) in eagan rehab bridge 19067, ada improvements, guardrail, lighting replacement, concrete pavement rehab, signal replacement at mn77/cliff rd, lighting replacement at mn77/diffley rd\n",
      "+ us10 at dayton port rest area in ramsey and i35 at chisago csah1 in rush city replace lighting (tied to 1380 84)\n",
      "+  ada us12, at carlson pkwy north ramp (deer creek pkwy) and south ramp (oakland rd) in minnetonka signal replacement and ada improvements\n",
      "+  spp ada us12, at central ave (csah 101) in wayzata rehabilitation on bridge #27133 and approach panels, concrete pavement rehab, signals, lighting and ada\n",
      "+ msas 291 (beltline blvd) from w 36th st to minnetonka blvd & csah 25 from beltline blvd to lynn ave and lynn ave from csah 25 to minnetonka blvd in st louis park construct pedestrian facilities and streetscaping elements\n",
      "+ e bloomington freeway from w 106th st to w 99th st in bloomington construct sidewalk and reconstruct roadway\n",
      "+  ella spp 17new i494, from 3rd ave s in s st paul to e end of mn river bridge in eagan mill and overlay, drainage,\n",
      "rehab 7 bridges, guardrail, tms, turn lanes, signals, ada, and sidewalk (tied to 1985 150)\n",
      "+  ella i494, from e of concord st in s st paul to mn52 in inver grove heights replace lighting (tied to 1985 148)\n",
      "+  17new i94 wb from pierce st to fairview ave in st paul fence replacement\n",
      "+  17new chap3 th 21, from n end bridge 9124 to mill st in jordan replace bridge 9123 over up railroad, reconstruct pavement, build retaining walls, repair erosion, construct drainage structures and storm sewer pipe\n",
      "+  spp chap3 mn5, jct i494 in hennepin co to s end of the mississippi river bridge concrete overlay, concrete pavement rehab, grading, paving, cable median barrier,  rehab of 11 bridges\n",
      "+  17new ada mn95, from 0.2 mi north of 8th ave n in bayport to 0.1 mi south of i94 in lakeland bituminous mill and overlay, cold in place recycling, ada ped ramp upgrades, drainage\n",
      "+ districtwide setaside for roadside safety ($4.2m) projects fy 2020\n",
      "+ msas 313 (hennepin ave) from msas 186 (spruce place) to msas 375 (13th st) and on msas 179 (harmon place) from msas 223 (10th st) to msas 225 (12th st) in mpls install mast arms on five traffic signals\n",
      "+  ac 101st ave n at us 169 in brooklyn park construct interchange (associated to 2750 92) (ac project, payback in fy21)\n",
      "+  17new chap3 eb i94, from prior ave to fairview ave in st paul noisewall\n",
      "+  spp mn316, from jct us61 in goodhue county to jct us61 in dakota county bituminous mill and overlay, pedestrian trail and signs\n",
      "+ mn41, at 82nd st in chaska signal replacement and ada improvements\n",
      "+ us10, n and s ramps at round lake blvd in coon rapids signal system replacement\n",
      "+ us12, from hennepin csah 6 in orono to hennepin csah 29 in maple plain construct concrete median barrier, reconstruct pavement\n",
      "+  spp us52, from 0.1 mi n of the us52/i494 interchange in inver grove hts to plato ave in st paul mill and overlay, cpr, weight enforcement pull off pad, wim sensors, ada and signing\n",
      "+  lga i35e, from lone oak rd (csah 26) to pilot knob (csah 31) in eagan signal replacements at lone oak, pilot knob and yankee doodle, free right modifications at pilot knob\n",
      "+  sppf mn071 mn13 and dakota ave in savage, from w of dakota ave to e of yosemite grade separated interchange at dakota ave, frontage roads and access ramps\n",
      "+  chap214 i494, at hennepin csah 9 (rockford road) in plymouth re deck bridge #27972, repair approaches including bituminous mill and concrete pavement, signals, drainage, ada, add turn lanes on ramps\n",
      "+ i694 and silver lake rd n and s ramps in new brighton signal replacement\n",
      "+  spp ada mn65, from bridge under csah 10 in spring lake park to 153rd ave in ham lake mill and overlay, drainage repairs, ada improvements\n",
      "   cocii mn65, at bridge #2440 (3rd ave s) over mississippi river in mpls design of major structure rehab of bridge\n",
      "-  ac csah 14, 0.15 mi e of csah 18, bridge 02015 over coon creek in coon rapdis rehab pier caps, replace deck panels (ac project, paybacks in fy19 and fy20)\n",
      "- csah 81, 0.04 mile n of 71st ave (csah 8) to 0.04 mile s of 83rd ave in brooklyn park reconstruct from four lane divided rural roadway to six lane divided urban roadway, multi use trail\n",
      "-  ada us169, at hennepin csah 130 (77th ave n/elm creek blvd n) in brooklyn park/maple grove signal and lighting system replacement\n",
      "-  spp us212, from 0.10 mi w of the w jct mn 5/cr 131 to 0.10 mi w of csah 36 bituminous mill and overlay, pavement reconstruction, signal replacements, reduced conflict intersections at morse st and csah 34, ada improvements and drainage in norwood young america\n",
      "- apple valley transit station expansion. expand capacity by 330 spaces at cedar ave and garret ave\n",
      "- along mn 5 from minnewashta pkwy in victoria to century blvd in chanhassen reconstruct mn 5 regional trail\n",
      "-  spp i35w, from portland ave to washington ave in mpls bituminous mill and overlay, concrete pavement rehabilitation, repair drainage structures and pipe, ada upgrades, tms\n",
      "-  ac podi spp 17new chap3 ada i35w, from co rd b2 in roseville to 0.1 mi n sunset ave (anoka cr 53) in lino lakes, construct mnpass lane from cr c to lexington ave (anoka csah 17), conc ovly from cr c to cr 53, misc pavement reconstruct & bit m&o, rehab 17 bridges and replace 5 bridges, add auxiliary lanes at multiple locations, noise walls and on us10, from n jct i35w to 0.7 mi e csah j, construct wb auxiliary lane, eb conc ovly, noise wall (associated to 6243 18) (ac project, paybacks in fy20, fy21, fy22)\n",
      "-  spp i694 in oakdale 10th st (csah10) to jct i694/i494/i94 and i494, 0.1 mi s tamarack rd to jct i694/i494/i94 – concrete overlay; auxiliary lane sb from 10th st to i94; replace and widen bridges 82831 and 82832; reconstruct sw loop; i94 from jct i694/i494/i94 to 0.8 mi w radio dr remove concrete median to modify collector distributor ramp\n",
      "-  17new chap3 i94 bridges 27909 eb and 27925 wb at jct shingle creek pkwy install pier pile wraps for two piers\n",
      "-  spp 17new ada i94, from mn280 to 0.1 mi w of western ave in st paul bituminous mill and overlay, erosion control, drainage repair and ada improvements\n",
      "- adjacent to i94 from manning ave to midwest trail n in afton and from 0.8 mi e of neal ave n to 0.3 mi w of stagecoach trail n in w lakeland twp reconstruct bituminous bike trail and drainage\n",
      "-  17new chap 3 mn13 at mn77 in eagan replace high mast tower lighting systems\n",
      "- mn51, at hoyt ave in falcon hgts/st paul signal system replacement\n",
      "-  ada mn77, at dakota csah 32 (cliff rd) over mn77 and at dakota cr30 (diffley rd) in eagan rehab bridge 19067, ada improvements, guardrai, lighting\n",
      "- us10 at dayton port rest area in ramsey and i35 at chisago csah1 in rush city replace lighting\n",
      "- us12, at carlson pkwy north ramp (deer creek pkwy) and south ramp (oakland rd) in minnetonka signal replacement and ada improvements\n",
      "-  spp ada us12, at central ave (csah 101) in wayzata rehabilitation on bridge #27133 and approach panels, signals, lighting and ada\n",
      "- msas 291 (beltline blvd) from w 36th st to minnetonka blvd & csah 25 from beltline blvd to lynn ave in st louis park construct pedestrian facilities and streetscaping elements\n",
      "- e bloomington freeway from w 106th st to w 99th st in bloomington construct sidewalk\n",
      "-  flex19 spp 17new i494, from 3rd ave s in s st paul to e end of mn river bridge in eagan mill and overlay, drainage,\n",
      "rehab 6 bridges, guardrail, tms, turn lanes, signals, ada, and sidewalk (tied to 1985 150)\n",
      "- i494, from e of concord st in s st paul to mn52 in inver grove heights replace lighting (tied to 1985 148)\n",
      "-  17new chap3 i94 wb from pierce st to fairview ave in st paul fence replacement\n",
      "-  17new chap3 th 21, from helena st to the northern intersection with helena blvd in jordan replace bridge 9123 over up railroad, reconstruct pavement, build retaining walls, repair erosion, construct drainage structures and storm sewer pipe\n",
      "-  spp mn5, jct i494 in hennepin co to s end of the minnesota river bridge concrete overlay, grading, paving, cable median barrier,  rehab of 11 bridges\n",
      "-  17new ada mn95, from 0.2 mi north of 8th ave n in bayport to 0.1 mi south of i94 in lakeland bituminous mill and overlay, ada ped ramp upgrades, drainage\n",
      "- districtwide setaside for water resources ($64k), roadside safety ($4.2m) projects fy 2020\n",
      "- msas 25 (hennepin ave) from msas 86 (spruce place) to msas 75 (13th st) and on msas 79 (harmon place) from msas 23 (10th st) to msas 25 (12th st) in mpls install mast arms on five traffic signals\n",
      "- 101st ave n at us 169 in brooklyn park construct interchange (associated to 2750 92)\n",
      "-  chap3 eb i94, from prior ave to fairview ave in st paul noisewall\n",
      "-  spp ada mn316, from jct us61 in goodhue county to jct us61 in dakota county bituminous mill and overlay, pedestrian trail and signs\n",
      "-  ada mn41, at 82nd st in chaska signal replacement and ada improvements\n",
      "-  ada us10, n and s ramps at round lake blvd in coon rapids signal system replacement\n",
      "- us12, from hennepin csah 6 in orono to intersection with hennepin csah 29 in maple plain widen to construct 10 foot buffer with median barrier\n",
      "-  spp ada us52, from 0.1 mi n of the us52/i494 interchange in inver grove hts to plato ave in st paul mill and overlay, cpr, weight enforcement pull off pad, wim sensors, ada and signing\n",
      "- i35e, from lone oak rd (csah 26) to pilot knob (csah 31) in eagan signal replacements at lone oak, pilot knob and yankee doodle, free right modifications at pilot knob\n",
      "-  sppf mn13 and dakota ave in savage, from w of dakota ave to e of yosemite grade separated interchange at dakota ave, frontage roads and access ramps\n",
      "-  spp i494, at hennepin csah 9 (rockford road) in plymouth re deck bridge #27972, repair approaches including bituminous mill and concrete pavement, signals, drainage, ada, add turn lanes on ramps\n",
      "-  ada i694 and silver lake rd n and s ramps in new brighton signal replacement\n",
      "-  spp mn65, from bridge under csah 10 in spring lake park to 153rd ave in ham lake mill and overlay, drainage repairs, ada improvements\n",
      "-  cmgc mn65, at bridge #2440 (3rd ave s) over mississippi river in mpls rehab bridge 2440\n",
      "-  spp mn77, from 0.5 mi s of old shakopee road in bloomington to mn62 in minneapolis bituminous mill and overlay and extend right turn lane on exit ramp from nb mn77 to old shakopee road\n",
      "+  spp ada mn77, from 0.5 mi s of old shakopee road in bloomington to mn62 in minneapolis bituminous mill and overlay and extend right turn lane on exit ramp from nb mn77 to old shakopee road\n",
      "?     ++++\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for line in enumerate(difflib.ndiff(proj_descs['Draft_desc'], proj_descs['Final_desc'])):\n",
    "    print (line)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are quite a few strings where a couple additions at the beginning of the string shift the entire statement downward, making everything look like an addition.  Instead, collect blocks where text is the same, and then remove these blocks from the original strings to obtain the difference between the two."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['acsherburneintersectionandconstructroundaboutatsherburnecocsah33']"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output_list = [li for li in difflib.ndiff(proj_descs['Draft_desc'][3], proj_descs['Final_desc'][3]) if li[0] != ' ']\n",
    "words = [w.replace(' ', '').replace('+', '') for w in output_list]\n",
    "#words\n",
    "merged_list = [''.join(words)]\n",
    "merged_list\n",
    "\n",
    "#str_no_plus = merged_list[0].replace('+', '')\n",
    "#str_no_plus.replace(' ', '')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Draft_final</th>\n",
       "      <th>Final_draft</th>\n",
       "      <th>Projnum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1n5th</td>\n",
       "      <td>04s3rd</td>\n",
       "      <td>027-681-035</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Draft_final Final_draft      Projnum\n",
       "0       1n5th      04s3rd  027-681-035"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string1 = proj_descs['Draft_desc'][8]\n",
    "string2 = proj_descs['Final_desc'][8]\n",
    "projnum = proj_descs['Projnum'][8]\n",
    "\n",
    "matches = difflib.SequenceMatcher(\n",
    "    None, string1, string2).get_matching_blocks()\n",
    "\n",
    "match_lista = []\n",
    "match_listb = []\n",
    "for match in matches:\n",
    "    match_lista.append(string1[match.a:match.a+match.size])\n",
    "    match_listb.append(string2[match.b:match.b+match.size])\n",
    "\n",
    "i = 0\n",
    "while i <len(match_lista):\n",
    "    string1 = string1.replace(match_lista[i], '')\n",
    "    string2 = string2.replace(match_listb[i], '')\n",
    "    i += 1\n",
    "    \n",
    "strings = pd.DataFrame({'Final_draft': [string1],\n",
    "                        'Draft_final': [string2],\n",
    "                        'Projnum': [projnum]})\n",
    "strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['csah 81, 0.04 mile n of 71st ave (csah 8) to 0.',\n",
       " ' mile ',\n",
       " ' of 8',\n",
       " ' ave in brooklyn park reconstruct from four lane divided rural roadway to six lane divided urban roadway, multi use trail',\n",
       " '']"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_lista"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's great!  We got all the code to work for a single row.  Now let's create a function so we can iterate over all the rows in the input df."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Create a function that can be iterated with\n",
    "def find_difference(x):\n",
    "    string1 = proj_descs['Draft_desc'][x]\n",
    "    string2 = proj_descs['Final_desc'][x]\n",
    "    projnum = proj_descs['Projnum'][x]\n",
    "\n",
    "    matches = difflib.SequenceMatcher(\n",
    "        None, string1, string2).get_matching_blocks()\n",
    "\n",
    "    match_lista = []\n",
    "    match_listb = []\n",
    "    for match in matches:\n",
    "        match_lista.append(string1[match.a:match.a+match.size])\n",
    "        match_listb.append(string2[match.b:match.b+match.size])\n",
    "    \n",
    "    i = 0\n",
    "    while i <len(match_lista):\n",
    "        string1 = string1.replace(match_lista[i], '')\n",
    "        string2 = string2.replace(match_listb[i], '')\n",
    "        i += 1\n",
    "    \n",
    "    strings = pd.DataFrame({'Deletion_from_draft': [string1],\n",
    "                        'Addition_in_final': [string2],\n",
    "                        'Projnum': [projnum]})\n",
    "    return(strings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "diff_21 = find_difference(21)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "differences = []\n",
    "\n",
    "for i in range(len(proj_descs)):\n",
    "    differences.append(find_difference(i))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "diff_df = pd.concat(differences)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Addition_in_final</th>\n",
       "      <th>Deletion_from_draft</th>\n",
       "      <th>Projnum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>880M-CA-20A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20</td>\n",
       "      <td></td>\n",
       "      <td>880M-CA-21A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20</td>\n",
       "      <td></td>\n",
       "      <td>880M-CA-22A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ac sherburne  intersection and construct roun...</td>\n",
       "      <td></td>\n",
       "      <td>071-070-040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ac (ac payback in 2020)</td>\n",
       "      <td></td>\n",
       "      <td>086-619-034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(including bike/ped trail)</td>\n",
       "      <td></td>\n",
       "      <td>7102-135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>mg 2440</td>\n",
       "      <td>oiidesign of major struture  of</td>\n",
       "      <td>2710-47B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>is rehab pier caps, replace eck panels (ac pro...</td>\n",
       "      <td>is rehab pier caps, replace eck panels (ac pro...</td>\n",
       "      <td>002-614-044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1n5th</td>\n",
       "      <td>04s3rd</td>\n",
       "      <td>027-681-035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>ada</td>\n",
       "      <td>2750-89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cold in place recycling,</td>\n",
       "      <td></td>\n",
       "      <td>1012-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>modrniztion. xpnd cpcity by 330 spcs t cdr v n...</td>\n",
       "      <td>xpnsion. xpnd cpcity by 330 spcs t cdr v nd gr...</td>\n",
       "      <td>090-080-017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>re</td>\n",
       "      <td>010-090-008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ada</td>\n",
       "      <td></td>\n",
       "      <td>2782-330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>chap117</td>\n",
       "      <td></td>\n",
       "      <td>6284-180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>and i94 to tamarack rdhab bridge 82817 and in...</td>\n",
       "      <td>conct s loop; i94 from jct i694/i494/i94 to .8...</td>\n",
       "      <td>8286-81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>nd i694 bridge 27926 eb ain brooln centerhree</td>\n",
       "      <td>pwwo</td>\n",
       "      <td>2781-498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>, signal replacement</td>\n",
       "      <td></td>\n",
       "      <td>6282-212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>and from 0.8 mi e of neal ave n to 0.3 mi w of...</td>\n",
       "      <td>8282-128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1901-184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ada , ada upgrades</td>\n",
       "      <td></td>\n",
       "      <td>6216-136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>l, lighting replacement, concrete pavement reh...</td>\n",
       "      <td>, lighting</td>\n",
       "      <td>1925-56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(tied to 1380 84)</td>\n",
       "      <td></td>\n",
       "      <td>1380-86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ada</td>\n",
       "      <td></td>\n",
       "      <td>2714-144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>concrete pavement rehab,</td>\n",
       "      <td></td>\n",
       "      <td>2714-145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>and lynn ave from csah 25 to minnetonka blvd</td>\n",
       "      <td></td>\n",
       "      <td>163-291-008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>and reconstruct roadway</td>\n",
       "      <td></td>\n",
       "      <td>107-090-010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ellaspp17newi494,from3rdavesinsstpaultoeendofm...</td>\n",
       "      <td>flex19spp17newi494,from3rdavesinsstpaultoeendo...</td>\n",
       "      <td>1985-148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ella</td>\n",
       "      <td></td>\n",
       "      <td>1985-150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>chap3</td>\n",
       "      <td>6282-236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>n endridge 9124 to mill st in jordan replaceri...</td>\n",
       "      <td>helena st to the northern intersection with he...</td>\n",
       "      <td>7002-48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>chap3iippi, concrete pavement rehab, grading,...</td>\n",
       "      <td>nneota, grading, paving, cable median barrier,...</td>\n",
       "      <td>2732-105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cold in place recycling,</td>\n",
       "      <td></td>\n",
       "      <td>8209-111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>water resources ($64k),</td>\n",
       "      <td>880M-TE-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>313 (hennepin ave) from 186 (spruce place) to ...</td>\n",
       "      <td>25 (hennepin ave) from 86 (spruce place) to 75...</td>\n",
       "      <td>141-030-047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ac  (ac project, payback in fy21)</td>\n",
       "      <td></td>\n",
       "      <td>110-129-006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>17new</td>\n",
       "      <td></td>\n",
       "      <td>6282-190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>ada</td>\n",
       "      <td>1926-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>ada</td>\n",
       "      <td>1008-86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>ada</td>\n",
       "      <td>0215-77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cncr mdian barrir, rpavmn</td>\n",
       "      <td>inrscin wih widn  10 f buffr wih mdian barrir</td>\n",
       "      <td>2713-123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>ada</td>\n",
       "      <td>1928-71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>lga</td>\n",
       "      <td></td>\n",
       "      <td>1982-203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>mn071</td>\n",
       "      <td></td>\n",
       "      <td>070-596-015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cha214i494,athenneincsah9(rockfordroad)inlymou...</td>\n",
       "      <td>si494,athenneincsah9(rockfordroad)inlymouthred...</td>\n",
       "      <td>2785-408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td></td>\n",
       "      <td>ada</td>\n",
       "      <td>6285-160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ada</td>\n",
       "      <td></td>\n",
       "      <td>0207-110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>oiidesign of major struture  of</td>\n",
       "      <td>mg 2440</td>\n",
       "      <td>2710-47B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ada</td>\n",
       "      <td></td>\n",
       "      <td>2758-77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   Addition_in_final  \\\n",
       "0                                                      \n",
       "0                                                 20   \n",
       "0                                                 20   \n",
       "0   ac sherburne  intersection and construct roun...   \n",
       "0                            ac (ac payback in 2020)   \n",
       "0                         (including bike/ped trail)   \n",
       "0                                            mg 2440   \n",
       "0  is rehab pier caps, replace eck panels (ac pro...   \n",
       "0                                              1n5th   \n",
       "0                                                      \n",
       "0                          cold in place recycling,    \n",
       "0  modrniztion. xpnd cpcity by 330 spcs t cdr v n...   \n",
       "0                                                      \n",
       "0                                                ada   \n",
       "0                                            chap117   \n",
       "0   and i94 to tamarack rdhab bridge 82817 and in...   \n",
       "0      nd i694 bridge 27926 eb ain brooln centerhree   \n",
       "0                               , signal replacement   \n",
       "0                                                      \n",
       "0                                                      \n",
       "0                                 ada , ada upgrades   \n",
       "0  l, lighting replacement, concrete pavement reh...   \n",
       "0                                  (tied to 1380 84)   \n",
       "0                                               ada    \n",
       "0                          concrete pavement rehab,    \n",
       "0      and lynn ave from csah 25 to minnetonka blvd    \n",
       "0                            and reconstruct roadway   \n",
       "0  ellaspp17newi494,from3rdavesinsstpaultoeendofm...   \n",
       "0                                              ella    \n",
       "0                                                      \n",
       "0  n endridge 9124 to mill st in jordan replaceri...   \n",
       "0   chap3iippi, concrete pavement rehab, grading,...   \n",
       "0                          cold in place recycling,    \n",
       "0                                                      \n",
       "0  313 (hennepin ave) from 186 (spruce place) to ...   \n",
       "0                  ac  (ac project, payback in fy21)   \n",
       "0                                              17new   \n",
       "0                                                      \n",
       "0                                                      \n",
       "0                                                      \n",
       "0                          cncr mdian barrir, rpavmn   \n",
       "0                                                      \n",
       "0                                               lga    \n",
       "0                                              mn071   \n",
       "0  cha214i494,athenneincsah9(rockfordroad)inlymou...   \n",
       "0                                                      \n",
       "0                                                ada   \n",
       "0                    oiidesign of major struture  of   \n",
       "0                                                ada   \n",
       "\n",
       "                                 Deletion_from_draft      Projnum  \n",
       "0                                                     880M-CA-20A  \n",
       "0                                                     880M-CA-21A  \n",
       "0                                                     880M-CA-22A  \n",
       "0                                                     071-070-040  \n",
       "0                                                     086-619-034  \n",
       "0                                                        7102-135  \n",
       "0                    oiidesign of major struture  of     2710-47B  \n",
       "0  is rehab pier caps, replace eck panels (ac pro...  002-614-044  \n",
       "0                                             04s3rd  027-681-035  \n",
       "0                                               ada       2750-89  \n",
       "0                                                         1012-24  \n",
       "0  xpnsion. xpnd cpcity by 330 spcs t cdr v nd gr...  090-080-017  \n",
       "0                                                 re  010-090-008  \n",
       "0                                                        2782-330  \n",
       "0                                                        6284-180  \n",
       "0  conct s loop; i94 from jct i694/i494/i94 to .8...      8286-81  \n",
       "0                                               pwwo     2781-498  \n",
       "0                                                        6282-212  \n",
       "0  and from 0.8 mi e of neal ave n to 0.3 mi w of...     8282-128  \n",
       "0                                                        1901-184  \n",
       "0                                                        6216-136  \n",
       "0                                         , lighting      1925-56  \n",
       "0                                                         1380-86  \n",
       "0                                                        2714-144  \n",
       "0                                                        2714-145  \n",
       "0                                                     163-291-008  \n",
       "0                                                     107-090-010  \n",
       "0  flex19spp17newi494,from3rdavesinsstpaultoeendo...     1985-148  \n",
       "0                                                        1985-150  \n",
       "0                                              chap3     6282-236  \n",
       "0  helena st to the northern intersection with he...      7002-48  \n",
       "0  nneota, grading, paving, cable median barrier,...     2732-105  \n",
       "0                                                        8209-111  \n",
       "0                            water resources ($64k),   880M-TE-20  \n",
       "0  25 (hennepin ave) from 86 (spruce place) to 75...  141-030-047  \n",
       "0                                                     110-129-006  \n",
       "0                                                        6282-190  \n",
       "0                                                ada      1926-22  \n",
       "0                                               ada       1008-86  \n",
       "0                                               ada       0215-77  \n",
       "0      inrscin wih widn  10 f buffr wih mdian barrir     2713-123  \n",
       "0                                                ada      1928-71  \n",
       "0                                                        1982-203  \n",
       "0                                                     070-596-015  \n",
       "0  si494,athenneincsah9(rockfordroad)inlymouthred...     2785-408  \n",
       "0                                               ada      6285-160  \n",
       "0                                                        0207-110  \n",
       "0                                            mg 2440     2710-47B  \n",
       "0                                                         2758-77  "
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "proj_comparisons = pd.merge(proj_descs, diff_df, on = 'Projnum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.DataFrame.to_csv(proj_comparisons, \"Differing Descriptions Comparison.csv\")"
   ]
  }
 ],
 "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.5.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}