Python for data extraction

Data extraction is a requirement that analysts often encounter in their daily work. Such as the loan amount of a certain user, the total interest income in a certain month or quarter, the amount and number of loans in a certain period of time, the number of loans greater than 5,000 yuan, etc. This article introduces how to extract data according to specific dimensions or conditions through python to complete the data extraction requirements.

Preparation work

#The first step is preparation work, import the library you need to use, read and create a data table named loandata .

import numpy as np
import pandas as pd
loandata=pd.DataFrame(pd.read_excel('loan_data.xlsx'))

Set index field

Before starting to extract data, set the member_id column as an index field. Then start extracting data.

Loandata = loandata.set_index('member_id')

Extract information by row

The first step is to extract data by row, such as extracting a user's information. The following uses the ix function to extract the user information of member_id which is 1303503.

loandata.ix[1303503]

Extract information by columns

The second step is to extract data by column, for example, extract all the information in the user's working age column. The following is the specific code and extraction results, which displays the working age information of all users.

loandata.ix[:,'emp_length']

Extract information by rows and columns

The third step is to extract information by rows and columns. Put the query conditions in the previous two parts together to query the specific information of a specific user. The following is the query for the user whose member_id is 1303503emp_lengthInformation.

loandata.ix[1303503,'emp_length']

Continue to add conditions based on the previous ones, and add a row to query the loan amount information of two specific users at the same time. The specific code and query results are as follows. The code amounts for both users are listed separately in the results.

loandata.ix[[1303503,1298717],'loan_amnt']

Add the sum function after the previous code to calculate the result and, the same query is performed for the loans of two specific users. The summary value of the loan amount is directly given in the following results.

loandata.ix[[1303503,1298717],'loan_amnt'].sum()

In addition to adding query conditions for rows, you can also add query conditions for columns. The following code The loan amount and annual income of a specific user are queried, and the results of these two fields are displayed in the results.

loandata.ix[1303503,['loan_amnt','annual_inc']]

Query of multiple columns can also be calculated by adding sum after the previous code Function, sums the two fields of the user's loan amount and annual income, and displays the result.

loandata.ix[1303503,['loan_amnt','annual_inc']].sum()

##Extract information for a specific date

Another very common requirement in data extraction is to summarize and extract data by date dimension, such as monthly and quarterly summary data extraction and data extraction by specific time periods, etc. .

设置索引字段

首先将索引字段改为数据表中的日期字段，这里将issue_d设置为数据表的索引字段。按日期进行查询和数据提取。

loandata = loandata.set_index('issue_d')

按日期提取信息

下面的代码查询了所有2016年的数据。

loandata['2016']

在前面代码的基础上增加月份，查询所有2016年3月的数据。

loandata['2016-03']

继续在前面代码的基础上增加日期，查询所有2016年6月16日的数据。

loandata['2016-06-16']

除了按单独日期查询以外，还可以按日期段进行数据查询，下面的代码中查询了所有2016年1月至5月的数据。下面显示了具体的查询结果，可以发现数据的日期都是在1-5月的，但是按日期维度显示的，这就需要我们对数据按月进行汇总。

loandata['2016-01':'2016-05']

按日期汇总信息

Pandas中的resample函数可以完成日期的聚合工作，包括按小时维度，日期维度，月维度，季度及年的维度等等。下面我们分别说明。首先是按周的维度对前面数据表的数据进行求和。下面的代码中W表示聚合方式是按周，how表示数据的计算方式，默认是计算平均值，这里设置为sum，进行求和计算。

loandata.resample('W',how=sum).head(10)

将W改为M，数据变成了按月聚合的方式。计算方式依然是求和。这里需要说明的是resample函数会显示出所有连续的时间段，例如前面按周的聚合操作会显示连续的周日期，这里的按月操作则会在结果中显示连续的月，如果某个时间段没有数据，会以NaN值显示。

loandata.resample('M',how=sum)

将前面代码中的M改为Q，则为按季度对数据进行聚合，计算方式依然为求和。从下面的数据表中看，日期显示的都是每个季度的最后一天，如果希望以每个季度的第一天显示，可以改为QS。

loandata.resample('Q',how=sum)

将前面代码中的Q改为A，就是按年对数据进行聚合，计算方式依然为求和。

loandata.resample('A',how=sum)

前面的方法都是对整个数据表进行聚合和求和操作，如果只需要对某一个字段的值进行聚合和求和，可以在数据表后增加列的名称。下面是将贷款金额字段按月聚合后求和，并用0填充空值。

loandata['loan_amnt'].resample('M',how=sum).fillna(0)

在前面代码的基础上再增加一个数值字段，并且在后面的计算方式中增加len用来计数。在下面的结果中分别对贷款金额和利息收入按月聚合，并进行求和和计数计算

loandata[['loan_amnt','total_rec_int']].resample('M',how=[len,sum])

有时我们需要只对某一时间段的数据进行聚合和计算，下面的代码中对2016年1月至5月的数据按月进行了聚合，并计算求和。用0填充空值。

loandata['2016-01':'2016-05'].resample('M',how=sum).fillna(0)

或者只对某些符合条件的数据进行聚合和计算。下面的代码中对于贷款金额大于5000的按月进行聚合，并计算求和。空值以0进行填充。

loandata[loandata['loan_amnt']>5000].resample('M',how=sum).fillna(0)

除了按周，月，季度和年以外，resample函数还可以按以下方式对日期进行聚合。

下面给出了具体的对应表和说明。

 

更多Python for data extraction相关文章请关注PHP中文网！