Export DOcplex model output to Excel using pandas

Question

I have the following solution of a model trans using DOcplex in python:

# Solve command
solution = mdl.solve(log_output=True)
solution.display()

#printed solution
solution for: transp
objective: 73231116.000
q_Coupe 1_Smithton = 6000.000
q_Coupe 2_Smithton = 1000.000
q_Coupe 3_Bell Bay = 9000.000
q_Coupe 4_Smithton = 3000.000
q_Coupe 5_Smithton = 3000.000
q_Coupe 6_Bell Bay = 4000.000
q_Coupe 8_Hobart = 7000.000
q_Coupe 10_Hobart = 3000.000
CAP_Bell Bay = 8.552
CAP_Smithton = 8.552
CAP_Hobart = 6.579
X_Bell Bay = 1
X_Smithton = 1
X_Hobart = 1

Herein:

I = list of forests
['Coupe 1', 'Coupe 2', 'Coupe 3', 'Coupe 4', 'Coupe 5', 'Coupe 6', 'Coupe 7', 'Coupe 8', 'Coupe 9', 'Coupe 10']
J = list of facilities
['Bell Bay', 'Surrey Hills', 'Smithton', 'Hobart']
arcs = [(i, j) for i in I for j in J]
[('Coupe 1', 'Bell Bay'), ('Coupe 1', 'Surrey Hills'), ('Coupe 1', 'Smithton'), ('Coupe 1', 'Hobart'), ('Coupe 2', 'Bell Bay'), ('Coupe 2', 'Surrey Hills'), ('Coupe 2', 'Smithton'), ('Coupe 2', 'Hobart'), ('Coupe 3', 'Bell Bay'), ('Coupe 3', 'Surrey Hills'), ('Coupe 3', 'Smithton'), ('Coupe 3', 'Hobart'), ('Coupe 4', 'Bell Bay'), ('Coupe 4', 'Surrey Hills'), ('Coupe 4', 'Smithton'), ('Coupe 4', 'Hobart'), ('Coupe 5', 'Bell Bay'), ('Coupe 5', 'Surrey Hills'), ('Coupe 5', 'Smithton'), ('Coupe 5', 'Hobart'), ('Coupe 6', 'Bell Bay'), ('Coupe 6', 'Surrey Hills'), ('Coupe 6', 'Smithton'), ('Coupe 6', 'Hobart'), ('Coupe 7', 'Bell Bay'), ('Coupe 7', 'Surrey Hills'), ('Coupe 7', 'Smithton'), ('Coupe 7', 'Hobart'), ('Coupe 8', 'Bell Bay'), ('Coupe 8', 'Surrey Hills'), ('Coupe 8', 'Smithton'), ('Coupe 8', 'Hobart'), ('Coupe 9', 'Bell Bay'), ('Coupe 9', 'Surrey Hills'), ('Coupe 9', 'Smithton'), ('Coupe 9', 'Hobart'), ('Coupe 10', 'Bell Bay'), ('Coupe 10', 'Surrey Hills'), ('Coupe 10', 'Smithton'), ('Coupe 10', 'Hobart')]

And these are the variables in the model:

q = mdl.continuous_var_dict(arcs, name='q')
CAP = mdl.continuous_var_dict(J, name='CAP')
X = mdl.binary_var_dict(CAP, name='X')

I want the q variable results of my solution exported to Excel as a matrix dataframe/table of I rows by J columns. And I wanna add another row of the solution of CAP per J. The missing values of q and CAP should be filled out with 0.

q               Bell Bay    Surrey Hills    Smithton    Hobart
Coupe 1         0.00        0.00            6000.00     0.00
Coupe 2         0.00        0.00            1000.00     0.00
Coupe 3         9000.00     0.00            0.00        0.00
Coupe 4         0.00        0.00            3000.00     0.00
Coupe 5         0.00        0.00            3000.00     0.00
Coupe 6         4000.00     0.00            0.00        0.00
Coupe 7         0.00        0.00            0.00        0.00
Coupe 8         0.00        0.00            0.00        7000.00
Coupe 9         0.00        0.00            0.00        0.00
Coupe 10        0.00        0.00            0.00        3000.00

CAP             8.552       0.00            8.552       6.579

Preferably use pandas.

score 1 · Answer 1 · answered Jul 22 '20 at 08:50

Here is a small code which converts the solution value of a variable matrix (that is a dict of 2-tuples) to variables, to a pandas DataFrame. Once converted to pandas, you have everything you need to export to Excel and other formats. The code extracts rows and column indices from the matrix keys, builds a column-oriented dictionary of values, and then a DataFrame.

def matrix_solution_to_dataframe(var_matrix, sol):
    # compute a 2d dataframe from a variable matrix and a solution
    # (i, j) -> life][i,j].solution_value in one step.
    matrix_val_d = sol.get_value_dict(var_matrix)
    # extract rows and column indices
    keys = var_matrix.keys()
    row_indices = set()
    col_indices = set()
    for (i, j) in keys:
        row_indices.add(i)
        col_indices.add(j)
    # build a column-oriented dict:
    dtf_d = {col: {row: matrix_val_d[row, col] for row in row_indices} for col in col_indices}
    try:
        from pandas import DataFrame
        return DataFrame(dtf_d)
    except ImportError:
        print(f"-- pandas not found, returning a dict")
        return dtf_d

score 0 · Answer 2 · edited Jul 30 '20 at 11:33

0

You could rely on https://pypi.org/project/XlsxWriter/ and do a loop on all elements in I and J.

edited Jul 30 '20 at 11:33

halfer

19,824
17
99
186

answered Jul 22 '20 at 06:01

Alex Fleischer

9,276
2
12
15

Export DOcplex model output to Excel using pandas

2 Answers2