如何在Python中按行调整DataFrame资产占比至符合限额

需求说明 #

已通过Python完成投资组合优化，得到资产占比结果DataFrame（每行数值和为1），另有各资产的限额DataFrame。需实现以下逻辑：

• 将超出限额的资产占比分配给未达限额的资产
• 完成上限调整后，检查资产占比下限，将低于下限的部分分配给仍有空间的资产
  现有实现该逻辑的R代码，需转为Python实现。

输入数据 #

资产占比DataFrame #

import pandas as pd

Column_names = ['Asset_1', 'Asset_2', 'Asset_3', 'Asset_4', 'Asset_5', 'Asset_6',
                'Asset_7', 'Asset_8', 'Asset_9', 'Asset_10']

List_values = [[0.1 , 0.  , 0.  , 0.  , 0.3 , 0.01, 0.01, 0.01, 0.02, 0.55],
               [0.1 , 0.  , 0.  , 0.02, 0.1 , 0.01, 0.02, 0.73, 0.02, 0.  ],
               [0.1 , 0.  , 0.  , 0.03, 0.1 , 0.02, 0.25, 0.  , 0.25, 0.25],
               [0.1 , 0.  , 0.  , 0.04, 0.  , 0.01, 0.  , 0.85, 0.  , 0.  ],
               [0.1 , 0.  , 0.  , 0.05, 0.  , 0.01, 0.  , 0.84, 0.  , 0.  ],
               [0.1 , 0.  , 0.  , 0.06, 0.  , 0.01, 0.6 , 0.  , 0.  , 0.23],
               [0.  , 0.  , 0.  , 0.07, 0.  , 0.01, 0.6 , 0.  , 0.32, 0.  ],
               [0.  , 0.  , 0.  , 0.08, 0.  , 0.01, 0.  , 0.  , 0.6 , 0.31],
               [0.  , 0.  , 0.  , 0.09, 0.  , 0.01, 0.  , 0.6 , 0.  , 0.3 ]]

portfolio_df = pd.DataFrame(data=List_values, columns=Column_names)

资产限额DataFrame #

limits_df = pd.DataFrame({
    'Asset_1': [0.2], 'Asset_2': [0.2], 'Asset_3': [0.2], 'Asset_4': [0.2],
    'Asset_5': [0.2], 'Asset_6': [0.1], 'Asset_7': [0.3], 'Asset_8': [0.45],
    'Asset_9': [0.15], 'Asset_10': [0.3]
})
# 转为Series方便处理
limits = limits_df.iloc[0]

期望输出 #

Desired_List_values = [
    [0.08, 0.  , 0.  , 0.  , 0.  , 0.45, 0.  , 0.  , 0.3 , 0.15],
    [0.  , 0.  , 0.  , 0.07, 0.  , 0.45, 0.  , 0.  , 0.3 , 0.15],
    [0.  , 0.  , 0.  , 0.07, 0.  , 0.45, 0.  , 0.  , 0.3 , 0.15],
    [0.  , 0.  , 0.  , 0.07, 0.  , 0.45, 0.  , 0.  , 0.3 , 0.15],
    [0.  , 0.  , 0.  , 0.07, 0.  , 0.45, 0.  , 0.  , 0.3 , 0.15],
    [0.  , 0.  , 0.  , 0.09, 0.  , 0.44, 0.  , 0.  , 0.3 , 0.15],
    [0.  , 0.  , 0.  , 0.1 , 0.  , 0.43, 0.  , 0.  , 0.3 , 0.15]
]

desired_df = pd.DataFrame(data=Desired_List_values, columns=Column_names)

注：原期望输出中的第一列序号为额外添加，此处已移除，仅保留资产占比数据。

原R代码 #

