机器学习——pandas库实战演练

标签: 机器学习  pandas库  贷款数据集

题目

(1)加载bank数据集,删除账户号列,增加每月还款额列(每月还款额列=贷款金额/贷款期限)(5分)
(2)对还款状态进行计数(6分)
(3)按照还款状态进行分组,计算贷款金额的最大值(7分)
(4)输出每月还款额的最大值与中位数(6分)
(5)对数据进行二次排序,按照贷款期限升序,每月归还额降序(8分)
(6)提取贷款号在50006000并且发放贷款日期在1993-07-111998-11-10的记录(8分)

代码实现

import pandas as pd
# (1)加载bank数据集,
data = pd.read_excel('../../datas/bank.xls')
# 删除账户号列,
print(data)
del data['账户号']
print(data)
# 增加每月还款额列(每月还款额列=贷款金额/贷款期限)(5分)
data['每月还款额'] = data['贷款金额']/data['贷款期限']
print(data)
# (2)对还款状态进行计数(6分)value_counts()是对每个类别进行计数的
# print(data['还款状态'].value_counts())
# (3)按照还款状态进行分组,计算贷款金额的最大值(7分)
print('贷款金额的最大值', data[['还款状态','贷款金额']].groupby('还款状态').max())
# (4)输出每月还款额的最大值与中位数(6分)
a = data['每月还款额']
print('最大值', a.max())
print('中位数',a.median())
# (5)对数据进行二次排序,按照贷款期限升序,每月归还额降序(8分)
df = data.sort_values(['贷款期限', '每月归还额'], ascending=[True, False])
print('排序:\n', df)
# (6)提取贷款号在5000~6000并且发放贷款日期在1993-07-11~1998-11-10的记录(8分)
df = data[(data['贷款号'] < 6000) & (data['贷款号'] > 5000) & (data['发放贷款日期'] > '1993-07-11') & (data['发放贷款日期'] < '1998-11-10')]
print(df)

效果展示

C:\Users\shy\AppData\Local\Continuum\anaconda3\python.exe C:/Users/shy/PycharmProjects/untitled2/August/day04_0805/demo03.py
      贷款号    账户号     发放贷款日期    贷款金额  贷款期限  每月归还额 还款状态
0    5314   1787 1993-07-05   96396    12   8033    B
1    5316   1801 1993-07-11  165960    36   4610    A
2    6863   9188 1993-07-28  127080    60   2118    A
3    5325   1843 1993-08-03  105804    36   2939    A
4    7240  11013 1993-09-06  274740    60   4579    A
5    6687   8261 1993-09-13   87840    24   3660    A
6    7284  11265 1993-09-15   52788    12   4399    A
7    6111   5428 1993-09-24  174744    24   7281    B
8    7235  10973 1993-10-13  154416    48   3217    A
9    5997   4894 1993-11-04  117024    24   4876    A
10   7121  10364 1993-11-10   21924    36    609    A
11   6077   5270 1993-11-22   79608    24   3317    A
12   6228   6034 1993-12-01  464520    60   7742    B
13   6356   6701 1993-12-08   95400    36   2650    A
14   5523   2705 1993-12-08   93888    36   2608    A
15   6456   7123 1993-12-09   47016    12   3918    A
16   7104  10320 1993-12-13  259740    60   4329    A
17   6820   9034 1993-12-16   38148    12   3179    A
18   6876   9236 1993-12-21   86616    12   7218    A
19   7097  10266 1993-12-23   75624    24   3151    B
20   4959      2 1994-01-05   80952    24   3373    A
21   6499   7401 1994-01-05   80952    24   3373    A
22   5479   2486 1994-01-10   24516    12   2043    A
23   5170   1071 1994-01-20  253200    60   4220    C
24   6721   8475 1994-01-21   21072    24    878    A
25   7259  11111 1994-01-31  108144    36   3004    A
26   5285   1603 1994-02-06   78936    12   6578    A
27   5189   1166 1994-02-07  149040    48   3105    A
28   6650   8073 1994-02-17   49320    12   4110    B
29   7226  10940 1994-02-23  197748    36   5493    A
..    ...    ...        ...     ...   ...    ...  ...
652  5698   3556 1998-09-22  174840    60   2914    C
653  5169   1068 1998-09-24  253200    60   4220    C
654  7294  11327 1998-09-27   39168    24   1632    C
655  5318   1808 1998-09-30  385584    48   8033    C
656  5368   2073 1998-10-05   44640    24   1860    C
657  6923   9433 1998-10-05   44640    24   1860    C
658  5463   2413 1998-10-06   17952    12   1496    C
659  5265   1498 1998-10-07  165780    60   2763    C
660  6321   6505 1998-10-08   38496    12   3208    C
661  5226   1318 1998-10-11  185544    36   5154    C
662  6868   9208 1998-10-12   82128    24   3422    C
663  4967     37 1998-10-14  318480    60   5308    D
664  5293   1656 1998-10-29  189792    48   3954    C
665  5865   4354 1998-10-30  327660    60   5461    C
666  5841   4268 1998-11-04   41988    12   3499    C
667  5526   2725 1998-11-08   69624    36   1934    C
668  6852   9140 1998-11-10   16032    48    334    C
669  6469   7180 1998-11-17   99744    24   4156    C
670  6168   5698 1998-11-21   99216    36   2756    C
671  7292  11317 1998-11-22  317460    60   5291    C
672  5036    309 1998-11-25   91248    48   1901    C
673  5644   3293 1998-11-27  276084    36   7669    C
674  6856   9156 1998-12-01  163332    36   4537    C
675  5428   2262 1998-12-02  230220    36   6395    C
676  5027    276 1998-12-02  160920    36   4470    C
677  4989    105 1998-12-05  352704    48   7348    C
678  5221   1284 1998-12-05   52512    12   4376    C
679  6402   6922 1998-12-06  139488    24   5812    C
680  5346   1928 1998-12-06   55632    24   2318    C
681  6748   8645 1998-12-08  240900    60   4015    C

