# 算法实践2.1数据预处理 ## 任务：数据预处理 对数据进行探索和分析： * 数据类型的分析 * 无关特征删除 * 数据类型转换 * 缺失值处理 * ...以及你能想到和借鉴的数据分析处理 ## 1.查看数据 首先读入数据，查看数据的维度，并且输出5行感受一下\~ ```python import pandas as pd data = pd.read_csv('./data_all.csv', engine='python') print(data.shape) data.head() ``` 输出： ``` (4754, 85) ``` | low\_volume\_percent | middle\_volume\_percent | take\_amount\_in\_later\_12\_month\_highest | trans\_amount\_increase\_rate\_lately | trans\_activity\_month | trans\_activity\_day | transd\_mcc | trans\_days\_interval\_filter | trans\_days\_interval | regional\_mobility | ... | consfin\_product\_count | consfin\_max\_limit | consfin\_avg\_limit | latest\_query\_day | loans\_latest\_day | reg\_preference\_for\_trad | latest\_query\_time\_month | latest\_query\_time\_weekday | loans\_latest\_time\_month | loans\_latest\_time\_weekday | | | -------------------- | ----------------------- | ------------------------------------------- | ------------------------------------- | ---------------------- | -------------------- | ----------- | ----------------------------- | --------------------- | ------------------ | --- | ----------------------- | ------------------- | ------------------- | ------------------ | ------------------ | -------------------------- | -------------------------- | ---------------------------- | -------------------------- | ---------------------------- | --- | | 0 | 0.01 | 0.99 | 0 | 0.90 | 0.55 | 0.313 | 17.0 | 27.0 | 26.0 | 3.0 | ... | 2.0 | 1200.0 | 1200.0 | 12.0 | 18.0 | 0 | 4.0 | 2.0 | 4.0 | 3.0 | | 1 | 0.02 | 0.94 | 2000 | 1.28 | 1.00 | 0.458 | 19.0 | 30.0 | 14.0 | 4.0 | ... | 6.0 | 22800.0 | 9360.0 | 4.0 | 2.0 | 0 | 5.0 | 3.0 | 5.0 | 5.0 | | 2 | 0.04 | 0.96 | 0 | 1.00 | 1.00 | 0.114 | 13.0 | 68.0 | 22.0 | 1.0 | ... | 1.0 | 4200.0 | 4200.0 | 2.0 | 6.0 | 0 | 5.0 | 5.0 | 5.0 | 1.0 | | 3 | 0.00 | 0.96 | 2000 | 0.13 | 0.57 | 0.777 | 22.0 | 14.0 | 6.0 | 3.0 | ... | 5.0 | 30000.0 | 12180.0 | 2.0 | 4.0 | 1 | 5.0 | 5.0 | 5.0 | 3.0 | | 4 | 0.01 | 0.99 | 0 | 0.46 | 1.00 | 0.175 | 13.0 | 66.0 | 42.0 | 1.0 | ... | 2.0 | 8400.0 | 8250.0 | 22.0 | 120.0 | 0 | 4.0 | 6.0 | 1.0 | 6.0 | 5 rows × 85 columns 可见一共是4754行，85列。所有的列似乎都是数值型的，为了更确定，我们用info()函数看看数据类型： ```python data.info() ``` 输出： ``` RangeIndex: 4754 entries, 0 to 4753 Data columns (total 85 columns): low_volume_percent 4754 non-null float64 middle_volume_percent 4754 non-null float64 take_amount_in_later_12_month_highest 4754 non-null int64 trans_amount_increase_rate_lately 4754 non-null float64 trans_activity_month 4754 non-null float64 trans_activity_day 4754 non-null float64 transd_mcc 4754 non-null float64 trans_days_interval_filter 4754 non-null float64 trans_days_interval 4754 non-null float64 regional_mobility 4754 non-null float64 repayment_capability 4754 non-null int64 is_high_user 4754 non-null int64 number_of_trans_from_2011 4754 non-null float64 first_transaction_time 4754 non-null float64 historical_trans_amount 4754 non-null int64 historical_trans_day 4754 non-null float64 rank_trad_1_month 4754 non-null float64 trans_amount_3_month 4754 non-null int64 avg_consume_less_12_valid_month 4754 non-null float64 abs 4754 non-null int64 top_trans_count_last_1_month 4754 non-null float64 avg_price_last_12_month 4754 non-null int64 avg_price_top_last_12_valid_month 4754 non-null float64 trans_top_time_last_1_month 4754 non-null float64 trans_top_time_last_6_month 4754 non-null float64 consume_top_time_last_1_month 4754 non-null float64 consume_top_time_last_6_month 4754 non-null float64 cross_consume_count_last_1_month 4754 non-null float64 trans_fail_top_count_enum_last_1_month 4754 non-null float64 trans_fail_top_count_enum_last_6_month 4754 non-null float64 trans_fail_top_count_enum_last_12_month 4754 non-null float64 consume_mini_time_last_1_month 4754 non-null float64 max_cumulative_consume_later_1_month 4754 non-null int64 max_consume_count_later_6_month 4754 non-null float64 railway_consume_count_last_12_month 4754 non-null float64 pawns_auctions_trusts_consume_last_1_month 4754 non-null int64 pawns_auctions_trusts_consume_last_6_month 4754 non-null int64 jewelry_consume_count_last_6_month 4754 non-null float64 status 4754 non-null int64 first_transaction_day 4754 non-null float64 trans_day_last_12_month 4754 non-null float64 apply_score 4754 non-null float64 apply_credibility 4754 non-null float64 query_org_count 4754 non-null float64 query_finance_count 4754 non-null float64 query_cash_count 4754 non-null float64 query_sum_count 4754 non-null float64 latest_one_month_apply 4754 non-null float64 latest_three_month_apply 4754 non-null float64 latest_six_month_apply 4754 non-null float64 loans_score 4754 non-null float64 loans_credibility_behavior 4754 non-null float64 loans_count 4754 non-null float64 loans_settle_count 4754 non-null float64 loans_overdue_count 4754 non-null float64 loans_org_count_behavior 4754 non-null float64 consfin_org_count_behavior 4754 non-null float64 loans_cash_count 4754 non-null float64 latest_one_month_loan 4754 non-null float64 latest_three_month_loan 4754 non-null float64 latest_six_month_loan 4754 non-null float64 history_suc_fee 4754 non-null float64 history_fail_fee 4754 non-null float64 latest_one_month_suc 4754 non-null float64 latest_one_month_fail 4754 non-null float64 loans_long_time 4754 non-null float64 loans_credit_limit 4754 non-null float64 loans_credibility_limit 4754 non-null float64 loans_org_count_current 4754 non-null float64 loans_product_count 4754 non-null float64 loans_max_limit 4754 non-null float64 loans_avg_limit 4754 non-null float64 consfin_credit_limit 4754 non-null float64 consfin_credibility 4754 non-null float64 consfin_org_count_current 4754 non-null float64 consfin_product_count 4754 non-null float64 consfin_max_limit 4754 non-null float64 consfin_avg_limit 4754 non-null float64 latest_query_day 4754 non-null float64 loans_latest_day 4754 non-null float64 reg_preference_for_trad 4754 non-null int64 latest_query_time_month 4754 non-null float64 latest_query_time_weekday 4754 non-null float64 loans_latest_time_month 4754 non-null float64 loans_latest_time_weekday 4754 non-null float64 dtypes: float64(73), int64(12) memory usage: 3.1 MB ``` 85列属性中，有73列是float型，12列是int型，并没有字符串型的属性，所以无需做额外处理。 而且可以看到每个特征都没有缺失值。也可以用另一个函数查看数据中缺失值的个数： ```python data.isnull().sum(axis=0) ``` 输出： ``` low_volume_percent 0 middle_volume_percent 0 take_amount_in_later_12_month_highest 0 trans_amount_increase_rate_lately 0 trans_activity_month 0 trans_activity_day 0 transd_mcc 0 trans_days_interval_filter 0 trans_days_interval 0 regional_mobility 0 repayment_capability 0 is_high_user 0 number_of_trans_from_2011 0 first_transaction_time 0 historical_trans_amount 0 historical_trans_day 0 rank_trad_1_month 0 trans_amount_3_month 0 avg_consume_less_12_valid_month 0 abs 0 top_trans_count_last_1_month 0 avg_price_last_12_month 0 avg_price_top_last_12_valid_month 0 trans_top_time_last_1_month 0 trans_top_time_last_6_month 0 consume_top_time_last_1_month 0 consume_top_time_last_6_month 0 cross_consume_count_last_1_month 0 trans_fail_top_count_enum_last_1_month 0 trans_fail_top_count_enum_last_6_month 0 .. loans_org_count_behavior 0 consfin_org_count_behavior 0 loans_cash_count 0 latest_one_month_loan 0 latest_three_month_loan 0 latest_six_month_loan 0 history_suc_fee 0 history_fail_fee 0 latest_one_month_suc 0 latest_one_month_fail 0 loans_long_time 0 loans_credit_limit 0 loans_credibility_limit 0 loans_org_count_current 0 loans_product_count 0 loans_max_limit 0 loans_avg_limit 0 consfin_credit_limit 0 consfin_credibility 0 consfin_org_count_current 0 consfin_product_count 0 consfin_max_limit 0 consfin_avg_limit 0 latest_query_day 0 loans_latest_day 0 reg_preference_for_trad 0 latest_query_time_month 0 latest_query_time_weekday 0 loans_latest_time_month 0 loans_latest_time_weekday 0 Length: 85, dtype: int64 ``` 由于属性太多，这里没有显示全，可以将上述结果存起来，然后只输出有缺失值的列： ```python data_isnull = data.isnull().sum(axis=0) data_isnull[data_isnull != 0] ``` 输出： ``` Series([], dtype: int64) ``` series为空，说明所有列都是无缺失值的。 ## 2.属性转换 虽然读取的数据中所有属性的数据类型都是数值型的，但实际上可能存在一些类别型属性，比如status是0和1两个类别，但是pandas会将其处理成数值的0和1。需要对这种情况进行额外处理。 对于这种类型的数据有两种处理方式，一种是one-hot encoding，把属性的每个取值对应成类似于00001这种形式。比如status有两个取值0和1，那么就可以编码成01和10。sklearn中有相应的函数，也可以用pandas的get\_dummies(df)函数。另一种方式是label encoding，只要给每个属性的取值一个唯一的数字即可，比如某个属性是weekday，可以取周一到周日七个值，用label encoding就是0-7这七个数字即可。sklearn中也有相应的函数。 下面我们来看看这个数据中有没有需要转换的属性。 一般这种属性都是int型的，所以我们只看int型的那些属性都有多少个取值。在第一步中我们看到数据类型就是两种：float64和int64，可以用select\_dtypes函数选出那些int64的列： ``` import numpy as np column = data.select_dtypes(include=[np.int64]).columns print(column) for col in column: print(col) print(data[col].unique().shape) ``` 输出： ``` Index(['take_amount_in_later_12_month_highest', 'repayment_capability', 'is_high_user', 'historical_trans_amount', 'trans_amount_3_month', 'abs', 'avg_price_last_12_month', 'max_cumulative_consume_later_1_month', 'pawns_auctions_trusts_consume_last_1_month', 'pawns_auctions_trusts_consume_last_6_month', 'status', 'reg_preference_for_trad'], dtype='object') take_amount_in_later_12_month_highest (166,) repayment_capability (2390,) is_high_user (2,) historical_trans_amount (4524,) trans_amount_3_month (3524,) abs (1697,) avg_price_last_12_month (330,) max_cumulative_consume_later_1_month (863,) pawns_auctions_trusts_consume_last_1_month (572,) pawns_auctions_trusts_consume_last_6_month (2730,) status (2,) reg_preference_for_trad (5,) ``` 根据属性的取值个数和属性名称大致可以判定该属性是否需要转换。看起来is\_high\_user和reg\_preference\_for\_trad可能需要转换。 is\_high\_user只有两个取值，用value\_counts函数看看每个取值有多少个： ``` data['is_high_user'].value_counts() ``` 输出： ``` 0 4701 1 53 Name: is_high_user, dtype: int64 ``` is\_high\_user取值是0和1，只有两个取值的属性其实也无需转换。 再来看看另一个属性reg\_preference\_for\_trad： ``` 0 3405 1 1064 2 150 3 131 4 4 Name: reg_preference_for_trad, dtype: int64 ``` reg\_preference\_for\_trad有5个取值，如果要用one-hot encoding方式就要转换，如果用label encoding方式就无需转换。为了展示one-hot encoding的用法，这里我们用one-hot方式。 代码如下： ```python regcols = ['reg_preference_for_trad_' + str(i) for i in range(5)] print(regcols) tmpdf = pd.get_dummies(data['reg_preference_for_trad'].replace('nan', np.nan)) tmpdf.columns = regcols data[regcols] = tmpdf ``` 这样就多出了5列，分别代表0-4这4种取值。 ``` ['reg_preference_for_trad_0', 'reg_preference_for_trad_1', 'reg_preference_for_trad_2', 'reg_preference_for_trad_3', 'reg_preference_for_trad_4'] ``` ## 3.剔除不相关特征 由于属性列太多，不方便一一解释说明（况且我也不知道每个字段的含义ε=(´ο｀\*)))）。属性列这么多，是不是都有用呢？这就跟我们的目标有关了。从之前的实验可以知道，我们希望预测用户贷款是否会逾期（对应的字段就是status），所以可以看每个属性跟status是否有关。 在这之前需要将X和y提取出来： ``` y = data['status'] X = data.drop(['status'], axis=1) ``` sklearn提供了特征选择的模块feature\_selection，里面包含了一些函数，这里使用的是特征方差提取方式，默认方差为0的特征会删除。 ``` from sklearn.feature_selection import VarianceThreshold selector = VarianceThreshold() print(X.shape) X = selector.fit_transform(X) print(X.shape) ``` 输出： ``` (4754, 89) (4754, 89) ``` 没有剔除任何特征。。说明这里面每个特征多少还是有点用处，只是用处大小的不同罢了。 可以输出每个特征的方差看看： ``` selector.variances_ ``` ``` array([1.72347223e-03, 2.09733428e-02, 1.53943134e+07, 4.81481182e+05, 3.87692950e-02, 2.89599675e-02, 2.00186059e+01, 5.15710959e+02, 2.71270743e+02, 7.92284964e-01, 2.72612789e+09, 1.10242173e-02, 1.01111554e+02, 2.19082523e+08, 1.02694235e+11, 9.93174477e+03, 6.96454281e-02, 1.03500977e+10, 1.93296547e+00, 7.29256913e+08, 1.22878638e-01, 5.86439064e+05, 9.86150936e-03, 2.83157843e+01, 1.67784749e+02, 2.97955539e+01, 1.72043674e+02, 5.03120265e+00, 3.64002171e+00, 1.98057119e+01, 2.25829209e+01, 1.39411696e+11, 1.16906146e+08, 3.22683811e+01, 2.28337441e-01, 4.37714017e+07, 7.94572765e+08, 4.06031438e-02, 2.88337722e+05, 3.63453287e+02, 2.51783949e+03, 1.63248201e+01, 4.78827044e+01, 1.36140674e+01, 6.51328333e+00, 1.22335903e+02, 2.02883578e+01, 5.63612962e+01, 8.29306545e+01, 3.62433789e+03, 4.68725564e+00, 5.84720597e+02, 4.51090812e+02, 9.62979328e+00, 5.40022384e+01, 8.46934738e+00, 2.86055044e+01, 2.15115553e+00, 1.16604483e+01, 1.15677503e+02, 8.68487650e+02, 6.08384690e+02, 3.63337054e+00, 1.43106069e+01, 1.23543567e+03, 4.71113041e+05, 1.10618434e+02, 2.86055044e+01, 3.29804734e+01, 2.10664740e+06, 5.13126727e+05, 5.58729026e+07, 1.98297162e+02, 8.46934738e+00, 1.11852954e+01, 1.92755652e+08, 3.39896154e+07, 1.33199490e+03, 2.83489836e+03, 4.72394037e-01, 1.77858966e+00, 3.73234062e+00, 8.92465960e+00, 3.59356702e+00, 2.03240714e-01, 1.73719927e-01, 3.05568245e-02, 2.67964236e-02, 8.40688770e-04]) ``` 有些特征的方差很大，有些特征的方差很小，其实看看特征的范围就大概能理解这一点了，方差大的特征大多是因为取值范围大，也不能完全靠这个说明特征与标签的相关性大小。可以设置阈值剔除一些不重要的特征，这里就不剔除了。 ## 4.模型应用 根据之前的实验，选出一个模型看看效果即可。这里选择用虽然简单但效果好的逻辑回归模型。 ```python def get_metric(clf, X, y_true): y_pred = clf.predict(X) y_proba = clf.predict_proba(X) acc = metrics.accuracy_score(y_true, y_pred) p = metrics.precision_score(y_true, y_pred) r = metrics.recall_score(y_true, y_pred) f1 = metrics.f1_score(y_true, y_pred) fpr, tpr, thresholds = metrics.roc_curve(y_true, y_proba[:, 1]) auc = metrics.auc(fpr, tpr) return acc, p, r, f1, fpr, tpr, auc from sklearn.model_selection import train_test_split from sklearn.linear_model import LogisticRegressionCV import matplotlib.pyplot as plt from sklearn import metrics from sklearn.preprocessing import StandardScaler X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=2018) scaler = StandardScaler() X_train = scaler.fit_transform(X_train) X_test = scaler.fit_transform(X_test) clf = LogisticRegressionCV(class_weight='balanced', max_iter=1000) clf.fit(X_train, y_train) acc, p, r, f1, fpr_train, tpr_train, auc_train = get_metric(clf, X_train, y_train) print("train accuracy:{:.2%}, precision:{:.2%}, recall:{:.2%}, F1:{:.2}".format(acc, p, r, f1)) acc, p, r, f1, fpr_test, tpr_test, auc_test = get_metric(clf, X_test, y_test) print("test accuracy:{:.2%}, precision:{:.2%}, recall:{:.2%}, F1:{:.2}".format(acc, p, r, f1)) plt.figure() lw = 2 plt.plot(fpr_train, tpr_train, color='darkorange', lw=lw, label='train (AUC:%0.2f)' % auc_train) plt.plot(fpr_test, tpr_test, color='cornflowerblue', lw=lw, label='test (AUC:%0.2f)' % auc_test) plt.plot([0, 1], [0, 1], color='navy', lw=lw, linestyle='--') plt.xlim([0.0, 1.0]) plt.ylim([0.0, 1.05]) plt.xlabel('False Positive Rate') plt.ylabel('True Positive Rate') plt.title('Receiver operating characteristic of LR') plt.legend(loc="lower right") plt.show() ``` 输出： ``` train accuracy:76.28%, precision:52.01%, recall:69.78%, F1:0.6 test accuracy:72.32%, precision:46.51%, recall:66.85%, F1:0.55 ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://codingling.gitbook.io/study/course-note/ji-xun-ying/suan-fa-shi-jian-2.1-shu-ju-yu-chu-li.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.