financial_data_extract_transform_load.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "mount_file_id": "1A1F-W9NdYxC3BZg5UmvdP0qbLv6tox54",
      "authorship_tag": "ABX9TyNd6KMnUmX5xs2cMrWOT3k6",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/alexD1990/Data_engineering/blob/main/financial_data_extract_transform_load.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Step 1 Collecting data from storage account (Extract)\n",
        "from google.colab import drive\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "import yfinance as yf\n",
        "import matplotlib.pyplot as plt\n",
        "import seaborn as sns\n",
        "import statistics\n",
        "\n",
        "def mount_google_drive(): # Function to mount Google Drive if not mounted, otherwise unmount and remount\n",
        "    drive.flush_and_unmount()\n",
        "    drive.mount('/content/drive')\n",
        "\n",
        "mount_google_drive() # Mount Google Drive\n",
        "file_path = \"filepath_to_your_csv.csv\" # Path to the CSV file in your Storage account\n",
        "\n",
        "# Read the CSV file into a DataFrame\n",
        "df_storage_account = pd.read_csv(file_path)\n",
        "\n",
        "df_storage_account.columns = df_storage_account.columns.str.strip() # Clean column names for whitespace\n",
        "df_storage_account_ticker = df_storage_account['Ticker'] # Create a list of tickers"
      ],
      "metadata": {
        "id": "Q9p70S1UnxaR",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "4f7dff01-2aa8-401b-be1d-ad05f3bdb52e"
      },
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Step 2 Collect data from yahoo finance API (Extract)\n",
        "import yfinance as yf\n",
        "import pandas as pd\n",
        "\n",
        "\n",
        "def get_financial_data(tickers):\n",
        "  \"\"\"\n",
        "  This function retrieves income statement data and closing prices for a list of ticker symbols.\n",
        "\n",
        "  Args:\n",
        "      tickers (list): A list of ticker symbols of the companies.\n",
        "\n",
        "  Returns:\n",
        "      dict: A dictionary where keys are ticker symbols and values are pandas DataFrames containing financial data.\n",
        "  \"\"\"\n",
        "\n",
        "  # Initialize dictionary to store dataframes\n",
        "  data_by_ticker = {}\n",
        "\n",
        "  # Loop through each ticker\n",
        "  for ticker in tickers:\n",
        "    try:\n",
        "      # Get income statement data\n",
        "      company = yf.Ticker(ticker)\n",
        "      df = company.income_stmt\n",
        "      df_transposed = df.T\n",
        "      new_df = df_transposed[['Total Revenue', 'Diluted EPS', 'Net Income']]\n",
        "      new_df.index = new_df.index.strftime('%Y')  # Change index to year\n",
        "\n",
        "      # Download closing prices for the past 4 years\n",
        "      closing_prices = {}\n",
        "      for year in range(2020, 2024):\n",
        "        start_date = f\"{year}-06-29\"\n",
        "        end_date = f\"{year}-06-30\"\n",
        "        year_data = yf.download(ticker, start=start_date, end=end_date, interval=\"1d\")\n",
        "        closing_prices[year] = year_data['Adj Close'].iloc[0]\n",
        "\n",
        "      # Create dataframe for closing prices\n",
        "      adj_close_df = pd.DataFrame(closing_prices.values(), index=closing_prices.keys(), columns=['Adj Close'])\n",
        "      adj_close_df.index = adj_close_df.index.astype(str)  # Ensure year is string\n",
        "\n",
        "      # Combine DataFrames\n",
        "      combined_df = pd.concat([new_df, adj_close_df], axis=1)\n",
        "\n",
        "      # Change data types\n",
        "      data_type_dict = {\n",
        "        'Total Revenue': 'int64',\n",
        "        'Diluted EPS': 'float64',\n",
        "        'Net Income': 'int64',\n",
        "        'Adj Close': 'float64'\n",
        "      }\n",
        "      combined_df = combined_df.astype(data_type_dict)\n",
        "\n",
        "      # Add dataframe to dictionary with ticker as key\n",
        "      data_by_ticker[ticker] = combined_df\n",
        "    except Exception as e:\n",
        "      print(f\"Error downloading data for {ticker}: {e}\")\n",
        "\n",
        "  return data_by_ticker\n",
        "\n",
        "# Example usage\n",
        "tickers = df_storage_account_ticker # Replace with your list of tickers\n",
        "data_by_ticker = get_financial_data(tickers)\n",
        "\n",
        "\n",
        "# You can now work with individual dataframes for each ticker\n",
        "\n",
        "data_by_ticker\n"
      ],
      "metadata": {
        "id": "llg9CA18jgrm"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Step 3 transform and make calculation on extracted data (Transform)\n",
        "\n",
        "def calculate_cagr(dataframe, column_name):\n",
        "  \"\"\"Calculates the CAGR for a specific column in a DataFrame.\n",
        "\n",
        "  Args:\n",
        "      dataframe (pandas.DataFrame): The DataFrame containing the data.\n",
        "      column_name (str): The name of the column for which CAGR is calculated.\n",
        "\n",
        "  Returns:\n",
        "      float: The CAGR for the specified column.\n",
        "  \"\"\"\n",
        "\n",
        "  # Get the opening and closing values for the column\n",
        "  current_value = dataframe[column_name].iloc[0]\n",
        "  past_value = dataframe[column_name].iloc[-1]\n",
        "\n",
        "  # Calculate the number of years (assuming annual data)\n",
        "  number_of_years = len(dataframe)\n",
        "\n",
        "  # Ensure non-zero opening value to avoid division by zero\n",
        "  if current_value == 0:\n",
        "    return None  # Handle cases where opening value is zero\n",
        "\n",
        "  # Calculate CAGR using the formula\n",
        "  cagr = ((current_value / past_value) ** (1 / number_of_years)) - 1\n",
        "\n",
        "  return cagr   # Express CAGR as a percentage\n",
        "\n",
        "# Empty list to store estimated dataframes for each ticker\n",
        "estimated_dataframes = []\n",
        "\n",
        "# Loop through each ticker (DataFrame) in the dictionary\n",
        "for ticker, dataframe in data_by_ticker.items():\n",
        "\n",
        "    # Calculate PE ratio and profit margin\n",
        "    dataframe['PE'] = dataframe['Adj Close'] / dataframe['Diluted EPS']\n",
        "    dataframe['Profit margin'] = dataframe['Net Income'] / dataframe['Total Revenue']\n",
        "\n",
        "    # Calculate CAGR for Total Revenue and Diluted EPS\n",
        "    revenue_cagr = calculate_cagr(dataframe, 'Total Revenue')\n",
        "    eps_cagr = calculate_cagr(dataframe, 'Diluted EPS')\n",
        "\n",
        "    # Get first year's revenue and EPS for future estimates\n",
        "    first_year_revenue = dataframe['Total Revenue'].iloc[0]\n",
        "    first_year_eps = dataframe['Diluted EPS'].iloc[0]\n",
        "\n",
        "    # Calculate mean PE ratio for the ticker\n",
        "    mean_pe = statistics.mean(dataframe['PE'])\n",
        "\n",
        "    # Create a dictionary to store estimates for this ticker\n",
        "    ticker_estimates = {\n",
        "        'Ticker': ticker,\n",
        "        'Revenue CAGR': revenue_cagr,\n",
        "        'EPS CAGR': eps_cagr,\n",
        "        '2023 Revenue': first_year_revenue,\n",
        "        '2023 EPS': first_year_eps,\n",
        "        'Mean PE': mean_pe,\n",
        "    }\n",
        "\n",
        "    # Extract last day's closing price using Yahoo Finance\n",
        "    company = yf.Ticker(ticker)\n",
        "    historical_data = company.history(period=\"1d\")\n",
        "    last_close_price = historical_data['Close'][0]\n",
        "\n",
        "    # Calculate future estimates and CAGR for stock price\n",
        "    ticker_estimates['2027 Revenue Est'] = first_year_revenue * (1 + revenue_cagr) ** len(dataframe)\n",
        "    ticker_estimates['2027 EPS Est'] = first_year_eps * (1 + eps_cagr) ** len(dataframe)\n",
        "    ticker_estimates['2027 Stock Price Est'] = ticker_estimates['2027 EPS Est'] * mean_pe\n",
        "    ticker_estimates['Current Stock Price'] = last_close_price\n",
        "    ticker_estimates['Stock Price CAGR'] = ((ticker_estimates['2027 Stock Price Est'] / last_close_price) ** (1 / len(dataframe))) - 1\n",
        "\n",
        "    # Create a DataFrame from the estimates dictionary\n",
        "    ticker_estimates_df = pd.DataFrame([ticker_estimates])\n",
        "\n",
        "    # Append the DataFrame for this ticker to the list\n",
        "    estimated_dataframes.append(ticker_estimates_df)\n",
        "\n",
        "# Combine all DataFrames into a single DataFrame\n",
        "all_estimates_df = pd.concat(estimated_dataframes)\n",
        "\n",
        "# Print the resulting DataFrame containing estimates for all tickers\n",
        "all_estimates_df\n",
        "\n"
      ],
      "metadata": {
        "id": "WGkgkKw67ItW"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Step 4 merge stock info from storage account with new dataframe (transform and clean)\n",
        "\n",
        "final_df = df_storage_account.merge(all_estimates_df, on='Ticker')\n",
        "final_df = final_df.drop('Morningstar Rating for Stocks', axis=1)\n",
        "new_column_order = ['Ticker',\n",
        "                    'Name',\n",
        "                    'Sector',\n",
        "                    'Industry',\n",
        "                    'Stock Style Box',\n",
        "                    'Market Cap  ($)',\n",
        "                    'Economic Moat',\n",
        "                    'Capital Allocation',\n",
        "                    '2023 Revenue',\n",
        "                    '2027 Revenue Est',\n",
        "                    'Revenue CAGR',\n",
        "                    '2023 EPS',\n",
        "                    '2027 EPS Est',\n",
        "                    'EPS CAGR',\n",
        "                    'Mean PE',\n",
        "                    'Current Stock Price',\n",
        "                    '2027 Stock Price Est',\n",
        "                    'Stock Price CAGR']\n",
        "final_df = final_df[new_column_order]"
      ],
      "metadata": {
        "id": "2Ym54nEYfT4e"
      },
      "execution_count": 137,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Step 5 Loading cleaned and transformed dataframe to storage account as csv (Loading)\n",
        "\n",
        "filename = 'stock_list.csv'\n",
        "filepath = 'your_filepath_to_your_storage_account' + filename #Filepath to storage environment\n",
        "\n",
        "# Save the DataFrame to a CSV file\n",
        "final_df.to_csv(filepath, index=False)"
      ],
      "metadata": {
        "id": "fZq19esLgEvt"
      },
      "execution_count": 138,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "final_df.head() # Preview of final dataframe"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 397
        },
        "id": "zsZ9Aiytz_Iw",
        "outputId": "756022ec-1184-44ff-d447-d684efc6cf24"
      },
      "execution_count": 140,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  Ticker                         Name                  Sector  \\\n",
              "0    MMM                        3M Co             Industrials   \n",
              "1  ABBNY                  ABB Ltd ADR             Industrials   \n",
              "2   AMCX     AMC Networks Inc Class A  Communication Services   \n",
              "3    AME                   AMETEK Inc             Industrials   \n",
              "4    AMN  AMN Healthcare Services Inc              Healthcare   \n",
              "\n",
              "                         Industry Stock Style Box Market Cap  ($)  \\\n",
              "0                   Conglomerates     Large Value     51168966000   \n",
              "1    Electrical Equipment & Parts    Large Growth     84351666344   \n",
              "2                   Entertainment     Small Value       541469967   \n",
              "3  Specialty Industrial Machinery        Mid Core     41166460467   \n",
              "4         Medical Care Facilities     Small Value      2078186364   \n",
              "\n",
              "  Economic Moat Capital Allocation  2023 Revenue  2027 Revenue Est  \\\n",
              "0        Narrow           Standard   32681000000      3.318567e+10   \n",
              "1          Wide           Standard   32235000000      3.976028e+10   \n",
              "2        Narrow           Standard    2711877000      2.612573e+09   \n",
              "3        Wide Q          Exemplary    6596950000      9.585787e+09   \n",
              "4        Narrow           Standard    3789254000      5.998397e+09   \n",
              "\n",
              "   Revenue CAGR  2023 EPS  2027 EPS Est  EPS CAGR    Mean PE  \\\n",
              "0      0.003838    -12.63           NaN       NaN   8.822812   \n",
              "1      0.053854      2.01      1.662593 -0.046331  15.512308   \n",
              "2     -0.009283      4.90      5.174569  0.013724  47.601122   \n",
              "3      0.097921      5.67      8.527560  0.107415  25.863735   \n",
              "4      0.121684      5.36     19.411892  0.379513  19.129649   \n",
              "\n",
              "   Current Stock Price  2027 Stock Price Est  Stock Price CAGR  \n",
              "0           104.070000                   NaN               NaN  \n",
              "1            47.860001             25.790649         -0.143214  \n",
              "2            12.670000            246.315288          1.099804  \n",
              "3           181.529999            220.554540          0.049885  \n",
              "4            59.419998            371.342671          0.581104  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-e1a361a6-8d6d-4417-ba77-133d54b2ee99\" class=\"colab-df-container\">\n",
              "    <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>Ticker</th>\n",
              "      <th>Name</th>\n",
              "      <th>Sector</th>\n",
              "      <th>Industry</th>\n",
              "      <th>Stock Style Box</th>\n",
              "      <th>Market Cap  ($)</th>\n",
              "      <th>Economic Moat</th>\n",
              "      <th>Capital Allocation</th>\n",
              "      <th>2023 Revenue</th>\n",
              "      <th>2027 Revenue Est</th>\n",
              "      <th>Revenue CAGR</th>\n",
              "      <th>2023 EPS</th>\n",
              "      <th>2027 EPS Est</th>\n",
              "      <th>EPS CAGR</th>\n",
              "      <th>Mean PE</th>\n",
              "      <th>Current Stock Price</th>\n",
              "      <th>2027 Stock Price Est</th>\n",
              "      <th>Stock Price CAGR</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>MMM</td>\n",
              "      <td>3M Co</td>\n",
              "      <td>Industrials</td>\n",
              "      <td>Conglomerates</td>\n",
              "      <td>Large Value</td>\n",
              "      <td>51168966000</td>\n",
              "      <td>Narrow</td>\n",
              "      <td>Standard</td>\n",
              "      <td>32681000000</td>\n",
              "      <td>3.318567e+10</td>\n",
              "      <td>0.003838</td>\n",
              "      <td>-12.63</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>8.822812</td>\n",
              "      <td>104.070000</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ABBNY</td>\n",
              "      <td>ABB Ltd ADR</td>\n",
              "      <td>Industrials</td>\n",
              "      <td>Electrical Equipment &amp; Parts</td>\n",
              "      <td>Large Growth</td>\n",
              "      <td>84351666344</td>\n",
              "      <td>Wide</td>\n",
              "      <td>Standard</td>\n",
              "      <td>32235000000</td>\n",
              "      <td>3.976028e+10</td>\n",
              "      <td>0.053854</td>\n",
              "      <td>2.01</td>\n",
              "      <td>1.662593</td>\n",
              "      <td>-0.046331</td>\n",
              "      <td>15.512308</td>\n",
              "      <td>47.860001</td>\n",
              "      <td>25.790649</td>\n",
              "      <td>-0.143214</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AMCX</td>\n",
              "      <td>AMC Networks Inc Class A</td>\n",
              "      <td>Communication Services</td>\n",
              "      <td>Entertainment</td>\n",
              "      <td>Small Value</td>\n",
              "      <td>541469967</td>\n",
              "      <td>Narrow</td>\n",
              "      <td>Standard</td>\n",
              "      <td>2711877000</td>\n",
              "      <td>2.612573e+09</td>\n",
              "      <td>-0.009283</td>\n",
              "      <td>4.90</td>\n",
              "      <td>5.174569</td>\n",
              "      <td>0.013724</td>\n",
              "      <td>47.601122</td>\n",
              "      <td>12.670000</td>\n",
              "      <td>246.315288</td>\n",
              "      <td>1.099804</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AME</td>\n",
              "      <td>AMETEK Inc</td>\n",
              "      <td>Industrials</td>\n",
              "      <td>Specialty Industrial Machinery</td>\n",
              "      <td>Mid Core</td>\n",
              "      <td>41166460467</td>\n",
              "      <td>Wide Q</td>\n",
              "      <td>Exemplary</td>\n",
              "      <td>6596950000</td>\n",
              "      <td>9.585787e+09</td>\n",
              "      <td>0.097921</td>\n",
              "      <td>5.67</td>\n",
              "      <td>8.527560</td>\n",
              "      <td>0.107415</td>\n",
              "      <td>25.863735</td>\n",
              "      <td>181.529999</td>\n",
              "      <td>220.554540</td>\n",
              "      <td>0.049885</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AMN</td>\n",
              "      <td>AMN Healthcare Services Inc</td>\n",
              "      <td>Healthcare</td>\n",
              "      <td>Medical Care Facilities</td>\n",
              "      <td>Small Value</td>\n",
              "      <td>2078186364</td>\n",
              "      <td>Narrow</td>\n",
              "      <td>Standard</td>\n",
              "      <td>3789254000</td>\n",
              "      <td>5.998397e+09</td>\n",
              "      <td>0.121684</td>\n",
              "      <td>5.36</td>\n",
              "      <td>19.411892</td>\n",
              "      <td>0.379513</td>\n",
              "      <td>19.129649</td>\n",
              "      <td>59.419998</td>\n",
              "      <td>371.342671</td>\n",
              "      <td>0.581104</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e1a361a6-8d6d-4417-ba77-133d54b2ee99')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-e1a361a6-8d6d-4417-ba77-133d54b2ee99 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-e1a361a6-8d6d-4417-ba77-133d54b2ee99');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-dca5d31a-728e-4937-acca-c2c5bbf0fde8\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-dca5d31a-728e-4937-acca-c2c5bbf0fde8')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-dca5d31a-728e-4937-acca-c2c5bbf0fde8 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "variable_name": "final_df",
              "repr_error": "Out of range float values are not JSON compliant: inf"
            }
          },
          "metadata": {},
          "execution_count": 140
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "FOpUihcx0By3"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}