2_companies.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Companies - Taxonomy\n",
    "\n",
    "Get the full list of companies included in FSS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import requests as req\n",
    "import pandas as pd\n",
    "import fastavro\n",
    "from io import BytesIO\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv()\n",
    "\n",
    "USER_KEY = os.getenv(\"FSS_USERKEY\")\n",
    "BASE_URL = \"https://api.dowjones.com\"\n",
    "\n",
    "REQ_HEADERS = {\n",
    "    \"user-key\": USER_KEY,\n",
    "    \"Accept\": \"application/json\"\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_325913/3270192162.py:6: DtypeWarning: Columns (19) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  csv_df = pd.read_csv(csv_file_url, storage_options=REQ_HEADERS)\n"
     ]
    }
   ],
   "source": [
    "csv_companies_url = f\"{BASE_URL}/fss/taxonomies/companies?format=csv\"\n",
    "\n",
    "response = req.get(csv_companies_url, headers=REQ_HEADERS)\n",
    "taxonomy_response = response.json()\n",
    "csv_file_url = taxonomy_response[\"download_urls\"][0]\n",
    "csv_df = pd.read_csv(csv_file_url, storage_options=REQ_HEADERS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>_CompanyCode</th>\n",
       "      <th>CompanyName</th>\n",
       "      <th>Address1</th>\n",
       "      <th>Address2</th>\n",
       "      <th>Address3</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>...</th>\n",
       "      <th>IndustryDescriptor</th>\n",
       "      <th>ListStatus</th>\n",
       "      <th>ExchangeTicker</th>\n",
       "      <th>Status</th>\n",
       "      <th>RegistrationID</th>\n",
       "      <th>URL</th>\n",
       "      <th>SEDOL</th>\n",
       "      <th>DUNS</th>\n",
       "      <th>CUSIP</th>\n",
       "      <th>ISIN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AABAG</td>\n",
       "      <td>Aachener Bausparkasse Aktiengesellschaft</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GFR</td>\n",
       "      <td>...</td>\n",
       "      <td>Mortgage Banks/Real Estate Credit</td>\n",
       "      <td>UL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>HRB88</td>\n",
       "      <td>http://www.aachener-bausparkasse.de</td>\n",
       "      <td>NaN</td>\n",
       "      <td>315719336</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABLXIS</td>\n",
       "      <td>Ablexis</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>...</td>\n",
       "      <td>Biotechnology Services</td>\n",
       "      <td>UL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>051068549</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADHARS</td>\n",
       "      <td>Adharshila Capital Services Ltd</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INDIA</td>\n",
       "      <td>...</td>\n",
       "      <td>Financial Investment Services</td>\n",
       "      <td>UL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>916517043</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AGBIOM</td>\n",
       "      <td>AgBiome LLC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>...</td>\n",
       "      <td>Biotechnology Services</td>\n",
       "      <td>UL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>079150527</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ALACRV</td>\n",
       "      <td>Aar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RUSS</td>\n",
       "      <td>...</td>\n",
       "      <td>Financial Investment Services</td>\n",
       "      <td>UL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  _CompanyCode                               CompanyName Address1 Address2  \\\n",
       "0        AABAG  Aachener Bausparkasse Aktiengesellschaft      NaN      NaN   \n",
       "1       ABLXIS                                   Ablexis      NaN      NaN   \n",
       "2       ADHARS           Adharshila Capital Services Ltd      NaN      NaN   \n",
       "3       AGBIOM                               AgBiome LLC      NaN      NaN   \n",
       "4       ALACRV                                       Aar      NaN      NaN   \n",
       "\n",
       "  Address3    City State PostalCode Country Region  ...  \\\n",
       "0      NaN     NaN   NaN        NaN     NaN    GFR  ...   \n",
       "1      NaN     NaN   NaN        NaN     NaN    USA  ...   \n",
       "2      NaN     NaN   NaN        NaN     NaN  INDIA  ...   \n",
       "3      NaN     NaN   NaN        NaN     NaN    USA  ...   \n",
       "4      NaN  Moscow   NaN        NaN     NaN   RUSS  ...   \n",
       "\n",
       "                  IndustryDescriptor ListStatus ExchangeTicker Status  \\\n",
       "0  Mortgage Banks/Real Estate Credit         UL            NaN      A   \n",
       "1             Biotechnology Services         UL            NaN      A   \n",
       "2      Financial Investment Services         UL            NaN      A   \n",
       "3             Biotechnology Services         UL            NaN      A   \n",
       "4      Financial Investment Services         UL            NaN      A   \n",
       "\n",
       "  RegistrationID                                  URL SEDOL       DUNS CUSIP  \\\n",
       "0          HRB88  http://www.aachener-bausparkasse.de   NaN  315719336   NaN   \n",
       "1            NaN                                  NaN   NaN  051068549   NaN   \n",
       "2            NaN                                  NaN   NaN  916517043   NaN   \n",
       "3            NaN                                  NaN   NaN  079150527   NaN   \n",
       "4            NaN                                  NaN   NaN        NaN   NaN   \n",
       "\n",
       "  ISIN  \n",
       "0       \n",
       "1       \n",
       "2       \n",
       "3       \n",
       "4       \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "csv_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## JSON\n",
    "\n",
    "Important: The JSON format returned in the file contains a record per line. This requires to use the option `lines=True` in `pandas.read_json(...)`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "json_companies_url = f\"{BASE_URL}/fss/taxonomies/companies?format=json\"\n",
    "\n",
    "response = req.get(json_companies_url, headers=REQ_HEADERS)\n",
    "taxonomy_response = response.json()\n",
    "json_file_url = taxonomy_response[\"download_urls\"][0]\n",
    "json_df = pd.read_json(json_file_url, orient='records', lines=True, storage_options=REQ_HEADERS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>_CompanyCode</th>\n",
       "      <th>CompanyName</th>\n",
       "      <th>Region</th>\n",
       "      <th>RegionDescriptor</th>\n",
       "      <th>Industry</th>\n",
       "      <th>IndustryDescriptor</th>\n",
       "      <th>ListStatus</th>\n",
       "      <th>Status</th>\n",
       "      <th>RegistrationID</th>\n",
       "      <th>URL</th>\n",
       "      <th>...</th>\n",
       "      <th>City</th>\n",
       "      <th>ExchangeTicker</th>\n",
       "      <th>SEDOL</th>\n",
       "      <th>CUSIP</th>\n",
       "      <th>Address1</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Address2</th>\n",
       "      <th>State</th>\n",
       "      <th>Address3</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AABAG</td>\n",
       "      <td>Aachener Bausparkasse Aktiengesellschaft</td>\n",
       "      <td>GFR</td>\n",
       "      <td>Germany</td>\n",
       "      <td>I8150103</td>\n",
       "      <td>Mortgage Banks/Real Estate Credit</td>\n",
       "      <td>UL</td>\n",
       "      <td>A</td>\n",
       "      <td>HRB88</td>\n",
       "      <td>http://www.aachener-bausparkasse.de</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABLXIS</td>\n",
       "      <td>Ablexis</td>\n",
       "      <td>USA</td>\n",
       "      <td>United States</td>\n",
       "      <td>I2569</td>\n",
       "      <td>Biotechnology Services</td>\n",
       "      <td>UL</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADHARS</td>\n",
       "      <td>Adharshila Capital Services Ltd</td>\n",
       "      <td>INDIA</td>\n",
       "      <td>India</td>\n",
       "      <td>I831</td>\n",
       "      <td>Financial Investment Services</td>\n",
       "      <td>UL</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AGBIOM</td>\n",
       "      <td>AgBiome LLC</td>\n",
       "      <td>USA</td>\n",
       "      <td>United States</td>\n",
       "      <td>I2569</td>\n",
       "      <td>Biotechnology Services</td>\n",
       "      <td>UL</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ALACRV</td>\n",
       "      <td>Aar</td>\n",
       "      <td>RUSS</td>\n",
       "      <td>Russia</td>\n",
       "      <td>I831</td>\n",
       "      <td>Financial Investment Services</td>\n",
       "      <td>UL</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  _CompanyCode                               CompanyName Region  \\\n",
       "0        AABAG  Aachener Bausparkasse Aktiengesellschaft    GFR   \n",
       "1       ABLXIS                                   Ablexis    USA   \n",
       "2       ADHARS           Adharshila Capital Services Ltd  INDIA   \n",
       "3       AGBIOM                               AgBiome LLC    USA   \n",
       "4       ALACRV                                       Aar   RUSS   \n",
       "\n",
       "  RegionDescriptor  Industry                 IndustryDescriptor ListStatus  \\\n",
       "0          Germany  I8150103  Mortgage Banks/Real Estate Credit         UL   \n",
       "1    United States     I2569             Biotechnology Services         UL   \n",
       "2            India      I831      Financial Investment Services         UL   \n",
       "3    United States     I2569             Biotechnology Services         UL   \n",
       "4           Russia      I831      Financial Investment Services         UL   \n",
       "\n",
       "  Status RegistrationID                                  URL  ...    City  \\\n",
       "0      A          HRB88  http://www.aachener-bausparkasse.de  ...     NaN   \n",
       "1      A            NaN                                  NaN  ...     NaN   \n",
       "2      A            NaN                                  NaN  ...     NaN   \n",
       "3      A            NaN                                  NaN  ...     NaN   \n",
       "4      A            NaN                                  NaN  ...  Moscow   \n",
       "\n",
       "  ExchangeTicker SEDOL CUSIP Address1 PostalCode Address2 State Address3  \\\n",
       "0            NaN   NaN   NaN      NaN        NaN      NaN   NaN      NaN   \n",
       "1            NaN   NaN   NaN      NaN        NaN      NaN   NaN      NaN   \n",
       "2            NaN   NaN   NaN      NaN        NaN      NaN   NaN      NaN   \n",
       "3            NaN   NaN   NaN      NaN        NaN      NaN   NaN      NaN   \n",
       "4            NaN   NaN   NaN      NaN        NaN      NaN   NaN      NaN   \n",
       "\n",
       "  Country  \n",
       "0     NaN  \n",
       "1     NaN  \n",
       "2     NaN  \n",
       "3     NaN  \n",
       "4     NaN  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## AVRO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_avro_from_url(url):\n",
    "    response = req.get(url, headers=REQ_HEADERS)\n",
    "    records = []\n",
    "\n",
    "    avro_file = BytesIO(response.content)\n",
    "    reader = fastavro.reader(avro_file)\n",
    "    records = [r for r in reader]\n",
    "\n",
    "    df = pd.DataFrame.from_records(records)\n",
    "\n",
    "    df['URL'] = df['URL'].fillna('')\n",
    "    df['ExchangeTicker'] = df['ExchangeTicker'].fillna('')\n",
    "    df['ISIN'] = df['ISIN'].fillna('')\n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "avro_companies_url = f\"{BASE_URL}/fss/taxonomies/companies?format=avro\"\n",
    "response = req.get(avro_companies_url, headers=REQ_HEADERS)\n",
    "taxonomy_response = response.json()\n",
    "avro_file_url = taxonomy_response[\"download_urls\"][0]\n",
    "avro_df = read_avro_from_url(avro_file_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(148522, 22)"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avro_df.shape"
   ]
  },
  {
   "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>_CompanyCode</th>\n",
       "      <th>CompanyName</th>\n",
       "      <th>Address1</th>\n",
       "      <th>Address2</th>\n",
       "      <th>Address3</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>...</th>\n",
       "      <th>IndustryDescriptor</th>\n",
       "      <th>ListStatus</th>\n",
       "      <th>ExchangeTicker</th>\n",
       "      <th>Status</th>\n",
       "      <th>RegistrationID</th>\n",
       "      <th>URL</th>\n",
       "      <th>SEDOL</th>\n",
       "      <th>DUNS</th>\n",
       "      <th>CUSIP</th>\n",
       "      <th>ISIN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AABAG</td>\n",
       "      <td>Aachener Bausparkasse Aktiengesellschaft</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>GFR</td>\n",
       "      <td>...</td>\n",
       "      <td>Mortgage Banks/Real Estate Credit</td>\n",
       "      <td>UL</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>HRB88</td>\n",
       "      <td>http://www.aachener-bausparkasse.de</td>\n",
       "      <td>None</td>\n",
       "      <td>315719336</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABLXIS</td>\n",
       "      <td>Ablexis</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>USA</td>\n",
       "      <td>...</td>\n",
       "      <td>Biotechnology Services</td>\n",
       "      <td>UL</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>051068549</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADHARS</td>\n",
       "      <td>Adharshila Capital Services Ltd</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>INDIA</td>\n",
       "      <td>...</td>\n",
       "      <td>Financial Investment Services</td>\n",
       "      <td>UL</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>916517043</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AGBIOM</td>\n",
       "      <td>AgBiome LLC</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>USA</td>\n",
       "      <td>...</td>\n",
       "      <td>Biotechnology Services</td>\n",
       "      <td>UL</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>079150527</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ALACRV</td>\n",
       "      <td>Aar</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>RUSS</td>\n",
       "      <td>...</td>\n",
       "      <td>Financial Investment Services</td>\n",
       "      <td>UL</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  _CompanyCode                               CompanyName Address1 Address2  \\\n",
       "0        AABAG  Aachener Bausparkasse Aktiengesellschaft     None     None   \n",
       "1       ABLXIS                                   Ablexis     None     None   \n",
       "2       ADHARS           Adharshila Capital Services Ltd     None     None   \n",
       "3       AGBIOM                               AgBiome LLC     None     None   \n",
       "4       ALACRV                                       Aar     None     None   \n",
       "\n",
       "  Address3    City State PostalCode Country Region  ...  \\\n",
       "0     None    None  None       None    None    GFR  ...   \n",
       "1     None    None  None       None    None    USA  ...   \n",
       "2     None    None  None       None    None  INDIA  ...   \n",
       "3     None    None  None       None    None    USA  ...   \n",
       "4     None  Moscow  None       None    None   RUSS  ...   \n",
       "\n",
       "                  IndustryDescriptor ListStatus ExchangeTicker Status  \\\n",
       "0  Mortgage Banks/Real Estate Credit         UL                     A   \n",
       "1             Biotechnology Services         UL                     A   \n",
       "2      Financial Investment Services         UL                     A   \n",
       "3             Biotechnology Services         UL                     A   \n",
       "4      Financial Investment Services         UL                     A   \n",
       "\n",
       "  RegistrationID                                  URL SEDOL       DUNS CUSIP  \\\n",
       "0          HRB88  http://www.aachener-bausparkasse.de  None  315719336  None   \n",
       "1           None                                       None  051068549  None   \n",
       "2           None                                       None  916517043  None   \n",
       "3           None                                       None  079150527  None   \n",
       "4           None                                       None       None  None   \n",
       "\n",
       "  ISIN  \n",
       "0       \n",
       "1       \n",
       "2       \n",
       "3       \n",
       "4       \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avro_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mapping companies\n",
    "\n",
    "Operations that help mapping companies from the taxonomy to a custom list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns:\n",
      "* _CompanyCode\n",
      "* CompanyName\n",
      "* Address1\n",
      "* Address2\n",
      "* Address3\n",
      "* City\n",
      "* State\n",
      "* PostalCode\n",
      "* Country\n",
      "* Region\n",
      "* RegionDescriptor\n",
      "* Industry\n",
      "* IndustryDescriptor\n",
      "* ListStatus\n",
      "* ExchangeTicker\n",
      "* Status\n",
      "* RegistrationID\n",
      "* URL\n",
      "* SEDOL\n",
      "* DUNS\n",
      "* CUSIP\n",
      "* ISIN\n"
     ]
    }
   ],
   "source": [
    "print(\"Columns:\")\n",
    "for col in avro_df.columns:\n",
    "    print(f\"* {col}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>_CompanyCode</th>\n",
       "      <th>CompanyName</th>\n",
       "      <th>Address1</th>\n",
       "      <th>Address2</th>\n",
       "      <th>Address3</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>...</th>\n",
       "      <th>IndustryDescriptor</th>\n",
       "      <th>ListStatus</th>\n",
       "      <th>ExchangeTicker</th>\n",
       "      <th>Status</th>\n",
       "      <th>RegistrationID</th>\n",
       "      <th>URL</th>\n",
       "      <th>SEDOL</th>\n",
       "      <th>DUNS</th>\n",
       "      <th>CUSIP</th>\n",
       "      <th>ISIN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>132885</th>\n",
       "      <td>APPLC</td>\n",
       "      <td>Apple, Inc.</td>\n",
       "      <td>One Apple Park Way</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Cupertino</td>\n",
       "      <td>California</td>\n",
       "      <td>95014-2083</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>...</td>\n",
       "      <td>Computers/Consumer Electronics</td>\n",
       "      <td>L</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td>http://www.apple.com</td>\n",
       "      <td>2046251</td>\n",
       "      <td>060704780</td>\n",
       "      <td>037833100</td>\n",
       "      <td>US0378331005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133542</th>\n",
       "      <td>GLLRSV</td>\n",
       "      <td>Pineapple, Inc.</td>\n",
       "      <td>12301 Wilshire Boulevard</td>\n",
       "      <td>Suite 420</td>\n",
       "      <td>None</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>90025</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>...</td>\n",
       "      <td>Marijuana Retailing</td>\n",
       "      <td>L</td>\n",
       "      <td>PNPL</td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td>http://www.pineappleexpress.com</td>\n",
       "      <td>BPLJQP7</td>\n",
       "      <td>062304913</td>\n",
       "      <td>72303J101</td>\n",
       "      <td>US72303J1016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       _CompanyCode      CompanyName                  Address1   Address2  \\\n",
       "132885        APPLC      Apple, Inc.        One Apple Park Way       None   \n",
       "133542       GLLRSV  Pineapple, Inc.  12301 Wilshire Boulevard  Suite 420   \n",
       "\n",
       "       Address3         City       State  PostalCode        Country Region  \\\n",
       "132885     None    Cupertino  California  95014-2083  United States    USA   \n",
       "133542     None  Los Angeles  California       90025  United States    USA   \n",
       "\n",
       "        ...              IndustryDescriptor ListStatus ExchangeTicker Status  \\\n",
       "132885  ...  Computers/Consumer Electronics          L           AAPL      A   \n",
       "133542  ...             Marijuana Retailing          L           PNPL      A   \n",
       "\n",
       "       RegistrationID                              URL    SEDOL       DUNS  \\\n",
       "132885           None             http://www.apple.com  2046251  060704780   \n",
       "133542           None  http://www.pineappleexpress.com  BPLJQP7  062304913   \n",
       "\n",
       "            CUSIP           ISIN  \n",
       "132885  037833100  US0378331005   \n",
       "133542  72303J101  US72303J1016   \n",
       "\n",
       "[2 rows x 22 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avro_df[avro_df.CompanyName.str.contains('apple, inc', case=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>_CompanyCode</th>\n",
       "      <th>CompanyName</th>\n",
       "      <th>Address1</th>\n",
       "      <th>Address2</th>\n",
       "      <th>Address3</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>...</th>\n",
       "      <th>IndustryDescriptor</th>\n",
       "      <th>ListStatus</th>\n",
       "      <th>ExchangeTicker</th>\n",
       "      <th>Status</th>\n",
       "      <th>RegistrationID</th>\n",
       "      <th>URL</th>\n",
       "      <th>SEDOL</th>\n",
       "      <th>DUNS</th>\n",
       "      <th>CUSIP</th>\n",
       "      <th>ISIN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>132854</th>\n",
       "      <td>APPLC</td>\n",
       "      <td>Apple, Inc.</td>\n",
       "      <td>One Apple Park Way</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Cupertino</td>\n",
       "      <td>California</td>\n",
       "      <td>95014-2083</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>...</td>\n",
       "      <td>Computers/Consumer Electronics</td>\n",
       "      <td>L</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td>http://www.apple.com</td>\n",
       "      <td>2046251</td>\n",
       "      <td>060704780</td>\n",
       "      <td>037833100</td>\n",
       "      <td>US0378331005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       _CompanyCode  CompanyName            Address1 Address2 Address3  \\\n",
       "132854        APPLC  Apple, Inc.  One Apple Park Way     None     None   \n",
       "\n",
       "             City       State  PostalCode        Country Region  ...  \\\n",
       "132854  Cupertino  California  95014-2083  United States    USA  ...   \n",
       "\n",
       "                    IndustryDescriptor ListStatus ExchangeTicker Status  \\\n",
       "132854  Computers/Consumer Electronics          L           AAPL      A   \n",
       "\n",
       "       RegistrationID                   URL    SEDOL       DUNS      CUSIP  \\\n",
       "132854           None  http://www.apple.com  2046251  060704780  037833100   \n",
       "\n",
       "                 ISIN  \n",
       "132854  US0378331005   \n",
       "\n",
       "[1 rows x 22 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avro_df[avro_df.ExchangeTicker == 'AAPL']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>_CompanyCode</th>\n",
       "      <th>CompanyName</th>\n",
       "      <th>Address1</th>\n",
       "      <th>Address2</th>\n",
       "      <th>Address3</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>...</th>\n",
       "      <th>IndustryDescriptor</th>\n",
       "      <th>ListStatus</th>\n",
       "      <th>ExchangeTicker</th>\n",
       "      <th>Status</th>\n",
       "      <th>RegistrationID</th>\n",
       "      <th>URL</th>\n",
       "      <th>SEDOL</th>\n",
       "      <th>DUNS</th>\n",
       "      <th>CUSIP</th>\n",
       "      <th>ISIN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>132854</th>\n",
       "      <td>APPLC</td>\n",
       "      <td>Apple, Inc.</td>\n",
       "      <td>One Apple Park Way</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Cupertino</td>\n",
       "      <td>California</td>\n",
       "      <td>95014-2083</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>...</td>\n",
       "      <td>Computers/Consumer Electronics</td>\n",
       "      <td>L</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td>http://www.apple.com</td>\n",
       "      <td>2046251</td>\n",
       "      <td>060704780</td>\n",
       "      <td>037833100</td>\n",
       "      <td>US0378331005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       _CompanyCode  CompanyName            Address1 Address2 Address3  \\\n",
       "132854        APPLC  Apple, Inc.  One Apple Park Way     None     None   \n",
       "\n",
       "             City       State  PostalCode        Country Region  ...  \\\n",
       "132854  Cupertino  California  95014-2083  United States    USA  ...   \n",
       "\n",
       "                    IndustryDescriptor ListStatus ExchangeTicker Status  \\\n",
       "132854  Computers/Consumer Electronics          L           AAPL      A   \n",
       "\n",
       "       RegistrationID                   URL    SEDOL       DUNS      CUSIP  \\\n",
       "132854           None  http://www.apple.com  2046251  060704780  037833100   \n",
       "\n",
       "                 ISIN  \n",
       "132854  US0378331005   \n",
       "\n",
       "[1 rows x 22 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avro_df[avro_df.ISIN.str.contains('US0378331005', case=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>_CompanyCode</th>\n",
       "      <th>CompanyName</th>\n",
       "      <th>Address1</th>\n",
       "      <th>Address2</th>\n",
       "      <th>Address3</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Country</th>\n",
       "      <th>Region</th>\n",
       "      <th>...</th>\n",
       "      <th>IndustryDescriptor</th>\n",
       "      <th>ListStatus</th>\n",
       "      <th>ExchangeTicker</th>\n",
       "      <th>Status</th>\n",
       "      <th>RegistrationID</th>\n",
       "      <th>URL</th>\n",
       "      <th>SEDOL</th>\n",
       "      <th>DUNS</th>\n",
       "      <th>CUSIP</th>\n",
       "      <th>ISIN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>56730</th>\n",
       "      <td>ZVHWML</td>\n",
       "      <td>Pineapple Financial, Inc.</td>\n",
       "      <td>111 Gordon Baker Road</td>\n",
       "      <td>Unit 200</td>\n",
       "      <td>None</td>\n",
       "      <td>North York</td>\n",
       "      <td>Ontario</td>\n",
       "      <td>M2H 3R1</td>\n",
       "      <td>Canada</td>\n",
       "      <td>CANA</td>\n",
       "      <td>...</td>\n",
       "      <td>Mortgage Banks/Real Estate Credit</td>\n",
       "      <td>L</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td>http://www.gopineapple.com</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84480</th>\n",
       "      <td>ASIIRE</td>\n",
       "      <td>Apple Sales International Limited</td>\n",
       "      <td>Hollyhill Industrial Estate  Hollyhill</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Cork</td>\n",
       "      <td>Co Cork</td>\n",
       "      <td>None</td>\n",
       "      <td>Ireland</td>\n",
       "      <td>IRE</td>\n",
       "      <td>...</td>\n",
       "      <td>Computers/Consumer Electronics</td>\n",
       "      <td>UL</td>\n",
       "      <td></td>\n",
       "      <td>A</td>\n",
       "      <td>157192</td>\n",
       "      <td>http://www.apple.com</td>\n",
       "      <td>None</td>\n",
       "      <td>238397210</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132854</th>\n",
       "      <td>APPLC</td>\n",
       "      <td>Apple, Inc.</td>\n",
       "      <td>One Apple Park Way</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Cupertino</td>\n",
       "      <td>California</td>\n",
       "      <td>95014-2083</td>\n",
       "      <td>United States</td>\n",
       "      <td>USA</td>\n",
       "      <td>...</td>\n",
       "      <td>Computers/Consumer Electronics</td>\n",
       "      <td>L</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>A</td>\n",
       "      <td>None</td>\n",
       "      <td>http://www.apple.com</td>\n",
       "      <td>2046251</td>\n",
       "      <td>060704780</td>\n",
       "      <td>037833100</td>\n",
       "      <td>US0378331005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       _CompanyCode                        CompanyName  \\\n",
       "56730        ZVHWML          Pineapple Financial, Inc.   \n",
       "84480        ASIIRE  Apple Sales International Limited   \n",
       "132854        APPLC                        Apple, Inc.   \n",
       "\n",
       "                                      Address1  Address2 Address3        City  \\\n",
       "56730                    111 Gordon Baker Road  Unit 200     None  North York   \n",
       "84480   Hollyhill Industrial Estate  Hollyhill      None     None        Cork   \n",
       "132854                      One Apple Park Way      None     None   Cupertino   \n",
       "\n",
       "             State  PostalCode        Country Region  ...  \\\n",
       "56730      Ontario     M2H 3R1         Canada   CANA  ...   \n",
       "84480      Co Cork        None        Ireland    IRE  ...   \n",
       "132854  California  95014-2083  United States    USA  ...   \n",
       "\n",
       "                       IndustryDescriptor ListStatus ExchangeTicker Status  \\\n",
       "56730   Mortgage Banks/Real Estate Credit          L                     A   \n",
       "84480      Computers/Consumer Electronics         UL                     A   \n",
       "132854     Computers/Consumer Electronics          L           AAPL      A   \n",
       "\n",
       "       RegistrationID                         URL    SEDOL       DUNS  \\\n",
       "56730            None  http://www.gopineapple.com     None       None   \n",
       "84480          157192        http://www.apple.com     None  238397210   \n",
       "132854           None        http://www.apple.com  2046251  060704780   \n",
       "\n",
       "            CUSIP           ISIN  \n",
       "56730        None                 \n",
       "84480        None                 \n",
       "132854  037833100  US0378331005   \n",
       "\n",
       "[3 rows x 22 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avro_df[avro_df['URL'].str.contains('apple.com', case=False)]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "fadev",
   "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.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}