Jupyter - Google Sheets.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import gspread\n",
    "from oauth2client.service_account import ServiceAccountCredentials\n",
    "from pandas.io.json import json_normalize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Connect to our service account\n",
    "scope = ['https://spreadsheets.google.com/feeds']\n",
    "credentials = ServiceAccountCredentials.from_json_keyfile_name('./Jupyter-and-Google-Sheet-584f47a17e35.json', scope)\n",
    "gc = gspread.authorize(credentials)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['Order',\n",
       "  'Candidate',\n",
       "  'Position',\n",
       "  'Start Date',\n",
       "  'End Date',\n",
       "  'Years of Experience',\n",
       "  'Points',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['45',\n",
       "  'Donald Trump',\n",
       "  'President',\n",
       "  '1/20/2017',\n",
       "  '1/20/2021',\n",
       "  '4.002739726',\n",
       "  '104',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['45',\n",
       "  'Donald Trump',\n",
       "  'Secretary of State',\n",
       "  '',\n",
       "  '',\n",
       "  '0',\n",
       "  '0',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['45',\n",
       "  'Donald Trump',\n",
       "  'Secretary of Defense',\n",
       "  '',\n",
       "  '',\n",
       "  '0',\n",
       "  '0',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['45',\n",
       "  'Donald Trump',\n",
       "  'Secretary of Treasury',\n",
       "  '',\n",
       "  '',\n",
       "  '0',\n",
       "  '0',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['45',\n",
       "  'Donald Trump',\n",
       "  'Vice President',\n",
       "  '',\n",
       "  '',\n",
       "  '0',\n",
       "  '0',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['45',\n",
       "  'Donald Trump',\n",
       "  'Attorney General',\n",
       "  '',\n",
       "  '',\n",
       "  '0',\n",
       "  '0',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  ''],\n",
       " ['45',\n",
       "  'Donald Trump',\n",
       "  'Military Supreme Commander',\n",
       "  '',\n",
       "  '',\n",
       "  '0',\n",
       "  '0',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '',\n",
       "  '']]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##Get candidate data sheet from Google Drive\n",
    "spreadsheet_key = '1f0OwtmuTk1fTdhnn4tuvVcPCZjdb00D79dWw4RFhYs0'\n",
    "book = gc.open_by_key(spreadsheet_key)\n",
    "worksheet = book.worksheet(\"Candidate Data\")\n",
    "table = worksheet.get_all_values()\n",
    "table[:8]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Order</th>\n",
       "      <th>Candidate</th>\n",
       "      <th>Position</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>End Date</th>\n",
       "      <th>Years of Experience</th>\n",
       "      <th>Points</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>45</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>President</td>\n",
       "      <td>2017-01-20</td>\n",
       "      <td>2021-01-20</td>\n",
       "      <td>4.00274</td>\n",
       "      <td>104.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>45</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>Secretary of State</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>45</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>Secretary of Defense</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>Secretary of Treasury</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>45</td>\n",
       "      <td>Donald Trump</td>\n",
       "      <td>Vice President</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Order     Candidate               Position Start Date   End Date  \\\n",
       "0     45  Donald Trump              President 2017-01-20 2021-01-20   \n",
       "1     45  Donald Trump     Secretary of State        NaT        NaT   \n",
       "2     45  Donald Trump   Secretary of Defense        NaT        NaT   \n",
       "3     45  Donald Trump  Secretary of Treasury        NaT        NaT   \n",
       "4     45  Donald Trump         Vice President        NaT        NaT   \n",
       "\n",
       "   Years of Experience  Points  \n",
       "0              4.00274   104.0  \n",
       "1              0.00000     0.0  \n",
       "2              0.00000     0.0  \n",
       "3              0.00000     0.0  \n",
       "4              0.00000     0.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##Convert table data into a dataframe\n",
    "df = pd.DataFrame(table[1:], columns=table[0])\n",
    "\n",
    "##Only keep columns we need\n",
    "df = df[['Order', 'Candidate', 'Position', 'Start Date', 'End Date', 'Years of Experience', 'Points']]\n",
    "\n",
    "##Convert number strings to floats and ints\n",
    "df = df.apply(pd.to_numeric, errors='ignore')\n",
    "\n",
    "##Convert date strings to datetime format\n",
    "df['End Date'] = pd.to_datetime(df['End Date'],infer_datetime_format=True)\n",
    "df['Start Date'] = pd.to_datetime(df['Start Date'],infer_datetime_format=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Order</th>\n",
       "      <th>Years of Experience</th>\n",
       "      <th>Points</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Candidate</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Abraham Lincoln</th>\n",
       "      <td>16</td>\n",
       "      <td>6.336986</td>\n",
       "      <td>114.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andrew Jackson</th>\n",
       "      <td>7</td>\n",
       "      <td>19.884932</td>\n",
       "      <td>246.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andrew Johnson</th>\n",
       "      <td>17</td>\n",
       "      <td>25.443836</td>\n",
       "      <td>204.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Barack Obama</th>\n",
       "      <td>44</td>\n",
       "      <td>12.054795</td>\n",
       "      <td>208.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Benjamin Harrison</th>\n",
       "      <td>23</td>\n",
       "      <td>12.887671</td>\n",
       "      <td>137.70</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Order  Years of Experience  Points\n",
       "Candidate                                           \n",
       "Abraham Lincoln      16             6.336986  114.00\n",
       "Andrew Jackson        7            19.884932  246.85\n",
       "Andrew Johnson       17            25.443836  204.45\n",
       "Barack Obama         44            12.054795  208.55\n",
       "Benjamin Harrison    23            12.887671  137.70"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Do some manipulation of the data\n",
    "candidate_groups = df.groupby('Candidate')\n",
    "scores_df = candidate_groups.sum()\n",
    "scores_df['Order'] = candidate_groups.first()\n",
    "scores_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Worksheet 'Jupyter Manipulated Data' id:o74wj08>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Save the data back to a new sheet in the dataframe\n",
    "from df2gspread import df2gspread as d2g\n",
    "\n",
    "wks_name = 'Jupyter Manipulated Data'\n",
    "\n",
    "d2g.upload(scores_df, spreadsheet_key, wks_name, credentials=credentials, row_names=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:JupSheets]",
   "language": "python",
   "name": "conda-env-JupSheets-py"
  },
  "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.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}