parse-exhibit-a.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import re\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from IPython.display import display\n",
    "from __future__ import print_function, division"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extracts 2016 & 2017 ownership relationships into single dataframe\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def extractOwnership(data):\n",
    "    dataframes = []\n",
    "\n",
    "    companies = data[data.id.notnull()]\n",
    "    row_iterator = companies.iterrows()\n",
    "    _, last = row_iterator.next()\n",
    "\n",
    "    for i, row in row_iterator:\n",
    "        company = last.company.split('*')[0].strip()\n",
    "        dft = data[_:i]\n",
    "        dft.columns = ['col0','col1','col2','col3','title','role']\n",
    "        dft = dft.dropna(axis=0, how='all')\n",
    "        start = dft[dft.col1=='Owned by:'].index+1\n",
    "        end = dft[dft.col1=='Has ownership interest in:'].index-1\n",
    "        if(len(end)>0):\n",
    "            dfts = dft.loc[start[0]:end[0]]\n",
    "            dfts_h = dft.loc[(end[0]+2):]\n",
    "        else:\n",
    "            dfts = dft.loc[start[0]:]\n",
    "            \n",
    "        dfts = dfts.drop(['col0','title'],axis=1)\n",
    "        \n",
    "        dfts.col1 = company\n",
    "        dfts.columns = ['company','percent','owner','role']\n",
    "        dataframes.append(dfts)\n",
    "        last = row\n",
    "        _ = i\n",
    "\n",
    "    df = pd.concat(dataframes)\n",
    "    df.owner = df.owner.map(lambda x: str(x).split('*')[0].strip())\n",
    "    df['year']=year\n",
    "    return df\n",
    "\n",
    "dataframes = []\n",
    "for year in [2016,2017]:\n",
    "    un = pd.read_csv('raw/trump-oge278e-exhibit-a-'+str(year)+'-raw.csv',header=None)\n",
    "    un.columns = ['id','company','col2', 'col3', 'title', 'role']\n",
    "    dataframes.append(extractOwnership(un))\n",
    "    \n",
    "entityRelDf = pd.concat(dataframes, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Initial clean up of entity relations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "entityRelDf.columns = ['entityA','ownership','entityB','role','year']\n",
    "entityRelDf.entityA = entityRelDf.entityA.map(lambda x: x.upper())\n",
    "entityRelDf.entityB = entityRelDf.entityB.map(lambda x: x.upper())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combine 2016 & 2017 entities into single dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "entity2016 = pd.concat([entityRelDf[entityRelDf.year==2016].entityA,entityRelDf[entityRelDf.year==2016].entityB]).unique()\n",
    "entity2017 = pd.concat([entityRelDf[entityRelDf.year==2017].entityA,entityRelDf[entityRelDf.year==2017].entityB]).unique()\n",
    "\n",
    "entity2016 =  pd.DataFrame(entity2016,columns=['entity'])\n",
    "entity2016['year']=2016\n",
    "\n",
    "entity2017 =  pd.DataFrame(entity2017,columns=['entity'])\n",
    "entity2017['year']=2017\n",
    "\n",
    "entityDf = pd.concat([entity2016,entity2017])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Add initial org_slug to entities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>entity</th>\n",
       "      <th>year</th>\n",
       "      <th>slug</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>371</th>\n",
       "      <td>TRUMP MARKS PUNTA DEL ESTE LLC</td>\n",
       "      <td>2016</td>\n",
       "      <td>trump-marks-punta-del-este-llc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>OCEAN DEVELOPMENT MEMBER INC</td>\n",
       "      <td>2017</td>\n",
       "      <td>ocean-development-member-inc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>DT BALI HOTEL MANAGER MEMBER CORP</td>\n",
       "      <td>2016</td>\n",
       "      <td>dt-bali-hotel-manager-member-corp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>318</th>\n",
       "      <td>TRUMP MARKS BATUMI LLC</td>\n",
       "      <td>2016</td>\n",
       "      <td>trump-marks-batumi-llc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>316</th>\n",
       "      <td>TRUMP MARKS BAJA CORP</td>\n",
       "      <td>2016</td>\n",
       "      <td>trump-marks-baja-corp</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                entity  year  \\\n",
       "371     TRUMP MARKS PUNTA DEL ESTE LLC  2016   \n",
       "128       OCEAN DEVELOPMENT MEMBER INC  2017   \n",
       "488  DT BALI HOTEL MANAGER MEMBER CORP  2016   \n",
       "318             TRUMP MARKS BATUMI LLC  2016   \n",
       "316              TRUMP MARKS BAJA CORP  2016   \n",
       "\n",
       "                                  slug  \n",
       "371     trump-marks-punta-del-este-llc  \n",
       "128       ocean-development-member-inc  \n",
       "488  dt-bali-hotel-manager-member-corp  \n",
       "318             trump-marks-batumi-llc  \n",
       "316              trump-marks-baja-corp  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1125\n"
     ]
    }
   ],
   "source": [
    "def slugify(s):\n",
    "    s = str(s)\n",
    "    s = re.sub(r'( |,|\\.|:|;)', '-', s)\n",
    "    s = re.sub(r\"(#|&|\\*|\\/|\\\\|'|’|\\(|\\)|\\?|\\!)\", '', s)\n",
    "    s = re.sub(r'-+', '-', s)\n",
    "    s = re.sub(r'-$', '', s)\n",
    "    s = s.lower()\n",
    "    return s\n",
    "\n",
    "entityRelDf['entityASlug'] = entityRelDf.entityA.map(lambda x: slugify(x))\n",
    "entityRelDf['entityBSlug'] = entityRelDf.entityB.map(lambda x: slugify(x))\n",
    "\n",
    "entityDf['slug'] = entityDf.entity.map(slugify)\n",
    "display(entityDf.sample(5))\n",
    "print(len(entityDf))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Standardize slugs & names to match the rest of the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>org_slug</th>\n",
       "      <th>org_name</th>\n",
       "      <th>other_org_names</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>dt-marks-gurgaon-managing-member-corp</td>\n",
       "      <td>DT Marks Gurgaon Managing Member Corp</td>\n",
       "      <td></td>\n",
       "      <td>2015 Part 1;2016 Part 1;2017 Part 1;2015 Exhib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>beach-haven-properties-no-3-inc</td>\n",
       "      <td>Beach Haven Properties No. 3 Inc.</td>\n",
       "      <td></td>\n",
       "      <td>2015 Exhibit A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>dt-marks-qatar-llc</td>\n",
       "      <td>DT Marks Qatar LLC</td>\n",
       "      <td></td>\n",
       "      <td>2015 Part 1;2016 Part 1;2017 Part 1;2015 Exhib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>565</th>\n",
       "      <td>trump-international-development-llc</td>\n",
       "      <td>Trump International Development LLC</td>\n",
       "      <td></td>\n",
       "      <td>2015 Part 1;2016 Part 1;2017 Part 1;2015 Exhib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>thc-qatar-hotel-manager-llc</td>\n",
       "      <td>THC Qatar Hotel Manager LLC</td>\n",
       "      <td></td>\n",
       "      <td>2015 Part 1;2016 Part 1;2017 Part 1;2015 Exhib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>thc-jeddah-hotel-advisor-llc</td>\n",
       "      <td>THC Jeddah Hotel Advisor LLC</td>\n",
       "      <td></td>\n",
       "      <td>2016 Part 1;2017 Part 1;2016 Exhibit A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>trump-international-golf-club-lc-trump-interna...</td>\n",
       "      <td>Trump International Golf Club LC (Trump Intern...</td>\n",
       "      <td>Trump International Golf Club LC;Trump Interna...</td>\n",
       "      <td>2015 Part 1;2016 Part 1;2017 Part 1;2015 Part ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>441</th>\n",
       "      <td>trump-marks-philadelphia-llc</td>\n",
       "      <td>Trump Marks Philadelphia LLC</td>\n",
       "      <td></td>\n",
       "      <td>2015 Part 1;2016 Part 1;2017 Part 1;2015 Exhib...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>dt-lido-hotel-manager-llc</td>\n",
       "      <td>DT Lido Hotel Manager LLC *</td>\n",
       "      <td></td>\n",
       "      <td>2016 Part 1;2017 Part 1;2016 Exhibit A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>jupiter-golf-club-managing-member-corp</td>\n",
       "      <td>Jupiter Golf Club Managing Member Corp</td>\n",
       "      <td></td>\n",
       "      <td>2015 Part 1;2016 Part 1;2017 Part 1;2015 Part ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              org_slug  \\\n",
       "87               dt-marks-gurgaon-managing-member-corp   \n",
       "5                      beach-haven-properties-no-3-inc   \n",
       "98                                  dt-marks-qatar-llc   \n",
       "565                trump-international-development-llc   \n",
       "223                        thc-qatar-hotel-manager-llc   \n",
       "218                       thc-jeddah-hotel-advisor-llc   \n",
       "361  trump-international-golf-club-lc-trump-interna...   \n",
       "441                       trump-marks-philadelphia-llc   \n",
       "74                           dt-lido-hotel-manager-llc   \n",
       "151             jupiter-golf-club-managing-member-corp   \n",
       "\n",
       "                                              org_name  \\\n",
       "87               DT Marks Gurgaon Managing Member Corp   \n",
       "5                    Beach Haven Properties No. 3 Inc.   \n",
       "98                                  DT Marks Qatar LLC   \n",
       "565                Trump International Development LLC   \n",
       "223                        THC Qatar Hotel Manager LLC   \n",
       "218                       THC Jeddah Hotel Advisor LLC   \n",
       "361  Trump International Golf Club LC (Trump Intern...   \n",
       "441                       Trump Marks Philadelphia LLC   \n",
       "74                         DT Lido Hotel Manager LLC *   \n",
       "151             Jupiter Golf Club Managing Member Corp   \n",
       "\n",
       "                                       other_org_names  \\\n",
       "87                                                       \n",
       "5                                                        \n",
       "98                                                       \n",
       "565                                                      \n",
       "223                                                      \n",
       "218                                                      \n",
       "361  Trump International Golf Club LC;Trump Interna...   \n",
       "441                                                      \n",
       "74                                                       \n",
       "151                                                      \n",
       "\n",
       "                                                source  \n",
       "87   2015 Part 1;2016 Part 1;2017 Part 1;2015 Exhib...  \n",
       "5                                       2015 Exhibit A  \n",
       "98   2015 Part 1;2016 Part 1;2017 Part 1;2015 Exhib...  \n",
       "565  2015 Part 1;2016 Part 1;2017 Part 1;2015 Exhib...  \n",
       "223  2015 Part 1;2016 Part 1;2017 Part 1;2015 Exhib...  \n",
       "218             2016 Part 1;2017 Part 1;2016 Exhibit A  \n",
       "361  2015 Part 1;2016 Part 1;2017 Part 1;2015 Part ...  \n",
       "441  2015 Part 1;2016 Part 1;2017 Part 1;2015 Exhib...  \n",
       "74              2016 Part 1;2017 Part 1;2016 Exhibit A  \n",
       "151  2015 Part 1;2016 Part 1;2017 Part 1;2015 Part ...  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "companyCols = ['org_slug','org_name','other_org_names','source']\n",
    "\n",
    "companies = []\n",
    "companies.append(pd.read_csv('data/1_trump-oge278e-part-one-companies.csv', na_values=''))\n",
    "companies.append(pd.read_csv('data/2_trump-oge278e-part-two-companies.csv', na_values=''))\n",
    "companies.append(pd.read_csv('data/3_trump-oge278e-exhibit-a-companies.csv', na_values=''))\n",
    "companies = pd.concat(companies)\n",
    "companies = companies[companyCols].fillna('')\n",
    "companies.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false
   },
   "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>entity</th>\n",
       "      <th>year</th>\n",
       "      <th>slug</th>\n",
       "      <th>org_name</th>\n",
       "      <th>org_slug</th>\n",
       "      <th>match_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40 WALL STREET COMMERCIAL LLC</td>\n",
       "      <td>2017</td>\n",
       "      <td>40-wall-street-commercial-llc</td>\n",
       "      <td>40 Wall Street Commercial LLC</td>\n",
       "      <td>40-wall-street-commercial-llc</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>DJT AEROSPACE LLC</td>\n",
       "      <td>2016</td>\n",
       "      <td>djt-aerospace-llc</td>\n",
       "      <td>DJT Aerospace LLC</td>\n",
       "      <td>djt-aerospace-llc</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>TRUMP INTERNATIONAL DEVELOPMENT MEMBER CORP</td>\n",
       "      <td>2016</td>\n",
       "      <td>trump-international-development-member-corp</td>\n",
       "      <td>Trump International Development Member Corp</td>\n",
       "      <td>trump-international-development-member-corp</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>274</th>\n",
       "      <td>TRUMP DRINKS ISRAEL MEMBER CORP</td>\n",
       "      <td>2017</td>\n",
       "      <td>trump-drinks-israel-member-corp</td>\n",
       "      <td>Trump Drinks Israel Member Corp</td>\n",
       "      <td>trump-drinks-israel-member-corp</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>481</th>\n",
       "      <td>THC CHINA DEVELOPMENT MANAGEMENT CORP</td>\n",
       "      <td>2016</td>\n",
       "      <td>thc-china-development-management-corp</td>\n",
       "      <td>THC China Development Management Corp (fka Tru...</td>\n",
       "      <td>thc-china-development-management-corp</td>\n",
       "      <td>other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>TRUMP MARKS CANOUAN LLC</td>\n",
       "      <td>2017</td>\n",
       "      <td>trump-marks-canouan-llc</td>\n",
       "      <td>Trump Marks Canouan LLC</td>\n",
       "      <td>trump-marks-canouan-llc</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>DT MARKS QATAR MEMBER CORP</td>\n",
       "      <td>2017</td>\n",
       "      <td>dt-marks-qatar-member-corp</td>\n",
       "      <td>DT Marks Qatar Member Corp</td>\n",
       "      <td>dt-marks-qatar-member-corp</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>BEDFORD HILLS CORP</td>\n",
       "      <td>2016</td>\n",
       "      <td>bedford-hills-corp</td>\n",
       "      <td>Bedford Hills Corp</td>\n",
       "      <td>bedford-hills-corp</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>TRUMP MARKS EGYPT CORP</td>\n",
       "      <td>2016</td>\n",
       "      <td>trump-marks-egypt-corp</td>\n",
       "      <td>Trump Marks Egypt Corp</td>\n",
       "      <td>trump-marks-egypt-corp</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>LFB ACQUISITION LLC</td>\n",
       "      <td>2016</td>\n",
       "      <td>lfb-acquisition-llc</td>\n",
       "      <td>LFB Acquisition LLC</td>\n",
       "      <td>lfb-acquisition-llc</td>\n",
       "      <td>match</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          entity  year  \\\n",
       "3                  40 WALL STREET COMMERCIAL LLC  2017   \n",
       "38                             DJT AEROSPACE LLC  2016   \n",
       "293  TRUMP INTERNATIONAL DEVELOPMENT MEMBER CORP  2016   \n",
       "274              TRUMP DRINKS ISRAEL MEMBER CORP  2017   \n",
       "481        THC CHINA DEVELOPMENT MANAGEMENT CORP  2016   \n",
       "324                      TRUMP MARKS CANOUAN LLC  2017   \n",
       "83                    DT MARKS QATAR MEMBER CORP  2017   \n",
       "20                            BEDFORD HILLS CORP  2016   \n",
       "328                       TRUMP MARKS EGYPT CORP  2016   \n",
       "120                          LFB ACQUISITION LLC  2016   \n",
       "\n",
       "                                            slug  \\\n",
       "3                  40-wall-street-commercial-llc   \n",
       "38                             djt-aerospace-llc   \n",
       "293  trump-international-development-member-corp   \n",
       "274              trump-drinks-israel-member-corp   \n",
       "481        thc-china-development-management-corp   \n",
       "324                      trump-marks-canouan-llc   \n",
       "83                    dt-marks-qatar-member-corp   \n",
       "20                            bedford-hills-corp   \n",
       "328                       trump-marks-egypt-corp   \n",
       "120                          lfb-acquisition-llc   \n",
       "\n",
       "                                              org_name  \\\n",
       "3                        40 Wall Street Commercial LLC   \n",
       "38                                   DJT Aerospace LLC   \n",
       "293        Trump International Development Member Corp   \n",
       "274                    Trump Drinks Israel Member Corp   \n",
       "481  THC China Development Management Corp (fka Tru...   \n",
       "324                            Trump Marks Canouan LLC   \n",
       "83                          DT Marks Qatar Member Corp   \n",
       "20                                  Bedford Hills Corp   \n",
       "328                             Trump Marks Egypt Corp   \n",
       "120                                LFB Acquisition LLC   \n",
       "\n",
       "                                        org_slug match_type  \n",
       "3                  40-wall-street-commercial-llc      match  \n",
       "38                             djt-aerospace-llc      match  \n",
       "293  trump-international-development-member-corp      match  \n",
       "274              trump-drinks-israel-member-corp      match  \n",
       "481        thc-china-development-management-corp      other  \n",
       "324                      trump-marks-canouan-llc      match  \n",
       "83                    dt-marks-qatar-member-corp      match  \n",
       "20                            bedford-hills-corp      match  \n",
       "328                       trump-marks-egypt-corp      match  \n",
       "120                          lfb-acquisition-llc      match  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Santardized the slugs within the Entities dataframe\n",
    "companies['other_org_names_slugs'] = companies.other_org_names.map(lambda x: map(slugify, '' if pd.isnull(x) else x.split(';')))\n",
    "\n",
    "match_type = []\n",
    "std_slugs = []\n",
    "std_name = []\n",
    "\n",
    "row_iterator = entityDf.iterrows()\n",
    "for i, row in row_iterator:\n",
    "    match = companies[companies.org_slug == row.slug]\n",
    "    match_other = companies[companies.other_org_names_slugs.map(lambda y:''.join([x for x in y if x == row.slug])) != '']\n",
    "\n",
    "    # if slugs match org_slug from the companies table\n",
    "    if(len(match)>0):\n",
    "        std_name.append(match.org_name.iloc[0])\n",
    "        std_slugs.append(match.org_slug.iloc[0])\n",
    "        match_type.append('match')\n",
    "        \n",
    "    # if is in other_org_names list from the companies table    \n",
    "    elif(len(match_other)==1):\n",
    "        std_name.append(match_other.org_name.iloc[0])\n",
    "        std_slugs.append(match_other.other_org_names_slugs.map(lambda y:''.join([x for x in y if x == row.slug])).iloc[0])\n",
    "        match_type.append('other')\n",
    "\n",
    "    # if not keep slug as org_slug    \n",
    "    else:       \n",
    "        std_name.append(row.entity)\n",
    "        std_slugs.append(row.slug)        \n",
    "        match_type.append('new')\n",
    "\n",
    "\n",
    "entityDf['org_name'] = std_name\n",
    "entityDf['org_slug'] = std_slugs\n",
    "entityDf['match_type'] = match_type\n",
    "\n",
    "display(entityDf.sample(10))\n",
    "\n",
    "match = entityDf[entityDf.match_type == 'match']\n",
    "other = entityDf[entityDf.match_type == 'other']\n",
    "new = entityDf[entityDf.match_type == 'new']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": false
   },
   "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>entityASlug</th>\n",
       "      <th>entityA</th>\n",
       "      <th>ownership</th>\n",
       "      <th>entityB</th>\n",
       "      <th>entityBSlug</th>\n",
       "      <th>role</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1510</th>\n",
       "      <td>dt-tower-i-llc</td>\n",
       "      <td>DT Tower I LLC</td>\n",
       "      <td>1</td>\n",
       "      <td>DT Tower I Member Corp</td>\n",
       "      <td>dt-tower-i-member-corp</td>\n",
       "      <td>Managing Member</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>327</th>\n",
       "      <td>tipperary-realty-corp</td>\n",
       "      <td>Tipperary Realty Corp</td>\n",
       "      <td>100</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "      <td>trump-donald-j</td>\n",
       "      <td>Shareholder</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>437</th>\n",
       "      <td>trump-international-hotels-management-llc</td>\n",
       "      <td>Trump International Hotels Management LLC</td>\n",
       "      <td>0.1</td>\n",
       "      <td>TIHM Member Corp</td>\n",
       "      <td>tihm-member-corp</td>\n",
       "      <td>Managing Member</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>dt-home-marks-international-llc</td>\n",
       "      <td>DT Home Marks International LLC</td>\n",
       "      <td>99</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "      <td>trump-donald-j</td>\n",
       "      <td>Member</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>thc-miami-restaurant-hospitality-member-corp</td>\n",
       "      <td>THC Miami Restaurant Hospitality Member Corp</td>\n",
       "      <td>100</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "      <td>trump-donald-j</td>\n",
       "      <td>Shareholder</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1527</th>\n",
       "      <td>thc-jeddah-hotel-advisor-llc</td>\n",
       "      <td>THC Jeddah Hotel Advisor LLC</td>\n",
       "      <td>99</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "      <td>trump-donald-j</td>\n",
       "      <td>Member</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1494</th>\n",
       "      <td>dt-jeddah-technical-services-manager-member-corp</td>\n",
       "      <td>DT Jeddah Technical Services Manager Member Corp</td>\n",
       "      <td>100</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "      <td>trump-donald-j</td>\n",
       "      <td>Shareholder</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>all-county-building-supply-maintenance-corp</td>\n",
       "      <td>All County Building Supply &amp; Maintenance Corp</td>\n",
       "      <td>25</td>\n",
       "      <td>TRUMP, DONALD J.</td>\n",
       "      <td>trump-donald-j</td>\n",
       "      <td>Shareholder</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>lawrence-towers-apartments-llc</td>\n",
       "      <td>Lawrence Towers Apartments LLC</td>\n",
       "      <td>75</td>\n",
       "      <td>TRUMP FAMILY MEMBERS</td>\n",
       "      <td>trump-family-members</td>\n",
       "      <td>Member</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1066</th>\n",
       "      <td>the-east-61-street-company-lp</td>\n",
       "      <td>East 61 St Company, L.P., The</td>\n",
       "      <td>0.1</td>\n",
       "      <td>Plaza Consulting Corp</td>\n",
       "      <td>plaza-consulting-corp</td>\n",
       "      <td>Partner</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           entityASlug  \\\n",
       "1510                                    dt-tower-i-llc   \n",
       "327                              tipperary-realty-corp   \n",
       "437          trump-international-hotels-management-llc   \n",
       "93                     dt-home-marks-international-llc   \n",
       "269       thc-miami-restaurant-hospitality-member-corp   \n",
       "1527                      thc-jeddah-hotel-advisor-llc   \n",
       "1494  dt-jeddah-technical-services-manager-member-corp   \n",
       "20         all-county-building-supply-maintenance-corp   \n",
       "183                     lawrence-towers-apartments-llc   \n",
       "1066                     the-east-61-street-company-lp   \n",
       "\n",
       "                                               entityA ownership  \\\n",
       "1510                                    DT Tower I LLC         1   \n",
       "327                              Tipperary Realty Corp       100   \n",
       "437          Trump International Hotels Management LLC       0.1   \n",
       "93                     DT Home Marks International LLC        99   \n",
       "269       THC Miami Restaurant Hospitality Member Corp       100   \n",
       "1527                      THC Jeddah Hotel Advisor LLC        99   \n",
       "1494  DT Jeddah Technical Services Manager Member Corp       100   \n",
       "20       All County Building Supply & Maintenance Corp        25   \n",
       "183                     Lawrence Towers Apartments LLC        75   \n",
       "1066                     East 61 St Company, L.P., The       0.1   \n",
       "\n",
       "                     entityB             entityBSlug             role  year  \n",
       "1510  DT Tower I Member Corp  dt-tower-i-member-corp  Managing Member  2017  \n",
       "327         TRUMP, DONALD J.          trump-donald-j      Shareholder  2016  \n",
       "437         TIHM Member Corp        tihm-member-corp  Managing Member  2016  \n",
       "93          TRUMP, DONALD J.          trump-donald-j           Member  2016  \n",
       "269         TRUMP, DONALD J.          trump-donald-j      Shareholder  2016  \n",
       "1527        TRUMP, DONALD J.          trump-donald-j           Member  2017  \n",
       "1494        TRUMP, DONALD J.          trump-donald-j      Shareholder  2017  \n",
       "20          TRUMP, DONALD J.          trump-donald-j      Shareholder  2016  \n",
       "183     TRUMP FAMILY MEMBERS    trump-family-members           Member  2016  \n",
       "1066   Plaza Consulting Corp   plaza-consulting-corp          Partner  2017  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Santardized the slugs within the Entity Relations dataframe\n",
    "aDf = entityRelDf.copy()\n",
    "aDf.columns = ['entityA','ownership','entityB','role','year','slug','entityBSlug']\n",
    "\n",
    "bDf = entityRelDf.copy()\n",
    "bDf.columns = ['entityA','ownership','entityB','role','year','entityASlug','slug']\n",
    "\n",
    "mergeA = pd.merge(aDf,entityDf[['year','slug','org_name','org_slug']],on=['year','slug'],how='left')\n",
    "mergeB = pd.merge(bDf,entityDf[['year','slug','org_name','org_slug']],on=['year','slug'],how='left')\n",
    "\n",
    "mergeA['entityA'] = mergeA.org_name\n",
    "mergeA['entityASlug'] = mergeA.org_slug\n",
    "\n",
    "mergeB['entityB'] = mergeB.org_name\n",
    "mergeB['entityBSlug'] = mergeB.org_slug\n",
    "\n",
    "mergeA = mergeA[['entityASlug','entityA','ownership','entityB','entityBSlug','role','year']]\n",
    "mergeB = mergeB[['entityASlug','entityA','ownership','entityB','entityBSlug','role','year']]\n",
    "\n",
    "mergeA = mergeA.drop(['entityB','entityBSlug','role','year'],axis=1)\n",
    "mergeB = mergeB.drop(['entityASlug','entityA','ownership'],axis=1)\n",
    "\n",
    "entityRelOut = mergeA.copy()\n",
    "entityRelOut['entityB'] = mergeB.entityB\n",
    "entityRelOut['entityBSlug'] = mergeB.entityBSlug\n",
    "entityRelOut['role'] = mergeB.role\n",
    "entityRelOut['year'] = mergeB.year\n",
    "\n",
    "entityRelOut.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Clean up & save out Entities & Entity Relations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>year</th>\n",
       "      <th>org_slug</th>\n",
       "      <th>org_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>2016</td>\n",
       "      <td>toronto-development-llc</td>\n",
       "      <td>Toronto Development LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>2017</td>\n",
       "      <td>hwa-555-owners-llc</td>\n",
       "      <td>HWA 555 Owners LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>369</th>\n",
       "      <td>2017</td>\n",
       "      <td>trump-marks-puerto-rico-i-member-corp</td>\n",
       "      <td>Trump Marks Puerto Rico I Member Corp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>2017</td>\n",
       "      <td>dt-tower-gurgaon-llc</td>\n",
       "      <td>DT Tower Gurgaon LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>2017</td>\n",
       "      <td>hudson-waterfront-associates-lp</td>\n",
       "      <td>Hudson Waterfront Associates, L.P.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>386</th>\n",
       "      <td>2016</td>\n",
       "      <td>trump-marks-toronto-lp</td>\n",
       "      <td>Trump Marks Toronto LP (Formally Trump Toronto...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>2016</td>\n",
       "      <td>midland-associates</td>\n",
       "      <td>Midland Associates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>2016</td>\n",
       "      <td>dt-dubai-golf-manager-llc</td>\n",
       "      <td>DT Dubai Golf Manager LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>344</th>\n",
       "      <td>2016</td>\n",
       "      <td>trump-marks-llc</td>\n",
       "      <td>Trump Marks LLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>2016</td>\n",
       "      <td>trump-marks-products-member-corp</td>\n",
       "      <td>Trump Marks Products Member Corp</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     year                               org_slug  \\\n",
       "228  2016                toronto-development-llc   \n",
       "476  2017                     hwa-555-owners-llc   \n",
       "369  2017  trump-marks-puerto-rico-i-member-corp   \n",
       "90   2017                   dt-tower-gurgaon-llc   \n",
       "114  2017        hudson-waterfront-associates-lp   \n",
       "386  2016                 trump-marks-toronto-lp   \n",
       "125  2016                     midland-associates   \n",
       "57   2016              dt-dubai-golf-manager-llc   \n",
       "344  2016                        trump-marks-llc   \n",
       "366  2016       trump-marks-products-member-corp   \n",
       "\n",
       "                                              org_name  \n",
       "228                            Toronto Development LLC  \n",
       "476                                 HWA 555 Owners LLC  \n",
       "369              Trump Marks Puerto Rico I Member Corp  \n",
       "90                                DT Tower Gurgaon LLC  \n",
       "114                 Hudson Waterfront Associates, L.P.  \n",
       "386  Trump Marks Toronto LP (Formally Trump Toronto...  \n",
       "125                                 Midland Associates  \n",
       "57                           DT Dubai Golf Manager LLC  \n",
       "344                                    Trump Marks LLC  \n",
       "366                   Trump Marks Products Member Corp  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "entityDfOut = entityDf.copy().drop(['entity','slug','match_type'],axis=1,inplace=True)\n",
    "entityDfOut = entityDf[['year','org_slug','org_name']]\n",
    "display(entityDfOut.sample(10))\n",
    "\n",
    "entityDfOut.to_csv('data/5_trump-oge278e-exhibit-a-entities.csv',index=False)\n",
    "entityRelOut.to_csv('data/6_trump-oge278e-exhibit-a-entity-relations.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}