[682 rows x 7 columns]
      贷款号     发放贷款日期    贷款金额  贷款期限  每月归还额 还款状态
0    5314 1993-07-05   96396    12   8033    B
1    5316 1993-07-11  165960    36   4610    A
2    6863 1993-07-28  127080    60   2118    A
3    5325 1993-08-03  105804    36   2939    A
4    7240 1993-09-06  274740    60   4579    A
5    6687 1993-09-13   87840    24   3660    A
6    7284 1993-09-15   52788    12   4399    A
7    6111 1993-09-24  174744    24   7281    B
8    7235 1993-10-13  154416    48   3217    A
9    5997 1993-11-04  117024    24   4876    A
10   7121 1993-11-10   21924    36    609    A
11   6077 1993-11-22   79608    24   3317    A
12   6228 1993-12-01  464520    60   7742    B
13   6356 1993-12-08   95400    36   2650    A
14   5523 1993-12-08   93888    36   2608    A
15   6456 1993-12-09   47016    12   3918    A
16   7104 1993-12-13  259740    60   4329    A
17   6820 1993-12-16   38148    12   3179    A
18   6876 1993-12-21   86616    12   7218    A
19   7097 1993-12-23   75624    24   3151    B
20   4959 1994-01-05   80952    24   3373    A
21   6499 1994-01-05   80952    24   3373    A
22   5479 1994-01-10   24516    12   2043    A
23   5170 1994-01-20  253200    60   4220    C
24   6721 1994-01-21   21072    24    878    A
25   7259 1994-01-31  108144    36   3004    A
26   5285 1994-02-06   78936    12   6578    A
27   5189 1994-02-07  149040    48   3105    A
28   6650 1994-02-17   49320    12   4110    B
29   7226 1994-02-23  197748    36   5493    A
..    ...        ...     ...   ...    ...  ...
652  5698 1998-09-22  174840    60   2914    C
653  5169 1998-09-24  253200    60   4220    C
654  7294 1998-09-27   39168    24   1632    C
655  5318 1998-09-30  385584    48   8033    C
656  5368 1998-10-05   44640    24   1860    C
657  6923 1998-10-05   44640    24   1860    C
658  5463 1998-10-06   17952    12   1496    C
659  5265 1998-10-07  165780    60   2763    C
660  6321 1998-10-08   38496    12   3208    C
661  5226 1998-10-11  185544    36   5154    C
662  6868 1998-10-12   82128    24   3422    C
663  4967 1998-10-14  318480    60   5308    D
664  5293 1998-10-29  189792    48   3954    C
665  5865 1998-10-30  327660    60   5461    C
666  5841 1998-11-04   41988    12   3499    C
667  5526 1998-11-08   69624    36   1934    C
668  6852 1998-11-10   16032    48    334    C
669  6469 1998-11-17   99744    24   4156    C
670  6168 1998-11-21   99216    36   2756    C
671  7292 1998-11-22  317460    60   5291    C
672  5036 1998-11-25   91248    48   1901    C
673  5644 1998-11-27  276084    36   7669    C
674  6856 1998-12-01  163332    36   4537    C
675  5428 1998-12-02  230220    36   6395    C
676  5027 1998-12-02  160920    36   4470    C
677  4989 1998-12-05  352704    48   7348    C
678  5221 1998-12-05   52512    12   4376    C
679  6402 1998-12-06  139488    24   5812    C
680  5346 1998-12-06   55632    24   2318    C
681  6748 1998-12-08  240900    60   4015    C

