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 Distribution
Built Distribution
Close
Hashes for excel-modelling-helper-0.5.8.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | 5c43313dcf77c9a20b6c91c0f96ae8f42f3a2869c0f92163f0030202dcf130ce |
|
MD5 | a4fcdf182e39485ab7c7ca5f4deb2948 |
|
BLAKE2b-256 | 81b28fef643ee88ab8d1ebe5630b760b216b7be68986c2ac5af78385111473a6 |
Close
Hashes for excel_modelling_helper-0.5.8-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | b8f7868a30cec9afd013699df01d2a0eccb2e57889b10a373d1cb9ce1eb4b503 |
|
MD5 | 96eb7a9e6fcd35bb6c2350beac989ffe |
|
BLAKE2b-256 | 3803d865b62bd88f0877e375ca42151a2f8e78c3f4a6d4206f65e6f5f79e83e9 |