风险评估数据处理-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_28903789/article/details/89302314

import json
import pandas as pd

path = 'F:/data/'
tabel3 = pd.read_excel(path+'表3.xlsx')

#建立新的dataframe
tabel3d = pd.DataFrame()

for i in range(tabel3.shape[0]):
    a = tabel3['report'].values[i]
    b = pd.Series(json.loads(a))
    c = pd.DataFrame(b).T
    tabel3d = tabel3d.append(c,sort=False)
tabel3d.shape

(52, 7)

#建立三列低中高风险击中数
tabel3d['high'] = None
tabel3d['medium'] = None
tabel3d['low'] = None
tabel3d['id'] = tabel3['id'].values
tabel3d.set_index(['id'],inplace=True)
tabel3d

	success	risk_items	report_id	final_score	final_decision	apply_time	report_time	high	medium	low
id
0	True	[]	ER2017021411501413297364	0	Accept	1487044214000	1487044215000	None	None	None
1	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411501515455146	100	Reject	1487044216000	1487044216000	None	None	None
2	True	[]	ER2017021411501719601983	0	Accept	1487044217000	1487044217000	None	None	None
3	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411501713850258	100	Reject	1487044218000	1487044218000	None	None	None
4	True	[{'risk_level': 'low', 'item_detail': {'high_r...	ER2017021411501816312404	100	Reject	1487044219000	1487044219000	None	None	None
5	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411501911793637	100	Reject	1487044219000	1487044220000	None	None	None
6	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411502019091675	100	Reject	1487044221000	1487044221000	None	None	None
7	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411502417113254	10	Accept	1487044224000	1487044224000	None	None	None
8	True	[{'risk_level': 'medium', 'item_detail': {'pla...	ER2017021411502417931331	8	Accept	1487044225000	1487044225000	None	None	None
9	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411502817613683	100	Reject	1487044228000	1487044228000	None	None	None
10	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411502813037700	39	Review	1487044229000	1487044229000	None	None	None
11	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411502911395291	100	Reject	1487044229000	1487044230000	None	None	None
12	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411502918786146	100	Reject	1487044230000	1487044230000	None	None	None
13	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411503110901843	50	Review	1487044232000	1487044232000	None	None	None
14	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411503214446611	22	Review	1487044232000	1487044232000	None	None	None
15	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411503212179416	14	Accept	1487044233000	1487044233000	None	None	None
16	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411503311051577	100	Reject	1487044233000	1487044233000	None	None	None
17	True	[]	ER2017021411503316597452	0	Accept	1487044234000	1487044234000	None	None	None
18	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411503314161010	14	Accept	1487044234000	1487044234000	None	None	None
19	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411503412732609	24	Review	1487044234000	1487044235000	None	None	None
20	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411503510305007	21	Review	1487044235000	1487044235000	None	None	None
21	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411503511905389	18	Accept	1487044236000	1487044236000	None	None	None
22	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411503612925482	18	Accept	1487044237000	1487044237000	None	None	None
23	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411503717346938	20	Review	1487044237000	1487044237000	None	None	None
24	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411503816927774	18	Accept	1487044239000	1487044239000	None	None	None
25	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411503910837169	10	Accept	1487044239000	1487044240000	None	None	None
26	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411504010400653	100	Reject	1487044240000	1487044241000	None	None	None
27	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411504119188759	100	Reject	1487044242000	1487044242000	None	None	None
28	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411504213421618	100	Reject	1487044243000	1487044243000	None	None	None
29	True	[]	ER2017021411504319915825	0	Accept	1487044243000	1487044243000	None	None	None
30	True	[]	ER2017021411504311010807	0	Accept	1487044244000	1487044244000	None	None	None
31	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411504712296721	20	Review	1487044248000	1487044248000	None	None	None
32	True	[]	ER2017021411504817300353	0	Accept	1487044248000	1487044249000	None	None	None
33	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411504914233468	54	Review	1487044249000	1487044249000	None	None	None
34	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411504914531669	21	Review	1487044250000	1487044250000	None	None	None
35	True	[{'risk_level': 'medium', 'item_detail': {'pla...	ER2017021411505013099482	10	Accept	1487044250000	1487044250000	None	None	None
36	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411505010915505	50	Review	1487044250000	1487044251000	None	None	None
37	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411505112709566	4	Accept	1487044251000	1487044251000	None	None	None
38	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411505114039000	24	Review	1487044251000	1487044252000	None	None	None
39	True	[{'risk_level': 'medium', 'item_detail': {}, '...	ER2017021411505114936709	40	Review	1487044252000	1487044252000	None	None	None
40	True	[{'risk_level': 'medium', 'item_id': 1113936, ...	ER2017021411505218219411	50	Review	1487044252000	1487044252000	None	None	None
41	True	[{'risk_level': 'medium', 'item_detail': {}, '...	ER2017021411505214343046	40	Review	1487044253000	1487044253000	None	None	None
42	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411505312186926	100	Reject	1487044253000	1487044253000	None	None	None
43	True	[{'risk_level': 'medium', 'item_detail': {'fra...	ER2017021411505418556790	91	Reject	1487044255000	1487044255000	None	None	None
44	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411505510017786	22	Review	1487044256000	1487044256000	None	None	None
45	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411505612544090	38	Review	1487044256000	1487044256000	None	None	None
46	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411505715645485	100	Reject	1487044257000	1487044257000	None	None	None
47	True	[{'risk_level': 'high', 'item_detail': {'discr...	ER2017021411505712250618	100	Reject	1487044258000	1487044258000	None	None	None
48	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411505819745610	47	Review	1487044258000	1487044258000	None	None	None
49	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411505911944800	5	Accept	1487044259000	1487044260000	None	None	None
50	True	[]	ER2017021411505910316065	0	Accept	1487044260000	1487044260000	None	None	None
51	True	[{'risk_level': 'low', 'item_detail': {'fraud_...	ER2017021411510019484203	11	Accept	1487044261000	1487044261000	None	None	None

#统计每个样本低中高风险击中个数并填入表中然后输出到文件
for i in range(tabel3d.shape[0]):
    
    high = 0
    medium = 0
    low = 0
    
    if len(tabel3d['risk_items'].values[i])>0:    
        
        for j in range(len(tabel3d['risk_items'].values[i])):
            
            if tabel3d['risk_items'].values[i][j]['risk_level'] == 'high':
                high+=1
                
            elif tabel3d['risk_items'].values[i][j]['risk_level'] == 'medium':
                medium+=1
                
            elif tabel3d['risk_items'].values[i][j]['risk_level'] == 'low':
                low+=1
                
            else:
                pass
    tabel3d.loc[i,'high'] = high
    tabel3d.loc[i,'medium'] = medium
    tabel3d.loc[i,'low'] = low
    
tabel3d.rename(columns={'high':'高风险击中个数','medium':'中风险击中个数','low':'低风险击中个数'},inplace=True)
#处理好的新表3导出到文件
tabel3d.to_excel(path+'表3-处理后.xlsx')

#输出risk items类别最多的样本的索引
long = []
for i in range(len(tabel3d['risk_items'])):
    long.append(len(tabel3d['risk_items'][i]))

for i in range(len(long)):
    if long[i] == max(long):
        print(i)

platform_list = []
for i in range(tabel3d.shape[0]):
    
    if len(tabel3d['risk_items'][i])>0:
            
            for j in tabel3d['risk_items'][i]:
                if 'item_detail' in j:

                    if 'platform_detail' in j['item_detail']:

                        platform_list.append(j['item_detail']['platform_detail'])

platform_list2 = []
for i in platform_list:
    for j in range(len(i)):
        platform_list2.append(i[j])
        
platform_list3 = []
for i in platform_list2:
    platform_list3.append(i[:-2])
set(platform_list3)

{'P2P网贷',
 'P2P网贷:',
 '一般消费分期平台',
 '互联网金融门户',
 '信用卡中心',
 '大型消费金融公司',
 '小额贷款公司',
 '融资租赁',
 '财产保险',
 '银行消费金融公司'}

题目二、代码