Как рассчитать данные и добавить значения в строку и столбец с помощью Python
Ниже мой код Python, который вычисляет некоторые значения данных.
Моя проблема заключается в том, что все данные рассчитаны, но я хочу преобразовать минусовое значение в положительные значения.
При запуске вы получаете лист One excel. Затем вы можете проверить все данные результатов.
Программа размещена в нижеследующем предложении:
У меня 4 пользователя, и я хочу распределить новую ценность продукта (80,76) между этими 4 пользователями. Исходное значение продукта 63,28 и новое значение продукта (80,76), которое нельзя изменить, оно исправлено.
Пользователь может разрешить покупку или продажу товаров, когда значения опускаются до минус.
Но сумма openstock всех пользовательских продуктов не превышает 165.
Ниже мой сгенерированный снимок данных. Я хочу преобразовать отрицательное значение в положительное.
Последний столбец итоговый, не выше 165.
import xlsxwriter, random
user1_send_out = 5.31055842025857
other_send_out = 3.54037228017238
open_stock = 0
close_stock = 0
new_cargo = 0
sale = 0
purchase = 0
send_out = 0
def user_calculation(open_stock, new_cargo, sale, purchase, send_out):
close_stock = open_stock + new_cargo + purchase - send_out - sale
user_result = [open_stock, new_cargo, sale, purchase, send_out, close_stock]
return close_stock, user_result
day_avilable_cargo = { days:[8, 3, 3, 80.76] for days in range(1, 366)}
user1_open_stock, user2_open_stock, user3_open_stock, user4_open_stock = 0, 0, 0, 0
user1_Con = True
user2_Con = True
user3_Con = True
user4_Con = True
NewCargo = False
user1_res = []
user2_res = []
user3_res = []
user4_res = []
min_purchase_val = 4.5
min_close_stock = 4
for days in range(1, 366):
if user1_Con:
user1_new_cargo = 0
user1_sale = 0
user1_purchase = 14
user1_open_stock = 0
user1_data = user_calculation(user1_open_stock, user1_new_cargo, user1_sale, user1_purchase, user1_send_out)
user1_new_cargo = 0
user1_sale = 0
user1_purchase = 0
user1_open_stock = user1_data[0]
max_sale = {'user1_sale': user1_open_stock, 'user2_sale': user2_open_stock, 'user3_sale': user3_open_stock, 'user4_sale': user4_open_stock}
# if user1_open_stock < min_close_stock and NewCargo:
# user1_purchase = min_purchase_val
# max_sale_variable = max(max_sale, key=max_sale.get)
# if max_sale_variable == 'user2_sale':
# user2_sale = min_purchase_val
# elif max_sale_variable == 'user3_sale':
# user3_sale = min_purchase_val
# elif max_sale_variable == 'user4_sale':
# user4_sale = min_purchase_val
# print(user1_purchase)
user1_res.append(user1_data[1])
# print(user3_sale)
if user2_Con:
user2_new_cargo = 63.28
user2_sale = 42
user2_purchase = 0
user2_open_stock = 0
user2_data = user_calculation(user2_open_stock, user2_new_cargo, user2_sale, user2_purchase, other_send_out)
user2_new_cargo = 0
user2_sale = 0
user2_purchase = 0
user2_open_stock = user2_data[0]
max_sale = {'user1_sale': user1_open_stock, 'user2_sale': user2_open_stock, 'user3_sale': user3_open_stock, 'user4_sale': user4_open_stock}
# if user2_open_stock < min_close_stock and NewCargo:
# user2_purchase = min_purchase_val
# max_sale_variable = max(max_sale, key=max_sale.get)
# if max_sale_variable == 'user1_sale':
# user1_sale = min_purchase_val
# elif max_sale_variable == 'user3_sale':
# user3_sale = min_purchase_val
# elif max_sale_variable == 'user4_sale':
# user4_sale = min_purchase_val
user2_res.append(user2_data[1])
# print(user3_sale)
if user3_Con:
user3_new_cargo = 0
user3_sale = 0
user3_purchase = 14
user3_open_stock = 0
user3_data = user_calculation(user3_open_stock, user3_new_cargo, user3_sale, user3_purchase, other_send_out)
user3_new_cargo = 0
user3_sale = 0
user3_purchase = 0
user3_open_stock = user3_data[0]
max_sale = {'user1_sale': user1_open_stock, 'user2_sale': user2_open_stock, 'user3_sale': user3_open_stock, 'user4_sale': user4_open_stock}
# if user3_open_stock < min_close_stock and NewCargo:
# user3_purchase = min_purchase_val
# max_sale_variable = max(max_sale, key=max_sale.get)
# if max_sale_variable == 'user1_sale':
# user1_sale = min_purchase_val
# elif max_sale_variable == 'user2_sale':
# user2_sale = min_purchase_val
# elif max_sale_variable == 'user4_sale':
# user4_sale = min_purchase_val
user3_res.append(user3_data[1])
if user4_Con:
user4_new_cargo = 0
user4_sale = 0
user4_purchase = 14
user4_open_stock = 0
user4_data = user_calculation(user4_open_stock, user4_new_cargo, user4_sale, user4_purchase, other_send_out)
user4_new_cargo = 0
user4_sale = 0
user4_purchase = 0
user4_open_stock = user4_data[0]
# max_sale = {'user1_sale': user1_open_stock, 'user2_sale': user2_open_stock, 'user3_sale': user3_open_stock, 'user4_sale': user4_open_stock}
# if user4_open_stock < min_close_stock and NewCargo:
# print(user4_open_stock)
# user4_purchase = min_purchase_val
# max_sale_variable = max(max_sale, key=max_sale.get)
# if max_sale_variable == 'user1_sale':
# user1_sale = min_purchase_val
# elif max_sale_variable == 'user2_sale':
# user2_sale = min_purchase_val
# elif max_sale_variable == 'user3_sale':
# user3_sale = min_purchase_val
user4_res.append(user4_data[1])
if user1_send_out > user1_open_stock and (user1_open_stock + user2_open_stock + user3_open_stock + user4_open_stock < 90):
user1_new_cargo = day_avilable_cargo[days].pop()
user1_purchase = 0
if user2_open_stock < min_close_stock:
user2_purchase = day_avilable_cargo[days].pop()
if user3_open_stock < min_close_stock:
user3_purchase = day_avilable_cargo[days].pop()
if user4_open_stock < min_close_stock:
user4_purchase = day_avilable_cargo[days].pop()
user1_sale = user2_purchase + user3_purchase + user4_purchase
NewCargo = True
elif other_send_out > user2_open_stock and (user1_open_stock + user2_open_stock + user3_open_stock + user4_open_stock < 90):
user2_new_cargo = day_avilable_cargo[days].pop()
user2_purchase = 0
if user1_open_stock < min_close_stock:
user1_purchase = day_avilable_cargo[days].pop()
if user3_open_stock < min_close_stock:
user3_purchase = day_avilable_cargo[days].pop()
if user4_open_stock < min_close_stock:
user4_purchase = day_avilable_cargo[days].pop()
user2_sale = user3_purchase + user4_purchase + user1_purchase
NewCargo = True
elif other_send_out > user3_open_stock and (user1_open_stock + user2_open_stock + user3_open_stock + user4_open_stock < 90):
user3_new_cargo = day_avilable_cargo[days].pop()
user3_purchase = 0
if user4_open_stock < min_close_stock:
user4_purchase = day_avilable_cargo[days].pop()
if user1_open_stock < min_close_stock:
user1_purchase = day_avilable_cargo[days].pop()
if user2_open_stock < min_close_stock:
user2_purchase = day_avilable_cargo[days].pop()
user3_sale = user4_purchase + user1_purchase + user2_purchase
NewCargo = True
elif other_send_out > user4_open_stock and (user1_open_stock + user2_open_stock + user3_open_stock + user4_open_stock < 90):
user4_new_cargo = day_avilable_cargo[days].pop()
user4_purchase = 0
if user1_open_stock < min_close_stock:
user1_purchase = day_avilable_cargo[days].pop()
if user2_open_stock < min_close_stock:
user2_purchase = day_avilable_cargo[days].pop()
if user3_open_stock < min_close_stock:
user3_purchase = day_avilable_cargo[days].pop()
user4_sale = user1_purchase + user2_purchase + user3_purchase
NewCargo = True
else:
max_sale = {'user1_sale': user1_open_stock, 'user2_sale': user2_open_stock, 'user3_sale': user3_open_stock, 'user4_sale': user4_open_stock}
if user1_open_stock < min_close_stock and NewCargo:
min_purchase_val = abs(round(user1_open_stock))
user1_purchase = min_purchase_val
max_sale_variable = max(max_sale, key=max_sale.get)
if max_sale_variable == 'user2_sale':
user2_sale = min_purchase_val
elif max_sale_variable == 'user3_sale':
user3_sale = min_purchase_val
elif max_sale_variable == 'user4_sale':
user4_sale = min_purchase_val
print(user1_purchase)
elif user2_open_stock < min_close_stock and NewCargo:
min_purchase_val = abs(round(user2_open_stock))
user2_purchase = min_purchase_val
max_sale_variable = max(max_sale, key=max_sale.get)
if max_sale_variable == 'user1_sale':
user1_sale = min_purchase_val
elif max_sale_variable == 'user3_sale':
user3_sale = min_purchase_val
elif max_sale_variable == 'user4_sale':
user4_sale = min_purchase_val
elif user3_open_stock < min_close_stock and NewCargo:
min_purchase_val = abs(round(user3_open_stock))
user3_purchase = min_purchase_val
max_sale_variable = max(max_sale, key=max_sale.get)
if max_sale_variable == 'user1_sale':
user1_sale = min_purchase_val
elif max_sale_variable == 'user2_sale':
user2_sale = min_purchase_val
elif max_sale_variable == 'user4_sale':
user4_sale = min_purchase_val
elif user4_open_stock < min_close_stock and NewCargo:
min_purchase_val = abs(round(user4_open_stock))
print(user4_open_stock)
user4_purchase = min_purchase_val
max_sale_variable = max(max_sale, key=max_sale.get)
if max_sale_variable == 'user1_sale':
user1_sale = min_purchase_val
elif max_sale_variable == 'user2_sale':
user2_sale = min_purchase_val
elif max_sale_variable == 'user3_sale':
user3_sale = min_purchase_val
user1_Con = False
user2_Con = False
user3_Con = False
user4_Con = False
# print(user4_res)
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('NewMainData.xlsx')
worksheet = workbook.add_worksheet()
# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0
# Iterate over the data and write it out row by row.
for data1, data2, data3, data4, data5, data6 in (user1_res):
worksheet.write(row, col, data1)
worksheet.write(row, col + 1, data2)
worksheet.write(row, col + 2, data3)
worksheet.write(row, col + 3, data4)
worksheet.write(row, col + 4, data5)
worksheet.write(row, col + 5, data6)
row += 1
row = 0
for data1, data2, data3, data4, data5, data6 in (user2_res):
worksheet.write(row, col + 6, data1)
worksheet.write(row, col + 7, data2)
worksheet.write(row, col + 8, data3)
worksheet.write(row, col + 9, data4)
worksheet.write(row, col + 10, data5)
worksheet.write(row, col + 11, data6)
row += 1
row = 0
for data1, data2, data3, data4, data5, data6 in (user3_res):
worksheet.write(row, col + 12, data1)
worksheet.write(row, col + 13, data2)
worksheet.write(row, col + 14, data3)
worksheet.write(row, col + 15, data4)
worksheet.write(row, col + 16, data5)
worksheet.write(row, col + 17, data6)
row += 1
row = 0
for data1, data2, data3, data4, data5, data6 in (user4_res):
worksheet.write(row, col + 18, data1)
worksheet.write(row, col + 19, data2)
worksheet.write(row, col + 20, data3)
worksheet.write(row, col + 21, data4)
worksheet.write(row, col + 22, data5)
worksheet.write(row, col + 23, data6)
row += 1
# Write a total using a formula.
# worksheet.write(row, 0, 'Total')
# worksheet.write(row, 1, '=SUM(B1:B4)')
workbook.close()