Как обновить значения в Excel ВО ВРЕМЯ расчета и на основе предыдущих результатов этого расчета?

Я работаю над моделью соответствия спроса и предложения. Краткое пояснение к модели: у меня есть несколько точек «предложения» с определенной единицей ресурсов и несколько точек «спроса». Цель состоит в том, чтобы «разгрузить» имеющиеся ресурсы из точек «предложения» в ближайшую точку «спроса», минимизировав транспортные расстояния.

В ArcGIS я уже нашел ближайшую точку «спроса» к каждой точке «предложения» с соответствующим расстоянием. Кроме того, у меня есть количество ресурсов, произведенных в точке «предложения» и ресурсов, необходимых в точке «спроса». Я сохранил результаты в таблице Excel и отсортировал их по расстоянию от малого к большому, чтобы иметь возможность работать с ними дальше с помощью Python.

Я сделал таблицу с простым обзором, где у меня есть 2 точки «предложения» и несколько точек «спроса». Ресурсы, доступные в точке «предложения» 1, равны 10, а в точке «предложения» 2 — 20. Спрос во всех точках «спроса» варьируется . Таблица Excel выглядит следующим образом:

С помощью этой таблицы Excel я хочу рассчитать, сколько единиц ресурсов может быть поставлено из каждой точки «поставки» («D») в ближайшую (и вторую ближайшую…n ближайшую) точку «спроса» («E»), пока значение в точке «снабжение» равно 0/распределено полностью. Я хочу сохранить значение того, сколько транспортируется из «предложения» в «спрос» в дополнительном столбце ('F'). Для этого я написал этот код:

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

Приведенный выше код работает и вычисляет все значения, которые мне нужны, в столбцах F во всех строках по длине списка.

Однако остается одна проблема:

То, как код написан сейчас, не обновляет значения спроса и предложения должным образом. Таким образом, не учитывается, что если некоторые ресурсы поставляются из одной точки «предложения» в точку «спроса», количество доступных ресурсов в ячейке «предложения» изменяется для последующих расчетов.

В частности, точка «снабжения» 1 имеет только 10 доступных единиц, которые она может «распределить». В приведенной ниже таблице пример «точка снабжения» 1 может поставлять ресурсы только в первую и вторую ближайшие точки «спроса», пока все ресурсы «точки снабжения» 1 не будут израсходованы (6 единиц до «точки спроса» с DEMAND_FID = 10). и 4 единицы до «точки спроса» DEMAND_FID = 11). После этого все единицы «точки снабжения» 1 (SUPPLY_R) «исчезли», спрос «точки спроса» с DEMAND_FID = 10 выполнен, а спрос «точки спроса» с DEMAND_FID = 11 уменьшен с 6 до 1. Следовательно, 6-ю строку даже не следует вычислять, так как точка «предложения» 1 не может поставлять какие-либо дополнительные ресурсы в «точку спроса». Поэтому я хочу обновить значения SUPPLY_R и DEMAND_R после того, как будет рассчитана одна строка.

«Идеальная» таблица с результатами должна выглядеть так:

Как я могу решить проблему изменения одного значения в одной строке на основе предыдущих строк в расчете?

Я попытался обновить новые значения «предложения» и «спроса» значениями из словаря, но это не дало желаемого результата.


python python-2.7 excel arcmap
person CaroW    schedule 22.02.2019    source источник
comment
На самом деле вы кодируете VBA, маскируясь под Python. На самом деле гораздо проще, если вместо этого вы будете рассматривать Excel как инструмент визуализации/экспорта, считывать все свои значения в фрейм данных Pandas import pandas as pd; df = pd.read_excel('input.xlsx') и выполнять там свои вычисления, прежде чем, наконец, экспортировать их в Excel через df.to_excel('output.xlsx).   -  person ycx    schedule 22.02.2019
comment
Вы можете настроить этот тип модели в Excel и использовать решатель для итерации до «лучшего» решения...   -  person Solar Mike    schedule 22.02.2019
comment
Взгляните на это для идеи: stackoverflow.com/a/53714432/4961700   -  person Solar Mike    schedule 22.02.2019
comment
@ycx Да, я знаю, что фреймы данных хороши для анализа листов Excel, и я также работаю с ними. Но как Dataframe может помочь мне (лучше) в этом случае решить мой вопрос? Есть ли специальная функция, которую я могу использовать с Dataframe?   -  person CaroW    schedule 25.02.2019
comment
@CaroW, вы можете использовать .groupby() и .transform() в библиотеке панд, а также совокупные суммы для достижения того, что вам нужно. Послесловия, это просто вопрос повторной перестановки их по исходному индексу. Попробуйте и отредактируйте свой вопрос с помощью некоторого кода, используя pandas, и я, вероятно, смогу работать оттуда, чтобы помочь вам в дальнейшем.   -  person ycx    schedule 25.02.2019

Ответы (1)


arrow_upward
1
arrow_downward

Я действительно решаю проблему сейчас. Я работаю со словарем, чтобы обновить значения в каждой строке (извините, нумерация столбцов теперь другая, но, надеюсь, метод становится понятным)

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]
person CaroW    schedule 11.03.2019