def create_scenarios(output_file, parameters_query, parameters_names, type_id):
query = '''
WITH
parameters AS (
{parameters_query}
),
exp_sub AS (
SELECT *
FROM experiments
WHERE id >= 23
),
bt_sub AS (
SELECT *
FROM backtests
WHERE id = 2
)
SELECT
parameters.*,
exp_sub.id as experiment_id,
exp_sub.returns_name,
exp_sub.asset_columns,
exp_sub.start_date,
exp_sub.end_date,
exp_sub.granularity,
bt_sub.id as backtest_id,
bt_sub.trailing_periods,
bt_sub.update_periods
FROM
parameters CROSS JOIN
exp_sub CROSS JOIN
bt_sub
'''.format(parameters_query=parameters_query)
scenarios = pd.read_sql(query, db.conn)
scenarios['type_id'] = type_id
metadata = pg.dataframe2dictlist(scenarios[['parameters_id', 'experiment_id', 'backtest_id', 'type_id']])
parameters = pg.dataframe2dictlist(scenarios[parameters_names])
returns = pg.dataframe2dictlist(scenarios[['returns_name', 'asset_columns',
'start_date', 'end_date', 'granularity']])
backtest = pg.dataframe2dictlist(scenarios[['trailing_periods', 'update_periods']])
scenarios_list = list()
for m, p, r, b in zip(metadata, parameters, returns, backtest):
scenarios_list.append(json.dumps(
{'portfolio': p,
'metadata': m,
'returns': r,
'backtest': b}, default=pg.json_default))
with open(output_file, 'w') as outfile:
outfile.write('\n'.join(scenarios_list))
e.end_date,
e.granularity,
bt_sub.id as backtest_id,
bt_sub.trailing_periods,
bt_sub.update_periods
from portfolio_parameters pp
cross join experiments e
cross join backtests bt
where pp.type_id = 3
and e.id >=3
'''
ewp = pd.read_sql(query, db.conn)
# %%
metadata_columns = ['parameters_id', 'type_id', 'experiment_id', 'backtest_id']
metadata_list = pg.dataframe2dictlist(ewp[metadata_columns])
portfolio_list = ewp.parameters.tolist()
returns_columns = ['end_date', 'start_date', 'granularity', 'returns_name',
'asset_columns']
returns_list = pg.dataframe2dictlist(ewp[returns_columns])
backtest_columns = ['trailing_periods', 'update_periods']
backtest_list = pg.dataframe2dictlist(ewp[backtest_columns])
# %%
scenarios_list = zip(metadata_list, portfolio_list, returns_list, backtest_list)
scenarios_fields = ['metadata', 'portfolio', 'returns', 'backtest']
scenarios = [json.dumps({k: v for k, v in zip(scenarios_fields, s)},
default=pg.json_default)