analysis.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Initial Setup\n",
    "\n",
    "- We will be importing the necessary dependencies and importing the dataset csv into a dataframe.\n",
    "- The first column will be dropped as it serves no purpose other than denote the row number\n",
    "- The column names will be standardized to have lower case letters separated by underscore(_)\n",
    "- The target value we are trying to predict with this exercise is Estimated Shares Outstanding (ESO) therefore we will be taking it separately into a target set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import dependencies\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import scipy.stats as stats\n",
    "\n",
    "from sklearn.preprocessing import MinMaxScaler, OneHotEncoder\n",
    "from sklearn.decomposition import PCA\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "\n",
    "\n",
    "import seaborn as sns\n",
    "\n",
    "# increase number of values displayed by query\n",
    "pd.set_option('display.max_rows', 100)\n",
    "pd.set_option('display.max_columns', 100)\n",
    "\n",
    "# read data from csv\n",
    "df = pd.read_csv('fundamentals.csv')\n",
    "\n",
    "# To check if the data is loaded correctly\n",
    "print(len(df))\n",
    "# print(df.info())\n",
    "\n",
    "# drop the first column since it only has row count\n",
    "df = df.drop(df.columns[0], axis=1)\n",
    "\n",
    "# check if column was dropped\n",
    "# print(df.info())\n",
    "\n",
    "# Create a standard list of column names\n",
    "standardized_column_names = [\n",
    "    'ticker_symbol',\n",
    "    'period_ending',\n",
    "    'accounts_payable',\n",
    "    'accounts_receivable',\n",
    "    'additional_income_expense_items',\n",
    "    'after_tax_roe',\n",
    "    'capital_expenditures',\n",
    "    'capital_surplus',\n",
    "    'cash_ratio',\n",
    "    'cash_and_cash_equivalents',\n",
    "    'changes_in_inventories',\n",
    "    'common_stocks',\n",
    "    'cost_of_revenue',\n",
    "    'current_ratio',\n",
    "    'deferred_asset_charges',\n",
    "    'deferred_liability_charges',\n",
    "    'depreciation',\n",
    "    'earnings_before_interest_and_tax',\n",
    "    'earnings_before_tax',\n",
    "    'effect_of_exchange_rate',\n",
    "    'equity_earnings_loss_unconsolidated_subsidiary',\n",
    "    'fixed_assets',\n",
    "    'goodwill',\n",
    "    'gross_margin',\n",
    "    'gross_profit',\n",
    "    'income_tax',\n",
    "    'intangible_assets',\n",
    "    'interest_expense',\n",
    "    'inventory',\n",
    "    'investments',\n",
    "    'liabilities',\n",
    "    'long_term_debt',\n",
    "    'long_term_investments',\n",
    "    'minority_interest',\n",
    "    'misc_stocks',\n",
    "    'net_borrowings',\n",
    "    'net_cash_flow',\n",
    "    'net_cash_flow_operating',\n",
    "    'net_cash_flows_financing',\n",
    "    'net_cash_flows_investing',\n",
    "    'net_income',\n",
    "    'net_income_adjustments',\n",
    "    'net_income_applicable_to_common_shareholders',\n",
    "    'net_income_cont_operations',\n",
    "    'net_receivables',\n",
    "    'non_recurring_items',\n",
    "    'operating_income',\n",
    "    'operating_margin',\n",
    "    'other_assets',\n",
    "    'other_current_assets',\n",
    "    'other_current_liabilities',\n",
    "    'other_equity',\n",
    "    'other_financing_activities',\n",
    "    'other_investing_activities',\n",
    "    'other_liabilities',\n",
    "    'other_operating_activities',\n",
    "    'other_operating_items',\n",
    "    'pre_tax_margin',\n",
    "    'pre_tax_roe',\n",
    "    'profit_margin',\n",
    "    'quick_ratio',\n",
    "    'research_and_development',\n",
    "    'retained_earnings',\n",
    "    'sale_and_purchase_of_stock',\n",
    "    'sales_general_and_admin',\n",
    "    'short_term_debt_current_portion_of_long_term_debt',\n",
    "    'short_term_investments',\n",
    "    \"total_assets\",\n",
    "    \"total_current_assets\",\n",
    "    \"total_current_liabilities\",\n",
    "    \"total_equity\",\n",
    "    \"total_liabilities\",\n",
    "    \"total_liabilities_&_equity\",\n",
    "    \"total_revenue\",\n",
    "    \"treasury_stock\",\n",
    "    \"for_year\",\n",
    "    \"earnings_per_share\",\n",
    "    \"estimated_shares_outstanding\"\n",
    "]\n",
    "\n",
    "print(len(standardized_column_names), \"column_names\")\n",
    "\n",
    "# rename columns\n",
    "df.columns = standardized_column_names\n",
    "\n",
    "# verify if columns were renamed\n",
    "# print(df.info())\n",
    "\n",
    "original_df = df.copy()\n",
    "\n",
    "# create a dataframe with the target columns (estimated_shares_outstanding)\n",
    "target_df = df[['estimated_shares_outstanding']].copy()\n",
    "# verify if target_df has estimated_shares_outstanding values\n",
    "print(target_df.info())\n",
    "\n",
    "# drop estimated_shares_outstanding column from the original dataframe\n",
    "df = df.drop(['estimated_shares_outstanding'], axis=1)\n",
    "\n",
    "\n",
    "# verify if estimated_shares_outstanding was dropped\n",
    "# print(df.info())\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handling missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df.info())\n",
    "\n",
    "# check for null values in ascending order\n",
    "df.isnull().sum().sort_values(ascending=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "According to the above the empty column count are as follows\n",
    "- quick_ratio                                          299\n",
    "- cash_ratio                                           299\n",
    "- current_ratio                                        299\n",
    "- earnings_per_share                                   219\n",
    "- for_year                                             173\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handling Ratios\n",
    "\n",
    "The equations for the ratios are as follows\n",
    "\n",
    "Current Ratio\n",
    "> Current Ratio = Total Current Assets / Total Current Liabilities\n",
    "\n",
    "\n",
    "Quick Ratio\n",
    "> Quick Ratio = (Total Current Assets - Inventory) / Total Current Liabilities\n",
    "\n",
    "Cash Ratio\n",
    "> Cash Ratio = Cash and Cash Equivalents / Total Current Liabilities\n",
    "\n",
    "*In all of the above equations we see liabilities as a denominator. My hypothesis is that the ratios are empty because Total Current liabilities are 0 (anything defined by zero is undefined, empty in this case)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Testing above hypothesis if the null values are due to the fact that the current liabilities are zero\n",
    "\n",
    "# number of rows where total current liabilities are zero and current ratio is null\n",
    "tcl_zero_cr_null = df[(df['total_current_liabilities'] == 0) & (df['current_ratio'].isnull())]\n",
    "print(len(tcl_zero_cr_null))\n",
    "\n",
    "# number of rows where total current liabilities are zero and quick ratio is not null\n",
    "tcl_zero_qr_null = df[(df['total_current_liabilities'] == 0) & (df['quick_ratio'].isnull())]\n",
    "print(len(tcl_zero_qr_null))\n",
    "\n",
    "# number of rows where total current liabilities are zero and cash ratio is not null\n",
    "tcl_zero_casr_null = df[(df['total_current_liabilities'] == 0) & (df['cash_ratio'].isnull())]\n",
    "print(len(tcl_zero_casr_null))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above code proves the hypothesis where the number of ratios being empty and the total current liabilities = 0 is the same.\n",
    "\n",
    "When handling missing values in a dataset, a common approach is to impute missing values. However, in this use case we cannot impute them as the denominator is zero and putting any other value derived from mean, median or mode would not be accurate.\n",
    "\n",
    "Additionally these three columns related to ratios are derived columns, therefore we can drop them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop Cash Ratio, Current Ratio and Quick Ratio\n",
    "df.drop(['current_ratio', 'quick_ratio' ,'cash_ratio'], axis=1, inplace=True)\n",
    "\n",
    "print(df.info())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handling missing 'earnings_per_share'\n",
    "\n",
    "Earnings per share can be calculated with the following equation\n",
    "> Earnings Per Share = Net Income / Estimated Shares Outstanding"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify if earnings_per_share = net_income / estimated_shares_outstanding\n",
    "eso_verification_merged_df = pd.merge(df[['net_income', 'earnings_per_share']], target_df, left_index=True, right_index=True)\n",
    "\n",
    "# drop rows where earnings_per_share is null\n",
    "eso_verification_merged_df = eso_verification_merged_df[eso_verification_merged_df['earnings_per_share'].notnull()]\n",
    "\n",
    "eso_verification_merged_df['calculated_eps'] = eso_verification_merged_df['net_income'] / eso_verification_merged_df['estimated_shares_outstanding']\n",
    "\n",
    "# compare two float64 columns with tolerance epsilon\n",
    "eso_verification_merged_df['eso_verification'] = np.isclose(eso_verification_merged_df['calculated_eps'], eso_verification_merged_df['earnings_per_share'], atol=1e-10)\n",
    "\n",
    "# get rows where eso_verification is False\n",
    "failed_eso_verification = eso_verification_merged_df[eso_verification_merged_df['eso_verification'] == False]\n",
    "\n",
    "print(len(failed_eso_verification))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above code proves that the equation to find Earnings per Share from Estimated Shares Outstanding is valid as the length of failed_eso_verification is 0.\n",
    "\n",
    "As Earnings per share is a derived column and is something that can be derived from the target column, we can drop the entire column as part of handling missing value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop earnings_per_share column from data frame\n",
    "df.drop(['earnings_per_share'], axis=1, inplace=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Handling missing 'for_year'\n",
    "\n",
    "The 'for_year' refers the the financial year to which the the row belongs to. Financial years don't always follow the start and end of the gregorian calendar we normally use. For example the financial year 2022 might end on the 1st of April 2023.\n",
    "\n",
    "We cannot simply imput the year value from 'period_ending' for the financial year since it would be accurate. Instead we can drop the 'for_year' column entirely as the relevant inference can be made with the 'period_ending' alone"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop for_year column\n",
    "df.drop(['for_year'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check for null values in ascending order\n",
    "df.isnull().sum().sort_values(ascending=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handle outliers - TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate Q-Q plot and Histograms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "for feature in df.columns:\n",
    "    if not pd.api.types.is_numeric_dtype(df[feature]):\n",
    "        continue\n",
    "\n",
    "    # Get the feature data\n",
    "    feature_data = df[feature]\n",
    "\n",
    "    # transform the feature\n",
    "    transformed_feature = np.log1p(feature_data)\n",
    "\n",
    "    # Create a Q-Q plot\n",
    "    fig, axes = plt.subplots(1, 2, figsize=(12, 4))\n",
    "    axes[0].set_title(f'Q-Q Plot of {feature}')\n",
    "    stats.probplot(feature_data, dist='norm', plot=axes[0])\n",
    "\n",
    "    # Create a histogram\n",
    "    axes[1].set_title(f'Histogram: {feature}')\n",
    "    sns.histplot(transformed_feature, kde=True, ax=axes[1])\n",
    "\n",
    "    # Show the plots\n",
    "    plt.show()\n",
    "\n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Apply feature  coding"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Apply one-hot encoding to ticker_symbol column\n",
    "encoder = OneHotEncoder()\n",
    "\n",
    "# Fit and transform the 'ticker_symbol' column using OneHotEncoder\n",
    "ticker_encoded = encoder.fit_transform(df[['ticker_symbol']])\n",
    "\n",
    "# Convert the encoded result into a DataFrame\n",
    "ticker_encoded_df = pd.DataFrame(ticker_encoded.toarray(), columns=encoder.get_feature_names_out(['ticker_symbol']))\n",
    "\n",
    "# Concatenate the encoded DataFrame with the original DataFrame\n",
    "df_one_hot = pd.concat([df, ticker_encoded_df], axis=1)\n",
    "\n",
    "# Drop the original 'ticker_symbol' column\n",
    "df_one_hot.drop('ticker_symbol', axis=1, inplace=True)\n",
    "\n",
    "print(ticker_encoded_df.columns)\n",
    "print(df_one_hot.columns)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scale and Standardize features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "scaler = MinMaxScaler()\n",
    "\n",
    "# get numeric columns from df\n",
    "numeric_columns = df.select_dtypes(include=['float64', 'int64']).columns\n",
    "\n",
    "# apply min-max scaling to numeric columns in df\n",
    "df_scaled = pd.DataFrame(scaler.fit_transform(df[numeric_columns]), columns=numeric_columns)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "for feature in df_scaled.columns:\n",
    "    if not pd.api.types.is_numeric_dtype(df_scaled[feature]):\n",
    "        continue\n",
    "\n",
    "    # Get the feature data\n",
    "    feature_data = df_scaled[feature]\n",
    "\n",
    "    # transform the feature\n",
    "    transformed_feature = np.log1p(feature_data)\n",
    "\n",
    "    # Create a Q-Q plot\n",
    "    fig, axes = plt.subplots(1, 2, figsize=(12, 4))\n",
    "    axes[0].set_title(f'Q-Q Plot of {feature}')\n",
    "    stats.probplot(feature_data, dist='norm', plot=axes[0])\n",
    "\n",
    "    # Create a histogram\n",
    "    axes[1].set_title(f'Histogram: {feature}')\n",
    "    sns.histplot(transformed_feature, kde=True, ax=axes[1])\n",
    "\n",
    "    # Show the plots\n",
    "    plt.show()\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature Descretization - TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature Reduction with PCA OR SVD - TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Identification of significant and independent features - TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prediction"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Linear regression with cross validation - TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lasso regression with cross validation - TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ridge regression with cross validation - TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Comparison of different regression models - TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.10.9"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}