mpShareDistrib <- function(x, l = 1, m = 1, dtz = F){

  # 0. Check inputs
  ss <- as.numeric(x)
  names(ss) <- names(x)
  ss <- na.omit(ss)

  l <- as.numeric(l)

  if(length(l) == 1) l <- rep(l, times = length(ss))
  if(length(l) != length(ss)) stop('Shares and limit vectors are not equal')

  m <- as.numeric(m)

  if(is.null(names(ss))) names(ss) <- paste0('sec', 1:length(ss))
  if(is.null(names(l))) names(l) <- names(ss)
  if(length(setdiff(names(ss), names(l))) > 0)
    stop('Shares and limit have different names for:', setdiff(names(ss), names(l)) )

  Cut_Share <- function(x, l, m){

    ss1 <- x

    ss1 <- ss1[order(names(ss1))]
    l1  <- l[names(ss1)]

    i <- 1
    while(max(ss1-l1)>0 & i < 10){

      # (above limit stock shares)
      abv_ss <- ss1[ss1>0] - l1[ss1>0]
      abv_ss <- abv_ss[abv_ss > 0]
      abv_ss <- sum(abv_ss)

      new1_ss <- ss1[which((ss1 - l1)>0)]
      new1_l  <- l[names(new1_ss)]
      new1_ss <- new1_ss - (new1_ss - new1_l)

      # (free stock stares)
      free_ss <- ss1[ss1 < l1]

      # (filled stock shares)
      fld_ss <- free_ss + abv_ss * free_ss / sum(free_ss)

 
      new1_ss <- c(new1_ss, ss1[ss1 == l1], fld_ss)

      ss1 <- new1_ss
      ss1 <- round(ss1, digits = 4)
      ss1 <- ss1[order(names(ss1))]

      i <- i + 1
    }

    return(ss1)
  }

  ss1 <- Cut_Share(ss[ss>0], l, m)

  ss2 <- ss1[ss1 > 0]  
  l2  <- l[names(ss2)]

  ss2 <- ss2[order(names(ss2))]
  l2  <- l2[order(names(l2))]

  i <- 1

  while(min(ss2) < m & i < 10){

    min_ss <- ss2[which.min(ss2)]

    free_ss2 <- ss2[-which.min(ss2)]
    free_l2 <- l2[names(free_ss2)]

    free_ss2 <- free_ss2[free_ss2 < free_l2]

    if(length(free_ss2) > 0)
    {

      free_l2 <- free_l2[names(free_ss2)]

      dstr <- free_ss2 / sum(free_ss2) * min_ss

      fld_ss  <- pmin(free_ss2 + dstr, free_l2)

      rem <- min_ss - sum(fld_ss - free_ss2)
      rem <- round(rem, digits = 4)
      names(rem) <- names(min_ss)

      if(rem > 0)
        new2_ss <- c(ss2[ss2 >= l2], fld_ss, rem)
      else
        new2_ss <- c(ss2[ss2 >= l2], fld_ss)

    } else {

      warning('Cannot distribute small share - no free space ')
      break()
    }

    ss2 <- new2_ss
    l2  <- l2[names(new2_ss)]
    i <- i + 1

  }

  # 3. Cut shares above limit
  #
  ss3 <- ss2

  # 4. Finalization
  #
  # Add distributed share with zero value
  {
    lngth_diff <- length(ss) - length(ss3)

    ss0 <- c()
    if(lngth_diff > 0){

      ss0 <- rep(0, lngth_diff)

      rmvd_ss <- setdiff(names(ss), names(ss3))
      names(ss0) <- rmvd_ss
    }

    ss_fin <- c(ss0, ss3)
    ss_fin <- ss_fin[order(names(ss_fin))]

    if(is.null(names(ss))) names(ss_fin) <- NULL

  }

  if(sum(ss) != sum(ss_fin)) warning('Shares distribution error')

  return(ss_fin)

}

Python实现代码 #

import pandas as pd
import numpy as np