[682 rows x 6 columns]
      贷款号     发放贷款日期    贷款金额  贷款期限  每月归还额 还款状态   每月还款额
0    5314 1993-07-05   96396    12   8033    B  8033.0
1    5316 1993-07-11  165960    36   4610    A  4610.0
2    6863 1993-07-28  127080    60   2118    A  2118.0
3    5325 1993-08-03  105804    36   2939    A  2939.0
4    7240 1993-09-06  274740    60   4579    A  4579.0
5    6687 1993-09-13   87840    24   3660    A  3660.0
6    7284 1993-09-15   52788    12   4399    A  4399.0
7    6111 1993-09-24  174744    24   7281    B  7281.0
8    7235 1993-10-13  154416    48   3217    A  3217.0
9    5997 1993-11-04  117024    24   4876    A  4876.0
10   7121 1993-11-10   21924    36    609    A   609.0
11   6077 1993-11-22   79608    24   3317    A  3317.0
12   6228 1993-12-01  464520    60   7742    B  7742.0
13   6356 1993-12-08   95400    36   2650    A  2650.0
14   5523 1993-12-08   93888    36   2608    A  2608.0
15   6456 1993-12-09   47016    12   3918    A  3918.0
16   7104 1993-12-13  259740    60   4329    A  4329.0
17   6820 1993-12-16   38148    12   3179    A  3179.0
18   6876 1993-12-21   86616    12   7218    A  7218.0
19   7097 1993-12-23   75624    24   3151    B  3151.0
20   4959 1994-01-05   80952    24   3373    A  3373.0
21   6499 1994-01-05   80952    24   3373    A  3373.0
22   5479 1994-01-10   24516    12   2043    A  2043.0
23   5170 1994-01-20  253200    60   4220    C  4220.0
24   6721 1994-01-21   21072    24    878    A   878.0
25   7259 1994-01-31  108144    36   3004    A  3004.0
26   5285 1994-02-06   78936    12   6578    A  6578.0
27   5189 1994-02-07  149040    48   3105    A  3105.0
28   6650 1994-02-17   49320    12   4110    B  4110.0
29   7226 1994-02-23  197748    36   5493    A  5493.0
..    ...        ...     ...   ...    ...  ...     ...
652  5698 1998-09-22  174840    60   2914    C  2914.0
653  5169 1998-09-24  253200    60   4220    C  4220.0
654  7294 1998-09-27   39168    24   1632    C  1632.0
655  5318 1998-09-30  385584    48   8033    C  8033.0
656  5368 1998-10-05   44640    24   1860    C  1860.0
657  6923 1998-10-05   44640    24   1860    C  1860.0
658  5463 1998-10-06   17952    12   1496    C  1496.0
659  5265 1998-10-07  165780    60   2763    C  2763.0
660  6321 1998-10-08   38496    12   3208    C  3208.0
661  5226 1998-10-11  185544    36   5154    C  5154.0
662  6868 1998-10-12   82128    24   3422    C  3422.0
663  4967 1998-10-14  318480    60   5308    D  5308.0
664  5293 1998-10-29  189792    48   3954    C  3954.0
665  5865 1998-10-30  327660    60   5461    C  5461.0
666  5841 1998-11-04   41988    12   3499    C  3499.0
667  5526 1998-11-08   69624    36   1934    C  1934.0
668  6852 1998-11-10   16032    48    334    C   334.0
669  6469 1998-11-17   99744    24   4156    C  4156.0
670  6168 1998-11-21   99216    36   2756    C  2756.0
671  7292 1998-11-22  317460    60   5291    C  5291.0
672  5036 1998-11-25   91248    48   1901    C  1901.0
673  5644 1998-11-27  276084    36   7669    C  7669.0
674  6856 1998-12-01  163332    36   4537    C  4537.0
675  5428 1998-12-02  230220    36   6395    C  6395.0
676  5027 1998-12-02  160920    36   4470    C  4470.0
677  4989 1998-12-05  352704    48   7348    C  7348.0
678  5221 1998-12-05   52512    12   4376    C  4376.0
679  6402 1998-12-06  139488    24   5812    C  5812.0
680  5346 1998-12-06   55632    24   2318    C  2318.0
681  6748 1998-12-08  240900    60   4015    C  4015.0

