Exporting Simulation Results

How to export simulation results from the BPTK-Py business simulation framework.

Keywords

system dynamics, systemdynamics, xmile, bptk, bptk-py, python, business simulation

Exporting Simulation Results

Exporting Scenario Data For Further Analysis In High-End Business Intelligence Tools

Notebooks are a perfect environment to create System Dynamics and Agent-based Models and analyse them in-depth – at least if you are a data scientist or computational modeler.

But what if you want to present your results to people who are not quite so tech savvy or don’t have all the necessary tools installed?

We face such situations quite often with our clients and at one point we asked ourselves:

• Why not use a high-end business intelligence tool such as Microsofts Power BI Desktop to create a polished dasboards?
• Why not share the reports using the Power BI service? After all, Power BI was created to create such data intelligence apps!
• Why not use notebooks to create sophisticated simulation models (which is what the Python ecosystem is good at) and then use Power BI for the fancy UI (which is what Power BI is good at)

To achieve this, all we really need BPTK-Py to do is to export the data generated by the simulation for those scenarios that are relevant for the report.

We can then import that data into Power BI and build the report using Power BI’s WYSIWIG tools.

To achieve this, we’ve added a new method called export_scenarios to BPTK-Py which writes the data for a set of scenarios and interactive settings to an Excel file.

Here is what the method looks like for the customer acquisition model, which is one of the models we’ve provided with the tutorial:

bptk.export_scenarios(
    scenario_manager="smCustomerAcquisition",
    equations=["customers","profit"],
    filename='/path/to/exported/data/customer_aquisition.xlsx',
    interactive_scenario="interactiveScenario",
    interactive_equations=["customers","profit"],
    interactive_settings= {
        "advertisingSuccessPct":(0,0.2,0.01),
        "referralFreeMonths":(0,40,10),
        "referralProgramAdoptionPct":(0,12,1),
        "referrals":(0,12,1)
    }
)

And here is a Power BI report we’ve created from the data, you can access it directly on PowerBI:

A Closer Look At BPTK-Py’s Export Function

This section takes a closer look at how the export function is implemented, just in case you would like to add some features or export the data in some other format.

The first thing to remember is how scenario data is stored in a pandas dataframe:

Notice that each indicator (aka equation, customers and profit in this example) has its own column and that the time dimension forms the index of the dataframe. Also notice that the name of the scenario referSomeonePlease is not stored in the dataframe itself.

Now when it comes to displaying the data in an interactive report (like the one above) we would like to be able to switch between scenarios. So one thing we need to do is to add a column containing a name of the scenario to the dataframe. We also would like one large dataframe containing the data from all the scenarios.

We now have all the data for all the scenarios in one large dataframe. Each row is indexed by the scenario it belongs to. The timestamp is only unique within a given scenario.

Generating The Data For Scenario Comparison

The data we have generated so far is a table with a column for each indicator, indexed by scenario.

This is fine if you want to look at data scenario by scenario or plot two different indicators for the same scenario.

But what if you want to compare the same indicator for different scenarios?

In such a case, your data needs to be structured a little differently - essential we then want a table with a column for each scenario, indexed by the indicator.

To achieve this, we need to loop through the scenarios again:

Generating The Data For Interactive Dashboards

In most cases creating an interactive report that just compares predefined scenarios is quite enough. But sometimes you would like to add a little dashboard to allow users to test different settings themselves, like the “Forecast” page in the example above.

The easiest way to achive this in Power BI is to use so called “What If” parameters to select a scenario from a set of pre-computed scenarios. We need to pre-compute them because currently Power BI doesn’t allow you to query data live with different parameters.

In most cases there will be thousands of “interactive” scenarios you need to pre-compute, so it is not feasible to enumerate them as a list. Instead the idea is to start with a base “interactive” scenario and then vary a set of parameters within a given range, much like in a Monte Carlo simulation.

*** smCustomerAcquisition ***
     base
     serviceFlop
     rethinkAdvertising
     referSomeonePlease
     hereWeGo
     boomButBust

*** smCustomerAcquisition ***
     base
     serviceFlop
     rethinkAdvertising
     referSomeonePlease
     hereWeGo
     boomButBust
     interactiveScenario

Now that we have a scenario, we need to define the “What if” parameters:

Now we need to pre-compute all possible combinations, which is quite a few for the ranges defined above:

11520

The last line of code uses some advanved functional programming to generates all possible combinations of the interactive parameters … let’s take a look at what it does using just two interactive parameters:

11520

Pre-computing every one of those combinations is the expensive step - around four minutes on a laptop, and 11 520 × 61 = 702 720 rows. It is shown here rather than run, because this page executes in your browser and a run of that size would freeze it:

interactive_dfs = []
interactive_scenario = "interactiveScenario"
interactive_equations = ["customers", "profit"]

scenario = bptk.get_scenario(scenario_manager, interactive_scenario)

for setting in settings:
    # apply one combination of the interactive parameters to the scenario
    for setting_index, key in enumerate(interactive_settings):
        scenario.set_property_value(key, setting[setting_index])

    bptk.reset_scenario_cache(
        scenario_manager=scenario_manager, scenario=interactive_scenario
    )
    df = bptk.plot_scenarios(
        scenario_managers=[scenario_manager],
        scenarios=[interactive_scenario],
        equations=interactive_equations,
        return_df=True,
    )

    # carry the settings and the timestamp into the frame, so a row identifies itself
    for setting_index, key in enumerate(interactive_settings):
        df[key] = [setting[setting_index]] * len(df.index)
    df["time"] = df.index

    interactive_dfs = interactive_dfs + [df]

interactive_tab = pd.concat(interactive_dfs, ignore_index=True, sort=False)
len(interactive_tab)   # 702720

Writing The Dataframes To An Excel File

With the dataframes in hand, Pandas’ ExcelWriter writes them into one workbook, one sheet each. This needs the xlsxwriter package, and it writes a file - so like the block above it is shown rather than run:

import xlsxwriter   # noqa: F401 - pandas needs it as the engine

with pd.ExcelWriter("./data/customer_acquisition.xlsx") as writer:
    scenarios_tab.to_excel(writer, sheet_name="scenarios")
    indicators_tab.to_excel(writer, sheet_name="indicators")
    interactive_tab.to_excel(writer, sheet_name="interactive")

Calling The Export Function Directly

Here is how you would call the export_scenarios function directly – it you don’t pass a filename it returns a dictionary containing the dataframes for both the scenarios and the interactive dashboard.

Important: With the given parameters the export function generates over 11.000 interactive scenarios amounting to around 30MB of data. On my machine (a Macbook Pro with 16MB of RAM) the function takes just under three minutes to complete.

And this is the whole thing in one call. export_scenarios does everything the sections above did step by step; leave filename out and it hands back a dictionary of dataframes instead of writing a file:

import os

bptk.export_scenarios(
    scenario_manager="smCustomerAcquisition",
    equations=["customers", "profit"],
    filename=os.path.join(os.getcwd(), "data", "customer_acquisition.xlsx"),
    interactive_scenario="interactiveScenario",
    interactive_equations=["customers", "profit"],
    interactive_settings={
        "advertisingSuccessPct": (0, 0.2, 0.01),
        "referralFreeMonths": (0, 40, 10),
        "referralProgramAdoptionPct": (0, 12, 1),
        "referrals": (0, 12, 1),
    },
)