Skip to content

Instantly share code, notes, and snippets.

@jhconning

jhconning/aganalytics_python.api.ipynb

Created May 5, 2017 19:33
Show Gist options
  • Save jhconning/0fcda495c25e8777a025079d629a7c57 to your computer and use it in GitHub Desktop.
Save jhconning/0fcda495c25e8777a025079d629a7c57 to your computer and use it in GitHub Desktop.
Download ZIP
sample python code for ag-analytics.org API
Display the source blob
Display the rendered blob
Raw
aganalytics_python.api.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Better python sample code\n",
"\n",
"The [sample python code](https://www.ag-analytics.org/AgRiskManagement/APIDocs#python) for retrieving data via the excellent www.ag-analytics.org data API only works with python 2 (we are now at release 3.6). As of python 3 the urllib2 library used in that code is no longer included in the standard python library. \n",
"\n",
"Here is some sample code that works well with python 3 using the very popular requests library."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"import pandas as pd\n",
"import requests\n",
"import io"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"def agquery(sql):\n",
" '''posts sql query to ag-analytics.org and returns a pandas dataframe'''\n",
" url = 'https://ag-analytics.org/AgRiskManagement/api/dataservice/AgDBQuery'\n",
" res = requests.post(url, data=sql)\n",
" df = pd.read_csv(io.StringIO(res.text))\n",
" return df"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## A sample query"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"sql_query = \"SELECT Year, UnitDesc, DomaincatDesc, Value \\\n",
" FROM NassEconomics WHERE StateAlpha = 'US' \\\n",
" AND Year = 2012 \\\n",
" AND SourceDesc = 'CENSUS' \\\n",
" AND ClassDesc = 'ALL CLASSES' \\\n",
" AND DomainDesc = 'AREA OPERATED' \\\n",
" AND ( (ShortDesc = 'FARM OPERATIONS - ACRES OPERATED') \\\n",
" OR (ShortDesc = 'FARM OPERATIONS - NUMBER OF OPERATIONS')) \\\n",
" AND UnitDesc = 'OPERATIONS' \""
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Year</th>\n",
" <th>UnitDesc</th>\n",
" <th>DomaincatDesc</th>\n",
" <th>Value</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (1 000 TO 1 999 ACRES)</td>\n",
" <td>91273.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (2 000 OR MORE ACRES)</td>\n",
" <td>82207.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (2 000 TO 4 999 ACRES)</td>\n",
" <td>58118.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (500 TO 999 ACRES)</td>\n",
" <td>142555.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (5 000 OR MORE ACRES)</td>\n",
" <td>24089.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (1.0 TO 9.9 ACRES)</td>\n",
" <td>223634.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (140 TO 179 ACRES)</td>\n",
" <td>129026.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (50 TO 179 ACRES)</td>\n",
" <td>634047.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (260 TO 499 ACRES)</td>\n",
" <td>198085.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (70.0 TO 99.9 ACRES)</td>\n",
" <td>184942.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (1 000 OR MORE ACRES)</td>\n",
" <td>173480.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (50.0 TO 69.9 ACRES)</td>\n",
" <td>153698.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (500 OR MORE ACRES)</td>\n",
" <td>316035.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (180 TO 219 ACRES)</td>\n",
" <td>84317.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (100 TO 139 ACRES)</td>\n",
" <td>166381.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (220 TO 259 ACRES)</td>\n",
" <td>63636.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (180 TO 499 ACRES)</td>\n",
" <td>346038.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>2012</td>\n",
" <td>OPERATIONS</td>\n",
" <td>AREA OPERATED: (10.0 TO 49.9 ACRES)</td>\n",
" <td>589549.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Year UnitDesc DomaincatDesc Value\n",
"0 2012 OPERATIONS AREA OPERATED: (1 000 TO 1 999 ACRES) 91273.0\n",
"1 2012 OPERATIONS AREA OPERATED: (2 000 OR MORE ACRES) 82207.0\n",
"2 2012 OPERATIONS AREA OPERATED: (2 000 TO 4 999 ACRES) 58118.0\n",
"3 2012 OPERATIONS AREA OPERATED: (500 TO 999 ACRES) 142555.0\n",
"4 2012 OPERATIONS AREA OPERATED: (5 000 OR MORE ACRES) 24089.0\n",
"5 2012 OPERATIONS AREA OPERATED: (1.0 TO 9.9 ACRES) 223634.0\n",
"6 2012 OPERATIONS AREA OPERATED: (140 TO 179 ACRES) 129026.0\n",
"7 2012 OPERATIONS AREA OPERATED: (50 TO 179 ACRES) 634047.0\n",
"8 2012 OPERATIONS AREA OPERATED: (260 TO 499 ACRES) 198085.0\n",
"9 2012 OPERATIONS AREA OPERATED: (70.0 TO 99.9 ACRES) 184942.0\n",
"10 2012 OPERATIONS AREA OPERATED: (1 000 OR MORE ACRES) 173480.0\n",
"11 2012 OPERATIONS AREA OPERATED: (50.0 TO 69.9 ACRES) 153698.0\n",
"12 2012 OPERATIONS AREA OPERATED: (500 OR MORE ACRES) 316035.0\n",
"13 2012 OPERATIONS AREA OPERATED: (180 TO 219 ACRES) 84317.0\n",
"14 2012 OPERATIONS AREA OPERATED: (100 TO 139 ACRES) 166381.0\n",
"15 2012 OPERATIONS AREA OPERATED: (220 TO 259 ACRES) 63636.0\n",
"16 2012 OPERATIONS AREA OPERATED: (180 TO 499 ACRES) 346038.0\n",
"17 2012 OPERATIONS AREA OPERATED: (10.0 TO 49.9 ACRES) 589549.0"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = agquery(sql_query)\n",
"df"
]
}
],
"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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment