Fish hatcheries grow baby fish and release them into streams. The fish then swim
Fish hatcheries grow baby fish and release them into streams. The fish then swim down the streams, sometimes as far as the oceans. The State of Oregon has historically spent tens of millions of dollars operating fish hatcheries, especially to support salmon spawning. These funds not only pay for raising and releasing baby fish; they also pay for monitoring of fish populations as the fish eventually return to spawn their own natural babies in dozens of streams, rivers and other waterways throughout the state.
All those monitoring stations produce data that scientists then need to analyze. That’s where you come in. In this assignment, you will use the third-party OpenPyXL library to calculate some statistics based on Excel spreadsheets. You will read from file (CLO2), use lists (CLO4), do computations (CLO1) and, of course, use a 3rd-party library (CLO6).
To illustrate, a typical spreadsheet might look a bit like the following:
Monitoring point:WILLAMETTE FALLS (downstream)Notes
WillametteTeam5/135/145/155/165/175/18
CHINOOK ADULTA37240023216218192Fish were sampled and sent to OSU on 5/14 and 5/17
CHINOOK JACKA572928(data missing)226
TOTAL STEELHEADB544752484158
Monitoring point:WILLAMETTE FALLS (upstream)data was not gathered on all dates
WillametteTeam5/135/145/155/165/18
CHINOOK ADULTA36538121915581
CHINOOK JACKA471227130
TOTAL STEELHEADB3944514038
You will need to grab data out of the spreadsheet and compute some statistics.s
What you must do
Use your text editor to create a .py file containing a Python program with a function called analyze(filename, query) that accepts two parameters. The first parameter, filename, will indicate the name of an XLSX file. The second parameter, query, will give the name of a fish type (such as ‘CHINOOK ADULT’). Your function should do the following:
Open the file
Iterate the rows and find those whose first column matches the query
Having found the rows that match, retrieve all numbers from all columns within those rows
Return a tuple containing the (minimum, average, maximum) of these values.
For example, calling analyze(filename, ‘CHINOOK JACK’) on the sample spreadsheet shown above would identify the following values: [57, 29, 28, 22, 6, 47, 12, 27, 13, 0]. The minimum of these is 0, the maximum is 57, and the average is 24.1. Thus, the function would return (0, 24.1, 57).
Please note that the sample spreadsheet above is just an example. The real spreadsheet passed in might have more columns, fewer columns, more rows, fewer rows, more fish names, or fewer fish names. There might be missing values as illustrated above. There might be unusual columns inserted into unpredictable places (such as the “Team” column above). Just find the rows based on the leftmost column, then scan horizontally to retrieve all of the cells in those rows that look like floating-point numbers. Finally, compute and return the requested statistics.