Skip to content

Instantly share code, notes, and snippets.

@phobson

phobson/unit_conversion_advanced.ipynb

Last active December 16, 2015 19:29
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save phobson/5485277 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save phobson/5485277 to your computer and use it in GitHub Desktop.
Download ZIP
More advanced unit conversion with pandas. View it here: http://nbviewer.ipython.org/5485277
Display the source blob
Display the rendered blob
Raw
unit_conversion_advanced.ipynb
{
"metadata": {
"name": "units conversion"
},
"nbformat": 3,
"nbformat_minor": 0,
"worksheets": [
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Import and units look-up dictionary"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import pandas\n",
"import StringIO\n",
"units_map = {\n",
" 'ug/L': 1e-6,\n",
" 'mg/L': 1e-3,\n",
" 'g/L' : 1e+0,\n",
"}\n",
"\n",
"target_units = {\n",
" 'lead': 'ug/L',\n",
" 'tss': 'mg/L'\n",
"}"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 5
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Mock up some data, define the target units"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data_csv = StringIO.StringIO(\"\"\"\\\n",
"site,storm,param,station,units,conc\n",
"A,1,lead,inflow,ug/L,10\n",
"A,1,lead,outflow,mg/L,0.05\n",
"A,1,tss,inflow,g/L,0.00003\n",
"A,1,tss,outflow,ug/L,70\n",
"A,2,lead,inflow,mg/L,0.02\n",
"A,2,lead,outflow,ug/L,60\n",
"A,2,tss,inflow,ug/L,40\n",
"A,2,tss,outflow,g/L,0.00008\n",
"B,1,lead,inflow,ug/L,15\n",
"B,1,lead,outflow,mg/L,0.055\n",
"B,1,tss,inflow,g/L,0.000035\n",
"B,1,tss,outflow,ug/L,75\n",
"B,2,lead,inflow,mg/L,0.025\n",
"B,2,lead,outflow,ug/L,65\n",
"B,2,tss,inflow,ug/L,45\n",
"B,2,tss,outflow,g/L,0.000085\"\"\")\n",
"data = pandas.read_csv(data_csv)\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>site</th>\n",
" <th>storm</th>\n",
" <th>param</th>\n",
" <th>station</th>\n",
" <th>units</th>\n",
" <th>conc</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0 </th>\n",
" <td> A</td>\n",
" <td> 1</td>\n",
" <td> lead</td>\n",
" <td> inflow</td>\n",
" <td> ug/L</td>\n",
" <td> 10.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1 </th>\n",
" <td> A</td>\n",
" <td> 1</td>\n",
" <td> lead</td>\n",
" <td> outflow</td>\n",
" <td> mg/L</td>\n",
" <td> 0.050000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2 </th>\n",
" <td> A</td>\n",
" <td> 1</td>\n",
" <td> tss</td>\n",
" <td> inflow</td>\n",
" <td> g/L</td>\n",
" <td> 0.000030</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3 </th>\n",
" <td> A</td>\n",
" <td> 1</td>\n",
" <td> tss</td>\n",
" <td> outflow</td>\n",
" <td> ug/L</td>\n",
" <td> 70.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4 </th>\n",
" <td> A</td>\n",
" <td> 2</td>\n",
" <td> lead</td>\n",
" <td> inflow</td>\n",
" <td> mg/L</td>\n",
" <td> 0.020000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5 </th>\n",
" <td> A</td>\n",
" <td> 2</td>\n",
" <td> lead</td>\n",
" <td> outflow</td>\n",
" <td> ug/L</td>\n",
" <td> 60.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6 </th>\n",
" <td> A</td>\n",
" <td> 2</td>\n",
" <td> tss</td>\n",
" <td> inflow</td>\n",
" <td> ug/L</td>\n",
" <td> 40.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7 </th>\n",
" <td> A</td>\n",
" <td> 2</td>\n",
" <td> tss</td>\n",
" <td> outflow</td>\n",
" <td> g/L</td>\n",
" <td> 0.000080</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8 </th>\n",
" <td> B</td>\n",
" <td> 1</td>\n",
" <td> lead</td>\n",
" <td> inflow</td>\n",
" <td> ug/L</td>\n",
" <td> 15.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9 </th>\n",
" <td> B</td>\n",
" <td> 1</td>\n",
" <td> lead</td>\n",
" <td> outflow</td>\n",
" <td> mg/L</td>\n",
" <td> 0.055000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td> B</td>\n",
" <td> 1</td>\n",
" <td> tss</td>\n",
" <td> inflow</td>\n",
" <td> g/L</td>\n",
" <td> 0.000035</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td> B</td>\n",
" <td> 1</td>\n",
" <td> tss</td>\n",
" <td> outflow</td>\n",
" <td> ug/L</td>\n",
" <td> 75.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td> B</td>\n",
" <td> 2</td>\n",
" <td> lead</td>\n",
" <td> inflow</td>\n",
" <td> mg/L</td>\n",
" <td> 0.025000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td> B</td>\n",
" <td> 2</td>\n",
" <td> lead</td>\n",
" <td> outflow</td>\n",
" <td> ug/L</td>\n",
" <td> 65.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td> B</td>\n",
" <td> 2</td>\n",
" <td> tss</td>\n",
" <td> inflow</td>\n",
" <td> ug/L</td>\n",
" <td> 45.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td> B</td>\n",
" <td> 2</td>\n",
" <td> tss</td>\n",
" <td> outflow</td>\n",
" <td> g/L</td>\n",
" <td> 0.000085</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"output_type": "pyout",
"prompt_number": 6,
"text": [
" site storm param station units conc\n",
"0 A 1 lead inflow ug/L 10.000000\n",
"1 A 1 lead outflow mg/L 0.050000\n",
"2 A 1 tss inflow g/L 0.000030\n",
"3 A 1 tss outflow ug/L 70.000000\n",
"4 A 2 lead inflow mg/L 0.020000\n",
"5 A 2 lead outflow ug/L 60.000000\n",
"6 A 2 tss inflow ug/L 40.000000\n",
"7 A 2 tss outflow g/L 0.000080\n",
"8 B 1 lead inflow ug/L 15.000000\n",
"9 B 1 lead outflow mg/L 0.055000\n",
"10 B 1 tss inflow g/L 0.000035\n",
"11 B 1 tss outflow ug/L 75.000000\n",
"12 B 2 lead inflow mg/L 0.025000\n",
"13 B 2 lead outflow ug/L 65.000000\n",
"14 B 2 tss inflow ug/L 45.000000\n",
"15 B 2 tss outflow g/L 0.000085"
]
}
],
"prompt_number": 6
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Compute factors needed to normalize the units\n",
"`get` is a function/method of dictaries that returns the value based on the key. Here we're mapping that function to all of the records in `data['unit']`"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data['target units'] = data['param'].map(target_units.get) \n",
"data['conversion factor'] = data['units'].map(units_map.get) / data['target units'].map(units_map.get)\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>site</th>\n",
" <th>storm</th>\n",
" <th>param</th>\n",
" <th>station</th>\n",
" <th>units</th>\n",
" <th>conc</th>\n",
" <th>target units</th>\n",
" <th>conversion factor</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0 </th>\n",
" <td> A</td>\n",
" <td> 1</td>\n",
" <td> lead</td>\n",
" <td> inflow</td>\n",
" <td> ug/L</td>\n",
" <td> 10.000000</td>\n",
" <td> ug/L</td>\n",
" <td> 1.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1 </th>\n",
" <td> A</td>\n",
" <td> 1</td>\n",
" <td> lead</td>\n",
" <td> outflow</td>\n",
" <td> mg/L</td>\n",
" <td> 0.050000</td>\n",
" <td> ug/L</td>\n",
" <td> 1000.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2 </th>\n",
" <td> A</td>\n",
" <td> 1</td>\n",
" <td> tss</td>\n",
" <td> inflow</td>\n",
" <td> g/L</td>\n",
" <td> 0.000030</td>\n",
" <td> mg/L</td>\n",
" <td> 1000.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3 </th>\n",
" <td> A</td>\n",
" <td> 1</td>\n",
" <td> tss</td>\n",
" <td> outflow</td>\n",
" <td> ug/L</td>\n",
" <td> 70.000000</td>\n",
" <td> mg/L</td>\n",
" <td> 0.001</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4 </th>\n",
" <td> A</td>\n",
" <td> 2</td>\n",
" <td> lead</td>\n",
" <td> inflow</td>\n",
" <td> mg/L</td>\n",
" <td> 0.020000</td>\n",
" <td> ug/L</td>\n",
" <td> 1000.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5 </th>\n",
" <td> A</td>\n",
" <td> 2</td>\n",
" <td> lead</td>\n",
" <td> outflow</td>\n",
" <td> ug/L</td>\n",
" <td> 60.000000</td>\n",
" <td> ug/L</td>\n",
" <td> 1.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6 </th>\n",
" <td> A</td>\n",
" <td> 2</td>\n",
" <td> tss</td>\n",
" <td> inflow</td>\n",
" <td> ug/L</td>\n",
" <td> 40.000000</td>\n",
" <td> mg/L</td>\n",
" <td> 0.001</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7 </th>\n",
" <td> A</td>\n",
" <td> 2</td>\n",
" <td> tss</td>\n",
" <td> outflow</td>\n",
" <td> g/L</td>\n",
" <td> 0.000080</td>\n",
" <td> mg/L</td>\n",
" <td> 1000.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8 </th>\n",
" <td> B</td>\n",
" <td> 1</td>\n",
" <td> lead</td>\n",
" <td> inflow</td>\n",
" <td> ug/L</td>\n",
" <td> 15.000000</td>\n",
" <td> ug/L</td>\n",
" <td> 1.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9 </th>\n",
" <td> B</td>\n",
" <td> 1</td>\n",
" <td> lead</td>\n",
" <td> outflow</td>\n",
" <td> mg/L</td>\n",
" <td> 0.055000</td>\n",
" <td> ug/L</td>\n",
" <td> 1000.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td> B</td>\n",
" <td> 1</td>\n",
" <td> tss</td>\n",
" <td> inflow</td>\n",
" <td> g/L</td>\n",
" <td> 0.000035</td>\n",
" <td> mg/L</td>\n",
" <td> 1000.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td> B</td>\n",
" <td> 1</td>\n",
" <td> tss</td>\n",
" <td> outflow</td>\n",
" <td> ug/L</td>\n",
" <td> 75.000000</td>\n",
" <td> mg/L</td>\n",
" <td> 0.001</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td> B</td>\n",
" <td> 2</td>\n",
" <td> lead</td>\n",
" <td> inflow</td>\n",
" <td> mg/L</td>\n",
" <td> 0.025000</td>\n",
" <td> ug/L</td>\n",
" <td> 1000.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td> B</td>\n",
" <td> 2</td>\n",
" <td> lead</td>\n",
" <td> outflow</td>\n",
" <td> ug/L</td>\n",
" <td> 65.000000</td>\n",
" <td> ug/L</td>\n",
" <td> 1.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td> B</td>\n",
" <td> 2</td>\n",
" <td> tss</td>\n",
" <td> inflow</td>\n",
" <td> ug/L</td>\n",
" <td> 45.000000</td>\n",
" <td> mg/L</td>\n",
" <td> 0.001</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td> B</td>\n",
" <td> 2</td>\n",
" <td> tss</td>\n",
" <td> outflow</td>\n",
" <td> g/L</td>\n",
" <td> 0.000085</td>\n",
" <td> mg/L</td>\n",
" <td> 1000.000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"output_type": "pyout",
"prompt_number": 7,
"text": [
" site storm param station units conc target units conversion factor\n",
"0 A 1 lead inflow ug/L 10.000000 ug/L 1.000\n",
"1 A 1 lead outflow mg/L 0.050000 ug/L 1000.000\n",
"2 A 1 tss inflow g/L 0.000030 mg/L 1000.000\n",
"3 A 1 tss outflow ug/L 70.000000 mg/L 0.001\n",
"4 A 2 lead inflow mg/L 0.020000 ug/L 1000.000\n",
"5 A 2 lead outflow ug/L 60.000000 ug/L 1.000\n",
"6 A 2 tss inflow ug/L 40.000000 mg/L 0.001\n",
"7 A 2 tss outflow g/L 0.000080 mg/L 1000.000\n",
"8 B 1 lead inflow ug/L 15.000000 ug/L 1.000\n",
"9 B 1 lead outflow mg/L 0.055000 ug/L 1000.000\n",
"10 B 1 tss inflow g/L 0.000035 mg/L 1000.000\n",
"11 B 1 tss outflow ug/L 75.000000 mg/L 0.001\n",
"12 B 2 lead inflow mg/L 0.025000 ug/L 1000.000\n",
"13 B 2 lead outflow ug/L 65.000000 ug/L 1.000\n",
"14 B 2 tss inflow ug/L 45.000000 mg/L 0.001\n",
"15 B 2 tss outflow g/L 0.000085 mg/L 1000.000"
]
}
],
"prompt_number": 7
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Recompute concentrations, reassign units, and drop superfluous columns, and create a row-index"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# recompute concentrations\n",
"data['conc'] = data['conc'] * data['conversion factor']\n",
"\n",
"# reassign units \n",
"data['units'] = data['target units']\n",
"\n",
"# drop extra columns\n",
"data = data.drop(['target units', 'conversion factor'], axis=1)\n",
"\n",
"# create a row-index\n",
"data.set_index(['site', 'storm', 'param', 'station'], inplace=True)\n",
"data"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th>units</th>\n",
" <th>conc</th>\n",
" </tr>\n",
" <tr>\n",
" <th>site</th>\n",
" <th>storm</th>\n",
" <th>param</th>\n",
" <th>station</th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"8\" valign=\"top\">A</th>\n",
" <th rowspan=\"4\" valign=\"top\">1</th>\n",
" <th rowspan=\"2\" valign=\"top\">lead</th>\n",
" <th>inflow</th>\n",
" <td> ug/L</td>\n",
" <td> 10.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>outflow</th>\n",
" <td> ug/L</td>\n",
" <td> 50.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">tss</th>\n",
" <th>inflow</th>\n",
" <td> mg/L</td>\n",
" <td> 0.030</td>\n",
" </tr>\n",
" <tr>\n",
" <th>outflow</th>\n",
" <td> mg/L</td>\n",
" <td> 0.070</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"4\" valign=\"top\">2</th>\n",
" <th rowspan=\"2\" valign=\"top\">lead</th>\n",
" <th>inflow</th>\n",
" <td> ug/L</td>\n",
" <td> 20.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>outflow</th>\n",
" <td> ug/L</td>\n",
" <td> 60.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">tss</th>\n",
" <th>inflow</th>\n",
" <td> mg/L</td>\n",
" <td> 0.040</td>\n",
" </tr>\n",
" <tr>\n",
" <th>outflow</th>\n",
" <td> mg/L</td>\n",
" <td> 0.080</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"8\" valign=\"top\">B</th>\n",
" <th rowspan=\"4\" valign=\"top\">1</th>\n",
" <th rowspan=\"2\" valign=\"top\">lead</th>\n",
" <th>inflow</th>\n",
" <td> ug/L</td>\n",
" <td> 15.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>outflow</th>\n",
" <td> ug/L</td>\n",
" <td> 55.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">tss</th>\n",
" <th>inflow</th>\n",
" <td> mg/L</td>\n",
" <td> 0.035</td>\n",
" </tr>\n",
" <tr>\n",
" <th>outflow</th>\n",
" <td> mg/L</td>\n",
" <td> 0.075</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"4\" valign=\"top\">2</th>\n",
" <th rowspan=\"2\" valign=\"top\">lead</th>\n",
" <th>inflow</th>\n",
" <td> ug/L</td>\n",
" <td> 25.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>outflow</th>\n",
" <td> ug/L</td>\n",
" <td> 65.000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">tss</th>\n",
" <th>inflow</th>\n",
" <td> mg/L</td>\n",
" <td> 0.045</td>\n",
" </tr>\n",
" <tr>\n",
" <th>outflow</th>\n",
" <td> mg/L</td>\n",
" <td> 0.085</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"output_type": "pyout",
"prompt_number": 8,
"text": [
" units conc\n",
"site storm param station \n",
"A 1 lead inflow ug/L 10.000\n",
" outflow ug/L 50.000\n",
" tss inflow mg/L 0.030\n",
" outflow mg/L 0.070\n",
" 2 lead inflow ug/L 20.000\n",
" outflow ug/L 60.000\n",
" tss inflow mg/L 0.040\n",
" outflow mg/L 0.080\n",
"B 1 lead inflow ug/L 15.000\n",
" outflow ug/L 55.000\n",
" tss inflow mg/L 0.035\n",
" outflow mg/L 0.075\n",
" 2 lead inflow ug/L 25.000\n",
" outflow ug/L 65.000\n",
" tss inflow mg/L 0.045\n",
" outflow mg/L 0.085"
]
}
],
"prompt_number": 8
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### With row data, we can use the `xs` (cross-section) method to query out values"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"data.xs(['lead', 'inflow'], level=['param', 'station'])"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th>units</th>\n",
" <th>conc</th>\n",
" </tr>\n",
" <tr>\n",
" <th>site</th>\n",
" <th>storm</th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">A</th>\n",
" <th>1</th>\n",
" <td> ug/L</td>\n",
" <td> 10</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> ug/L</td>\n",
" <td> 20</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">B</th>\n",
" <th>1</th>\n",
" <td> ug/L</td>\n",
" <td> 15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> ug/L</td>\n",
" <td> 25</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"output_type": "pyout",
"prompt_number": 9,
"text": [
" units conc\n",
"site storm \n",
"A 1 ug/L 10\n",
" 2 ug/L 20\n",
"B 1 ug/L 15\n",
" 2 ug/L 25"
]
}
],
"prompt_number": 9
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Now unstack, rename, and organize the table"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# unstack/cross tab based on inflow/outflow\n",
"table = data.unstack(level='station')\n",
"table = table.unstack(level='param')\n",
"\n",
"# rename the column level labels\n",
"table.columns.names = ['Quantity', 'Monitoring Station', 'Parameter']\n",
"table"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th>Quantity</th>\n",
" <th colspan=\"4\" halign=\"left\">units</th>\n",
" <th colspan=\"4\" halign=\"left\">conc</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th>Monitoring Station</th>\n",
" <th colspan=\"2\" halign=\"left\">inflow</th>\n",
" <th colspan=\"2\" halign=\"left\">outflow</th>\n",
" <th colspan=\"2\" halign=\"left\">inflow</th>\n",
" <th colspan=\"2\" halign=\"left\">outflow</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th>Parameter</th>\n",
" <th>lead</th>\n",
" <th>tss</th>\n",
" <th>lead</th>\n",
" <th>tss</th>\n",
" <th>lead</th>\n",
" <th>tss</th>\n",
" <th>lead</th>\n",
" <th>tss</th>\n",
" </tr>\n",
" <tr>\n",
" <th>site</th>\n",
" <th>storm</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">A</th>\n",
" <th>1</th>\n",
" <td> ug/L</td>\n",
" <td> mg/L</td>\n",
" <td> ug/L</td>\n",
" <td> mg/L</td>\n",
" <td> 10</td>\n",
" <td> 0.030</td>\n",
" <td> 50</td>\n",
" <td> 0.070</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> ug/L</td>\n",
" <td> mg/L</td>\n",
" <td> ug/L</td>\n",
" <td> mg/L</td>\n",
" <td> 20</td>\n",
" <td> 0.040</td>\n",
" <td> 60</td>\n",
" <td> 0.080</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">B</th>\n",
" <th>1</th>\n",
" <td> ug/L</td>\n",
" <td> mg/L</td>\n",
" <td> ug/L</td>\n",
" <td> mg/L</td>\n",
" <td> 15</td>\n",
" <td> 0.035</td>\n",
" <td> 55</td>\n",
" <td> 0.075</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> ug/L</td>\n",
" <td> mg/L</td>\n",
" <td> ug/L</td>\n",
" <td> mg/L</td>\n",
" <td> 25</td>\n",
" <td> 0.045</td>\n",
" <td> 65</td>\n",
" <td> 0.085</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"output_type": "pyout",
"prompt_number": 10,
"text": [
"Quantity units conc \n",
"Monitoring Station inflow outflow inflow outflow \n",
"Parameter lead tss lead tss lead tss lead tss\n",
"site storm \n",
"A 1 ug/L mg/L ug/L mg/L 10 0.030 50 0.070\n",
" 2 ug/L mg/L ug/L mg/L 20 0.040 60 0.080\n",
"B 1 ug/L mg/L ug/L mg/L 15 0.035 55 0.075\n",
" 2 ug/L mg/L ug/L mg/L 25 0.045 65 0.085"
]
}
],
"prompt_number": 10
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Reorder the columns"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"table.columns = table.columns.swaplevel('Parameter', 'Quantity')\n",
"table = table[[\n",
" ('lead', 'inflow', 'conc'), ('lead', 'inflow', 'units'), \n",
" ('lead', 'outflow', 'conc'), ('lead', 'outflow', 'units'),\n",
" ('tss', 'inflow', 'conc'), ('tss', 'inflow', 'units'), \n",
" ('tss', 'outflow', 'conc'), ('tss', 'outflow', 'units'),\n",
"]]\n",
"table"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th>Parameter</th>\n",
" <th colspan=\"4\" halign=\"left\">lead</th>\n",
" <th colspan=\"4\" halign=\"left\">tss</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th>Monitoring Station</th>\n",
" <th colspan=\"2\" halign=\"left\">inflow</th>\n",
" <th colspan=\"2\" halign=\"left\">outflow</th>\n",
" <th colspan=\"2\" halign=\"left\">inflow</th>\n",
" <th colspan=\"2\" halign=\"left\">outflow</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th>Quantity</th>\n",
" <th>conc</th>\n",
" <th>units</th>\n",
" <th>conc</th>\n",
" <th>units</th>\n",
" <th>conc</th>\n",
" <th>units</th>\n",
" <th>conc</th>\n",
" <th>units</th>\n",
" </tr>\n",
" <tr>\n",
" <th>site</th>\n",
" <th>storm</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">A</th>\n",
" <th>1</th>\n",
" <td> 10</td>\n",
" <td> ug/L</td>\n",
" <td> 50</td>\n",
" <td> ug/L</td>\n",
" <td> 0.030</td>\n",
" <td> mg/L</td>\n",
" <td> 0.070</td>\n",
" <td> mg/L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> 20</td>\n",
" <td> ug/L</td>\n",
" <td> 60</td>\n",
" <td> ug/L</td>\n",
" <td> 0.040</td>\n",
" <td> mg/L</td>\n",
" <td> 0.080</td>\n",
" <td> mg/L</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">B</th>\n",
" <th>1</th>\n",
" <td> 15</td>\n",
" <td> ug/L</td>\n",
" <td> 55</td>\n",
" <td> ug/L</td>\n",
" <td> 0.035</td>\n",
" <td> mg/L</td>\n",
" <td> 0.075</td>\n",
" <td> mg/L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> 25</td>\n",
" <td> ug/L</td>\n",
" <td> 65</td>\n",
" <td> ug/L</td>\n",
" <td> 0.045</td>\n",
" <td> mg/L</td>\n",
" <td> 0.085</td>\n",
" <td> mg/L</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"output_type": "pyout",
"prompt_number": 11,
"text": [
"Parameter lead lead lead lead tss tss tss tss\n",
"Monitoring Station inflow inflow outflow outflow inflow inflow outflow outflow\n",
"Quantity conc units conc units conc units conc units\n",
"site storm \n",
"A 1 10 ug/L 50 ug/L 0.030 mg/L 0.070 mg/L\n",
" 2 20 ug/L 60 ug/L 0.040 mg/L 0.080 mg/L\n",
"B 1 15 ug/L 55 ug/L 0.035 mg/L 0.075 mg/L\n",
" 2 25 ug/L 65 ug/L 0.045 mg/L 0.085 mg/L"
]
}
],
"prompt_number": 11
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Now with everything setup, we can sequentially access a column by it's values"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# get all the lead data\n",
"table['lead']"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th>Monitoring Station</th>\n",
" <th colspan=\"2\" halign=\"left\">inflow</th>\n",
" <th colspan=\"2\" halign=\"left\">outflow</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th>Quantity</th>\n",
" <th>conc</th>\n",
" <th>units</th>\n",
" <th>conc</th>\n",
" <th>units</th>\n",
" </tr>\n",
" <tr>\n",
" <th>site</th>\n",
" <th>storm</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">A</th>\n",
" <th>1</th>\n",
" <td> 10</td>\n",
" <td> ug/L</td>\n",
" <td> 50</td>\n",
" <td> ug/L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> 20</td>\n",
" <td> ug/L</td>\n",
" <td> 60</td>\n",
" <td> ug/L</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">B</th>\n",
" <th>1</th>\n",
" <td> 15</td>\n",
" <td> ug/L</td>\n",
" <td> 55</td>\n",
" <td> ug/L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> 25</td>\n",
" <td> ug/L</td>\n",
" <td> 65</td>\n",
" <td> ug/L</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"output_type": "pyout",
"prompt_number": 12,
"text": [
"Monitoring Station inflow inflow outflow outflow\n",
"Quantity conc units conc units\n",
"site storm \n",
"A 1 10 ug/L 50 ug/L\n",
" 2 20 ug/L 60 ug/L\n",
"B 1 15 ug/L 55 ug/L\n",
" 2 25 ug/L 65 ug/L"
]
}
],
"prompt_number": 12
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# get all of the inflow TSS data\n",
"table['tss']['inflow']"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Quantity</th>\n",
" <th>conc</th>\n",
" <th>units</th>\n",
" </tr>\n",
" <tr>\n",
" <th>site</th>\n",
" <th>storm</th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">A</th>\n",
" <th>1</th>\n",
" <td> 0.030</td>\n",
" <td> mg/L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> 0.040</td>\n",
" <td> mg/L</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">B</th>\n",
" <th>1</th>\n",
" <td> 0.035</td>\n",
" <td> mg/L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> 0.045</td>\n",
" <td> mg/L</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"output_type": "pyout",
"prompt_number": 13,
"text": [
"Quantity conc units\n",
"site storm \n",
"A 1 0.030 mg/L\n",
" 2 0.040 mg/L\n",
"B 1 0.035 mg/L\n",
" 2 0.045 mg/L"
]
}
],
"prompt_number": 13
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### And we can use both the column-indices and the `xs` method if we want"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# get all of the inflow TSS data at Site A\n",
"table['tss']['inflow'].xs('A', level='site')"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th>Quantity</th>\n",
" <th>conc</th>\n",
" <th>units</th>\n",
" </tr>\n",
" <tr>\n",
" <th>storm</th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1</th>\n",
" <td> 0.03</td>\n",
" <td> mg/L</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td> 0.04</td>\n",
" <td> mg/L</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"output_type": "pyout",
"prompt_number": 14,
"text": [
"Quantity conc units\n",
"storm \n",
"1 0.03 mg/L\n",
"2 0.04 mg/L"
]
}
],
"prompt_number": 14
}
],
"metadata": {}
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment