4 k g( ^: v8 G( z/ g. I0 o) z8 H5 A7 ?9 ?& Q: V) Y) h1 o4 l
下面的一，二问实际都在解决; Q8 t3 A) I2 x- o
1 L# i& j6 [" n+ W- U% K& K3 L; S6 W0 A
贷不贷款？ 6 b+ E5 A8 d F5 u6 I贷款金额多少？ 7 @- q5 [& t- V数据清洗" Z1 R* W7 F! o$ u( l. O
这道题的附件数据没有出现缺省或者异常数据，因此对于数据的预处理，更多的是根据问题的需求来做的。: r4 E! T; [# Y
; ~; L, s: i% y. ?( M; O, U4 C- g
将是否违约，违约设置为1，不违约设置为0 0 S) f; f }# S; u/ L7 b- o( O* ?9 l. g' t
/ Q3 _' N( a. r# h2 Y
信誉等级ABCD分别对应4,3,2,1 $ C* v! i, |9 p+ J2 b2 v; @9 c( a) f/ a; D* |3 s" n6 W* a$ k# y
3 y- {; ]# c( ?; c- w发票状态，有效发票为a，作废发票为b 6 H1 R! I- `( a O5 w% `, T2 F A * p* i" N4 W5 o* X & s( W( W+ l. N; L6 Z3 P我将销项和进项所有数据，以公司代码为区别，提取到了不同的sheet当中，对于该公司有效发票数，作废发票数，负数发票数，方便对数据观察。" U3 {0 ~ D4 U7 ]$ t
- n5 r/ l8 ]: v- @2 d6 K
/ q2 T6 v0 T3 c# 遍历所有sheet数据3 N5 e4 e- B; ^8 s' Q' F) k
for xsn in sn.sheet_names[1:]:5 h0 h H2 R8 g, s* {2 \- B
# 读取文件 8 B0 Q# K. |8 B' \7 ^" o# w datas = pd.read_excel(file_pos, sheet_name=xsn)/ l9 y4 x. y' }# @" P
datas['date']=pd.to_datetime(datas['date'],format='%Y/%m/%d') . F; B6 u) X* x( t! j0 _0 A datas.set_index('date', drop=True) $ H: F9 a; @2 J; `/ v. `' ` # 找到全部公司名称代号 , A0 a3 o# v) E# l9 Y' o code_list = list(set(list((datas['code']))))' g7 F, d% P* |
for name in code_list:" s! `7 C0 U" p1 j8 A. H. @; h" ~
tmp_datas = datas[datas['code'] == name]1 g0 Z' V: E4 g, ^+ H# m
tmp_datas.index = range(len(tmp_datas))- S5 E& b7 ?0 G
# 转换日期未object类型 Z6 b* H$ ?& l9 ~: l: n: a
tmp_datas['date'] = [x.strftime('%Y/%m/%d') for x in tmp_datas['date']] % `& h; ?) F+ N9 e: E count1 = tmp_datas['tax_status'].value_counts(); k* k8 s, r+ a. z
tmp_datas['a_count'] = list(count1)[0] {& D% L: D, n- [ if(len(count1) > 1):- h; N4 h, E i5 L
tmp_datas['b_count'] = list(count1)[1] " N! E# M+ D6 O4 V tmp2 = tmp_datas[tmp_datas['cost'] < 0]8 u9 g: Y& {. ^6 O1 X
tmp_datas['neg_value_tax'] = len(tmp2)$ z; p6 ~* L+ y7 L7 |
if xsn == sn.sheet_names[1]:. ~+ g7 c+ `* @, r r* _
tmp_datas.to_excel(writer1,sheet_name=name,index=False)6 u( f3 {& c) a2 p$ n
else: : q* p% ~. l3 { tmp_datas.to_excel(writer2,sheet_name=name,index=False) " j6 x7 ?* u) j负数发票：在之前购买的物品，并开具了相关正向发票，后来退货所以开具了值为负数的发票，抵消前面正数发票的值。- v, i9 w& z; @3 }3 k) G1 ~' w- n6 j8 |
. l/ g" v9 Z( @; d- n! }
8 b2 o$ V& E' n- U' M+ H) o8 H
提取到信息： ; g& X' T' T* ]- e: x' z- `8 `$ n + U$ x# j% e9 `' D% M* W2 X2 u; S- V, F/ x! ^' B
部分公司数据记录很少，或者时间跨度大，需要综合数据指标，抵消数据数量和跨度大的影响$ g; o$ P( G9 N0 b* R
有些负数发票，在之前找不到对应的正数发票，可能是因为在数据记录日期之前购买的，在之后退款，因此在附件中找不到记录。( U& c ?2 m: P
问题一 9 S4 l/ r+ K0 z% { k建立指标 Q1 G3 d2 k' C: p5 q
进项发票作废率，进项负数发票率，进项每月平均交易额，进项每月交易次数， " k }. r, x8 f3 A; D4 _ 2 E/ Q& c- [+ L4 K9 }3 P/ @# a5 O. C! T$ H# ]7 z% [; |$ T
销项发票作废率，销项负数发票率，销项每月平均交易额，销项每月交易次数，销售收入增长率7 i3 f# r3 Y9 S' l- d
) D" m p' w S A! Y9 d) A" E ( ?( ?. s J& r" Z提取出相关指标到附件 & m! A% T! Y( l% d: F. i: M/ i# l 5 X4 K0 f' _7 Z& ?; Y: m / a% ?4 k6 H: e7 x* d3 ifor xsn in sn.sheet_names[1:]: : x7 r k _; W5 e # 读取文件 ) `- V; E' {/ y datas = pd.read_excel(file_pos, sheet_name=xsn)9 p2 e, l# q1 `' X5 U
code_list = list(set(list((datas['code'])))) $ v% I k4 c% O/ q" C: e4 i/ w3 j for name in code_list: + w; @5 X# e. G- E- M, g tmp_datas = datas[datas['code'] == name]' T# X0 ~+ ]8 `9 s A
tmp_datas.index = range(len(tmp_datas)) ; C/ r. e" p& w insert_datas.append(name) ! E2 r- r8 G/ p* I( s9 q # 作废数8 d/ P* u4 b% s& h! ?! u, z3 r
cacel_count = len(tmp_datas[tmp_datas['tax_status'] == 'b'])* f# J' @/ U3 W1 ~) w
# 有效数 % u4 m4 U6 ?8 y+ n valid_count = len(tmp_datas[tmp_datas['tax_status'] == 'a'])# ^3 p8 `/ U, P; D4 c5 Y- n
# 发票作废率 ( h, l( i4 I m count1 = (cacel_count / (cacel_count + valid_count))*100; h: e+ {5 W, m$ _
# 负数发票数; p" S5 y/ l* J- C: R
neg_count = len(tmp_datas[tmp_datas['cost'] < 0])& k7 M8 P/ z" ~) u! p: k2 s7 D
# 负数发票率' z6 |. e# Q, u$ W& {) h+ W, B
count2 = (neg_count / valid_count) * 100# [/ X, H, V, M" o8 {
# 转换时间 1 w1 g; j- b2 N- n! m; c' t tmp_datas['date'] = [x.strftime('%Y/%m/%d') for x in tmp_datas['date']] 4 Q" p7 h5 i& H6 n # 时间最大值 6 E, [# f/ N1 z. k max_time = tmp_datas.iloc[0:,1].max() . l+ `# b# H- h; U! N) N # 时间最小值( c5 m- z+ E2 n6 M! D
min_time = tmp_datas.iloc[0:,1].min() 8 m2 q3 d- q" k! e, @' c( R # 时间差! c3 ]$ m; ]$ H) f, u
diff_time = months(max_time, min_time) + 1 ! x2 D: A' b# X4 S3 z7 S # 有效票 . C7 i0 W0 j& J: C; L8 r valid_tax = tmp_datas[tmp_datas['tax_status'] == 'a'] Q/ S$ u5 Y& v: V9 w9 Q2 |+ q # 平均月交易额1 o, ~, q8 F T
avg_money = valid_tax['totle_cost'].sum() / diff_time $ v/ I! v" d/ o* |9 ~, { # 平均每月交易次数1 b- L/ D, ?" P, N( c ^8 @& I
trans_count = len(tmp_datas) / diff_time 2 P) ?& i5 p e- }* J- z insert_datas += [count1, count2, avg_money, trans_count,] \& _. V8 M0 ]+ w if flag:# f8 ~# z2 {# f5 J V7 M0 ^
df1.loc[len(df1)] = insert_datas* b# A* C& T% r! [& v, D
df1.to_excel(writer1,sheet_name='进项信息',index=False) 9 S* @* u* o5 m& O# @6 l# |1 H else:& D+ h, M' q5 w' I ?
merge_time = tmp_datas.groupby(tmp_datas['date']).sum() 8 k; }$ h7 Y" ?4 O9 c7 |2 t/ Y p # 销售收入增长率 ' L( D$ L1 V {* ]1 Q# | income_info= list((merge_time['cost'] - merge_time['cost'].shift(1)).fillna(1)) 3 k/ J# q# p* u g; k diff_time_day = days(max_time,min_time): Y" b. V# s; l- Z( @6 X. T
income_tax = (sum(income_info) / diff_time_day)*100 5 }# `7 a) Z1 _! s* k: m insert_datas.append(income_tax)" k5 V% ?" e: W, S) V8 p
df2.loc[len(df2)] = insert_datas4 z& d. \4 w9 u4 `0 h& ~
df2.to_excel(writer1,sheet_name='销项信息',index=False) 9 U* l: N) b" d* [; f5 }* d insert_datas = []6 d: t" `) n# P: w. m2 V
flag = False 3 q# J8 M+ |1 S" t* H) z v % S7 S3 ]2 e( k, B" P% r1 d, Y: v ( ?, S- a& g2 u, z, I . x. _( i/ b% ?' C, r, i. l" O% q0 o! o; H$ r
并将是否违约插入到最后一列) I% a7 v# R i3 b$ G* s1 `" N
?0 U0 E/ Y9 G
+ h% h6 g3 f! n8 `) t! e1 `0 @# 提取是否违约的列表$ ~5 ^; N8 T! R+ l- r) S: `4 b
m = [] 9 b0 }8 U" n! H+ Tfor name in code_list:, E% Q; a0 W) O9 L
m.append(datas[datas['code']==name]['break_contract'].tolist()[0])' I G4 k5 Y+ d( Z+ F: D X
df1.loc[:,len(df1)] = m % u; R& b$ l" p df1.to_excel(writer3,sheet_name='sheet1',index=False) ?) R6 }* {* p4 V ~; u t! i建立模型 1 V+ S& u# A+ O# E$ g2 V! [Logistics违约率预测模型 / @8 m, e) G0 q3 U" h使用Logistics违约预测模型，代入所有的指标数据为自变量，是否违约为因变量，预测出违约率。 6 K. U4 y r2 h' f4 Q' k 3 |( o) I9 n* h! G; d9 I # E$ E$ P* t; ?! z* x1 k$ I% oX=datas[['进项发票作废率','进项负数发票率','进项每月平均交易额','进项每月交易次数','销项发票作废率','销项负数发票率','销项每月平均交易额','销项每月交易次数','销售收入增长率']]# _5 A% C) V9 B
y=datas['是否违约'] ; L% o2 I; M0 g) zX_train, X_test, y_train, y_test = train_test_split(X,y, test_size=0.2,random_state=2020) , @( K( m! |# z# t: P$ zX_validation, X_test, y_validation, y_test = train_test_split(X_test,y_test, test_size=0.1,random_state=2020)5 H+ y3 z( x* \1 C+ `5 ?
model = LogisticRegression() 8 k3 W8 Q% K0 x7 ]; ]0 _model.fit(X_train,y_train) / x, W: [6 a5 i) s$ Va=model.predict_proba(X_validation). W% o: h, Z. p% h3 S$ J3 x+ w6 v" e
result=[]. O! d( s" ^; r9 P; p9 _, M
for i in range(len(a)): ) e" O5 [6 d6 K3 t: M' Oif a[1]>0.5:( j1 t, X7 g+ q
result.append(1) 3 r T3 t/ m9 N- r9 J+ pelse: : w) y2 \$ T, ~8 u0 M result.append(0) 2 n2 R ]/ B$ ffrom sklearn import metrics8 h' a9 z: X( a, |
print('误差: %.4f' % (1-metrics.recall_score(y_validation,result,average='weighted'))) . G( G" t/ k& K# w) _; \" l最终得到一张我们的分析表格 . n- u+ s+ i" o) L! y& e( R( b) Q8 M, U( c' W: e% p1 \1 Q8 f0 w
$ l4 Y9 a L7 F8 B" S# t
/ D( W* S2 u& O
, [7 G0 e# h F) i- H6 d
6 j2 u2 E' Z( z& I* ]3 s" l( F) t% y$ I& b
通过预测是否违约，我们就能解决贷不贷款的问题。' G* N) c; P/ P+ h
/ ]# K+ y" M1 h5 f: Y& [
9 p1 q4 Q7 @' R
贷款金额! Q j1 m) d# @! w) J+ q6 o' n3 t
贷款金额的确认，根据该公司不违约率在所有公司中的权重，乘以总贷款金额确认：8 ?, Q1 ]. i w2 L4 ^* h( ]
' I Y3 q: m0 R. A/ C8 V3 { 3 ]) R5 P7 ]. F Z- t. W ' w. a0 n# Q. _! K- I& V0 {- }8 r# r9 o8 z
; u1 t2 [1 b8 `
ri=1−Zi∑123j=1(1−Zi)×M% p7 j. X8 p$ w; Y
ri=1−Zi∑j=1123(1−Zi)×M$ A: s9 s: l7 m" m8 C
因此，我们得到的贷款金额是违约率和贷款总金额组成的关系式，这在第二问中能起到重要作用。 2 W5 ]; D& A2 E0 j+ j# c ^% ]" U9 Q; f! {' ? Y7 `
( v! L' C6 F% s3 O/ G
贷款年利率$ I, y/ R1 Z, r) f! @! _
绘制出年利率与客户流失率图，可以分析出两者应该是有关系的。利用SPSS拟合出不同信誉等级，年利率与客户流失率的关系式。% f: _, k' H# O( r2 k1 e! Q; X
/ h( ^. j. T' f
4 H# p0 p& x; x! r信誉等级 R平方 关系式 3 p& a& X: X0 Z% gA 0.9977 y = 37.97x^3-258.57x^2+640.944*x -1.121 2 Q6 g3 k0 T0 }/ fB 0.9982 y = 33.995x^3-225.051x^2+552.829*x-1.017( {' [- V+ a- r, r
C 0.9982 y = 32.157x^3-207.386x^2+504.717*x-0.973 & ~$ T p# F4 u1 G# U; B( A( J% P银行获利=贷款金额x贷款年利率x(1-利率对于信誉评级客户流失率)- `1 w4 [! `! ?8 C5 Z" Z% d: U
( a! n% H" H$ a9 m1 y8 R 4 K5 t& n0 }- `" P. O$ v在贷款金额确认，贷款年利率范围在0.4~1.5的情况下，利用上面拟合的关系式，我们能够暴力跑出最优年利率。2 F3 `' b7 g- B/ {5 f; i* \4 r
9 R0 h1 Y5 s- k' m" d I( d, o8 `2 {
" d5 s3 X( f+ c/ J% t8 h' X0 `double turnover_rate(double x, char ch) { ( r# G) H6 Y" d" C7 Z double y = 0, result = 0;, p( G; x- ?& O& v1 b# y
switch (ch) { - x, ]% m) ~/ @3 Y! I7 e case 'A': 1 X, M5 f# R2 {# X4 j7 x. i y = 37.969520 * pow(x, 3) - 258.570452 * pow(x, 2) + 640.944427 * x - 1.121484; 8 k) m5 N8 w- [" \$ l. ?, a result = x * (1 - y / 100.0);) p" s/ J. T# |+ G9 G7 b- \4 {# Y/ E
break; 0 } V& P% z' H- {) Q case 'B':* `. }1 P0 q/ |, e( ]6 }
y = 33.994698 * pow(x, 3) - 225.050538 * pow(x, 2) + 552.829151 * x - 1.016503;( f& t8 `7 X' ^7 J8 x
result = x * (1 - y / 100.0);$ U6 [; Y K! S( r3 W4 l& c
break; ) T# v3 R# h1 ]) f. c( l case 'C': ) I/ C/ F# i/ h% u8 B y = 32.156864 * pow(x, 3) - 207.385880 * pow(x, 2) + 504.716993 * x - 0.973497; - b3 P) }+ p) C3 y0 q result = x * (1 - y / 100.0);0 A* y# o4 ^& F
break;/ _& I& J0 Q* k: i7 S0 [1 Z7 a
default: " | {& D# o: k, K cout << "输出有误！" << ch << endl; , N7 ]5 D# e8 R8 m$ M } + M) @( _& f% O! f4 U# b" d 5 R- D5 M5 \7 A+ O+ U$ x7 h' g5 d8 y6 ~
return result; 5 f( O0 U4 l$ y9 Q: {% o1 Y} " I' H: Y2 Q( m k7 {0 W! ~* Q& z: `) R I; \+ k8 M, |3 i: ~1 n
8 v. J, l. v% `( y; C0 ?1 C" V1 A" h9 w2 N$ n, B6 ]* F4 r
6 a1 J/ r1 Q3 \' `问题二1 r' `* C, Z9 w
利用代码，重新计算出各指标数据 , ^( A* T0 h. I代入Logistics违约率预测模型，预测出各公司的违约率3 |8 f2 \( C! J- D: z
根据标准普尔评级建立，主标尺，对不同违约率进行A~D等级划分，信誉等级D不予贷款 6 g$ f2 H) p8 u6 u将违约率代入，之前得到的公式，得到具体贷款金额+ N* z9 e8 H: B N4 G" b
最优年利率沿用上一问0 D Y. k8 B7 X. M; w1 @
# 信用等级 ' g% C! _; J- w: |! {cs = [] 2 D+ N9 g- g( g# 最优年利率,客户流失率,利率值$ t i. [9 p7 T) m7 X, O
tax = []0 Q. Z0 v1 \( a4 a4 L
for i in m:& \9 n2 V! V* y( r) k& _8 _+ f
if i <= 0.0069264:# r. `. U" Q% m) q* F
cs.append('A')8 S9 h6 l" K {2 k. H1 V0 d2 v R
tax.append([0.083,0.503173,0.0412366])& M8 G) q ]4 F( T$ Q9 B( {" l
elif i > 0.0069264 and i <= 0.22619:0 g5 a$ b. V# v- H
cs.append('B') 1 u m: ^ R5 w+ N tax.append([0.097,0.505215,0.0479942])+ b& l& ]: c* a9 t4 @$ R0 F9 X7 ^7 {
elif i > 0.22619 and i <= 0.509915: 4 R4 O4 H, E# [ cs.append('C') [, Y- Y: ?( X7 Z: v
tax.append([0.1069,0.506501,0.052755]) / y7 } F5 u1 B( } F6 Z7 n2 M elif i > 0.509915:$ T' e, \4 B( k7 C6 y5 B
cs.append('D') & f+ Y# x9 Q9 {! C tax.append([0.15,0,0])# y; Y7 O0 L8 k, L. S2 |3 S
else: # v. E8 k* n$ z1 }0 t! F print('违规') 0 U- u h' k G. o! r \, n * `7 W) _9 X2 x3 \
parr = []7 U4 k8 D X, ?: d% A. ~$ R
for arr in list(a): # W9 J/ v2 c% v: R) z* e parr.append(list(arr)[0])- \; t: m& w& e' Z2 n4 Q( a
sum_val = sum(parr)) z+ b' l- H& t: F$ G. q1 X
amount = [] r. b& Y: o+ j) n2 x9 ~; Pfor ival in parr: $ z9 h$ d* U& e$ D! k0 N5 S8 C tmp = ival / sum_val * 100000000 8 y4 l9 a- k" a+ T0 Q; D7 T7 c if ival < 1 - 0.509915:7 y0 G. f3 K- z8 }" `
amount.append(0) ; g4 h4 |$ g( v else:% [0 B, \9 T4 {' p
amount.append(tmp) ! ?! q+ l9 }: b2 @; X" G) s# i/ r# [1 J
& q R& T3 q* ]+ q2 I# O: B, \2 g& i/ ~4 l' v4 L2 D5 E
