Assignment_01_Sajid.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(r'D:\\DS-ICG\\kiwisaver_sample_wk9.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task 1: Data Inspection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Name</th>\n",
       "      <th>Firm Name</th>\n",
       "      <th>Branding Name</th>\n",
       "      <th>Holdings</th>\n",
       "      <th>Global Category</th>\n",
       "      <th>Morningstar Rating</th>\n",
       "      <th>Domicile</th>\n",
       "      <th>Fund Legal Structure</th>\n",
       "      <th>Equity Style</th>\n",
       "      <th>Inception Date</th>\n",
       "      <th>Manager Name</th>\n",
       "      <th>Manager Tenure</th>\n",
       "      <th>Net Assets Date</th>\n",
       "      <th>Net Assets Size</th>\n",
       "      <th>Net Assets Share Class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AMP IL no E Fee NZ Share (RN)</td>\n",
       "      <td>AMP Group</td>\n",
       "      <td>AMP</td>\n",
       "      <td>59</td>\n",
       "      <td>New Zealand Equity</td>\n",
       "      <td>3.0</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Mid Growth</td>\n",
       "      <td>3/02/1993</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>135202.500</td>\n",
       "      <td>81121.500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMP KiwiSaver AMP Income Generator</td>\n",
       "      <td>AMP Group</td>\n",
       "      <td>AMP</td>\n",
       "      <td>689</td>\n",
       "      <td>Moderate Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Mid Blend</td>\n",
       "      <td>28/07/2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>861.923</td>\n",
       "      <td>517.154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AMP KiwiSaver ANZ Balanced Plus</td>\n",
       "      <td>AMP Group</td>\n",
       "      <td>AMP</td>\n",
       "      <td>1544</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>3.0</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>146665.955</td>\n",
       "      <td>87999.573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AMP Prem PSS OnePath NZ Shares</td>\n",
       "      <td>AMP Group</td>\n",
       "      <td>AMP</td>\n",
       "      <td>125</td>\n",
       "      <td>Australia &amp; New Zealand Equity</td>\n",
       "      <td>3.0</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Mid Growth</td>\n",
       "      <td>9/08/2004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>3176.420</td>\n",
       "      <td>1905.852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ANZ Default KiwiSaver Scheme Cnsrv(Dflt)</td>\n",
       "      <td>ANZ New Zealand Investments Limited</td>\n",
       "      <td>ANZ</td>\n",
       "      <td>1024</td>\n",
       "      <td>Cautious Allocation</td>\n",
       "      <td>4.0</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Mid Growth</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>Stuart Millar</td>\n",
       "      <td>9.42</td>\n",
       "      <td>15/03/2017</td>\n",
       "      <td>705818.111</td>\n",
       "      <td>423490.867</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       Name  \\\n",
       "0             AMP IL no E Fee NZ Share (RN)   \n",
       "1        AMP KiwiSaver AMP Income Generator   \n",
       "2           AMP KiwiSaver ANZ Balanced Plus   \n",
       "3            AMP Prem PSS OnePath NZ Shares   \n",
       "4  ANZ Default KiwiSaver Scheme Cnsrv(Dflt)   \n",
       "\n",
       "                             Firm Name Branding Name  Holdings  \\\n",
       "0                            AMP Group           AMP        59   \n",
       "1                            AMP Group           AMP       689   \n",
       "2                            AMP Group           AMP      1544   \n",
       "3                            AMP Group           AMP       125   \n",
       "4  ANZ New Zealand Investments Limited           ANZ      1024   \n",
       "\n",
       "                  Global Category  Morningstar Rating     Domicile  \\\n",
       "0              New Zealand Equity                 3.0  New Zealand   \n",
       "1             Moderate Allocation                 NaN  New Zealand   \n",
       "2           Aggressive Allocation                 3.0  New Zealand   \n",
       "3  Australia & New Zealand Equity                 3.0  New Zealand   \n",
       "4             Cautious Allocation                 4.0  New Zealand   \n",
       "\n",
       "            Fund Legal Structure  Equity Style Inception Date   Manager Name  \\\n",
       "0  Open Ended Investment Company    Mid Growth      3/02/1993            NaN   \n",
       "1  Open Ended Investment Company     Mid Blend     28/07/2016            NaN   \n",
       "2  Open Ended Investment Company  Large Growth      1/10/2007            NaN   \n",
       "3  Open Ended Investment Company    Mid Growth      9/08/2004            NaN   \n",
       "4  Open Ended Investment Company    Mid Growth      1/10/2007  Stuart Millar   \n",
       "\n",
       "   Manager Tenure Net Assets Date  Net Assets Size  Net Assets Share Class  \n",
       "0             NaN             NaN       135202.500               81121.500  \n",
       "1             NaN      28/02/2017          861.923                 517.154  \n",
       "2             NaN      28/02/2017       146665.955               87999.573  \n",
       "3             NaN      28/02/2017         3176.420                1905.852  \n",
       "4            9.42      15/03/2017       705818.111              423490.867  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 76 entries, 0 to 75\n",
      "Data columns (total 15 columns):\n",
      "Name                      76 non-null object\n",
      "Firm Name                 76 non-null object\n",
      "Branding Name             76 non-null object\n",
      "Holdings                  76 non-null int64\n",
      "Global Category           76 non-null object\n",
      "Morningstar Rating        40 non-null float64\n",
      "Domicile                  76 non-null object\n",
      "Fund Legal Structure      76 non-null object\n",
      "Equity Style              76 non-null object\n",
      "Inception Date            76 non-null object\n",
      "Manager Name              33 non-null object\n",
      "Manager Tenure            33 non-null float64\n",
      "Net Assets Date           75 non-null object\n",
      "Net Assets Size           76 non-null float64\n",
      "Net Assets Share Class    76 non-null float64\n",
      "dtypes: float64(4), int64(1), object(10)\n",
      "memory usage: 9.0+ KB\n"
     ]
    }
   ],
   "source": [
    "data.info()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Problematic Columns: \n",
    "Following columns require some pre-processing (A fair amount of records have Null values for these columns):\n",
    "\n",
    "Morningstar Rating        40 non-null float64\n",
    "Manager Name              33 non-null object\n",
    "Manager Tenure            33 non-null float64\n",
    "Net Assets Date           75 non-null object\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task 2: Imputation of the missing data (Columns mentioned above)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(data['Morningstar Rating'].mean())\n",
    "print(data['Morningstar Rating'].mode())\n",
    "print(data['Morningstar Rating'].median())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    40.000000\n",
       "mean      3.725000\n",
       "std       0.905468\n",
       "min       1.000000\n",
       "25%       3.000000\n",
       "50%       4.000000\n",
       "75%       4.000000\n",
       "max       5.000000\n",
       "Name: Morningstar Rating, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Morningstar Rating'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Name</th>\n",
       "      <th>Firm Name</th>\n",
       "      <th>Branding Name</th>\n",
       "      <th>Holdings</th>\n",
       "      <th>Global Category</th>\n",
       "      <th>Morningstar Rating</th>\n",
       "      <th>Domicile</th>\n",
       "      <th>Fund Legal Structure</th>\n",
       "      <th>Equity Style</th>\n",
       "      <th>Inception Date</th>\n",
       "      <th>Manager Name</th>\n",
       "      <th>Manager Tenure</th>\n",
       "      <th>Net Assets Date</th>\n",
       "      <th>Net Assets Size</th>\n",
       "      <th>Net Assets Share Class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMP KiwiSaver AMP Income Generator</td>\n",
       "      <td>AMP Group</td>\n",
       "      <td>AMP</td>\n",
       "      <td>689</td>\n",
       "      <td>Moderate Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Mid Blend</td>\n",
       "      <td>28/07/2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>861.923</td>\n",
       "      <td>517.154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>BNZ KiwiSaver First Home Buyer Fund</td>\n",
       "      <td>BNZ Investment Services Limited</td>\n",
       "      <td>Bank of New Zealand</td>\n",
       "      <td>2456</td>\n",
       "      <td>Cautious Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>17/09/2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14/03/2017</td>\n",
       "      <td>27564.969</td>\n",
       "      <td>16538.981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Booster KiwiSaver Capital Guaranteed</td>\n",
       "      <td>Booster Financial Services Limited</td>\n",
       "      <td>Grosvenor</td>\n",
       "      <td>151</td>\n",
       "      <td>Miscellaneous</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>1/07/2014</td>\n",
       "      <td>David Beattie</td>\n",
       "      <td>2.67</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>32777.503</td>\n",
       "      <td>19666.502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Booster KiwiSaver Default Saver</td>\n",
       "      <td>Booster Financial Services Limited</td>\n",
       "      <td>Grosvenor</td>\n",
       "      <td>159</td>\n",
       "      <td>Cautious Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>1/07/2014</td>\n",
       "      <td>David Beattie;Nic Craven;</td>\n",
       "      <td>2.67</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>23651.194</td>\n",
       "      <td>14190.716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Booster KiwiSaver Socially Rsp Inv Bal</td>\n",
       "      <td>Booster Financial Services Limited</td>\n",
       "      <td>Grosvenor</td>\n",
       "      <td>147</td>\n",
       "      <td>Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>1/07/2014</td>\n",
       "      <td>David Beattie</td>\n",
       "      <td>2.67</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>18747.062</td>\n",
       "      <td>11248.237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>FC KiwiSaver Scheme's Active Balanced</td>\n",
       "      <td>ASB Group Investments Limited</td>\n",
       "      <td>ASB</td>\n",
       "      <td>1237</td>\n",
       "      <td>Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/12/2014</td>\n",
       "      <td>7094.312</td>\n",
       "      <td>4256.587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>FC KiwiSaver Scheme's Active Cnsrv</td>\n",
       "      <td>ASB Group Investments Limited</td>\n",
       "      <td>ASB</td>\n",
       "      <td>1212</td>\n",
       "      <td>Moderate Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/12/2014</td>\n",
       "      <td>1794.037</td>\n",
       "      <td>1076.422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>FC KiwiSaver Scheme's Active Growth</td>\n",
       "      <td>ASB Group Investments Limited</td>\n",
       "      <td>ASB</td>\n",
       "      <td>1218</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/12/2014</td>\n",
       "      <td>8047.739</td>\n",
       "      <td>4828.643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Fidelity KiwiSaver-Aggressive Kiwi Fund</td>\n",
       "      <td>Booster Financial Services Limited</td>\n",
       "      <td>Grosvenor</td>\n",
       "      <td>76</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/10/2014</td>\n",
       "      <td>15280.369</td>\n",
       "      <td>9168.221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Fidelity KiwiSaver-Balanced Kiwi Fund</td>\n",
       "      <td>Booster Financial Services Limited</td>\n",
       "      <td>Grosvenor</td>\n",
       "      <td>75</td>\n",
       "      <td>Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/10/2014</td>\n",
       "      <td>94957.184</td>\n",
       "      <td>56974.310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Fidelity KiwiSaver-Ethical Kiwi</td>\n",
       "      <td>Booster Financial Services Limited</td>\n",
       "      <td>Grosvenor</td>\n",
       "      <td>113</td>\n",
       "      <td>Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>14/05/2008</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/10/2014</td>\n",
       "      <td>10302.667</td>\n",
       "      <td>6181.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Fidelity KiwiSaver-Growth Kiwi Fund</td>\n",
       "      <td>Booster Financial Services Limited</td>\n",
       "      <td>Grosvenor</td>\n",
       "      <td>75</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/10/2014</td>\n",
       "      <td>42618.096</td>\n",
       "      <td>25570.858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Forsyth Barr KiwiSaver Balanced Port</td>\n",
       "      <td>Forsyth Barr Limited</td>\n",
       "      <td>Forsyth Barr</td>\n",
       "      <td>153</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>26/06/2008</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29/09/2016</td>\n",
       "      <td>12116.528</td>\n",
       "      <td>7269.917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Forsyth Barr KiwiSaver Growth Portfolio</td>\n",
       "      <td>Forsyth Barr Limited</td>\n",
       "      <td>Forsyth Barr</td>\n",
       "      <td>153</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>26/06/2008</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29/09/2016</td>\n",
       "      <td>10738.997</td>\n",
       "      <td>6443.398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Mercer KiwiSaver Growth</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1602</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>18/02/2015</td>\n",
       "      <td>Phillip Houghton-Brown</td>\n",
       "      <td>2.08</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>29364.475</td>\n",
       "      <td>17618.685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Mercer KiwiSaver Moderate</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1364</td>\n",
       "      <td>Moderate Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>18/02/2015</td>\n",
       "      <td>Phillip Houghton-Brown</td>\n",
       "      <td>2.08</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>38324.385</td>\n",
       "      <td>22994.631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Mercer Super Trust KiwiSaver Active Bal</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1228</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/05/2015</td>\n",
       "      <td>87240.978</td>\n",
       "      <td>52344.587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Mercer Super Trust KiwiSaver Conserv</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1039</td>\n",
       "      <td>Moderate Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/05/2015</td>\n",
       "      <td>633.303</td>\n",
       "      <td>379.982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Mercer Super Trust KiwiSaver Growth</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1239</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/05/2015</td>\n",
       "      <td>6859.595</td>\n",
       "      <td>4115.757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Mercer Super Trust KiwiSaver High Growth</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1131</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/05/2015</td>\n",
       "      <td>9139.787</td>\n",
       "      <td>5483.872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Mercer Super Trust KiwiSaver Moderate</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1213</td>\n",
       "      <td>Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/05/2015</td>\n",
       "      <td>2240.726</td>\n",
       "      <td>1344.436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>Mercer Super Trust KiwiSaver Shares</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>862</td>\n",
       "      <td>Australia &amp; New Zealand Equity</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/05/2015</td>\n",
       "      <td>5335.246</td>\n",
       "      <td>3201.148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Mercer Super Trust KiwiSaver TransTasman</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>74</td>\n",
       "      <td>Australia &amp; New Zealand Equity</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Mid Growth</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31/01/2015</td>\n",
       "      <td>1881.585</td>\n",
       "      <td>1128.951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Milford KiwiSaver Conservative Fund</td>\n",
       "      <td>Milford Funds Limited</td>\n",
       "      <td>Milford Funds</td>\n",
       "      <td>361</td>\n",
       "      <td>Cautious Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Mid Growth</td>\n",
       "      <td>1/10/2012</td>\n",
       "      <td>David Lewis</td>\n",
       "      <td>1.50</td>\n",
       "      <td>15/03/2017</td>\n",
       "      <td>23663.555</td>\n",
       "      <td>14198.133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>NZ Defence Force KiwiSaver Balanced</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1538</td>\n",
       "      <td>Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>9/10/2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>17475.715</td>\n",
       "      <td>10485.429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>NZ Defence Force KiwiSaver Conservative</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1306</td>\n",
       "      <td>Cautious Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>9/10/2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>916.695</td>\n",
       "      <td>550.017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>NZ Defence Force KiwiSaver Growth</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1602</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>9/10/2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>4589.997</td>\n",
       "      <td>2753.998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>NZ Defence Force KiwiSaver Moderate</td>\n",
       "      <td>Mercer (N.Z.) Limited</td>\n",
       "      <td>Mercer</td>\n",
       "      <td>1364</td>\n",
       "      <td>Moderate Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>9/10/2015</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>1084.035</td>\n",
       "      <td>650.421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>NZ Funds KiwiSaver Growth Strategy</td>\n",
       "      <td>New Zealand Funds Management Limited</td>\n",
       "      <td>New Zealand Funds</td>\n",
       "      <td>185</td>\n",
       "      <td>Miscellaneous</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Mid Value</td>\n",
       "      <td>21/09/2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>60645.442</td>\n",
       "      <td>36387.265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>NZ Funds KiwiSaver Inflation Strategy</td>\n",
       "      <td>New Zealand Funds Management Limited</td>\n",
       "      <td>New Zealand Funds</td>\n",
       "      <td>182</td>\n",
       "      <td>Miscellaneous</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Mid Value</td>\n",
       "      <td>21/09/2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28/02/2017</td>\n",
       "      <td>43965.166</td>\n",
       "      <td>26379.100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>SIL NZ Share</td>\n",
       "      <td>ANZ New Zealand Investments Limited</td>\n",
       "      <td>ANZ</td>\n",
       "      <td>145</td>\n",
       "      <td>New Zealand Equity</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Mid Growth</td>\n",
       "      <td>1/03/1992</td>\n",
       "      <td>Mark Brown;Craig Brown;</td>\n",
       "      <td>11.25</td>\n",
       "      <td>31/10/2016</td>\n",
       "      <td>15396.723</td>\n",
       "      <td>9238.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>Simplicity KiwiSaver Balanced Fund</td>\n",
       "      <td>Simplicity (NZ) Limited</td>\n",
       "      <td>Simplicity NZ</td>\n",
       "      <td>66</td>\n",
       "      <td>Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>1/09/2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14/03/2017</td>\n",
       "      <td>15381.980</td>\n",
       "      <td>9229.188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Simplicity KiwiSaver Conservative Fund</td>\n",
       "      <td>Simplicity (NZ) Limited</td>\n",
       "      <td>Simplicity NZ</td>\n",
       "      <td>31</td>\n",
       "      <td>Cautious Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>1/09/2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14/03/2017</td>\n",
       "      <td>3951.668</td>\n",
       "      <td>2371.001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Simplicity KiwiSaver Growth Fund</td>\n",
       "      <td>Simplicity (NZ) Limited</td>\n",
       "      <td>Simplicity NZ</td>\n",
       "      <td>66</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>1/09/2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14/03/2017</td>\n",
       "      <td>47602.397</td>\n",
       "      <td>28561.438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>Staples Rodway KiwiSaver Balanced</td>\n",
       "      <td>Staples Rodway Superannuation Limited</td>\n",
       "      <td>Staples Rodway</td>\n",
       "      <td>1361</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Growth</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29/02/2016</td>\n",
       "      <td>15219.139</td>\n",
       "      <td>9131.483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>Staples Rodway KiwiSaver Growth</td>\n",
       "      <td>Staples Rodway Superannuation Limited</td>\n",
       "      <td>Staples Rodway</td>\n",
       "      <td>873</td>\n",
       "      <td>Aggressive Allocation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>New Zealand</td>\n",
       "      <td>Open Ended Investment Company</td>\n",
       "      <td>Large Blend</td>\n",
       "      <td>1/10/2007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29/02/2016</td>\n",
       "      <td>7621.845</td>\n",
       "      <td>4573.107</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        Name  \\\n",
       "1         AMP KiwiSaver AMP Income Generator   \n",
       "22       BNZ KiwiSaver First Home Buyer Fund   \n",
       "27      Booster KiwiSaver Capital Guaranteed   \n",
       "28           Booster KiwiSaver Default Saver   \n",
       "32    Booster KiwiSaver Socially Rsp Inv Bal   \n",
       "35     FC KiwiSaver Scheme's Active Balanced   \n",
       "36        FC KiwiSaver Scheme's Active Cnsrv   \n",
       "37       FC KiwiSaver Scheme's Active Growth   \n",
       "38   Fidelity KiwiSaver-Aggressive Kiwi Fund   \n",
       "39     Fidelity KiwiSaver-Balanced Kiwi Fund   \n",
       "40           Fidelity KiwiSaver-Ethical Kiwi   \n",
       "41       Fidelity KiwiSaver-Growth Kiwi Fund   \n",
       "44      Forsyth Barr KiwiSaver Balanced Port   \n",
       "45   Forsyth Barr KiwiSaver Growth Portfolio   \n",
       "51                   Mercer KiwiSaver Growth   \n",
       "53                 Mercer KiwiSaver Moderate   \n",
       "54   Mercer Super Trust KiwiSaver Active Bal   \n",
       "55      Mercer Super Trust KiwiSaver Conserv   \n",
       "56       Mercer Super Trust KiwiSaver Growth   \n",
       "57  Mercer Super Trust KiwiSaver High Growth   \n",
       "58     Mercer Super Trust KiwiSaver Moderate   \n",
       "59       Mercer Super Trust KiwiSaver Shares   \n",
       "60  Mercer Super Trust KiwiSaver TransTasman   \n",
       "62       Milford KiwiSaver Conservative Fund   \n",
       "63       NZ Defence Force KiwiSaver Balanced   \n",
       "64   NZ Defence Force KiwiSaver Conservative   \n",
       "65         NZ Defence Force KiwiSaver Growth   \n",
       "66       NZ Defence Force KiwiSaver Moderate   \n",
       "67        NZ Funds KiwiSaver Growth Strategy   \n",
       "68     NZ Funds KiwiSaver Inflation Strategy   \n",
       "70                              SIL NZ Share   \n",
       "71        Simplicity KiwiSaver Balanced Fund   \n",
       "72    Simplicity KiwiSaver Conservative Fund   \n",
       "73          Simplicity KiwiSaver Growth Fund   \n",
       "74         Staples Rodway KiwiSaver Balanced   \n",
       "75           Staples Rodway KiwiSaver Growth   \n",
       "\n",
       "                                Firm Name        Branding Name  Holdings  \\\n",
       "1                               AMP Group                  AMP       689   \n",
       "22        BNZ Investment Services Limited  Bank of New Zealand      2456   \n",
       "27     Booster Financial Services Limited            Grosvenor       151   \n",
       "28     Booster Financial Services Limited            Grosvenor       159   \n",
       "32     Booster Financial Services Limited            Grosvenor       147   \n",
       "35          ASB Group Investments Limited                  ASB      1237   \n",
       "36          ASB Group Investments Limited                  ASB      1212   \n",
       "37          ASB Group Investments Limited                  ASB      1218   \n",
       "38     Booster Financial Services Limited            Grosvenor        76   \n",
       "39     Booster Financial Services Limited            Grosvenor        75   \n",
       "40     Booster Financial Services Limited            Grosvenor       113   \n",
       "41     Booster Financial Services Limited            Grosvenor        75   \n",
       "44                   Forsyth Barr Limited         Forsyth Barr       153   \n",
       "45                   Forsyth Barr Limited         Forsyth Barr       153   \n",
       "51                  Mercer (N.Z.) Limited               Mercer      1602   \n",
       "53                  Mercer (N.Z.) Limited               Mercer      1364   \n",
       "54                  Mercer (N.Z.) Limited               Mercer      1228   \n",
       "55                  Mercer (N.Z.) Limited               Mercer      1039   \n",
       "56                  Mercer (N.Z.) Limited               Mercer      1239   \n",
       "57                  Mercer (N.Z.) Limited               Mercer      1131   \n",
       "58                  Mercer (N.Z.) Limited               Mercer      1213   \n",
       "59                  Mercer (N.Z.) Limited               Mercer       862   \n",
       "60                  Mercer (N.Z.) Limited               Mercer        74   \n",
       "62                  Milford Funds Limited        Milford Funds       361   \n",
       "63                  Mercer (N.Z.) Limited               Mercer      1538   \n",
       "64                  Mercer (N.Z.) Limited               Mercer      1306   \n",
       "65                  Mercer (N.Z.) Limited               Mercer      1602   \n",
       "66                  Mercer (N.Z.) Limited               Mercer      1364   \n",
       "67   New Zealand Funds Management Limited    New Zealand Funds       185   \n",
       "68   New Zealand Funds Management Limited    New Zealand Funds       182   \n",
       "70    ANZ New Zealand Investments Limited                  ANZ       145   \n",
       "71                Simplicity (NZ) Limited        Simplicity NZ        66   \n",
       "72                Simplicity (NZ) Limited        Simplicity NZ        31   \n",
       "73                Simplicity (NZ) Limited        Simplicity NZ        66   \n",
       "74  Staples Rodway Superannuation Limited       Staples Rodway      1361   \n",
       "75  Staples Rodway Superannuation Limited       Staples Rodway       873   \n",
       "\n",
       "                   Global Category  Morningstar Rating     Domicile  \\\n",
       "1              Moderate Allocation                 NaN  New Zealand   \n",
       "22             Cautious Allocation                 NaN  New Zealand   \n",
       "27                   Miscellaneous                 NaN  New Zealand   \n",
       "28             Cautious Allocation                 NaN  New Zealand   \n",
       "32                      Allocation                 NaN  New Zealand   \n",
       "35                      Allocation                 NaN  New Zealand   \n",
       "36             Moderate Allocation                 NaN  New Zealand   \n",
       "37           Aggressive Allocation                 NaN  New Zealand   \n",
       "38           Aggressive Allocation                 NaN  New Zealand   \n",
       "39                      Allocation                 NaN  New Zealand   \n",
       "40                      Allocation                 NaN  New Zealand   \n",
       "41           Aggressive Allocation                 NaN  New Zealand   \n",
       "44           Aggressive Allocation                 NaN  New Zealand   \n",
       "45           Aggressive Allocation                 NaN  New Zealand   \n",
       "51           Aggressive Allocation                 NaN  New Zealand   \n",
       "53             Moderate Allocation                 NaN  New Zealand   \n",
       "54           Aggressive Allocation                 NaN  New Zealand   \n",
       "55             Moderate Allocation                 NaN  New Zealand   \n",
       "56           Aggressive Allocation                 NaN  New Zealand   \n",
       "57           Aggressive Allocation                 NaN  New Zealand   \n",
       "58                      Allocation                 NaN  New Zealand   \n",
       "59  Australia & New Zealand Equity                 NaN  New Zealand   \n",
       "60  Australia & New Zealand Equity                 NaN  New Zealand   \n",
       "62             Cautious Allocation                 NaN  New Zealand   \n",
       "63                      Allocation                 NaN  New Zealand   \n",
       "64             Cautious Allocation                 NaN  New Zealand   \n",
       "65           Aggressive Allocation                 NaN  New Zealand   \n",
       "66             Moderate Allocation                 NaN  New Zealand   \n",
       "67                   Miscellaneous                 NaN  New Zealand   \n",
       "68                   Miscellaneous                 NaN  New Zealand   \n",
       "70              New Zealand Equity                 NaN  New Zealand   \n",
       "71                      Allocation                 NaN  New Zealand   \n",
       "72             Cautious Allocation                 NaN  New Zealand   \n",
       "73           Aggressive Allocation                 NaN  New Zealand   \n",
       "74           Aggressive Allocation                 NaN  New Zealand   \n",
       "75           Aggressive Allocation                 NaN  New Zealand   \n",
       "\n",
       "             Fund Legal Structure  Equity Style Inception Date  \\\n",
       "1   Open Ended Investment Company     Mid Blend     28/07/2016   \n",
       "22  Open Ended Investment Company   Large Blend     17/09/2015   \n",
       "27  Open Ended Investment Company   Large Blend      1/07/2014   \n",
       "28  Open Ended Investment Company   Large Blend      1/07/2014   \n",
       "32  Open Ended Investment Company   Large Blend      1/07/2014   \n",
       "35  Open Ended Investment Company  Large Growth      1/10/2007   \n",
       "36  Open Ended Investment Company  Large Growth      1/10/2007   \n",
       "37  Open Ended Investment Company  Large Growth      1/10/2007   \n",
       "38  Open Ended Investment Company   Large Blend      1/10/2007   \n",
       "39  Open Ended Investment Company   Large Blend      1/10/2007   \n",
       "40  Open Ended Investment Company  Large Growth     14/05/2008   \n",
       "41  Open Ended Investment Company   Large Blend      1/10/2007   \n",
       "44  Open Ended Investment Company  Large Growth     26/06/2008   \n",
       "45  Open Ended Investment Company  Large Growth     26/06/2008   \n",
       "51  Open Ended Investment Company  Large Growth     18/02/2015   \n",
       "53  Open Ended Investment Company  Large Growth     18/02/2015   \n",
       "54  Open Ended Investment Company   Large Blend      1/10/2007   \n",
       "55  Open Ended Investment Company   Large Blend      1/10/2007   \n",
       "56  Open Ended Investment Company   Large Blend      1/10/2007   \n",
       "57  Open Ended Investment Company  Large Growth      1/10/2007   \n",
       "58  Open Ended Investment Company   Large Blend      1/10/2007   \n",
       "59  Open Ended Investment Company  Large Growth      1/10/2007   \n",
       "60  Open Ended Investment Company    Mid Growth      1/10/2007   \n",
       "62  Open Ended Investment Company    Mid Growth      1/10/2012   \n",
       "63  Open Ended Investment Company  Large Growth      9/10/2015   \n",
       "64  Open Ended Investment Company  Large Growth      9/10/2015   \n",
       "65  Open Ended Investment Company  Large Growth      9/10/2015   \n",
       "66  Open Ended Investment Company  Large Growth      9/10/2015   \n",
       "67  Open Ended Investment Company     Mid Value     21/09/2010   \n",
       "68  Open Ended Investment Company     Mid Value     21/09/2010   \n",
       "70  Open Ended Investment Company    Mid Growth      1/03/1992   \n",
       "71  Open Ended Investment Company  Large Growth      1/09/2016   \n",
       "72  Open Ended Investment Company  Large Growth      1/09/2016   \n",
       "73  Open Ended Investment Company  Large Growth      1/09/2016   \n",
       "74  Open Ended Investment Company  Large Growth      1/10/2007   \n",
       "75  Open Ended Investment Company   Large Blend      1/10/2007   \n",
       "\n",
       "                 Manager Name  Manager Tenure Net Assets Date  \\\n",
       "1                         NaN             NaN      28/02/2017   \n",
       "22                        NaN             NaN      14/03/2017   \n",
       "27              David Beattie            2.67      28/02/2017   \n",
       "28  David Beattie;Nic Craven;            2.67      28/02/2017   \n",
       "32              David Beattie            2.67      28/02/2017   \n",
       "35                        NaN             NaN      31/12/2014   \n",
       "36                        NaN             NaN      31/12/2014   \n",
       "37                        NaN             NaN      31/12/2014   \n",
       "38                        NaN             NaN      31/10/2014   \n",
       "39                        NaN             NaN      31/10/2014   \n",
       "40                        NaN             NaN      31/10/2014   \n",
       "41                        NaN             NaN      31/10/2014   \n",
       "44                        NaN             NaN      29/09/2016   \n",
       "45                        NaN             NaN      29/09/2016   \n",
       "51     Phillip Houghton-Brown            2.08      28/02/2017   \n",
       "53     Phillip Houghton-Brown            2.08      28/02/2017   \n",
       "54                        NaN             NaN      31/05/2015   \n",
       "55                        NaN             NaN      31/05/2015   \n",
       "56                        NaN             NaN      31/05/2015   \n",
       "57                        NaN             NaN      31/05/2015   \n",
       "58                        NaN             NaN      31/05/2015   \n",
       "59                        NaN             NaN      31/05/2015   \n",
       "60                        NaN             NaN      31/01/2015   \n",
       "62                David Lewis            1.50      15/03/2017   \n",
       "63                        NaN             NaN      28/02/2017   \n",
       "64                        NaN             NaN      28/02/2017   \n",
       "65                        NaN             NaN      28/02/2017   \n",
       "66                        NaN             NaN      28/02/2017   \n",
       "67                        NaN             NaN      28/02/2017   \n",
       "68                        NaN             NaN      28/02/2017   \n",
       "70    Mark Brown;Craig Brown;           11.25      31/10/2016   \n",
       "71                        NaN             NaN      14/03/2017   \n",
       "72                        NaN             NaN      14/03/2017   \n",
       "73                        NaN             NaN      14/03/2017   \n",
       "74                        NaN             NaN      29/02/2016   \n",
       "75                        NaN             NaN      29/02/2016   \n",
       "\n",
       "    Net Assets Size  Net Assets Share Class  \n",
       "1           861.923                 517.154  \n",
       "22        27564.969               16538.981  \n",
       "27        32777.503               19666.502  \n",
       "28        23651.194               14190.716  \n",
       "32        18747.062               11248.237  \n",
       "35         7094.312                4256.587  \n",
       "36         1794.037                1076.422  \n",
       "37         8047.739                4828.643  \n",
       "38        15280.369                9168.221  \n",
       "39        94957.184               56974.310  \n",
       "40        10302.667                6181.600  \n",
       "41        42618.096               25570.858  \n",
       "44        12116.528                7269.917  \n",
       "45        10738.997                6443.398  \n",
       "51        29364.475               17618.685  \n",
       "53        38324.385               22994.631  \n",
       "54        87240.978               52344.587  \n",
       "55          633.303                 379.982  \n",
       "56         6859.595                4115.757  \n",
       "57         9139.787                5483.872  \n",
       "58         2240.726                1344.436  \n",
       "59         5335.246                3201.148  \n",
       "60         1881.585                1128.951  \n",
       "62        23663.555               14198.133  \n",
       "63        17475.715               10485.429  \n",
       "64          916.695                 550.017  \n",
       "65         4589.997                2753.998  \n",
       "66         1084.035                 650.421  \n",
       "67        60645.442               36387.265  \n",
       "68        43965.166               26379.100  \n",
       "70        15396.723                9238.034  \n",
       "71        15381.980                9229.188  \n",
       "72         3951.668                2371.001  \n",
       "73        47602.397               28561.438  \n",
       "74        15219.139                9131.483  \n",
       "75         7621.845                4573.107  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['Morningstar Rating'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "med_mr = data['Morningstar Rating'].median() # I am taking median because 4.0 is the median and 50% values are below 4.0 in the available data\n",
    "def remove_na_mr(row):\n",
    "    if pd.isnull(row['Morningstar Rating']):\n",
    "        return med_mr\n",
    "    else:\n",
    "        return row['Morningstar Rating']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mean of original 'Morningstar Rating': 3.725  - Mean after imputation 3.8552631578947367\n",
      "Median of original 'Morningstar Rating': 4.0  - Median after imputation 4.0\n"
     ]
    }
   ],
   "source": [
    "print(\"Mean of original 'Morningstar Rating':\",data['Morningstar Rating'].mean(),\" - Mean after imputation\",data.apply(remove_na_mr, axis=1).mean())\n",
    "print(\"Median of original 'Morningstar Rating':\",data['Morningstar Rating'].median(),\" - Median after imputation\",data.apply(remove_na_mr, axis=1).median())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['MR_new'] = data.apply(remove_na_mr, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mean of original 'Morningstar Rating': 3.725  - Mean after imputation 3.8552631578947367\n",
      "Median of original 'Morningstar Rating': 4.0  - Median after imputation 4.0\n"
     ]
    }
   ],
   "source": [
    "print(\"Mean of original 'Morningstar Rating':\",data['Morningstar Rating'].mean(),\" - Mean after imputation\",data['MR_new'].mean())\n",
    "print(\"Median of original 'Morningstar Rating':\",data['Morningstar Rating'].median(),\" - Median after imputation\",data['MR_new'].median())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task 3: Unique, 2 most used & 2 least used \"Equity Style\" values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count               76\n",
       "unique               5\n",
       "top       Large Growth\n",
       "freq                33\n",
       "Name: Equity Style, dtype: object"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Equity Style'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unique \"Equity Styles\": ['Mid Growth' 'Mid Blend' 'Large Growth' 'Large Blend' 'Mid Value']\n"
     ]
    }
   ],
   "source": [
    "print('Unique \"Equity Styles\":',data['Equity Style'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "es_cols = data['Equity Style'].value_counts().index.tolist() \n",
    "es_values = data['Equity Style'].value_counts().values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Most used: Large Growth ( 33 )  - 2nd Most used: Large Blend ( 28 )\n",
      "Least used: Mid Value ( 2 )  - 2nd Least used: Mid Blend ( 3 )\n"
     ]
    }
   ],
   "source": [
    "print('Most used:',es_cols[0],'(',es_values[0],')',' - 2nd Most used:',es_cols[1],'(',es_values[1],')')\n",
    "print('Least used:',es_cols[-1],'(',es_values[-1],')',' - 2nd Least used:',es_cols[-2],'(',es_values[-2],')')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task 4: Mapping of \"Morningstar Rating\" from [5,4,3,2,1] to ['Awsome','Good','Average','bad','Awful']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['MR_str'] = data['MR_new'].map(lambda s: 'Awful' if s==1 else 'Bad' if s==2 else 'Average' if s==3 else 'Good' if s==4 else 'Awesome')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>MR_str</th>\n",
       "      <th>MR_new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Average</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Good</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Average</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Average</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Good</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Average</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Average</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Good</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Awesome</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Good</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    MR_str  MR_new\n",
       "0  Average     3.0\n",
       "1     Good     4.0\n",
       "2  Average     3.0\n",
       "3  Average     3.0\n",
       "4     Good     4.0\n",
       "5  Average     3.0\n",
       "6  Average     3.0\n",
       "7     Good     4.0\n",
       "8  Awesome     5.0\n",
       "9     Good     4.0"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[['MR_str','MR_new']].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task 5: Data Aggregation using GroupBy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Name', 'Firm Name', 'Branding Name', 'Holdings', 'Global Category',\n",
       "       'Morningstar Rating', 'Domicile', 'Fund Legal Structure',\n",
       "       'Equity Style', 'Inception Date', 'Manager Name', 'Manager Tenure',\n",
       "       'Net Assets Date', 'Net Assets Size', 'Net Assets Share Class',\n",
       "       'MR_new', 'MR_str'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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></th>\n",
       "      <th>Holdings</th>\n",
       "      <th>Net Assets Size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Branding Name</th>\n",
       "      <th>Equity Style</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">AMP</th>\n",
       "      <th>Large Growth</th>\n",
       "      <td>1544.000000</td>\n",
       "      <td>1.466660e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Blend</th>\n",
       "      <td>689.000000</td>\n",
       "      <td>8.619230e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>92.000000</td>\n",
       "      <td>6.918946e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">ANZ</th>\n",
       "      <th>Large Growth</th>\n",
       "      <td>1543.111111</td>\n",
       "      <td>5.191668e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>438.666667</td>\n",
       "      <td>2.460913e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">ASB</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>1319.500000</td>\n",
       "      <td>1.191800e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large Growth</th>\n",
       "      <td>1222.333333</td>\n",
       "      <td>5.645363e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Aon</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>265.000000</td>\n",
       "      <td>1.911613e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>384.000000</td>\n",
       "      <td>8.220035e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bank of New Zealand</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>2456.800000</td>\n",
       "      <td>1.425594e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fisher Funds</th>\n",
       "      <th>Large Growth</th>\n",
       "      <td>209.500000</td>\n",
       "      <td>6.073430e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Forsyth Barr</th>\n",
       "      <th>Large Growth</th>\n",
       "      <td>153.000000</td>\n",
       "      <td>1.142776e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Generate</th>\n",
       "      <th>Mid Blend</th>\n",
       "      <td>56.000000</td>\n",
       "      <td>1.074962e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>108.000000</td>\n",
       "      <td>5.560979e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Grosvenor</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>132.307692</td>\n",
       "      <td>6.634276e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large Growth</th>\n",
       "      <td>113.000000</td>\n",
       "      <td>1.030267e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Mercer</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>1179.750000</td>\n",
       "      <td>2.424365e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large Growth</th>\n",
       "      <td>1358.272727</td>\n",
       "      <td>1.039314e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>74.000000</td>\n",
       "      <td>1.881585e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Milford Funds</th>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>429.000000</td>\n",
       "      <td>5.880018e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Zealand Funds</th>\n",
       "      <th>Mid Value</th>\n",
       "      <td>183.500000</td>\n",
       "      <td>5.230530e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Simplicity NZ</th>\n",
       "      <th>Large Growth</th>\n",
       "      <td>54.333333</td>\n",
       "      <td>2.231202e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Staples Rodway</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>873.000000</td>\n",
       "      <td>7.621845e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large Growth</th>\n",
       "      <td>1361.000000</td>\n",
       "      <td>1.521914e+04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Holdings  Net Assets Size\n",
       "Branding Name       Equity Style                              \n",
       "AMP                 Large Growth  1544.000000     1.466660e+05\n",
       "                    Mid Blend      689.000000     8.619230e+02\n",
       "                    Mid Growth      92.000000     6.918946e+04\n",
       "ANZ                 Large Growth  1543.111111     5.191668e+05\n",
       "                    Mid Growth     438.666667     2.460913e+05\n",
       "ASB                 Large Blend   1319.500000     1.191800e+06\n",
       "                    Large Growth  1222.333333     5.645363e+03\n",
       "Aon                 Large Blend    265.000000     1.911613e+04\n",
       "                    Mid Growth     384.000000     8.220035e+04\n",
       "Bank of New Zealand Large Blend   2456.800000     1.425594e+05\n",
       "Fisher Funds        Large Growth   209.500000     6.073430e+05\n",
       "Forsyth Barr        Large Growth   153.000000     1.142776e+04\n",
       "Generate            Mid Blend       56.000000     1.074962e+05\n",
       "                    Mid Growth     108.000000     5.560979e+04\n",
       "Grosvenor           Large Blend    132.307692     6.634276e+04\n",
       "                    Large Growth   113.000000     1.030267e+04\n",
       "Mercer              Large Blend   1179.750000     2.424365e+04\n",
       "                    Large Growth  1358.272727     1.039314e+05\n",
       "                    Mid Growth      74.000000     1.881585e+03\n",
       "Milford Funds       Mid Growth     429.000000     5.880018e+04\n",
       "New Zealand Funds   Mid Value      183.500000     5.230530e+04\n",
       "Simplicity NZ       Large Growth    54.333333     2.231202e+04\n",
       "Staples Rodway      Large Blend    873.000000     7.621845e+03\n",
       "                    Large Growth  1361.000000     1.521914e+04"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby(['Branding Name','Equity Style'])['Holdings','Net Assets Size'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task 6: Data Aggregation using pivot tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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></th>\n",
       "      <th>Holdings</th>\n",
       "      <th>Net Assets Size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Branding Name</th>\n",
       "      <th>Equity Style</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">AMP</th>\n",
       "      <th>Large Growth</th>\n",
       "      <td>1544.000000</td>\n",
       "      <td>1.466660e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Blend</th>\n",
       "      <td>689.000000</td>\n",
       "      <td>8.619230e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>92.000000</td>\n",
       "      <td>6.918946e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">ANZ</th>\n",
       "      <th>Large Growth</th>\n",
       "      <td>1543.111111</td>\n",
       "      <td>5.191668e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>438.666667</td>\n",
       "      <td>2.460913e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">ASB</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>1319.500000</td>\n",
       "      <td>1.191800e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large Growth</th>\n",
       "      <td>1222.333333</td>\n",
       "      <td>5.645363e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Aon</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>265.000000</td>\n",
       "      <td>1.911613e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>384.000000</td>\n",
       "      <td>8.220035e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bank of New Zealand</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>2456.800000</td>\n",
       "      <td>1.425594e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fisher Funds</th>\n",
       "      <th>Large Growth</th>\n",
       "      <td>209.500000</td>\n",
       "      <td>6.073430e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Forsyth Barr</th>\n",
       "      <th>Large Growth</th>\n",
       "      <td>153.000000</td>\n",
       "      <td>1.142776e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Generate</th>\n",
       "      <th>Mid Blend</th>\n",
       "      <td>56.000000</td>\n",
       "      <td>1.074962e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>108.000000</td>\n",
       "      <td>5.560979e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Grosvenor</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>132.307692</td>\n",
       "      <td>6.634276e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large Growth</th>\n",
       "      <td>113.000000</td>\n",
       "      <td>1.030267e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Mercer</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>1179.750000</td>\n",
       "      <td>2.424365e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large Growth</th>\n",
       "      <td>1358.272727</td>\n",
       "      <td>1.039314e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>74.000000</td>\n",
       "      <td>1.881585e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Milford Funds</th>\n",
       "      <th>Mid Growth</th>\n",
       "      <td>429.000000</td>\n",
       "      <td>5.880018e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Zealand Funds</th>\n",
       "      <th>Mid Value</th>\n",
       "      <td>183.500000</td>\n",
       "      <td>5.230530e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Simplicity NZ</th>\n",
       "      <th>Large Growth</th>\n",
       "      <td>54.333333</td>\n",
       "      <td>2.231202e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Staples Rodway</th>\n",
       "      <th>Large Blend</th>\n",
       "      <td>873.000000</td>\n",
       "      <td>7.621845e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Large Growth</th>\n",
       "      <td>1361.000000</td>\n",
       "      <td>1.521914e+04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Holdings  Net Assets Size\n",
       "Branding Name       Equity Style                              \n",
       "AMP                 Large Growth  1544.000000     1.466660e+05\n",
       "                    Mid Blend      689.000000     8.619230e+02\n",
       "                    Mid Growth      92.000000     6.918946e+04\n",
       "ANZ                 Large Growth  1543.111111     5.191668e+05\n",
       "                    Mid Growth     438.666667     2.460913e+05\n",
       "ASB                 Large Blend   1319.500000     1.191800e+06\n",
       "                    Large Growth  1222.333333     5.645363e+03\n",
       "Aon                 Large Blend    265.000000     1.911613e+04\n",
       "                    Mid Growth     384.000000     8.220035e+04\n",
       "Bank of New Zealand Large Blend   2456.800000     1.425594e+05\n",
       "Fisher Funds        Large Growth   209.500000     6.073430e+05\n",
       "Forsyth Barr        Large Growth   153.000000     1.142776e+04\n",
       "Generate            Mid Blend       56.000000     1.074962e+05\n",
       "                    Mid Growth     108.000000     5.560979e+04\n",
       "Grosvenor           Large Blend    132.307692     6.634276e+04\n",
       "                    Large Growth   113.000000     1.030267e+04\n",
       "Mercer              Large Blend   1179.750000     2.424365e+04\n",
       "                    Large Growth  1358.272727     1.039314e+05\n",
       "                    Mid Growth      74.000000     1.881585e+03\n",
       "Milford Funds       Mid Growth     429.000000     5.880018e+04\n",
       "New Zealand Funds   Mid Value      183.500000     5.230530e+04\n",
       "Simplicity NZ       Large Growth    54.333333     2.231202e+04\n",
       "Staples Rodway      Large Blend    873.000000     7.621845e+03\n",
       "                    Large Growth  1361.000000     1.521914e+04"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(data, index=['Branding Name','Equity Style'], values=['Holdings','Net Assets Size'], aggfunc=\"mean\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task 7: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Manager Name\n",
       "David Beattie                2.333333\n",
       "David Beattie;Nic Craven;    2.750000\n",
       "David Lewis                       NaN\n",
       "John Smith                   3.750000\n",
       "Jonathan Windust             5.000000\n",
       "Mark Brighouse               4.000000\n",
       "Mark Brown                   4.000000\n",
       "Mark Brown;Craig Brown;           NaN\n",
       "Phillip Houghton-Brown       4.000000\n",
       "Stuart Millar                4.000000\n",
       "Name: Morningstar Rating, dtype: float64"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#data[data['Age'].isnull()].groupby('Sex')['Sex'].count()\n",
    "data.groupby('Manager Name')['Morningstar Rating'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>Morningstar Rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manager Name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>David Beattie</th>\n",
       "      <td>2.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>David Beattie;Nic Craven;</th>\n",
       "      <td>2.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Smith</th>\n",
       "      <td>3.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jonathan Windust</th>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mark Brighouse</th>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mark Brown</th>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Phillip Houghton-Brown</th>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stuart Millar</th>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Morningstar Rating\n",
       "Manager Name                                 \n",
       "David Beattie                        2.333333\n",
       "David Beattie;Nic Craven;            2.750000\n",
       "John Smith                           3.750000\n",
       "Jonathan Windust                     5.000000\n",
       "Mark Brighouse                       4.000000\n",
       "Mark Brown                           4.000000\n",
       "Phillip Houghton-Brown               4.000000\n",
       "Stuart Millar                        4.000000"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(data, index=['Manager Name'], values=['Morningstar Rating'], aggfunc='mean')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 76 entries, 0 to 75\n",
      "Data columns (total 17 columns):\n",
      "Name                      76 non-null object\n",
      "Firm Name                 76 non-null object\n",
      "Branding Name             76 non-null object\n",
      "Holdings                  76 non-null int64\n",
      "Global Category           76 non-null object\n",
      "Morningstar Rating        40 non-null float64\n",
      "Domicile                  76 non-null object\n",
      "Fund Legal Structure      76 non-null object\n",
      "Equity Style              76 non-null object\n",
      "Inception Date            76 non-null object\n",
      "Manager Name              33 non-null object\n",
      "Manager Tenure            33 non-null float64\n",
      "Net Assets Date           75 non-null object\n",
      "Net Assets Size           76 non-null float64\n",
      "Net Assets Share Class    76 non-null float64\n",
      "MR_new                    76 non-null float64\n",
      "MR_str                    76 non-null object\n",
      "dtypes: float64(5), int64(1), object(11)\n",
      "memory usage: 10.2+ KB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Morningstar Rating\n",
       "3.0    5\n",
       "4.0    5\n",
       "5.0    4\n",
       "Name: Morningstar Rating, dtype: int64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['Manager Name'].isnull()].groupby('Morningstar Rating')['Morningstar Rating'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MR_new\n",
       "3.0     5\n",
       "4.0    34\n",
       "5.0     4\n",
       "Name: MR_new, dtype: int64"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['Manager Name'].isnull()].groupby('MR_new')['MR_new'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MR_str\n",
       "Average     5\n",
       "Awesome     4\n",
       "Good       34\n",
       "Name: MR_str, dtype: int64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[data['Manager Name'].isnull()].groupby('MR_str')['MR_str'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}