Numpy-test.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Numpy-test\n",
    "Assignment for RedCarpetUp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing necessary libraries "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from bs4 import BeautifulSoup\n",
    "import requests\n",
    "import os\n",
    "import re\n",
    "from io import BytesIO\n",
    "from urllib.request import urlopen\n",
    "from zipfile import ZipFile"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating Data file for storing outputs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "file_path=\"data_file\"\n",
    "if not os.path.exists(file_path):\n",
    "    os.mkdir(file_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Defining two functions\n",
    "\n",
    "    1.sec_dataframe: \n",
    "        a.Input: getting the link for finding url's of zip file\n",
    "        b.Execution:finding \"associated-data-ditribution\" class as it contain the table of zip file. Then extracting the date and exempt information from the link itself.\n",
    "        c.Return: This function return the dataframe containing url of zip, Date and type of zip links.\n",
    "        \n",
    "        \n",
    "    2.get_sec_zip_by_period:\n",
    "        a.Input: getting periods, exempt and recent option to extract files\n",
    "        b.Execution: condition1: if opted for most recent option (True) then function will execute last executed time periods\n",
    "                     condition2: if not opted for most recent option (False) then periods passed will be executed.\n",
    "                     checking if there is any last executed file \n",
    "        c.Output: Extracting excel files for the given periods and joining all the excel files\n",
    "                     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def sec_dataframe(link) :    \n",
    "    page = requests.get(link)\n",
    "    soup = BeautifulSoup(page.text, 'html.parser')\n",
    "    artist_name_list = soup.find(class_='associated-data-distribution')\n",
    "    artist_name_list_items = artist_name_list.find_all('a')\n",
    "    sec=['https://www.sec.gov'+artist_name.get('href') for artist_name in artist_name_list_items]\n",
    "    sec=pd.DataFrame(data=sec,columns=['File_URL'])\n",
    "    sec['File_Name']=sec['File_URL'].apply(lambda x: x.rsplit('/',1)[1])\n",
    "    sec['Date']=sec['File_Name'].apply(lambda x:x[2:8])\n",
    "    sec['Date']=pd.to_datetime(sec['Date'],format=\"%m%d%y\")\n",
    "    sec['type']=sec['File_Name'].apply(lambda x:'exempt' if 'exempt' in x else 'non-exempt')\n",
    "    sec.drop('File_Name',axis=1,inplace=True)\n",
    "    return sec"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_sec_zip_by_period(periods = [], is_exempt = False, only_most_recent=False):\n",
    "    global recent_periods\n",
    "    if is_exempt:\n",
    "        exempt_value='exempt'\n",
    "    else:\n",
    "        exempt_value='non-exempt'\n",
    "    if only_most_recent==True:\n",
    "        try:\n",
    "            recent_periods\n",
    "            processing_periods=recent_periods\n",
    "        except NameError:\n",
    "            print (\"There is no recent period processed\")\n",
    "            \n",
    "    else:\n",
    "        print(\"period:{}\".format(periods))\n",
    "        processing_periods=periods\n",
    "        if len(periods)!=0:\n",
    "            recent_periods=periods\n",
    "            \n",
    "    processing_periods=[np.datetime64(period)for period in processing_periods]\n",
    "    sec_period=pd.DataFrame([],columns=sec.columns)\n",
    "    for period in processing_periods:\n",
    "        temp=sec[(sec['Date'].values.astype('datetime64[M]')==period) & (sec['type']==exempt_value)]\n",
    "        if temp.empty:\n",
    "            print('The{} period is out of table.'.format(period))\n",
    "        sec_period=sec_period.append(temp)\n",
    "    sec_details=pd.DataFrame()\n",
    "    for i in np.arange(len(sec_period.index)):\n",
    "        zipurl = sec_period.iloc[i]['File_URL']\n",
    "        zipresp=urlopen(zipurl)\n",
    "        zfile=ZipFile(BytesIO(zipresp.read()))\n",
    "        file_name=zfile.namelist()\n",
    "        excel_file=pd.read_excel(zfile.open(file_name[0]))\n",
    "        excel_file=excel_file[['SEC Region','Organization CRD#','SEC#','Legal Name','Main Office State','5F(2)(c)','5A']]\n",
    "        #print(excel_file.info())\n",
    "        if sec_details.empty:\n",
    "            sec_details=excel_file\n",
    "        else:\n",
    "            #excel_file=excel_file[['SEC Region','Organization CRD#','SEC#','Legal Name','Main Office State','5F(2)(c)','5A']]\n",
    "            sec_details=sec_details.join(excel_file.set_index('Organization CRD#'),on=['Organization CRD#'],rsuffix='_second',how='outer')\n",
    "    return sec_details"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calling two functions to get dataframe of excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "period:['2017-11', '2017-10', '2000-09']\n",
      "The2000-09 period is out of table.\n"
     ]
    }
   ],
   "source": [
    "#As the link provided in the assignment is redirecting to the below used link, we are using this instead of the given link.\n",
    "link='https://www.sec.gov/help/foiadocsinvafoiahtm.html'\n",
    "sec=sec_dataframe(link)\n",
    "sec.head()\n",
    "value=get_sec_zip_by_period(periods=['2017-11','2017-10','2000-09'],is_exempt=False,only_most_recent=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Processing raw file to get required columns\n",
    "    1. Processing null values with average values \n",
    "    1.getting average values of AUM, No of employees over given period of time.\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "value['SEC#']=value[['SEC#','SEC#_second']].apply(lambda row :row['SEC#_second'] if pd.isnull(row['SEC#']) else row['SEC#'],axis=1)\n",
    "value['SEC Region']=value[['SEC Region','SEC Region_second']].apply(lambda row :row['SEC Region_second'] if pd.isnull(row['SEC Region']) else row['SEC Region'],axis=1)\n",
    "value['Legal Name']=value[['Legal Name','Legal Name_second']].apply(lambda row :row['Legal Name_second'] if pd.isnull(row['Legal Name']) else row['Legal Name'],axis=1)\n",
    "value['Main Office State']=value[['Main Office State','Main Office State_second']].apply(lambda row :row['Main Office State_second'] if pd.isnull(row['Main Office State']) else row['Main Office State'],axis=1)\n",
    "value['5F(2)(c)']=value[['5F(2)(c)','5F(2)(c)_second']].apply(lambda row :row['5F(2)(c)_second'] if pd.isnull(row['5F(2)(c)']) else row['5F(2)(c)'],axis=1)\n",
    "value['5F(2)(c)_second']=value[['5F(2)(c)','5F(2)(c)_second']].apply(lambda row :row['5F(2)(c)'] if pd.isnull(row['5F(2)(c)_second']) else row['5F(2)(c)_second'],axis=1)\n",
    "\n",
    "value['5A']=value[['5A','5A_second']].apply(lambda row :row['5A_second'] if pd.isnull(row['5A']) else row['5A'],axis=1)\n",
    "value['5A_second']=value[['5A','5A_second']].apply(lambda row :row['5A'] if pd.isnull(row['5A_second']) else row['5A_second'],axis=1)\n",
    "\n",
    "value['AUM']=value[['5F(2)(c)','5F(2)(c)_second']].apply(lambda row : (row['5F(2)(c)']+row['5F(2)(c)_second'])/2,axis=1)\n",
    "value['No_of_Employees']=value[['5A','5A_second']].apply(lambda row : (row['5A']+row['5A_second'])/2,axis=1)\n",
    "\n",
    "final_sec=value.drop(['SEC Region_second','SEC#_second','Legal Name_second','Main Office State_second','5F(2)(c)','5F(2)(c)_second','5A','5A_second'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#final_sec"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generating Top managers details having highest AUM values and obtaining state wise distribution of these values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Top_aum=final_sec.sort_values('AUM',ascending=False).head(15)\n",
    "Top_aum.to_csv('data_file/Top_AUM.csv',index=False)\n",
    "\n",
    "f = {'No_of_Employees':['sum'], 'AUM':['sum'],'Legal Name':['count']}\n",
    "distribution=final_sec.groupby('Main Office State').agg(f)\n",
    "distribution.columns=distribution.columns.droplevel(1)\n",
    "distribution.to_csv('data_file/distribution.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# getting blackstone firms details\n",
    "blackstone=final_sec[final_sec['Legal Name'].apply( lambda x: 'blackstone'.lower() in x.lower())]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding  the blackstone firm in json file and getting their source id based on score value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "link='https://doppler.finra.org/doppler-lookup/api/v1/search/firms?hl=true&nrows=99000&query=blackstone&r=2500&wt=json'\n",
    "json_file=pd.read_json(link)\n",
    "list_of_json=pd.DataFrame(json_file['results'][0]['results'])['fields']\n",
    "json_df=pd.DataFrame()\n",
    "for i in np.arange(len(list_of_json)):\n",
    "    d=list_of_json[i]\n",
    "    df = pd.DataFrame.from_dict(d, orient='index')\n",
    "    df=df.transpose()\n",
    "    json_df=json_df.append(df)\n",
    "result_json=pd.DataFrame()\n",
    "for i in np.arange(len(blackstone)):\n",
    "    blackstone_firm=blackstone['Legal Name'].iloc[i]\n",
    "    list_value=[]\n",
    "    for j in np.arange(len(json_df)):\n",
    "        if (blackstone_firm == json_df['bc_firm_name'].iloc[j] and json_df['score'].iloc[j]>0.4 ):\n",
    "            list_value.append(True)\n",
    "        else:\n",
    "            for k in np.arange(len(json_df['bc_other_names'].iloc[j])):\n",
    "                count=0\n",
    "                if (blackstone_firm == json_df['bc_other_names'].iloc[j][k] and json_df['score'].iloc[j]>0.4):\n",
    "                    list_value.append(True)\n",
    "                    count=1\n",
    "                    break\n",
    "            if count==0:\n",
    "                list_value.append(False)\n",
    "    result_json=result_json.append(json_df[list_value])\n",
    "    result_json=result_json[['bc_firm_name','bc_source_id']]\n",
    "    result_json.reset_index(inplace=True,drop=True)\n",
    "        #print([True if (blackstone_firm in x) or (blackstone_firm in y) else False for i,x,y in json_df[['bc_firm_name','bc_other_names']]])\n",
    "    \n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### getting the brochure urls of sortlisted blackstone firms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "brochure=pd.DataFrame()\n",
    "for i in np.arange(len(result_json)):\n",
    "    id_value=result_json['bc_source_id'].iloc[i]\n",
    "    link='https://adviserinfo.sec.gov/IAPD/IAPDFirmSummary.aspx?ORG_PK='+id_value\n",
    "    page = requests.get(link)\n",
    "    soup = BeautifulSoup(page.text, 'html.parser')\n",
    "    artist_name = soup.find(id='ctl00_cphMain_landing_p2BrochureLink')\n",
    "    temp=['https://adviserinfo.sec.gov'+artist_name.get('href')]\n",
    "    temp=pd.DataFrame(data=temp,columns=['File_URL'])\n",
    "    brochure=brochure.append(temp)\n",
    "brochure.reset_index(inplace=True,drop=True)\n",
    "brochure=result_json.join(brochure)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Downloading pdf of brochures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for i in np.arange(len(brochure)):\n",
    "    url=brochure['File_URL'][i]\n",
    "    response = requests.get(url)\n",
    "    d = response.headers['content-disposition']\n",
    "    fname = re.findall(\"filename=(.+)\", d)\n",
    "    with open('data_file/'+fname[0], 'wb') as f:\n",
    "        f.write(response.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#json_df.sort_values('score',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#search firm name in given link but the score values are very very less than 4\n",
    "\n",
    "#json_df=pd.DataFrame()\n",
    "#for i in np.arange(len(blackstone)):\n",
    "#    firm=blackstone['Legal Name'].iloc[i].replace(' ','%20')\n",
    "#   link='https://doppler.finra.org/doppler-lookup/api/v1/search/firms?hl=true&nrows=99000&query='+firm+'&r=2500&wt=json'\n",
    "#    json_file=pd.read_json(link)\n",
    "#    list_of_json=pd.DataFrame(json_file['results'][0]['results'])['fields']\n",
    "    \n",
    "    \n",
    "#    for j in np.arange(len(list_of_json)):\n",
    "#        d=list_of_json[j]\n",
    "#        df = pd.DataFrame.from_dict(d, orient='index')\n",
    "#        df=df.transpose()\n",
    "#        json_df=json_df.append(df)\n",
    "        \n",
    "\n",
    "#'BLACKSTONE ISG-I ADVISORS L.L.C.'.replace(' ','%20')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#json_df.sort_values('score',ascending=False)"
   ]
  },
  {
   "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
}