loop through column value to adjust max value to equal 100-CodePudding

'''
# initialize list of lists
data = {'Portfolio1': [20, 9.99, 20, 40, 10], 'Portfolio2': [20, 
40, 10.02, 20, 10], 'Portfolio3': [20, 40, 20, 9.98, 10], 
'Portfolio4': [20, 40, 20, 10.02, 10]}

df = pd.DataFrame(data)

# print dataframe.
df
'''

Output:

    Portfolio 1   Portfolio 2   Portfolio 3  Portfolio 4
    20.           20.           20.          20.                                                    
    9.99          40.           40.          40.  
    20.           10.02         20.          20.  
    40            20            9.98         10.02   
    10.           10.           10.          10.

The goal is to loop through the portfolio’s weights till they add up to exactly 100% is

1.) Identify security with max weight in the model

2.) If total is < 100%, then add 0.01% to security with max weight in model. If total is > 100% then subtract 0.01% to security with max weight in model.

3.) Check if the sum adds up to 100%. If yes, then stop. If no, then repeat.

4.) Need to also factor in the possibility that a change in the max value could create an equal weighting of a previously lesser value (i.e. max value of 40 changed to 39.99 is also now equal to an existing value in the pandas column that equals 39.99). Very rare edge case, which is why need to increase or decrease the value by 0.01 and continue to loop through the column to see if the updated max value now equals an existing value

Current Code=
max_allocation = df['Target Allocation'].idxmax()
allocation_sum = df['Target Allocation'].sum()
def weight_algo(df):
    for x in df['Target Allocation']:
        if allocation_sum > 100.0:
            max_allocation = df['Target Allocation'].max() - 0.01
        elif allocation_sum < 100:
            max_allocation = df.at[max_allocation]   0.01
        else:
            break

return df

weight_algo(data)

This function does not change the value of the max allocation but I get no error?

CodePudding user response：

Not too sure about your use of the key Target Allocation , but if your plan is to adjust the maximum holding weight till you have a 100% portfolio allocation, you can use the following code to run a loop on each column.

Must note that this will not work if your max weight has a decimal precision of > 2 decimals.

portfolios = df.columns

for p in portfolios:
    currSum = df[p].sum() 
    while currSum != 100:
        currMax = df[p].idxmax()
        if currSum < 100:
            df.loc[currMax, p]  = 0.01
        else:
            df.loc[currMax, p] -= 0.01
        currSum = df[p].sum()

The problem with the code that you've written is that you are not updating the original data frame, but increasing the index of the maximum row in a separate variable. This line

max_allocation = df.at[max_allocation] 0.01

Is incorrect especially because you are adding a fraction to an integer index. On another note you are only doing a single pass through the dataframe when actually it could take a few iterations before you converge to a solution.