Fund Objective.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from IPython.display import display\n",
    "from fuzzywuzzy import fuzz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>LipperID</th>\n",
       "      <th>Objective</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>65006437</td>\n",
       "      <td>The fund is an equity fund geared towards achi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>65006438</td>\n",
       "      <td>The fund is an equity fund geared towards achi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>65006406</td>\n",
       "      <td>The equity fund aims to achieve long-term capi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>65006407</td>\n",
       "      <td>The equity fund aims to achieve long-term capi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>65012146</td>\n",
       "      <td>The equity fund aims to achieve long-term capi...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   LipperID                                          Objective\n",
       "0  65006437  The fund is an equity fund geared towards achi...\n",
       "1  65006438  The fund is an equity fund geared towards achi...\n",
       "2  65006406  The equity fund aims to achieve long-term capi...\n",
       "3  65006407  The equity fund aims to achieve long-term capi...\n",
       "4  65012146  The equity fund aims to achieve long-term capi..."
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gfd1 = pd.read_csv(\"...\\\\GFD-1\\\\FundObjective.txt\", sep=\"\\t\", encoding='cp1252')\n",
    "gfd1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>LipperID</th>\n",
       "      <th>Objective</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60060059</td>\n",
       "      <td>The Goldman Sachs Global High Yield Portfolio ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>60060073</td>\n",
       "      <td>The Portfolio's investment objective is high t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>60067871</td>\n",
       "      <td>The fund is a mixed fund and aims to achieve m...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60068281</td>\n",
       "      <td>The investment objective of the fund is to gen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60059801</td>\n",
       "      <td>The Goldman Sachs Global High Yield Portfolio ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   LipperID                                          Objective\n",
       "0  60060059  The Goldman Sachs Global High Yield Portfolio ...\n",
       "1  60060073  The Portfolio's investment objective is high t...\n",
       "2  60067871  The fund is a mixed fund and aims to achieve m...\n",
       "3  60068281  The investment objective of the fund is to gen...\n",
       "4  60059801  The Goldman Sachs Global High Yield Portfolio ..."
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cfg = pd.read_csv(\"...\\\\CFG\\\\FundObjective.txt\", sep=\"\\t\")\n",
    "cfg_cols = cfg.columns\n",
    "cfg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cfg.equals(gfd1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Missing in GFD-1: 122\n",
      "Missing in CFG: 0\n"
     ]
    }
   ],
   "source": [
    "print(\"Missing in GFD-1: \" + str(len(cfg[~cfg[\"LipperID\"].isin(gfd1[\"LipperID\"])][\"LipperID\"])))\n",
    "missing_in_gfd1_df = cfg[~cfg[\"LipperID\"].isin(gfd1[\"LipperID\"])]\n",
    "missing_in_gfd1 = cfg[~cfg[\"LipperID\"].isin(gfd1[\"LipperID\"])][\"LipperID\"]\n",
    "\n",
    "\n",
    "print(\"Missing in CFG: \" + str(len(gfd1[~gfd1[\"LipperID\"].isin(cfg[\"LipperID\"])][\"LipperID\"])))\n",
    "missing_in_cfg_df = gfd1[~gfd1[\"LipperID\"].isin(cfg[\"LipperID\"])]\n",
    "missing_in_cfg = gfd1[~gfd1[\"LipperID\"].isin(cfg[\"LipperID\"])][\"LipperID\"]\n",
    "\n",
    "universe_diffs = pd.concat([missing_in_cfg, missing_in_gfd1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>LipperID</th>\n",
       "      <th>Objective_CFG</th>\n",
       "      <th>Objective_GFD-1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>29822</th>\n",
       "      <td>68243447</td>\n",
       "      <td>The Portfolio's investment objective is long t...</td>\n",
       "      <td>The Portfolio's investment objective is long t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5317</th>\n",
       "      <td>65044759</td>\n",
       "      <td>The fund invests at least 70% of its assets in...</td>\n",
       "      <td>The fund invests at least 70% of its assets in...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7663</th>\n",
       "      <td>65124680</td>\n",
       "      <td>The investment objective of the Fund is to ach...</td>\n",
       "      <td>The investment objective of the Fund is to ach...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23462</th>\n",
       "      <td>68204216</td>\n",
       "      <td>The sub-fund seeks capital growth by investing...</td>\n",
       "      <td>The sub-fund seeks capital growth by investing...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4343</th>\n",
       "      <td>68324758</td>\n",
       "      <td>The objective of the fund is to provide a comb...</td>\n",
       "      <td>The objective of the fund is to provide a comb...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       LipperID                                      Objective_CFG  \\\n",
       "29822  68243447  The Portfolio's investment objective is long t...   \n",
       "5317   65044759  The fund invests at least 70% of its assets in...   \n",
       "7663   65124680  The investment objective of the Fund is to ach...   \n",
       "23462  68204216  The sub-fund seeks capital growth by investing...   \n",
       "4343   68324758  The objective of the fund is to provide a comb...   \n",
       "\n",
       "                                         Objective_GFD-1  \n",
       "29822  The Portfolio's investment objective is long t...  \n",
       "5317   The fund invests at least 70% of its assets in...  \n",
       "7663   The investment objective of the Fund is to ach...  \n",
       "23462  The sub-fund seeks capital growth by investing...  \n",
       "4343   The objective of the fund is to provide a comb...  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "on=[\"LipperID\"]\n",
    "df = cfg.merge(gfd1, how='outer', on=on, suffixes=(\"_CFG\", \"_GFD-1\"))\n",
    "df = df[~df[\"LipperID\"].isin(universe_diffs)]\n",
    "df = df.fillna(\"-\")\n",
    "del cfg\n",
    "del gfd1\n",
    "df.sample(n=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Objective\n",
      "\n"
     ]
    },
    {
     "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>LipperID</th>\n",
       "      <th>Objective_CFG</th>\n",
       "      <th>Objective_GFD-1</th>\n",
       "      <th>Check</th>\n",
       "      <th>Ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24983</th>\n",
       "      <td>68286205</td>\n",
       "      <td>The fund will invest most of its assets in equ...</td>\n",
       "      <td>The fund will invest most of its assets in equ...</td>\n",
       "      <td>Match</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35624</th>\n",
       "      <td>68307995</td>\n",
       "      <td>The fund aims to achieve long-term returns thr...</td>\n",
       "      <td>&lt;Not Available&gt;</td>\n",
       "      <td>Difference</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36406</th>\n",
       "      <td>68294029</td>\n",
       "      <td>The fund aims to deliver the return of the Gol...</td>\n",
       "      <td>The fund aims to deliver the return of the Gol...</td>\n",
       "      <td>Match</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15445</th>\n",
       "      <td>68238600</td>\n",
       "      <td>The Sub-Fund aims to provide a positive absolu...</td>\n",
       "      <td>The Sub-Fund aims to provide a positive absolu...</td>\n",
       "      <td>Match</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38690</th>\n",
       "      <td>68145152</td>\n",
       "      <td>The fund aims to achieve long-term capital gro...</td>\n",
       "      <td>The fund aims to achieve long-term capital gro...</td>\n",
       "      <td>Match</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       LipperID                                      Objective_CFG  \\\n",
       "24983  68286205  The fund will invest most of its assets in equ...   \n",
       "35624  68307995  The fund aims to achieve long-term returns thr...   \n",
       "36406  68294029  The fund aims to deliver the return of the Gol...   \n",
       "15445  68238600  The Sub-Fund aims to provide a positive absolu...   \n",
       "38690  68145152  The fund aims to achieve long-term capital gro...   \n",
       "\n",
       "                                         Objective_GFD-1       Check  Ratio  \n",
       "24983  The fund will invest most of its assets in equ...       Match    100  \n",
       "35624                                    <Not Available>  Difference      2  \n",
       "36406  The fund aims to deliver the return of the Gol...       Match    100  \n",
       "15445  The Sub-Fund aims to provide a positive absolu...       Match    100  \n",
       "38690  The fund aims to achieve long-term capital gro...       Match    100  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "writer = pd.ExcelWriter(\"Fund Objective Checks.xlsx\", engine='xlsxwriter')\n",
    "df_dict = {}\n",
    "missing_in_gfd1_df.to_excel(writer,\"Additional in new feed\", index=False)\n",
    "missing_in_cfg_df.to_excel(writer,\"Missing in new feed\", index=False)\n",
    "for col in cfg_cols[len(on):]:\n",
    "    print(col+\"\\n\")\n",
    "    check=[]\n",
    "    ratio = []\n",
    "    columns = []\n",
    "    columns = on + [col+\"_CFG\", col+\"_GFD-1\"]\n",
    "    #print(columns)\n",
    "    check_df = df.loc[:, columns]\n",
    "    for n in range(df.shape[0]):\n",
    "        ratio.append(fuzz.ratio(str(check_df.iloc[n, -2]),str(check_df.iloc[n, -1])))\n",
    "        if str(check_df.iloc[n, -2]) == str(check_df.iloc[n, -1]):\n",
    "            check.append(\"Match\")\n",
    "        elif str(check_df.iloc[n, -2]) == \"-\":\n",
    "            check.append(\"Data not in CFG\")\n",
    "        elif str(check_df.iloc[n, -1]) == \"-\":\n",
    "            check.append(\"Data not in GFD-1\")\n",
    "        else:\n",
    "            check.append(\"Difference\")\n",
    "    check_df[\"Check\"] = np.array(check)\n",
    "    check_df[\"Ratio\"] = np.array(ratio)\n",
    "    check_df.drop_duplicates(inplace=True)\n",
    "    check_df.to_excel(writer,'%s' % col, index=False)\n",
    "    check_df[check_df.Check != \"Match\"].to_excel(writer,'%s Differences' % col, index=False)\n",
    "    display(check_df.sample(n=5))\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}