GSC_Opportunities.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "GSC Opportunities.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true,
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "accelerator": "GPU"
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/BritneyMuller/colab-notebooks/blob/master/GSC_Opportunities.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "G7y0mSAmOKrV",
        "colab_type": "text"
      },
      "source": [
        "# GSC Opportunities\n",
        "\n",
        "Made by [![Follow](https://img.shields.io/twitter/follow/BritneyMuller?style=social)](https://twitter.com/BritneyMuller)\n",
        "\n",
        "Please contact britneymuller@gmail.com with Subject [Colab Link Analysis] with any questions.\n",
        "\n",
        "Explore: [github.com/BritneyMuller/colab-notebooks ](https://github.com/BritneyMuller/colab-notebooks)for more Notebook examples"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Y59nEdqJO7Wg",
        "colab_type": "text"
      },
      "source": [
        "# Upload GSC Data\n",
        "\n",
        "\n",
        "![Export internal link csv to your local computer](https://i.imgur.com/J9y04cL.png)\n",
        "\n",
        "---\n",
        "\n",
        "\n",
        "\n",
        "Note: [Shift + Return] is the shortcut to run a single cell. \n",
        "\n",
        "Try running the code below by clicking into the cell and doing [Shift + Return].\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dOGPtWU6fAKR",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import csv\n",
        "import json\n",
        "import requests\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "import re\n",
        "from IPython.display import display"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TSOQ1M2kO1F_",
        "colab_type": "code",
        "colab": {
          "resources": {
            "http://localhost:8080/nbextensions/google.colab/files.js": {
              "data": "Ly8gQ29weXJpZ2h0IDIwMTcgR29vZ2xlIExMQwovLwovLyBMaWNlbnNlZCB1bmRlciB0aGUgQXBhY2hlIExpY2Vuc2UsIFZlcnNpb24gMi4wICh0aGUgIkxpY2Vuc2UiKTsKLy8geW91IG1heSBub3QgdXNlIHRoaXMgZmlsZSBleGNlcHQgaW4gY29tcGxpYW5jZSB3aXRoIHRoZSBMaWNlbnNlLgovLyBZb3UgbWF5IG9idGFpbiBhIGNvcHkgb2YgdGhlIExpY2Vuc2UgYXQKLy8KLy8gICAgICBodHRwOi8vd3d3LmFwYWNoZS5vcmcvbGljZW5zZXMvTElDRU5TRS0yLjAKLy8KLy8gVW5sZXNzIHJlcXVpcmVkIGJ5IGFwcGxpY2FibGUgbGF3IG9yIGFncmVlZCB0byBpbiB3cml0aW5nLCBzb2Z0d2FyZQovLyBkaXN0cmlidXRlZCB1bmRlciB0aGUgTGljZW5zZSBpcyBkaXN0cmlidXRlZCBvbiBhbiAiQVMgSVMiIEJBU0lTLAovLyBXSVRIT1VUIFdBUlJBTlRJRVMgT1IgQ09ORElUSU9OUyBPRiBBTlkgS0lORCwgZWl0aGVyIGV4cHJlc3Mgb3IgaW1wbGllZC4KLy8gU2VlIHRoZSBMaWNlbnNlIGZvciB0aGUgc3BlY2lmaWMgbGFuZ3VhZ2UgZ292ZXJuaW5nIHBlcm1pc3Npb25zIGFuZAovLyBsaW1pdGF0aW9ucyB1bmRlciB0aGUgTGljZW5zZS4KCi8qKgogKiBAZmlsZW92ZXJ2aWV3IEhlbHBlcnMgZm9yIGdvb2dsZS5jb2xhYiBQeXRob24gbW9kdWxlLgogKi8KKGZ1bmN0aW9uKHNjb3BlKSB7CmZ1bmN0aW9uIHNwYW4odGV4dCwgc3R5bGVBdHRyaWJ1dGVzID0ge30pIHsKICBjb25zdCBlbGVtZW50ID0gZG9jdW1lbnQuY3JlYXRlRWxlbWVudCgnc3BhbicpOwogIGVsZW1lbnQudGV4dENvbnRlbnQgPSB0ZXh0OwogIGZvciAoY29uc3Qga2V5IG9mIE9iamVjdC5rZXlzKHN0eWxlQXR0cmlidXRlcykpIHsKICAgIGVsZW1lbnQuc3R5bGVba2V5XSA9IHN0eWxlQXR0cmlidXRlc1trZXldOwogIH0KICByZXR1cm4gZWxlbWVudDsKfQoKLy8gTWF4IG51bWJlciBvZiBieXRlcyB3aGljaCB3aWxsIGJlIHVwbG9hZGVkIGF0IGEgdGltZS4KY29uc3QgTUFYX1BBWUxPQURfU0laRSA9IDEwMCAqIDEwMjQ7CgpmdW5jdGlvbiBfdXBsb2FkRmlsZXMoaW5wdXRJZCwgb3V0cHV0SWQpIHsKICBjb25zdCBzdGVwcyA9IHVwbG9hZEZpbGVzU3RlcChpbnB1dElkLCBvdXRwdXRJZCk7CiAgY29uc3Qgb3V0cHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKG91dHB1dElkKTsKICAvLyBDYWNoZSBzdGVwcyBvbiB0aGUgb3V0cHV0RWxlbWVudCB0byBtYWtlIGl0IGF2YWlsYWJsZSBmb3IgdGhlIG5leHQgY2FsbAogIC8vIHRvIHVwbG9hZEZpbGVzQ29udGludWUgZnJvbSBQeXRob24uCiAgb3V0cHV0RWxlbWVudC5zdGVwcyA9IHN0ZXBzOwoKICByZXR1cm4gX3VwbG9hZEZpbGVzQ29udGludWUob3V0cHV0SWQpOwp9CgovLyBUaGlzIGlzIHJvdWdobHkgYW4gYXN5bmMgZ2VuZXJhdG9yIChub3Qgc3VwcG9ydGVkIGluIHRoZSBicm93c2VyIHlldCksCi8vIHdoZXJlIHRoZXJlIGFyZSBtdWx0aXBsZSBhc3luY2hyb25vdXMgc3RlcHMgYW5kIHRoZSBQeXRob24gc2lkZSBpcyBnb2luZwovLyB0byBwb2xsIGZvciBjb21wbGV0aW9uIG9mIGVhY2ggc3RlcC4KLy8gVGhpcyB1c2VzIGEgUHJvbWlzZSB0byBibG9jayB0aGUgcHl0aG9uIHNpZGUgb24gY29tcGxldGlvbiBvZiBlYWNoIHN0ZXAsCi8vIHRoZW4gcGFzc2VzIHRoZSByZXN1bHQgb2YgdGhlIHByZXZpb3VzIHN0ZXAgYXMgdGhlIGlucHV0IHRvIHRoZSBuZXh0IHN0ZXAuCmZ1bmN0aW9uIF91cGxvYWRGaWxlc0NvbnRpbnVlKG91dHB1dElkKSB7CiAgY29uc3Qgb3V0cHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKG91dHB1dElkKTsKICBjb25zdCBzdGVwcyA9IG91dHB1dEVsZW1lbnQuc3RlcHM7CgogIGNvbnN0IG5leHQgPSBzdGVwcy5uZXh0KG91dHB1dEVsZW1lbnQubGFzdFByb21pc2VWYWx1ZSk7CiAgcmV0dXJuIFByb21pc2UucmVzb2x2ZShuZXh0LnZhbHVlLnByb21pc2UpLnRoZW4oKHZhbHVlKSA9PiB7CiAgICAvLyBDYWNoZSB0aGUgbGFzdCBwcm9taXNlIHZhbHVlIHRvIG1ha2UgaXQgYXZhaWxhYmxlIHRvIHRoZSBuZXh0CiAgICAvLyBzdGVwIG9mIHRoZSBnZW5lcmF0b3IuCiAgICBvdXRwdXRFbGVtZW50Lmxhc3RQcm9taXNlVmFsdWUgPSB2YWx1ZTsKICAgIHJldHVybiBuZXh0LnZhbHVlLnJlc3BvbnNlOwogIH0pOwp9CgovKioKICogR2VuZXJhdG9yIGZ1bmN0aW9uIHdoaWNoIGlzIGNhbGxlZCBiZXR3ZWVuIGVhY2ggYXN5bmMgc3RlcCBvZiB0aGUgdXBsb2FkCiAqIHByb2Nlc3MuCiAqIEBwYXJhbSB7c3RyaW5nfSBpbnB1dElkIEVsZW1lbnQgSUQgb2YgdGhlIGlucHV0IGZpbGUgcGlja2VyIGVsZW1lbnQuCiAqIEBwYXJhbSB7c3RyaW5nfSBvdXRwdXRJZCBFbGVtZW50IElEIG9mIHRoZSBvdXRwdXQgZGlzcGxheS4KICogQHJldHVybiB7IUl0ZXJhYmxlPCFPYmplY3Q+fSBJdGVyYWJsZSBvZiBuZXh0IHN0ZXBzLgogKi8KZnVuY3Rpb24qIHVwbG9hZEZpbGVzU3RlcChpbnB1dElkLCBvdXRwdXRJZCkgewogIGNvbnN0IGlucHV0RWxlbWVudCA9IGRvY3VtZW50LmdldEVsZW1lbnRCeUlkKGlucHV0SWQpOwogIGlucHV0RWxlbWVudC5kaXNhYmxlZCA9IGZhbHNlOwoKICBjb25zdCBvdXRwdXRFbGVtZW50ID0gZG9jdW1lbnQuZ2V0RWxlbWVudEJ5SWQob3V0cHV0SWQpOwogIG91dHB1dEVsZW1lbnQuaW5uZXJIVE1MID0gJyc7CgogIGNvbnN0IHBpY2tlZFByb21pc2UgPSBuZXcgUHJvbWlzZSgocmVzb2x2ZSkgPT4gewogICAgaW5wdXRFbGVtZW50LmFkZEV2ZW50TGlzdGVuZXIoJ2NoYW5nZScsIChlKSA9PiB7CiAgICAgIHJlc29sdmUoZS50YXJnZXQuZmlsZXMpOwogICAgfSk7CiAgfSk7CgogIGNvbnN0IGNhbmNlbCA9IGRvY3VtZW50LmNyZWF0ZUVsZW1lbnQoJ2J1dHRvbicpOwogIGlucHV0RWxlbWVudC5wYXJlbnRFbGVtZW50LmFwcGVuZENoaWxkKGNhbmNlbCk7CiAgY2FuY2VsLnRleHRDb250ZW50ID0gJ0NhbmNlbCB1cGxvYWQnOwogIGNvbnN0IGNhbmNlbFByb21pc2UgPSBuZXcgUHJvbWlzZSgocmVzb2x2ZSkgPT4gewogICAgY2FuY2VsLm9uY2xpY2sgPSAoKSA9PiB7CiAgICAgIHJlc29sdmUobnVsbCk7CiAgICB9OwogIH0pOwoKICAvLyBXYWl0IGZvciB0aGUgdXNlciB0byBwaWNrIHRoZSBmaWxlcy4KICBjb25zdCBmaWxlcyA9IHlpZWxkIHsKICAgIHByb21pc2U6IFByb21pc2UucmFjZShbcGlja2VkUHJvbWlzZSwgY2FuY2VsUHJvbWlzZV0pLAogICAgcmVzcG9uc2U6IHsKICAgICAgYWN0aW9uOiAnc3RhcnRpbmcnLAogICAgfQogIH07CgogIGNhbmNlbC5yZW1vdmUoKTsKCiAgLy8gRGlzYWJsZSB0aGUgaW5wdXQgZWxlbWVudCBzaW5jZSBmdXJ0aGVyIHBpY2tzIGFyZSBub3QgYWxsb3dlZC4KICBpbnB1dEVsZW1lbnQuZGlzYWJsZWQgPSB0cnVlOwoKICBpZiAoIWZpbGVzKSB7CiAgICByZXR1cm4gewogICAgICByZXNwb25zZTogewogICAgICAgIGFjdGlvbjogJ2NvbXBsZXRlJywKICAgICAgfQogICAgfTsKICB9CgogIGZvciAoY29uc3QgZmlsZSBvZiBmaWxlcykgewogICAgY29uc3QgbGkgPSBkb2N1bWVudC5jcmVhdGVFbGVtZW50KCdsaScpOwogICAgbGkuYXBwZW5kKHNwYW4oZmlsZS5uYW1lLCB7Zm9udFdlaWdodDogJ2JvbGQnfSkpOwogICAgbGkuYXBwZW5kKHNwYW4oCiAgICAgICAgYCgke2ZpbGUudHlwZSB8fCAnbi9hJ30pIC0gJHtmaWxlLnNpemV9IGJ5dGVzLCBgICsKICAgICAgICBgbGFzdCBtb2RpZmllZDogJHsKICAgICAgICAgICAgZmlsZS5sYXN0TW9kaWZpZWREYXRlID8gZmlsZS5sYXN0TW9kaWZpZWREYXRlLnRvTG9jYWxlRGF0ZVN0cmluZygpIDoKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgJ24vYSd9IC0gYCkpOwogICAgY29uc3QgcGVyY2VudCA9IHNwYW4oJzAlIGRvbmUnKTsKICAgIGxpLmFwcGVuZENoaWxkKHBlcmNlbnQpOwoKICAgIG91dHB1dEVsZW1lbnQuYXBwZW5kQ2hpbGQobGkpOwoKICAgIGNvbnN0IGZpbGVEYXRhUHJvbWlzZSA9IG5ldyBQcm9taXNlKChyZXNvbHZlKSA9PiB7CiAgICAgIGNvbnN0IHJlYWRlciA9IG5ldyBGaWxlUmVhZGVyKCk7CiAgICAgIHJlYWRlci5vbmxvYWQgPSAoZSkgPT4gewogICAgICAgIHJlc29sdmUoZS50YXJnZXQucmVzdWx0KTsKICAgICAgfTsKICAgICAgcmVhZGVyLnJlYWRBc0FycmF5QnVmZmVyKGZpbGUpOwogICAgfSk7CiAgICAvLyBXYWl0IGZvciB0aGUgZGF0YSB0byBiZSByZWFkeS4KICAgIGxldCBmaWxlRGF0YSA9IHlpZWxkIHsKICAgICAgcHJvbWlzZTogZmlsZURhdGFQcm9taXNlLAogICAgICByZXNwb25zZTogewogICAgICAgIGFjdGlvbjogJ2NvbnRpbnVlJywKICAgICAgfQogICAgfTsKCiAgICAvLyBVc2UgYSBjaHVua2VkIHNlbmRpbmcgdG8gYXZvaWQgbWVzc2FnZSBzaXplIGxpbWl0cy4gU2VlIGIvNjIxMTU2NjAuCiAgICBsZXQgcG9zaXRpb24gPSAwOwogICAgd2hpbGUgKHBvc2l0aW9uIDwgZmlsZURhdGEuYnl0ZUxlbmd0aCkgewogICAgICBjb25zdCBsZW5ndGggPSBNYXRoLm1pbihmaWxlRGF0YS5ieXRlTGVuZ3RoIC0gcG9zaXRpb24sIE1BWF9QQVlMT0FEX1NJWkUpOwogICAgICBjb25zdCBjaHVuayA9IG5ldyBVaW50OEFycmF5KGZpbGVEYXRhLCBwb3NpdGlvbiwgbGVuZ3RoKTsKICAgICAgcG9zaXRpb24gKz0gbGVuZ3RoOwoKICAgICAgY29uc3QgYmFzZTY0ID0gYnRvYShTdHJpbmcuZnJvbUNoYXJDb2RlLmFwcGx5KG51bGwsIGNodW5rKSk7CiAgICAgIHlpZWxkIHsKICAgICAgICByZXNwb25zZTogewogICAgICAgICAgYWN0aW9uOiAnYXBwZW5kJywKICAgICAgICAgIGZpbGU6IGZpbGUubmFtZSwKICAgICAgICAgIGRhdGE6IGJhc2U2NCwKICAgICAgICB9LAogICAgICB9OwogICAgICBwZXJjZW50LnRleHRDb250ZW50ID0KICAgICAgICAgIGAke01hdGgucm91bmQoKHBvc2l0aW9uIC8gZmlsZURhdGEuYnl0ZUxlbmd0aCkgKiAxMDApfSUgZG9uZWA7CiAgICB9CiAgfQoKICAvLyBBbGwgZG9uZS4KICB5aWVsZCB7CiAgICByZXNwb25zZTogewogICAgICBhY3Rpb246ICdjb21wbGV0ZScsCiAgICB9CiAgfTsKfQoKc2NvcGUuZ29vZ2xlID0gc2NvcGUuZ29vZ2xlIHx8IHt9OwpzY29wZS5nb29nbGUuY29sYWIgPSBzY29wZS5nb29nbGUuY29sYWIgfHwge307CnNjb3BlLmdvb2dsZS5jb2xhYi5fZmlsZXMgPSB7CiAgX3VwbG9hZEZpbGVzLAogIF91cGxvYWRGaWxlc0NvbnRpbnVlLAp9Owp9KShzZWxmKTsK",
              "ok": true,
              "headers": [
                [
                  "content-type",
                  "application/javascript"
                ]
              ],
              "status": 200,
              "status_text": ""
            }
          },
          "base_uri": "https://localhost:8080/",
          "height": 73
        },
        "outputId": "37ea0eb2-4415-44ef-a831-2d46d60730a3"
      },
      "source": [
        "from google.colab import files\n",
        "uploaded = files.upload()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "\n",
              "     <input type=\"file\" id=\"files-eb6cc82a-f357-475c-afc5-55085466aebc\" name=\"files[]\" multiple disabled\n",
              "        style=\"border:none\" />\n",
              "     <output id=\"result-eb6cc82a-f357-475c-afc5-55085466aebc\">\n",
              "      Upload widget is only available when the cell has been executed in the\n",
              "      current browser session. Please rerun this cell to enable.\n",
              "      </output>\n",
              "      <script src=\"/nbextensions/google.colab/files.js\"></script> "
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "stream",
          "text": [
            "Saving gsc-example.csv to gsc-example (1).csv\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DvhbitJsgn3A",
        "colab_type": "text"
      },
      "source": [
        "Upload data as \"df\" (dataframe)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5bwFFew5O4_y",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df = pd.read_csv(\"gsc-example.csv\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B1d2HJG0gt4P",
        "colab_type": "text"
      },
      "source": [
        "Look at the first 5 lines (this includes the header)\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PAuO2kzLe5QU",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 197
        },
        "outputId": "7dfd070e-71b9-486b-cca1-dc32d4be25ff"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Query</th>\n",
              "      <th>Clicks</th>\n",
              "      <th>Impressions</th>\n",
              "      <th>CTR</th>\n",
              "      <th>Position</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>moz</td>\n",
              "      <td>153700</td>\n",
              "      <td>379698</td>\n",
              "      <td>40.48%</td>\n",
              "      <td>1.36</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>seo</td>\n",
              "      <td>60101</td>\n",
              "      <td>1797545</td>\n",
              "      <td>3.34%</td>\n",
              "      <td>5.91</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>search engine optimization</td>\n",
              "      <td>28049</td>\n",
              "      <td>378850</td>\n",
              "      <td>7.4%</td>\n",
              "      <td>2.57</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>what is seo</td>\n",
              "      <td>26103</td>\n",
              "      <td>232252</td>\n",
              "      <td>11.24%</td>\n",
              "      <td>1.35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>mozbar</td>\n",
              "      <td>22275</td>\n",
              "      <td>52886</td>\n",
              "      <td>42.12%</td>\n",
              "      <td>1.13</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                        Query  Clicks  Impressions     CTR  Position\n",
              "0                         moz  153700       379698  40.48%      1.36\n",
              "1                         seo   60101      1797545   3.34%      5.91\n",
              "2  search engine optimization   28049       378850    7.4%      2.57\n",
              "3                 what is seo   26103       232252  11.24%      1.35\n",
              "4                      mozbar   22275        52886  42.12%      1.13"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sum4dPoukwW4",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 197
        },
        "outputId": "83df704a-c00f-432c-8a56-443100da1a03"
      },
      "source": [
        "df.tail()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Query</th>\n",
              "      <th>Clicks</th>\n",
              "      <th>Impressions</th>\n",
              "      <th>CTR</th>\n",
              "      <th>Position</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>moz keyword explorer</td>\n",
              "      <td>15302</td>\n",
              "      <td>29006</td>\n",
              "      <td>52.75%</td>\n",
              "      <td>1.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>moz pro</td>\n",
              "      <td>15043</td>\n",
              "      <td>32931</td>\n",
              "      <td>45.68%</td>\n",
              "      <td>1.36</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>domain authority checker</td>\n",
              "      <td>13928</td>\n",
              "      <td>149709</td>\n",
              "      <td>9.3%</td>\n",
              "      <td>4.31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>keyword explorer</td>\n",
              "      <td>13083</td>\n",
              "      <td>43957</td>\n",
              "      <td>29.76%</td>\n",
              "      <td>1.06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>moz seo</td>\n",
              "      <td>12346</td>\n",
              "      <td>31242</td>\n",
              "      <td>39.52%</td>\n",
              "      <td>2.98</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                       Query  Clicks  Impressions     CTR  Position\n",
              "8       moz keyword explorer   15302        29006  52.75%      1.00\n",
              "9                    moz pro   15043        32931  45.68%      1.36\n",
              "10  domain authority checker   13928       149709    9.3%      4.31\n",
              "11          keyword explorer   13083        43957  29.76%      1.06\n",
              "12                   moz seo   12346        31242  39.52%      2.98"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "z9GaE8SJi89u",
        "colab_type": "text"
      },
      "source": [
        "## Find all Queries with 'X' keyword\n",
        "This example shows all keywords with \"seo\""
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LbuhQAbvhk_P",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 167
        },
        "outputId": "1e3f5787-f6b0-4572-a157-881655ab1dc2"
      },
      "source": [
        "df_filtered = df[(df['Query'].str.contains(\"seo\", regex=True)==True)]\n",
        "df_filtered.head(100)"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Query</th>\n",
              "      <th>Clicks</th>\n",
              "      <th>Impressions</th>\n",
              "      <th>CTR</th>\n",
              "      <th>Position</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>seo</td>\n",
              "      <td>60101</td>\n",
              "      <td>1797545</td>\n",
              "      <td>3.34%</td>\n",
              "      <td>5.91</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>what is seo</td>\n",
              "      <td>26103</td>\n",
              "      <td>232252</td>\n",
              "      <td>11.24%</td>\n",
              "      <td>1.35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>seo tools</td>\n",
              "      <td>16312</td>\n",
              "      <td>158546</td>\n",
              "      <td>10.29%</td>\n",
              "      <td>3.04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>moz seo</td>\n",
              "      <td>12346</td>\n",
              "      <td>31242</td>\n",
              "      <td>39.52%</td>\n",
              "      <td>2.98</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "          Query  Clicks  Impressions     CTR  Position\n",
              "1           seo   60101      1797545   3.34%      5.91\n",
              "3   what is seo   26103       232252  11.24%      1.35\n",
              "6     seo tools   16312       158546  10.29%      3.04\n",
              "12      moz seo   12346        31242  39.52%      2.98"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9M-wgv-vwKqM",
        "colab_type": "text"
      },
      "source": [
        "#Load Filter-able Data Table"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VnQX_Xs5o7uX",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 497
        },
        "outputId": "68497715-07ae-411d-8706-7603049d999a"
      },
      "source": [
        "%load_ext google.colab.data_table\n",
        "\n",
        "df"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.module+javascript": "\n      import \"https://ssl.gstatic.com/colaboratory/data_table/a6224c040fa35dcf/data_table.js\";\n\n      window.createDataTable({\n        data: [[{\n            'v': 0,\n            'f': \"0\",\n        },\n\"moz\",\n{\n            'v': 153700,\n            'f': \"153700\",\n        },\n{\n            'v': 379698,\n            'f': \"379698\",\n        },\n\"40.48%\",\n{\n            'v': 1.36,\n            'f': \"1.36\",\n        }],\n [{\n            'v': 1,\n            'f': \"1\",\n        },\n\"seo\",\n{\n            'v': 60101,\n            'f': \"60101\",\n        },\n{\n            'v': 1797545,\n            'f': \"1797545\",\n        },\n\"3.34%\",\n{\n            'v': 5.91,\n            'f': \"5.91\",\n        }],\n [{\n            'v': 2,\n            'f': \"2\",\n        },\n\"search engine optimization\",\n{\n            'v': 28049,\n            'f': \"28049\",\n        },\n{\n            'v': 378850,\n            'f': \"378850\",\n        },\n\"7.4%\",\n{\n            'v': 2.57,\n            'f': \"2.57\",\n        }],\n [{\n            'v': 3,\n            'f': \"3\",\n        },\n\"what is seo\",\n{\n            'v': 26103,\n            'f': \"26103\",\n        },\n{\n            'v': 232252,\n            'f': \"232252\",\n        },\n\"11.24%\",\n{\n            'v': 1.35,\n            'f': \"1.35\",\n        }],\n [{\n            'v': 4,\n            'f': \"4\",\n        },\n\"mozbar\",\n{\n            'v': 22275,\n            'f': \"22275\",\n        },\n{\n            'v': 52886,\n            'f': \"52886\",\n        },\n\"42.12%\",\n{\n            'v': 1.13,\n            'f': \"1.13\",\n        }],\n [{\n            'v': 5,\n            'f': \"5\",\n        },\n\"keyword research\",\n{\n            'v': 17030,\n            'f': \"17030\",\n        },\n{\n            'v': 159687,\n            'f': \"159687\",\n        },\n\"10.66%\",\n{\n            'v': 3.12,\n            'f': \"3.12\",\n        }],\n [{\n            'v': 6,\n            'f': \"6\",\n        },\n\"seo tools\",\n{\n            'v': 16312,\n            'f': \"16312\",\n        },\n{\n            'v': 158546,\n            'f': \"158546\",\n        },\n\"10.29%\",\n{\n            'v': 3.04,\n            'f': \"3.04\",\n        }],\n [{\n            'v': 7,\n            'f': \"7\",\n        },\n\"domain authority\",\n{\n            'v': 15969,\n            'f': \"15969\",\n        },\n{\n            'v': 68647,\n            'f': \"68647\",\n        },\n\"23.26%\",\n{\n            'v': 1.51,\n            'f': \"1.51\",\n        }],\n [{\n            'v': 8,\n            'f': \"8\",\n        },\n\"moz keyword explorer\",\n{\n            'v': 15302,\n            'f': \"15302\",\n        },\n{\n            'v': 29006,\n            'f': \"29006\",\n        },\n\"52.75%\",\n{\n            'v': 1.0,\n            'f': \"1.0\",\n        }],\n [{\n            'v': 9,\n            'f': \"9\",\n        },\n\"moz pro\",\n{\n            'v': 15043,\n            'f': \"15043\",\n        },\n{\n            'v': 32931,\n            'f': \"32931\",\n        },\n\"45.68%\",\n{\n            'v': 1.36,\n            'f': \"1.36\",\n        }],\n [{\n            'v': 10,\n            'f': \"10\",\n        },\n\"domain authority checker\",\n{\n            'v': 13928,\n            'f': \"13928\",\n        },\n{\n            'v': 149709,\n            'f': \"149709\",\n        },\n\"9.3%\",\n{\n            'v': 4.31,\n            'f': \"4.31\",\n        }],\n [{\n            'v': 11,\n            'f': \"11\",\n        },\n\"keyword explorer\",\n{\n            'v': 13083,\n            'f': \"13083\",\n        },\n{\n            'v': 43957,\n            'f': \"43957\",\n        },\n\"29.76%\",\n{\n            'v': 1.06,\n            'f': \"1.06\",\n        }],\n [{\n            'v': 12,\n            'f': \"12\",\n        },\n\"moz seo\",\n{\n            'v': 12346,\n            'f': \"12346\",\n        },\n{\n            'v': 31242,\n            'f': \"31242\",\n        },\n\"39.52%\",\n{\n            'v': 2.98,\n            'f': \"2.98\",\n        }]],\n        columns: [[\"number\", \"index\"], [\"string\", \"Query\"], [\"number\", \"Clicks\"], [\"number\", \"Impressions\"], [\"string\", \"CTR\"], [\"number\", \"Position\"]],\n        columnOptions: [{\"width\": \"1px\", \"className\": \"index_column\"}],\n        rowsPerPage: 25,\n        helpUrl: \"https://colab.research.google.com/notebooks/data_table.ipynb\",\n        suppressOutputScrolling: true,\n        minimumWidth: undefined,\n      });\n    ",
            "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>Query</th>\n",
              "      <th>Clicks</th>\n",
              "      <th>Impressions</th>\n",
              "      <th>CTR</th>\n",
              "      <th>Position</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>moz</td>\n",
              "      <td>153700</td>\n",
              "      <td>379698</td>\n",
              "      <td>40.48%</td>\n",
              "      <td>1.36</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>seo</td>\n",
              "      <td>60101</td>\n",
              "      <td>1797545</td>\n",
              "      <td>3.34%</td>\n",
              "      <td>5.91</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>search engine optimization</td>\n",
              "      <td>28049</td>\n",
              "      <td>378850</td>\n",
              "      <td>7.4%</td>\n",
              "      <td>2.57</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>what is seo</td>\n",
              "      <td>26103</td>\n",
              "      <td>232252</td>\n",
              "      <td>11.24%</td>\n",
              "      <td>1.35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>mozbar</td>\n",
              "      <td>22275</td>\n",
              "      <td>52886</td>\n",
              "      <td>42.12%</td>\n",
              "      <td>1.13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>keyword research</td>\n",
              "      <td>17030</td>\n",
              "      <td>159687</td>\n",
              "      <td>10.66%</td>\n",
              "      <td>3.12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>seo tools</td>\n",
              "      <td>16312</td>\n",
              "      <td>158546</td>\n",
              "      <td>10.29%</td>\n",
              "      <td>3.04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>domain authority</td>\n",
              "      <td>15969</td>\n",
              "      <td>68647</td>\n",
              "      <td>23.26%</td>\n",
              "      <td>1.51</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>moz keyword explorer</td>\n",
              "      <td>15302</td>\n",
              "      <td>29006</td>\n",
              "      <td>52.75%</td>\n",
              "      <td>1.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>moz pro</td>\n",
              "      <td>15043</td>\n",
              "      <td>32931</td>\n",
              "      <td>45.68%</td>\n",
              "      <td>1.36</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>domain authority checker</td>\n",
              "      <td>13928</td>\n",
              "      <td>149709</td>\n",
              "      <td>9.3%</td>\n",
              "      <td>4.31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>keyword explorer</td>\n",
              "      <td>13083</td>\n",
              "      <td>43957</td>\n",
              "      <td>29.76%</td>\n",
              "      <td>1.06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>moz seo</td>\n",
              "      <td>12346</td>\n",
              "      <td>31242</td>\n",
              "      <td>39.52%</td>\n",
              "      <td>2.98</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                         Query  Clicks  Impressions     CTR  Position\n",
              "0                          moz  153700       379698  40.48%      1.36\n",
              "1                          seo   60101      1797545   3.34%      5.91\n",
              "2   search engine optimization   28049       378850    7.4%      2.57\n",
              "3                  what is seo   26103       232252  11.24%      1.35\n",
              "4                       mozbar   22275        52886  42.12%      1.13\n",
              "5             keyword research   17030       159687  10.66%      3.12\n",
              "6                    seo tools   16312       158546  10.29%      3.04\n",
              "7             domain authority   15969        68647  23.26%      1.51\n",
              "8         moz keyword explorer   15302        29006  52.75%      1.00\n",
              "9                      moz pro   15043        32931  45.68%      1.36\n",
              "10    domain authority checker   13928       149709    9.3%      4.31\n",
              "11            keyword explorer   13083        43957  29.76%      1.06\n",
              "12                     moz seo   12346        31242  39.52%      2.98"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rAQcVHFCw8IC",
        "colab_type": "text"
      },
      "source": [
        "A few interesting features of the data table display:\n",
        "\n",
        "- Clicking the <button>Filter</button> button in the upper right allows you to search for terms or values in any particular column.\n",
        "- Clicking on any column title lets you sort the results according to that column's value.\n",
        "- The table displays only a subset of the data at a time. You can navigate through pages of data using the controls on the lower right.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ji3aim2NwSZ1",
        "colab_type": "text"
      },
      "source": [
        "#Disabaling Table View"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CNS6o4NrwU5S",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 437
        },
        "outputId": "50672e88-0cd5-4928-d03f-add869894960"
      },
      "source": [
        "%unload_ext google.colab.data_table\n",
        "df"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>Query</th>\n",
              "      <th>Clicks</th>\n",
              "      <th>Impressions</th>\n",
              "      <th>CTR</th>\n",
              "      <th>Position</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>moz</td>\n",
              "      <td>153700</td>\n",
              "      <td>379698</td>\n",
              "      <td>40.48%</td>\n",
              "      <td>1.36</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>seo</td>\n",
              "      <td>60101</td>\n",
              "      <td>1797545</td>\n",
              "      <td>3.34%</td>\n",
              "      <td>5.91</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>search engine optimization</td>\n",
              "      <td>28049</td>\n",
              "      <td>378850</td>\n",
              "      <td>7.4%</td>\n",
              "      <td>2.57</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>what is seo</td>\n",
              "      <td>26103</td>\n",
              "      <td>232252</td>\n",
              "      <td>11.24%</td>\n",
              "      <td>1.35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>mozbar</td>\n",
              "      <td>22275</td>\n",
              "      <td>52886</td>\n",
              "      <td>42.12%</td>\n",
              "      <td>1.13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>keyword research</td>\n",
              "      <td>17030</td>\n",
              "      <td>159687</td>\n",
              "      <td>10.66%</td>\n",
              "      <td>3.12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>seo tools</td>\n",
              "      <td>16312</td>\n",
              "      <td>158546</td>\n",
              "      <td>10.29%</td>\n",
              "      <td>3.04</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>domain authority</td>\n",
              "      <td>15969</td>\n",
              "      <td>68647</td>\n",
              "      <td>23.26%</td>\n",
              "      <td>1.51</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>moz keyword explorer</td>\n",
              "      <td>15302</td>\n",
              "      <td>29006</td>\n",
              "      <td>52.75%</td>\n",
              "      <td>1.00</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>moz pro</td>\n",
              "      <td>15043</td>\n",
              "      <td>32931</td>\n",
              "      <td>45.68%</td>\n",
              "      <td>1.36</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>domain authority checker</td>\n",
              "      <td>13928</td>\n",
              "      <td>149709</td>\n",
              "      <td>9.3%</td>\n",
              "      <td>4.31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>keyword explorer</td>\n",
              "      <td>13083</td>\n",
              "      <td>43957</td>\n",
              "      <td>29.76%</td>\n",
              "      <td>1.06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>moz seo</td>\n",
              "      <td>12346</td>\n",
              "      <td>31242</td>\n",
              "      <td>39.52%</td>\n",
              "      <td>2.98</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                         Query  Clicks  Impressions     CTR  Position\n",
              "0                          moz  153700       379698  40.48%      1.36\n",
              "1                          seo   60101      1797545   3.34%      5.91\n",
              "2   search engine optimization   28049       378850    7.4%      2.57\n",
              "3                  what is seo   26103       232252  11.24%      1.35\n",
              "4                       mozbar   22275        52886  42.12%      1.13\n",
              "5             keyword research   17030       159687  10.66%      3.12\n",
              "6                    seo tools   16312       158546  10.29%      3.04\n",
              "7             domain authority   15969        68647  23.26%      1.51\n",
              "8         moz keyword explorer   15302        29006  52.75%      1.00\n",
              "9                      moz pro   15043        32931  45.68%      1.36\n",
              "10    domain authority checker   13928       149709    9.3%      4.31\n",
              "11            keyword explorer   13083        43957  29.76%      1.06\n",
              "12                     moz seo   12346        31242  39.52%      2.98"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1636hMVkQ43k",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}