1 / 23

Translating GLSAND to SVM

Translating GLSAND to SVM. Segments. The Commercial Navigation PI GLSAND Depth-Cost to SVM Elevation-Cost The first dataset, U.S. to U.S. and U.S.-Canadian shipping Subroutes The second dataset, international and Canada-Canada. Commercial Navigation PI.

kendis
Download Presentation

Translating GLSAND to SVM

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Translating GLSAND to SVM

  2. Segments • The Commercial Navigation PI • GLSAND Depth-Cost to SVM Elevation-Cost • The first dataset, U.S. to U.S. and U.S.-Canadian shipping • Subroutes • The second dataset, international and Canada-Canada

  3. Commercial Navigation PI • The Buffalo district Corps of Engineers provided two datasets used to estimate shipping costs in the SVM • Canadian(Nov10).zip dated 1 Nov 2010, which includes all Canadian-Canadian voyages and the GL-foreign voyages. I extracted CODcost-CAN-CAN.xlsx from that zip file, put it in a “Newer Data 2010” folder • A replacement zip file for the US-US and US-CAN data provided in 2009. The replacement file uses the same price levels as the CAN-CAN data. I put that in the “Newer Data 2010” folder as well. I extracted those .csv files for each month and each country pair to a subfolder in the “Newer Data 2010” folder called Updated US and CAN.

  4. GLSAND Depth-Cost to SVM Elevation-Cost (first dataset) • The GLSAND-SVM Translator 1 reduces the number of US-US and US-CAN trips by adding all trips between two lakes that have the same dock depths in each lake and using the same code for all months. • The preprocessor combines trips between the same lakes but different docks if the dock depths are the same on each of the two lakes. For example, a trip from Chicago to Buffalo could be combined with a trip from Gary to Toledo if the Gary and Chicago dock depths were the same and the Buffalo and Toledo dock depths were the same. The SVM uses that code for all 12 months • Without eliminating the duplications from month to month, there are 3,xxx trips in the GL-SAND output but only 2zx in the SVM.

  5. GLSAND to SVM flowchart Assign subroute codes to each GLSAND trip on “All US SubRoutes” and “All US-CAN SubRoutes” Add US and US-CAN costs for each SubRoute on “New SVM Costs” Convert 24 csv files into 24 worksheets for 12 months (12 months all US and 12 for US-CAN trips) combine into one workbook. Add costs for GLSAND trips in the same subroute on “US SubRoutes$” and “US-Can SubRoutes$”

  6. Convert 24 csv files into 24 worksheets Showing October US-CAN

  7. Assign subroute codes to each GLSAND trip These final codes are drawn from three areas to the right These codes are calculated in two worksheets, “All U.S. SubRoutes” and “All US-CAN Subroutes” Starting in column AP, the east lake-west lake pair (Superior = 5, MH = 4, LSC = 3, Erie = 2, Ontario =1, St. Lawrence R. = 0) Then the east and west dock depths, in inches. Starting in column BC, the route numbers, 1-11 288,312 4 22

  8. Add costs for GLSAND trips These calculations are done in two worksheets, one for US subroutes and one for US-CAN subroutes. There are two parts in each worksheet, as well. The top 300 rows hold the costs for all 12 months in the same order as the .csv files from GLSAND: The “Omissions” line makes sure the sum of the costs of the subroutes = the sum of the costs in the GLSAND trips ($0 difference is perfect) Rows 301-597 use the SVM SubRoute codes ordered from smallest to largest, and the costs are calculated using the SUMIF formula. For example, the 408” costs for 155,336,324 are the sum of all the costs in cells O5 to 0300 where 155,336,324 is the subroute code (B5:B300) These codes are ordered and unique – next slide explains

  9. Ordered, unique codes There may be two or more trips each month with the same code, and keeping each trip would make the SVM larger and slower, so the next step is to sum all the costs for trips with the same code in the same month. The spreadsheet “Unique SubRoutes” produces one list of unique codes for the entire year – any code from the GLSAND trips is in here, but only once – and then makes a unique list for each month. The next slide shows the five step process for doing that.

  10. Ordered, unique codes First, all the US and US-CAN subroute codes (including duplicates) are listed in column B, using functions such as ='All U.S. SubRoutes '!B5 for cell B5. Column C coverts the text in Column B to values so they can be sorted as numbers in columns in Column D. For example, the first code, 422,288,312 is the 1,215th smallest code, whereas 1,023,288,252 a few rows down is 3,023rdsmallest.

  11. Ordered, unique codes The unsorted codes are then sorted from smallest to largest using a vertical lookup function (for example, cell I5=VLOOKUP(H5,$D$5:$E$3103,2,FALSE) Column F is just a list of integers Because there are duplicate codes, there are often several codes tied for each rank, so column G counts the ties at each rank. For example, there are 4 trips with the smallest code, 155,324,252 Column H calculates the untied rank using the number of ties. For example, because 4 are tied for smallest, the second smallest is ranked 5th.

  12. Ordered, unique codes To start the process of making a list of unique codes for each month, columns R to AC count the number of times each subroute appears each month The green shading appears when there were trips that month. The first subroute, for example, was traversed once in June, July, September and November, so that code will start the list of codes for those months.

  13. Subroutes All U.S. and All U.S. CAN

  14. Purpose of this worksheet • To support the calculation of trip category depth-cost tables for the SVM from final (November 2010) GLSAND outputs. There are two analogous worksheets, for US-US and US-CAN trips; they work exactly the same way. This slide show explains the US-US worksheet: • Reads trip codes from the 24 monthly GLSAND output worksheets • Makes a final list in the range A1:M300 based on several iterations of reading and analysis. • The first value for January (cell B5) illustrates the process.

  15. What does the B5 value mean? • The value of cell B5 is 422,288,312, a code for one trip category that decodes to the subroute (4), the eastern lake (2) and the western lake (also 2), eastern dock depth 288”, western dock depth 312”. • This is a trip that takes place entirely on Lake Erie (lake 2) and that makes the sub-route Erie, code 4. • In contrast, the value of cell B9 is 1023,288,252 and that illustrates a two lake trip, subroute 10 between eastern lake 2 (Erie) and western lake 3 (Lake St. Clair) with an Erie dock depth of 288” and a Lake St. Clair dock depth of 252”.

  16. How is the B5 value calculated? • The formula for cell B5 is IF(BC5="","",CONCATENATE(BC5,AP5,",",DG5)) • That formula is repeated throughout the entire B5:M300 range • First, if the formulas leading to cell BC5 create a blank value for BC5, then B5 would also be blank, but that’s not true so • B5 is a concatenation of BC5 (the subroute), AP5 (the east-west two lake code), a comma, and then DG5, which is the east and then west dock depths separated by a comma. • Next, the definitions of BC5 and AP5. DG5 is defined later.

  17. How is the BC5 value calculated? • BC5 is the subroute code, here “4”. The formula for cell BC5 is: IF(AP5="","",VLOOKUP(AP5,$BO$5:$BP$40,2))That formula is repeated throughout the entire BC5:BN300 range • First, if the formulas leading to cell AP5 create a blank value for AP5, then BC5 would also be blank, but that’s not true so BC5 is defined by a vertical lookup: • Find the value in AP5 in column BO and read the value in column BP next to that cell. • AP5 is the two digit east-west lake code, 22 • Column BO is the list of sub-routes. Cell BO19 holds code 22 (Erie-Erie). Cell BP19 holds the sub-route number for that lake combination, 4.

  18. How is the AP5 value calculated? • AP5 = 22. The formula for cell BC5 is IF(P5="","",IF(AC5,CONCATENATE(MID(P5,2,1),LEFT(P5,1)),LEFT(P5,2))). That formula is repeated throughout the entire AP5:BA300 range • First, if the formulas leading to cell P5 create a blank value for P5, then AP5 would be blank, but that’s not true so AP5 is defined by a concatenation drawn from the values in cells AC5 (TRUE or FALSE) and P5, which is the trip code less the route number 22,288,312. • AC5 is TRUE if the order of the lakes in P5 is West, East. IF AC5 is TRUE, concatenate: • The number found within P5 by moving 2 from the far left and grabbing 1 digit (22,288,312) • The number found within P5 by taking the leftmost digit (22,288,312) • If AC5 is FALSE, the AP5is the leftmost 2 digits from P5 ((22,288,312) • Note, when the east and west lakes are the same, it doesn’t make any difference, but AC5 is defined as FALSE (see next slide)

  19. How is the AC5 value calculated? • AC5 = FALSE. The formula for cell BC5 is IF(P5="","",IF(LEFT(P5,1)>MID(P5,2,1),TRUE(),FALSE)). That formula is repeated throughout the entire AC5:AN300 range • First, if the formulas leading to cell P5 create a blank value for P5, then AC5 would be blank, but that’s not true so AC5 is defined as TRUE if the first lake code in P5 is larger than the second lake code in P5. Because the numbers increase as you go west - St. Lawrence River (0), Lake Ontario (1), Erie (2), Lake St. Clair (3), Michigan-Huron (4), Superior (5) – TRUE means the lakes are arranged west-east. • If the lakes are the same or arranged west-east, AC5 is FALSE, and that is used in cell AP5 to reverse them to create an east-west order. Where the first number is larger than the second (for example, 42 in cell P10) then the lakes are arranged east-west, AC10 is TRUE and AP5 reads the lake code order as it is.

  20. How is the P5 value calculated? • P5 = 22,288,312. The formula for cell P5 is CONCATENATE('CODcost1-US-US'!$B3,'CODcost1-US-US'!$D3,",",'CODcost1-US-US'!$C3,",",'CODcost1-US-US'!$E3). That formula is repeated throughout the entire P5:AA300 range • This formula simply checks the GLSAND output and returns a trip category code for a trip or a blank if there is no trip • The formulas in column P look up US-US trips made in January; column Q is February, and so on. The month is hard coded by CODcost1-US-US; the 1 signifies January, the US-US means both the origin and destination ports are in the United States. • The row specified in the concatenation (here 3) changes with the row of the formula. It is 3 in cell P5, 4 in cell P6 and so on. • The concatenation • grabs the origin lake in cell 'CODcost1-US-US'!$B3 • the destination lake code in cell D3 • inserts a comma, • then grabs the origin dock depth in cell C3, • another comma • and the destination dock depth in cell E3. 2 2 , 288 , 312

  21. How is the DG5 value calculated? • The last few slides have documented the derivation of the first part of B5, the route code and east and west lake codes, drawn from cells BC5 and AP5, tracing AP5 from AC5 and AC5 from P5. The last part of B5 is the east and west dock depths, and those are pulled from cell DG5. • DG5 = 288,312. The formula for cell DG5 is IF(P5="","",IF(AC5,CONCATENATE(CT5,",",CG5),CONCATENATE(CG5,",",CT5))). That formula is repeated throughout the entire DG5:DR300 range • As with the other formulae, the first check is to see if P5 is blank; if so,DG5 will be made blank, too. • But it’s not so DG5 defined as either as the concatenation of CT5 (destination lake), comma, CG5 (origin lake) if AC5 is true (meaning CT5 destination lake is west of CG5 origin lake) or the reverse, CG5,comma,CT5, if the origin lake is east of the destination lake. • All that remains to explain are cells CG and CT5, the codes for the origin and destination lakes.

  22. How are the CG5 and CT5 values calculated? • Both are defined using the MID function to grab characters from the route information in cell P5 • CG5 is 288, defined as: IF(AP5="","",IF(LEFT(AP5,1)=1,888,IF(LEFT(AP5,1)=0,999,MID(P5,4,3)))). • That means that • If AP5,the east lake code-west lake code, is blank, then CG5 is blank • If not, then if the farthest left character in the two lake code is 1 assign the value 888 to CG5; if it’s 0, assign 999, but otherwise takes the 4th, 5th and 6th characters from P5. 22,288,312 288 CT5 takes the last 3 (312)

  23. System view of worksheet Origin dock depth codes in CG5:CR300 Final subroute-lakes-dock depth codes in B5:M300 Origin lake-destination dock depthcodes in P5:AA300 Destination dock depth codes in CG5:CR300 East lake – West lake codes in AP5:BA300 Final subroute codes in BC5:BN300 Origin lake-destination lake codes in P5:AA300 (TRUE if lakes are ordered west-east in P5 TRUE/FALSE codes in AC5:AN300 (TRUE if lakes are ordered west-east in P5 Trip information in US-US Cost 1 (January) through US-US Cost 12 (December)

More Related