This video is going to be no frills low
production quality. I’m just going to briefly show you how to obtain process
and quickly analyze discharge data from USGS website. So first of all um see in
this window on the left here I’ve got the www.usgs.gov/water website open.
USGS is the United States Geological Survey and they collect and serve most
of the surface water data in the United States. All right now they’ve got some
interesting content up here at the top. What I want to draw your attention to
is the current stream flow conditions map here in that of the United States
and I’m just going to select Missouri from the drop-down and it should take me
to that site see you may have to go back click Missouri conditions for Missouri okay so this is
current conditions for Missouri stream flow flow of 268 sites found I’m going
to scroll down and the site that we want to focus on is the James River at
Gleaner Missouri so this one James River at galena Missouri it’s under the
Mississippi River Basin White River Basin and on the number for this station
is 0705 2500 we can just click on that hyperlink and it takes us to this page
and it will give us a several choices we can make here right now we can see what
the current gage height and feed is for this site most recent data points from
1215 on februari 22nd which is the day I’m recording this I’m you can see the
past few days in the graph here here’s the discharge will release past few days
dying see the current the current discharge is 373 cubic feet per second
just below 400 on the graph here and you can even see how that relates between
the maximum which is 1945 in the minimum in 1964 I’m going to scroll back up
remember if i go fast here is just keep this video short you can always call and
rewind a little bit to find on the steps if you miss them all right from this
drop down under faux finial provisional data subject to revision there’s a drop
down and I want to search for bomb surface water peak streamflow I went to
peak stream flow values because that’s what we use calculate recurrence
intervals so surface water peak stream flow from Missouri usgs james river at
Gleaner missouri on surface water peak stream flows
everything looks good here and what I’m going to ask it for under output formats
is a tab separated 5 you can see a scatter plot graph of annual peak
streamflow and years and you can see the data we want to get that in the tab
separated file I’m going to click on there and it opens up on this page now
this looks can this looks on hard to decipher and difficult to interpret and
all of that but this is the way a lot of scientific data is served off the
internet and what’s key here is this top part portion of the data that has the
number signs before it is the header information and you can’t ignore this if
you want to decipher what this stuff means what it tells us is what these
codes mean so agency underscore CD that stands for agency code site number USGS
station number on peak underscore VA or peak value that’s Daniels peak stream
flow value in CFS so this is telling us what those codes mean what units are
being reported in and so forth it says here the sites in this file includes the
James River at galena and then here are some more codes these won’t be too
important to us today now to get the actual data what I need to do is just
highlight from just using my mouse and highlighting the data selecting it all
the way to the bottom I’m going to copy that data right click and copy or you
could command C or control C on a pc then I’ve got an Excel workbook open
you’re just a blank workbook I’m going to click in the uppermost cell and I’m
going to paste those values in and you can see they come from over here over to
this side and they’ve been pasted it into columns and rows you’ll notice that
several of the columns are largely unpopulated we don’t even get by your
last being on these so I just highlighted
them I’m going to right-click and clear the contents from there just to get rid
of stuff we don’t need I actually tried to figure out I can’t figure out what
this row is when I was practicing before I made the video we don’t need it we can
just right-click on the actual row headings on right clicking on the tube
this is the second row and we’re going to delete that it gets rid of it all
right now what we’re left with is the agency code which is USGS that means
they collected it the site number which is the 07 Excel truncated are leaning
0800 705 2500 on that’s going to be saying because we’ve only got the one
site it is possible to download multiple sites in one data set we just chose this
one so that’s all we have right now so they’re all going to be the same so you
really don’t need those two columns and I join us anything but I’m just going to
leave them there on peak TM and if we look Pete p.m. his time with peak
ScreenFlow 24-hour format those are only recorded for the laughs most recent
years and not even all of those i’m going to highlight that column we don’t
need this data so i highlighted clicked on the D I like the column and i’m just
going to clear the contents that’s it’s everything to a point there now the next
thing we need to do I’m going to click on a column mean you could also click on
column D it doesn’t matter right click on that column header and put insert so
that I have two columns side by side and now to plot these data correctly we have
to translate what’s going on in here a little bit first of all this is PBT PBT
is the date of pic streamflow so if i make these data a little larger you can
see that for this first one it was april first 1922 Bob what we want out of that
is the year because that’s what we’re going to
plot we enter into the care which day of the year for a month of the year it
occurred in at least not now we are going to in a second have to deal with
that but for now I just want to get the year um you could just go down through
here and type 1922 1923 and so forth but that would take you a while there’s a
faster way to do it is to create a formula in this cell so equals is on the
way that you started formula itself there’s a function called year so ye AR
and rid of parentheses and then if I click on the date that I want to extract
a year from it puts the situ in there because that’s the cell so I’ll see to
close that parentheses and it extracts just the year from that page now it is
possible that when you do that pay attention here because this will really
confuse you when you first write that formula it’s possible that you do the
formula correctly equal here bomb parenthesis see to close parenthesis you
hit enter on that and you get something looks like 4 / 5 / 05 which looks
completely wrong the problem there is is its formatting things as a date but this
should be a year so the easiest thing is to come up here from the drop-down and
choose number or right click on that cell click format cells and in there
choose number since I’m in here I’m going to decrease the decimal places to
0 and now we’ve got our year out of there I’m if you’re not familiar with
Excel we can propagate this formula all the way down the list this bottom right
hand corner there’s a little square Q there is a square green a green square
that outlines this cell because it’s the one that’s active right now but there is
a little square in the corner if I double click that square if propagates
that formula down the whole list you can see this one it’s called 2015 out and so
forth all right now look closely at the data
and what you’ll notice is 1924 had to peak flows July 12 and December
nineteenth this will not be a hung the 2015 data are not complete yet you
actually see that there was a peak in june twenty of 2015 once we get to the
end of 2016 we may find that this list then the next peak in here is in
december 2015 from when we had the big flood and big rain falls back in
december what’s happening here is the usgs considers a water year to run from
october first to the following September 30th if you google that and i think i
googled usgs water year and the first choice that comes up is the US usgs
definition of what is a water year and it says that the US Geological Survey
water year is defined as the twelfth month period October first for any given
year through September 30th of the following year so the reason we have two
separate Peaks over here in July in December’s because they’re in the same
calendar year but in different water use the water year goes from the 1924 water
year would go from said oh excuse me October first 19 23 through September
30th 1924 and so this July twelfth reading was the biggest flow during that
time period on october first 1924 we actually started the 1925 water year the
water year is named for the year in which it ends not begins so it begins on
a covert 23rd october first 1924 and would have ended on sep tember 30th 1925
so this is actually this peak flow on December nineteenth nineteen twenty four
is actually the peak flow for the 1925 water you now we can write a simple
formula to straighten all this out flows um and once again we use the equal sign
to start a formula and we can use an if statement and if statements just a
logical test you can give it a test in this case what we want is to say if the
month for this date so that’s the month c2 and then if that month is less than
or equal to 9 so in other words because the water year goes through September
30th if the month that the number for the month from that date is less than
nine then something’s going to happen and what we want to happen is well if
it’s less than nine it happened before September 30 if it’s going to be in the
main team in this case 22 water year so i’m going to click be to hear now be our
choice if it’s not less than nine if the date is october november or december so
10 11 and 12 then it’s in the following water here and what we can do is take
the year in this column and add one to it so plus 1 so i’ll leave that formula
active for a second it’s 5th open parentheses month let’s see see to close
parentheses equal to or less than nine comma then what we want to do is just
put whatever is in cell d2 and if it’s not less than mine in other words if
it’s greater than mine so 10 11 or 12 we’re going to take a year and add one
to correct the waterview out it’s just asking me here I I should have put one
more parentheses here at two sets of parentheses to flow as I think make sure
what the mistake was gonna crack at it okay now i propagate that formula given
by double clicking in North bottom right-hand corner what you see now is
these two 1944 values that corrective one was for the nineteen twenty fought
for and 19.5 water year so I’m just going to make this column called water
year and if it would help you be less confused
this is calendar you all right now we’ve got our data processed it’s not unusual
when you get data off the internet lots of scientific data served as well you’ve
got to do a little bit of processing and of it to get it inform you can use all
right now we have water year in the peak discharge so I’m just going to rename
this column on PQ q is the variable for discharge and that’s what we can make a
graph of now if we wanted to we could graph those and get a scatter plot for
the maximum flow for each of the water years and erected before we do that if
you recall our recurrence interval exercise which I’ll just open for a
second that we looked at in class recurrence interval is n plus 1 where n
is the number of years of record / m which is the rank and I’m going to move
this off the screen actually I can put it over hearing the beep out of the way
for us where we can see it on this is for the root river what I’m interested
here is the formula so the first thing we need to do is rearrange our data so
I’m going to select all of our data I’m not going to worry about these columns
out here we could just clear those out one I can use that data ok so I’m going
to highlight all of our data including the header row up there at the top it’s
got the column labels and then I’m going to choose the data menu and sort there’s
a few way you can go through the data menu in the toolbar for Excel up here
bring come this way and then there’s a shortcut keys in both Mac and PC that
will get you here but you want to sort the data and I’m going to click not the
quicksort put the custom sort button which is the bigger one it gives me on
this dialog and what I can tell it i want to so
it by water year and that should be all I need and I can sort them from smallish
here too large it here that should be all I need because each water year
should be unique and I hit ok and oops what was I thinking I don’t want to sort
by water year I want to sort by PQ from largest to smallest so I want the
largest flood the top the list and smallest the bottom so my column should
you keep this card now what you see is we’ve got 2,000 he came up to the top
because it was eighty-five thousand one hundred cubic feet per second the 1993
was a second on 2030 11 was the third and if we scroll all the way down in
1934 was our minimum he discharged you ok now we can do the ranking and this is
easy just one two three you don’t have to type all those down once you get the
pattern started Excel will recognize it so type 1 2 & 3 highlight all three of
those cells and then double-click that magic square in the bottom right corner
and it’s given us our rank for all of those now you can see the RN is 94 years
we have 94 years in the record and then we can calculate our recurrence interval
on the recurrence interval once again is n plus 1 divided by M so we can write a
quick formula r equals n and n remember is 94 94 plus 1 and +1 / the the rank of
the current record that we’re interested in so this one so that makes this a 95
year recurrence interval and then if we double-click we just calculated all the
other recurrence interval goals and we can clean up the decimals
using this little button that’s got a blue arrow pointing to the right with
different numbers of zeros and might have to click both the buttons back and
forth to get where you want and now we’ve got our recurrence interval
alright that’s that should give you everything you need to finish up the
assignment and create a graph and do the forecasting once again if you missed
anything around with that you can always pause rewind or ask me at office hours
and an email all right good luck thank you bye