[682 rows x 7 columns]
C    403
A    203
D     45
B     31
Name: 还款状态, dtype: int64
贷款金额的最大值         贷款金额
还款状态        
A     323472
B     464520
C     590820
D     541200
最大值 9910.0
中位数 3934.0
排序:
       贷款号     发放贷款日期    贷款金额  贷款期限  每月归还额 还款状态   每月还款额
343  6934 1997-02-09  116832    12   9736    A  9736.0
629  5015 1998-07-12  109344    12   9112    C  9112.0
586  6320 1998-04-28  104964    12   8747    C  8747.0
52   5002 1994-05-31  104808    12   8734    A  8734.0
436  4986 1997-08-10  102876    12   8573    A  8573.0
294  5375 1996-10-03  101520    12   8460    A  8460.0
379  7253 1997-04-26   98304    12   8192    A  8192.0
47   6998 1994-05-21   97632    12   8136    A  8136.0
454  6612 1997-08-31   97392    12   8116    A  8116.0
0    5314 1993-07-05   96396    12   8033    B  8033.0
290  6995 1996-09-26   96168    12   8014    A  8014.0
130  5464 1995-01-30   95808    12   7984    A  7984.0
467  5643 1997-09-21   94512    12   7876    B  7876.0
555  6730 1998-02-15   93036    12   7753    C  7753.0
433  5377 1997-08-04   92400    12   7700    A  7700.0
32   5519 1994-03-08   91632    12   7636    A  7636.0
369  7046 1997-03-28   91632    12   7636    A  7636.0
370  5868 1997-03-29   90156    12   7513    A  7513.0
487  7277 1997-10-24   89280    12   7440    A  7440.0
623  5585 1998-07-08   88608    12   7384    C  7384.0
307  4996 1996-11-06   88440    12   7370    A  7370.0
92   5882 1994-09-26   87816    12   7318    A  7318.0
400  7119 1997-06-02   86880    12   7240    A  7240.0
18   6876 1993-12-21   86616    12   7218    A  7218.0
303  6921 1996-10-29   86184    12   7182    A  7182.0
304  5366 1996-10-29   86184    12   7182    A  7182.0
104  6150 1994-11-02   83016    12   6918    A  6918.0
88   5595 1994-09-02   82896    12   6908    A  6908.0
46   6678 1994-05-20   80340    12   6695    A  6695.0
519  6059 1997-12-28   79824    12   6652    A  6652.0
..    ...        ...     ...   ...    ...  ...     ...
338  6076 1997-02-02  117420    60   1957    C  1957.0
441  6984 1997-08-15  116040    60   1934    C  1934.0
621  5154 1998-07-01  108720    60   1812    C  1812.0
143  6169 1995-03-16  103680    60   1728    D  1728.0
373  5110 1997-04-01  102240    60   1704    C  1704.0
118  5044 1994-12-21  100980    60   1683    C  1683.0
397  6346 1997-05-22  100260    60   1671    C  1671.0
439  6665 1997-08-15   99300    60   1655    C  1655.0
438  6677 1997-08-12   97980    60   1633    C  1633.0
328  5895 1997-01-03   93960    60   1566    C  1566.0
266  5495 1996-08-02   89340    60   1489    C  1489.0
267  7013 1996-08-02   89340    60   1489    C  1489.0
42   6696 1994-05-09   89040    60   1484    C  1484.0
180  6726 1995-09-03   85860    60   1431    C  1431.0
136  6294 1995-02-12   79920    60   1332    C  1332.0
345  6490 1997-02-10   73260    60   1221    C  1221.0
333  6095 1997-01-24   68340    60   1139    C  1139.0
627  6357 1998-07-12   64860    60   1081    C  1081.0
165  6474 1995-06-06   63360    60   1056    C  1056.0
525  5909 1998-01-03   61320    60   1022    C  1022.0
170  7138 1995-07-03   60000    60   1000    C  1000.0
222  6659 1996-02-20   56100    60    935    C   935.0
71   5549 1994-07-16   50460    60    841    C   841.0
238  5452 1996-04-29   49440    60    824    C   824.0
413  6970 1997-06-17   31620    60    527    C   527.0
624  7200 1998-07-11   31140    60    519    C   519.0
252  5311 1996-06-15   30060    60    501    C   501.0
514  7187 1997-12-24   24180    60    403    C   403.0
595  5147 1998-05-21   22260    60    371    C   371.0
375  7230 1997-04-02   18720    60    312    C   312.0

