我需要弄清楚有积压的公司和每种产品库存不足的公司。我想显示每个公司可以给予多少股票给另一家公司，然后显示更新后的股票。

import pandas as pd
import numpy as np

data = pd.read_excel("DummyData.xlsx")

df = pd.DataFrame(data)

numeric_columns = ['Stock', 'Orders', 'Production']
df[numeric_columns] = df[numeric_columns].apply(pd.to_numeric, errors='coerce')

df['Stock'] = df['Stock'].fillna(0)
df['Orders'] = df['Orders'].fillna(0)
df['Production'] = df['Production'].fillna(0)
df['Stock-Orders'] = df['Stock'] - df['Orders']
df['Over/Understock'] = df['Stock-Orders'] + df['Production']
df['Stock needed'] = df['Over/Understock'].apply(lambda x: -x if x < 0 else 0)

df = df.sort_values(by=['Product', 'Over/Understock'], ascending=[True, False])
df['Cumulative Overstock'] = df.groupby('Product')['Over/Understock'].apply(lambda x: x.where(x > 0).cumsum().ffill())
df = df.sort_values(by=['Product', 'Over/Understock'], ascending=[True, True])
df['Cumulative Understock'] = df.groupby('Product')['Over/Understock'].apply(lambda x: x.where(x < 0).cumsum().ffill())
df = df.sort_values(by=['Product', 'Over/Understock'], ascending=[True, False])
df['Cumulative Stock'] = df.groupby('Product')['Over/Understock'].apply(lambda x: x.cumsum())

df['Cumulative Overstock'] = df['Cumulative Overstock'].fillna(0)
df['Cumulative Understock'] = df['Cumulative Understock'].fillna(0)
df['Remaining Cumulative Stock'] = df['Cumulative Stock'].fillna(0)

# Adding a 'Rank' column based on the sorted order within each product group
df['Rank'] = df.groupby('Product').cumcount() + 1

df['Status'] = df.apply(
    lambda row: 'Enough' if row['Stock needed'] <= row['Cumulative Overstock'] else 'Not enough',
    axis=1
)

df = df[['Product', 'Company Name', 'Stock', 'Orders', 'Production', 'Stock-Orders', 'Over/Understock',
         'Rank', 'Stock needed', 'Cumulative Overstock', 'Cumulative Understock', 'Remaining Cumulative Stock', 'Status']]

# Sort the DataFrame by Company Name and Cumulative Understock in descending order
df = df.sort_values(by=['Product', 'Rank'], ascending=[True, True])

# Add columns for Giving Companies
unique_companies = df['Company Name'].unique()
giving_columns = [f'Allocation Comp {i + 1}' for i in range(len(unique_companies))]
allocated_columns = [f'Allocated Stock {i + 1}' for i in range(len(unique_companies))]
df[giving_columns] = ''
df[allocated_columns] = 0  # Initialize allocated values to 0

# Function to find giving companies and their allocated values
def find_giving_companies(row):
    if row['Stock needed'] <= 0:
        return [('', 0)] * len(unique_companies)

    giving_companies = []
    remaining_stock_needed = row['Stock needed']

    # Iterate through ranks to find giving companies
    for rank in range(1, row['Rank'] + 1):
        giving_company_row = df[(df['Product'] == row['Product']) & (df['Rank'] == rank)]
        
        if not giving_company_row.empty:
            giving_company = giving_company_row['Company Name'].values
            available_stock = giving_company_row['Over/Understock'].values

            if giving_company.size > 0 and available_stock.size > 0 and giving_company[0] != row['Company Name'] and available_stock[0] > 0:
                # Check if this giving company can cover all remaining stock needed
                if available_stock[0] >= remaining_stock_needed:
                    giving_companies.append((giving_company[0], remaining_stock_needed))
                    remaining_stock_needed = 0
                    break
                else:
                    giving_companies.append((giving_company[0], available_stock[0]))
                    remaining_stock_needed -= available_stock[0]

    # Pad the list with empty strings and zeros if needed
    giving_companies.extend([('', 0)] * (len(unique_companies) - len(giving_companies)))

    return giving_companies[:len(unique_companies)]

# Lists to store giving companies and their allocated values
giving_company_list = []

# Apply the function to each row
for i, row in df.iterrows():
    giving_companies = find_giving_companies(row)
    giving_company_list.append(giving_companies)

# Update the DataFrame with giving companies and their allocated values
for i, comp_list in enumerate(giving_company_list):
    for j, (comp, value) in enumerate(comp_list):
        allocated_stock_col = f'Allocated Stock {j + 1}'
        allocation_comp_col = f'Allocation Comp {j + 1}'

        if value < 0 and comp in df['Company Name'].values:
            # Set negative value for giving company in their row
            giving_company_row = df[(df['Product'] == df.at[i, 'Product']) & (df['Company Name'] == comp)].index
            if not giving_company_row.empty:
                df.at[giving_company_row[0], allocated_stock_col] = value

        # Set positive value for receiving company
        df.at[i, allocated_stock_col] = value
        df.at[i, allocation_comp_col] = comp
        df.at[i, f'Allocated Stock {j + 1}'] = value  # Set negative value for giving company

df['Stock needed after Allocation'] = df.apply(lambda row: row['Stock needed'] - row[allocated_columns].sum() if row['Status'] == 'Not enough' else 0, axis=1)
df['Stock needed after Allocation'] = df.apply(lambda row: -row['Stock needed after Allocation'] if row['Stock needed after Allocation'] > 0 else 0, axis=1)

df

enter image description here这是我创建的Python代码和输出表。
我知道要包括的是一个更新的新的过剩/库存不足列。假设公司B首先将库存赠送给公司A，然后我想更新新的“库存过剩/不足”列，以便公司B的库存比以前少。我现在想使用这个更新的列为下一个需要股票的公司。我想用等级来区分每一列的优先级。因此，排名越高，他们从其他公司获得的股票就越多。
1.超越等级去寻找给予的公司
1.检查给予公司是否有足够的库存
1.检查该给予公司是否可以覆盖所有剩余库存4.，计算新的超额/不足价值
1.使用此新的高/低值检查公司是否仍有足够的库存
1.检查下一家公司的库存是否足够
直到每种产品的库存尽可能地分配。
我尝试了很多方法，但似乎都不起作用。我想它不起作用，因为我需要在给予公司的行分配的值，以及，但我不设法做到这一点..
我希望我的意图是明确的-如果没有随时问：）