Use Excel to define your model parameters.
Project description
Example
=======
Given an excel file with rows similar to the below
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| var | s | module | di | par | p | p | u | s | e | C | r | l | c | s |
| iab | c | | st | am | a | a | n | t | n | A | e | a | o | o |
| le | e | | ri | 1 | r | r | i | a | d | G | f | b | m | u |
| | n | | bu | | a | a | t | r | d | R | d | e | m | r |
| | a | | ti | | m | m | | t | a | | a | l | e | c |
| | r | | on | | 2 | 3 | | d | t | | t | | n | e |
| | i | | | | | | | a | e | | e | | t | |
| | o | | | | | | | t | | | | | | |
| | | | | | | | | e | | | | | | |
+=====+===+===============+====+=====+===+===+===+===+===+===+===+===+===+===+
| a | | numpy.random | ch | 1 | | | k | 0 | 0 | 0 | 0 | t | | |
| | | | oi | | | | g | 1 | 1 | . | 1 | e | | |
| | | | ce | | | | | / | / | 1 | / | s | | |
| | | | | | | | | 0 | 0 | 0 | 0 | t | | |
| | | | | | | | | 1 | 4 | | 1 | v | | |
| | | | | | | | | / | / | | / | a | | |
| | | | | | | | | 2 | 2 | | 2 | r | | |
| | | | | | | | | 0 | 0 | | 0 | 1 | | |
| | | | | | | | | 0 | 0 | | 0 | | | |
| | | | | | | | | 9 | 9 | | 9 | | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| b | | numpy.random | un | 2 | 4 | | - | | | | | l | | |
| | | | if | | | | | | | | | a | | |
| | | | or | | | | | | | | | b | | |
| | | | m | | | | | | | | | e | | |
| | | | | | | | | | | | | l | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| c | | numpy.random | tr | 3 | 6 | 1 | - | | | | | l | | |
| | | | ia | | | 0 | | | | | | a | | |
| | | | ng | | | | | | | | | b | | |
| | | | ul | | | | | | | | | e | | |
| | | | ar | | | | | | | | | l | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| d | | bottom_up_com | Di | cor | | | J | | | | | l | | |
| | | parision.samp | st | e_r | | | / | | | | | a | | |
| | | ling_core_rou | ri | out | | | G | | | | | b | | |
| | | ters | bu | ers | | | b | | | | | e | | |
| | | | ti | .cs | | | | | | | | l | | |
| | | | on | v | | | | | | | | | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| | | | | | | | | | | | | | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| a | s | numpy.random | ch | 2 | | | | | | | | t | | |
| | 1 | | oi | | | | | | | | | e | | |
| | | | ce | | | | | | | | | s | | |
| | | | | | | | | | | | | t | | |
| | | | | | | | | | | | | v | | |
| | | | | | | | | | | | | a | | |
| | | | | | | | | | | | | r | | |
| | | | | | | | | | | | | 1 | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| mul | | numpy.random | ch | 1,2 | | | k | 0 | 0 | | | t | | |
| tip | | | oi | ,3 | | | g | 1 | 1 | | | e | | |
| le | | | ce | | | | | / | / | | | s | | |
| cho | | | | | | | | 0 | 0 | | | t | | |
| ice | | | | | | | | 1 | 1 | | | v | | |
| | | | | | | | | / | / | | | a | | |
| | | | | | | | | 2 | 2 | | | r | | |
| | | | | | | | | 0 | 0 | | | 1 | | |
| | | | | | | | | 0 | 0 | | | | | |
| | | | | | | | | 7 | 9 | | | | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
You can run python/ numpy code that references these variables and
generates random distributions.
For example, the following will initialise a variable ``c`` with a
vector of size 2 with random values from a triangular distribution.
::
np.random.seed(123)
data = ParameterLoader.from_excel('test.xlsx', size=2, sheet_index=0)
c = data['c']
>>> [ 7.08471918 5.45131111]
Other types of distributions include ``choice`` and ``normal``. However
you can specify any distribution from numpy that takes up to three
parameters to init.
You can also specify a .csv file with samples and an empiricial
distribution function is generated and variable values will be sampled
from that.
Scenarios
---------
It is possible to define scenarios and have paramter values for a
variable change with each scenario.
::
data = ParameterLoader.from_excel('test.xlsx', size=1, sheet_index=0)
res = data['a'][0]
assert res == 1.
data.select_scenario('s1')
res = data['a'][0]
assert res == 2.
use ``data.unselect_scenario()`` to return to the default value.
Pandas Dataframes
-----------------
It is possible to define a time frame for distributions and have sample
values change over time.
::
# the time axis of our dataset
times = pd.date_range('2009-01-01', '2009-04-01', freq='MS')
# the sample axis our dataset
samples = 2
dfl = DataSeriesLoader.from_excel('test.xlsx', times, size=samples, sheet_index=0)
res = dfl['a']
assert res.loc[[datetime(2009, 1, 1)]][0] == 1
assert np.abs(res.loc[[datetime(2009, 4, 1)]][0] - pow(1.1, 3. / 12)) < 0.00001
Reload
------
Reloading data sources is useful when underlying excel files change.
::
times = pd.date_range('2009-01-01', '2009-04-01', freq='MS')
samples = 2
data = MultiSourceLoader()
data.add_source(ExcelSeriesLoaderDataSource('test.xlsx', times, size=samples, sheet_index=0))
res = data['a'][0]
assert res == 1.
wb = load_workbook(filename='test.xlsx')
ws = wb.worksheets[0]
ws['E2'] = 4.
wb.save(filename='test.xlsx')
data.reload_sources()
res = data['a'][0]
assert res == 4.
wb = load_workbook(filename='test.xlsx')
ws = wb.worksheets[0]
ws['E2'] = 1.
wb.save(filename='test.xlsx')
data.reload_sources()
data.set_scenario('s1')
res = data['a'][0]
assert res == 2.
data.reset_scenario()
res = data['a'][0]
assert res == 1.
Metadata
--------
The contents of the rows is also contained in the metadata
::
# the time axis of our dataset
times = pd.date_range('2009-01-01', '2009-04-01', freq='MS')
# the sample axis our dataset
samples = 3
dfl = DataSeriesLoader.from_excel('test.xlsx', times, size=samples, sheet_index=0)
res = dfl['a']
print(res._metadata)
15.5.2015 0.1.1 Renamed class to ParameterLoader
22.5.2015 0.1.2 Add sheet index as parameter to loader
11.1.2016 0.2.2 Added support to generate pandas dataframes, update to python 3
18.4.2016 0.2.7 Added new flag 'single_var' to freeze all variables except one to their mean value - use in sensitivity analysis.
19.8.2016 0.3.0 Upgrade to xarray 0.8.1
20.8.2016 0.3.1 Single var mean now analytical for choice, uniform, triangular and normal; trim white space from var names
4.07.2017 0.4.0 Rewrite with new API
4.07.2017 0.4.1 Added XLWings interface to read from Excel
14.09.2017 0.5.0 Delay sampling from data source until __call__ on Parameter.
16.2.2018 0.5.1 Fixed error in generation of random distributions with zero param values
=======
Given an excel file with rows similar to the below
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| var | s | module | di | par | p | p | u | s | e | C | r | l | c | s |
| iab | c | | st | am | a | a | n | t | n | A | e | a | o | o |
| le | e | | ri | 1 | r | r | i | a | d | G | f | b | m | u |
| | n | | bu | | a | a | t | r | d | R | d | e | m | r |
| | a | | ti | | m | m | | t | a | | a | l | e | c |
| | r | | on | | 2 | 3 | | d | t | | t | | n | e |
| | i | | | | | | | a | e | | e | | t | |
| | o | | | | | | | t | | | | | | |
| | | | | | | | | e | | | | | | |
+=====+===+===============+====+=====+===+===+===+===+===+===+===+===+===+===+
| a | | numpy.random | ch | 1 | | | k | 0 | 0 | 0 | 0 | t | | |
| | | | oi | | | | g | 1 | 1 | . | 1 | e | | |
| | | | ce | | | | | / | / | 1 | / | s | | |
| | | | | | | | | 0 | 0 | 0 | 0 | t | | |
| | | | | | | | | 1 | 4 | | 1 | v | | |
| | | | | | | | | / | / | | / | a | | |
| | | | | | | | | 2 | 2 | | 2 | r | | |
| | | | | | | | | 0 | 0 | | 0 | 1 | | |
| | | | | | | | | 0 | 0 | | 0 | | | |
| | | | | | | | | 9 | 9 | | 9 | | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| b | | numpy.random | un | 2 | 4 | | - | | | | | l | | |
| | | | if | | | | | | | | | a | | |
| | | | or | | | | | | | | | b | | |
| | | | m | | | | | | | | | e | | |
| | | | | | | | | | | | | l | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| c | | numpy.random | tr | 3 | 6 | 1 | - | | | | | l | | |
| | | | ia | | | 0 | | | | | | a | | |
| | | | ng | | | | | | | | | b | | |
| | | | ul | | | | | | | | | e | | |
| | | | ar | | | | | | | | | l | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| d | | bottom_up_com | Di | cor | | | J | | | | | l | | |
| | | parision.samp | st | e_r | | | / | | | | | a | | |
| | | ling_core_rou | ri | out | | | G | | | | | b | | |
| | | ters | bu | ers | | | b | | | | | e | | |
| | | | ti | .cs | | | | | | | | l | | |
| | | | on | v | | | | | | | | | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| | | | | | | | | | | | | | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| a | s | numpy.random | ch | 2 | | | | | | | | t | | |
| | 1 | | oi | | | | | | | | | e | | |
| | | | ce | | | | | | | | | s | | |
| | | | | | | | | | | | | t | | |
| | | | | | | | | | | | | v | | |
| | | | | | | | | | | | | a | | |
| | | | | | | | | | | | | r | | |
| | | | | | | | | | | | | 1 | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
| mul | | numpy.random | ch | 1,2 | | | k | 0 | 0 | | | t | | |
| tip | | | oi | ,3 | | | g | 1 | 1 | | | e | | |
| le | | | ce | | | | | / | / | | | s | | |
| cho | | | | | | | | 0 | 0 | | | t | | |
| ice | | | | | | | | 1 | 1 | | | v | | |
| | | | | | | | | / | / | | | a | | |
| | | | | | | | | 2 | 2 | | | r | | |
| | | | | | | | | 0 | 0 | | | 1 | | |
| | | | | | | | | 0 | 0 | | | | | |
| | | | | | | | | 7 | 9 | | | | | |
+-----+---+---------------+----+-----+---+---+---+---+---+---+---+---+---+---+
You can run python/ numpy code that references these variables and
generates random distributions.
For example, the following will initialise a variable ``c`` with a
vector of size 2 with random values from a triangular distribution.
::
np.random.seed(123)
data = ParameterLoader.from_excel('test.xlsx', size=2, sheet_index=0)
c = data['c']
>>> [ 7.08471918 5.45131111]
Other types of distributions include ``choice`` and ``normal``. However
you can specify any distribution from numpy that takes up to three
parameters to init.
You can also specify a .csv file with samples and an empiricial
distribution function is generated and variable values will be sampled
from that.
Scenarios
---------
It is possible to define scenarios and have paramter values for a
variable change with each scenario.
::
data = ParameterLoader.from_excel('test.xlsx', size=1, sheet_index=0)
res = data['a'][0]
assert res == 1.
data.select_scenario('s1')
res = data['a'][0]
assert res == 2.
use ``data.unselect_scenario()`` to return to the default value.
Pandas Dataframes
-----------------
It is possible to define a time frame for distributions and have sample
values change over time.
::
# the time axis of our dataset
times = pd.date_range('2009-01-01', '2009-04-01', freq='MS')
# the sample axis our dataset
samples = 2
dfl = DataSeriesLoader.from_excel('test.xlsx', times, size=samples, sheet_index=0)
res = dfl['a']
assert res.loc[[datetime(2009, 1, 1)]][0] == 1
assert np.abs(res.loc[[datetime(2009, 4, 1)]][0] - pow(1.1, 3. / 12)) < 0.00001
Reload
------
Reloading data sources is useful when underlying excel files change.
::
times = pd.date_range('2009-01-01', '2009-04-01', freq='MS')
samples = 2
data = MultiSourceLoader()
data.add_source(ExcelSeriesLoaderDataSource('test.xlsx', times, size=samples, sheet_index=0))
res = data['a'][0]
assert res == 1.
wb = load_workbook(filename='test.xlsx')
ws = wb.worksheets[0]
ws['E2'] = 4.
wb.save(filename='test.xlsx')
data.reload_sources()
res = data['a'][0]
assert res == 4.
wb = load_workbook(filename='test.xlsx')
ws = wb.worksheets[0]
ws['E2'] = 1.
wb.save(filename='test.xlsx')
data.reload_sources()
data.set_scenario('s1')
res = data['a'][0]
assert res == 2.
data.reset_scenario()
res = data['a'][0]
assert res == 1.
Metadata
--------
The contents of the rows is also contained in the metadata
::
# the time axis of our dataset
times = pd.date_range('2009-01-01', '2009-04-01', freq='MS')
# the sample axis our dataset
samples = 3
dfl = DataSeriesLoader.from_excel('test.xlsx', times, size=samples, sheet_index=0)
res = dfl['a']
print(res._metadata)
15.5.2015 0.1.1 Renamed class to ParameterLoader
22.5.2015 0.1.2 Add sheet index as parameter to loader
11.1.2016 0.2.2 Added support to generate pandas dataframes, update to python 3
18.4.2016 0.2.7 Added new flag 'single_var' to freeze all variables except one to their mean value - use in sensitivity analysis.
19.8.2016 0.3.0 Upgrade to xarray 0.8.1
20.8.2016 0.3.1 Single var mean now analytical for choice, uniform, triangular and normal; trim white space from var names
4.07.2017 0.4.0 Rewrite with new API
4.07.2017 0.4.1 Added XLWings interface to read from Excel
14.09.2017 0.5.0 Delay sampling from data source until __call__ on Parameter.
16.2.2018 0.5.1 Fixed error in generation of random distributions with zero param values
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distributions
No source distribution files available for this release.See tutorial on generating distribution archives.
Built Distribution
Close
Hashes for excel_modelling_helper-0.5.11-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 1c5ee5f8dd7e388d6222532d745ff41e0cc05b1d16f3e822ab16939bdf8437ae |
|
MD5 | 80d45f5ba04a7ff709c0602a6ca51199 |
|
BLAKE2b-256 | 4acc3de3f86b33f2d090e4049ebe2e0ffbcb759881746192204ee46d8803d48d |