1.normalizing_edges.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas\n",
    "\n",
    "N_ROWS = 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# I. Normalizing Edges:\n",
    "Edges represent links or relationships. They are the connections between the entities, officers, intermediaries and address present in the dataset. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_id</th>\n",
       "      <th>type</th>\n",
       "      <th>end_id</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000035</td>\n",
       "      <td>registered_address</td>\n",
       "      <td>14095990</td>\n",
       "      <td>registered address</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000044</td>\n",
       "      <td>registered_address</td>\n",
       "      <td>14091035</td>\n",
       "      <td>registered address</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   start_id                type    end_id                text\n",
       "0  10000035  registered_address  14095990  registered address\n",
       "1  10000044  registered_address  14091035  registered address"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connections = pandas.read_csv('data/raw/edges.csv', usecols=[0, 1, 2, 3], skiprows=1,\n",
    "                            names=['start_id', 'type', 'end_id', 'text'])\n",
    "connections.head(N_ROWS)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Normalizing relationship types:\n",
    "Types and counts of the 'relationships':"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "type\n",
       "intermediary_of       213634\n",
       "officer_of            309363\n",
       "registered_address    151105\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connections.groupby('type').size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sanitizing the types to better fit the adjacent data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "type\n",
       "address         151105\n",
       "intermediary    213634\n",
       "officer         309363\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def normalize_type(txt):\n",
    "    if '_of' in txt:\n",
    "        return txt.replace('_of', '')\n",
    "    else:\n",
    "        return txt.replace('registered_', '')\n",
    "\n",
    "connections.type = connections.type.apply(normalize_type)\n",
    "connections.groupby('type').size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Normalizing the relationship descriptions:\n",
    "The text column has a lot of outliers and the address' description is a descrepency."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "text\n",
       "shareholder of                                  293961\n",
       "intermediary of                                 213634\n",
       "registered address                              151105\n",
       "beneficiary of                                   15150\n",
       "beneficial owner of                                 79\n",
       "director of                                         48\n",
       "power of attorney of                                29\n",
       "owner, director and shareholder of                  28\n",
       "owner of                                            16\n",
       "beneficiary, shareholder and director of             6\n",
       "director / shareholder of                            6\n",
       "director / beneficial owner of                       4\n",
       "sole shareholder of                                  4\n",
       "connected of                                         3\n",
       "secretary of                                         3\n",
       "member of foundation council of                      3\n",
       "principal beneficiary of                             3\n",
       "director (rami makhlouf) of                          2\n",
       "director / shareholder / beneficial owner of         2\n",
       "sole signatory of                                    2\n",
       "signatory of                                         2\n",
       "grantee of a mortgage of                             1\n",
       "first beneficiary of                                 1\n",
       "member / shareholder of                              1\n",
       "director and shareholder of                          1\n",
       "sole signatory / beneficial owner of                 1\n",
       "power of attorney / shareholder of                   1\n",
       "president - director of                              1\n",
       "president and director of                            1\n",
       "president of                                         1\n",
       "protector of                                         1\n",
       "shareholder (through julex foundation) of            1\n",
       "authorized signatory of                              1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connections.groupby('text').size().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Normalizing the type 'address' to read \"start_id is address of end_id\" just like the other types. We thus swap start_id and end_id and update the description."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_id</th>\n",
       "      <th>type</th>\n",
       "      <th>end_id</th>\n",
       "      <th>text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14095990</td>\n",
       "      <td>address</td>\n",
       "      <td>10000035</td>\n",
       "      <td>address of</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14091035</td>\n",
       "      <td>address</td>\n",
       "      <td>10000044</td>\n",
       "      <td>address of</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   start_id     type    end_id        text\n",
       "0  14095990  address  10000035  address of\n",
       "1  14091035  address  10000044  address of"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connections['temp'] = connections.start_id.copy()\n",
    "selector = connections.type == 'address'\n",
    "# swap start_id for end_id \n",
    "connections.loc[selector, 'start_id'] = connections.loc[selector, 'end_id']\n",
    "# swap end_id for temp\n",
    "connections.loc[selector, 'end_id'] = connections.loc[selector, 'temp']\n",
    "del connections['temp']\n",
    "\n",
    "connections.loc[connections.type == 'address', 'text'] = 'address of'\n",
    "\n",
    "connections.head(N_ROWS)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some descriptions like \"director / shareholder / beneficial owner of\"  descrbes three different relationships, we expand them into different rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "text\n",
       "shareholder of                               293961\n",
       "intermediary of                              213634\n",
       "address of                                   151105\n",
       "beneficiary of                                15150\n",
       "beneficial owner of                              79\n",
       "director of                                      48\n",
       "power of attorney of                             29\n",
       "owner                                            28\n",
       "owner of                                         16\n",
       "director                                         12\n",
       "beneficiary                                       6\n",
       "sole shareholder of                               4\n",
       "principal beneficiary of                          3\n",
       "secretary of                                      3\n",
       "connected of                                      3\n",
       "member of foundation council of                   3\n",
       "signatory of                                      2\n",
       "director (rami makhlouf) of                       2\n",
       "sole signatory of                                 2\n",
       "president of                                      1\n",
       "first beneficiary of                              1\n",
       "shareholder (through julex foundation) of         1\n",
       "member                                            1\n",
       "sole signatory                                    1\n",
       "director                                          1\n",
       "power of attorney                                 1\n",
       "protector of                                      1\n",
       "president                                         1\n",
       "president                                         1\n",
       "authorized signatory of                           1\n",
       "grantee of a mortgage of                          1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def normalize_separator(txt):\n",
    "    for ch in [',', '-', ' and']:\n",
    "        if ch in txt:\n",
    "            txt = txt.replace(ch, '/')\n",
    "    return txt\n",
    "\n",
    "connections.text = connections['text'].map(normalize_separator)\n",
    "connections.text = connections['text'].str.split('/', expand=True)\n",
    "\n",
    "connections.groupby('text').size().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We futher normalise the descriptions by enforcing 'of' and omiting unwanted details, these details could be moved to the \"note\" column if needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "text\n",
       "shareholder of         293966\n",
       "intermediary of        213634\n",
       "address of             151105\n",
       "beneficiary of          15160\n",
       "beneficial owner of        79\n",
       "director of                63\n",
       "owner of                   44\n",
       "attorney of                30\n",
       "signatory of                6\n",
       "member of                   4\n",
       "secretary of                3\n",
       "president of                3\n",
       "connected of                3\n",
       "protector of                1\n",
       "grantee of                  1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def enforce_description(txt):\n",
    "    to_del = ['first', 'sole', 'principal', 'authorized', 'power of ', 'of a mortgage ', \n",
    "            'of foundation council of', '(rami makhlouf) ', '(through julex foundation)']\n",
    "    for word in to_del:\n",
    "        if word in txt:\n",
    "            txt = txt.replace(word, '')\n",
    "    if 'of' not in txt:\n",
    "        txt += ' of'\n",
    "    return ' '.join(txt.split())\n",
    "\n",
    "connections.text = connections['text'].map(enforce_description)\n",
    "connections.groupby('text').size().sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "NOTE: There are two types of ownerships: legal owners, whom legaly own the property, and beneficiary owners, whom enjoy the ownership while not legaly owning the property."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "connections.to_csv('data/processing/edges.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "file_extension": ".py",
  "kernelspec": {
   "display_name": "Python 3.7.5 64-bit",
   "language": "python",
   "name": "python37564bit72ba89a531ef4df18e1af0f065f1e4d6"
  },
  "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.7.5"
  },
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pygments_lexer": "ipython3",
  "version": 3
 },
 "nbformat": 4,
 "nbformat_minor": 2
}