Skip to content

Instantly share code, notes, and snippets.

@karlafej

karlafej/gspread.ipynb

Created May 14, 2018 13:59
Show Gist options
  • Save karlafej/2de8ee4f14b74e636418b2ff881c0843 to your computer and use it in GitHub Desktop.
Save karlafej/2de8ee4f14b74e636418b2ff881c0843 to your computer and use it in GitHub Desktop.
Download ZIP
Import google sheet into pandas dataframe
Display the source blob
Display the rendered blob
Raw
gspread.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 49,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import gspread\n",
"from oauth2client.service_account import ServiceAccountCredentials\n",
"\n",
"scope = ['https://spreadsheets.google.com/feeds',\n",
" 'https://www.googleapis.com/auth/drive']\n",
"\n",
"credentials = ServiceAccountCredentials.from_json_keyfile_name('Drive-e368d2cd19d7.json', scope)\n",
"\n",
"gc = gspread.authorize(credentials)"
]
},
{
"cell_type": "code",
"execution_count": 50,
"metadata": {},
"outputs": [],
"source": [
"worksheet = gc.open_by_url('https://docs.google.com/spreadsheets/d/xxxxxxxx_xxxxxx')\n",
"sheet = worksheet.sheet1"
]
},
{
"cell_type": "code",
"execution_count": 51,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[['parameter_stable_id', 'parameter_name', 'units', 'min', 'max', 'mean'], ['IMPC_HEM_001_001', 'White blood cell count', '10^3/ul', '0', '58.54', '7.5542']]\n"
]
}
],
"source": [
"rows = sheet.get_all_values()\n",
"print(rows[:2])"
]
},
{
"cell_type": "code",
"execution_count": 52,
"metadata": {},
"outputs": [],
"source": [
"df = pd.DataFrame.from_records(rows)"
]
},
{
"cell_type": "code",
"execution_count": 53,
"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>0</th>\n",
" <th>1</th>\n",
" <th>2</th>\n",
" <th>3</th>\n",
" <th>4</th>\n",
" <th>5</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>parameter_stable_id</td>\n",
" <td>parameter_name</td>\n",
" <td>units</td>\n",
" <td>min</td>\n",
" <td>max</td>\n",
" <td>mean</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>IMPC_HEM_001_001</td>\n",
" <td>White blood cell count</td>\n",
" <td>10^3/ul</td>\n",
" <td>0</td>\n",
" <td>58.54</td>\n",
" <td>7.5542</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>IMPC_HEM_002_001</td>\n",
" <td>Red blood cell count</td>\n",
" <td>10^6/ul</td>\n",
" <td>0</td>\n",
" <td>17.45</td>\n",
" <td>10.45</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>IMPC_HEM_003_001</td>\n",
" <td>Hemoglobin</td>\n",
" <td>g/dl</td>\n",
" <td>0</td>\n",
" <td>24.1</td>\n",
" <td>14.824</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>IMPC_HEM_004_001</td>\n",
" <td>Hematocrit</td>\n",
" <td>%</td>\n",
" <td>0</td>\n",
" <td>80.2</td>\n",
" <td>50.041</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 0 1 2 3 4 5\n",
"0 parameter_stable_id parameter_name units min max mean\n",
"1 IMPC_HEM_001_001 White blood cell count 10^3/ul 0 58.54 7.5542\n",
"2 IMPC_HEM_002_001 Red blood cell count 10^6/ul 0 17.45 10.45\n",
"3 IMPC_HEM_003_001 Hemoglobin g/dl 0 24.1 14.824\n",
"4 IMPC_HEM_004_001 Hematocrit % 0 80.2 50.041"
]
},
"execution_count": 53,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.head()"
]
},
{
"cell_type": "code",
"execution_count": 54,
"metadata": {},
"outputs": [],
"source": [
"df = df.rename(columns=df.iloc[0]).drop(df.index[0])"
]
},
{
"cell_type": "code",
"execution_count": 55,
"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>parameter_stable_id</th>\n",
" <th>parameter_name</th>\n",
" <th>units</th>\n",
" <th>min</th>\n",
" <th>max</th>\n",
" <th>mean</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>IMPC_HEM_001_001</td>\n",
" <td>White blood cell count</td>\n",
" <td>10^3/ul</td>\n",
" <td>0</td>\n",
" <td>58.54</td>\n",
" <td>7.5542</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>IMPC_HEM_002_001</td>\n",
" <td>Red blood cell count</td>\n",
" <td>10^6/ul</td>\n",
" <td>0</td>\n",
" <td>17.45</td>\n",
" <td>10.45</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>IMPC_HEM_003_001</td>\n",
" <td>Hemoglobin</td>\n",
" <td>g/dl</td>\n",
" <td>0</td>\n",
" <td>24.1</td>\n",
" <td>14.824</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>IMPC_HEM_004_001</td>\n",
" <td>Hematocrit</td>\n",
" <td>%</td>\n",
" <td>0</td>\n",
" <td>80.2</td>\n",
" <td>50.041</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>IMPC_HEM_005_001</td>\n",
" <td>Mean cell volume</td>\n",
" <td>fl</td>\n",
" <td>19.5</td>\n",
" <td>66.5</td>\n",
" <td>47.886</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" parameter_stable_id parameter_name units min max mean\n",
"1 IMPC_HEM_001_001 White blood cell count 10^3/ul 0 58.54 7.5542\n",
"2 IMPC_HEM_002_001 Red blood cell count 10^6/ul 0 17.45 10.45\n",
"3 IMPC_HEM_003_001 Hemoglobin g/dl 0 24.1 14.824\n",
"4 IMPC_HEM_004_001 Hematocrit % 0 80.2 50.041\n",
"5 IMPC_HEM_005_001 Mean cell volume fl 19.5 66.5 47.886"
]
},
"execution_count": 55,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"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.4"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment