Keyword_Research_Opportunities.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Keyword Research 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/Keyword_Research_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": [
        "# Keyword Research Made Easy\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": "markdown",
      "metadata": {
        "id": "EUNZpQBtXdN8",
        "colab_type": "text"
      },
      "source": [
        "Look at the last 5 lines (this also includes the header)"
      ]
    },
    {
      "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": "markdown",
      "metadata": {
        "id": "mRxd3HcGRkK4",
        "colab_type": "text"
      },
      "source": [
        "#Change Column Headers"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1636hMVkQ43k",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Writing out the column names for our data\n",
        "CTR_column=\"CTR %\"\n",
        "Position_column=\"Rank\"\n",
        "\n",
        "df=df.rename(columns={'CTR':'CTR %','Position':'Rank'})\n"
      ],
      "execution_count": 35,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XZ6pKRNlRuyD",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 437
        },
        "outputId": "a137162b-cc75-4106-e733-c10d3915fdd5"
      },
      "source": [
        "df"
      ],
      "execution_count": 36,
      "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>Rank</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 %  Rank\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": 36
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "eR5WyYC1TIK3",
        "colab_type": "text"
      },
      "source": [
        ""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7dIbFPqaTFXC",
        "colab_type": "text"
      },
      "source": [
        "#Collect all branded keywords"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PNBc3qgQTH1Z",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#Replace \"moz|rand|ose\" with your branded keywords to easily create a new Branded Keywords df\n",
        "df_branded = df[(df['Query'].str.contains(\"moz|rand|ose\", regex=True)==True)]\n"
      ],
      "execution_count": 44,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wPRalE3WTQfr",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 197
        },
        "outputId": "b8346573-5711-4063-a0d9-f44c28188df5"
      },
      "source": [
        "df_branded"
      ],
      "execution_count": 45,
      "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>Rank</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>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>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>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 %  Rank\n",
              "0                    moz  153700       379698  40.48%  1.36\n",
              "4                 mozbar   22275        52886  42.12%  1.13\n",
              "8   moz keyword explorer   15302        29006  52.75%  1.00\n",
              "9                moz pro   15043        32931  45.68%  1.36\n",
              "12               moz seo   12346        31242  39.52%  2.98"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 45
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sgNymMQtTrjm",
        "colab_type": "text"
      },
      "source": [
        "#Export Branded Keywords"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "daeit17gTUgA",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df_branded.to_csv('branded-keyword-data.csv')"
      ],
      "execution_count": 46,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9Us3CsJoUhKe",
        "colab_type": "text"
      },
      "source": [
        "#Add Branded Keyword Data to Original Dataframe"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "k0MlLg8ZULhC",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 437
        },
        "outputId": "6feb392f-857e-4bf1-cc5a-1c27892dcc07"
      },
      "source": [
        "df[\"Branded\"]=df.Query.str.contains(\"moz|rand|ose\")\n",
        "df\n"
      ],
      "execution_count": 51,
      "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>Rank</th>\n",
              "      <th>branded</th>\n",
              "      <th>Branded</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",
              "      <td>True</td>\n",
              "      <td>True</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",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>True</td>\n",
              "      <td>True</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",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>True</td>\n",
              "      <td>True</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",
              "      <td>True</td>\n",
              "      <td>True</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",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                         Query  Clicks  Impressions  ...  Rank  branded  Branded\n",
              "0                          moz  153700       379698  ...  1.36     True     True\n",
              "1                          seo   60101      1797545  ...  5.91    False    False\n",
              "2   search engine optimization   28049       378850  ...  2.57    False    False\n",
              "3                  what is seo   26103       232252  ...  1.35    False    False\n",
              "4                       mozbar   22275        52886  ...  1.13     True     True\n",
              "5             keyword research   17030       159687  ...  3.12    False    False\n",
              "6                    seo tools   16312       158546  ...  3.04    False    False\n",
              "7             domain authority   15969        68647  ...  1.51    False    False\n",
              "8         moz keyword explorer   15302        29006  ...  1.00     True     True\n",
              "9                      moz pro   15043        32931  ...  1.36     True     True\n",
              "10    domain authority checker   13928       149709  ...  4.31    False    False\n",
              "11            keyword explorer   13083        43957  ...  1.06    False    False\n",
              "12                     moz seo   12346        31242  ...  2.98     True     True\n",
              "\n",
              "[13 rows x 7 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 51
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1A-pqks5UxTo",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 437
        },
        "outputId": "661650d1-faa6-457e-dabf-becb7d666cb5"
      },
      "source": [
        "#Woops accidentilly ran the above cell twice and added 2 'branded' + 'Branded' columns\n",
        "#No problem! Let's drop the extra lowercase column:\n",
        "df.drop(['branded'], axis=1)"
      ],
      "execution_count": 52,
      "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>Rank</th>\n",
              "      <th>Branded</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",
              "      <td>True</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",
              "      <td>False</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",
              "      <td>False</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",
              "      <td>False</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",
              "      <td>True</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",
              "      <td>False</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",
              "      <td>False</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",
              "      <td>False</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",
              "      <td>True</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",
              "      <td>True</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",
              "      <td>False</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",
              "      <td>False</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",
              "      <td>True</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                         Query  Clicks  Impressions   CTR %  Rank  Branded\n",
              "0                          moz  153700       379698  40.48%  1.36     True\n",
              "1                          seo   60101      1797545   3.34%  5.91    False\n",
              "2   search engine optimization   28049       378850    7.4%  2.57    False\n",
              "3                  what is seo   26103       232252  11.24%  1.35    False\n",
              "4                       mozbar   22275        52886  42.12%  1.13     True\n",
              "5             keyword research   17030       159687  10.66%  3.12    False\n",
              "6                    seo tools   16312       158546  10.29%  3.04    False\n",
              "7             domain authority   15969        68647  23.26%  1.51    False\n",
              "8         moz keyword explorer   15302        29006  52.75%  1.00     True\n",
              "9                      moz pro   15043        32931  45.68%  1.36     True\n",
              "10    domain authority checker   13928       149709    9.3%  4.31    False\n",
              "11            keyword explorer   13083        43957  29.76%  1.06    False\n",
              "12                     moz seo   12346        31242  39.52%  2.98     True"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 52
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "O4ERQOABVTVS",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#Let's add a column for all keyword instances of 'Domain Authority'"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GRnZrcIvU_rf",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 437
        },
        "outputId": "9671ac9f-1eec-45be-b024-5c1bcf9eb586"
      },
      "source": [
        "df[\"DA\"]=df.Query.str.contains(\"DA|authority|domain|google ranking factor|link juice\")\n",
        "df\n",
        "\n",
        "#jk /= google ranking factor :)"
      ],
      "execution_count": 53,
      "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>Rank</th>\n",
              "      <th>branded</th>\n",
              "      <th>Branded</th>\n",
              "      <th>DA</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",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>True</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",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>False</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",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>False</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",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>True</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",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</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",
              "      <td>True</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                         Query  Clicks  Impressions  ... branded  Branded     DA\n",
              "0                          moz  153700       379698  ...    True     True  False\n",
              "1                          seo   60101      1797545  ...   False    False  False\n",
              "2   search engine optimization   28049       378850  ...   False    False  False\n",
              "3                  what is seo   26103       232252  ...   False    False  False\n",
              "4                       mozbar   22275        52886  ...    True     True  False\n",
              "5             keyword research   17030       159687  ...   False    False  False\n",
              "6                    seo tools   16312       158546  ...   False    False  False\n",
              "7             domain authority   15969        68647  ...   False    False   True\n",
              "8         moz keyword explorer   15302        29006  ...    True     True  False\n",
              "9                      moz pro   15043        32931  ...    True     True  False\n",
              "10    domain authority checker   13928       149709  ...   False    False   True\n",
              "11            keyword explorer   13083        43957  ...   False    False  False\n",
              "12                     moz seo   12346        31242  ...    True     True  False\n",
              "\n",
              "[13 rows x 8 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 53
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ACRytRduVzdy",
        "colab_type": "text"
      },
      "source": [
        "#COPY THIS NOTEBOOK & EASILY ORGANIZE + EXPORT YOUR KEYWORD DATA HERE!!!!\n",
        "\n",
        "Did I mention that this way is also waaaayy faster?!\n",
        "\n",
        "#Never use another long excel command like this again:\n",
        "![Export internal link csv to your local computer](https://i.imgur.com/in1u5rs.png)\n",
        "\n",
        "Shoutout to Robin Lord for the inspo behind this!!!! See his slides & examples here: https://www.dropbox.com/sh/vl5miyt6sgbvmkl/AAC5365YcWTun_EzkQLtixe1a?dl=0"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "q4ECDDZ2XBJ5",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#It's talks like Robin's that can change the trajectory of someone's career!\n",
        "#KEEP LEARNING! There are easier ways to do traditional SEO, promise!\n",
        "\n",
        "#*big hugs* -Britney"
      ],
      "execution_count": 55,
      "outputs": []
    }
  ]
}