根据加权平均标准优化贷款组合 [英] Optimizing A Loan Portfolio On Weighted Average Criteria
问题描述
我意识到我以前尝试描述问题的方法并没有很大帮助，并且实际上并没有很好地模仿我目前正在做的事情，所以我重写了这篇文章.我已经包含了我的工作代码.我将在示例中使用的数据是Lending Club贷款数据(csv格式)，可以从此处下载:
I realised that my previous attempt at a way to describe the problem wasn't very helpful and indeed didnt actually model what I'm currently doing at the moment very well, so I've rewritten the post. I've included my working code. The data in the example I will use is the lending club loan data (csv format), which can be downloaded from here: https://www.kaggle.com/wendykan/lending-club-loan-data
我目前正在使用python中的PuLP和pandas解决我目前遇到的优化问题.我是线性编程的新手，最近利用它来解决有关最小化成本问题的问题.
I'm currently utilising PuLP and pandas in python to solve an optimization issue I have at the moment. I am quite new to linear programming, and utilised it recently to solve issues around minimising cost problems.
对于这个贷款俱乐部的例子，假设我有100,000美元.鉴于俱乐部俱乐部贷款是证券化的，并且可以由个人投资，所以我想分发这100,000美元，以便:
For this lending club example, lets say I have $100,000. Given that lending club loans are securitised and can be invested in by individuals, I would like to distribute the $100,000 such that:
1..投资的100,000美元的加权平均利率不能低于8％.
1. the weighted average interest rate of the invested $100,000 cannot be below 8%.
2..所投资的100,000美元的加权平均债务与收入之比不能超过10
2. The weighted average debt-to-income ratio of the invested $100,000 cannot exceed 10
立即忽略(( 3..)优化的投资组合不能超过10,000美元.)
Ignore for now (3. To minimise exposure to any one loan, the average loan size of the optimised portfolio cannot exceed $10,000.)
我在实施约束3和我的100,000美元投资上限的约束时遇到困难.对于约束1&2，我已经按贷款规模对贷款进行了加权，然后将利率和债务收入比率乘以权重，从而可以以线性方式对这些约束进行建模，使得:
I am having difficulties implementing constraint 3 and the constraint of my investment ceiling of $100,000. For constraints 1 & 2, I've weighted loans by loan size and multiplied the interest rate and Debt-income ratio by the weights, and can thus model those constraints in a linear fashion such that:
Sum of weighted interest rates => 0.08
Sum of weighted debt-income ratios <= 10
购买的贷款的最终投资组合不需要完全等于100,000美元.目标函数是在约束即LpMaximise之内达到接近100,000.
The final portfolio of loans purchased do not need to exactly equal $100,000. The objective function is to get to as close to 100,000 within the constraints i.e. LpMaximise.
我选择将贷款选择建模为一个二进制变量，因为我只想知道它们是进还是出.此外，出于速度和内存的考虑，我从数据中选择了一个50行的切片.
I've chosen to model the selection of loans as a binary variable, as I would simply like to know if they are in or out. Furthermore, for the sake of speed and memory, I've chosen a slice of 50 rows from the data to work with.
这是我的代码:
import pandas as pd
from pulp import *
import numpy as np
df = pd.read_csv('~/Desktop/loan.csv')
df.columns = [c.replace(' ','') for c in df.columns]
#cleaning up the data to get rid of spaces and to standardise percentage data
df.loc[:,'id'] = df.loc[:,'id'].astype(int)
df['int_rate'] = df['int_rate']/100 #convert interest rate to ratios.
#slicing the data to get a small sample of 50 loans
df = df.iloc[0:49,:]
#setting up the weighted averages for linear equations
sumloans = df.loc[:,'funded_amnt'].sum()
df['weights'] = df['funded_amnt'].div(sumloans,axis='index')
#Converting dataframe to weighted values!
df2 = df[["id","funded_amnt","dti","int_rate"]]
df2[["funded_amntwtd","dtiwtd","int_ratewtd"]] = df[["funded_amnt","dti","int_rate"]].multiply(df["weights"],axis="index")
df3 = pd.merge(df,df2.iloc[:,[4,5,6]],on=df["id"],how='left')
#Free up memory
df = None
df2 = None
#Variable construction
loanid = df3['id'].tolist()
dtiwtd = df3.set_index('id').to_dict()['dtiwtd']
loanmix = df3.set_index('id').to_dict()['funded_amnt']
wtdloanmix = df3.set_index('id').to_dict()['funded_amntwtd']
wa_int = df3.set_index('id').to_dict()['int_ratewtd']
id_vars = LpVariable.dicts("ID",indexs=loanid, cat='Integer',lowBound=0, upBound=1)
#Objective function added first. Summing all the loan values but examining constraints
prob = LpProblem("Funding",pulp.LpMaximize)
prob += lpSum([loanmix[i]*id_vars[i] for i in id_vars])
prob += lpSum([loanmix[i]*id_vars[i] for i in id_vars]) <= 100000 #"Sum of loans purchased must be equal to or less than $100,000"
prob += lpSum([dtiwtd[i]*id_vars[i] for i in id_vars]) <= 10 #"Sum of weighted dtis cannot be greater than 10"
prob += lpSum([wa_int[i]*id_vars[i] for i in id_vars]) >= 0.08 #"Sum of weighted interest rates cannot be less than 8%"
#Placeholder for inserting constraint on avg. loan size
prob.solve()
print("Status:", pulp.LpStatus[prob.status])
for v in prob.variables():
print(v.name, "=", v.varValue)
print("Total amount invested = ", value(prob.objective))
解决方案状态显示为不可行"，并且输出中包含一些非二进制整数.
The solution status comes out as 'Unfeasible' and the output has some non binary integers.
在此问题上的任何帮助，我将不胜感激.我是线性代数和高等数学的新手，但我已阅读此页( http://lpsolve.sourceforge.net/5.1/ratio.htm )帮助我设置了前两个约束.我只是停留在如何编写方程式或代码上，以确保优化后的投资组合的平均贷款价值低于10,000美元.
I would appreciate any help around this matter. I'm a newbie to linear algebra and higher mathematics, but i have read this page (http://lpsolve.sourceforge.net/5.1/ratio.htm) which helped me set up the first two constraints. I'm just stuck on how to write an equation or code that would ensure the optimized portfolio has a average loan value of less than $10,000.
推荐答案
您正在对整数进行线性编程，这比使用实数(双精度)更麻烦.由于您的金额可以视为两倍，因此请减少整数.
You are doing linear programming on integers, which is more troublesome than with reals (doubles). Since your amounts can be treated as doubles, drop the ints.
id_vars是否应该容纳金额或比例(加1)?如果是后者，则您需要对其总和施加额外约束.(如果设置了二进制数，则意味着一个1和n-1个零.)如果它们是数量，则您的加权平均值不是平均值，例如按目前的情况，您要确保利率的总和(不是平均水平)至少为8％，如果您以8％的贷款利率为1％，就可以实现这一目标.
Is id_vars supposed to hold amounts or proportions (adding to 1)? If the latter then you need an extra constraint on their sum. (And with your binary set up it would mean a single one and n-1 zeroes.) And if they are amounts then your weighted averages are not averages, e.g. as it stands you ensure that the sum (not average) of interest rates is at least 8%, which you can achieve with 8 loans at 1%.
也:如果我正确理解了语法(我对LP很熟悉，但对您的库不熟悉)，则您正在尝试使投资金额最大化，但前提是该投资金额至少为100000.这是意外的:为什么不最大化回报?
Also: If I understand the syntax correctly (I am familiar with LP but not with your library), you are trying to maximize the investment amount, under constraint that it be at least 100000. This is unexpected: why not maximize return?
约束2平均债务收入比不能超过10"在数学上是错误的.如果您有两项投资，债务分别为d1和d2，收入为i1和i2，且比例为a和1-a，则该批次的债务收入比为[a * d1 +(1-a)
Constraint 2, "average debt-to-income ratio cannot exceed 10", is mathematically incorrect. If you have 2 investments with debts d1 and d2, income i1 and i2, with proportions a and 1-a, the debt-to-income ratio of the lot is [a*d1 + (1-a)d2] / [ai1 + (1-a)i2], not ad1/i1 + (1-a)*d2/i2.
约束3可以更好地实现为一系列约束(如注释中所暗示):对于所有i，loanmix [i]< = 0.1.
Constraint 3 would be better implemented as a series of constraints (as hinted at in the comments): loanmix[i] <= 0.1 for all i.
最低贷款要求不是线性的(线性编程中的大忌讳).实际上，它们的形式为:对于所有i，loanmix [i] == 0或loanmix [i]> = minloan [i].骇客可能要离散化:使用$ 1000的增量代替$ 1000的最小值.但是随后您又回到了Integerland.
Minimum loan requirements are not linear (big taboo in linear programming). Indeed, they are of the form: for all i, loanmix[i] == 0 or loanmix[i] >= minloan[i]. A hack may be to discretize: instead of min of $1000, use increments of $1000. But then you are back to Integerland.
这篇关于根据加权平均标准优化贷款组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
