[FIXED] Exporting Results from Linear Optimization in PuLP to Excel

Issue

I'm new to Python and the PuLP package in general, and have some results in the terminal of my Jupyter Notebook I'd like to export to Excel if possible. I did not create a data frame initially-- but was wondering if it is possible to create a data frame for results after they have printed. My output is below:

Service_('CZe_0004',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0004',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0004',_'DC_PA_CHAMBERSBURG') = 0.0
Service_('CZe_0004',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0004',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0004',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0004',_'MFC_SC_GREER') = 0.0
Service_('CZe_0005',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0005',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0005',_'DC_PA_CHAMBERSBURG') = 0.0
Service_('CZe_0005',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0005',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0005',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0005',_'MFC_SC_GREER') = 0.0
Service_('CZe_0006',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0006',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0006',_'DC_PA_CHAMBERSBURG') = 0.0
Service_('CZe_0006',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0006',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0006',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0006',_'MFC_SC_GREER') = 0.0
Service_('CZe_0007',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0007',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0007',_'DC_PA_CHAMBERSBURG') = 0.0
Service_('CZe_0007',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0007',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0007',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0007',_'MFC_SC_GREER') = 0.0
Service_('CZe_0008',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0008',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0008',_'DC_PA_CHAMBERSBURG') = 0.0
Service_('CZe_0008',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0008',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0008',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0008',_'MFC_SC_GREER') = 0.0
Service_('CZe_0009',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0009',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0009',_'DC_PA_CHAMBERSBURG') = 0.0
Service_('CZe_0009',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0009',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0009',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0009',_'MFC_SC_GREER') = 0.0
Service_('CZe_0010',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0010',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0010',_'DC_PA_CHAMBERSBURG') = 87.14
Service_('CZe_0010',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0010',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0010',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0010',_'MFC_SC_GREER') = 0.0
Service_('CZe_0011',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0011',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0011',_'DC_PA_CHAMBERSBURG') = 0.0
Service_('CZe_0011',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0011',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0011',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0011',_'MFC_SC_GREER') = 0.0
Service_('CZe_0012',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0012',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0012',_'DC_PA_CHAMBERSBURG') = 0.0
Service_('CZe_0012',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0012',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0012',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0012',_'MFC_SC_GREER') = 0.0
Service_('CZe_0013',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0013',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0013',_'DC_PA_CHAMBERSBURG') = 0.0
Service_('CZe_0013',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0013',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0013',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0013',_'MFC_SC_GREER') = 0.0
Service_('CZe_0014',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0014',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0014',_'DC_PA_CHAMBERSBURG') = 0.0
Service_('CZe_0014',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0014',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0014',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0014',_'MFC_SC_GREER') = 0.0
Service_('CZe_0015',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0015',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0015',_'DC_PA_CHAMBERSBURG') = 94.76
Service_('CZe_0015',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0015',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0015',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0015',_'MFC_SC_GREER') = 0.0
Service_('CZe_0016',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0016',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0016',_'DC_PA_CHAMBERSBURG') = 0.0
Service_('CZe_0016',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0016',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0016',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0016',_'MFC_SC_GREER') = 0.0
Service_('CZe_0017',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0017',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0017',_'DC_PA_CHAMBERSBURG') = 84.73
Service_('CZe_0017',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0017',_'MFC_FL_JACKSONVILLE') = 0.0
Service_('CZe_0017',_'MFC_IL_ROMEOVILLE') = 0.0
Service_('CZe_0017',_'MFC_SC_GREER') = 0.0
Service_('CZe_0018',_'DC_CA_FRESNO') = 0.0
Service_('CZe_0018',_'DC_IN_GREENWOOD') = 0.0
Service_('CZe_0018',_'DC_PA_CHAMBERSBURG') = 142.46
Service_('CZe_0018',_'DC_TX_DALLAS') = 0.0
Service_('CZe_0018',_'MFC_FL_JACKSONVILLE') = 0.0

I believe I have saturated my search attempts and simply don't know what to try.

Solution

You do not need to use a data frame to do this, unless you have another purpose for the data frame. I think it is just as easy to write out the results to a datafile and use that.

I'd suggest just writing your own "CSV" files (comma separated value). It is a standard format and fairly universal. Excel can read them just fine, and they are easier to read back into python.

Here is a little script that makes a fake pulp problem and writes the values to a .csv.

If you haven't done it before in Excel, it will open the .csv fine but when you go to save it will give you some options to either re-save it as a .csv with some hideous warnings about formatting, or just saving it as a new .xlsx file. Your pick.

import pulp

sources = ['cze1', 'cze2', 'cze3']
destinations = ['Jax', 'Fresno', 'Dallas']

prob = pulp.LpProblem('example')
service = pulp.LpVariable.dicts('service', [(s, d) for s in sources for d in destinations])

# assign some bogus values (simulate solving...)
for (s, d) in service.keys():
    service[s,d].setInitialValue(1.5)

# write to a csv file
output_filename = 'output.csv'

# use a context manager to open/close the file...
with open(output_filename, 'w') as fout:
    for (s, d) in service.keys():
        line = ','.join([s, d, str(service[s,d].value())])   # make a string out of the elements, separated by commas
        fout.write(line)    # write the line to the file
        fout.write('\n')    # add a newline character

Answered By - AirSquid