4_snapshot_files.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# FSS Snapshot Files (AVRO)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "import requests as req\n",
    "import pandas as pd\n",
    "import fastavro\n",
    "\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Helper Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_avro_file(filepath) -> pd.DataFrame:\n",
    "\n",
    "    with open(filepath, \"rb\") as fp:\n",
    "        reader = fastavro.reader(fp)\n",
    "        r_df = pd.DataFrame.from_records(reader)\n",
    "\n",
    "    return r_df\n",
    "\n",
    "def read_avro_folder(folderpath) -> pd.DataFrame:\n",
    "\n",
    "    r_df = pd.DataFrame()\n",
    "    for filename in os.listdir(folderpath):\n",
    "        if filename.lower().endswith(\".avro\"):\n",
    "            t_df = read_avro_file(folderpath + \"/\" + filename)\n",
    "            r_df = pd.concat([r_df, t_df])\n",
    "    return r_df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read all files within a folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For articles = True use '0e2f212bf5' (109 files)\n",
    "# For articles = True (Small) use 'a236f418d8' (1 file)\n",
    "# For articles = False use 'eb26b0186e' (1 file)\n",
    "short_id = '8a5d2cfa53'\n",
    "\n",
    "fss_df = read_avro_folder(f\"./data/{short_id}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check data volume and basic stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(467820, 13)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fss_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['_company_code', 'score_date', 'score', 'daily_percentage_change',\n",
       "       'weekly_percentage_change', 'confidence', 'signal',\n",
       "       'total_article_count', 'negative_article_count',\n",
       "       'positive_article_count', 'company_name', '_an', 'theme'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fss_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>score</th>\n",
       "      <th>daily_percentage_change</th>\n",
       "      <th>weekly_percentage_change</th>\n",
       "      <th>confidence</th>\n",
       "      <th>total_article_count</th>\n",
       "      <th>negative_article_count</th>\n",
       "      <th>positive_article_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>423344.000000</td>\n",
       "      <td>423344.000000</td>\n",
       "      <td>423344.000000</td>\n",
       "      <td>423344.0</td>\n",
       "      <td>423344.000000</td>\n",
       "      <td>423344.000000</td>\n",
       "      <td>423344.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>0.233167</td>\n",
       "      <td>0.399763</td>\n",
       "      <td>2.563248</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19204.425715</td>\n",
       "      <td>8676.362939</td>\n",
       "      <td>10528.062776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.069947</td>\n",
       "      <td>0.428316</td>\n",
       "      <td>1.479529</td>\n",
       "      <td>0.0</td>\n",
       "      <td>642.238601</td>\n",
       "      <td>1389.919437</td>\n",
       "      <td>816.227763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.154600</td>\n",
       "      <td>-0.100000</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18126.000000</td>\n",
       "      <td>7135.000000</td>\n",
       "      <td>9670.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.159900</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>1.300000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18677.000000</td>\n",
       "      <td>7250.000000</td>\n",
       "      <td>9776.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.297600</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19642.000000</td>\n",
       "      <td>9972.000000</td>\n",
       "      <td>9882.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>0.299000</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>3.500000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19777.000000</td>\n",
       "      <td>10001.000000</td>\n",
       "      <td>11329.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>0.301700</td>\n",
       "      <td>1.500000</td>\n",
       "      <td>5.100000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>19887.000000</td>\n",
       "      <td>10018.000000</td>\n",
       "      <td>11780.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               score  daily_percentage_change  weekly_percentage_change  \\\n",
       "count  423344.000000            423344.000000             423344.000000   \n",
       "mean        0.233167                 0.399763                  2.563248   \n",
       "std         0.069947                 0.428316                  1.479529   \n",
       "min         0.154600                -0.100000                  0.100000   \n",
       "25%         0.159900                 0.100000                  1.300000   \n",
       "50%         0.297600                 0.200000                  2.500000   \n",
       "75%         0.299000                 0.800000                  3.500000   \n",
       "max         0.301700                 1.500000                  5.100000   \n",
       "\n",
       "       confidence  total_article_count  negative_article_count  \\\n",
       "count    423344.0        423344.000000           423344.000000   \n",
       "mean          1.0         19204.425715             8676.362939   \n",
       "std           0.0           642.238601             1389.919437   \n",
       "min           1.0         18126.000000             7135.000000   \n",
       "25%           1.0         18677.000000             7250.000000   \n",
       "50%           1.0         19642.000000             9972.000000   \n",
       "75%           1.0         19777.000000            10001.000000   \n",
       "max           1.0         19887.000000            10018.000000   \n",
       "\n",
       "       positive_article_count  \n",
       "count           423344.000000  \n",
       "mean             10528.062776  \n",
       "std                816.227763  \n",
       "min               9670.000000  \n",
       "25%               9776.000000  \n",
       "50%               9882.000000  \n",
       "75%              11329.000000  \n",
       "max              11780.000000  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fss_df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>_company_code</th>\n",
       "      <th>score_date</th>\n",
       "      <th>score</th>\n",
       "      <th>daily_percentage_change</th>\n",
       "      <th>weekly_percentage_change</th>\n",
       "      <th>confidence</th>\n",
       "      <th>signal</th>\n",
       "      <th>total_article_count</th>\n",
       "      <th>negative_article_count</th>\n",
       "      <th>positive_article_count</th>\n",
       "      <th>company_name</th>\n",
       "      <th>_an</th>\n",
       "      <th>theme</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MCROST</td>\n",
       "      <td>2024-12-16</td>\n",
       "      <td>0.3083</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>19231</td>\n",
       "      <td>9810</td>\n",
       "      <td>9421</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>CNEWSN0020240723ek7n0008g</td>\n",
       "      <td>not assigned</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MCROST</td>\n",
       "      <td>2024-12-16</td>\n",
       "      <td>0.3083</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>19231</td>\n",
       "      <td>9810</td>\n",
       "      <td>9421</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>MPSHEP0020240625ek6p000dx</td>\n",
       "      <td>not assigned</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MCROST</td>\n",
       "      <td>2024-12-16</td>\n",
       "      <td>0.3083</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>19231</td>\n",
       "      <td>9810</td>\n",
       "      <td>9421</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>TECHR00020240725ek7p000xg</td>\n",
       "      <td>not assigned</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MCROST</td>\n",
       "      <td>2024-12-16</td>\n",
       "      <td>0.3083</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>19231</td>\n",
       "      <td>9810</td>\n",
       "      <td>9421</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>MIRUK00020241119ekbj002ut</td>\n",
       "      <td>not assigned</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MCROST</td>\n",
       "      <td>2024-12-16</td>\n",
       "      <td>0.3083</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>19231</td>\n",
       "      <td>9810</td>\n",
       "      <td>9421</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>BNZNGA0020240820ek8k0015z</td>\n",
       "      <td>not assigned</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  _company_code  score_date   score  daily_percentage_change  \\\n",
       "0        MCROST  2024-12-16  0.3083                      0.1   \n",
       "1        MCROST  2024-12-16  0.3083                      0.1   \n",
       "2        MCROST  2024-12-16  0.3083                      0.1   \n",
       "3        MCROST  2024-12-16  0.3083                      0.1   \n",
       "4        MCROST  2024-12-16  0.3083                      0.1   \n",
       "\n",
       "   weekly_percentage_change  confidence    signal  total_article_count  \\\n",
       "0                       2.0           1  Moderate                19231   \n",
       "1                       2.0           1  Moderate                19231   \n",
       "2                       2.0           1  Moderate                19231   \n",
       "3                       2.0           1  Moderate                19231   \n",
       "4                       2.0           1  Moderate                19231   \n",
       "\n",
       "   negative_article_count  positive_article_count           company_name  \\\n",
       "0                    9810                    9421  Microsoft Corporation   \n",
       "1                    9810                    9421  Microsoft Corporation   \n",
       "2                    9810                    9421  Microsoft Corporation   \n",
       "3                    9810                    9421  Microsoft Corporation   \n",
       "4                    9810                    9421  Microsoft Corporation   \n",
       "\n",
       "                         _an         theme  \n",
       "0  CNEWSN0020240723ek7n0008g  not assigned  \n",
       "1  MPSHEP0020240625ek6p000dx  not assigned  \n",
       "2  TECHR00020240725ek7p000xg  not assigned  \n",
       "3  MIRUK00020241119ekbj002ut  not assigned  \n",
       "4  BNZNGA0020240820ek8k0015z  not assigned  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fss_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>_company_code</th>\n",
       "      <th>score_date</th>\n",
       "      <th>score</th>\n",
       "      <th>daily_percentage_change</th>\n",
       "      <th>weekly_percentage_change</th>\n",
       "      <th>confidence</th>\n",
       "      <th>signal</th>\n",
       "      <th>total_article_count</th>\n",
       "      <th>negative_article_count</th>\n",
       "      <th>positive_article_count</th>\n",
       "      <th>company_name</th>\n",
       "      <th>_an</th>\n",
       "      <th>theme</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>162360</th>\n",
       "      <td>MCROST</td>\n",
       "      <td>2024-12-16</td>\n",
       "      <td>0.3083</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>19231</td>\n",
       "      <td>9810</td>\n",
       "      <td>9421</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>DJDN000020200130eg1u003qp</td>\n",
       "      <td>Financial performance e.g. declines, growth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162361</th>\n",
       "      <td>MCROST</td>\n",
       "      <td>2024-12-16</td>\n",
       "      <td>0.3083</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>19231</td>\n",
       "      <td>9810</td>\n",
       "      <td>9421</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>CMPTNG0020240726ek7q00002</td>\n",
       "      <td>Financial performance e.g. declines, growth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162362</th>\n",
       "      <td>MCROST</td>\n",
       "      <td>2024-12-16</td>\n",
       "      <td>0.3083</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>19231</td>\n",
       "      <td>9810</td>\n",
       "      <td>9421</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>INVDAI0020240222ek2m0008g</td>\n",
       "      <td>Financial performance e.g. declines, growth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162363</th>\n",
       "      <td>MCROST</td>\n",
       "      <td>2024-12-16</td>\n",
       "      <td>0.3083</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>19231</td>\n",
       "      <td>9810</td>\n",
       "      <td>9421</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>BON0000020200722eg7m000p1</td>\n",
       "      <td>Financial performance e.g. declines, growth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162364</th>\n",
       "      <td>MCROST</td>\n",
       "      <td>2024-12-16</td>\n",
       "      <td>0.3083</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>19231</td>\n",
       "      <td>9810</td>\n",
       "      <td>9421</td>\n",
       "      <td>Microsoft Corporation</td>\n",
       "      <td>LBA0000020240619ek6j02cq2</td>\n",
       "      <td>Financial performance e.g. declines, growth</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       _company_code  score_date   score  daily_percentage_change  \\\n",
       "162360        MCROST  2024-12-16  0.3083                      0.1   \n",
       "162361        MCROST  2024-12-16  0.3083                      0.1   \n",
       "162362        MCROST  2024-12-16  0.3083                      0.1   \n",
       "162363        MCROST  2024-12-16  0.3083                      0.1   \n",
       "162364        MCROST  2024-12-16  0.3083                      0.1   \n",
       "\n",
       "        weekly_percentage_change  confidence    signal  total_article_count  \\\n",
       "162360                       2.0           1  Moderate                19231   \n",
       "162361                       2.0           1  Moderate                19231   \n",
       "162362                       2.0           1  Moderate                19231   \n",
       "162363                       2.0           1  Moderate                19231   \n",
       "162364                       2.0           1  Moderate                19231   \n",
       "\n",
       "        negative_article_count  positive_article_count           company_name  \\\n",
       "162360                    9810                    9421  Microsoft Corporation   \n",
       "162361                    9810                    9421  Microsoft Corporation   \n",
       "162362                    9810                    9421  Microsoft Corporation   \n",
       "162363                    9810                    9421  Microsoft Corporation   \n",
       "162364                    9810                    9421  Microsoft Corporation   \n",
       "\n",
       "                              _an                                        theme  \n",
       "162360  DJDN000020200130eg1u003qp  Financial performance e.g. declines, growth  \n",
       "162361  CMPTNG0020240726ek7q00002  Financial performance e.g. declines, growth  \n",
       "162362  INVDAI0020240222ek2m0008g  Financial performance e.g. declines, growth  \n",
       "162363  BON0000020200722eg7m000p1  Financial performance e.g. declines, growth  \n",
       "162364  LBA0000020240619ek6j02cq2  Financial performance e.g. declines, growth  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fss_df[fss_df.theme == 'Financial performance e.g. declines, growth'].head()"
   ]
  }
 ],
 "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
}