structure_from_database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import pandas as pd\n",
    "\n",
    "class MySQL():\n",
    "    def __init__(self,passwd):\n",
    "        self.conn = pymysql.connect(host='localhost', unix_socket=None, user='corpnet', passwd=passwd, db='2017orbis') # new data\n",
    "        #self.conn = pymysql.connect(host='localhost', unix_socket=None, user='corpnet', passwd=passwd, db='2015-Sept') # old data\n",
    "        self.cur = self.conn.cursor()\n",
    "\n",
    "    def close(self):\n",
    "        self.cur.close()\n",
    "        self.conn.close()\n",
    "\n",
    "    def getFirstLevel(self,shareholder,fileSave=\"./data_companyNetworks/subsidiaries_edges.csv\"):\n",
    "        # get BvD of all the companies with > 50 direct or total.\n",
    "        with open(fileSave,\"a+\") as f:\n",
    "\n",
    "            # old data\n",
    "            #self.cur.execute(\"SELECT ownership.BvD_ID,ownership.directOwn,ownership.totalOwn \" \\\n",
    "            #                    \"FROM ownership \" \\\n",
    "            #                \"WHERE ownership.`BvD_ID_Shareholder` = \\\"{0}\\\" AND (ownership.`directOwn` > 50 OR ownership.`totalOwn` > 50)\".format(shareholder))\n",
    "            \n",
    "            # new data            \n",
    "            self.cur.execute(\"SELECT o.SubsidiaryBvD, o.direct, o.total \" \\\n",
    "                                \"FROM ownershipCurrent o \" \\\n",
    "                            \"WHERE o.ShareholderBvD = \\\"{0}\\\" AND (o.directNumber > 50 OR o.totalNumber > 50)\".format(shareholder))\n",
    "            core = []\n",
    "            for r in self.cur:\n",
    "                #print((\"{}\\t\"*(len(r)+1)).format(shareholder,*r)+\"\\n\")\n",
    "                f.write((\"{}\\t\"*(len(r)+1)).format(shareholder,r[0],100,*r[2:]).rstrip()+\"\\n\")\n",
    "                core.append(r[0])\n",
    "        return set(core)\n",
    "\n",
    "    def getSubsidiaries(self,shareholder,directOwner=5.,fileSave=\"./data_companyNetworks/subsidiaries_edges.csv\",coreCompany = False):\n",
    "        with open(fileSave,\"a+\") as f:\n",
    "            \n",
    "            # old data\n",
    "            #self.cur.execute(\"SELECT ownership.BvD_ID,ownership.directOwn,ownership.totalOwn \" \\\n",
    "            #                \"FROM ownership \" \\\n",
    "            #                \"WHERE ownership.`BvD_ID_Shareholder` = \\\"{0}\\\" AND (ownership.`directOwn` >= {1})\".format(shareholder,directOwner))\n",
    "\n",
    "            # new data\n",
    "            self.cur.execute(\"SELECT o.SubsidiaryBvD, o.direct, o.total \" \\\n",
    "                            \"FROM ownershipCurrent o \" \\\n",
    "                            \"WHERE o.ShareholderBvD = \\\"{0}\\\" AND (o.directNumber >= {1})\".format(shareholder,directOwner))\n",
    "\n",
    "\n",
    "            if coreCompany: shareholder = coreCompany\n",
    "            subsidiaries = []\n",
    "            for r in self.cur:\n",
    "                #print((\"{}\\t\"*(len(r)+1)).format(shareholder,*r)+\"\\n\")\n",
    "                f.write((\"{}\\t\"*(len(r)+1)).format(shareholder,*r).rstrip()+\"\\n\")\n",
    "                subsidiaries.append(r[0])\n",
    "\n",
    "        return set(subsidiaries)\n",
    "\n",
    "    def getNodeInformation(self,companiesList,fileSave=\"./data_companyNetworks/subsidiaries_nodes.csv\"):\n",
    "        with open(fileSave,\"w+\") as f:\n",
    "            r = [\"Id\", \"Label\", \"City\", \"Country\", \"Sector\", \"Revenue\", \"MainExchange\", \"Assets\", \"Employees\", \"State\"] #, \"CashFlow\", \"ShareholderFunds\", \"P/L\"]\n",
    "            f.write((\"{}\\t\"*(len(r))).format(*r)+\"\\n\")\n",
    "            for company in companiesList:\n",
    "                \n",
    "                # old data\n",
    "                #self.cur.execute(\"SELECT companies_all_info.`BvD_ID:Id`,companies_all_info.companyName, companies_all_info.city, companies_all_info.country, companies_all_info.BvD_majorSector, \" \\\n",
    "                #                 \"companies_all_info.`operatingRevenue:long`, companies_all_info.`mainExchange`, companies_all_info.`totalAssets:long`, companies_all_info.`employees:int`, companies_all_info.`cashFlow:long`, \" \\\n",
    "                #                 \"companies_all_info.`shareholdersFunds:long`,companies_all_info.`P_L_beforeTax:int` \" \\\n",
    "                #                 \"FROM companies_all_info \" \\\n",
    "                #                \"WHERE companies_all_info.`BvD_ID:Id` = \\\"{0}\\\";\".format(company))\n",
    "\n",
    "                # new data\n",
    "                self.cur.execute(\"SELECT c.BvD, c.companyName, c.city, c.iso2, c.letterNACE, \" \\\n",
    "                                 \"c.operatingRevenue, c.mainExchange, c.currentAssets, c.numberEmployees, CASE WHEN  ( c.typeEntity = 'Public authority/State/Government'  OR  c.letterNACE = 'O' ) THEN 1 else 0 END AS State \" \\\n",
    "                                 \"FROM recentFinancials c \" \\\n",
    "                                \"WHERE c.BvD = \\\"{0}\\\";\".format(company))\n",
    "\n",
    "                for r in self.cur:\n",
    "                    #print((\"{}\\t\"*(len(r)+1)).format(shareholder,*r)+\"\\n\")\n",
    "                    f.write((\"{}\\t\"*(len(r))).format(*r)+\"\\n\")\n",
    "\n",
    "\n",
    "    def getSeveralLevelsSubsidiaries(self,nameCompany,initCompany,directOwner=5.,numberLayers = 10):\n",
    "        fileSave = \"./data_companyNetworks/subsidiaries{0}_{1}_edges.csv\".format(nameCompany,numberLayers)\n",
    "        core = self.getFirstMultiLevel(initCompany,fileSave=fileSave)\n",
    "\n",
    "        allSubsidiaries = listStudy = set(core)\n",
    "        for i in range(numberLayers):\n",
    "            print(\"Layer: \",i+1)\n",
    "            layerSubsidiaries = set(initCompany)\n",
    "            for IDcompany in listStudy:\n",
    "                if IDcompany in core:\n",
    "                    subsidiaries = self.getSubsidiaries(IDcompany,directOwner,fileSave,coreCompany=initCompany)\n",
    "                else:\n",
    "                    subsidiaries = self.getSubsidiaries(IDcompany,directOwner,fileSave)\n",
    "                layerSubsidiaries = layerSubsidiaries | subsidiaries\n",
    "\n",
    "            listStudy = layerSubsidiaries - allSubsidiaries\n",
    "            allSubsidiaries = allSubsidiaries | layerSubsidiaries\n",
    "\n",
    "\n",
    "        #Drop duplicates (initCompany -> core1 -> core2 is counted twice as initCompany -> core2 since we're considering all of them as initCompany)\n",
    "        df = pd.read_csv(fileSave,sep=\"\\t\",header=None,index_col=None,na_values=[\"None\"])\n",
    "        df = df.sort_values(by=[0,1,2,3])\n",
    "        df.drop_duplicates(subset=[0,1],inplace=True)\n",
    "\n",
    "        df.columns=[\"Source\",\"Target\",\"Weight\",\"WeightTotal\"]\n",
    "        df.to_csv(fileSave,sep=\"\\t\",index=None)\n",
    "\n",
    "        listCompanies = set(df[\"Source\"]) | set(df[\"Target\"])\n",
    "        fileSave = \"./data_companyNetworks/subsidiaries{0}_{1}_nodes.csv\".format(nameCompany,numberLayers)\n",
    "        self.getNodeInformation(listCompanies,fileSave)\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    import sys, os\n",
    "    listArg = sys.argv\n",
    "\n",
    "    #listArg = [\"_\",\"Blackrock\",\"US320174431\",2]\n",
    "    if len(listArg) == 4:\n",
    "        companyName, companyID,layers = str(listArg[1]),str(listArg[2]),int(listArg[3])\n",
    "    else:\n",
    "        companyName = str(input(\"Insert company name: \"))\n",
    "        companyID = str(input(\"Insert BvD ID: \"))\n",
    "        layers = int(input(\"Insert number of Layers: \"))\n",
    "\n",
    "    if not os.path.exists(\"./data_companyNetworks\"):\n",
    "        print(\"Creating directory data_companyNetworks\")\n",
    "        os.makedirs(\"./data_companyNetworks\")\n",
    "\n",
    "\n",
    "    a = MySQL()\n",
    "    a.getSeveralLevelsSubsidiaries(companyName,companyID,numberLayers=layers)\n",
    "    a.close()\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}