-
-
Save monkut/7f9c6b416eb1685d626f7445b5f70d69 to your computer and use it in GitHub Desktop.
Simple Google Spreadsheets to Pandas DataFrame in IPython Notebook
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"metadata": { | |
"name": "", | |
"signature": "sha256:5132c75b98b006eeff071000b2fbf4d6cadd1969abf44b24077e79a8c37ad200" | |
}, | |
"nbformat": 3, | |
"nbformat_minor": 0, | |
"worksheets": [ | |
{ | |
"cells": [ | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"This is a simple flow to authorize an IPython Notebook to fetch Google Spreadsheets.\n", | |
"\n", | |
"1. Go to the [Google Developer Console](https://console.developers.google.com/project)\n", | |
"1. Create a new project\n", | |
"1. Enable the Google Drive API\n", | |
"1. Create a new Client ID of type \"Installed Application (other)\"\n", | |
"1. Copy that new Client ID and Secret into the cell below\n", | |
"\n", | |
"`pip install oauth2client, gspread, and pandas`" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"collapsed": false, | |
"input": [ | |
"# these won't work for you :)\n", | |
"client_id = \"142887107151-depemnnp9g4nvsi2j8eap63q3flr0e0o.apps.googleusercontent.com\"\n", | |
"client_secret = \"cUq-lPM6Ly1bpLUEBc8krvAO\"" | |
], | |
"language": "python", | |
"metadata": {}, | |
"outputs": [] | |
}, | |
{ | |
"cell_type": "code", | |
"collapsed": false, | |
"input": [ | |
"import oauth2client.client, oauth2client.file, oauth2client.tools\n", | |
"import gspread\n", | |
"\n", | |
"flow = oauth2client.client.OAuth2WebServerFlow(client_id, client_secret, 'https://spreadsheets.google.com/feeds')\n", | |
"storage = oauth2client.file.Storage('credentials.dat')\n", | |
"credentials = storage.get()\n", | |
"if credentials is None or credentials.invalid:\n", | |
" import argparse\n", | |
" flags = argparse.ArgumentParser(parents=[oauth2client.tools.argparser]).parse_args([])\n", | |
" credentials = oauth2client.tools.run_flow(flow, storage, flags)\n", | |
"\n", | |
"gc = gspread.authorize(credentials)\n", | |
"\n", | |
"# when this cell is run, your browser will take you to a Google authorization page.\n", | |
"# this authorization is complete, the credentials will be cached in a file named credentials.dat" | |
], | |
"language": "python", | |
"metadata": {}, | |
"outputs": [], | |
"prompt_number": 5 | |
}, | |
{ | |
"cell_type": "code", | |
"collapsed": false, | |
"input": [ | |
"%pylab inline" | |
], | |
"language": "python", | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"output_type": "stream", | |
"stream": "stdout", | |
"text": [ | |
"Populating the interactive namespace from numpy and matplotlib\n" | |
] | |
} | |
], | |
"prompt_number": 6 | |
}, | |
{ | |
"cell_type": "code", | |
"collapsed": false, | |
"input": [ | |
"import pandas as pd" | |
], | |
"language": "python", | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"output_type": "stream", | |
"stream": "stderr", | |
"text": [ | |
"/usr/local/Cellar/python/2.7.5/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/pandas/io/excel.py:626: UserWarning: Installed openpyxl is not supported at this time. Use >=1.6.1 and <2.0.0.\n", | |
" .format(openpyxl_compat.start_ver, openpyxl_compat.stop_ver))\n" | |
] | |
} | |
], | |
"prompt_number": 7 | |
}, | |
{ | |
"cell_type": "code", | |
"collapsed": false, | |
"input": [ | |
"sheet = gc.open(\"My Spreadsheet\").sheet1" | |
], | |
"language": "python", | |
"metadata": {}, | |
"outputs": [], | |
"prompt_number": 8 | |
}, | |
{ | |
"cell_type": "code", | |
"collapsed": false, | |
"input": [ | |
"df = pd.DataFrame(sheet.get_all_records())" | |
], | |
"language": "python", | |
"metadata": {}, | |
"outputs": [], | |
"prompt_number": 9 | |
}, | |
{ | |
"cell_type": "code", | |
"collapsed": false, | |
"input": [ | |
"df" | |
], | |
"language": "python", | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"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>thing1</th>\n", | |
" <th>thing2</th>\n", | |
" </tr>\n", | |
" </thead>\n", | |
" <tbody>\n", | |
" <tr>\n", | |
" <th>0</th>\n", | |
" <td> 1</td>\n", | |
" <td> 4</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>1</th>\n", | |
" <td> 2</td>\n", | |
" <td> 5</td>\n", | |
" </tr>\n", | |
" <tr>\n", | |
" <th>2</th>\n", | |
" <td> 3</td>\n", | |
" <td> 6</td>\n", | |
" </tr>\n", | |
" </tbody>\n", | |
"</table>\n", | |
"</div>" | |
], | |
"metadata": {}, | |
"output_type": "pyout", | |
"prompt_number": 10, | |
"text": [ | |
" thing1 thing2\n", | |
"0 1 4\n", | |
"1 2 5\n", | |
"2 3 6" | |
] | |
} | |
], | |
"prompt_number": 10 | |
}, | |
{ | |
"cell_type": "code", | |
"collapsed": false, | |
"input": [ | |
"plt.plot(df.thing1, df.thing2)" | |
], | |
"language": "python", | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"metadata": {}, | |
"output_type": "pyout", | |
"prompt_number": 11, | |
"text": [ | |
"[<matplotlib.lines.Line2D at 0x10ac79dd0>]" | |
] | |
}, | |
{ | |
"metadata": {}, | |
"output_type": "display_data", | |
"png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAEACAYAAABI5zaHAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAADiFJREFUeJzt3G2IHOUBwPH/JrHYJKiUypWaQCQaqNDSaJsEo2aRVkiw\nfhLqFzUKemhthVKhHwo5o1U0BEVKNbTXIrRUioKmNqUm0BWhGA15qTVJ0SSCsRoL1peYD/Xi9sNs\nkslmX2Zn533+P1hud2du8zCOz/3vudkFSZIkSZIkSZIkSZIkSZKkyjsPeBrYB+wFVvTY5zHgDWAP\nsDS7oUmS4noSuLVzfw5wbtf2NcCWzv3lwMsZjUuSFNO5wMEh+zwBfD/0eD8wkdqIJEkDzYqwz4XA\nf4DfAjuBXwFzu/a5AHg79PgwsCCJAUqSRhdlcp8DXAr8svP1U+CnPfZrdD1ujzc0SVJccyLsc7hz\ne7Xz+GnOnNzfARaGHi/oPHfS4sWL2wcOHIg5TEmqrQPARaN+U5Ryf49gyWVJ5/F3gNe79tkM3NS5\nvwL4EDhy2ugOHKDdbntL6LZu3brcx1CVm8fS41mU2969bZYvb3P11W0OHQqeAxaPOrFHndwBfgj8\nnuAyx28ADwKTnRsEV8ocBN4ENgF3xhmMJNXR8ePw8MNw1VWwdi1s2waLFo33mlGWZSCY1L/d9dym\nrsd3jTcUSaqfffvglltg3jx49dXxJ/UTopa7CqbZbOY9hMrwWCbL4xlNGrUe1n2FS5ranfUjSaq1\ncK1PTw+e1BuNBsSYqy13ScpI2rUeFnXNXZI0hrTW1vux3CUpRVnWepjlLkkpybrWwyx3SUpYXrUe\nZrlLUoLyrPUwy12SElCEWg+z3CVpTEWp9TDLXZJiKlqth1nukhRDEWs9zHKXpBEUudbDLHdJiqjo\ntR5muUvSEGWp9TDLXZIGKFOth1nuktRDGWs9zHKXpC5lrfUwy12SOspe62GWuyRRjVoPs9wl1VqV\naj3McpdUW1Wr9TDLXVLtVLXWwyx3SbVS5VoPs9wl1UIdaj3McpdUeXWp9TDLXVJl1a3Wwyx3SZVU\nx1oPs9wlVUqdaz3McpdUGXWv9TDLXVLpWetnstwllZq13pvlLqmUrPXBLHdJpWOtD2e5SyqNcK3f\nfDNs3erE3o/lLqkUrPXRWO6SCs1aj8dyl1RY1np8lrukwrHWx2e5SyoUaz0ZlrukQrDWk2W5S8qd\ntZ48y11Sbqz19FjuknJhrafLcpeUKWs9G5a7pMxY69mx3CWlzlrPXtRyfwv4GDgOfAYs69reBJ4D\nDnYePwPcP/7wJJWdtZ6PqJN7m2AC/2DAPi8C1407IEnVcPw4bNwIGzbA+vUwOQmzXCvIzChr7o0x\nt0uqCWs9f1F/jraBbcAO4LY+2y8H9gBbgEsSGZ2kUnFtvTiilvtK4F3gfGArsB94KbR9J7AQOAas\nBp4FlnS/yNTU1Mn7zWaTZrMZY8iSishaT0ar1aLVao39OnGWUtYBR4GNA/Y5BFzG6Wv07Xa7HeOf\nk1Rkrq2nq9FoQIy5Okq5zwVmA58A84BrgHu79pkA3idYnlnWGcigP75KqgBrvbii/HydIFiC2Q1s\nB54HXgAmOzeA64HXOvs8CtyQ+EglFYZr68WX5RUuLstIFRCu9elpJ/W0xV2WcWVMUiTWern42TKS\nhnJtvXwsd0l9WevlZblL6slaLzfLXdJprPVqsNwlnWStV4flLslaryDLXao5a72aLHeppqz1arPc\npRqy1qvPcpdqxFqvD8tdqglrvV4sd6nirPV6stylCrPW68tylyrIWpflLlWMtS6w3KXKsNYVZrlL\nFWCtq5vlLpWYta5+LHeppKx1DWK5SyVjrSsKy10qEWtdUVnuUglY6xqV5S4VnLWuOCx3qaCsdY3D\ncpcKyFrXuCx3qUCsdSXFcpcKwlpXkix3KWfWutJguUs5staVFstdyoG1rrRZ7lLGrHVlwXKXMmKt\nK0uWu5QBa11Zs9ylFFnryovlLqXEWleeLHcpYda6isBylxJkrasoLHcpAda6isZyl8ZkrauILHcp\nJmtdRWa5SzFY6yo6y10agbWusrDcpYisdZWJ5S4NYa2rjCx3aQBrXWVluUs9WOsqu6jl/hbwMXAc\n+AxY1mOfx4DVwDFgLbBr/OFJ2bPWVQVRy70NNIGl9J7Y1wAXARcDtwOPJzE4KUvWuqpklDX3xoBt\n1wFPdu5vB84DJoAjMcclZcpaV9WMUu7bgB3AbT22XwC8HXp8GFgw3tCk9Fnrqqqo5b4SeBc4H9gK\n7Ade6tqnu+zb3S8yNTV18n6z2aTZbEb856Xknaj1uXPhlVfgwgvzHpEErVaLVqs19usMWmrpZx1w\nFNgYeu4JoAU81Xm8H1jF6csy7Xb7jPleytzx47BxI2zYAOvXw+QkzPK6MRVUo9GAGHN1lHKfC8wG\nPgHmAdcA93btsxm4i2ByXwF8iOvtKiDX1lUXUXplgmAJZjfBH0ufB14AJjs3gC3AQeBNYBNwZ+Ij\nlcbg2rrqJs6yTFwuyygX4VqfnnZSV7nEXZZxpVGVZa2rzvxsGVWSa+uqO8tdlWKtSwHLXZVhrUun\nWO4qPWtdOpPlrlKz1qXeLHeVkrUuDWa5q3SsdWk4y12lYa1L0VnuKgVrXRqN5a5Cs9aleCx3FZa1\nLsVnuatwrHVpfJa7CsVal5JhuasQrHUpWZa7cmetS8mz3JUba11Kj+WuXFjrUrosd2XKWpeyYbkr\nM9a6lB3LXamz1qXsWe5KlbUu5cNyVyqsdSlflrsSZ61L+bPclRhrXSoOy12JsNalYrHcNRZrXSom\ny12xWetScVnuGpm1LhWf5a6RWOtSOVjuisRal8rFctdQ1rpUPpa7+rLWpfKy3NWTtS6Vm+Wu01jr\nUjVY7jrJWpeqw3KXtS5VkOVec9a6VE2We01Z61K1We41ZK1L1We514i1LtWH5V4T1rpUL5Z7xVnr\nUj1Z7hVmrUv1ZblXkLUuyXKvGGtdEljulWGtSwqLWu6zgR3AYeB7XduawHPAwc7jZ4D7kxicorHW\nJXWLWu53A3uBdp/tLwJLOzcn9oxY65L6iVLuC4A1wM+BH/fZp5HYiBSJtS5pkCjl/ghwD/B5n+1t\n4HJgD7AFuCSZoamXmRlrXdJww8r9WuB9YBfB2novO4GFwDFgNfAssKTXjlNTUyfvN5tNms1+L6le\n9u2DtWth/nxrXaqqVqtFq9Ua+3WGLac8ANwIzABnA+cQ/MH0pgHfcwi4DPig6/l2u91vyV6DzMzA\nxo2wYQPcdx9MTsIsr3OSaqHRaECMpe9RvmEV8BPOvFpmgqDu28Ay4I/Aoh7f7+QeQ7jWp6etdalu\n4k7uo/bfidl5snMDuB54DdgNPArcMOogdKaZGXjoIbjyymByd21d0iiyvMrFco/IWpd0QlblrhRZ\n65KS4mfLFES41nfscFKXNB7LPWfWuqQ0WO45stYlpcVyz4G1LiltlnvGrHVJWbDcM2KtS8qS5Z4B\na11S1iz3FFnrkvJiuafEWpeUJ8s9Yda6pCKw3BNkrUsqCss9Ada6pKKx3MdkrUsqIss9JmtdUpFZ\n7jFY65KKznIfgbUuqSws94isdUllYrkPYa1LKiPLfQBrXVJZWe49WOuSys5y72KtS6oCy73DWpdU\nJZY71rqk6ql1uVvrkqqqtuVurUuqstqVu7UuqQ5qVe7WuqS6qEW5W+uS6qby5W6tS6qjypa7tS6p\nzipZ7ta6pLqrVLlb65IUqEy5W+uSdErpy91al6QzlbrcrXVJ6q2U5W6tS9JgpSt3a12ShitNuVvr\nkhRdKcrdWpek0RS63K11SYqnsOVurUtSfIUrd2tdksZXqHK31iUpGYUod2tdkpKVe7lb65KUvKjl\nPhvYBfypz/bHgDeAPcDSKC9orUtSeqJO7ncDe4F2j21rgIuAi4HbgceHvdi+fbByJbzwQlDrd9wB\nswqxQFQerVYr7yFUhscyWR7PYogypS4gmMB/DTR6bL8OeLJzfztwHjDR64Ws9eT4P1ByPJbJ8ngW\nQ5Q190eAe4Bz+my/AHg79PgwwQ+EI907rlzp2rokZWFYuV8LvE+w3t6r2k/o3tZr+cZal6SMDJqw\nAR4AbgRmgLMJ6v0Z4KbQPk8ALeCpzuP9wCrOLPc3gcXjDVeSaucAwd81U7OK3lfLrAG2dO6vAF5O\ncxCSpOFGvc79xHLLZOfrJoKJfQ1BmX8K3JLM0CRJkiSl5jcEa+2vDdhn5Dc81diw49kEPiL4g/cu\n4GfZDKuUFgJ/A14H/gn8qM9+np/RRDmeTTw/ozqb4FLy3QTvKXqwz365nZ9Xdv7BfpNReH1+Oa7P\nDzPseDaBzZmNpty+Anyzc38+8C/ga137eH5GF+V4NvH8HMXcztc5BOfeFV3bRzo/k35f6EvAfwds\nj/yGJwHDjycMv+JJgfcIqgjgKLAP+GrXPp6f0UU5nuD5OYpjna9fIPjIlw+6to90fmb9pv9+b3hS\nPG3gcoJf0bYAl+Q7nNJYRPAb0fau5z0/41lE7+Pp+TmaWQQ/MI8QLHnt7do+0vmZx6dCRnrDkyLZ\nSbD2eQxYDTwLLMl1RMU3H3ia4POSjvbY7vk5mkHH0/NzNJ8TLHWdC/yVYFmr1bVP5PMz63J/h+A/\n9gkLOs8pnk849avcX4CzgC/lN5zCO4vgTXi/I5hounl+jmbY8fT8jOcj4M/At7qeH+n8zHpy38yp\nd7euAD6kx2fQKLIJTv0kX9a5371Op0ADmCb4VffRPvt4fkYX5Xh6fkb3ZYI1dIAvAt8luMIoLNfz\n8w/Av4H/EawN3UrwhqfJ0D6/IHjD0x7g0qwGVlLDjucPCC5D2w38neA/uHq7guDX3t2cujRvNZ6f\ncUU5np6f0X2dYBlrN/APgg9rBM9PSZIkSZIkSZIkSZIkSZIkSZIkSUX1f6Z0BKuyNxcoAAAAAElF\nTkSuQmCC\n", | |
"text": [ | |
"<matplotlib.figure.Figure at 0x10ac56910>" | |
] | |
} | |
], | |
"prompt_number": 11 | |
}, | |
{ | |
"cell_type": "code", | |
"collapsed": false, | |
"input": [], | |
"language": "python", | |
"metadata": {}, | |
"outputs": [] | |
} | |
], | |
"metadata": {} | |
} | |
] | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment