Articles in this section

See more

Getting tailings deposition volumes from a spreadsheet

Avatar
carlo
  • Updated
Follow

This article shows a simple deposition model where the deposition volumes, pond volumes, and elevation guesses are taken directly from an Excel spreadsheet and inserted into a multi-stream deposition form.

The spreadsheet

A basic spreadsheet has the deposition volumes, beach elevation guess, pond elevation, and pond elevation guess for each timestep in the simulation. 

What we need to do, is that each time we do a deposition run, Muk3D extracts the relevant column of data from the spreadsheet.

Getting the run number the user want's to get the data for

The first step is to ask the user what deposition run they want to model by popping up a dialog box to ask for an integer (run number).  This value will be used to work out which column of data is to be imported by the script.

1
2
3
4
5
6
 
from muk3d.ui.forms import ask_integer

# ask the user what the run is
run_no = ask_integer("Run number", default_value=1)

print run_no

Start by importing the ask_integer from the muk3d.ui.forms module.  Then we popup the dialog box asking for the run number and assign the result to the variable run_no .  If the user presses cancel instead of Ok on the dialog box, the return value will be None.  We can add a check that if Cancel is pressed, the script is aborted.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
from muk3d.ui.forms import ask_integer

# ask the user what the run is
run_no = ask_integer("Run number", default_value=1)

print run_no

# check that a value was entered and Cancel was not pressed.

if run_no is None:
    # end is a muk3d command that exits a script gracefully
    end()

Connecting to an Excel spreadsheet

In the Muk3D Python API, there are some classes that can help interact with an Excel spreadsheet.  These are found in the muk3d.office module.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
from muk3d.office import ExcelApplication
from os.path import abspath

# create a link to the Excel application
excel_app = ExcelApplication()

# open the workbook.  Path names when opening Excel documents must be an absolute path.  If you 
# try and open a file using a relative path, Excel won't load it and will raise an exception.

workbook = excel_app.open_workbook(abspath('../MassBalance.xlsx'))

# get a worksheet by name.
muk_io = workbook.get_sheet('MukIO')

In line 1, the ExcelApplication class is imported from the muk3d.office module.  In line 2, the function abspath is imported from the os.path module.  This command takes a relative path and based on the current directory (the working directory), determines the full path of the file relative to the root directory for the drive the file is on.  We need this, because when we give Excel the filename of the spreadsheet we want to interact with, we need to provide the absolute path (e.g. c:/test/directory/spreadsheet.xlsx).  If we just pass the name of the spreadsheet, Excel doesn't know where in the file system that file resides and will raise an exception.

In line 5 a new instance of the ExcelApplication is created and assigned to the variable excel_app. 

In line 10, the workbook is opened in Excel.  If the workbook is already open, it will connect to that instance of the workbook, otherwise it will open the workbook.

Finally, in line 13 we access the relevant worksheet in the workbook, in this case called MukIO and the worksheet is assigned to the variable muk_io.

Getting values from the spreadsheet

Now we can start to grab values from the spreadsheet.  The variable muk_io contains an instance of the class muk3d.office.ExcelWorksheet.  If you look at the Muk3D Python API docs, this class has a method called muk_io.get_value(row, col) which lets as get a value from the worksheet using the row/column indices.

In Excel, indexing is 1 based - the first row/column is 1, so the indices 1,1 would get the top left hand cell in the worksheet.  (This is different to much of the indexing in Python where the first element in an array or list has the index of 0).

Based on this, if the user enters a run index of 1 in when the scripts run, the column we're getting from the worksheet will be column 3, which we will reference as run_no + 2.  The first row that we'll be getting (line 1 elevation) will be row 3.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
# when getting the data, Excel uses indexing for rows and columns starting at 1
# the cell in the top left corner is 1,1

# get the line 1 volume and elevation guess

line_1_z = muk_io.get_value(3, run_no + 2)
line_1_v = muk_io.get_value(4, run_no + 2)

# get the line 2 volume and elevation guess

line_2_z = muk_io.get_value(5, run_no + 2)
line_2_v = muk_io.get_value(6, run_no + 2)

# get the line 3 volume and elevation guess

line_3_z = muk_io.get_value(7, run_no + 2)
line_3_v = muk_io.get_value(8, run_no + 2)

# get the pond volume and elevation guess
pond_z = muk_io.get_value(9, run_no + 2)
pond_v = muk_io.get_value(10, run_no + 2)

Using the code above, variables storing the line volumes and elevation guesses, and the pond volume and elevation guess are created.

Running multi-stream deposition

The next step is to run the deposition model.  Start by recording running a multi-stream deposition with a fixed pond volume. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
cmd = get_command('Multi stream fixed pond volume')
result = cmd({   'base': u'../grid+dam.mgrid',
    'deposition model': 'Multi stream fixed pond volume',
    'fluidVolume': 100000.0,
    'maxChange': 2.0,
    'maxIterations': 40,
    'maxPondElevation': 114.0,
    'maxPondElevationChange': 1.0,
    'maxPondIterations': 20,
    'pointSpacing': 150.0,
    'pond': 'pond',
    'pondElevation': 101.0,
    'pondTolerance': 1.0,
    'pond_location': None,
    'schedule': [   [   u'Flow path down slope',
                        u'../discharge1.mcurve',
                        u'Sand',
                        100000.0,
                        105.0],
                    [   u'Flow path down slope',
                        u'../discharge2.mcurve',
                        u'Sand',
                        200000.0,
                        104.0]],
    'tolerance': 1.0,
    'use_direct_deposition': False,
    'verticalOffset': 0.0})

Some of the inputs here will be replaced with variables:

  • Line 4: fluidVolume -> pond_v
  • Line 12: pondElevation -> pond_z
  • Line 15: schedule -> ???

Based on the deposition volumes we get from the spreadsheet, we need to construct the list that is getting passed into the schedule parameter.

The format that is expected for each line in the deposition table is a list that contains:

  • Deposition model
  • Discharge line
  • Tailings name
  • Target volume
  • Beach elevation guess

Start by creating an empty list in line 1.  This will be populated by the different lines, if they have a target volume.  For each line, if the deposition volume is greater than zero, a new list is appended to the deposition_table list.  That list contains the deposition model (Flow path down slope for all lines), appropriate discharge points (saved in the parent directory of the current directory the deposition run is executing in), the tailings properties (Sand), and then volume and elevation guess for the appropriate line.  

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
deposition_table = []

# Now, add each line, but only if the line has volume > 0

if line_1_v > 0:
    deposition_table.append( [   u'Flow path down slope',
                        u'../discharge1.mcurve',
                        u'Sand',
                        line_1_v,
                        line_1_z])
    
if line_2_v > 0:
    deposition_table.append( [   u'Flow path down slope',
                        u'../discharge2.mcurve',
                        u'Sand',
                        line_2_v,
                        line_2_z])
    
if line_3_v > 0:                        
    deposition_table.append( [   u'Flow path down slope',
                        u'../discharge3.mcurve',
                        u'Sand',
                        line_3_v,
                        line_3_z])

Now we can substitute all the variables in the deposition command and call it, but we don't want to execute it yet. 

In line 22, a key/value pair was added:  'show_dialog': True.  If this is present in any command arguments for a command run from a macro, this forces the command's dialog box to be shown instead of just executing the command.  The reason we want to do this is so the user can choose the base grid to use for the run.  (If this script included logic for selecting the appropriate base grid,  then we wouldn't need to show the dialog).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
cmd = get_command('Multi stream fixed pond volume')
result = cmd({   'base': u'',  # leave this blank and the user can select the grid to use.
    'deposition model': 'Multi stream fixed pond volume',
    'fluidVolume': pond_v,  # set the pond volume
    'maxChange': 2.0,
    'maxIterations': 40,
    'maxPondElevation': 114.0,
    'maxPondElevationChange': 1.0,
    'maxPondIterations': 20,
    'pointSpacing': 150.0,
    'pond': 'pond',
    'pondElevation': pond_z,  # set the initial pond elevation guess
    'pondTolerance': 1.0,
    'pond_location': (   2552.0744724278015,
                         -2067.5205581943374,
                         102.6405243335685),
    'schedule': deposition_table, # set the deposition lines
    'seCurveIncrement': 0.1,
    'tolerance': 1.0,
    'use_direct_deposition': False,
    'verticalOffset': 0.0,
    'show_dialog': True})

Finally once the deposition has run, the same concepts for extracting data from Excel can be used to add result values back to Excel.  Getting output values from a deposition run has been covered in other tutorials (this article talks about processing multi-stream output data ). 

Writing to the spreadsheet can be done using muk_io.set_value(row, column, value) remembering that row and columns start indexing at 1 instead of the usual 0 for Python lists and arrays.

