Add_snowpit_stratigraphy_to_db_spatially_enable.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add Snowpit Stratigraphy to Database\n",
    "* then spatially enable inside the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "'''\n",
    "  import external libraries\n",
    "'''\n",
    "import json\n",
    "import sqlalchemy\n",
    "import glob\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import glob\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "'''\n",
    "  define custom functions\n",
    "'''\n",
    "#Pull in and clean pit data (from excel -> clean tabular format)\n",
    "def clean_snowex_pit_stratigraphy(pits):\n",
    "    ''' \n",
    "      pits: list of pathnames to original snowex pit files (.xlsx)\n",
    "    '''\n",
    "    all_clean_dat=pd.DataFrame() #create empty data frame to fill in the loop\n",
    "    for pit_pth in pits:\n",
    "        dat=pd.read_excel(pit_pth,header=8) #read original table\n",
    "        dat=dat.reset_index(drop=['index', 'Unnamed: 1']) #give index; reads in as NAN due to Excel formatting\n",
    "\n",
    "        lst=list(range(9,17)) #list of column numbers desired\n",
    "        lst.append(7)\n",
    "        clean_dat=dat[lst].dropna(how='all') #drop nan rows created by \"merged cells\" data entry in excel\n",
    "\n",
    "        #add date column\n",
    "        pit_date=os.path.basename(pit_pth).split('_')[1]\n",
    "        clean_dat['date']=pit_date\n",
    "\n",
    "        #add UTM column\n",
    "        utm_coord_df=pd.read_excel(pit_pth)\n",
    "        utm_n=utm_coord_df['UTMN:'].values[0]\n",
    "        utm_e=utm_coord_df['UTMN:'].values[2]#stored in same column\n",
    "        clean_dat['utm_n']=utm_n\n",
    "        clean_dat['utm_e']=utm_e\n",
    "\n",
    "        #add pitname column\n",
    "        pit_id=utm_coord_df['Location:'].values[4]\n",
    "        clean_dat['pit_id']=pit_id\n",
    "\n",
    "        #rename columns \n",
    "        clean_dat.columns=['snow_bottom_depth','min_grain_size','max_grain_size','mean_grain_size', 'grain_type', 'photo_exists','snow_wetness', 'stratig_comments', 'snow_top_depth', 'date', 'utm_n', 'utm_e', 'pit_id']#reorder to match order in entry sheet\n",
    "        new_cols=clean_dat.columns.tolist()\n",
    "        new_cols.remove('date') #move date to be first column\n",
    "        new_cols.remove('snow_top_depth')\n",
    "        new_cols=['date', 'snow_top_depth']+new_cols\n",
    "        clean_dat=clean_dat[new_cols]\n",
    "        all_clean_dat=all_clean_dat.append(clean_dat)\n",
    "        \n",
    "    return (all_clean_dat)\n",
    "\n",
    "    \n",
    "#Open database connection; return connection engine object\n",
    "def open_database_connection(json_cred_pth):\n",
    "    #json_cred_pth: path to json file holding database connection credentails\n",
    "    with open(json_cred_pth) as f:\n",
    "        db_conn_dict = json.load(f)\n",
    "\n",
    "    cred_string = 'postgresql://{user}:{password}@{host}:{port}/{database}'.format(**db_conn_dict)\n",
    "\n",
    "    dbeng = sqlalchemy.create_engine(cred_string) #this is the connection \"enginge\" that we use to push and pull data\n",
    "    return dbeng"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#List all Excel Files containing desired snowpit data (profiles)\n",
    "pits=glob.glob('Pit_Output/*/*/pit*.xlsx') #list files below\n",
    "pits=[os.path.abspath(x) for x in pits] #store full path (not only relative)\n",
    "#remove file throwing errors from list for now\n",
    "pits.remove(r\"C:\\Users\\emily\\Documents\\Python\\Repos\\snowex\\Pit_Output\\2017-02-15\\PIT_39S\\pit_20170215_39S.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Create database connection\n",
    "json_cred_pth=\"SQL_access.json\" #file with credentials\n",
    "dbeng=open_database_connection(json_cred_pth) #this gets database connection engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Create \"clean\", single concatenated snowpit stratigraphy table\n",
    "clean_strat=clean_snowex_pit_stratigraphy(pits)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Add data to the database (may take a minute, if many rows)\n",
    "clean_strat.to_sql('pit_stratigraphy', dbeng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Spatially-enable the table in the databse\n",
    "\n",
    "#Add empty 'geom' column, of type \"geometry\", and in UTM 12N/ WGS 84 (same as incoming data)\n",
    "dbeng.execute(\"\"\"ALTER TABLE %s ADD COLUMN geom geometry(Point, 32612);\"\"\" %('pit_stratigraphy')) \n",
    "# populate the geometry field; reproject to lat/lon\n",
    "dbeng.execute(\"\"\"UPDATE %s SET geom = ST_Transform(ST_setSRID(ST_MakePoint(utm_e,utm_n),32612),4326);\"\"\" %('pit_stratigraphy'))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}