4

I am working on a model to match supply and demand. A short explanation to the model: I have several “supply” points with a certain unit of resources and several “demand” points. The aim is to “discharge” the available resources from the “supply” points to the closest “demand” point, by minimizing transport distances.

In ArcGIS, I already found the closest “demand” point to each “supply” point with corresponding distance. In addition I have the amount of resources produced at the “supply” point and resources needed at the “demand” point . I stored the results in an Excel table and sorted them for distance small to large so that I can work further with them using Python.

I made a table with a simple overview, where I have 2 “supply” points and several “demand” points. The resources available at “supply” point 1 is 10 and at “supply” point 2 is 20. The demand at all “demand” points varies . The Excel table looks like this:

With that Excel table, I want to calculate how many units of resources could be supplied from each “supply” point ('D') to the closest (and 2nd closest …n closest) “demand” point('E'), until the value in the “supply” point is 0/distributed completely. I want to store the value of how much is transported from “supply” to “demand” in an additional column ('F') For that I wrote this code:

for row in range(1,sheet.max_row+1):
   if sheet['D'+str(row)].value !=0 and sheet['E'+str(row)].value !=0:
    for row in range(1,sheet.max_row+1):
        if sheet['D'+str(row)].value > sheet['E'+str(row)].value:
            sheet['F'+str(row)].value = sheet['E'+str(row)].value
            sheet['D'+str(row)].value = sheet['D'+str(row)].value - sheet['E'+str(row)].value
            sheet['E'+str(row)].value = 0
        if sheet['D'+str(row)].value < sheet['E'+str(row)].value:
            sheet['F'+str(row)].value = sheet['D'+str(row)].value
            sheet['E'+str(row)].value = sheet['E'+str(row)].value -sheet['D'+str(row)].value
            sheet['D'+str(row)].value = 0
        if sheet['D'+str(row)].value == sheet['E'+str(row)].value:
            sheet['F'+str(row)].value = sheet['E'+str(row)].value
            sheet['D'+str(row)].value = 0
            sheet['E'+str(row)].value = 0

The above code works and calculates all values that I want in the columns F in all rows over the length of the list.

However, there remains one problem:

The way the code is written now, it does not update the values of supply and demand properly. So it does not take into account that, if some resources are supplied from one “supply” point to a “demand” point, the amount of available resources in the “supply” cell changes for calculations thereafter.

More specifically, the “supply” point 1 only has 10 units available that it can “distribute”. In the example table below, “supply” point 1 can only supply resources to the first and second closest “demand” point, until all resources of “supply” point 1 are used up (6 units to “demand point” with DEMAND_FID = 10 and 4 units to “demand point” DEMAND_FID = 11). Afterwards, all units of “supply” point 1 (SUPPLY_R) are “gone”, the demand of “demand point” with DEMAND_FID = 10 is fulfilled and the demand of “demand point” with DEMAND_FID = 11 is reduced from 6 to 1. Therefore, the 6th row should not even be calculated, since “supply” point 1 cannot supply any additional resources to a “demand point” Therefore, I want to update the values of SUPPLY_R and DEMAND_R after one row has been calculated.

The “ideal” table with results should look like this:

How can I solve the issue of changing one value in one row, based on the previous rows within a calculation?

I have tried to update the new “supply” and “demand” values with values from a dictionary but this does not give the desired result.

CaroW
  • 63
  • 6
  • You are actually coding in VBA masquerading as Python. It is actually much simpler if you instead treat Excel as a visualisation/export tool, read all your values into a Pandas dataframe `import pandas as pd; df = pd.read_excel('input.xlsx')` and do your calculations there before finally exporting it to Excel via `df.to_excel('output.xlsx)`. – ycx Feb 22 '19 at 15:02
  • You could set this type of model up in excel and use the solver to iterate through to the “best” solution... – Solar Mike Feb 22 '19 at 17:25
  • Have a look at this for an idea: https://stackoverflow.com/a/53714432/4961700 – Solar Mike Feb 22 '19 at 17:40
  • @ycx Yes I know that dataframes are good for analyzing Excel sheets and am also working with them. But how does a Dataframe can help me (better) in this case to solve my question? Is there a special function that I can use with a Dataframe? – CaroW Feb 25 '19 at 16:59
  • 1
    @CaroW you can make use of `.groupby()` and `.transform()` in the pandas library as well as cumulative sums to achieve what you require. Afterwords, its simply a matter of rearranging them by their original index again. Try it out and edit your question with some code using pandas and I can probably work from there to help you further. – ycx Feb 25 '19 at 17:16

1 Answers1

1

I actually solve the issue now. I am working with a dictionary to update the values in each row (sorry the numbering of columns is now different but the method becomes clear hopefully)

dict1={1: 156, 2: 5, 3: 207, ....., 226: 142}
dict2={{1: 23, 2: 18, 3: 23,....., 1530: 9}

for row in range(2,sheet.max_row+1):
   sheet['F'+str(row)].value = dict1[sheet['C'+str(row)].value] 
   sheet['I'+str(row)].value = dict2[sheet['D'+str(row)].value]

   if sheet['F'+str(row)].value > sheet['I'+str(row)].value:
          sheet['L'+str(row)].value = sheet['I'+str(row)].value 
          if sheet['L'+str(row)].value <= 0:
                sheet['L'+str(row)].value = None
          sheet['F'+str(row)].value = sheet['F'+str(row)].value -sheet['I'+str(row)].value
          sheet['I'+str(row)].value = 0
          if sheet['L'+str(row)].value > 0:
                sheet['M'+str(row)].value = sheet['C'+str(row)].value
          else:
                sheet['M'+str(row)].value = None
          if sheet['L'+str(row)].value > 0:
                sheet['N'+str(row)].value = sheet['D'+str(row)].value
          else:
                sheet['N'+str(row)].value = None

      #2 different methods to update the dictionary
          dict1_1={sheet['C'+str(row)].value :sheet['F'+str(row)].value}
          dict1.update(dict1_1)
          sheet['F'+str(row)].value = dict1[sheet['C'+str(row)].value]
          dict2[sheet['D'+str(row)].value]= sheet['I'+str(row)].value
          sheet['I'+str(row)].value = dict2[sheet['D'+str(row)].value]
CaroW
  • 63
  • 6