def mp_share_distrib(x, limits, min_limit=1):
    # 输入处理：转为Series，保留非空值
    ss = pd.Series(x).dropna()
    ss = ss[ss > 0]  # 仅保留正占比资产
    limits = pd.Series(limits)
    
    # 检查输入长度匹配
    if len(limits) == 1:
        limits = pd.Series([limits.iloc[0]] * len(ss), index=ss.index)
    if len(ss) != len(limits):
        raise ValueError("Shares and limit vectors are not equal")
    
    # 确保索引一致
    if not ss.index.equals(limits.index):
        missing = ss.index.difference(limits.index)
        raise ValueError(f"Shares and limit have different names for: {list(missing)}")
    
    def cut_share(ss_input, limits_input):
        ss1 = ss_input.copy().sort_index()
        l1 = limits_input.loc[ss1.index].sort_index()
        
        i = 1
        while (ss1 - l1).max() > 0 and i < 10:
            # 计算超出限额的总金额
            abv_mask = (ss1 > 0) & (ss1 > l1)
            abv_ss = (ss1[abv_mask] - l1[abv_mask]).sum()
            
            # 超出部分截断到限额
            new1_ss = ss1[abv_mask].copy()
            new1_ss = l1[abv_mask]
            
            # 未达限额的资产
            free_mask = ss1 < l1
            free_ss = ss1[free_mask]
            
            # 分配超出部分：按当前占比权重分配
            if free_ss.sum() > 0:
                fld_ss = free_ss + abv_ss * (free_ss / free_ss.sum())
            else:
                fld_ss = free_ss
            
            # 合并所有资产：截断后的、刚好达限额的、分配后的
            exact_mask = ss1 == l1
            new1_ss = pd.concat([new1_ss, ss1[exact_mask], fld_ss]).sort_index()
            ss1 = new1_ss.round(4)
            
            i += 1
        return ss1
    
    # 第一步：处理上限超额分配
    ss1 = cut_share(ss, limits)
    
    # 第二步：处理下限不足分配
    ss2 = ss1[ss1 > 0].sort_index()
    l2 = limits.loc[ss2.index].sort_index()
    
    i = 1
    while ss2.min() < min_limit and i < 10:
        min_idx = ss2.idxmin()
        min_val = ss2[min_idx]
        
        # 排除当前最小的资产，找仍有空间的资产
        free_ss2 = ss2.drop(min_idx)
        free_l2 = l2.drop(min_idx)
        free_mask = free_ss2 < free_l2
        free_ss2 = free_ss2[free_mask]
        free_l2 = free_l2[free_mask]
        
        if len(free_ss2) == 0:
            print("Warning: Cannot distribute small share - no free space")
            break
        
        # 按当前占比权重分配最小部分
        total_free = free_ss2.sum()
        dstr = free_ss2 / total_free * min_val
        fld_ss = pd.Series(np.minimum(free_ss2 + dstr, free_l2), index=free_ss2.index)
        
        # 计算剩余未分配的部分
        rem = min_val - (fld_ss - free_ss2).sum()
        rem = round(rem, 4)
        
        # 合并结果：已达限额的、分配后的、剩余部分（如果有）
        exact_mask = ss2 >= l2
        new2_ss = pd.concat([ss2[exact_mask], fld_ss])
        if rem > 0:
            new2_ss = pd.concat([new2_ss, pd.Series([rem], index=[min_idx])])
        
        ss2 = new2_ss.sort_index()
        l2 = limits.loc[ss2.index].sort_index()
        i += 1
    
    # 第三步：补回零占比资产
    all_assets = pd.Series(0, index=x.index)
    all_assets.loc[ss2.index] = ss2
    ss_fin = all_assets.round(4)
    
    # 检查总和是否一致
    if not np.isclose(ss.sum(), ss_fin.sum(), atol=1e-4):
        print("Warning: Shares distribution error")
    
    return ss_fin

# 应用到整个DataFrame
def apply_portfolio_adjustment(portfolio_df, limits):
    adjusted_rows = []
    for idx, row in portfolio_df.iterrows():
        adjusted = mp_share_distrib(row, limits, min_limit=0)  # 此处min_limit根据需求调整，原R代码默认是1
        adjusted_rows.append(adjusted.values)
    return pd.DataFrame(adjusted_rows, columns=portfolio_df.columns)

# 执行调整
adjusted_portfolio = apply_portfolio_adjustment(portfolio_df, limits)
print(adjusted_portfolio)

说明 #

• 原R代码中m参数为下限阈值，默认值为1，实际应用中需根据业务需求调整（例如如果下限是0，则传入min_limit=0）
• 代码中保留了原R的迭代次数限制（最多10次），避免无限循环
• 所有计算保留4位小数，与原R代码一致
• 处理过程中严格保持资产索引一致，避免匹配错误

内容的提问来源于stack exchange，提问作者Olsen_I