NGL_API_Example.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9c48a864-27fc-4c8b-9903-c5935b3e40dc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "223b6c024a95438ab02a9eaabe1c08cf",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(Label(value='NGL authentication (expires after 2 hours)'), VBox(children=(Text(value='', descri…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import requests\n",
    "from requests.auth import HTTPBasicAuth\n",
    "import json\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import ipywidgets as widgets\n",
    "global token\n",
    "\n",
    "auth_label = widgets.Label(value='NGL authentication (expires after 2 hours)')\n",
    "username = widgets.Text(value='',placeholder='username',description='username',disabled=False)\n",
    "password = widgets.Password(value='',placeholder='Enter password', description='password', disabled=False)\n",
    "button = widgets.Button(description='Login',disabled=False)\n",
    "out = widgets.Output()\n",
    "auth_widget = widgets.VBox([username, password, button])\n",
    "auth_panel = widgets.VBox([auth_label, auth_widget, out])\n",
    "display(auth_panel)\n",
    "\n",
    "def ngl_auth(b):\n",
    "    global token\n",
    "    url = 'https://nextgenerationliquefaction.org/api/query.php/auth'\n",
    "    headers = {\"User-Agent\":\"XY\"}\n",
    "    r = requests.get(url, headers=headers, auth=HTTPBasicAuth(username.value,password.value))\n",
    "    if(not json.loads(r.text)['success']):\n",
    "        with out:\n",
    "            print(json.loads(r.text)['message'])\n",
    "        return\n",
    "    else:\n",
    "        with out:\n",
    "            print(json.loads(r.text)['message'])\n",
    "        token = json.loads(r.text)['token']\n",
    "        return token\n",
    "    \n",
    "def ngl_query(sql, token):\n",
    "    url = 'https://nextgenerationliquefaction.org/api/query.php/query'\n",
    "    headers = {\"User-Agent\":\"XY\", \"Authorization\": \"Bearer {}\".format(token)}\n",
    "    data = {'sql':sql}\n",
    "    r= requests.post(url, headers=headers, json=data)\n",
    "    try:\n",
    "        df = pd.DataFrame.from_dict(json.loads(r.text)['data'], orient='columns')\n",
    "        df.fillna(value=np.nan, inplace=True)\n",
    "    except:\n",
    "        df = pd.DataFrame()\n",
    "    \n",
    "    for col in df.columns:\n",
    "        try:\n",
    "            s = df[col]\n",
    "            df[col] = [int(i) if i.isdigit() else float(i) for i in s]\n",
    "        except:\n",
    "            continue\n",
    "    \n",
    "    for col in df.columns:\n",
    "        if df[col].dtype != 'int64':\n",
    "            try:\n",
    "                df[col] = df[col].astype(float)\n",
    "            except:\n",
    "                continue\n",
    "    return(df)\n",
    "\n",
    "button.on_click(ngl_auth)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "37854401-d007-45d1-83ed-37a427aaf7e6",
   "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>SITE_ID</th>\n",
       "      <th>SITE_NAME</th>\n",
       "      <th>SITE_LAT</th>\n",
       "      <th>SITE_LON</th>\n",
       "      <th>SITE_GEOL</th>\n",
       "      <th>SITE_REM</th>\n",
       "      <th>SITE_STAT</th>\n",
       "      <th>SITE_REVW</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>147</td>\n",
       "      <td>Amagasaki</td>\n",
       "      <td>34.715560</td>\n",
       "      <td>135.400750</td>\n",
       "      <td>Qal</td>\n",
       "      <td>Industrial site near Yomoga River.  Coordinate...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>148</td>\n",
       "      <td>Bonds Corner</td>\n",
       "      <td>32.693100</td>\n",
       "      <td>-115.338200</td>\n",
       "      <td>Qal, deep, Imperial Valley</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>149</td>\n",
       "      <td>Hachirogata</td>\n",
       "      <td>39.850000</td>\n",
       "      <td>140.017000</td>\n",
       "      <td>Af (Fill)</td>\n",
       "      <td>Gingery indicates the geology near the strong ...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>150</td>\n",
       "      <td>Higashi-Kobe Bridge</td>\n",
       "      <td>34.710214</td>\n",
       "      <td>135.293345</td>\n",
       "      <td>Af (Fill)</td>\n",
       "      <td>A geologic map of the area (Geological Survey ...</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>151</td>\n",
       "      <td>Hanshin Expressway</td>\n",
       "      <td>34.724834</td>\n",
       "      <td>135.301489</td>\n",
       "      <td></td>\n",
       "      <td>Hanshin Expressway (Mylonakis et al. 2006)</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>418</th>\n",
       "      <td>847</td>\n",
       "      <td>Wufeng MAA BH-9</td>\n",
       "      <td>24.053842</td>\n",
       "      <td>120.693991</td>\n",
       "      <td></td>\n",
       "      <td>Site data from PEER (2000): https://apps.peer....</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>419</th>\n",
       "      <td>848</td>\n",
       "      <td>Wufeng MAA C-10, BH-10</td>\n",
       "      <td>24.053510</td>\n",
       "      <td>120.696770</td>\n",
       "      <td></td>\n",
       "      <td>Site data from PEER (2000): https://apps.peer....</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>420</th>\n",
       "      <td>849</td>\n",
       "      <td>Wufeng MAA BH-11</td>\n",
       "      <td>24.062664</td>\n",
       "      <td>120.696644</td>\n",
       "      <td></td>\n",
       "      <td>Site data from PEER (2000): https://apps.peer....</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>421</th>\n",
       "      <td>850</td>\n",
       "      <td>Wufeng MAA BH-12</td>\n",
       "      <td>24.050435</td>\n",
       "      <td>120.695026</td>\n",
       "      <td></td>\n",
       "      <td>Site data from PEER (2000): https://apps.peer....</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>422</th>\n",
       "      <td>851</td>\n",
       "      <td>Wufeng MAA BH-13</td>\n",
       "      <td>24.064350</td>\n",
       "      <td>120.698433</td>\n",
       "      <td></td>\n",
       "      <td>Site data from PEER (2000): https://apps.peer....</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>423 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     SITE_ID               SITE_NAME   SITE_LAT    SITE_LON  \\\n",
       "0        147               Amagasaki  34.715560  135.400750   \n",
       "1        148            Bonds Corner  32.693100 -115.338200   \n",
       "2        149             Hachirogata  39.850000  140.017000   \n",
       "3        150     Higashi-Kobe Bridge  34.710214  135.293345   \n",
       "4        151      Hanshin Expressway  34.724834  135.301489   \n",
       "..       ...                     ...        ...         ...   \n",
       "418      847         Wufeng MAA BH-9  24.053842  120.693991   \n",
       "419      848  Wufeng MAA C-10, BH-10  24.053510  120.696770   \n",
       "420      849        Wufeng MAA BH-11  24.062664  120.696644   \n",
       "421      850        Wufeng MAA BH-12  24.050435  120.695026   \n",
       "422      851        Wufeng MAA BH-13  24.064350  120.698433   \n",
       "\n",
       "                      SITE_GEOL  \\\n",
       "0                           Qal   \n",
       "1    Qal, deep, Imperial Valley   \n",
       "2                     Af (Fill)   \n",
       "3                     Af (Fill)   \n",
       "4                                 \n",
       "..                          ...   \n",
       "418                               \n",
       "419                               \n",
       "420                               \n",
       "421                               \n",
       "422                               \n",
       "\n",
       "                                              SITE_REM  SITE_STAT  SITE_REVW  \n",
       "0    Industrial site near Yomoga River.  Coordinate...          1          2  \n",
       "1                                                               1          2  \n",
       "2    Gingery indicates the geology near the strong ...          1          2  \n",
       "3    A geologic map of the area (Geological Survey ...          1          2  \n",
       "4           Hanshin Expressway (Mylonakis et al. 2006)          1          2  \n",
       "..                                                 ...        ...        ...  \n",
       "418  Site data from PEER (2000): https://apps.peer....          0          0  \n",
       "419  Site data from PEER (2000): https://apps.peer....          0          0  \n",
       "420  Site data from PEER (2000): https://apps.peer....          0          0  \n",
       "421  Site data from PEER (2000): https://apps.peer....          0          0  \n",
       "422  Site data from PEER (2000): https://apps.peer....          0          0  \n",
       "\n",
       "[423 rows x 8 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "sql = 'SELECT * FROM SITE'\n",
    "df = ngl_query(sql, token)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2989d961-6956-4080-8ebb-e2b22f18f301",
   "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>SCPG_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>934</th>\n",
       "      <td>1920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>935</th>\n",
       "      <td>1921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>936</th>\n",
       "      <td>1922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>937</th>\n",
       "      <td>1923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>938</th>\n",
       "      <td>1924</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>939 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     SCPG_ID\n",
       "0        254\n",
       "1       1167\n",
       "2       1081\n",
       "3       1226\n",
       "4       1154\n",
       "..       ...\n",
       "934     1920\n",
       "935     1921\n",
       "936     1922\n",
       "937     1923\n",
       "938     1924\n",
       "\n",
       "[939 rows x 1 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "sql = 'SELECT DISTINCT SCPG_ID FROM SCPG'\n",
    "df = ngl_query(sql, token)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99ad6ee3-f81f-400a-be47-6c02b0510d12",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "url = 'https://www.uclageo.com/gm_database/api/index.php/flatfile?format=json&tables=fourier_spectra,motion,time_series,basin_model&limit=1'\n",
    "headers = {\"User-Agent\":\"XY\"}\n",
    "r = requests.get(url,headers=headers)\n",
    "print(json.loads(r.text)[0])"
   ]
  }
 ],
 "metadata": {
  "IMAGE_NAME": "taccsciapps/ds-nb-img:base-0.1.0",
  "UUID": "126d285c-0e86-11ed-bb3d-0ae65db74299",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}