The full script

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
"""
This example shows how tailings deposition data can be retrieved from and Excel spreadsheet
and then used to populate a multi-stream deposition table.  

It starts by asking the user which timestep is being modelled here (so it knows where to get
the data from in the worksheet).

An Excel application is then created and the spreadsheet opened.  The relevant workbook is then 
retrieved and the data read.

"""

from muk3d.util import set_working_directory
from muk3d.office import ExcelApplication
from muk3d.ui.forms import ask_integer
from os.path import abspath
from os import mkdir

# ask the user what the run is
run_no = ask_integer("Run number", default_value=1)


# create a link to the Excel application
excel_app = ExcelApplication()

# open the workbook.  Path names when opening Excel documents should be an absolute path.  If you 
# try and open a file using a relative path, Excel won't load it and will raise an exception.

workbook = excel_app.open_workbook(abspath('../MassBalance.xlsx'))

# get a worksheet by name.
muk_io = workbook.get_sheet('MukIO')

# get line values



# when getting the data, Excel uses indexing for rows and columns starting at 1
# the cell in the top left corner is 1,1

# get the line 1 volume and elevation guess

line_1_z = muk_io.get_value(3, run_no + 2)
line_1_v = muk_io.get_value(4, run_no + 2)

# get the line 2 volume and elevation guess

line_2_z = muk_io.get_value(5, run_no + 2)
line_2_v = muk_io.get_value(6, run_no + 2)

# get the line 3 volume and elevation guess

line_3_z = muk_io.get_value(7, run_no + 2)
line_3_v = muk_io.get_value(8, run_no + 2)

# get the pond volume and elevation guess
pond_z = muk_io.get_value(9, run_no + 2)
pond_v = muk_io.get_value(10, run_no + 2)


# Now build the deposition table for the multi-stream deposition

# first, create a list that will be passed to the multi-stream deposition
# command with the line details in it.

deposition_table = []

# Now, add each line, but only if the line has volume > 0

if line_1_v > 0:
    deposition_table.append( [   u'Flow path down slope',
                        u'../discharge1.mcurve',
                        u'Sand',
                        line_1_v,
                        line_1_z])
    
if line_2_v > 0:
    deposition_table.append( [   u'Flow path down slope',
                        u'../discharge2.mcurve',
                        u'Sand',
                        line_2_v,
                        line_2_z])
    
if line_3_v > 0:                        
    deposition_table.append( [   u'Flow path down slope',
                        u'../discharge3.mcurve',
                        u'Sand',
                        line_3_v,
                        line_3_z])
    
                        
                        
# Now run the deposition dialog.  Normally in a macro, the command will just run
# when its called.  By including the key/value 'show_dialog': True, the dialog 
# will be displayed so that the user can edit things like the base topography.

cmd = get_command('Multi stream fixed pond volume')
result = cmd({   'base': u'',  # leave this blank and the user can select the grid to use.
    'deposition model': 'Multi stream fixed pond volume',
    'fluidVolume': pond_v,  # set the pond volume
    'maxChange': 2.0,
    'maxIterations': 40,
    'maxPondElevation': 114.0,
    'maxPondElevationChange': 1.0,
    'maxPondIterations': 20,
    'pointSpacing': 150.0,
    'pond': 'pond',
    'pondElevation': pond_z,  # set the initial pond elevation guess
    'pondTolerance': 1.0,
    'pond_location': (   2552.0744724278015,
                         -2067.5205581943374,
                         102.6405243335685),
    'schedule': deposition_table, # set the deposition lines
    'seCurveIncrement': 0.1,
    'tolerance': 1.0,
    'use_direct_deposition': False,
    'verticalOffset': 0.0,
    'show_dialog': True})

 

Running the script

This command has been written with the following assumptions when running a deposition model:

  • discharge points for each line are stored in the parent folder of the deposition run(s)
  • spreadsheet with depostion data is stored in the parent folder of the deposition run(s)
  • script is stored in the parent folder of the deposition run(s)

Starting in the directory holding the deposition points and spreadsheet, create a new sub-folder for the first run.  Run the script and enter 1 in the dialog box to get data for the first timestep.  Since there are only volumes given for Line 1, the multi-stream deposition table should have a single row in it with the Line 1 values and Line 1 discharge points.

Similarly the pond should have the correct volume and initial elevation guess.

After populating the base grid field, the command can be executed and the deposition model completed.

 

 

 

 

 

 

 

 

Related articles