githubstats/data_wrangling.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Wrangling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Flow"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Imgur](http://i.imgur.com/W5hfGVo.png)\n",
    "\n",
    "Mining data directly from GitHub, `Viz` is powered by the [GitHub API](https://developer.github.com/v3/) and leverages the following:\n",
    "\n",
    "* [`github3.py`](https://github.com/sigmavirus24/github3.py) to access the GitHub API through Python.\n",
    "* [`pandas`](https://github.com/pydata/pandas) in the following [IPython Notebook](https://github.com/donnemartin/viz/blob/master/githubstats/data_wrangling.ipynb) for data wrangling.\n",
    "* [Google Maps API](https://developers.google.com/maps/?hl=en) through [`geocoder`](https://github.com/DenisCarriere/geocoder) for location data.\n",
    "* [Tableau Public](https://public.tableau.com/s/) for visualizations.*\n",
    "\n",
    "In the future, [Google BigQuery](https://cloud.google.com/bigquery/) along with [GitHub Archive](https://www.githubarchive.org/) could also supplement the GitHub API."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prepare Repo Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the repos data and drop duplicates:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape before dropping duplicates (8697, 5)\n",
      "Shape after  dropping duplicates (8697, 5)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>full_name</th>\n",
       "      <th>stars</th>\n",
       "      <th>forks</th>\n",
       "      <th>description</th>\n",
       "      <th>language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>thedaviddias/Front-End-Checklist</td>\n",
       "      <td>24267</td>\n",
       "      <td>2058</td>\n",
       "      <td>🗂 The perfect Front-End Checklist for modern w...</td>\n",
       "      <td>JavaScript</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GoogleChrome/puppeteer</td>\n",
       "      <td>21976</td>\n",
       "      <td>1259</td>\n",
       "      <td>Headless Chrome Node API</td>\n",
       "      <td>JavaScript</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>parcel-bundler/parcel</td>\n",
       "      <td>13981</td>\n",
       "      <td>463</td>\n",
       "      <td>📦🚀 Blazing fast, zero configuration web applic...</td>\n",
       "      <td>JavaScript</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chalarangelo/30-seconds-of-code</td>\n",
       "      <td>13466</td>\n",
       "      <td>1185</td>\n",
       "      <td>Curated collection of useful Javascript snippe...</td>\n",
       "      <td>JavaScript</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>wearehive/project-guidelines</td>\n",
       "      <td>11279</td>\n",
       "      <td>970</td>\n",
       "      <td>A set of best practices for JavaScript projects</td>\n",
       "      <td>JavaScript</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          full_name  stars  forks  \\\n",
       "0  thedaviddias/Front-End-Checklist  24267   2058   \n",
       "1            GoogleChrome/puppeteer  21976   1259   \n",
       "2             parcel-bundler/parcel  13981    463   \n",
       "3   Chalarangelo/30-seconds-of-code  13466   1185   \n",
       "4      wearehive/project-guidelines  11279    970   \n",
       "\n",
       "                                         description    language  \n",
       "0  🗂 The perfect Front-End Checklist for modern w...  JavaScript  \n",
       "1                           Headless Chrome Node API  JavaScript  \n",
       "2  📦🚀 Blazing fast, zero configuration web applic...  JavaScript  \n",
       "3  Curated collection of useful Javascript snippe...  JavaScript  \n",
       "4    A set of best practices for JavaScript projects  JavaScript  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repos = pd.read_csv(\"data/2017/repos-dump.csv\", quotechar='\"', skipinitialspace=True)\n",
    "print('Shape before dropping duplicates', repos.shape)\n",
    "repos = repos.drop_duplicates(subset='full_name', keep='last')\n",
    "print('Shape after  dropping duplicates', repos.shape)\n",
    "repos.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Separate out the `user` and `repo` from `full_name` into new columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(8697, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>full_name</th>\n",
       "      <th>stars</th>\n",
       "      <th>forks</th>\n",
       "      <th>description</th>\n",
       "      <th>language</th>\n",
       "      <th>user</th>\n",
       "      <th>repo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>thedaviddias/Front-End-Checklist</td>\n",
       "      <td>24267</td>\n",
       "      <td>2058</td>\n",
       "      <td>🗂 The perfect Front-End Checklist for modern w...</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>thedaviddias</td>\n",
       "      <td>Front-End-Checklist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GoogleChrome/puppeteer</td>\n",
       "      <td>21976</td>\n",
       "      <td>1259</td>\n",
       "      <td>Headless Chrome Node API</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>GoogleChrome</td>\n",
       "      <td>puppeteer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>parcel-bundler/parcel</td>\n",
       "      <td>13981</td>\n",
       "      <td>463</td>\n",
       "      <td>📦🚀 Blazing fast, zero configuration web applic...</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>parcel-bundler</td>\n",
       "      <td>parcel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chalarangelo/30-seconds-of-code</td>\n",
       "      <td>13466</td>\n",
       "      <td>1185</td>\n",
       "      <td>Curated collection of useful Javascript snippe...</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>Chalarangelo</td>\n",
       "      <td>30-seconds-of-code</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>wearehive/project-guidelines</td>\n",
       "      <td>11279</td>\n",
       "      <td>970</td>\n",
       "      <td>A set of best practices for JavaScript projects</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>wearehive</td>\n",
       "      <td>project-guidelines</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          full_name  stars  forks  \\\n",
       "0  thedaviddias/Front-End-Checklist  24267   2058   \n",
       "1            GoogleChrome/puppeteer  21976   1259   \n",
       "2             parcel-bundler/parcel  13981    463   \n",
       "3   Chalarangelo/30-seconds-of-code  13466   1185   \n",
       "4      wearehive/project-guidelines  11279    970   \n",
       "\n",
       "                                         description    language  \\\n",
       "0  🗂 The perfect Front-End Checklist for modern w...  JavaScript   \n",
       "1                           Headless Chrome Node API  JavaScript   \n",
       "2  📦🚀 Blazing fast, zero configuration web applic...  JavaScript   \n",
       "3  Curated collection of useful Javascript snippe...  JavaScript   \n",
       "4    A set of best practices for JavaScript projects  JavaScript   \n",
       "\n",
       "             user                 repo  \n",
       "0    thedaviddias  Front-End-Checklist  \n",
       "1    GoogleChrome            puppeteer  \n",
       "2  parcel-bundler               parcel  \n",
       "3    Chalarangelo   30-seconds-of-code  \n",
       "4       wearehive   project-guidelines  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def extract_user(line):\n",
    "    return line.split('/')[0]\n",
    "\n",
    "def extract_repo(line):\n",
    "    return line.split('/')[1]\n",
    "\n",
    "repos['user'] = repos['full_name'].str[:].apply(extract_user)\n",
    "repos['repo'] = repos['full_name'].str[:].apply(extract_repo)\n",
    "print(repos.shape)\n",
    "repos.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prepare User Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the users data and drop duplicates:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape before dropping duplicates (6426, 8)\n",
      "Shape after  dropping duplicates (6426, 8)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>location</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>dns-violations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hannob</td>\n",
       "      <td>Hanno Böck</td>\n",
       "      <td>User</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>52.520007</td>\n",
       "      <td>13.404954</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>takecian</td>\n",
       "      <td>Takeshi Fujiki</td>\n",
       "      <td>User</td>\n",
       "      <td>Tokyo, Japan</td>\n",
       "      <td>35.689487</td>\n",
       "      <td>139.691706</td>\n",
       "      <td>Tokyo</td>\n",
       "      <td>Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>jtomschroeder</td>\n",
       "      <td>Tom Schroeder</td>\n",
       "      <td>User</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>41.878114</td>\n",
       "      <td>-87.629798</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>wapiflapi</td>\n",
       "      <td>Wannes Rombouts</td>\n",
       "      <td>User</td>\n",
       "      <td>France</td>\n",
       "      <td>46.227638</td>\n",
       "      <td>2.213749</td>\n",
       "      <td>NaN</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id             name          type      location        lat  \\\n",
       "0  dns-violations              NaN  Organization           NaN        NaN   \n",
       "1          hannob       Hanno Böck          User        Berlin  52.520007   \n",
       "2        takecian   Takeshi Fujiki          User  Tokyo, Japan  35.689487   \n",
       "3   jtomschroeder    Tom Schroeder          User       Chicago  41.878114   \n",
       "4       wapiflapi  Wannes Rombouts          User        France  46.227638   \n",
       "\n",
       "         long     city        country  \n",
       "0         NaN      NaN            NaN  \n",
       "1   13.404954   Berlin        Germany  \n",
       "2  139.691706    Tokyo          Japan  \n",
       "3  -87.629798  Chicago  United States  \n",
       "4    2.213749      NaN         France  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users = pd.read_csv(\"data/2017/user-geocodes-dump.csv\", quotechar='\"', skipinitialspace=True)\n",
    "print('Shape before dropping duplicates', users.shape)\n",
    "users = users.drop_duplicates(subset='id', keep='last')\n",
    "print('Shape after  dropping duplicates', users.shape)\n",
    "users.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rename column `id` to `user`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>location</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>dns-violations</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hannob</td>\n",
       "      <td>Hanno Böck</td>\n",
       "      <td>User</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>52.520007</td>\n",
       "      <td>13.404954</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>takecian</td>\n",
       "      <td>Takeshi Fujiki</td>\n",
       "      <td>User</td>\n",
       "      <td>Tokyo, Japan</td>\n",
       "      <td>35.689487</td>\n",
       "      <td>139.691706</td>\n",
       "      <td>Tokyo</td>\n",
       "      <td>Japan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>jtomschroeder</td>\n",
       "      <td>Tom Schroeder</td>\n",
       "      <td>User</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>41.878114</td>\n",
       "      <td>-87.629798</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>wapiflapi</td>\n",
       "      <td>Wannes Rombouts</td>\n",
       "      <td>User</td>\n",
       "      <td>France</td>\n",
       "      <td>46.227638</td>\n",
       "      <td>2.213749</td>\n",
       "      <td>NaN</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             user             name          type      location        lat  \\\n",
       "0  dns-violations              NaN  Organization           NaN        NaN   \n",
       "1          hannob       Hanno Böck          User        Berlin  52.520007   \n",
       "2        takecian   Takeshi Fujiki          User  Tokyo, Japan  35.689487   \n",
       "3   jtomschroeder    Tom Schroeder          User       Chicago  41.878114   \n",
       "4       wapiflapi  Wannes Rombouts          User        France  46.227638   \n",
       "\n",
       "         long     city        country  \n",
       "0         NaN      NaN            NaN  \n",
       "1   13.404954   Berlin        Germany  \n",
       "2  139.691706    Tokyo          Japan  \n",
       "3  -87.629798  Chicago  United States  \n",
       "4    2.213749      NaN         France  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.rename(columns={'id': 'user'}, inplace=True)\n",
    "users.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge Repo and User Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Left join repos and users:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape repos: (8697, 7)\n",
      "Shape users: (6426, 8)\n",
      "Shape repos_users: (8697, 14)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>full_name</th>\n",
       "      <th>stars</th>\n",
       "      <th>forks</th>\n",
       "      <th>description</th>\n",
       "      <th>language</th>\n",
       "      <th>user</th>\n",
       "      <th>repo</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>location</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>thedaviddias/Front-End-Checklist</td>\n",
       "      <td>24267</td>\n",
       "      <td>2058</td>\n",
       "      <td>🗂 The perfect Front-End Checklist for modern w...</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>thedaviddias</td>\n",
       "      <td>Front-End-Checklist</td>\n",
       "      <td>David Dias</td>\n",
       "      <td>User</td>\n",
       "      <td>France, Mauritius, Canada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GoogleChrome/puppeteer</td>\n",
       "      <td>21976</td>\n",
       "      <td>1259</td>\n",
       "      <td>Headless Chrome Node API</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>GoogleChrome</td>\n",
       "      <td>puppeteer</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>parcel-bundler/parcel</td>\n",
       "      <td>13981</td>\n",
       "      <td>463</td>\n",
       "      <td>📦🚀 Blazing fast, zero configuration web applic...</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>parcel-bundler</td>\n",
       "      <td>parcel</td>\n",
       "      <td>Parcel</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chalarangelo/30-seconds-of-code</td>\n",
       "      <td>13466</td>\n",
       "      <td>1185</td>\n",
       "      <td>Curated collection of useful Javascript snippe...</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>Chalarangelo</td>\n",
       "      <td>30-seconds-of-code</td>\n",
       "      <td>Angelos Chalaris</td>\n",
       "      <td>User</td>\n",
       "      <td>Athens, Greece</td>\n",
       "      <td>37.983810</td>\n",
       "      <td>23.727539</td>\n",
       "      <td>Athens</td>\n",
       "      <td>Greece</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>wearehive/project-guidelines</td>\n",
       "      <td>11279</td>\n",
       "      <td>970</td>\n",
       "      <td>A set of best practices for JavaScript projects</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>wearehive</td>\n",
       "      <td>project-guidelines</td>\n",
       "      <td>Hive</td>\n",
       "      <td>Organization</td>\n",
       "      <td>London</td>\n",
       "      <td>51.507351</td>\n",
       "      <td>-0.127758</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          full_name  stars  forks  \\\n",
       "0  thedaviddias/Front-End-Checklist  24267   2058   \n",
       "1            GoogleChrome/puppeteer  21976   1259   \n",
       "2             parcel-bundler/parcel  13981    463   \n",
       "3   Chalarangelo/30-seconds-of-code  13466   1185   \n",
       "4      wearehive/project-guidelines  11279    970   \n",
       "\n",
       "                                         description    language  \\\n",
       "0  🗂 The perfect Front-End Checklist for modern w...  JavaScript   \n",
       "1                           Headless Chrome Node API  JavaScript   \n",
       "2  📦🚀 Blazing fast, zero configuration web applic...  JavaScript   \n",
       "3  Curated collection of useful Javascript snippe...  JavaScript   \n",
       "4    A set of best practices for JavaScript projects  JavaScript   \n",
       "\n",
       "             user                 repo              name          type  \\\n",
       "0    thedaviddias  Front-End-Checklist        David Dias          User   \n",
       "1    GoogleChrome            puppeteer               NaN  Organization   \n",
       "2  parcel-bundler               parcel            Parcel  Organization   \n",
       "3    Chalarangelo   30-seconds-of-code  Angelos Chalaris          User   \n",
       "4       wearehive   project-guidelines              Hive  Organization   \n",
       "\n",
       "                    location        lat       long    city         country  \n",
       "0  France, Mauritius, Canada        NaN        NaN     NaN             NaN  \n",
       "1                        NaN        NaN        NaN     NaN             NaN  \n",
       "2                        NaN        NaN        NaN     NaN             NaN  \n",
       "3             Athens, Greece  37.983810  23.727539  Athens          Greece  \n",
       "4                     London  51.507351  -0.127758  London  United Kingdom  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repos_users = pd.merge(repos, users, on='user', how='left')\n",
    "print('Shape repos:', repos.shape)\n",
    "print('Shape users:', users.shape)\n",
    "print('Shape repos_users:', repos_users.shape)\n",
    "repos_users.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tidy Up Repo and User Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Re-order the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(8697, 14)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>full_name</th>\n",
       "      <th>repo</th>\n",
       "      <th>description</th>\n",
       "      <th>stars</th>\n",
       "      <th>forks</th>\n",
       "      <th>language</th>\n",
       "      <th>user</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>location</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>thedaviddias/Front-End-Checklist</td>\n",
       "      <td>Front-End-Checklist</td>\n",
       "      <td>🗂 The perfect Front-End Checklist for modern w...</td>\n",
       "      <td>24267</td>\n",
       "      <td>2058</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>thedaviddias</td>\n",
       "      <td>David Dias</td>\n",
       "      <td>User</td>\n",
       "      <td>France, Mauritius, Canada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GoogleChrome/puppeteer</td>\n",
       "      <td>puppeteer</td>\n",
       "      <td>Headless Chrome Node API</td>\n",
       "      <td>21976</td>\n",
       "      <td>1259</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>GoogleChrome</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>parcel-bundler/parcel</td>\n",
       "      <td>parcel</td>\n",
       "      <td>📦🚀 Blazing fast, zero configuration web applic...</td>\n",
       "      <td>13981</td>\n",
       "      <td>463</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>parcel-bundler</td>\n",
       "      <td>Parcel</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chalarangelo/30-seconds-of-code</td>\n",
       "      <td>30-seconds-of-code</td>\n",
       "      <td>Curated collection of useful Javascript snippe...</td>\n",
       "      <td>13466</td>\n",
       "      <td>1185</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>Chalarangelo</td>\n",
       "      <td>Angelos Chalaris</td>\n",
       "      <td>User</td>\n",
       "      <td>Athens, Greece</td>\n",
       "      <td>37.983810</td>\n",
       "      <td>23.727539</td>\n",
       "      <td>Athens</td>\n",
       "      <td>Greece</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>wearehive/project-guidelines</td>\n",
       "      <td>project-guidelines</td>\n",
       "      <td>A set of best practices for JavaScript projects</td>\n",
       "      <td>11279</td>\n",
       "      <td>970</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>wearehive</td>\n",
       "      <td>Hive</td>\n",
       "      <td>Organization</td>\n",
       "      <td>London</td>\n",
       "      <td>51.507351</td>\n",
       "      <td>-0.127758</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          full_name                 repo  \\\n",
       "0  thedaviddias/Front-End-Checklist  Front-End-Checklist   \n",
       "1            GoogleChrome/puppeteer            puppeteer   \n",
       "2             parcel-bundler/parcel               parcel   \n",
       "3   Chalarangelo/30-seconds-of-code   30-seconds-of-code   \n",
       "4      wearehive/project-guidelines   project-guidelines   \n",
       "\n",
       "                                         description  stars  forks  \\\n",
       "0  🗂 The perfect Front-End Checklist for modern w...  24267   2058   \n",
       "1                           Headless Chrome Node API  21976   1259   \n",
       "2  📦🚀 Blazing fast, zero configuration web applic...  13981    463   \n",
       "3  Curated collection of useful Javascript snippe...  13466   1185   \n",
       "4    A set of best practices for JavaScript projects  11279    970   \n",
       "\n",
       "     language            user              name          type  \\\n",
       "0  JavaScript    thedaviddias        David Dias          User   \n",
       "1  JavaScript    GoogleChrome               NaN  Organization   \n",
       "2  JavaScript  parcel-bundler            Parcel  Organization   \n",
       "3  JavaScript    Chalarangelo  Angelos Chalaris          User   \n",
       "4  JavaScript       wearehive              Hive  Organization   \n",
       "\n",
       "                    location        lat       long    city         country  \n",
       "0  France, Mauritius, Canada        NaN        NaN     NaN             NaN  \n",
       "1                        NaN        NaN        NaN     NaN             NaN  \n",
       "2                        NaN        NaN        NaN     NaN             NaN  \n",
       "3             Athens, Greece  37.983810  23.727539  Athens          Greece  \n",
       "4                     London  51.507351  -0.127758  London  United Kingdom  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repos_users = repos_users.reindex_axis(['full_name',\n",
    "                                        'repo',\n",
    "                                        'description',\n",
    "                                        'stars',\n",
    "                                        'forks',\n",
    "                                        'language',\n",
    "                                        'user',\n",
    "                                        'name',\n",
    "                                        'type',\n",
    "                                        'location',\n",
    "                                        'lat',\n",
    "                                        'long',\n",
    "                                        'city',\n",
    "                                        'country'], axis=1)\n",
    "print(repos_users.shape)\n",
    "repos_users.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add Overall Ranks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rank each element based on number of stars:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(8697, 15)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>full_name</th>\n",
       "      <th>repo</th>\n",
       "      <th>description</th>\n",
       "      <th>stars</th>\n",
       "      <th>forks</th>\n",
       "      <th>language</th>\n",
       "      <th>user</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>location</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>thedaviddias/Front-End-Checklist</td>\n",
       "      <td>Front-End-Checklist</td>\n",
       "      <td>🗂 The perfect Front-End Checklist for modern w...</td>\n",
       "      <td>24267</td>\n",
       "      <td>2058</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>thedaviddias</td>\n",
       "      <td>David Dias</td>\n",
       "      <td>User</td>\n",
       "      <td>France, Mauritius, Canada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GoogleChrome/puppeteer</td>\n",
       "      <td>puppeteer</td>\n",
       "      <td>Headless Chrome Node API</td>\n",
       "      <td>21976</td>\n",
       "      <td>1259</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>GoogleChrome</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>parcel-bundler/parcel</td>\n",
       "      <td>parcel</td>\n",
       "      <td>📦🚀 Blazing fast, zero configuration web applic...</td>\n",
       "      <td>13981</td>\n",
       "      <td>463</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>parcel-bundler</td>\n",
       "      <td>Parcel</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chalarangelo/30-seconds-of-code</td>\n",
       "      <td>30-seconds-of-code</td>\n",
       "      <td>Curated collection of useful Javascript snippe...</td>\n",
       "      <td>13466</td>\n",
       "      <td>1185</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>Chalarangelo</td>\n",
       "      <td>Angelos Chalaris</td>\n",
       "      <td>User</td>\n",
       "      <td>Athens, Greece</td>\n",
       "      <td>37.983810</td>\n",
       "      <td>23.727539</td>\n",
       "      <td>Athens</td>\n",
       "      <td>Greece</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>wearehive/project-guidelines</td>\n",
       "      <td>project-guidelines</td>\n",
       "      <td>A set of best practices for JavaScript projects</td>\n",
       "      <td>11279</td>\n",
       "      <td>970</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>wearehive</td>\n",
       "      <td>Hive</td>\n",
       "      <td>Organization</td>\n",
       "      <td>London</td>\n",
       "      <td>51.507351</td>\n",
       "      <td>-0.127758</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          full_name                 repo  \\\n",
       "0  thedaviddias/Front-End-Checklist  Front-End-Checklist   \n",
       "1            GoogleChrome/puppeteer            puppeteer   \n",
       "2             parcel-bundler/parcel               parcel   \n",
       "3   Chalarangelo/30-seconds-of-code   30-seconds-of-code   \n",
       "4      wearehive/project-guidelines   project-guidelines   \n",
       "\n",
       "                                         description  stars  forks  \\\n",
       "0  🗂 The perfect Front-End Checklist for modern w...  24267   2058   \n",
       "1                           Headless Chrome Node API  21976   1259   \n",
       "2  📦🚀 Blazing fast, zero configuration web applic...  13981    463   \n",
       "3  Curated collection of useful Javascript snippe...  13466   1185   \n",
       "4    A set of best practices for JavaScript projects  11279    970   \n",
       "\n",
       "     language            user              name          type  \\\n",
       "0  JavaScript    thedaviddias        David Dias          User   \n",
       "1  JavaScript    GoogleChrome               NaN  Organization   \n",
       "2  JavaScript  parcel-bundler            Parcel  Organization   \n",
       "3  JavaScript    Chalarangelo  Angelos Chalaris          User   \n",
       "4  JavaScript       wearehive              Hive  Organization   \n",
       "\n",
       "                    location        lat       long    city         country  \\\n",
       "0  France, Mauritius, Canada        NaN        NaN     NaN             NaN   \n",
       "1                        NaN        NaN        NaN     NaN             NaN   \n",
       "2                        NaN        NaN        NaN     NaN             NaN   \n",
       "3             Athens, Greece  37.983810  23.727539  Athens          Greece   \n",
       "4                     London  51.507351  -0.127758  London  United Kingdom   \n",
       "\n",
       "   rank  \n",
       "0     3  \n",
       "1     4  \n",
       "2    11  \n",
       "3    13  \n",
       "4    16  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repos_users['rank'] = repos_users['stars'].rank(ascending=False)\n",
    "print(repos_users.shape)\n",
    "repos_users.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Verify Results: Users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Equivalent [GitHub search query](https://github.com/search?utf8=%E2%9C%93&q=created%3A2017-01-01..2017-12-31+stars%3A%3E%3D100+user%3Adonnemartin&type=Repositories&ref=searchresults): `created:2017-01-01..2017-12-31 stars:>=100 user:donnemartin`\n",
    "\n",
    "*Note: The data might be slightly off, as the search query will take into account data up to when the query was executed.  Data in this notebook was mined on January 1, 2017 to 'freeze' the results for the year 2017.  The longer you run the search from January 1, 2017, the larger the discrepancy.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>full_name</th>\n",
       "      <th>repo</th>\n",
       "      <th>description</th>\n",
       "      <th>stars</th>\n",
       "      <th>forks</th>\n",
       "      <th>language</th>\n",
       "      <th>user</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>location</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3308</th>\n",
       "      <td>donnemartin/system-design-primer</td>\n",
       "      <td>system-design-primer</td>\n",
       "      <td>Learn how to design large-scale systems. Prep ...</td>\n",
       "      <td>21780</td>\n",
       "      <td>2633</td>\n",
       "      <td>Python</td>\n",
       "      <td>donnemartin</td>\n",
       "      <td>Donne Martin</td>\n",
       "      <td>User</td>\n",
       "      <td>Washington, D.C.</td>\n",
       "      <td>38.907192</td>\n",
       "      <td>-77.036871</td>\n",
       "      <td>Washington</td>\n",
       "      <td>United States</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             full_name                  repo  \\\n",
       "3308  donnemartin/system-design-primer  system-design-primer   \n",
       "\n",
       "                                            description  stars  forks  \\\n",
       "3308  Learn how to design large-scale systems. Prep ...  21780   2633   \n",
       "\n",
       "     language         user          name  type          location        lat  \\\n",
       "3308   Python  donnemartin  Donne Martin  User  Washington, D.C.  38.907192   \n",
       "\n",
       "           long        city        country  rank  \n",
       "3308 -77.036871  Washington  United States     5  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repos_users[repos_users['user'] == 'donnemartin']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Verify Results: Python Repos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Equivalent [GitHub search query](https://github.com/search?utf8=%E2%9C%93&q=created%3A2017-01-01..2017-12-31+stars%3A%3E%3D100+language%3Apython&type=Repositories&ref=searchresults): `created:2017-01-01..2017-12-31 stars:>=100 language:python`\n",
    "\n",
    "*Note: The data might be slightly off, as the search query will take into account data up to when the query was executed.  Data in this notebook was mined on January 1, 2017 to 'freeze' the results for the year 2017.  The longer you run the search from January 1, 2017, the larger the discrepancy.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1357, 15)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>full_name</th>\n",
       "      <th>repo</th>\n",
       "      <th>description</th>\n",
       "      <th>stars</th>\n",
       "      <th>forks</th>\n",
       "      <th>language</th>\n",
       "      <th>user</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>location</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3308</th>\n",
       "      <td>donnemartin/system-design-primer</td>\n",
       "      <td>system-design-primer</td>\n",
       "      <td>Learn how to design large-scale systems. Prep ...</td>\n",
       "      <td>21780</td>\n",
       "      <td>2633</td>\n",
       "      <td>Python</td>\n",
       "      <td>donnemartin</td>\n",
       "      <td>Donne Martin</td>\n",
       "      <td>User</td>\n",
       "      <td>Washington, D.C.</td>\n",
       "      <td>38.907192</td>\n",
       "      <td>-77.036871</td>\n",
       "      <td>Washington</td>\n",
       "      <td>United States</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3309</th>\n",
       "      <td>python/cpython</td>\n",
       "      <td>cpython</td>\n",
       "      <td>The Python programming language</td>\n",
       "      <td>15060</td>\n",
       "      <td>3779</td>\n",
       "      <td>Python</td>\n",
       "      <td>python</td>\n",
       "      <td>Python</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3310</th>\n",
       "      <td>ageitgey/face_recognition</td>\n",
       "      <td>face_recognition</td>\n",
       "      <td>The world's simplest facial recognition api fo...</td>\n",
       "      <td>8487</td>\n",
       "      <td>1691</td>\n",
       "      <td>Python</td>\n",
       "      <td>ageitgey</td>\n",
       "      <td>Adam Geitgey</td>\n",
       "      <td>User</td>\n",
       "      <td>Various places</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3311</th>\n",
       "      <td>tonybeltramelli/pix2code</td>\n",
       "      <td>pix2code</td>\n",
       "      <td>pix2code: Generating Code from a Graphical Use...</td>\n",
       "      <td>8037</td>\n",
       "      <td>605</td>\n",
       "      <td>Python</td>\n",
       "      <td>tonybeltramelli</td>\n",
       "      <td>Tony Beltramelli</td>\n",
       "      <td>User</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3312</th>\n",
       "      <td>google/python-fire</td>\n",
       "      <td>python-fire</td>\n",
       "      <td>Python Fire is a library for automatically gen...</td>\n",
       "      <td>7663</td>\n",
       "      <td>386</td>\n",
       "      <td>Python</td>\n",
       "      <td>google</td>\n",
       "      <td>Google</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             full_name                  repo  \\\n",
       "3308  donnemartin/system-design-primer  system-design-primer   \n",
       "3309                    python/cpython               cpython   \n",
       "3310         ageitgey/face_recognition      face_recognition   \n",
       "3311          tonybeltramelli/pix2code              pix2code   \n",
       "3312                google/python-fire           python-fire   \n",
       "\n",
       "                                            description  stars  forks  \\\n",
       "3308  Learn how to design large-scale systems. Prep ...  21780   2633   \n",
       "3309                    The Python programming language  15060   3779   \n",
       "3310  The world's simplest facial recognition api fo...   8487   1691   \n",
       "3311  pix2code: Generating Code from a Graphical Use...   8037    605   \n",
       "3312  Python Fire is a library for automatically gen...   7663    386   \n",
       "\n",
       "     language             user              name          type  \\\n",
       "3308   Python      donnemartin      Donne Martin          User   \n",
       "3309   Python           python            Python  Organization   \n",
       "3310   Python         ageitgey      Adam Geitgey          User   \n",
       "3311   Python  tonybeltramelli  Tony Beltramelli          User   \n",
       "3312   Python           google            Google  Organization   \n",
       "\n",
       "              location        lat       long        city        country  rank  \n",
       "3308  Washington, D.C.  38.907192 -77.036871  Washington  United States     5  \n",
       "3309               NaN        NaN        NaN         NaN            NaN     9  \n",
       "3310    Various places        NaN        NaN         NaN            NaN    31  \n",
       "3311           Denmark        NaN        NaN         NaN            NaN    34  \n",
       "3312               NaN        NaN        NaN         NaN            NaN    36  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(repos_users[repos_users['language'] == 'Python'].shape)\n",
    "repos_users[repos_users['language'] == 'Python'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Verify Results: Overall Repos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Equivalent [GitHub search query](https://github.com/search?utf8=%E2%9C%93&q=created%3A2017-01-01..2017-12-31+stars%3A%3E%3D100&type=Repositories&ref=searchresults): `created:2017-01-01..2017-12-31 stars:>=100`\n",
    "\n",
    "*Note: The data might be slightly off, as the search query will take into account data up to when the query was executed.  Data in this notebook was mined on January 1, 2017 to 'freeze' the results for the year 2017.  The longer you run the search from January 1, 2017, the larger the discrepancy.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(8697, 15)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>full_name</th>\n",
       "      <th>repo</th>\n",
       "      <th>description</th>\n",
       "      <th>stars</th>\n",
       "      <th>forks</th>\n",
       "      <th>language</th>\n",
       "      <th>user</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>location</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>thedaviddias/Front-End-Checklist</td>\n",
       "      <td>Front-End-Checklist</td>\n",
       "      <td>🗂 The perfect Front-End Checklist for modern w...</td>\n",
       "      <td>24267</td>\n",
       "      <td>2058</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>thedaviddias</td>\n",
       "      <td>David Dias</td>\n",
       "      <td>User</td>\n",
       "      <td>France, Mauritius, Canada</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GoogleChrome/puppeteer</td>\n",
       "      <td>puppeteer</td>\n",
       "      <td>Headless Chrome Node API</td>\n",
       "      <td>21976</td>\n",
       "      <td>1259</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>GoogleChrome</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>parcel-bundler/parcel</td>\n",
       "      <td>parcel</td>\n",
       "      <td>📦🚀 Blazing fast, zero configuration web applic...</td>\n",
       "      <td>13981</td>\n",
       "      <td>463</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>parcel-bundler</td>\n",
       "      <td>Parcel</td>\n",
       "      <td>Organization</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chalarangelo/30-seconds-of-code</td>\n",
       "      <td>30-seconds-of-code</td>\n",
       "      <td>Curated collection of useful Javascript snippe...</td>\n",
       "      <td>13466</td>\n",
       "      <td>1185</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>Chalarangelo</td>\n",
       "      <td>Angelos Chalaris</td>\n",
       "      <td>User</td>\n",
       "      <td>Athens, Greece</td>\n",
       "      <td>37.983810</td>\n",
       "      <td>23.727539</td>\n",
       "      <td>Athens</td>\n",
       "      <td>Greece</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>wearehive/project-guidelines</td>\n",
       "      <td>project-guidelines</td>\n",
       "      <td>A set of best practices for JavaScript projects</td>\n",
       "      <td>11279</td>\n",
       "      <td>970</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>wearehive</td>\n",
       "      <td>Hive</td>\n",
       "      <td>Organization</td>\n",
       "      <td>London</td>\n",
       "      <td>51.507351</td>\n",
       "      <td>-0.127758</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          full_name                 repo  \\\n",
       "0  thedaviddias/Front-End-Checklist  Front-End-Checklist   \n",
       "1            GoogleChrome/puppeteer            puppeteer   \n",
       "2             parcel-bundler/parcel               parcel   \n",
       "3   Chalarangelo/30-seconds-of-code   30-seconds-of-code   \n",
       "4      wearehive/project-guidelines   project-guidelines   \n",
       "\n",
       "                                         description  stars  forks  \\\n",
       "0  🗂 The perfect Front-End Checklist for modern w...  24267   2058   \n",
       "1                           Headless Chrome Node API  21976   1259   \n",
       "2  📦🚀 Blazing fast, zero configuration web applic...  13981    463   \n",
       "3  Curated collection of useful Javascript snippe...  13466   1185   \n",
       "4    A set of best practices for JavaScript projects  11279    970   \n",
       "\n",
       "     language            user              name          type  \\\n",
       "0  JavaScript    thedaviddias        David Dias          User   \n",
       "1  JavaScript    GoogleChrome               NaN  Organization   \n",
       "2  JavaScript  parcel-bundler            Parcel  Organization   \n",
       "3  JavaScript    Chalarangelo  Angelos Chalaris          User   \n",
       "4  JavaScript       wearehive              Hive  Organization   \n",
       "\n",
       "                    location        lat       long    city         country  \\\n",
       "0  France, Mauritius, Canada        NaN        NaN     NaN             NaN   \n",
       "1                        NaN        NaN        NaN     NaN             NaN   \n",
       "2                        NaN        NaN        NaN     NaN             NaN   \n",
       "3             Athens, Greece  37.983810  23.727539  Athens          Greece   \n",
       "4                     London  51.507351  -0.127758  London  United Kingdom   \n",
       "\n",
       "   rank  \n",
       "0     3  \n",
       "1     4  \n",
       "2    11  \n",
       "3    13  \n",
       "4    16  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(repos_users.shape)\n",
    "repos_users.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Output Results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write out the results to csv to visualize in Tableau:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "users.to_csv('data/2017/users.csv', index=False)\n",
    "repos_users.to_csv('data/2017/repos-users-geocodes.csv', index=False)\n",
    "repos_users.to_csv('data/2017/repos-users.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "repos_rank = repos_users.reindex_axis(['full_name', 'rank'], axis=1)\n",
    "repos_rank.to_csv('data/2017/repos-ranks.csv', index=False)"
   ]
  }
 ],
 "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}