Income_balance_wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"balance_sheet/balanceSheetStatement-QCOM-annual.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5 entries, 0 to 4\n",
      "Data columns (total 65 columns):\n",
      " #   Column                                   Non-Null Count  Dtype  \n",
      "---  ------                                   --------------  -----  \n",
      " 0   Unnamed: 0                               5 non-null      int64  \n",
      " 1   symbol                                   5 non-null      object \n",
      " 2   reportedCurrency                         5 non-null      object \n",
      " 3   cik                                      5 non-null      int64  \n",
      " 4   fillingDate                              5 non-null      object \n",
      " 5   acceptedDate                             5 non-null      object \n",
      " 6   calendarYear                             5 non-null      int64  \n",
      " 7   period                                   5 non-null      object \n",
      " 8   cashAndCashEquivalents                   5 non-null      int64  \n",
      " 9   shortTermInvestments                     5 non-null      int64  \n",
      " 10  cashAndShortTermInvestments              5 non-null      int64  \n",
      " 11  netReceivables                           5 non-null      int64  \n",
      " 12  inventory                                5 non-null      int64  \n",
      " 13  otherCurrentAssets                       5 non-null      int64  \n",
      " 14  totalCurrentAssets                       5 non-null      int64  \n",
      " 15  propertyPlantEquipmentNet                5 non-null      int64  \n",
      " 16  goodwill                                 5 non-null      int64  \n",
      " 17  intangibleAssets                         5 non-null      int64  \n",
      " 18  goodwillAndIntangibleAssets              5 non-null      int64  \n",
      " 19  longTermInvestments                      5 non-null      int64  \n",
      " 20  taxAssets                                5 non-null      int64  \n",
      " 21  otherNonCurrentAssets                    5 non-null      int64  \n",
      " 22  totalNonCurrentAssets                    5 non-null      int64  \n",
      " 23  otherAssets                              5 non-null      int64  \n",
      " 24  totalAssets                              5 non-null      int64  \n",
      " 25  accountPayables                          5 non-null      int64  \n",
      " 26  shortTermDebt                            5 non-null      int64  \n",
      " 27  taxPayables                              5 non-null      int64  \n",
      " 28  deferredRevenue                          5 non-null      int64  \n",
      " 29  otherCurrentLiabilities                  5 non-null      int64  \n",
      " 30  totalCurrentLiabilities                  5 non-null      int64  \n",
      " 31  longTermDebt                             5 non-null      int64  \n",
      " 32  deferredRevenueNonCurrent                5 non-null      int64  \n",
      " 33  deferredTaxLiabilitiesNonCurrent         5 non-null      int64  \n",
      " 34  otherNonCurrentLiabilities               5 non-null      int64  \n",
      " 35  totalNonCurrentLiabilities               5 non-null      int64  \n",
      " 36  otherLiabilities                         5 non-null      int64  \n",
      " 37  capitalLeaseObligations                  5 non-null      int64  \n",
      " 38  totalLiabilities                         5 non-null      int64  \n",
      " 39  preferredStock                           5 non-null      int64  \n",
      " 40  commonStock                              5 non-null      int64  \n",
      " 41  retainedEarnings                         5 non-null      int64  \n",
      " 42  accumulatedOtherComprehensiveIncomeLoss  5 non-null      int64  \n",
      " 43  othertotalStockholdersEquity             5 non-null      int64  \n",
      " 44  totalStockholdersEquity                  5 non-null      int64  \n",
      " 45  totalEquity                              5 non-null      int64  \n",
      " 46  totalLiabilitiesAndStockholdersEquity    5 non-null      int64  \n",
      " 47  minorityInterest                         5 non-null      int64  \n",
      " 48  totalLiabilitiesAndTotalEquity           5 non-null      int64  \n",
      " 49  totalInvestments                         5 non-null      int64  \n",
      " 50  totalDebt                                5 non-null      int64  \n",
      " 51  netDebt                                  5 non-null      int64  \n",
      " 52  link                                     5 non-null      object \n",
      " 53  finalLink                                5 non-null      object \n",
      " 54  lastRetainedEarnings                     1 non-null      float64\n",
      " 55  netIncome                                1 non-null      float64\n",
      " 56  stockRepurchases                         1 non-null      float64\n",
      " 57  dividendsPaid                            1 non-null      float64\n",
      " 58  otherDistributions                       1 non-null      float64\n",
      " 59  grossPPE                                 1 non-null      float64\n",
      " 60  annualDepreciation                       1 non-null      float64\n",
      " 61  capitalExpenditure                       1 non-null      float64\n",
      " 62  netPPE                                   1 non-null      float64\n",
      " 63  lastGoodwill                             1 non-null      float64\n",
      " 64  acquisitionsAndAdjustments               1 non-null      float64\n",
      "dtypes: float64(11), int64(47), object(7)\n",
      "memory usage: 2.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>totalAssets</th>\n",
       "      <th>totalLiabilities</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>49014000000</td>\n",
       "      <td>31001000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   totalAssets  totalLiabilities\n",
       "1  49014000000       31001000000"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['calendarYear'] == 2022][['totalAssets', 'totalLiabilities']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "> Pandas Instructions:\n",
      "```\n",
      "df[df['calendarYear'] == 2022]['totalAssets']\n",
      "```\n",
      "> Pandas Output: 1    49014000000\n",
      "Name: totalAssets, dtype: int64\n",
      "The total assets in the annual balance statement for the year 2022 amount to $49,014,000,000.\n"
     ]
    }
   ],
   "source": [
    "from llama_index.query_engine import PandasQueryEngine\n",
    "\n",
    "query_engine = PandasQueryEngine(df=df, verbose=True, synthesize_response=True)\n",
    "response = query_engine.query(\n",
    "    \"What are the assets in annual balance statement for the year 2022?\"\n",
    ")\n",
    "print(str(response))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "balance_sheet/balanceSheetStatement-AMZN-annual.csv\n",
      "balance_sheet/balanceSheetStatement-AMD-annual.csv\n",
      "balance_sheet/balanceSheetStatement-TSLA-annual.csv\n",
      "balance_sheet/balanceSheetStatement-AAPL-annual.csv\n",
      "balance_sheet/balanceSheetStatement-PYPL-annual.csv\n",
      "balance_sheet/balanceSheetStatement-ADP-annual.csv\n",
      "balance_sheet/balanceSheetStatement-NVDA-annual.csv\n",
      "balance_sheet/balanceSheetStatement-COST-annual.csv\n",
      "balance_sheet/balanceSheetStatement-META-annual.csv\n",
      "balance_sheet/balanceSheetStatement-INTC-annual.csv\n",
      "balance_sheet/balanceSheetStatement-ABNB-annual.csv\n",
      "balance_sheet/balanceSheetStatement-NFLX-annual.csv\n",
      "balance_sheet/balanceSheetStatement-QCOM-annual.csv\n",
      "balance_sheet/balanceSheetStatement-GOOG-annual.csv\n"
     ]
    }
   ],
   "source": [
    "import glob\n",
    "import pandas as pd\n",
    "\n",
    "# Get the list of all CSV files in the current directory\n",
    "csv_files = glob.glob('balance_sheet/*.csv')\n",
    "\n",
    "df_list = []\n",
    "\n",
    "remove_list  =['symbol', 'reportedCurrency', \n",
    "               'cik', 'fillingDate', 'acceptedDate', 'period', 'link', 'finalLink', 'lastRetainedEarnings', 'stockRepurchases', 'otherDistributions', \n",
    "               'grossPPE', 'annualDepreciation','lastGoodwill', 'stockRepurchases', 'minorityInterest', \n",
    "               'totalLiabilitiesAndStockholdersEquity','othertotalStockholdersEquity', 'accumulatedOtherComprehensiveIncomeLoss']\n",
    "# Loop through each CSV file and read it into a DataFrame\n",
    "for file in csv_files:\n",
    "    print(file)\n",
    "    df = pd.read_csv(file, index_col = None)\n",
    "    df.reset_index(drop=True, inplace=True)\n",
    "    df.drop(['Unnamed: 0.1'], axis = 1, inplace=True)\n",
    "    df.to_csv(f\"{file}\", index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "income_statement/incomeStatement-GOOG-annual.csv\n",
      "income_statement/incomeStatement-QCOM-annual.csv\n",
      "income_statement/incomeStatement-ABNB-annual.csv\n",
      "income_statement/incomeStatement-NFLX-annual.csv\n",
      "income_statement/incomeStatement-INTC-annual.csv\n",
      "income_statement/incomeStatement-META-annual.csv\n",
      "income_statement/incomeStatement-COST-annual.csv\n",
      "income_statement/incomeStatement-NVDA-annual.csv\n",
      "income_statement/incomeStatement-PYPL-annual.csv\n",
      "income_statement/incomeStatement-AAPL-annual.csv\n",
      "income_statement/incomeStatement-AMD-annual.csv\n",
      "income_statement/incomeStatement-TSLA-annual.csv\n",
      "income_statement/incomeStatement-AMZN-annual.csv\n",
      "income_statement/incomeStatement-ADP-annual.csv\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "import glob\n",
    "import pandas as pd\n",
    "\n",
    "# Get the list of all CSV files in the current directory\n",
    "csv_files = glob.glob('income_statement/*.csv')\n",
    "\n",
    "df_list = []\n",
    "\n",
    "remove_list  =['symbol', 'reportedCurrency', \n",
    "               'cik', 'fillingDate', 'acceptedDate', 'period', 'link', 'finalLink','generalAndAdministrativeExpenses', 'operatingIncomeRatio', 'lastRetainedEarnings', \n",
    "               'grossPPE', 'stockRepurchases', 'otherDistributions', 'retainedEarnings', \n",
    "               'epsdiluted','eps', 'weightedAverageShsOut']\n",
    "# Loop through each CSV file and read it into a DataFrame\n",
    "for file in csv_files:\n",
    "    print(file)\n",
    "    df = pd.read_csv(file, index_col = None)\n",
    "    df.reset_index(drop=True, inplace=True)\n",
    "    df.drop(['Unnamed: 0'], axis = 1, inplace=True)\n",
    "    df.to_csv(f\"{file}\", index = False)"
   ]
  },
  {
   "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.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}