[682 rows x 7 columns]
      贷款号     发放贷款日期    贷款金额  贷款期限  每月归还额 还款状态   每月还款额
3    5325 1993-08-03  105804    36   2939    A  2939.0
9    5997 1993-11-04  117024    24   4876    A  4876.0
14   5523 1993-12-08   93888    36   2608    A  2608.0
22   5479 1994-01-10   24516    12   2043    A  2043.0
23   5170 1994-01-20  253200    60   4220    C  4220.0
26   5285 1994-02-06   78936    12   6578    A  6578.0
27   5189 1994-02-07  149040    48   3105    A  3105.0
32   5519 1994-03-08   91632    12   7636    A  7636.0
44   5130 1994-05-11   24312    12   2026    A  2026.0
45   5568 1994-05-17  272520    60   4542    C  4542.0
52   5002 1994-05-31  104808    12   8734    A  8734.0
56   5918 1994-06-04  300204    36   8339    A  8339.0
63   5319 1994-06-23  239460    60   3991    C  3991.0
65   5837 1994-06-29  244560    60   4076    C  4076.0
66   5657 1994-07-05    4980    12    415    A   415.0
67   5533 1994-07-05  111384    24   4641    A  4641.0
71   5549 1994-07-16   50460    60    841    C   841.0
72   5060 1994-07-19  252060    60   4201    D  4201.0
74   5126 1994-07-24  208128    48   4336    B  4336.0
77   5395 1994-07-30  299088    36   8308    B  8308.0
86   5836 1994-08-27   79632    24   3318    A  3318.0
88   5595 1994-09-02   82896    12   6908    A  6908.0
89   5723 1994-09-10   23628    12   1969    A  1969.0
92   5882 1994-09-26   87816    12   7318    A  7318.0
93   5754 1994-09-28   23052    12   1921    A  1921.0
99   5887 1994-10-03  192744    36   5354    A  5354.0
102  5161 1994-10-28   98184    24   4091    A  4091.0
103  5390 1994-10-30  170256    24   7094    A  7094.0
106  5032 1994-11-03  123696    48   2577    A  2577.0
110  5176 1994-11-12  192552    24   8023    B  8023.0
..    ...        ...     ...   ...    ...  ...     ...
617  5823 1998-06-21  212976    36   5916    C  5916.0
620  5669 1998-06-27   71820    36   1995    C  1995.0
621  5154 1998-07-01  108720    60   1812    C  1812.0
623  5585 1998-07-08   88608    12   7384    C  7384.0
626  5774 1998-07-12   96984    36   2694    C  2694.0
628  5042 1998-07-12  331584    48   6908    C  6908.0
629  5015 1998-07-12  109344    12   9112    C  9112.0
630  5354 1998-07-14  290820    60   4847    C  4847.0
633  5365 1998-07-19  271260    60   4521    C  4521.0
635  5991 1998-07-27  335184    48   6983    D  6983.0
637  5893 1998-08-03   86184    36   2394    C  2394.0
639  5304 1998-08-20  172272    24   7178    C  7178.0
640  5561 1998-08-29  437460    60   7291    C  7291.0
641  5520 1998-08-30  231168    48   4816    C  4816.0
642  5241 1998-09-02  125472    24   5228    C  5228.0
645  5046 1998-09-09   42816    12   3568    C  3568.0
646  5334 1998-09-10  209040    24   8710    C  8710.0
649  5760 1998-09-18   74916    36   2081    C  2081.0
650  5614 1998-09-21  253560    60   4226    C  4226.0
652  5698 1998-09-22  174840    60   2914    C  2914.0
653  5169 1998-09-24  253200    60   4220    C  4220.0
655  5318 1998-09-30  385584    48   8033    C  8033.0
656  5368 1998-10-05   44640    24   1860    C  1860.0
658  5463 1998-10-06   17952    12   1496    C  1496.0
659  5265 1998-10-07  165780    60   2763    C  2763.0
661  5226 1998-10-11  185544    36   5154    C  5154.0
664  5293 1998-10-29  189792    48   3954    C  3954.0
665  5865 1998-10-30  327660    60   5461    C  5461.0
666  5841 1998-11-04   41988    12   3499    C  3499.0
667  5526 1998-11-08   69624    36   1934    C  1934.0

[266 rows x 7 columns]

Process finished with exit code 0

数据集

在这里插入图片描述

原文链接:加载失败,请重新获取