data-cleaning.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "s1Fp1j8RmBMM"
      },
      "source": [
        "# Notebook Setup"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Zt05sfpblukq",
        "outputId": "f59dc293-8567-4129-9541-eb3314ca059a"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Mounted at /content/drive/\n"
          ]
        }
      ],
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive/')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "yZiDxDPbmDJU"
      },
      "outputs": [],
      "source": [
        "import os\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "from pandas import Series, DataFrame\n",
        "pd.options.display.float_format = '{:.10f}'.format"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E7kvBH6YItbn"
      },
      "source": [
        "# Quality assurance"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MrjP_fk0cSQI",
        "outputId": "93534cb1-250a-4753-89a6-030a78a33169"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "___________________2023___________________ \n",
            "\n",
            "Starting check for ADA-2023-4hr...\n",
            "Note: path ADAAVAX is not available. 11 columns are created and filled with nulls\n",
            "Note: path ADADAI is not available. 11 columns are created and filled with nulls\n",
            "Note: path ADADOT is not available. 11 columns are created and filled with nulls\n",
            "Note: path ADALINK is not available. 11 columns are created and filled with nulls\n",
            "Note: path ADALTC is not available. 11 columns are created and filled with nulls\n",
            "Note: path ADAMATIC is not available. 11 columns are created and filled with nulls\n",
            "Note: path ADASOL is not available. 11 columns are created and filled with nulls\n",
            "Note: path ADATRX is not available. 11 columns are created and filled with nulls\n",
            "Note: path ADAXLM is not available. 11 columns are created and filled with nulls\n",
            "Note: path ADAXRP is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for ADA-2023-4hr is downloaded to /content/drive/MyDrive/crypto/ADA-master-files/ADA-2023-4hr.csv\n",
            "\n",
            "Starting check for AVAX-2023-4hr...\n",
            "Note: path AVAXADA is not available. 11 columns are created and filled with nulls\n",
            "Note: path AVAXDAI is not available. 11 columns are created and filled with nulls\n",
            "Note: path AVAXDOT is not available. 11 columns are created and filled with nulls\n",
            "Note: path AVAXLINK is not available. 11 columns are created and filled with nulls\n",
            "Note: path AVAXLTC is not available. 11 columns are created and filled with nulls\n",
            "Note: path AVAXMATIC is not available. 11 columns are created and filled with nulls\n",
            "Note: path AVAXSOL is not available. 11 columns are created and filled with nulls\n",
            "Note: path AVAXTRX is not available. 11 columns are created and filled with nulls\n",
            "Note: path AVAXXLM is not available. 11 columns are created and filled with nulls\n",
            "Note: path AVAXXRP is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for AVAX-2023-4hr is downloaded to /content/drive/MyDrive/crypto/AVAX-master-files/AVAX-2023-4hr.csv\n",
            "\n",
            "Starting check for BNB-2023-4hr...\n",
            "Note: path BNBDAI is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for BNB-2023-4hr is downloaded to /content/drive/MyDrive/crypto/BNB-master-files/BNB-2023-4hr.csv\n",
            "\n",
            "Starting check for BTC-2023-4hr...\n",
            "Note: path BTCDAI is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for BTC-2023-4hr is downloaded to /content/drive/MyDrive/crypto/BTC-master-files/BTC-2023-4hr.csv\n",
            "\n",
            "Starting check for DAI-2023-4hr...\n",
            "Note: path DAIADA is not available. 11 columns are created and filled with nulls\n",
            "Note: path DAIAVAX is not available. 11 columns are created and filled with nulls\n",
            "Note: path DAIBNB is not available. 11 columns are created and filled with nulls\n",
            "Note: path DAIBTC is not available. 11 columns are created and filled with nulls\n",
            "Note: path DAIDOT is not available. 11 columns are created and filled with nulls\n",
            "Note: path DAILINK is not available. 11 columns are created and filled with nulls\n",
            "Note: path DAILTC is not available. 11 columns are created and filled with nulls\n",
            "Note: path DAIMATIC is not available. 11 columns are created and filled with nulls\n",
            "Note: path DAISOL is not available. 11 columns are created and filled with nulls\n",
            "Note: path DAITRX is not available. 11 columns are created and filled with nulls\n",
            "Note: path DAIXLM is not available. 11 columns are created and filled with nulls\n",
            "Note: path DAIXRP is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for DAI-2023-4hr is downloaded to /content/drive/MyDrive/crypto/DAI-master-files/DAI-2023-4hr.csv\n",
            "\n",
            "Starting check for DOT-2023-4hr...\n",
            "Note: path DOTADA is not available. 11 columns are created and filled with nulls\n",
            "Note: path DOTAVAX is not available. 11 columns are created and filled with nulls\n",
            "Note: path DOTDAI is not available. 11 columns are created and filled with nulls\n",
            "Note: path DOTLINK is not available. 11 columns are created and filled with nulls\n",
            "Note: path DOTLTC is not available. 11 columns are created and filled with nulls\n",
            "Note: path DOTMATIC is not available. 11 columns are created and filled with nulls\n",
            "Note: path DOTSOL is not available. 11 columns are created and filled with nulls\n",
            "Note: path DOTTRX is not available. 11 columns are created and filled with nulls\n",
            "Note: path DOTXLM is not available. 11 columns are created and filled with nulls\n",
            "Note: path DOTXRP is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for DOT-2023-4hr is downloaded to /content/drive/MyDrive/crypto/DOT-master-files/DOT-2023-4hr.csv\n",
            "\n",
            "Starting check for ETH-2023-4hr...\n",
            "All unix timestamps match!\n",
            "Master file for ETH-2023-4hr is downloaded to /content/drive/MyDrive/crypto/ETH-master-files/ETH-2023-4hr.csv\n",
            "\n",
            "Starting check for LINK-2023-4hr...\n",
            "Note: path LINKADA is not available. 11 columns are created and filled with nulls\n",
            "Note: path LINKAVAX is not available. 11 columns are created and filled with nulls\n",
            "Note: path LINKDAI is not available. 11 columns are created and filled with nulls\n",
            "Note: path LINKDOT is not available. 11 columns are created and filled with nulls\n",
            "Note: path LINKLTC is not available. 11 columns are created and filled with nulls\n",
            "Note: path LINKMATIC is not available. 11 columns are created and filled with nulls\n",
            "Note: path LINKSOL is not available. 11 columns are created and filled with nulls\n",
            "Note: path LINKTRX is not available. 11 columns are created and filled with nulls\n",
            "Note: path LINKXLM is not available. 11 columns are created and filled with nulls\n",
            "Note: path LINKXRP is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for LINK-2023-4hr is downloaded to /content/drive/MyDrive/crypto/LINK-master-files/LINK-2023-4hr.csv\n",
            "\n",
            "Starting check for LTC-2023-4hr...\n",
            "Note: path LTCADA is not available. 11 columns are created and filled with nulls\n",
            "Note: path LTCAVAX is not available. 11 columns are created and filled with nulls\n",
            "Note: path LTCDAI is not available. 11 columns are created and filled with nulls\n",
            "Note: path LTCDOT is not available. 11 columns are created and filled with nulls\n",
            "Note: path LTCLINK is not available. 11 columns are created and filled with nulls\n",
            "Note: path LTCMATIC is not available. 11 columns are created and filled with nulls\n",
            "Note: path LTCSOL is not available. 11 columns are created and filled with nulls\n",
            "Note: path LTCTRX is not available. 11 columns are created and filled with nulls\n",
            "Note: path LTCXLM is not available. 11 columns are created and filled with nulls\n",
            "Note: path LTCXRP is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for LTC-2023-4hr is downloaded to /content/drive/MyDrive/crypto/LTC-master-files/LTC-2023-4hr.csv\n",
            "\n",
            "Starting check for MATIC-2023-4hr...\n",
            "Note: path MATICADA is not available. 11 columns are created and filled with nulls\n",
            "Note: path MATICAVAX is not available. 11 columns are created and filled with nulls\n",
            "Note: path MATICDAI is not available. 11 columns are created and filled with nulls\n",
            "Note: path MATICDOT is not available. 11 columns are created and filled with nulls\n",
            "Note: path MATICLINK is not available. 11 columns are created and filled with nulls\n",
            "Note: path MATICLTC is not available. 11 columns are created and filled with nulls\n",
            "Note: path MATICSOL is not available. 11 columns are created and filled with nulls\n",
            "Note: path MATICTRX is not available. 11 columns are created and filled with nulls\n",
            "Note: path MATICXLM is not available. 11 columns are created and filled with nulls\n",
            "Note: path MATICXRP is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for MATIC-2023-4hr is downloaded to /content/drive/MyDrive/crypto/MATIC-master-files/MATIC-2023-4hr.csv\n",
            "\n",
            "Starting check for SOL-2023-4hr...\n",
            "Note: path SOLADA is not available. 11 columns are created and filled with nulls\n",
            "Note: path SOLAVAX is not available. 11 columns are created and filled with nulls\n",
            "Note: path SOLDAI is not available. 11 columns are created and filled with nulls\n",
            "Note: path SOLDOT is not available. 11 columns are created and filled with nulls\n",
            "Note: path SOLLINK is not available. 11 columns are created and filled with nulls\n",
            "Note: path SOLLTC is not available. 11 columns are created and filled with nulls\n",
            "Note: path SOLMATIC is not available. 11 columns are created and filled with nulls\n",
            "Note: path SOLTRX is not available. 11 columns are created and filled with nulls\n",
            "Note: path SOLXLM is not available. 11 columns are created and filled with nulls\n",
            "Note: path SOLXRP is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for SOL-2023-4hr is downloaded to /content/drive/MyDrive/crypto/SOL-master-files/SOL-2023-4hr.csv\n",
            "\n",
            "Starting check for TRX-2023-4hr...\n",
            "Note: path TRXADA is not available. 11 columns are created and filled with nulls\n",
            "Note: path TRXAVAX is not available. 11 columns are created and filled with nulls\n",
            "Note: path TRXDAI is not available. 11 columns are created and filled with nulls\n",
            "Note: path TRXDOT is not available. 11 columns are created and filled with nulls\n",
            "Note: path TRXLINK is not available. 11 columns are created and filled with nulls\n",
            "Note: path TRXLTC is not available. 11 columns are created and filled with nulls\n",
            "Note: path TRXMATIC is not available. 11 columns are created and filled with nulls\n",
            "Note: path TRXSOL is not available. 11 columns are created and filled with nulls\n",
            "Note: path TRXXLM is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for TRX-2023-4hr is downloaded to /content/drive/MyDrive/crypto/TRX-master-files/TRX-2023-4hr.csv\n",
            "\n",
            "Starting check for USDT-2023-4hr...\n",
            "All unix timestamps match!\n",
            "Master file for USDT-2023-4hr is downloaded to /content/drive/MyDrive/crypto/USDT-master-files/USDT-2023-4hr.csv\n",
            "\n",
            "Starting check for XLM-2023-4hr...\n",
            "Note: path XLMADA is not available. 11 columns are created and filled with nulls\n",
            "Note: path XLMAVAX is not available. 11 columns are created and filled with nulls\n",
            "Note: path XLMDAI is not available. 11 columns are created and filled with nulls\n",
            "Note: path XLMDOT is not available. 11 columns are created and filled with nulls\n",
            "Note: path XLMLINK is not available. 11 columns are created and filled with nulls\n",
            "Note: path XLMLTC is not available. 11 columns are created and filled with nulls\n",
            "Note: path XLMMATIC is not available. 11 columns are created and filled with nulls\n",
            "Note: path XLMSOL is not available. 11 columns are created and filled with nulls\n",
            "Note: path XLMTRX is not available. 11 columns are created and filled with nulls\n",
            "Note: path XLMXRP is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for XLM-2023-4hr is downloaded to /content/drive/MyDrive/crypto/XLM-master-files/XLM-2023-4hr.csv\n",
            "\n",
            "Starting check for XRP-2023-4hr...\n",
            "Note: path XRPADA is not available. 11 columns are created and filled with nulls\n",
            "Note: path XRPAVAX is not available. 11 columns are created and filled with nulls\n",
            "Note: path XRPDAI is not available. 11 columns are created and filled with nulls\n",
            "Note: path XRPDOT is not available. 11 columns are created and filled with nulls\n",
            "Note: path XRPLINK is not available. 11 columns are created and filled with nulls\n",
            "Note: path XRPLTC is not available. 11 columns are created and filled with nulls\n",
            "Note: path XRPMATIC is not available. 11 columns are created and filled with nulls\n",
            "Note: path XRPSOL is not available. 11 columns are created and filled with nulls\n",
            "Note: path XRPXLM is not available. 11 columns are created and filled with nulls\n",
            "All unix timestamps match!\n",
            "Master file for XRP-2023-4hr is downloaded to /content/drive/MyDrive/crypto/XRP-master-files/XRP-2023-4hr.csv\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# 4hr = 2004\n",
        "# 1hr = 8015\n",
        "# 30m = 5856\n",
        "# 5m = 35136\n",
        "# 1m = 175680\n",
        "years = [2023]\n",
        "intervals = [\"4hr\"]\n",
        "# lengths = [2004, 8015, 5856, 35136, 175680]\n",
        "master_coin = sorted(['ADA', 'AVAX', 'BNB', 'BTC', 'DAI', 'DOT', 'ETH', 'LINK', 'LTC', 'MATIC', 'SOL', 'TRX', 'USDT', 'XLM', 'XRP'])\n",
        "attached_coin = sorted(['ADA', 'AVAX', 'BNB', 'BTC', 'DAI', 'DOT', 'ETH', 'LINK', 'LTC', 'MATIC', 'SOL', 'TRX', 'USDT', 'XLM', 'XRP'])\n",
        "all_coins = []\n",
        "\n",
        "y = 0\n",
        "i = 0\n",
        "mc = 0\n",
        "while y < len(years):\n",
        "  print(\"___________________\"+str(years[y])+\"___________________ \\n\")\n",
        "\n",
        "  while i < len(intervals):\n",
        "\n",
        "    while mc < len(master_coin):\n",
        "      print(\"Starting check for {}-{}-{}...\".format(master_coin[mc], years[y], intervals[i]))\n",
        "      ac = 0\n",
        "\n",
        "      while ac < len(attached_coin):\n",
        "\n",
        "        frames = []\n",
        "\n",
        "        # Creating 1:1 dataframe for same coin path\n",
        "        if master_coin[mc] == attached_coin[ac]:\n",
        "          same = pd.DataFrame(index=np.arange(2004), columns=np.arange(11))\n",
        "          same.columns = [\"unix\", \"open\", \"high\", \"low\", \"close\", \"volume\", \"close_time\", \"quote_volume\", \"count\", \"tb_volume\", \"tb_quote_volume\"]\n",
        "          same.columns.values[0] = \"{}{} unix\".format(master_coin[mc], attached_coin[ac])\n",
        "          same[\"open\"] = 1\n",
        "          same[\"high\"] = 1\n",
        "          same[\"low\"] = 1\n",
        "          same[\"close\"] = 1\n",
        "          all_coins.append(same)\n",
        "          ac += 1\n",
        "\n",
        "        else:\n",
        "          try:\n",
        "            # Fetching existing CSV files\n",
        "            dir = \"/content/drive/MyDrive/crypto/{}/{}/{}/{}{}/\".format(years[y], intervals[i], master_coin[mc], master_coin[mc], attached_coin[ac])\n",
        "            fileslist = os.listdir(dir)\n",
        "            for file in sorted(fileslist):\n",
        "              frames.append(pd.read_csv(dir+file, header=None))\n",
        "\n",
        "            # Converting CSV file to a pandas DataFrame\n",
        "            master = pd.concat(frames)\n",
        "\n",
        "            # Transforming DataFrame format\n",
        "            master.columns = [\"unix\", \"open\", \"high\", \"low\", \"close\", \"volume\", \"close_time\", \"quote_volume\", \"count\", \"tb_volume\", \"tb_quote_volume\", \"ignore\"]\n",
        "            master.columns.values[0] = \"{}{} unix\".format(master_coin[mc], attached_coin[ac])\n",
        "            master[\"close_time\"] = master[\"close_time\"].astype(str)\n",
        "            master.reset_index(drop=True, inplace=True)\n",
        "            master.drop(columns=\"ignore\", inplace = True)\n",
        "\n",
        "            # Storing to list to concat all DataFrames later\n",
        "            all_coins.append(master)\n",
        "            ac += 1\n",
        "\n",
        "          except:\n",
        "            try:\n",
        "              # Creating reverse path\n",
        "              dir = \"/content/drive/MyDrive/crypto/{}/{}/{}/{}{}/\".format(years[y], intervals[i], attached_coin[ac], attached_coin[ac], master_coin[mc])\n",
        "              fileslist = os.listdir(dir)\n",
        "              for file in sorted(fileslist):\n",
        "                frames.append(pd.read_csv(dir+file, header=None))\n",
        "\n",
        "              master = pd.concat(frames)\n",
        "              master.columns = [\"unix\", \"open\", \"high\", \"low\", \"close\", \"volume\", \"close_time\", \"quote_volume\", \"count\", \"tb_volume\", \"tb_quote_volume\", \"ignore\"]\n",
        "              master.columns.values[0] = \"{}{} unix\".format(master_coin[mc], attached_coin[ac])\n",
        "              master[\"close_time\"] = master[\"close_time\"].astype(str)\n",
        "              master[\"open\"] = 1/master[\"open\"]\n",
        "              master[\"high\"] = 1/master[\"high\"]\n",
        "              master[\"low\"] = 1/master[\"low\"]\n",
        "              master[\"close\"] = 1/master[\"close\"]\n",
        "              master[\"volume\"] = np.nan\n",
        "              master[\"quote_volume\"] = np.nan\n",
        "              master[\"count\"] = np.nan\n",
        "              master[\"tb_volume\"] = np.nan\n",
        "              master[\"tb_quote_volume\"] = np.nan\n",
        "              master.drop(columns=\"ignore\", inplace = True)\n",
        "              master.reset_index(drop=True, inplace=True)\n",
        "\n",
        "              all_coins.append(master)\n",
        "              ac += 1\n",
        "\n",
        "            except:\n",
        "              # Creating blank dataframe for coins that are not available on K-line\n",
        "              missing = pd.DataFrame(index=np.arange(2004), columns=np.arange(11))\n",
        "              missing.columns = [\"unix\", \"open\", \"high\", \"low\", \"close\", \"volume\", \"close_time\", \"quote_volume\", \"count\", \"tb_volume\", \"tb_quote_volume\"]\n",
        "              missing.columns.values[0] = \"{}{} unix\".format(master_coin[mc], attached_coin[ac])\n",
        "              all_coins.append(missing)\n",
        "              print(\"Note: path {}{} is not available. 11 columns are created and filled with nulls\".format(master_coin[mc], attached_coin[ac]))\n",
        "              ac += 1\n",
        "              pass\n",
        "\n",
        "      # Checking whether all unix timestamps are same per row\n",
        "      df = pd.concat(all_coins, axis=1, ignore_index = False)\n",
        "      unix = df.iloc[:, ::11]\n",
        "      existing_unix = []\n",
        "      non_null_cols = unix.notnull().all()\n",
        "      for u in range(0, len(non_null_cols)):\n",
        "        if non_null_cols[u] == True:\n",
        "          existing_unix.append(non_null_cols.index[u])\n",
        "          check = df[existing_unix].eq(df[existing_unix].iloc[:, 0], axis=0).all(1)\n",
        "\n",
        "      if check.value_counts().index[0] == True and check.value_counts()[0] == len(df):\n",
        "        print(\"All unix timestamps match!\".format(years[y], intervals[i]))\n",
        "\n",
        "        # Fill in all unix NAs\n",
        "        for u in range(0, 15):\n",
        "          if df.filter(like=\"unix\", axis=1).isnull().all().value_counts()[0] == 15:\n",
        "            pass\n",
        "\n",
        "          else:\n",
        "            if df.filter(like=\"unix\", axis=1).isnull().all()[u] == True:\n",
        "              df[df.filter(like=\"unix\", axis=1).isnull().all().index[u]] = df[existing_unix[0]]\n",
        "\n",
        "            # Fill in all close time NAs\n",
        "            close_time = df.filter(like=\"close_time\", axis=1)\n",
        "            non_null_cols = close_time.notnull().all()\n",
        "            for ct in range(0, len(non_null_cols)):\n",
        "              if non_null_cols[ct] == True:\n",
        "                existing_ct = close_time.iloc[:, ct]\n",
        "\n",
        "            for ct in range(0, 15):\n",
        "              if df.filter(like=\"close_time\", axis=1).isnull().all()[ct] == True:\n",
        "                df[df.filter(like=\"close_time\", axis=1).isnull().all().index[ct]] = existing_ct\n",
        "\n",
        "        dir = \"/content/drive/MyDrive/crypto/master-files/{}\".format(master_coin[mc])\n",
        "        fname = \"{}-{}-{}.csv\".format(master_coin[mc], years[y], intervals[i])\n",
        "        df.to_csv(dir+fname, float_format='%.10f', index=False)\n",
        "        print(\"Master file for {}-{}-{} is downloaded to \".format(master_coin[mc], years[y], intervals[i])+dir+fname+\"\\n\")\n",
        "      else:\n",
        "        print(\"WARNING: Mismatching unix occured! \\n\")\n",
        "        dir = \"/content/drive/MyDrive/crypto/master-files/{}\".format(master_coin[mc])\n",
        "        fname = \"{}-{}-{}.csv\".format(master_coin[mc], years[y], intervals[i])\n",
        "        df.to_csv(dir+fname, float_format='%.10f', index=False)\n",
        "        print(\"Master file for {}-{}-{} is downloaded to \".format(master_coin[mc], years[y], intervals[i])+dir+fname+\"\\n\")\n",
        "\n",
        "      del master\n",
        "      all_coins = []\n",
        "      mc += 1\n",
        "    i += 1\n",
        "  y += 1\n",
        "  i = 0"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 461
        },
        "id": "iy0lhTLl2Xdi",
        "outputId": "3e0ec350-aa7e-4c7f-fb25-595f3c848925"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-0df8fc15-6aa7-4dd9-91dd-ddb31e8542bb\" 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>BNBADA unix</th>\n",
              "      <th>BNBAVAX unix</th>\n",
              "      <th>BNBBNB unix</th>\n",
              "      <th>BNBBTC unix</th>\n",
              "      <th>BNBDAI unix</th>\n",
              "      <th>BNBDOT unix</th>\n",
              "      <th>BNBETH unix</th>\n",
              "      <th>BNBLINK unix</th>\n",
              "      <th>BNBLTC unix</th>\n",
              "      <th>BNBMATIC unix</th>\n",
              "      <th>BNBSOL unix</th>\n",
              "      <th>BNBTRX unix</th>\n",
              "      <th>BNBUSDT unix</th>\n",
              "      <th>BNBXLM unix</th>\n",
              "      <th>BNBXRP unix</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "      <td>1690848000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "      <td>1690849800000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "      <td>1690851600000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "      <td>1690853400000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "      <td>1690855200000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5851</th>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "      <td>1701379800000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5852</th>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "      <td>1701381600000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5853</th>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "      <td>1701383400000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5854</th>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "      <td>1701385200000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5855</th>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "      <td>1701387000000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5856 rows × 15 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-0df8fc15-6aa7-4dd9-91dd-ddb31e8542bb')\"\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-0df8fc15-6aa7-4dd9-91dd-ddb31e8542bb 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-0df8fc15-6aa7-4dd9-91dd-ddb31e8542bb');\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-db211bc7-3e36-4dd5-b45e-2a1512e484d5\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-db211bc7-3e36-4dd5-b45e-2a1512e484d5')\"\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-db211bc7-3e36-4dd5-b45e-2a1512e484d5 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "text/plain": [
              "        BNBADA unix   BNBAVAX unix    BNBBNB unix    BNBBTC unix  \\\n",
              "0     1690848000000  1690848000000  1690848000000  1690848000000   \n",
              "1     1690849800000  1690849800000  1690849800000  1690849800000   \n",
              "2     1690851600000  1690851600000  1690851600000  1690851600000   \n",
              "3     1690853400000  1690853400000  1690853400000  1690853400000   \n",
              "4     1690855200000  1690855200000  1690855200000  1690855200000   \n",
              "...             ...            ...            ...            ...   \n",
              "5851  1701379800000  1701379800000  1701379800000  1701379800000   \n",
              "5852  1701381600000  1701381600000  1701381600000  1701381600000   \n",
              "5853  1701383400000  1701383400000  1701383400000  1701383400000   \n",
              "5854  1701385200000  1701385200000  1701385200000  1701385200000   \n",
              "5855  1701387000000  1701387000000  1701387000000  1701387000000   \n",
              "\n",
              "        BNBDAI unix    BNBDOT unix    BNBETH unix   BNBLINK unix  \\\n",
              "0     1690848000000  1690848000000  1690848000000  1690848000000   \n",
              "1     1690849800000  1690849800000  1690849800000  1690849800000   \n",
              "2     1690851600000  1690851600000  1690851600000  1690851600000   \n",
              "3     1690853400000  1690853400000  1690853400000  1690853400000   \n",
              "4     1690855200000  1690855200000  1690855200000  1690855200000   \n",
              "...             ...            ...            ...            ...   \n",
              "5851  1701379800000  1701379800000  1701379800000  1701379800000   \n",
              "5852  1701381600000  1701381600000  1701381600000  1701381600000   \n",
              "5853  1701383400000  1701383400000  1701383400000  1701383400000   \n",
              "5854  1701385200000  1701385200000  1701385200000  1701385200000   \n",
              "5855  1701387000000  1701387000000  1701387000000  1701387000000   \n",
              "\n",
              "        BNBLTC unix  BNBMATIC unix    BNBSOL unix    BNBTRX unix  \\\n",
              "0     1690848000000  1690848000000  1690848000000  1690848000000   \n",
              "1     1690849800000  1690849800000  1690849800000  1690849800000   \n",
              "2     1690851600000  1690851600000  1690851600000  1690851600000   \n",
              "3     1690853400000  1690853400000  1690853400000  1690853400000   \n",
              "4     1690855200000  1690855200000  1690855200000  1690855200000   \n",
              "...             ...            ...            ...            ...   \n",
              "5851  1701379800000  1701379800000  1701379800000  1701379800000   \n",
              "5852  1701381600000  1701381600000  1701381600000  1701381600000   \n",
              "5853  1701383400000  1701383400000  1701383400000  1701383400000   \n",
              "5854  1701385200000  1701385200000  1701385200000  1701385200000   \n",
              "5855  1701387000000  1701387000000  1701387000000  1701387000000   \n",
              "\n",
              "       BNBUSDT unix    BNBXLM unix    BNBXRP unix  \n",
              "0     1690848000000  1690848000000  1690848000000  \n",
              "1     1690849800000  1690849800000  1690849800000  \n",
              "2     1690851600000  1690851600000  1690851600000  \n",
              "3     1690853400000  1690853400000  1690853400000  \n",
              "4     1690855200000  1690855200000  1690855200000  \n",
              "...             ...            ...            ...  \n",
              "5851  1701379800000  1701379800000  1701379800000  \n",
              "5852  1701381600000  1701381600000  1701381600000  \n",
              "5853  1701383400000  1701383400000  1701383400000  \n",
              "5854  1701385200000  1701385200000  1701385200000  \n",
              "5855  1701387000000  1701387000000  1701387000000  \n",
              "\n",
              "[5856 rows x 15 columns]"
            ]
          },
          "execution_count": 3,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "test = pd.read_csv(\"/content/drive/MyDrive/crypto/master-files/BNB/BNB-2023-30m.csv\")\n",
        "test.iloc[:,::11]"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}