Jupyter Toolbox for Data Anlaysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Replacing Excel with Jupyter Notebooks\n",
    "\n",
    "Anything you don't understand is scary and for many people that's doubly true for code. Moving from Excel to Python has a huge number of benefits, your work becomes:\n",
    "\n",
    "- repeatable: Code can be easily re-run and you can re-use the same pieces of work in a different situation\n",
    "- understandable: If you perform a piece of analysis in code, the code will show you what has been done. While not perfect, this is still better than Excel, where all you see is the end product.\n",
    "- scaleable: Excel struggles with large amounts of data. Python doesn't. Eventually you'll hit a limit but it's far further off.\n",
    "- more powerful: Things that are trivial in Python can be exceptionally hard in Excel (for example using text fields in the value part of a pivot chart)\n",
    "- Harder to screw up your data: If you fuck up your data in Excel, it's entirely possible you just edited your source and you have to start all over again. In Jupyter notebooks all you have to do is run a couple of cells.\n",
    "\n",
    "But unlike Excel, it doesn't have lots of nice shiny buttons. The goal of this workbook is to show you how to do all the common actions you take in Excel in python as well as how to work with data. \n",
    "\n",
    "We'll be relying heavily on pandas, which provides lots of useful functions for data analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How does Jupyter notebook work?\n",
    "\n",
    "Jupyter notebook (IMO) is the ideal environment for data analysis. It works as follows.\n",
    "- You type code into cells. \n",
    "- You run the cells.\n",
    "\n",
    "By breaking the code into cells it allows you to work in steps and investigate the data as you are going.\n",
    "\n",
    "Jupyter notebook has two modes:\n",
    "- Command mode (let's you create new cells, delete them, run them etc.)\n",
    "- Edit mode (let's you type into and edit the cells)\n",
    "\n",
    "Escape takes you to command mode (the highlighted cell will have a blue bar on the left),  Enter takes you to edit mode (the highlighted cell will have a green bar on the left).\n",
    "\n",
    "While there are buttons at the top, I'd highly recommend learning the shortcuts. Aside from those two the most important are:\n",
    "\n",
    "**Command Mode:**\n",
    "- a: create a cell above\n",
    "- b: create a cell below\n",
    "- Cmd/Ctrl+Enter - Runs a cell\n",
    "- dd: deletes a cell (be careful, you can't undo this)\n",
    "- m: Set a cell to text mode (markdown), like this cell you're reading\n",
    "- y: Set a cell to code mode (the default)\n",
    "\n",
    "**Edit mode:**\n",
    "- shift+tab: show documentation for the function the cursor is inside. Hit tab multiple times to change where the documentation pop-up appears. (sometimes won't trigger in which case just google \"pandas documentation x\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Notes on working through this\n",
    "I've now watched a couple people try to work through this workbook, first most immediate note is:\n",
    "- After each command, inspect the result using the head() command we're going to show off below. People are used to seeing the data as they work, and inspecting it at every stage will help show you what the functions are doing."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** In order to use libraries we need to import them. We're only going to import:\n",
    "- pandas - functions for data analysis\n",
    "- numpy - more functions for data analysis (in my experience, typically faster, but not quite as user friendly as pandas)\n",
    "- display from IPython.display - This allows us to print things out with nice formatting.\n",
    "- re - regular expressions. pandas already has this built in, in a lot of places so we won't often need this.\n",
    "\n",
    "There are a huge number we haven't even touched here!\n",
    "\n",
    "Let's get started!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "from IPython.display import display"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** For our example here, we're going to load in a top 20 ranking file from STAT.\n",
    "\n",
    "This shows the top 20 rankings domains for a set of keywords and is exactly the data we might want to do some analysis on .\n",
    "\n",
    "The following function allows us to open a CSV file into a dataframe. That's a fancy word for a table in pandas.\n",
    "\n",
    "The output of the function on the right is saved into the variable on the left."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"Example Car SERPs.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring data\n",
    "\n",
    "First things first, how do we look the data we've just loaded? It's obvious in Excel, how do I do it with pandas and python?\n",
    "\n",
    "We need to be able to see our data, because that's often how we're going to validate that our code has done what we expected. Plus if you've come from Excel you'll be used to looking at the data as you go.\n",
    "\n",
    "Using head we can get our first look at the data in pandas. It looks like a table and how we'd expect. Hooray! We can also see that pandas assigns each row a unique number in the first column. This is called the index and when we begin to filter our data, we'll see that shown in the index.\n",
    "\n",
    "**What does this do?:** This prints out the first x rows of a dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Keyword</th>\n",
       "      <th>Market</th>\n",
       "      <th>Location</th>\n",
       "      <th>Device</th>\n",
       "      <th>Regional Monthly Search Volume</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Result Types for Jan  1, 2017</th>\n",
       "      <th>Protocol for Jan  1, 2017</th>\n",
       "      <th>Ranking URL on Jan  1, 2017</th>\n",
       "      <th>Result Types for Sep  1, 2017</th>\n",
       "      <th>Protocol for Sep  1, 2017</th>\n",
       "      <th>Ranking URL on Sep  1, 2017</th>\n",
       "      <th>Tags</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>answers / list</td>\n",
       "      <td>http</td>\n",
       "      <td>www.autoexpress.co.uk/best-cars/98556/the-best...</td>\n",
       "      <td>!permanent keywords / auto express / featured ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Keyword Market  Location   Device  Regional Monthly Search Volume  \\\n",
       "0  0 apr car deals  GB-en       NaN  desktop                          1900.0   \n",
       "\n",
       "   Rank Result Types for Jan  1, 2017 Protocol for Jan  1, 2017  \\\n",
       "0     1                           NaN                       NaN   \n",
       "\n",
       "  Ranking URL on Jan  1, 2017 Result Types for Sep  1, 2017  \\\n",
       "0                         NaN                answers / list   \n",
       "\n",
       "  Protocol for Sep  1, 2017  \\\n",
       "0                      http   \n",
       "\n",
       "                         Ranking URL on Sep  1, 2017  \\\n",
       "0  www.autoexpress.co.uk/best-cars/98556/the-best...   \n",
       "\n",
       "                                                Tags  \n",
       "0  !permanent keywords / auto express / featured ...  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** Gives you the size of the dataframe in rows by columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(39600, 13)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** Shows you the datatype for each column. (i.e. this column is a string (i.e. text), this column is a number etc.) What is object? For our purposes it's the same as string.\n",
    "\n",
    "Datatype is really important. If you have a column of numbers, but someone has accidentally left a word in one row, the whole column will be treated as strings so you won't be able to perform maths operations on them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Keyword           object\n",
       "Market            object\n",
       "Location          object\n",
       "Device            object\n",
       "Search Volume    float64\n",
       "Rank               int64\n",
       "Result Types      object\n",
       "Protocol          object\n",
       "Ranking URL       object\n",
       "Tags              object\n",
       "dtype: object"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** This allows you to select a column. \n",
    "\n",
    "We've also combined it with our previous command so it doesn't print out the entire column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    GB-en\n",
       "Name: Market, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Market'].head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** This shows you all the different values in a column, along with a count of how many there are of each."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GB-en    39540\n",
       "DE-de       60\n",
       "Name: Market, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Market'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Saving results vs printing results out\n",
    "\n",
    "Each line of code we've had so far has printed something out or saved the result of our calculation into a variable.\n",
    "\n",
    "If we have a single line of code which doesn't save anything, Jupyter will print out whatever is returned by that line of code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    GB-en\n",
       "Name: Market, dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Market'].head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we've saved the results of code into a variable then it won't print anything out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "saved = df['Market'].head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can view the contents of the variable however, by putting the variable by itself into a cell and running that cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    GB-en\n",
       "Name: Market, dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "saved"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However if you try to print out multiple things at once, by just listing them one by one, then only the past one will be printed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Only I will print.'"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Market'].head(1)\n",
    "df['Market'].head(2)\n",
    "df['Market'].head(3)\n",
    "\"Only I will print.\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you do want to print out multiple rows we need to tell pandas. Here its good practice to use display, because often it will make the formatting nicer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    GB-en\n",
       "Name: Market, dtype: object"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0    GB-en\n",
       "1    GB-en\n",
       "Name: Market, dtype: object"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df['Market'].head(1))\n",
    "display(df['Market'].head(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Filtering data\n",
    "\n",
    "Great so we know how to print out data and how to examine the data we've loaded. What other common tasks might we find outselves doing?\n",
    "\n",
    "We'll start with filtering. A common use case is wanting to filter data based on the values in columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** Filter a dataframe based on a string column. In this case we're filtering based on whether or not a regex matches.\n",
    "\n",
    "We can see the index number on the left changing, because the first row where Keyword contains bmw or audi is 180."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Keyword</th>\n",
       "      <th>Market</th>\n",
       "      <th>Location</th>\n",
       "      <th>Device</th>\n",
       "      <th>Regional Monthly Search Volume</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Result Types for Jan  1, 2017</th>\n",
       "      <th>Protocol for Jan  1, 2017</th>\n",
       "      <th>Ranking URL on Jan  1, 2017</th>\n",
       "      <th>Result Types for Sep  1, 2017</th>\n",
       "      <th>Protocol for Sep  1, 2017</th>\n",
       "      <th>Ranking URL on Sep  1, 2017</th>\n",
       "      <th>Tags</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>1 series bmw</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>1</td>\n",
       "      <td>organic</td>\n",
       "      <td>http</td>\n",
       "      <td>www.bmw.co.uk/en_GB/new-vehicles/1.html</td>\n",
       "      <td>organic</td>\n",
       "      <td>http</td>\n",
       "      <td>www.bmw.co.uk/en_GB/new-vehicles/1.html</td>\n",
       "      <td>temp - semrush / used-cars</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>1 series bmw</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>2</td>\n",
       "      <td>organic</td>\n",
       "      <td>http</td>\n",
       "      <td>www.autotrader.co.uk/used-cars/bmw/1-series</td>\n",
       "      <td>organic</td>\n",
       "      <td>https</td>\n",
       "      <td>www.autotrader.co.uk/used-cars/bmw/1-series</td>\n",
       "      <td>temp - semrush / used-cars</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>1 series bmw</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>3</td>\n",
       "      <td>organic</td>\n",
       "      <td>http</td>\n",
       "      <td>www.whatcar.com/bmw/1-series/hatchback/review/</td>\n",
       "      <td>organic</td>\n",
       "      <td>https</td>\n",
       "      <td>www.gumtree.com/cars/uk/bmw+1+series</td>\n",
       "      <td>temp - semrush / used-cars</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>1 series bmw</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>4</td>\n",
       "      <td>organic</td>\n",
       "      <td>https</td>\n",
       "      <td>en.wikipedia.org/wiki/BMW_1_Series</td>\n",
       "      <td>organic</td>\n",
       "      <td>https</td>\n",
       "      <td>www.whatcar.com/bmw/1-series/hatchback/review/</td>\n",
       "      <td>temp - semrush / used-cars</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>1 series bmw</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>5</td>\n",
       "      <td>organic</td>\n",
       "      <td>https</td>\n",
       "      <td>www.gumtree.com/cars/uk/bmw/bmw+1+series</td>\n",
       "      <td>organic</td>\n",
       "      <td>https</td>\n",
       "      <td>en.wikipedia.org/wiki/BMW_1_Series</td>\n",
       "      <td>temp - semrush / used-cars</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Keyword Market  Location   Device  Regional Monthly Search Volume  \\\n",
       "180  1 series bmw  GB-en       NaN  desktop                          3600.0   \n",
       "181  1 series bmw  GB-en       NaN  desktop                          3600.0   \n",
       "182  1 series bmw  GB-en       NaN  desktop                          3600.0   \n",
       "183  1 series bmw  GB-en       NaN  desktop                          3600.0   \n",
       "184  1 series bmw  GB-en       NaN  desktop                          3600.0   \n",
       "\n",
       "     Rank Result Types for Jan  1, 2017 Protocol for Jan  1, 2017  \\\n",
       "180     1                       organic                      http   \n",
       "181     2                       organic                      http   \n",
       "182     3                       organic                      http   \n",
       "183     4                       organic                     https   \n",
       "184     5                       organic                     https   \n",
       "\n",
       "                        Ranking URL on Jan  1, 2017  \\\n",
       "180         www.bmw.co.uk/en_GB/new-vehicles/1.html   \n",
       "181     www.autotrader.co.uk/used-cars/bmw/1-series   \n",
       "182  www.whatcar.com/bmw/1-series/hatchback/review/   \n",
       "183              en.wikipedia.org/wiki/BMW_1_Series   \n",
       "184        www.gumtree.com/cars/uk/bmw/bmw+1+series   \n",
       "\n",
       "    Result Types for Sep  1, 2017 Protocol for Sep  1, 2017  \\\n",
       "180                       organic                      http   \n",
       "181                       organic                     https   \n",
       "182                       organic                     https   \n",
       "183                       organic                     https   \n",
       "184                       organic                     https   \n",
       "\n",
       "                        Ranking URL on Sep  1, 2017  \\\n",
       "180         www.bmw.co.uk/en_GB/new-vehicles/1.html   \n",
       "181     www.autotrader.co.uk/used-cars/bmw/1-series   \n",
       "182            www.gumtree.com/cars/uk/bmw+1+series   \n",
       "183  www.whatcar.com/bmw/1-series/hatchback/review/   \n",
       "184              en.wikipedia.org/wiki/BMW_1_Series   \n",
       "\n",
       "                           Tags  \n",
       "180  temp - semrush / used-cars  \n",
       "181  temp - semrush / used-cars  \n",
       "182  temp - semrush / used-cars  \n",
       "183  temp - semrush / used-cars  \n",
       "184  temp - semrush / used-cars  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filtered = df[(df[\"Keyword\"].str.contains(\"bmw|audi\", regex=True)==True)]\n",
    "df_filtered.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** Filter a dataframe based on a numeric column. Here we return all the results for rank 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_filtered = df[df[\"Rank\"]==1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** Filter a dataframe based on a boolean (i.e. TRUE and FALSE) column. Here we return all the results which are true. We don't have a boolean column yet, so let's quickly create one (we'll go over in more detail later)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Ignore me for now\n",
    "df['is_rank_1'] = False\n",
    "df.loc[df['Rank'] == 1, 'is_rank_1'] = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we can filter for any rows where is_rank_1 is equal to True."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_filtered = df[df['is_rank_1']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we want to invert this and get any where it doesn't match 1, we do it with `~`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_filtered = df[~df['is_rank_1']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** Filter a dataframe based off a list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "list_of_values = [\n",
    "    '1 series bmw',\n",
    "    'ford deals'\n",
    "]\n",
    "df_filtered = df[df[\"Keyword\"].isin(list_of_values)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** What if we want to use multiple conditions? Again nice and easy, we just put more than one in our first set of square brackets as seen below. `&` is the symbol for and, and `|` is the symbol for or."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_filtered = df[df[\"Market\"].str.contains(\"GB-en\")==False | \n",
    "                 df[\"Device\"].str.contains(\"desktop\")==True]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Aside:** If you're exploring or debugging data, then you'll often want to change filters multiple times to see what you're working with. It's easiest to do this in one cell, but having to re-write them is annoying. The following layout for filtering allows you to easily comment out the filters you're not using, so you don't have to delete and re-write them."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "df[\n",
    "    df['Keyword'].str.contains(\"ford\")\n",
    "    & df['Device'].str.match(\"smartphone\")\n",
    "    & df['Market'].str.match(\"GB-en\")\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "# Creating new columns based off existing columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes we don't want to filter our entire set, we just want to create a new column which is correctly filtered. We already saw this above but we skipped over it, so now we'll look at it in more detail. \n",
    "\n",
    "But before we can create new columns based off filters we need to know how to create new columns.\n",
    "\n",
    "**What does this do?:** Creates a new column called *Industry* and sets the value to \"cars\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['industry'] = \"Cars\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we'll look at creating a column which is True or False based on a condition. We can use any of the conditions we used in the previous section.\n",
    "\n",
    "**What does this do?:** Sets the column to True and False based on the filter condition. \n",
    "\n",
    "Below we're creating a column which is True wherever rank is 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['is_rank_1'] = df['Rank'] == 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each condition (if you have more than 1) needs to be wrapped in brackets.\n",
    "\n",
    "Below we're creating a column which is `True` for the all the rows where rank=1 and `False` otherwise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['auto_express_with_rank_2'] = (df['Tags'].str.contains(\"auto express\")) & (df['Rank'] < 4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if we wanted to create a basic search visibility metric? Below we do that by dividing rank by search volume.\n",
    "\n",
    "**What does this do?:** Below we're creating a column based on a calculation from other columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['basic_search_visibility'] = df['Regional Monthly Search Volume']/df['Rank']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another common usecase, creating a column based off a regex extract. Pandas comes with a built in function for us.\n",
    "\n",
    "**What does this do?:** This function creates a column, which is the value of the first captured group (everything inside the first set of brackets) in the regex."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['car_type'] = df['Keyword'].str.extract(\"(bmw|audi|ford)\", re.IGNORECASE, expand=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bmw     3660\n",
       "audi    2960\n",
       "ford    1800\n",
       "Name: car_type, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['car_type'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bonus round - using custom functions\n",
    "\n",
    "Pandas has a remarkable number of functions for creating extra columns and manipulating them. It's well worth trying to get comfortable with the documentation, particularly for [string handling](https://pandas.pydata.org/pandas-docs/stable/api.html#string-handling), however occasionally what you're doing is complex and these functions won't do. A good example is tagging.\n",
    "\n",
    "I.e. I want a column where any keywords containing high end cars are tagged with \"high-end\" and any keywords containing low end cars are tagged \"low-end\".\n",
    "\n",
    "First we need to create some tags and a tagging function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tags = [\n",
    "    {\n",
    "        \"name\": \"high-end\",\n",
    "        \"regex\": \"bmw|audi\"\n",
    "    },\n",
    "    {\n",
    "        \"name\": \"low-end\",\n",
    "        \"regex\": \"ford\"\n",
    "    }\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def basic_keyword_tagging(string, tags):\n",
    "    ''' This function takes a string and a tag dataset that is a list of dictionaries.\n",
    "    \n",
    "    Each dictionary must contain a name value and a regex value. It will loop through\n",
    "    the tags and as soon as one matches, return that. If none match it will return \"other\".\n",
    "    '''\n",
    "    \n",
    "    for tag in tags:\n",
    "        if re.match(tag['regex'], row['Keyword']):\n",
    "            return tag['name']\n",
    "        else:\n",
    "            return \"other\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** The function below, applies our function to each row in the dataframe and runs the value to our new column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df['what_is_car_model'] = df.apply(lambda x: basic_tagging(x['Keyword'], tags), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Grouping data\n",
    "\n",
    "Now we're getting into the fun stuff. Grouping. Grouping is super useful. Let's suppose we wanted to see the average rank and total keyword count per URL in our sample. That's the kind of question grouping is useful for."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?**: This is the most flexible version of groupby. There are simpler syntaxes, but if you know this one you'll be able to do basically everything you want with it. In the example below, we're going to get average rank and total keyword count for each for the later date in our sample (1st Sep 2017)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_group = df.groupby('Ranking URL on Sep  1, 2017').agg({\n",
    "    'Keyword': 'count',\n",
    "    'Rank': 'mean'\n",
    "}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ranking URL on Sep  1, 2017</th>\n",
       "      <th>Keyword</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111.intdmf.com/autolottnotts.com</td>\n",
       "      <td>2</td>\n",
       "      <td>15.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111.intdmf.com/autolotts.com</td>\n",
       "      <td>2</td>\n",
       "      <td>14.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>44teeth.com/contributors/loved-honda-msx-sold-...</td>\n",
       "      <td>2</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4testpass.com/pass-driving-test/</td>\n",
       "      <td>3</td>\n",
       "      <td>12.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a-professional-appraisal.ca/contact-us/</td>\n",
       "      <td>2</td>\n",
       "      <td>19.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>a2bikes.co.uk/bike/kawasaki-ninja-300</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ah-ltd.co.uk/tax-rate-information/vehicle.html</td>\n",
       "      <td>1</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>allcarskent.co.uk/</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>allegro.pl/kategoria/audi-rs7-146643</td>\n",
       "      <td>2</td>\n",
       "      <td>14.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>allegro.pl/kategoria/bmw-m2-255467</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>allegro.pl/kategoria/lancia-delta-12562</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>amp.autoweek.com/article/car-reviews/2017-chry...</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>amp.autoweek.com/article/events/hurricane-harv...</td>\n",
       "      <td>1</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>amp.businessinsider.com/best-first-cars-2012-1</td>\n",
       "      <td>1</td>\n",
       "      <td>14.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>amp.businessinsider.com/best-used-cars-for-fir...</td>\n",
       "      <td>1</td>\n",
       "      <td>11.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>amp.ft.com/content/7fa6cd04-8e68-11e7-a352-e46...</td>\n",
       "      <td>1</td>\n",
       "      <td>11.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>amp.ft.com/content/ad691d06-5620-11e4-bbd6-001...</td>\n",
       "      <td>2</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>amp.ft.com/content/e66c9f97-6d35-3642-88a9-bc9...</td>\n",
       "      <td>1</td>\n",
       "      <td>19.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>amp.reddit.com/r/MotoUK/comments/2u3asv/honda_...</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>amp.reddit.com/r/askcarsales/comments/60wjgw/h...</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>amp.theguardian.com/business/2017/aug/16/uk-ca...</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>amp.theguardian.com/business/2017/jul/05/uk-ca...</td>\n",
       "      <td>1</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>amp.theguardian.com/money/2014/feb/14/getting-...</td>\n",
       "      <td>4</td>\n",
       "      <td>13.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>amp.theguardian.com/money/2014/jul/26/buy-new-...</td>\n",
       "      <td>3</td>\n",
       "      <td>13.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>amp.theguardian.com/money/2016/feb/03/deal-tro...</td>\n",
       "      <td>2</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>amp.theguardian.com/money/2017/feb/25/secondha...</td>\n",
       "      <td>2</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>amp.theguardian.com/technology/2012/aug/24/kia...</td>\n",
       "      <td>1</td>\n",
       "      <td>19.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>amp.theguardian.com/technology/2012/dec/09/chr...</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>amp.theguardian.com/technology/2016/dec/04/hyu...</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>amp.theguardian.com/technology/2016/jul/30/cit...</td>\n",
       "      <td>1</td>\n",
       "      <td>19.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13384</th>\n",
       "      <td>www.youtube.com/watch?v=u-CUobmVO2s</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13385</th>\n",
       "      <td>www.youtube.com/watch?v=uLGMQ6WG9Kc</td>\n",
       "      <td>1</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13386</th>\n",
       "      <td>www.youtube.com/watch?v=uZ6Rmc_ljvg</td>\n",
       "      <td>1</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13387</th>\n",
       "      <td>www.youtube.com/watch?v=u_wOXj3IiT4</td>\n",
       "      <td>1</td>\n",
       "      <td>19.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13388</th>\n",
       "      <td>www.youtube.com/watch?v=v0B-0JR7suU</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13389</th>\n",
       "      <td>www.youtube.com/watch?v=vPu5wGV3854</td>\n",
       "      <td>1</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13390</th>\n",
       "      <td>www.youtube.com/watch?v=vWm-sncUhd8</td>\n",
       "      <td>1</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13391</th>\n",
       "      <td>www.youtube.com/watch?v=vYYQnZcQxwI</td>\n",
       "      <td>1</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13392</th>\n",
       "      <td>www.youtube.com/watch?v=vjcfE3zlOf4</td>\n",
       "      <td>1</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13393</th>\n",
       "      <td>www.youtube.com/watch?v=w--uV25yLWQ</td>\n",
       "      <td>2</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13394</th>\n",
       "      <td>www.youtube.com/watch?v=wmP6UdQX8qo</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13395</th>\n",
       "      <td>www.youtube.com/watch?v=x7kNKNAS2GY</td>\n",
       "      <td>1</td>\n",
       "      <td>14.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13396</th>\n",
       "      <td>www.youtube.com/watch?v=xgKjp1pq7iI</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13397</th>\n",
       "      <td>www.youtube.com/watch?v=ySvb44ucuIE</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13398</th>\n",
       "      <td>www.youtube.com/watch?v=zUKFG_Y2pBk</td>\n",
       "      <td>1</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13399</th>\n",
       "      <td>www.youtube.com/watch?v=zuNutWkhV8U</td>\n",
       "      <td>1</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13400</th>\n",
       "      <td>www.z-tech.co.uk/e-newz/bmw-c-evolution-a-few-...</td>\n",
       "      <td>2</td>\n",
       "      <td>14.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13401</th>\n",
       "      <td>www.zeroto60times.com/compare-cars/</td>\n",
       "      <td>3</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13402</th>\n",
       "      <td>www.zestcarrental.com/</td>\n",
       "      <td>1</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13403</th>\n",
       "      <td>www.zigwheels.com/compare-cars</td>\n",
       "      <td>2</td>\n",
       "      <td>14.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13404</th>\n",
       "      <td>www.zigwheels.com/newbikes/KTM/Duke-390-ABS</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13405</th>\n",
       "      <td>www.zigwheels.com/newbikes/KTM/Duke-390-ABS/on...</td>\n",
       "      <td>1</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13406</th>\n",
       "      <td>www.zigwheels.com/newcars</td>\n",
       "      <td>1</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13407</th>\n",
       "      <td>www.zigwheels.com/newcars/Lamborghini/Aventador</td>\n",
       "      <td>1</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13408</th>\n",
       "      <td>www.zigwheels.com/newcars/Lamborghini/Aventado...</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13409</th>\n",
       "      <td>www.zigwheels.com/newcars/Lamborghini/Huracan</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13410</th>\n",
       "      <td>www.zigwheels.com/newcars?is_amp=1</td>\n",
       "      <td>1</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13411</th>\n",
       "      <td>www.zipcar.co.uk/check-rates/london</td>\n",
       "      <td>1</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13412</th>\n",
       "      <td>www.zoopla.co.uk/home-values/</td>\n",
       "      <td>2</td>\n",
       "      <td>3.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13413</th>\n",
       "      <td>www.zuto.com/free-car-valuation/</td>\n",
       "      <td>105</td>\n",
       "      <td>14.866667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13414 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Ranking URL on Sep  1, 2017  Keyword       Rank\n",
       "0                       111.intdmf.com/autolottnotts.com        2  15.500000\n",
       "1                           111.intdmf.com/autolotts.com        2  14.500000\n",
       "2      44teeth.com/contributors/loved-honda-msx-sold-...        2  17.000000\n",
       "3                       4testpass.com/pass-driving-test/        3  12.666667\n",
       "4                a-professional-appraisal.ca/contact-us/        2  19.500000\n",
       "5                  a2bikes.co.uk/bike/kawasaki-ninja-300        1  16.000000\n",
       "6         ah-ltd.co.uk/tax-rate-information/vehicle.html        1  13.000000\n",
       "7                                     allcarskent.co.uk/        1  18.000000\n",
       "8                   allegro.pl/kategoria/audi-rs7-146643        2  14.500000\n",
       "9                     allegro.pl/kategoria/bmw-m2-255467        1  20.000000\n",
       "10               allegro.pl/kategoria/lancia-delta-12562        1  20.000000\n",
       "11     amp.autoweek.com/article/car-reviews/2017-chry...        1  18.000000\n",
       "12     amp.autoweek.com/article/events/hurricane-harv...        1  10.000000\n",
       "13        amp.businessinsider.com/best-first-cars-2012-1        1  14.000000\n",
       "14     amp.businessinsider.com/best-used-cars-for-fir...        1  11.000000\n",
       "15     amp.ft.com/content/7fa6cd04-8e68-11e7-a352-e46...        1  11.000000\n",
       "16     amp.ft.com/content/ad691d06-5620-11e4-bbd6-001...        2  15.000000\n",
       "17     amp.ft.com/content/e66c9f97-6d35-3642-88a9-bc9...        1  19.000000\n",
       "18     amp.reddit.com/r/MotoUK/comments/2u3asv/honda_...        1  18.000000\n",
       "19     amp.reddit.com/r/askcarsales/comments/60wjgw/h...        1  20.000000\n",
       "20     amp.theguardian.com/business/2017/aug/16/uk-ca...        1  20.000000\n",
       "21     amp.theguardian.com/business/2017/jul/05/uk-ca...        1  15.000000\n",
       "22     amp.theguardian.com/money/2014/feb/14/getting-...        4  13.750000\n",
       "23     amp.theguardian.com/money/2014/jul/26/buy-new-...        3  13.333333\n",
       "24     amp.theguardian.com/money/2016/feb/03/deal-tro...        2   5.000000\n",
       "25     amp.theguardian.com/money/2017/feb/25/secondha...        2  18.000000\n",
       "26     amp.theguardian.com/technology/2012/aug/24/kia...        1  19.000000\n",
       "27     amp.theguardian.com/technology/2012/dec/09/chr...        1  20.000000\n",
       "28     amp.theguardian.com/technology/2016/dec/04/hyu...        1  20.000000\n",
       "29     amp.theguardian.com/technology/2016/jul/30/cit...        1  19.000000\n",
       "...                                                  ...      ...        ...\n",
       "13384                www.youtube.com/watch?v=u-CUobmVO2s        1  20.000000\n",
       "13385                www.youtube.com/watch?v=uLGMQ6WG9Kc        1   6.000000\n",
       "13386                www.youtube.com/watch?v=uZ6Rmc_ljvg        1  10.000000\n",
       "13387                www.youtube.com/watch?v=u_wOXj3IiT4        1  19.000000\n",
       "13388                www.youtube.com/watch?v=v0B-0JR7suU        1  18.000000\n",
       "13389                www.youtube.com/watch?v=vPu5wGV3854        1  10.000000\n",
       "13390                www.youtube.com/watch?v=vWm-sncUhd8        1  15.000000\n",
       "13391                www.youtube.com/watch?v=vYYQnZcQxwI        1  17.000000\n",
       "13392                www.youtube.com/watch?v=vjcfE3zlOf4        1  15.000000\n",
       "13393                www.youtube.com/watch?v=w--uV25yLWQ        2  12.000000\n",
       "13394                www.youtube.com/watch?v=wmP6UdQX8qo        1  16.000000\n",
       "13395                www.youtube.com/watch?v=x7kNKNAS2GY        1  14.000000\n",
       "13396                www.youtube.com/watch?v=xgKjp1pq7iI        1  20.000000\n",
       "13397                www.youtube.com/watch?v=ySvb44ucuIE        1  16.000000\n",
       "13398                www.youtube.com/watch?v=zUKFG_Y2pBk        1   4.000000\n",
       "13399                www.youtube.com/watch?v=zuNutWkhV8U        1   7.000000\n",
       "13400  www.z-tech.co.uk/e-newz/bmw-c-evolution-a-few-...        2  14.500000\n",
       "13401                www.zeroto60times.com/compare-cars/        3  12.000000\n",
       "13402                             www.zestcarrental.com/        1  17.000000\n",
       "13403                     www.zigwheels.com/compare-cars        2  14.000000\n",
       "13404        www.zigwheels.com/newbikes/KTM/Duke-390-ABS        1  16.000000\n",
       "13405  www.zigwheels.com/newbikes/KTM/Duke-390-ABS/on...        1  15.000000\n",
       "13406                          www.zigwheels.com/newcars        1  12.000000\n",
       "13407    www.zigwheels.com/newcars/Lamborghini/Aventador        1  13.000000\n",
       "13408  www.zigwheels.com/newcars/Lamborghini/Aventado...        1  16.000000\n",
       "13409      www.zigwheels.com/newcars/Lamborghini/Huracan        1  18.000000\n",
       "13410                 www.zigwheels.com/newcars?is_amp=1        1  13.000000\n",
       "13411                www.zipcar.co.uk/check-rates/london        1  17.000000\n",
       "13412                      www.zoopla.co.uk/home-values/        2   3.500000\n",
       "13413                   www.zuto.com/free-car-valuation/      105  14.866667\n",
       "\n",
       "[13414 rows x 3 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_group"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But we've just aggregated by URL there. Want if we wanted to average rank and keyword count, per URL, per country? In that case we provide more values to our initial group by."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_group = df.groupby(['Ranking URL on Sep  1, 2017', 'Market']).agg({\n",
    "    'Keyword': 'count',\n",
    "    'Rank': 'mean'\n",
    "}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ranking URL on Sep  1, 2017</th>\n",
       "      <th>Market</th>\n",
       "      <th>Keyword</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>111.intdmf.com/autolottnotts.com</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>15.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>111.intdmf.com/autolotts.com</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>14.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>44teeth.com/contributors/loved-honda-msx-sold-...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4testpass.com/pass-driving-test/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>3</td>\n",
       "      <td>12.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a-professional-appraisal.ca/contact-us/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>19.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>a2bikes.co.uk/bike/kawasaki-ninja-300</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ah-ltd.co.uk/tax-rate-information/vehicle.html</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>allcarskent.co.uk/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>allegro.pl/kategoria/audi-rs7-146643</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>14.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>allegro.pl/kategoria/bmw-m2-255467</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>allegro.pl/kategoria/lancia-delta-12562</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>amp.autoweek.com/article/car-reviews/2017-chry...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>amp.autoweek.com/article/events/hurricane-harv...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>amp.businessinsider.com/best-first-cars-2012-1</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>14.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>amp.businessinsider.com/best-used-cars-for-fir...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>11.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>amp.ft.com/content/7fa6cd04-8e68-11e7-a352-e46...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>11.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>amp.ft.com/content/ad691d06-5620-11e4-bbd6-001...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>amp.ft.com/content/e66c9f97-6d35-3642-88a9-bc9...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>19.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>amp.reddit.com/r/MotoUK/comments/2u3asv/honda_...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>amp.reddit.com/r/askcarsales/comments/60wjgw/h...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>amp.theguardian.com/business/2017/aug/16/uk-ca...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>amp.theguardian.com/business/2017/jul/05/uk-ca...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>amp.theguardian.com/money/2014/feb/14/getting-...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>4</td>\n",
       "      <td>13.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>amp.theguardian.com/money/2014/jul/26/buy-new-...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>3</td>\n",
       "      <td>13.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>amp.theguardian.com/money/2016/feb/03/deal-tro...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>amp.theguardian.com/money/2017/feb/25/secondha...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>amp.theguardian.com/technology/2012/aug/24/kia...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>19.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>amp.theguardian.com/technology/2012/dec/09/chr...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>amp.theguardian.com/technology/2016/dec/04/hyu...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>amp.theguardian.com/technology/2016/jul/30/cit...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>19.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13384</th>\n",
       "      <td>www.youtube.com/watch?v=u-CUobmVO2s</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13385</th>\n",
       "      <td>www.youtube.com/watch?v=uLGMQ6WG9Kc</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13386</th>\n",
       "      <td>www.youtube.com/watch?v=uZ6Rmc_ljvg</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13387</th>\n",
       "      <td>www.youtube.com/watch?v=u_wOXj3IiT4</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>19.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13388</th>\n",
       "      <td>www.youtube.com/watch?v=v0B-0JR7suU</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13389</th>\n",
       "      <td>www.youtube.com/watch?v=vPu5wGV3854</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13390</th>\n",
       "      <td>www.youtube.com/watch?v=vWm-sncUhd8</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13391</th>\n",
       "      <td>www.youtube.com/watch?v=vYYQnZcQxwI</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13392</th>\n",
       "      <td>www.youtube.com/watch?v=vjcfE3zlOf4</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13393</th>\n",
       "      <td>www.youtube.com/watch?v=w--uV25yLWQ</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13394</th>\n",
       "      <td>www.youtube.com/watch?v=wmP6UdQX8qo</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13395</th>\n",
       "      <td>www.youtube.com/watch?v=x7kNKNAS2GY</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>14.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13396</th>\n",
       "      <td>www.youtube.com/watch?v=xgKjp1pq7iI</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13397</th>\n",
       "      <td>www.youtube.com/watch?v=ySvb44ucuIE</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13398</th>\n",
       "      <td>www.youtube.com/watch?v=zUKFG_Y2pBk</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13399</th>\n",
       "      <td>www.youtube.com/watch?v=zuNutWkhV8U</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13400</th>\n",
       "      <td>www.z-tech.co.uk/e-newz/bmw-c-evolution-a-few-...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>14.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13401</th>\n",
       "      <td>www.zeroto60times.com/compare-cars/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>3</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13402</th>\n",
       "      <td>www.zestcarrental.com/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13403</th>\n",
       "      <td>www.zigwheels.com/compare-cars</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>14.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13404</th>\n",
       "      <td>www.zigwheels.com/newbikes/KTM/Duke-390-ABS</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13405</th>\n",
       "      <td>www.zigwheels.com/newbikes/KTM/Duke-390-ABS/on...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13406</th>\n",
       "      <td>www.zigwheels.com/newcars</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13407</th>\n",
       "      <td>www.zigwheels.com/newcars/Lamborghini/Aventador</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13408</th>\n",
       "      <td>www.zigwheels.com/newcars/Lamborghini/Aventado...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13409</th>\n",
       "      <td>www.zigwheels.com/newcars/Lamborghini/Huracan</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13410</th>\n",
       "      <td>www.zigwheels.com/newcars?is_amp=1</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13411</th>\n",
       "      <td>www.zipcar.co.uk/check-rates/london</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13412</th>\n",
       "      <td>www.zoopla.co.uk/home-values/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>2</td>\n",
       "      <td>3.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13413</th>\n",
       "      <td>www.zuto.com/free-car-valuation/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>105</td>\n",
       "      <td>14.866667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13414 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Ranking URL on Sep  1, 2017 Market  Keyword  \\\n",
       "0                       111.intdmf.com/autolottnotts.com  GB-en        2   \n",
       "1                           111.intdmf.com/autolotts.com  GB-en        2   \n",
       "2      44teeth.com/contributors/loved-honda-msx-sold-...  GB-en        2   \n",
       "3                       4testpass.com/pass-driving-test/  GB-en        3   \n",
       "4                a-professional-appraisal.ca/contact-us/  GB-en        2   \n",
       "5                  a2bikes.co.uk/bike/kawasaki-ninja-300  GB-en        1   \n",
       "6         ah-ltd.co.uk/tax-rate-information/vehicle.html  GB-en        1   \n",
       "7                                     allcarskent.co.uk/  GB-en        1   \n",
       "8                   allegro.pl/kategoria/audi-rs7-146643  GB-en        2   \n",
       "9                     allegro.pl/kategoria/bmw-m2-255467  GB-en        1   \n",
       "10               allegro.pl/kategoria/lancia-delta-12562  GB-en        1   \n",
       "11     amp.autoweek.com/article/car-reviews/2017-chry...  GB-en        1   \n",
       "12     amp.autoweek.com/article/events/hurricane-harv...  GB-en        1   \n",
       "13        amp.businessinsider.com/best-first-cars-2012-1  GB-en        1   \n",
       "14     amp.businessinsider.com/best-used-cars-for-fir...  GB-en        1   \n",
       "15     amp.ft.com/content/7fa6cd04-8e68-11e7-a352-e46...  GB-en        1   \n",
       "16     amp.ft.com/content/ad691d06-5620-11e4-bbd6-001...  GB-en        2   \n",
       "17     amp.ft.com/content/e66c9f97-6d35-3642-88a9-bc9...  GB-en        1   \n",
       "18     amp.reddit.com/r/MotoUK/comments/2u3asv/honda_...  GB-en        1   \n",
       "19     amp.reddit.com/r/askcarsales/comments/60wjgw/h...  GB-en        1   \n",
       "20     amp.theguardian.com/business/2017/aug/16/uk-ca...  GB-en        1   \n",
       "21     amp.theguardian.com/business/2017/jul/05/uk-ca...  GB-en        1   \n",
       "22     amp.theguardian.com/money/2014/feb/14/getting-...  GB-en        4   \n",
       "23     amp.theguardian.com/money/2014/jul/26/buy-new-...  GB-en        3   \n",
       "24     amp.theguardian.com/money/2016/feb/03/deal-tro...  GB-en        2   \n",
       "25     amp.theguardian.com/money/2017/feb/25/secondha...  GB-en        2   \n",
       "26     amp.theguardian.com/technology/2012/aug/24/kia...  GB-en        1   \n",
       "27     amp.theguardian.com/technology/2012/dec/09/chr...  GB-en        1   \n",
       "28     amp.theguardian.com/technology/2016/dec/04/hyu...  GB-en        1   \n",
       "29     amp.theguardian.com/technology/2016/jul/30/cit...  GB-en        1   \n",
       "...                                                  ...    ...      ...   \n",
       "13384                www.youtube.com/watch?v=u-CUobmVO2s  GB-en        1   \n",
       "13385                www.youtube.com/watch?v=uLGMQ6WG9Kc  GB-en        1   \n",
       "13386                www.youtube.com/watch?v=uZ6Rmc_ljvg  GB-en        1   \n",
       "13387                www.youtube.com/watch?v=u_wOXj3IiT4  GB-en        1   \n",
       "13388                www.youtube.com/watch?v=v0B-0JR7suU  GB-en        1   \n",
       "13389                www.youtube.com/watch?v=vPu5wGV3854  GB-en        1   \n",
       "13390                www.youtube.com/watch?v=vWm-sncUhd8  GB-en        1   \n",
       "13391                www.youtube.com/watch?v=vYYQnZcQxwI  GB-en        1   \n",
       "13392                www.youtube.com/watch?v=vjcfE3zlOf4  GB-en        1   \n",
       "13393                www.youtube.com/watch?v=w--uV25yLWQ  GB-en        2   \n",
       "13394                www.youtube.com/watch?v=wmP6UdQX8qo  GB-en        1   \n",
       "13395                www.youtube.com/watch?v=x7kNKNAS2GY  GB-en        1   \n",
       "13396                www.youtube.com/watch?v=xgKjp1pq7iI  GB-en        1   \n",
       "13397                www.youtube.com/watch?v=ySvb44ucuIE  GB-en        1   \n",
       "13398                www.youtube.com/watch?v=zUKFG_Y2pBk  GB-en        1   \n",
       "13399                www.youtube.com/watch?v=zuNutWkhV8U  GB-en        1   \n",
       "13400  www.z-tech.co.uk/e-newz/bmw-c-evolution-a-few-...  GB-en        2   \n",
       "13401                www.zeroto60times.com/compare-cars/  GB-en        3   \n",
       "13402                             www.zestcarrental.com/  GB-en        1   \n",
       "13403                     www.zigwheels.com/compare-cars  GB-en        2   \n",
       "13404        www.zigwheels.com/newbikes/KTM/Duke-390-ABS  GB-en        1   \n",
       "13405  www.zigwheels.com/newbikes/KTM/Duke-390-ABS/on...  GB-en        1   \n",
       "13406                          www.zigwheels.com/newcars  GB-en        1   \n",
       "13407    www.zigwheels.com/newcars/Lamborghini/Aventador  GB-en        1   \n",
       "13408  www.zigwheels.com/newcars/Lamborghini/Aventado...  GB-en        1   \n",
       "13409      www.zigwheels.com/newcars/Lamborghini/Huracan  GB-en        1   \n",
       "13410                 www.zigwheels.com/newcars?is_amp=1  GB-en        1   \n",
       "13411                www.zipcar.co.uk/check-rates/london  GB-en        1   \n",
       "13412                      www.zoopla.co.uk/home-values/  GB-en        2   \n",
       "13413                   www.zuto.com/free-car-valuation/  GB-en      105   \n",
       "\n",
       "            Rank  \n",
       "0      15.500000  \n",
       "1      14.500000  \n",
       "2      17.000000  \n",
       "3      12.666667  \n",
       "4      19.500000  \n",
       "5      16.000000  \n",
       "6      13.000000  \n",
       "7      18.000000  \n",
       "8      14.500000  \n",
       "9      20.000000  \n",
       "10     20.000000  \n",
       "11     18.000000  \n",
       "12     10.000000  \n",
       "13     14.000000  \n",
       "14     11.000000  \n",
       "15     11.000000  \n",
       "16     15.000000  \n",
       "17     19.000000  \n",
       "18     18.000000  \n",
       "19     20.000000  \n",
       "20     20.000000  \n",
       "21     15.000000  \n",
       "22     13.750000  \n",
       "23     13.333333  \n",
       "24      5.000000  \n",
       "25     18.000000  \n",
       "26     19.000000  \n",
       "27     20.000000  \n",
       "28     20.000000  \n",
       "29     19.000000  \n",
       "...          ...  \n",
       "13384  20.000000  \n",
       "13385   6.000000  \n",
       "13386  10.000000  \n",
       "13387  19.000000  \n",
       "13388  18.000000  \n",
       "13389  10.000000  \n",
       "13390  15.000000  \n",
       "13391  17.000000  \n",
       "13392  15.000000  \n",
       "13393  12.000000  \n",
       "13394  16.000000  \n",
       "13395  14.000000  \n",
       "13396  20.000000  \n",
       "13397  16.000000  \n",
       "13398   4.000000  \n",
       "13399   7.000000  \n",
       "13400  14.500000  \n",
       "13401  12.000000  \n",
       "13402  17.000000  \n",
       "13403  14.000000  \n",
       "13404  16.000000  \n",
       "13405  15.000000  \n",
       "13406  12.000000  \n",
       "13407  13.000000  \n",
       "13408  16.000000  \n",
       "13409  18.000000  \n",
       "13410  13.000000  \n",
       "13411  17.000000  \n",
       "13412   3.500000  \n",
       "13413  14.866667  \n",
       "\n",
       "[13414 rows x 4 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_group"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sorting data\n",
    "\n",
    "The results we had above, would've been a lot more helpful if they were sorted. We want to see the URLs that rank for the most keywords, not just a list of URLs in alphabetical order."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does it do?:** Sorts a dataframe by selecting columns, in the case below we're sorting from biggest to smallest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ranking URL on Sep  1, 2017</th>\n",
       "      <th>Market</th>\n",
       "      <th>Keyword</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7163</th>\n",
       "      <td>www.google.co.uk/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>812</td>\n",
       "      <td>4.735222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5595</th>\n",
       "      <td>www.confused.com/car-valuation</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>568</td>\n",
       "      <td>2.228873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3018</th>\n",
       "      <td>www.autotrader.co.uk/car-valuation</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>425</td>\n",
       "      <td>2.082353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10030</th>\n",
       "      <td>www.parkers.co.uk/car-valuation/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>412</td>\n",
       "      <td>4.368932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13091</th>\n",
       "      <td>www.whatcar.com/valuations/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>402</td>\n",
       "      <td>4.407960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11095</th>\n",
       "      <td>www.raccars.co.uk/value-my-car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>399</td>\n",
       "      <td>6.210526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7124</th>\n",
       "      <td>www.glass.co.uk/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>385</td>\n",
       "      <td>9.379221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12779</th>\n",
       "      <td>www.webuyanycar.com/car-valuation/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>360</td>\n",
       "      <td>7.788889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>921</th>\n",
       "      <td>hpivaluations.com/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>319</td>\n",
       "      <td>7.401254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8632</th>\n",
       "      <td>www.lookers.co.uk/valuations/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>276</td>\n",
       "      <td>13.757246</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12703</th>\n",
       "      <td>www.vcars.co.uk/used-cars/valuation.php</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>232</td>\n",
       "      <td>8.719828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2750</th>\n",
       "      <td>www.autoexpress.co.uk/car-news/94359/car-valua...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>206</td>\n",
       "      <td>14.194175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8964</th>\n",
       "      <td>www.motoring.co.uk/free-car-valuation</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>200</td>\n",
       "      <td>12.010000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5969</th>\n",
       "      <td>www.driving.co.uk/car-valuations/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>165</td>\n",
       "      <td>14.872727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8341</th>\n",
       "      <td>www.kbb.com/whats-my-car-worth/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>150</td>\n",
       "      <td>12.386667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7857</th>\n",
       "      <td>www.honestjohn.co.uk/used-prices/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>143</td>\n",
       "      <td>11.937063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6494</th>\n",
       "      <td>www.evanshalshaw.com/sell-your-car/valuation/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>129</td>\n",
       "      <td>14.139535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8220</th>\n",
       "      <td>www.jct600.co.uk/valuation/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>121</td>\n",
       "      <td>14.537190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13413</th>\n",
       "      <td>www.zuto.com/free-car-valuation/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>105</td>\n",
       "      <td>14.866667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4932</th>\n",
       "      <td>www.cars.com/sell/book-value/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>101</td>\n",
       "      <td>14.712871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11492</th>\n",
       "      <td>www.techadvisor.co.uk/how-to/internet/how-buy-...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>94</td>\n",
       "      <td>13.819149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11491</th>\n",
       "      <td>www.techadvisor.co.uk/how-to/internet/how-buy-...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>93</td>\n",
       "      <td>14.193548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12789</th>\n",
       "      <td>www.webuycarstoday.co.uk/free-car-valuation</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>88</td>\n",
       "      <td>16.147727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8844</th>\n",
       "      <td>www.moneyadviceservice.org.uk/en/tools/car-cos...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>87</td>\n",
       "      <td>10.724138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12539</th>\n",
       "      <td>www.vauxhall.co.uk/offers-finance/value-my-car...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>87</td>\n",
       "      <td>15.022989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10032</th>\n",
       "      <td>www.parkers.co.uk/cars-for-sale/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>85</td>\n",
       "      <td>7.082353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3549</th>\n",
       "      <td>www.autoweb.co.uk/free-car-valuation</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>83</td>\n",
       "      <td>15.216867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9063</th>\n",
       "      <td>www.motors.co.uk/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>81</td>\n",
       "      <td>5.296296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1399</th>\n",
       "      <td>m.vauxhall.co.uk/requestsandservices/value-my-...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>80</td>\n",
       "      <td>15.287500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8852</th>\n",
       "      <td>www.moneysavingexpert.com/car-finance/buying-u...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>77</td>\n",
       "      <td>6.870130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9469</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/Ford-focu...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9467</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/Fiat-500-...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9466</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/Citroen-B...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>19.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2832</th>\n",
       "      <td>www.autoexpress.co.uk/ford/tourneo/64241/ford-...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9464</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/BMW-m3-sa...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9463</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/BMW-X6-le...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5067</th>\n",
       "      <td>www.caruk.net/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>19.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5066</th>\n",
       "      <td>www.cartrade.com/sell-used-car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9480</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/bmw-1_ser...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>9.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2829</th>\n",
       "      <td>www.autoexpress.co.uk/ford/ranger/18588/ford-r...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9482</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/business-...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9483</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/guides/bu...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9502</th>\n",
       "      <td>www.networkq.co.uk/used-car-search/insignia/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6693</th>\n",
       "      <td>www.exchangeandmart.co.uk/used-cars-for-sale/c...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>19.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9500</th>\n",
       "      <td>www.networkq.co.uk/used-car-search/astra/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5076</th>\n",
       "      <td>www.carwale.com/m/bmw-cars/i8/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5074</th>\n",
       "      <td>www.carwale.com/m/astonmartin-cars/vanquish/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5073</th>\n",
       "      <td>www.carwale.com/lamborghini-cars/huracan/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9494</th>\n",
       "      <td>www.neneoverland.co.uk/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5071</th>\n",
       "      <td>www.carwale.com/comparecars/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9492</th>\n",
       "      <td>www.ncp.co.uk/find-a-car-park</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>17.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9491</th>\n",
       "      <td>www.nc700-forum.com/forum/nc750-general-discus...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>14.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9490</th>\n",
       "      <td>www.nc700-forum.com/forum/nc750-general-discus...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>16.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5070</th>\n",
       "      <td>www.carwale.com/bmw-cars/i8/</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9488</th>\n",
       "      <td>www.nba.com/video/2017/07/24/20170724-top-100-...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9487</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/hot-leasi...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>20.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9486</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/guides/pe...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9485</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/guides/pe...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9484</th>\n",
       "      <td>www.nationwidevehiclecontracts.co.uk/guides/ne...</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>18.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5788</th>\n",
       "      <td>www.desperateseller.co.uk/cheap-cars/bmw</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>1</td>\n",
       "      <td>15.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13414 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             Ranking URL on Sep  1, 2017 Market  Keyword  \\\n",
       "7163                                   www.google.co.uk/  GB-en      812   \n",
       "5595                      www.confused.com/car-valuation  GB-en      568   \n",
       "3018                  www.autotrader.co.uk/car-valuation  GB-en      425   \n",
       "10030                   www.parkers.co.uk/car-valuation/  GB-en      412   \n",
       "13091                        www.whatcar.com/valuations/  GB-en      402   \n",
       "11095                     www.raccars.co.uk/value-my-car  GB-en      399   \n",
       "7124                                    www.glass.co.uk/  GB-en      385   \n",
       "12779                 www.webuyanycar.com/car-valuation/  GB-en      360   \n",
       "921                                   hpivaluations.com/  GB-en      319   \n",
       "8632                       www.lookers.co.uk/valuations/  GB-en      276   \n",
       "12703            www.vcars.co.uk/used-cars/valuation.php  GB-en      232   \n",
       "2750   www.autoexpress.co.uk/car-news/94359/car-valua...  GB-en      206   \n",
       "8964               www.motoring.co.uk/free-car-valuation  GB-en      200   \n",
       "5969                   www.driving.co.uk/car-valuations/  GB-en      165   \n",
       "8341                     www.kbb.com/whats-my-car-worth/  GB-en      150   \n",
       "7857                   www.honestjohn.co.uk/used-prices/  GB-en      143   \n",
       "6494       www.evanshalshaw.com/sell-your-car/valuation/  GB-en      129   \n",
       "8220                         www.jct600.co.uk/valuation/  GB-en      121   \n",
       "13413                   www.zuto.com/free-car-valuation/  GB-en      105   \n",
       "4932                       www.cars.com/sell/book-value/  GB-en      101   \n",
       "11492  www.techadvisor.co.uk/how-to/internet/how-buy-...  GB-en       94   \n",
       "11491  www.techadvisor.co.uk/how-to/internet/how-buy-...  GB-en       93   \n",
       "12789        www.webuycarstoday.co.uk/free-car-valuation  GB-en       88   \n",
       "8844   www.moneyadviceservice.org.uk/en/tools/car-cos...  GB-en       87   \n",
       "12539  www.vauxhall.co.uk/offers-finance/value-my-car...  GB-en       87   \n",
       "10032                   www.parkers.co.uk/cars-for-sale/  GB-en       85   \n",
       "3549                www.autoweb.co.uk/free-car-valuation  GB-en       83   \n",
       "9063                                   www.motors.co.uk/  GB-en       81   \n",
       "1399   m.vauxhall.co.uk/requestsandservices/value-my-...  GB-en       80   \n",
       "8852   www.moneysavingexpert.com/car-finance/buying-u...  GB-en       77   \n",
       "...                                                  ...    ...      ...   \n",
       "9469   www.nationwidevehiclecontracts.co.uk/Ford-focu...  GB-en        1   \n",
       "9467   www.nationwidevehiclecontracts.co.uk/Fiat-500-...  GB-en        1   \n",
       "9466   www.nationwidevehiclecontracts.co.uk/Citroen-B...  GB-en        1   \n",
       "2832   www.autoexpress.co.uk/ford/tourneo/64241/ford-...  GB-en        1   \n",
       "9464   www.nationwidevehiclecontracts.co.uk/BMW-m3-sa...  GB-en        1   \n",
       "9463   www.nationwidevehiclecontracts.co.uk/BMW-X6-le...  GB-en        1   \n",
       "5067                                      www.caruk.net/  GB-en        1   \n",
       "5066                      www.cartrade.com/sell-used-car  GB-en        1   \n",
       "9480   www.nationwidevehiclecontracts.co.uk/bmw-1_ser...  GB-en        1   \n",
       "2829   www.autoexpress.co.uk/ford/ranger/18588/ford-r...  GB-en        1   \n",
       "9482   www.nationwidevehiclecontracts.co.uk/business-...  GB-en        1   \n",
       "9483   www.nationwidevehiclecontracts.co.uk/guides/bu...  GB-en        1   \n",
       "9502        www.networkq.co.uk/used-car-search/insignia/  GB-en        1   \n",
       "6693   www.exchangeandmart.co.uk/used-cars-for-sale/c...  GB-en        1   \n",
       "9500           www.networkq.co.uk/used-car-search/astra/  GB-en        1   \n",
       "5076                      www.carwale.com/m/bmw-cars/i8/  GB-en        1   \n",
       "5074        www.carwale.com/m/astonmartin-cars/vanquish/  GB-en        1   \n",
       "5073           www.carwale.com/lamborghini-cars/huracan/  GB-en        1   \n",
       "9494                             www.neneoverland.co.uk/  GB-en        1   \n",
       "5071                        www.carwale.com/comparecars/  GB-en        1   \n",
       "9492                       www.ncp.co.uk/find-a-car-park  GB-en        1   \n",
       "9491   www.nc700-forum.com/forum/nc750-general-discus...  GB-en        1   \n",
       "9490   www.nc700-forum.com/forum/nc750-general-discus...  GB-en        1   \n",
       "5070                        www.carwale.com/bmw-cars/i8/  GB-en        1   \n",
       "9488   www.nba.com/video/2017/07/24/20170724-top-100-...  GB-en        1   \n",
       "9487   www.nationwidevehiclecontracts.co.uk/hot-leasi...  GB-en        1   \n",
       "9486   www.nationwidevehiclecontracts.co.uk/guides/pe...  GB-en        1   \n",
       "9485   www.nationwidevehiclecontracts.co.uk/guides/pe...  GB-en        1   \n",
       "9484   www.nationwidevehiclecontracts.co.uk/guides/ne...  GB-en        1   \n",
       "5788            www.desperateseller.co.uk/cheap-cars/bmw  GB-en        1   \n",
       "\n",
       "            Rank  \n",
       "7163    4.735222  \n",
       "5595    2.228873  \n",
       "3018    2.082353  \n",
       "10030   4.368932  \n",
       "13091   4.407960  \n",
       "11095   6.210526  \n",
       "7124    9.379221  \n",
       "12779   7.788889  \n",
       "921     7.401254  \n",
       "8632   13.757246  \n",
       "12703   8.719828  \n",
       "2750   14.194175  \n",
       "8964   12.010000  \n",
       "5969   14.872727  \n",
       "8341   12.386667  \n",
       "7857   11.937063  \n",
       "6494   14.139535  \n",
       "8220   14.537190  \n",
       "13413  14.866667  \n",
       "4932   14.712871  \n",
       "11492  13.819149  \n",
       "11491  14.193548  \n",
       "12789  16.147727  \n",
       "8844   10.724138  \n",
       "12539  15.022989  \n",
       "10032   7.082353  \n",
       "3549   15.216867  \n",
       "9063    5.296296  \n",
       "1399   15.287500  \n",
       "8852    6.870130  \n",
       "...          ...  \n",
       "9469   18.000000  \n",
       "9467   15.000000  \n",
       "9466   19.000000  \n",
       "2832   16.000000  \n",
       "9464    7.000000  \n",
       "9463    4.000000  \n",
       "5067   19.000000  \n",
       "5066   18.000000  \n",
       "9480    9.000000  \n",
       "2829    8.000000  \n",
       "9482   13.000000  \n",
       "9483   10.000000  \n",
       "9502    5.000000  \n",
       "6693   19.000000  \n",
       "9500    8.000000  \n",
       "5076   16.000000  \n",
       "5074   20.000000  \n",
       "5073   10.000000  \n",
       "9494   20.000000  \n",
       "5071   20.000000  \n",
       "9492   17.000000  \n",
       "9491   14.000000  \n",
       "9490   16.000000  \n",
       "5070   18.000000  \n",
       "9488   15.000000  \n",
       "9487   20.000000  \n",
       "9486   13.000000  \n",
       "9485   10.000000  \n",
       "9484   18.000000  \n",
       "5788   15.000000  \n",
       "\n",
       "[13414 rows x 4 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_group.sort_values(['Keyword'], ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Displaying more data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So as you've probably noticed above, the results are truncated in the middle. (Look for the row which contains ... in the above cell.)\n",
    "\n",
    "Jupyter notebook does this to avoid printing out everything to your screen. In the case that you do need more columns, rows or wider columns you can set them with the settings below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd.options.display.max_rows = 60\n",
    "pd.options.display.max_columns = 20\n",
    "pd.options.display.max_colwidth = 50"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Joining together multiple datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What if you have more than one dataset and need to join them together? This is a pretty common scenario that we'll now cover.\n",
    "\n",
    "There are two possible ways we might need to join:\n",
    "1. Joining together two CSVs with the same columns, because our data is split across multiple files\n",
    "2. Joining together a new dataset with our dataset based on matching values (the kind of thing you'd use vlookup or index match for in Excel.\n",
    "\n",
    "## 1. Joining together CSV's with the same columns.\n",
    "\n",
    "For this we've created a fake second dataset that we're going to load and join on the bottom of ours."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df2 = pd.read_csv(\"Example Car SERPs - Small Second Set.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we prepare our data by putting all of our dataframes into a list (which is what the square brackets [] are doing)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "list_of_df = [df, df2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** This function takes a list of dataframes and stacks them one on top of the other."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "both_dfs = pd.concat(list_of_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see how they've been put on top of each other by looking at the size of each individually and the two together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(39600, 18)\n",
      "(20, 13)\n",
      "(39620, 18)\n"
     ]
    }
   ],
   "source": [
    "print(df.shape)\n",
    "print(df2.shape)\n",
    "print(both_dfs.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Joining together data based on matching values\n",
    "\n",
    "So previously we've made a poor search visibility metric by multiplying search volume by rank. Now we can make a better search visibility metric by adding in some CTR rate estimates.\n",
    "\n",
    "We start by loading in our CTR dataset and take a quick look at it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ctr = pd.read_csv(\"ctr_graph.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rank</th>\n",
       "      <th>Device</th>\n",
       "      <th>CTR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>desktop</td>\n",
       "      <td>27.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>desktop</td>\n",
       "      <td>12.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>desktop</td>\n",
       "      <td>8.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>desktop</td>\n",
       "      <td>5.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>desktop</td>\n",
       "      <td>3.84</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rank   Device    CTR\n",
       "0     1  desktop  27.09\n",
       "1     2  desktop  12.61\n",
       "2     3  desktop   8.42\n",
       "3     4  desktop   5.48\n",
       "4     5  desktop   3.84"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ctr.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we need to join CTR with our ranking data. That means we need to match the correct CTR based on both Device and Rank.\n",
    "\n",
    "**What does this do?:** The following function takes two datasets and joins them together. Joining data actualy requires you specify a fair few things, so it's worth explaining the different common options here. (Remember shift+tab inside pd.merge will show the documentation!)\n",
    "\n",
    "The first two things we've put in `merge` are always are the two dataframes we want to join. \n",
    "\n",
    "Then we need to say how they want to join. Left join, means we'll take all the rows from the first df and only the rows from the second df where a match exists.\n",
    "\n",
    "Finally we need to say what are we joining on. In this case we're selecting the correct value from the CTR where both rank and device match."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 387,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_with_ctr = pd.merge(df, ctr, how=\"left\", on=[\"Rank\", \"Device\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 390,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Keyword</th>\n",
       "      <th>Market</th>\n",
       "      <th>Location</th>\n",
       "      <th>Device</th>\n",
       "      <th>Regional Monthly Search Volume</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Result Types for Jan  1, 2017</th>\n",
       "      <th>Protocol for Jan  1, 2017</th>\n",
       "      <th>Ranking URL on Jan  1, 2017</th>\n",
       "      <th>Result Types for Sep  1, 2017</th>\n",
       "      <th>Protocol for Sep  1, 2017</th>\n",
       "      <th>Ranking URL on Sep  1, 2017</th>\n",
       "      <th>Tags</th>\n",
       "      <th>CTR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>answers / list</td>\n",
       "      <td>http</td>\n",
       "      <td>www.autoexpress.co.uk/best-cars/98556/the-best...</td>\n",
       "      <td>!permanent keywords / auto express / featured ...</td>\n",
       "      <td>27.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>https</td>\n",
       "      <td>www.carwow.co.uk/best/cars-for-sale-with-0-per...</td>\n",
       "      <td>!permanent keywords / auto express / featured ...</td>\n",
       "      <td>12.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Keyword Market  Location   Device  Regional Monthly Search Volume  \\\n",
       "0  0 apr car deals  GB-en       NaN  desktop                          1900.0   \n",
       "1  0 apr car deals  GB-en       NaN  desktop                          1900.0   \n",
       "\n",
       "   Rank Result Types for Jan  1, 2017 Protocol for Jan  1, 2017  \\\n",
       "0     1                           NaN                       NaN   \n",
       "1     2                           NaN                       NaN   \n",
       "\n",
       "  Ranking URL on Jan  1, 2017 Result Types for Sep  1, 2017  \\\n",
       "0                         NaN                answers / list   \n",
       "1                         NaN                       organic   \n",
       "\n",
       "  Protocol for Sep  1, 2017  \\\n",
       "0                      http   \n",
       "1                     https   \n",
       "\n",
       "                         Ranking URL on Sep  1, 2017  \\\n",
       "0  www.autoexpress.co.uk/best-cars/98556/the-best...   \n",
       "1  www.carwow.co.uk/best/cars-for-sale-with-0-per...   \n",
       "\n",
       "                                                Tags    CTR  \n",
       "0  !permanent keywords / auto express / featured ...  27.09  \n",
       "1  !permanent keywords / auto express / featured ...  12.61  "
      ]
     },
     "execution_count": 390,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_with_ctr.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we just need to generate the correct search visibility, which we can do using the same method we had above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 394,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_with_ctr['search_visibility'] = df_with_ctr['Regional Monthly Search Volume']*df_with_ctr['CTR']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Optional - extra information on join types\n",
    "\n",
    "Join types are best explained with venn diagrams, following screenshot is taken from [this article](http://www.sql-join.com/sql-join-types/), although its for SQL I highly recommend reading the whole thing.\n",
    "\n",
    "![title](join_types_diagram_specific.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with URLs\n",
    "\n",
    "A quick interlude. This isn't technically a part of basic data manipulation, but I've found I'll end up doing this so much, that it's worth including it in here. \n",
    "\n",
    "Typically the usecase is we'll want to take a URL and extract the individual components from it (path, request, protocol, domain etc. etc.)\n",
    "\n",
    "So let's do that! \n",
    "\n",
    "**Warning:** Here we're unavoidably going to see some more complexity. Hopefully you should be able to follow it conceptually, even if the actuall ins and outs of the functions get a little complicated.\n",
    "\n",
    "This is moving a bit beyond basic manipulation here, we'll be using the output from these cells later so even if you don't follow it make sure to run them!\n",
    "\n",
    "Onwards. We're going to need a new library to help us with this, so let's load that first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from urllib.parse import urlparse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This part is a little more complicated. We're going to write a custom function and apply it row by row. This is the same method we used above in the bonus section. But it should be nice and copy and pastable for when you need it :)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_url_parts(url):\n",
    "    ''' Takes a URL and returns a tuple of the decomposed URL\n",
    "    \n",
    "    Returns: Tuple of: protocol, hostname, path, query and fragment.\n",
    "    '''\n",
    "    parsed_url = urlparse(url)\n",
    "    # Skip params here and return query string as a whole. Params require different analysis\n",
    "    # We're returning as tuple, because of how we're going to attach to the dataframe.\n",
    "    ordered_values = tuple(value for key,value in parsed_url._asdict().items() if key != \"params\")\n",
    "    \n",
    "    return ordered_values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Due to how we're adding the columns, we need to name the columns we're about to add:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "columns = [\n",
    "    'protocol',\n",
    "    'hostname',\n",
    "    'path',\n",
    "    'query',\n",
    "    'fragment'\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then STAT has inconviently split procotol and URL into separate columns, which will break our function so we need to join them togther."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['Ranking Full URL on Sep  1, 2017'] = df['Protocol for Sep  1, 2017'] + \"://\" + df['Ranking URL on Sep  1, 2017']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At this point before we're good to go, we do a little data manipulation. First our row might have empty cells in. Pandas by default treats empty cells as numbers (floats to be specific). This is going to cause us some issues when we're working with strings, so we want to convert our empty cells explicitly to empty strings. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['Ranking Full URL on Sep  1, 2017'] = df['Ranking Full URL on Sep  1, 2017'].fillna('')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally our data is ready to go and we can create the new columns.\n",
    "\n",
    "**What does this do:** It takes a function which returns multiple values, applies it to a column and then adds the resulting columns (named in columns), to our dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "temp = list(zip(*df['Ranking Full URL on Sep  1, 2017'].map(get_url_parts)))\n",
    "for i, c in enumerate(columns): \n",
    "    df[c] = temp[i]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Look at our shiny new columns!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Keyword</th>\n",
       "      <th>Market</th>\n",
       "      <th>Location</th>\n",
       "      <th>Device</th>\n",
       "      <th>Regional Monthly Search Volume</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Result Types for Jan  1, 2017</th>\n",
       "      <th>Protocol for Jan  1, 2017</th>\n",
       "      <th>Ranking URL on Jan  1, 2017</th>\n",
       "      <th>Result Types for Sep  1, 2017</th>\n",
       "      <th>Protocol for Sep  1, 2017</th>\n",
       "      <th>Ranking URL on Sep  1, 2017</th>\n",
       "      <th>Tags</th>\n",
       "      <th>Ranking Full URL on Sep  1, 2017</th>\n",
       "      <th>protocol</th>\n",
       "      <th>hostname</th>\n",
       "      <th>path</th>\n",
       "      <th>query</th>\n",
       "      <th>fragment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>answers / list</td>\n",
       "      <td>http</td>\n",
       "      <td>www.autoexpress.co.uk/best-cars/98556/the-best...</td>\n",
       "      <td>!permanent keywords / auto express / featured ...</td>\n",
       "      <td>http://www.autoexpress.co.uk/best-cars/98556/t...</td>\n",
       "      <td>http</td>\n",
       "      <td>www.autoexpress.co.uk</td>\n",
       "      <td>/best-cars/98556/the-best-new-0-finance-car-deals</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>https</td>\n",
       "      <td>www.carwow.co.uk/best/cars-for-sale-with-0-per...</td>\n",
       "      <td>!permanent keywords / auto express / featured ...</td>\n",
       "      <td>https://www.carwow.co.uk/best/cars-for-sale-wi...</td>\n",
       "      <td>https</td>\n",
       "      <td>www.carwow.co.uk</td>\n",
       "      <td>/best/cars-for-sale-with-0-per-cent-finance</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Keyword Market  Location   Device  Regional Monthly Search Volume  \\\n",
       "0  0 apr car deals  GB-en       NaN  desktop                          1900.0   \n",
       "1  0 apr car deals  GB-en       NaN  desktop                          1900.0   \n",
       "\n",
       "   Rank Result Types for Jan  1, 2017 Protocol for Jan  1, 2017  \\\n",
       "0     1                           NaN                       NaN   \n",
       "1     2                           NaN                       NaN   \n",
       "\n",
       "  Ranking URL on Jan  1, 2017 Result Types for Sep  1, 2017  \\\n",
       "0                         NaN                answers / list   \n",
       "1                         NaN                       organic   \n",
       "\n",
       "  Protocol for Sep  1, 2017  \\\n",
       "0                      http   \n",
       "1                     https   \n",
       "\n",
       "                         Ranking URL on Sep  1, 2017  \\\n",
       "0  www.autoexpress.co.uk/best-cars/98556/the-best...   \n",
       "1  www.carwow.co.uk/best/cars-for-sale-with-0-per...   \n",
       "\n",
       "                                                Tags  \\\n",
       "0  !permanent keywords / auto express / featured ...   \n",
       "1  !permanent keywords / auto express / featured ...   \n",
       "\n",
       "                    Ranking Full URL on Sep  1, 2017 protocol  \\\n",
       "0  http://www.autoexpress.co.uk/best-cars/98556/t...     http   \n",
       "1  https://www.carwow.co.uk/best/cars-for-sale-wi...    https   \n",
       "\n",
       "                hostname                                               path  \\\n",
       "0  www.autoexpress.co.uk  /best-cars/98556/the-best-new-0-finance-car-deals   \n",
       "1       www.carwow.co.uk        /best/cars-for-sale-with-0-per-cent-finance   \n",
       "\n",
       "  query fragment  \n",
       "0                 \n",
       "1                 "
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Moving from wide data to stacked data\n",
    "\n",
    "Let's take a quick look at our STAT data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Keyword</th>\n",
       "      <th>Market</th>\n",
       "      <th>Location</th>\n",
       "      <th>Device</th>\n",
       "      <th>Regional Monthly Search Volume</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Result Types for Jan  1, 2017</th>\n",
       "      <th>Protocol for Jan  1, 2017</th>\n",
       "      <th>Ranking URL on Jan  1, 2017</th>\n",
       "      <th>Result Types for Sep  1, 2017</th>\n",
       "      <th>Protocol for Sep  1, 2017</th>\n",
       "      <th>Ranking URL on Sep  1, 2017</th>\n",
       "      <th>Tags</th>\n",
       "      <th>Ranking Full URL on Sep  1, 2017</th>\n",
       "      <th>protocol</th>\n",
       "      <th>hostname</th>\n",
       "      <th>path</th>\n",
       "      <th>query</th>\n",
       "      <th>fragment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>answers / list</td>\n",
       "      <td>http</td>\n",
       "      <td>www.autoexpress.co.uk/best-cars/98556/the-best...</td>\n",
       "      <td>!permanent keywords / auto express / featured ...</td>\n",
       "      <td>http://www.autoexpress.co.uk/best-cars/98556/t...</td>\n",
       "      <td>http</td>\n",
       "      <td>www.autoexpress.co.uk</td>\n",
       "      <td>/best-cars/98556/the-best-new-0-finance-car-deals</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Keyword Market  Location   Device  Regional Monthly Search Volume  \\\n",
       "0  0 apr car deals  GB-en       NaN  desktop                          1900.0   \n",
       "\n",
       "   Rank Result Types for Jan  1, 2017 Protocol for Jan  1, 2017  \\\n",
       "0     1                           NaN                       NaN   \n",
       "\n",
       "  Ranking URL on Jan  1, 2017 Result Types for Sep  1, 2017  \\\n",
       "0                         NaN                answers / list   \n",
       "\n",
       "  Protocol for Sep  1, 2017  \\\n",
       "0                      http   \n",
       "\n",
       "                         Ranking URL on Sep  1, 2017  \\\n",
       "0  www.autoexpress.co.uk/best-cars/98556/the-best...   \n",
       "\n",
       "                                                Tags  \\\n",
       "0  !permanent keywords / auto express / featured ...   \n",
       "\n",
       "                    Ranking Full URL on Sep  1, 2017 protocol  \\\n",
       "0  http://www.autoexpress.co.uk/best-cars/98556/t...     http   \n",
       "\n",
       "                hostname                                               path  \\\n",
       "0  www.autoexpress.co.uk  /best-cars/98556/the-best-new-0-finance-car-deals   \n",
       "\n",
       "  query fragment  \n",
       "0                 "
      ]
     },
     "execution_count": 274,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you've probably noticed at this point, there are two columns for the different dates. This is great for comparing two individual days, because they're side by side, however often we want the data in stacked format.\n",
    "\n",
    "That means that each date is a row and in this case that would give us a unified set of columns names. \n",
    "\n",
    "This is particularly useful if we then want to analysis over periods such as week by week for example. To prepare for that let's try to alter out STAT data so it fits that format. This will let us show off one particularly useful pandas function."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First however we need to do some data prep. As mentioned above STAT has inconviently split procotol and URL into separate columns, which will break our function so we need to join them togther."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 331,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['Ranking Full URL on Sep  1, 2017'] = df['Protocol for Sep  1, 2017'] + \"://\" + df['Ranking URL on Sep  1, 2017']\n",
    "df['Ranking Full URL on Jan  1, 2017'] = df['Protocol for Jan  1, 2017'] + \"://\" + df['Ranking URL on Jan  1, 2017']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Temporary aside: Re-naming columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The function we're about to use, will also turn the column headers into row values, so we need to set the column headers so they're just the dates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 332,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.rename(columns={\n",
    "    'Ranking Full URL on Sep  1, 2017':'2017-09-01', \n",
    "    'Ranking Full URL on Jan  1, 2017':'2017-01-01'\n",
    "}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What does this do?:** This amazing function turns columns into rows. Anything in id_vars will stay fixed, any columns in value_vars will be the columns used for stacking.\n",
    "\n",
    "In our example below, we're turning our two ranking URL columns into stacked data and preserving anything else interesting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 333,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_melt = pd.melt(df, \n",
    "        id_vars=['Keyword',\n",
    "                 'Market',\n",
    "                 'Location',\n",
    "                 'Device', \n",
    "                 'Regional Monthly Search Volume',\n",
    "                 'Rank',\n",
    "                 'Result Types for Jan  1, 2017',\n",
    "                 'Result Types for Sep  1, 2017'\n",
    "                ], \n",
    "        value_vars=[\n",
    "                    '2017-01-01', \n",
    "                    '2017-09-01'\n",
    "                   ],\n",
    "        var_name=\"Date\",\n",
    "        value_name=\"url\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at what we've done:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 334,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Keyword</th>\n",
       "      <th>Market</th>\n",
       "      <th>Location</th>\n",
       "      <th>Device</th>\n",
       "      <th>Regional Monthly Search Volume</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Result Types for Jan  1, 2017</th>\n",
       "      <th>Result Types for Sep  1, 2017</th>\n",
       "      <th>Date</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>answers / list</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>answers / list</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79170</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>11</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.cargurus.co.uk/Cars/instantMarketV...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79171</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>12</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.vcars.co.uk/used-cars/valuation.php</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79172</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>13</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.financial-ombudsman.org.uk/publicat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79173</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>14</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.financial-ombudsman.org.uk/publicat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79174</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>15</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.gocompare.com/car-insurance/vehicle...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79175</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>16</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.buyacar.co.uk/cars/used-cars/474/fi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79176</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>17</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.accountingweb.co.uk/any-answers/ma...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79177</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>18</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.accountingweb.co.uk/any-answers/ho...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79178</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>19</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.redbook.com.au/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79179</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>20</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.jct600.co.uk/valuation/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79180</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>1</td>\n",
       "      <td>organic</td>\n",
       "      <td>answers / paragraph</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.confused.com/car-valuation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79181</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>2</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.confused.com/car-valuation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79182</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>3</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.autotrader.co.uk/car-valuation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79183</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>4</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.whatcar.com/valuations/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79184</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>5</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.parkers.co.uk/car-valuation/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79185</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>6</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.raccars.co.uk/value-my-car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79186</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>7</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://hpivaluations.com/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79187</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>8</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.webuyanycar.com/car-valuation/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79188</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>9</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.glass.co.uk/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79189</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>10</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.motoring.co.uk/free-car-valuation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79190</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>11</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.cargurus.co.uk/Cars/instantMarketV...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79191</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>12</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.mycarinfo.com.my/MarketValue</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79192</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>13</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.vcars.co.uk/used-cars/valuation.php</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79193</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>14</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.honestjohn.co.uk/used-prices/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79194</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>15</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.racq.com.au/cars-and-driving/cars/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79195</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>16</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.accountingweb.co.uk/any-answers/ma...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79196</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>17</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.financial-ombudsman.org.uk/publicat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79197</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>18</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.gocompare.com/car-insurance/vehicle...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79198</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>19</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.jct600.co.uk/valuation/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79199</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>20</td>\n",
       "      <td>organic</td>\n",
       "      <td>organic</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://m.redbook.com.au/</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>79200 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Keyword Market  Location      Device  \\\n",
       "0          0 apr car deals  GB-en       NaN     desktop   \n",
       "1          0 apr car deals  GB-en       NaN     desktop   \n",
       "2          0 apr car deals  GB-en       NaN     desktop   \n",
       "3          0 apr car deals  GB-en       NaN     desktop   \n",
       "4          0 apr car deals  GB-en       NaN     desktop   \n",
       "5          0 apr car deals  GB-en       NaN     desktop   \n",
       "6          0 apr car deals  GB-en       NaN     desktop   \n",
       "7          0 apr car deals  GB-en       NaN     desktop   \n",
       "8          0 apr car deals  GB-en       NaN     desktop   \n",
       "9          0 apr car deals  GB-en       NaN     desktop   \n",
       "10         0 apr car deals  GB-en       NaN     desktop   \n",
       "11         0 apr car deals  GB-en       NaN     desktop   \n",
       "12         0 apr car deals  GB-en       NaN     desktop   \n",
       "13         0 apr car deals  GB-en       NaN     desktop   \n",
       "14         0 apr car deals  GB-en       NaN     desktop   \n",
       "15         0 apr car deals  GB-en       NaN     desktop   \n",
       "16         0 apr car deals  GB-en       NaN     desktop   \n",
       "17         0 apr car deals  GB-en       NaN     desktop   \n",
       "18         0 apr car deals  GB-en       NaN     desktop   \n",
       "19         0 apr car deals  GB-en       NaN     desktop   \n",
       "20     0 car finance deals  GB-en       NaN     desktop   \n",
       "21     0 car finance deals  GB-en       NaN     desktop   \n",
       "22     0 car finance deals  GB-en       NaN     desktop   \n",
       "23     0 car finance deals  GB-en       NaN     desktop   \n",
       "24     0 car finance deals  GB-en       NaN     desktop   \n",
       "25     0 car finance deals  GB-en       NaN     desktop   \n",
       "26     0 car finance deals  GB-en       NaN     desktop   \n",
       "27     0 car finance deals  GB-en       NaN     desktop   \n",
       "28     0 car finance deals  GB-en       NaN     desktop   \n",
       "29     0 car finance deals  GB-en       NaN     desktop   \n",
       "...                    ...    ...       ...         ...   \n",
       "79170  market value of car  GB-en       NaN     desktop   \n",
       "79171  market value of car  GB-en       NaN     desktop   \n",
       "79172  market value of car  GB-en       NaN     desktop   \n",
       "79173  market value of car  GB-en       NaN     desktop   \n",
       "79174  market value of car  GB-en       NaN     desktop   \n",
       "79175  market value of car  GB-en       NaN     desktop   \n",
       "79176  market value of car  GB-en       NaN     desktop   \n",
       "79177  market value of car  GB-en       NaN     desktop   \n",
       "79178  market value of car  GB-en       NaN     desktop   \n",
       "79179  market value of car  GB-en       NaN     desktop   \n",
       "79180  market value of car  GB-en       NaN  smartphone   \n",
       "79181  market value of car  GB-en       NaN  smartphone   \n",
       "79182  market value of car  GB-en       NaN  smartphone   \n",
       "79183  market value of car  GB-en       NaN  smartphone   \n",
       "79184  market value of car  GB-en       NaN  smartphone   \n",
       "79185  market value of car  GB-en       NaN  smartphone   \n",
       "79186  market value of car  GB-en       NaN  smartphone   \n",
       "79187  market value of car  GB-en       NaN  smartphone   \n",
       "79188  market value of car  GB-en       NaN  smartphone   \n",
       "79189  market value of car  GB-en       NaN  smartphone   \n",
       "79190  market value of car  GB-en       NaN  smartphone   \n",
       "79191  market value of car  GB-en       NaN  smartphone   \n",
       "79192  market value of car  GB-en       NaN  smartphone   \n",
       "79193  market value of car  GB-en       NaN  smartphone   \n",
       "79194  market value of car  GB-en       NaN  smartphone   \n",
       "79195  market value of car  GB-en       NaN  smartphone   \n",
       "79196  market value of car  GB-en       NaN  smartphone   \n",
       "79197  market value of car  GB-en       NaN  smartphone   \n",
       "79198  market value of car  GB-en       NaN  smartphone   \n",
       "79199  market value of car  GB-en       NaN  smartphone   \n",
       "\n",
       "       Regional Monthly Search Volume  Rank Result Types for Jan  1, 2017  \\\n",
       "0                              1900.0     1                           NaN   \n",
       "1                              1900.0     2                           NaN   \n",
       "2                              1900.0     3                           NaN   \n",
       "3                              1900.0     4                           NaN   \n",
       "4                              1900.0     5                           NaN   \n",
       "5                              1900.0     6                           NaN   \n",
       "6                              1900.0     7                           NaN   \n",
       "7                              1900.0     8                           NaN   \n",
       "8                              1900.0     9                           NaN   \n",
       "9                              1900.0    10                           NaN   \n",
       "10                             1900.0    11                           NaN   \n",
       "11                             1900.0    12                           NaN   \n",
       "12                             1900.0    13                           NaN   \n",
       "13                             1900.0    14                           NaN   \n",
       "14                             1900.0    15                           NaN   \n",
       "15                             1900.0    16                           NaN   \n",
       "16                             1900.0    17                           NaN   \n",
       "17                             1900.0    18                           NaN   \n",
       "18                             1900.0    19                           NaN   \n",
       "19                             1900.0    20                           NaN   \n",
       "20                             4400.0     1                           NaN   \n",
       "21                             4400.0     2                           NaN   \n",
       "22                             4400.0     3                           NaN   \n",
       "23                             4400.0     4                           NaN   \n",
       "24                             4400.0     5                           NaN   \n",
       "25                             4400.0     6                           NaN   \n",
       "26                             4400.0     7                           NaN   \n",
       "27                             4400.0     8                           NaN   \n",
       "28                             4400.0     9                           NaN   \n",
       "29                             4400.0    10                           NaN   \n",
       "...                               ...   ...                           ...   \n",
       "79170                          1300.0    11                       organic   \n",
       "79171                          1300.0    12                       organic   \n",
       "79172                          1300.0    13                       organic   \n",
       "79173                          1300.0    14                       organic   \n",
       "79174                          1300.0    15                       organic   \n",
       "79175                          1300.0    16                       organic   \n",
       "79176                          1300.0    17                       organic   \n",
       "79177                          1300.0    18                       organic   \n",
       "79178                          1300.0    19                       organic   \n",
       "79179                          1300.0    20                       organic   \n",
       "79180                          1300.0     1                       organic   \n",
       "79181                          1300.0     2                       organic   \n",
       "79182                          1300.0     3                       organic   \n",
       "79183                          1300.0     4                       organic   \n",
       "79184                          1300.0     5                       organic   \n",
       "79185                          1300.0     6                       organic   \n",
       "79186                          1300.0     7                       organic   \n",
       "79187                          1300.0     8                       organic   \n",
       "79188                          1300.0     9                       organic   \n",
       "79189                          1300.0    10                       organic   \n",
       "79190                          1300.0    11                       organic   \n",
       "79191                          1300.0    12                       organic   \n",
       "79192                          1300.0    13                       organic   \n",
       "79193                          1300.0    14                       organic   \n",
       "79194                          1300.0    15                       organic   \n",
       "79195                          1300.0    16                       organic   \n",
       "79196                          1300.0    17                       organic   \n",
       "79197                          1300.0    18                       organic   \n",
       "79198                          1300.0    19                       organic   \n",
       "79199                          1300.0    20                       organic   \n",
       "\n",
       "      Result Types for Sep  1, 2017        Date  \\\n",
       "0                    answers / list  2017-01-01   \n",
       "1                           organic  2017-01-01   \n",
       "2                           organic  2017-01-01   \n",
       "3                           organic  2017-01-01   \n",
       "4                           organic  2017-01-01   \n",
       "5                           organic  2017-01-01   \n",
       "6                           organic  2017-01-01   \n",
       "7                           organic  2017-01-01   \n",
       "8                           organic  2017-01-01   \n",
       "9                           organic  2017-01-01   \n",
       "10                          organic  2017-01-01   \n",
       "11                          organic  2017-01-01   \n",
       "12                          organic  2017-01-01   \n",
       "13                          organic  2017-01-01   \n",
       "14                          organic  2017-01-01   \n",
       "15                          organic  2017-01-01   \n",
       "16                          organic  2017-01-01   \n",
       "17                          organic  2017-01-01   \n",
       "18                          organic  2017-01-01   \n",
       "19                          organic  2017-01-01   \n",
       "20                   answers / list  2017-01-01   \n",
       "21                          organic  2017-01-01   \n",
       "22                          organic  2017-01-01   \n",
       "23                          organic  2017-01-01   \n",
       "24                          organic  2017-01-01   \n",
       "25                          organic  2017-01-01   \n",
       "26                          organic  2017-01-01   \n",
       "27                          organic  2017-01-01   \n",
       "28                          organic  2017-01-01   \n",
       "29                          organic  2017-01-01   \n",
       "...                             ...         ...   \n",
       "79170                       organic  2017-09-01   \n",
       "79171                       organic  2017-09-01   \n",
       "79172                       organic  2017-09-01   \n",
       "79173                       organic  2017-09-01   \n",
       "79174                       organic  2017-09-01   \n",
       "79175                       organic  2017-09-01   \n",
       "79176                       organic  2017-09-01   \n",
       "79177                       organic  2017-09-01   \n",
       "79178                       organic  2017-09-01   \n",
       "79179                       organic  2017-09-01   \n",
       "79180           answers / paragraph  2017-09-01   \n",
       "79181                       organic  2017-09-01   \n",
       "79182                       organic  2017-09-01   \n",
       "79183                       organic  2017-09-01   \n",
       "79184                       organic  2017-09-01   \n",
       "79185                       organic  2017-09-01   \n",
       "79186                       organic  2017-09-01   \n",
       "79187                       organic  2017-09-01   \n",
       "79188                       organic  2017-09-01   \n",
       "79189                       organic  2017-09-01   \n",
       "79190                       organic  2017-09-01   \n",
       "79191                       organic  2017-09-01   \n",
       "79192                       organic  2017-09-01   \n",
       "79193                       organic  2017-09-01   \n",
       "79194                       organic  2017-09-01   \n",
       "79195                       organic  2017-09-01   \n",
       "79196                       organic  2017-09-01   \n",
       "79197                       organic  2017-09-01   \n",
       "79198                       organic  2017-09-01   \n",
       "79199                       organic  2017-09-01   \n",
       "\n",
       "                                                     url  \n",
       "0                                                    NaN  \n",
       "1                                                    NaN  \n",
       "2                                                    NaN  \n",
       "3                                                    NaN  \n",
       "4                                                    NaN  \n",
       "5                                                    NaN  \n",
       "6                                                    NaN  \n",
       "7                                                    NaN  \n",
       "8                                                    NaN  \n",
       "9                                                    NaN  \n",
       "10                                                   NaN  \n",
       "11                                                   NaN  \n",
       "12                                                   NaN  \n",
       "13                                                   NaN  \n",
       "14                                                   NaN  \n",
       "15                                                   NaN  \n",
       "16                                                   NaN  \n",
       "17                                                   NaN  \n",
       "18                                                   NaN  \n",
       "19                                                   NaN  \n",
       "20                                                   NaN  \n",
       "21                                                   NaN  \n",
       "22                                                   NaN  \n",
       "23                                                   NaN  \n",
       "24                                                   NaN  \n",
       "25                                                   NaN  \n",
       "26                                                   NaN  \n",
       "27                                                   NaN  \n",
       "28                                                   NaN  \n",
       "29                                                   NaN  \n",
       "...                                                  ...  \n",
       "79170  https://www.cargurus.co.uk/Cars/instantMarketV...  \n",
       "79171    https://www.vcars.co.uk/used-cars/valuation.php  \n",
       "79172  http://www.financial-ombudsman.org.uk/publicat...  \n",
       "79173  http://www.financial-ombudsman.org.uk/publicat...  \n",
       "79174  http://www.gocompare.com/car-insurance/vehicle...  \n",
       "79175  http://www.buyacar.co.uk/cars/used-cars/474/fi...  \n",
       "79176  https://www.accountingweb.co.uk/any-answers/ma...  \n",
       "79177  https://www.accountingweb.co.uk/any-answers/ho...  \n",
       "79178                        https://www.redbook.com.au/  \n",
       "79179                https://www.jct600.co.uk/valuation/  \n",
       "79180             https://www.confused.com/car-valuation  \n",
       "79181             https://www.confused.com/car-valuation  \n",
       "79182         https://www.autotrader.co.uk/car-valuation  \n",
       "79183                https://www.whatcar.com/valuations/  \n",
       "79184            http://www.parkers.co.uk/car-valuation/  \n",
       "79185              http://www.raccars.co.uk/value-my-car  \n",
       "79186                         https://hpivaluations.com/  \n",
       "79187         https://www.webuyanycar.com/car-valuation/  \n",
       "79188                            http://www.glass.co.uk/  \n",
       "79189      https://www.motoring.co.uk/free-car-valuation  \n",
       "79190  https://www.cargurus.co.uk/Cars/instantMarketV...  \n",
       "79191           https://www.mycarinfo.com.my/MarketValue  \n",
       "79192    https://www.vcars.co.uk/used-cars/valuation.php  \n",
       "79193          https://www.honestjohn.co.uk/used-prices/  \n",
       "79194  https://www.racq.com.au/cars-and-driving/cars/...  \n",
       "79195  https://www.accountingweb.co.uk/any-answers/ma...  \n",
       "79196  http://www.financial-ombudsman.org.uk/publicat...  \n",
       "79197  http://www.gocompare.com/car-insurance/vehicle...  \n",
       "79198                https://www.jct600.co.uk/valuation/  \n",
       "79199                          https://m.redbook.com.au/  \n",
       "\n",
       "[79200 rows x 10 columns]"
      ]
     },
     "execution_count": 334,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_melt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However we're still not quite there, unfortunately we can't melt both result types and ranking URL at the same time, as they'll both end up stacked and we want result type alongside ranking URL. So now we can use some of the techniques we learnt earlier to get to the correct format."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're going to define a custom function and use to return a single column that will be correct."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 339,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_correct_result_type(row):\n",
    "    if row['Date'] == \"2017-01-01\":\n",
    "        return row['Result Types for Jan  1, 2017']\n",
    "    else:\n",
    "        return row['Result Types for Sep  1, 2017']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 340,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_melt['result_type'] = df_melt.apply(lambda x: get_correct_result_type(x), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Temporary Aside: Deleting columns\n",
    "\n",
    "Now we can drop the result types we're not going to use. \n",
    "\n",
    "**What does this do?:** It deletes the list of columns provided. If you set axis=0 then it will try to delete rows. We use inplace=True to delete rows from df_melt rather than return a new dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 343,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_melt.drop(['Result Types for Jan  1, 2017', 'Result Types for Sep  1, 2017'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can take a look at our new stacked data format!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 344,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Keyword</th>\n",
       "      <th>Market</th>\n",
       "      <th>Location</th>\n",
       "      <th>Device</th>\n",
       "      <th>Regional Monthly Search Volume</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Date</th>\n",
       "      <th>url</th>\n",
       "      <th>result_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>6</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>7</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>8</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>10</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>11</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>12</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>13</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>14</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>15</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>16</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>17</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>18</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>19</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0 apr car deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>20</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>6</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>7</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>8</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0 car finance deals</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>10</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79170</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>11</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.cargurus.co.uk/Cars/instantMarketV...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79171</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>12</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.vcars.co.uk/used-cars/valuation.php</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79172</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>13</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.financial-ombudsman.org.uk/publicat...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79173</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>14</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.financial-ombudsman.org.uk/publicat...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79174</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>15</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.gocompare.com/car-insurance/vehicle...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79175</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>16</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.buyacar.co.uk/cars/used-cars/474/fi...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79176</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>17</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.accountingweb.co.uk/any-answers/ma...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79177</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>18</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.accountingweb.co.uk/any-answers/ho...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79178</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>19</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.redbook.com.au/</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79179</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>desktop</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>20</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.jct600.co.uk/valuation/</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79180</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.confused.com/car-valuation</td>\n",
       "      <td>answers / paragraph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79181</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.confused.com/car-valuation</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79182</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.autotrader.co.uk/car-valuation</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79183</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.whatcar.com/valuations/</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79184</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.parkers.co.uk/car-valuation/</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79185</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>6</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.raccars.co.uk/value-my-car</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79186</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>7</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://hpivaluations.com/</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79187</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>8</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.webuyanycar.com/car-valuation/</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79188</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.glass.co.uk/</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79189</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>10</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.motoring.co.uk/free-car-valuation</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79190</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>11</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.cargurus.co.uk/Cars/instantMarketV...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79191</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>12</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.mycarinfo.com.my/MarketValue</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79192</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>13</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.vcars.co.uk/used-cars/valuation.php</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79193</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>14</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.honestjohn.co.uk/used-prices/</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79194</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>15</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.racq.com.au/cars-and-driving/cars/...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79195</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>16</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.accountingweb.co.uk/any-answers/ma...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79196</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>17</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.financial-ombudsman.org.uk/publicat...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79197</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>18</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>http://www.gocompare.com/car-insurance/vehicle...</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79198</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>19</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://www.jct600.co.uk/valuation/</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79199</th>\n",
       "      <td>market value of car</td>\n",
       "      <td>GB-en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>smartphone</td>\n",
       "      <td>1300.0</td>\n",
       "      <td>20</td>\n",
       "      <td>2017-09-01</td>\n",
       "      <td>https://m.redbook.com.au/</td>\n",
       "      <td>organic</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>79200 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Keyword Market  Location      Device  \\\n",
       "0          0 apr car deals  GB-en       NaN     desktop   \n",
       "1          0 apr car deals  GB-en       NaN     desktop   \n",
       "2          0 apr car deals  GB-en       NaN     desktop   \n",
       "3          0 apr car deals  GB-en       NaN     desktop   \n",
       "4          0 apr car deals  GB-en       NaN     desktop   \n",
       "5          0 apr car deals  GB-en       NaN     desktop   \n",
       "6          0 apr car deals  GB-en       NaN     desktop   \n",
       "7          0 apr car deals  GB-en       NaN     desktop   \n",
       "8          0 apr car deals  GB-en       NaN     desktop   \n",
       "9          0 apr car deals  GB-en       NaN     desktop   \n",
       "10         0 apr car deals  GB-en       NaN     desktop   \n",
       "11         0 apr car deals  GB-en       NaN     desktop   \n",
       "12         0 apr car deals  GB-en       NaN     desktop   \n",
       "13         0 apr car deals  GB-en       NaN     desktop   \n",
       "14         0 apr car deals  GB-en       NaN     desktop   \n",
       "15         0 apr car deals  GB-en       NaN     desktop   \n",
       "16         0 apr car deals  GB-en       NaN     desktop   \n",
       "17         0 apr car deals  GB-en       NaN     desktop   \n",
       "18         0 apr car deals  GB-en       NaN     desktop   \n",
       "19         0 apr car deals  GB-en       NaN     desktop   \n",
       "20     0 car finance deals  GB-en       NaN     desktop   \n",
       "21     0 car finance deals  GB-en       NaN     desktop   \n",
       "22     0 car finance deals  GB-en       NaN     desktop   \n",
       "23     0 car finance deals  GB-en       NaN     desktop   \n",
       "24     0 car finance deals  GB-en       NaN     desktop   \n",
       "25     0 car finance deals  GB-en       NaN     desktop   \n",
       "26     0 car finance deals  GB-en       NaN     desktop   \n",
       "27     0 car finance deals  GB-en       NaN     desktop   \n",
       "28     0 car finance deals  GB-en       NaN     desktop   \n",
       "29     0 car finance deals  GB-en       NaN     desktop   \n",
       "...                    ...    ...       ...         ...   \n",
       "79170  market value of car  GB-en       NaN     desktop   \n",
       "79171  market value of car  GB-en       NaN     desktop   \n",
       "79172  market value of car  GB-en       NaN     desktop   \n",
       "79173  market value of car  GB-en       NaN     desktop   \n",
       "79174  market value of car  GB-en       NaN     desktop   \n",
       "79175  market value of car  GB-en       NaN     desktop   \n",
       "79176  market value of car  GB-en       NaN     desktop   \n",
       "79177  market value of car  GB-en       NaN     desktop   \n",
       "79178  market value of car  GB-en       NaN     desktop   \n",
       "79179  market value of car  GB-en       NaN     desktop   \n",
       "79180  market value of car  GB-en       NaN  smartphone   \n",
       "79181  market value of car  GB-en       NaN  smartphone   \n",
       "79182  market value of car  GB-en       NaN  smartphone   \n",
       "79183  market value of car  GB-en       NaN  smartphone   \n",
       "79184  market value of car  GB-en       NaN  smartphone   \n",
       "79185  market value of car  GB-en       NaN  smartphone   \n",
       "79186  market value of car  GB-en       NaN  smartphone   \n",
       "79187  market value of car  GB-en       NaN  smartphone   \n",
       "79188  market value of car  GB-en       NaN  smartphone   \n",
       "79189  market value of car  GB-en       NaN  smartphone   \n",
       "79190  market value of car  GB-en       NaN  smartphone   \n",
       "79191  market value of car  GB-en       NaN  smartphone   \n",
       "79192  market value of car  GB-en       NaN  smartphone   \n",
       "79193  market value of car  GB-en       NaN  smartphone   \n",
       "79194  market value of car  GB-en       NaN  smartphone   \n",
       "79195  market value of car  GB-en       NaN  smartphone   \n",
       "79196  market value of car  GB-en       NaN  smartphone   \n",
       "79197  market value of car  GB-en       NaN  smartphone   \n",
       "79198  market value of car  GB-en       NaN  smartphone   \n",
       "79199  market value of car  GB-en       NaN  smartphone   \n",
       "\n",
       "       Regional Monthly Search Volume  Rank        Date  \\\n",
       "0                              1900.0     1  2017-01-01   \n",
       "1                              1900.0     2  2017-01-01   \n",
       "2                              1900.0     3  2017-01-01   \n",
       "3                              1900.0     4  2017-01-01   \n",
       "4                              1900.0     5  2017-01-01   \n",
       "5                              1900.0     6  2017-01-01   \n",
       "6                              1900.0     7  2017-01-01   \n",
       "7                              1900.0     8  2017-01-01   \n",
       "8                              1900.0     9  2017-01-01   \n",
       "9                              1900.0    10  2017-01-01   \n",
       "10                             1900.0    11  2017-01-01   \n",
       "11                             1900.0    12  2017-01-01   \n",
       "12                             1900.0    13  2017-01-01   \n",
       "13                             1900.0    14  2017-01-01   \n",
       "14                             1900.0    15  2017-01-01   \n",
       "15                             1900.0    16  2017-01-01   \n",
       "16                             1900.0    17  2017-01-01   \n",
       "17                             1900.0    18  2017-01-01   \n",
       "18                             1900.0    19  2017-01-01   \n",
       "19                             1900.0    20  2017-01-01   \n",
       "20                             4400.0     1  2017-01-01   \n",
       "21                             4400.0     2  2017-01-01   \n",
       "22                             4400.0     3  2017-01-01   \n",
       "23                             4400.0     4  2017-01-01   \n",
       "24                             4400.0     5  2017-01-01   \n",
       "25                             4400.0     6  2017-01-01   \n",
       "26                             4400.0     7  2017-01-01   \n",
       "27                             4400.0     8  2017-01-01   \n",
       "28                             4400.0     9  2017-01-01   \n",
       "29                             4400.0    10  2017-01-01   \n",
       "...                               ...   ...         ...   \n",
       "79170                          1300.0    11  2017-09-01   \n",
       "79171                          1300.0    12  2017-09-01   \n",
       "79172                          1300.0    13  2017-09-01   \n",
       "79173                          1300.0    14  2017-09-01   \n",
       "79174                          1300.0    15  2017-09-01   \n",
       "79175                          1300.0    16  2017-09-01   \n",
       "79176                          1300.0    17  2017-09-01   \n",
       "79177                          1300.0    18  2017-09-01   \n",
       "79178                          1300.0    19  2017-09-01   \n",
       "79179                          1300.0    20  2017-09-01   \n",
       "79180                          1300.0     1  2017-09-01   \n",
       "79181                          1300.0     2  2017-09-01   \n",
       "79182                          1300.0     3  2017-09-01   \n",
       "79183                          1300.0     4  2017-09-01   \n",
       "79184                          1300.0     5  2017-09-01   \n",
       "79185                          1300.0     6  2017-09-01   \n",
       "79186                          1300.0     7  2017-09-01   \n",
       "79187                          1300.0     8  2017-09-01   \n",
       "79188                          1300.0     9  2017-09-01   \n",
       "79189                          1300.0    10  2017-09-01   \n",
       "79190                          1300.0    11  2017-09-01   \n",
       "79191                          1300.0    12  2017-09-01   \n",
       "79192                          1300.0    13  2017-09-01   \n",
       "79193                          1300.0    14  2017-09-01   \n",
       "79194                          1300.0    15  2017-09-01   \n",
       "79195                          1300.0    16  2017-09-01   \n",
       "79196                          1300.0    17  2017-09-01   \n",
       "79197                          1300.0    18  2017-09-01   \n",
       "79198                          1300.0    19  2017-09-01   \n",
       "79199                          1300.0    20  2017-09-01   \n",
       "\n",
       "                                                     url          result_type  \n",
       "0                                                    NaN                  NaN  \n",
       "1                                                    NaN                  NaN  \n",
       "2                                                    NaN                  NaN  \n",
       "3                                                    NaN                  NaN  \n",
       "4                                                    NaN                  NaN  \n",
       "5                                                    NaN                  NaN  \n",
       "6                                                    NaN                  NaN  \n",
       "7                                                    NaN                  NaN  \n",
       "8                                                    NaN                  NaN  \n",
       "9                                                    NaN                  NaN  \n",
       "10                                                   NaN                  NaN  \n",
       "11                                                   NaN                  NaN  \n",
       "12                                                   NaN                  NaN  \n",
       "13                                                   NaN                  NaN  \n",
       "14                                                   NaN                  NaN  \n",
       "15                                                   NaN                  NaN  \n",
       "16                                                   NaN                  NaN  \n",
       "17                                                   NaN                  NaN  \n",
       "18                                                   NaN                  NaN  \n",
       "19                                                   NaN                  NaN  \n",
       "20                                                   NaN                  NaN  \n",
       "21                                                   NaN                  NaN  \n",
       "22                                                   NaN                  NaN  \n",
       "23                                                   NaN                  NaN  \n",
       "24                                                   NaN                  NaN  \n",
       "25                                                   NaN                  NaN  \n",
       "26                                                   NaN                  NaN  \n",
       "27                                                   NaN                  NaN  \n",
       "28                                                   NaN                  NaN  \n",
       "29                                                   NaN                  NaN  \n",
       "...                                                  ...                  ...  \n",
       "79170  https://www.cargurus.co.uk/Cars/instantMarketV...              organic  \n",
       "79171    https://www.vcars.co.uk/used-cars/valuation.php              organic  \n",
       "79172  http://www.financial-ombudsman.org.uk/publicat...              organic  \n",
       "79173  http://www.financial-ombudsman.org.uk/publicat...              organic  \n",
       "79174  http://www.gocompare.com/car-insurance/vehicle...              organic  \n",
       "79175  http://www.buyacar.co.uk/cars/used-cars/474/fi...              organic  \n",
       "79176  https://www.accountingweb.co.uk/any-answers/ma...              organic  \n",
       "79177  https://www.accountingweb.co.uk/any-answers/ho...              organic  \n",
       "79178                        https://www.redbook.com.au/              organic  \n",
       "79179                https://www.jct600.co.uk/valuation/              organic  \n",
       "79180             https://www.confused.com/car-valuation  answers / paragraph  \n",
       "79181             https://www.confused.com/car-valuation              organic  \n",
       "79182         https://www.autotrader.co.uk/car-valuation              organic  \n",
       "79183                https://www.whatcar.com/valuations/              organic  \n",
       "79184            http://www.parkers.co.uk/car-valuation/              organic  \n",
       "79185              http://www.raccars.co.uk/value-my-car              organic  \n",
       "79186                         https://hpivaluations.com/              organic  \n",
       "79187         https://www.webuyanycar.com/car-valuation/              organic  \n",
       "79188                            http://www.glass.co.uk/              organic  \n",
       "79189      https://www.motoring.co.uk/free-car-valuation              organic  \n",
       "79190  https://www.cargurus.co.uk/Cars/instantMarketV...              organic  \n",
       "79191           https://www.mycarinfo.com.my/MarketValue              organic  \n",
       "79192    https://www.vcars.co.uk/used-cars/valuation.php              organic  \n",
       "79193          https://www.honestjohn.co.uk/used-prices/              organic  \n",
       "79194  https://www.racq.com.au/cars-and-driving/cars/...              organic  \n",
       "79195  https://www.accountingweb.co.uk/any-answers/ma...              organic  \n",
       "79196  http://www.financial-ombudsman.org.uk/publicat...              organic  \n",
       "79197  http://www.gocompare.com/car-insurance/vehicle...              organic  \n",
       "79198                https://www.jct600.co.uk/valuation/              organic  \n",
       "79199                          https://m.redbook.com.au/              organic  \n",
       "\n",
       "[79200 rows x 9 columns]"
      ]
     },
     "execution_count": 344,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_melt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TBED: \n",
    "- Need to expand on pivoting and melting and various use cases.\n",
    "- profiling code\n",
    "- progress bars"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}