Use Goldberry to harvest a single basketball game and push it to CartoDB. WARNING This will pull ~2-3,000,000 points, so probably not for your typical free CartoDB account :)

harvest_a_game.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Upload a game of basketball to CartoDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# from __future__ import print_function\n",
    "import json\n",
    "import urllib2, urllib\n",
    "\n",
    "import goldsberry\n",
    "import pandas as pd\n",
    "\n",
    "import requests\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DISPLAY_LAST_COMMA_FIRST</th>\n",
       "      <th>FROM_YEAR</th>\n",
       "      <th>PERSON_ID</th>\n",
       "      <th>PLAYERCODE</th>\n",
       "      <th>ROSTERSTATUS</th>\n",
       "      <th>TEAM_ABBREVIATION</th>\n",
       "      <th>TEAM_CITY</th>\n",
       "      <th>TEAM_CODE</th>\n",
       "      <th>TEAM_ID</th>\n",
       "      <th>TEAM_NAME</th>\n",
       "      <th>TO_YEAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3600</th>\n",
       "      <td>Thompson, Klay</td>\n",
       "      <td>2011</td>\n",
       "      <td>202691</td>\n",
       "      <td>klay_thompson</td>\n",
       "      <td>1</td>\n",
       "      <td>GSW</td>\n",
       "      <td>Golden State</td>\n",
       "      <td>warriors</td>\n",
       "      <td>1610612744</td>\n",
       "      <td>Warriors</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     DISPLAY_LAST_COMMA_FIRST FROM_YEAR  PERSON_ID     PLAYERCODE  \\\n",
       "3600           Thompson, Klay      2011     202691  klay_thompson   \n",
       "\n",
       "      ROSTERSTATUS TEAM_ABBREVIATION     TEAM_CITY TEAM_CODE     TEAM_ID  \\\n",
       "3600             1               GSW  Golden State  warriors  1610612744   \n",
       "\n",
       "     TEAM_NAME TO_YEAR  \n",
       "3600  Warriors    2015  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "players_alltime = goldsberry.PlayerList(AllTime=True)\n",
    "players_alltime = pd.DataFrame(players_alltime)\n",
    "\n",
    "focusp = players_alltime[players_alltime['DISPLAY_LAST_COMMA_FIRST'].str.contains(\"Thompson, Klay\")==True]\n",
    "focusp_id = focusp['PERSON_ID'].min()\n",
    "focusp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "37    GSW vs. SAC\n",
      "Name: MATCHUP, dtype: object\n",
      "0021400651\n"
     ]
    }
   ],
   "source": [
    "games = goldsberry.player.game_logs(focusp_id,  season='2014').logs()\n",
    "games = pd.DataFrame(games)\n",
    "\n",
    "\n",
    "# find a game of interest, here by date\n",
    "game = games[games['GAME_DATE'].str.contains(\"JAN 23\")==True]\n",
    "game_id = game['Game_ID'].min()\n",
    "\n",
    "print game['MATCHUP']\n",
    "print game_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "target_game = goldsberry.game.play_by_play(game_id).plays()\n",
    "sql_api = \"http://{your-account-name}.cartodb.com/api/v2/sql?\"\n",
    "for i in target_game:\n",
    "    event_num = i['EVENTNUM']\n",
    "    insert = \"INSERT INTO game_movement (team_id, game_id, player_id, radius, moment, game_clock, shot_clock, inv_game_clock, quarter, the_geom) VALUES \"\n",
    "    inserts = []\n",
    "    tct = tct + 1\n",
    "    if int(event_num) > 0:\n",
    "        url = \"http://stats.nba.com/stats/locations_getmoments/?eventid=%s&gameid=%s\" % (str(event_num), game_id)\n",
    "        response = requests.get(url)\n",
    "        \n",
    "        moments = response.json()[\"moments\"]\n",
    "        for moment in moments:\n",
    "            # For each player/ball in the list found within each moment\n",
    "            if moment[0] > 2:\n",
    "                for player in moment[5]:\n",
    "                    if player[1] == 202691 or player[1] == -1:\n",
    "                        player.extend((moments.index(moment), moment[2], moment[3]))\n",
    "                        player_moments.append(player)\n",
    "                        inserts.append(\"(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)\" % (\n",
    "                            player[0],\n",
    "                            game_id,\n",
    "                            focusp_id,\n",
    "                            player[4],\n",
    "                            player[5],\n",
    "                            player[6],\n",
    "                            player[7],\n",
    "                            str(720.0-float(player[6]) + (720.0*(moment[0] - 1.0))), # inv_game_clock makes it easy to work with Torque\n",
    "                            moment[0],\n",
    "                            \"ST_Transform(ST_SetSRID(ST_MakePoint(%s, %s),3857),4326)\" % (float(player[2])*10, float(player[3])*10)\n",
    "                    ))\n",
    "    if len(inserts)>0:\n",
    "        insert = insert + ','.join(inserts)\n",
    "        r = requests.put(sql_api, data={'api_key':'{your api key}', 'q': insert})\n",
    "\n",
    "print 'okay'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}