I have written an optimization model and now I want to generate some output files (xlsx) for the different variables. I have put the whole data of the variables in one DataFrame with the following code:
block_vars = []
for var in model.component_data_objects(Var):
block_vars.append(var.parent_component())
block_vars = list(set(block_vars))
dc = {(str(bv).split('.')[0], str(bv).split('.')[-1], i): bv[i].value for bv in block_vars for i in getattr(bv, '_index')}
df = pd.DataFrame(list(dc.items()), columns=['tuple','value'])
df['variable_name'] = df['tuple'].str[-2]
df['variable_index'] = df['tuple'].str[-1]
df.drop('tuple', axis=1, inplace=True)
This works fine (even though it probably is not the smoothest way.
Now I am filtering the different variables with a block as follows:
writer = pd.ExcelWriter('UC.xlsx')
conditions = {'variable_name':'vCommit'}
df_uc = df.copy()
df_uc = df_uc[(df_uc[list(conditions)] == pd.Series(conditions)).all(axis=1)].drop('variable_name', 1)
df_uc.to_excel(writer, 'Tabelle1')
This works as well. Now comes the part I am struggeling with. Those variables are indexed (with 2 or 3 indexes, depending on the variable), and I would like to be the output something like:
index1 index2 value
1 1 1
1 2 0
...
but those indexes are in a tuple in one row of the DataFrame and I am not sure how to access them and reshape the DataFrame correspondingly. Does anybody know a way to do that? Thanks for your help!!!
I would expand out the index into multiple columns when first creating the DataFrame. You can try to look at the code here for inspiration: https://github.com/gseastream/pyomo/blob/fa9b8f20a0f9afafa7cbd4607baa8b4963a96f42/pyomo/repn/plugins/excel_writer.py
Grant was working on an interface to Excel, but development priorities shifted elsewhere.
Also, a quick note: you can use model.component_objects(Var) instead of what you have with list(set(block_vars)).