from pylab import *
import pandas as pd
from IPython.core.display import HTML
from matplotlib.dates import MonthLocator,DateFormatter
import urllib
# full path name of spreadsheet file from Coalition for Buzzards Bay
xls_file = pd.ExcelFile('/usgs/data2/notebook/Falmouth_data.xlsx')
'all stations, raw nutrients'!$1:$1 True 'CBB WF2 nutrient data'!$4:$6 True
# parse data, replacing all the entries in the na_values list below with NaN, and also converting <0.1 to 0.1
# I can't get this to work. It runs without error, but I still have <0.1 in my data.
asn = xls_file.parse('all stations, raw nutrients',index_col='Date',
na_values=['?',None,'No Sample','NS','NES','no sample left','Sample Destroyed','machine error','#VALUE!'])
--------------------------------------------------------------------------- NameError Traceback (most recent call last) <ipython-input-1-d1093e18331e> in <module>() 1 # parse data, replacing all the entries in the na_values list below with NaN, and also converting <0.1 to 0.1 2 # I can't get this to work. It runs without error, but I still have <0.1 in my data. ----> 3 asn = xls_file.parse('all stations, raw nutrients',index_col='Date',parse_dates=True, 4 na_values=['?',None,'No Sample','NS','NES','no sample left','Sample Destroyed','machine error','#VALUE!']) NameError: name 'xls_file' is not defined
def convert_detection_limit(a,var,lim,val):
try:
a[var][where(a[var]==lim)[0]] = val
except:
print 'No detection values found for %s' % var
return a
asn=convert_detection_limit(asn,'PO4 uM','<0.1',0.05)
asn=convert_detection_limit(asn,'NH4 uM','<0.1',0.05)
asn=convert_detection_limit(asn,'NO3 uM','<0.05',0.025)
asn=convert_detection_limit(asn,'Pheo ug/L','<0.05',0.025)
sta = {
'PO4 uM': ('<0.1',0.05),
'NH4 uM': ('<0.1',0.05),
'Pheo': ('<0.05',0.025),
'NO3 uM': ('<0.05',0.025)
}
#find all the data at station
qh2=asn[asn['Station']=='QH2'] #quissett harbor, qh2
mg4=asn[asn['Station']=='MG4'] #megansett harbor, mg4
#plot up POM
qh2['POM uM'].plot(figsize(16,4),linestyle='None',marker='o',legend='best')
<matplotlib.axes.AxesSubplot at 0x42e7450>
# print just a few columns and render as HTML
new=mg4.reindex(columns=['Embayment','Station','Depth (m)','Temp C','Salt ppt',
'PO4 uM','POM uM','NH4 uM','NO3 uM','TDN uM','Pheo ug/L'])
HTML(new.to_html())
Embayment | Station | Depth (m) | Temp C | Salt ppt | PO4 uM | POM uM | NH4 uM | NO3 uM | TDN uM | Pheo ug/L | |
---|---|---|---|---|---|---|---|---|---|---|---|
Date | |||||||||||
1992-09-10 | MEGANSETT HARBOR | MG4 | 999 | NaN | 28.700000 | 0.4466501 | 3.881427 | 0.384 | 0.0025 | 15.197500 | 0 |
1992-09-10 | MEGANSETT HARBOR | MG4 | 0.15 | NaN | 30.700000 | 0.4962779 | 3.48496 | 0.4260758 | 0.0025 | 11.720500 | 1.1125 |
1992-09-16 | MEGANSETT HARBOR | MG4 | 0.15 | NaN | 29.700000 | 0.5571862 | 3.910275 | 0.04299966 | 0.0025 | 13.019500 | 0.4895 |
1992-09-16 | MEGANSETT HARBOR | MG4 | 999 | NaN | 29.700000 | 0.5571862 | 3.965878 | 0 | 0.0025 | 15.911500 | 0.801 |
1993-07-14 | MEGANSETT HARBOR | MG4 | 999 | NaN | 31.700000 | 0.4228856 | 5.97483 | 0.5316412 | 0.0025 | 10.484500 | 0.53 |
1993-07-14 | MEGANSETT HARBOR | MG4 | 0.15 | NaN | 29.700000 | 0.4228856 | 5.936537 | 0.6602641 | 0.245 | 23.796632 | 1.18 |
1996-07-15 | MEGANSETT HARBOR | MG4 | 0.15 | 23.0 | 30.000000 | 0.501 | 11.16332 | 0.69689 | 0.31 | 19.330000 | 2.631857 |
1997-07-16 | MEGANSETT HARBOR | MG4 | 0.15 | 26.5 | 30.000000 | 0.5102041 | 5.337001 | 1.11828 | 0.1313324 | 16.401258 | 0.024768 |
1997-07-30 | MEGANSETT HARBOR | MG4 | 0.15 | 25.0 | 30.000000 | 0.1861472 | 4.574094 | 1.75 | 1.265093 | 17.036466 | 1.457996 |
1997-08-14 | MEGANSETT HARBOR | MG4 | 0.15 | 23.0 | 30.000000 | 0.928934 | 3.167947 | 0.6923077 | 0.4498763 | 12.669251 | 0.06129067 |
1997-08-28 | MEGANSETT HARBOR | MG4 | 0.15 | NaN | NaN | 0.4507772 | 4.085546 | 1.109375 | 0.105978 | 17.933954 | 0.1444 |
1998-07-07 | MEGANSETT HARBOR | MG4 | 0.15 | 20.5 | 27.000000 | 0.35 | 6.084727 | 0.5833333 | 0.0426234 | 11.668124 | 0.05887829 |
1998-07-21 | MEGANSETT HARBOR | MG4 | 0.15 | 25.0 | 26.000000 | 0.2985782 | 6.850127 | 0.5945946 | 0.1730784 | 12.475031 | 0.2795869 |
1998-08-05 | MEGANSETT HARBOR | MG4 | 0.15 | 24.5 | 26.000000 | 0.3 | 6.808861 | 1.100917 | 0.1874306 | 18.753772 | 0.03937929 |
1998-08-19 | MEGANSETT HARBOR | MG4 | 0.15 | 21.5 | 27.000000 | 0.5532995 | 6.203066 | 0.05 | 0.04213677 | 10.331781 | 0.5809871 |
1999-07-13 | MEGANSETT HARBOR | MG4 | 0.15 | 22.0 | 32.000000 | 0.72 | 4.592807 | 0.55 | 0.09594197 | 12.654626 | 0.8338743 |
1999-07-27 | MEGANSETT HARBOR | MG4 | 0.15 | 25.5 | 31.000000 | 0.31 | 4.083359 | 0.68 | 0.05582557 | 16.356156 | 0.1230152 |
1999-08-10 | MEGANSETT HARBOR | MG4 | 0.15 | 23.0 | 31.000000 | 0.63 | 6.653568 | 0.91 | 0.2145534 | 15.054102 | 0.1951782 |
1999-08-24 | MEGANSETT HARBOR | MG4 | 0.15 | 23.0 | 31.000000 | 0.72 | 5.837909 | 0.22 | 0.05616738 | 11.871913 | 0.406411 |
2000-07-11 | MEGANSETT HARBOR | MG4 | 0.15 | 23.5 | 31.000000 | 0.07596878 | 7.187115 | 0.007246768 | 0.2214446 | 12.409811 | 0.7452994 |
2000-07-27 | MEGANSETT HARBOR | MG4 | 0.15 | 21.5 | 34.000000 | 0.365532 | 4.283179 | 0.196307 | 0.2700549 | 10.491503 | 0.8954337 |
2000-08-09 | MEGANSETT HARBOR | MG4 | 0.15 | 24.5 | 30.000000 | 0.1612743 | 6.703201 | 0.4347057 | 0.06833588 | 13.284802 | 0.5763583 |
2000-08-25 | MEGANSETT HARBOR | MG4 | 0.15 | 22.5 | 29.000000 | 0.4164226 | 7.508302 | 0.7398626 | 0.1001076 | 13.867211 | 0.195908 |
2001-07-18 | MEGANSETT HARBOR | MG4 | 0.15 | 24.5 | 30.500000 | 0.119567 | 7.890534 | 1.623831 | 0.79 | 22.989081 | 0.2997039 |
2001-07-31 | MEGANSETT HARBOR | MG4 | 0.15 | 22.5 | 31.000000 | 0.298449 | 4.583398 | 0.0262389 | 0.081799 | 18.201394 | 0.5261549 |
2001-08-15 | MEGANSETT HARBOR | MG4 | 0.15 | 23.5 | 30.000000 | 1.192599 | 4.465428 | 0.9628046 | 0.9493224 | 18.640250 | 1.295608 |
2001-08-29 | MEGANSETT HARBOR | MG4 | 0.15 | 24.0 | 29.000000 | 0.6143608 | 5.591756 | 0.2726173 | 0.2234685 | 18.486670 | 0.6259179 |
2002-07-08 | MEGANSETT HARBOR | MG4 | 0.15 | 22.5 | 31.000000 | 0.2381225 | 8.490578 | 0.4557175 | 0.289843 | 16.802449 | 0.025 |
2002-07-18 | MEGANSETT HARBOR | MG4 | 0.15 | 23.0 | 33.000000 | 0.201152 | 6.377542 | 0.05 | 1.667317 | 21.841865 | 0.4144479 |
2002-08-05 | MEGANSETT HARBOR | MG4 | 0.15 | 25.0 | NaN | 0.28328 | 5.959803 | 0.860565 | 0.1663126 | 31.835053 | 0.3562107 |
2002-08-19 | MEGANSETT HARBOR | MG4 | 0.15 | 25.5 | NaN | 0.814004 | 6.038511 | 0.99405 | 0.1682235 | 17.210782 | 0.6076286 |
2003-07-10 | MEGANSETT HARBOR | MG4 | 0.15 | 24.0 | 30.900000 | 0.7173725 | 6.177317 | 1.265342 | 0.09912987 | 18.711792 | 0.2541136 |
2003-07-24 | MEGANSETT HARBOR | MG4 | 0.15 | 24.5 | 29.800000 | 0.4815961 | 4.592696 | 0.3976865 | 0.1578034 | 28.916233 | 1.078614 |
2003-08-07 | MEGANSETT HARBOR | MG4 | 0.15 | 24.0 | 27.800000 | 0.3068045 | 5.96699 | 0.4419129 | 0.3165634 | 16.735277 | 0.04539 |
2003-08-25 | MEGANSETT HARBOR | MG4 | 0.15 | 23.0 | 30.400000 | 0.7519231 | 4.514707 | 2.169267 | 0.2992355 | 14.360099 | 0.2182679 |
2004-07-13 | MEGANSETT HARBOR | MG4 | 0.15 | 20.0 | 29.700000 | 0.2584788 | 5.886231 | 0.8024945 | 0.3453216 | 26.374068 | 0.2546309 |
2004-07-27 | MEGANSETT HARBOR | MG4 | 0.15 | 21.0 | 31.400000 | 0.1511776 | 5.571335 | 0.4382118 | 0.0895 | 12.980157 | 0.4351757 |
2004-08-11 | MEGANSETT HARBOR | MG4 | 0.15 | 22.0 | 31.800000 | 0.5442493 | 5.154434 | 0.7696878 | 0.1047 | 11.960801 | 0.09254071 |
2004-08-26 | MEGANSETT HARBOR | MG4 | 0.15 | 21.0 | 29.200000 | 0.52082 | 4.562058 | 0.06512908 | 0.1610879 | 27.973740 | 0.5909443 |
2005-07-18 | MEGANSETT HARBOR | MG4 | 0.15 | 24.0 | 29.100000 | 0.08243243 | 5.22231 | 0.1379316 | 0.05654032 | 28.111384 | 0.025 |
2005-08-01 | MEGANSETT HARBOR | MG4 | 0.15 | 25.0 | 30.300000 | 0.4688329 | 4.104533 | 0.3716348 | 0.321705 | 15.340574 | 0.288109 |
2005-08-16 | MEGANSETT HARBOR | MG4 | 0.15 | 24.0 | 30.800000 | 1.539433 | 5.199953 | 1.061234 | 0.3037843 | 20.248555 | 1.013969 |
2005-08-30 | MEGANSETT HARBOR | MG4 | 0.15 | 25.0 | 30.000000 | 0.7286602 | 5.701415 | 1.168473 | 0.2504703 | 13.850351 | 0.8319034 |
2006-07-06 | MEGANSETT HARBOR | MG4 | 0.15 | 22.5 | 26.500000 | 0.05 | 7.697941 | 0.07122452 | 0.025 | 16.413312 | 5.757622 |
2006-07-20 | MEGANSETT HARBOR | MG4 | 0.15 | 23.5 | 29.900000 | 0.3 | 3.713385 | 0.4867569 | 0.2243128 | 14.724430 | 2.248685 |
2006-08-07 | MEGANSETT HARBOR | MG4 | 0.15 | 25.0 | 30.400000 | 0.8809099 | 5.665318 | 0.4334729 | 0.1135744 | 18.460257 | 0.025 |
2006-08-21 | MEGANSETT HARBOR | MG4 | 0.15 | 23.0 | 30.000000 | 0.8730508 | 13.47639 | 0.5292386 | 0.1217625 | 26.206578 | 0.0823885 |
2007-07-10 | MEGANSETT HARBOR | MG4 | 0.15 | 23.0 | 30.200000 | 0.1526868 | 5.019933 | 1.138298 | 0.07228218 | 17.260869 | 0.719274 |
2007-07-26 | MEGANSETT HARBOR | MG4 | 0.15 | 24.0 | 30.200000 | 0.3473132 | 5.323931 | 0.05 | 0.025 | NaN | 1.238741 |
2007-08-09 | MEGANSETT HARBOR | MG4 | 0.15 | 24.5 | 30.700000 | 0.7783912 | 6.119466 | 1.612742 | 0.025 | 20.881766 | 1.798627 |
2007-08-23 | MEGANSETT HARBOR | MG4 | 0.15 | 22.0 | 30.700000 | 0.77545 | 8.082078 | 0.153553 | 0.025 | 20.042151 | 1.225547 |
2008-07-14 | MEGANSETT HARBOR | MG4 | 0.15 | 26.0 | 30.100000 | 0.8068703 | 6.176514 | 0.05 | 0.444289 | 23.353431 | 1.197671 |
2008-07-29 | MEGANSETT HARBOR | MG4 | 0.15 | 25.0 | 30.200000 | 0.45 | 14.28868 | 0.2943072 | 0.0553137 | 17.225768 | 0.4680716 |
2008-08-26 | MEGANSETT HARBOR | MG4 | 0.15 | 22.0 | 27.100000 | 0.8645118 | 5.753442 | 0.4674514 | 0.1525745 | 14.599207 | 0.3637439 |
2009-07-06 | MEGANSETT HARBOR | MG4 | 0.15 | 22.5 | 27.900000 | 0.4235977 | 5.035047 | 0.5839416 | 0.2655239 | 12.124133 | 1.336455 |
2009-07-20 | MEGANSETT HARBOR | MG4 | 0.15 | 24.0 | 27.300000 | 0.6357285 | 7.600755 | 0.35 | 0.1503504 | 42.744487 | 2.020381 |
2009-08-03 | MEGANSETT HARBOR | MG4 | 0.15 | 25.0 | 27.900000 | 0.3005837 | 3.777544 | 0.3793103 | 0.1192495 | 12.011649 | 1.48326 |
2009-08-17 | MEGANSETT HARBOR | MG4 | 0.15 | 22.5 | 28.500000 | 0.2267206 | 6.023756 | 0.09638554 | 0.3312349 | 11.827407 | 1.166197 |
2010-07-08 | MEGANSETT HARBOR | MG4 | 0.15 | 26.0 | 28.877828 | 0.8185367 | 8.513558 | 0.5 | 0.5149547 | 14.268119 | 0.8101456 |
2010-07-21 | MEGANSETT HARBOR | MG4 | 0.15 | 25.0 | 26.353840 | 0.2493135 | 8.516445 | 0.3081395 | 0.4019851 | 17.070000 | 2.609322 |
2010-08-05 | MEGANSETT HARBOR | MG4 | 0.15 | 24.0 | 28.515598 | 0.6308897 | 5.045644 | 0.5892857 | 0.4403215 | 15.381844 | 1.953408 |
2010-08-19 | MEGANSETT HARBOR | MG4 | 0.15 | 23.5 | 29.458492 | 0.7214734 | 24.04881 | 0.3181818 | 0.1 | 15.932267 | 0.6822882 |
2011-08-09 | MEGANSETT HARBOR | MG4 | 0.15 | 24.0 | 29.000000 | 0.7998314 | 6.954062 | 0.1602564 | 0.0347036 | 12.968333 | 0.9311133 |
2011-08-25 | MEGANSETT HARBOR | MG4 | 0.15 | 25.0 | 32.000000 | 0.5398455 | 4.300236 | 0.8580247 | 0.228481 | 11.380500 | 1.225737 |
#try to make stacked plots, one for each year
# I'm sure this isn't the best way
figure(figsize(16,8))
fix, axes = subplots(5,1,sharex=False, sharey=True)
starty=1995
mthFmt = DateFormatter('%b')
months = MonthLocator()
for j in range(5):
start='%4.4d' % (starty + j)
stop = '%4.4d' % (starty + j + 1)
qh2['POM uM'][start:stop].plot(ax=axes[j],linestyle='None',marker='o',xlim=[start,stop],label=start)
axes[j].xaxis.set_major_locator(months)
axes[j].xaxis.set_major_formatter(mthFmt)
axes[j].legend(loc='upper left')
<matplotlib.figure.Figure at 0x3dfa790>
# all west falmouth data
wf =asn[asn['Station'].isin(['WF5','WF5pw'])]
wf['PO4 uM'].plot(linestyle='None',marker='o',legend='best')
<matplotlib.axes.AxesSubplot at 0x3916490>