BBG_CA_Raw_File_Conversion_ian.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "from pathlib import Path\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime as datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "tags": [
     "parameters"
    ]
   },
   "outputs": [],
   "source": [
    "input_filename='/users/jkimball/Desktop/CAX/all.txt'\n",
    "csv_data=\"\"\n",
    "output_file=\"/tmp/real_output.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_data = \"\"\"\n",
    "7970 Hello World Equity|114714|1000|300|\n",
    "9057 JP Equity|157888|1000|300|\n",
    "9058 JP Equity|157888|1000|300|\n",
    "8054 JP Equity|114805|0|0|206076815|ACQUIS|U|RAC Electric Vehicles Inc|N.A.|N.A.|N.A.|N.A.|N.A.|20191112|20191112|20191129|BBG000BB6ZY1|BBG001FFF251|8053|JP|6|CP_FLAG|1|CP_TKR|8053 JP|CP_NAME|Sumitomo Corp|CP_ID_BB_COMP|114805|CP_UNIT|RAC Electric Vehicles Inc|CP_TOT_AMT|500.000000\n",
    "8053 JP Equity|114805|0|0|206076815|ACQUIS|U|RAC Electric Vehicles Inc|N.A.|N.A.|N.A.|N.A.|N.A.|20191112|20191112|20191129|BBG000BB6ZY1|BBG001FFF251|8053|JP|6|CP_FLAG|1|CP_TKR|8053 JP|CP_NAME|Sumitomo Corp|CP_ID_BB_COMP|114805|CP_UNIT|RAC Electric Vehicles Inc|CP_TOT_AMT|500.000000\n",
    "7887 JP Equity|157889|1000|300|\n",
    "8058 Goodbye World Equity|114151|1000|300|\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "import io\n",
    "if len(csv_data) > 0:\n",
    "    input_filename = io.StringIO(csv_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read BBG File\n",
    "\n",
    "cols = [\n",
    "      'BB_Code'\n",
    "    , 'BB_Company_Id'\n",
    "    , 'BB_Security_Id'\n",
    "    , 'Rcode'\n",
    "    , 'BB_Action_Id'\n",
    "    , 'Mnemonic'\n",
    "    , 'BB_Flag'\n",
    "    , 'BB_Company_Name'\n",
    "    , 'BB_Vendor_Id_Type'\n",
    "    , 'BB_Vendor_Id'\n",
    "    , 'BB_Currency'\n",
    "    , 'Market_Sector_Desc'\n",
    "    , 'BB_Unique'\n",
    "    , 'Ann_Date'\n",
    "    , 'Eff_Date'\n",
    "    , 'Amd_Date'\n",
    "    , 'BB_Global'\n",
    "    , 'BB_Global_Company_Id'\n",
    "    , 'BB_Security_Id_Desc'\n",
    "    , 'BB_Feed_Source'\n",
    "    , 'Nfields'\n",
    "]\n",
    "\n",
    "count = len(cols)\n",
    "\n",
    "def field_dict_func(value):\n",
    "    fields = value.split('|')[count:]\n",
    "    return dict(zip(fields[::2], fields[1::2]))\n",
    "\n",
    "def standard_field_func(value):\n",
    "    fields = value.split('|')\n",
    "    if len(fields) < count:\n",
    "        fields += [''] * (count - len(fields))\n",
    "    return tuple(fields[:count])\n",
    "\n",
    "bbg = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "chunks = pd.read_csv(\n",
    "    input_filename\n",
    "    , sep = 'f~o~o~b~a~r~'\n",
    "    , engine = 'python'\n",
    "    , header = None\n",
    "    , skip_blank_lines = True\n",
    "    , iterator = True\n",
    "    , chunksize = 10000\n",
    ")\n",
    "\n",
    "for chunk in chunks:\n",
    "    df = pd.DataFrame()\n",
    "    df = chunk[0].str.split('|', expand=True)\n",
    "    for i in range(len(df.columns), count):\n",
    "        df[i] = None\n",
    "    df = df.iloc[:, 0:count]\n",
    "    df.columns = cols\n",
    "    df['field_dict'] = chunk[0].apply(field_dict_func)\n",
    "    bbg = pd.concat([bbg, df], sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Validate\n",
    "\n",
    "check = bbg[~bbg['Nfields'].isnull()].apply(lambda r: int(r['Nfields']) == len(r['field_dict']), axis=1)\n",
    "check = list(check[check == False].index)\n",
    "\n",
    "if check:\n",
    "    raise ValueError('Bloomberg Nfields value check failure on rows: {0}'.format(str(check)))    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7, 22)"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bbg.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {
    "tags": [
     "output"
    ]
   },
   "outputs": [],
   "source": [
    "bbg.to_csv(output_file, index=False, sep=\"|\", escapechar=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    if TESTING:\n",
    "        import unittest\n",
    "\n",
    "        class TestNotebook(unittest.TestCase):\n",
    "\n",
    "            def test_validate_dividend(self):\n",
    "                usd_div = bbg.loc[bbg.BB_Code == '8053 JP Equity']\n",
    "                self.assertEqual(usd_div.Eff_Date.values[0], '20191112')\n",
    "\n",
    "        rc = unittest.main(argv=[''], verbosity=2, exit=False)\n",
    "\n",
    "        if (len(rc.result.failures)+len(rc.result.errors) > 0):\n",
    "            raise Exception(\"Tests failed!\")\n",
    "except NameError:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "hide_input": false,
  "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}