Python Crawler Data Processing __python

Source: Internet
Author: User

First, understand the following functions

Set variable length () function char_length () replace () function max () function
1.1. Setting variable SET @ variable name = value

Set @address = ' China-Shandong province-Liaocheng-Shen County ';
Select @address

1.2, Length () function char_length () function difference

Select Length (' A '),
char_length (' a ')
, Length (' Middle '),
char_length (' Medium ')

1.3. Replace () function and length () function combination

Set @address = ' China-Shandong province-Liaocheng-Shen County ';
Select @address
, replace (@address, '-', ')
as Address_1, Length (@address)
as LEN_ADD1, Length (@ Address, '-', ')
as len_add2, Length (@address)-length (replace (@address, '-', ')) as _count

ETL Cleaning field when there is a clear separator how to determine the new data table add several split out of the field

Calculates the maximum number of characters in the Com_industry to determine that the maximum value of a few fields is increased by +1, which can be split into several fields. This table is 3 so you can split 4 industry fields--4 industry levels.

Select Max (Length (com_industry)-length (replace (com_industry, '-', ')) as _max_count from
Etl1_socom_data

1.4. Set Variable Substring_index () string intercept function usage

Set @address = ' China-Shandong province-Liaocheng-Shen County ';
Select 
substring_index (@address, '-', 1) as,
Substring_index (Substring_index (@address, '-', 2), '-',-1) As Province,
Substring_index (Substring_index (@address, '-', 3), '-', -1) as City,
Substring_index (@address, '- ', -1) as District

1.5. Conditional Judgment function case when
Case when then when then else value end as field name

Select Case when 89>101 then ' is greater than ' else ' less than ' end as Betl1_socom_data
second, kettle conversion Etl1 Cleaning

The first step is to build the table in the video
The field index does not have the index algorithm proposed to enhance query efficiency with btree algorithm

2.1.kettle FileName: trans_etl1_socom_data
2.2. Including controls: Table input >>> table output
2.3. Data Flow direction: S_socom_data>>>>etl1_socom_data

Kettle Conversion 1 screenshot

2.4, table input 2.4, SQL script Initial cleaning com_district and Com_industry fields

Select A.*, case when com_district like '% industry ' or com_district like '%/com_district like '% Yukon ' then null else Com_distri CT End as Com_district1, case when com_district like '% Karma ' or com_district like '%/com_district like '% fertile ' then concat ( Com_district, '-', com_industry) Else Com_industry end as com_industry_total, replace (COM_ADDR, ' Address: ', ') as COM_ADDR1, Replace (Com_phone, ' Tel: ', ') as Com_phone1, replace (com_fax, ' Fax: ', ') as COM_FAX1, replace (Com_mobile, ' Mobile: ', ') as Com_ Mobile1, replace (com_url, ' url: ', ') as COM_URL1, replace (Com_email, ' mailbox: ', ') as COM_EMAIL1, replace (Com_contactor, ' Contact person: ', ' as Com_contactor1, replace (com_emploies_nums, ' Number of companies: ', ') as COM_EMPLOIES_NUMS1, replace (Com_reg_capital, ' Registered capital: Million ', ' as Com_reg_capital1, replace (com_type, ' economic type: ', ') as Com_type1, replace (com_product, ' Company product: ', ') as Com_ Product1, replace (Com_desc, ' Company Profile: ', ') as COM_DESC1 from S_socom_data as a

2.5, table output

Table Output Settings considerations


Precautions:
① involving crawler incremental operations do not tick the crop table option
② Data connection problem Select the database in which the table is located in the table output
③ field mapping problem make sure that the fields in the data flow correspond to the number of fields in the physical table consistent three, kettle conversion Etl2 Cleaning

First build the Table Add 4 fields demo steps in the video
The field index does not have the index algorithm proposed to enhance query efficiency with btree algorithm

Field split cleaning mainly for new com_industry generated by ETL1
3.1.kettle FileName: trans_etl2_socom_data
3.2. Including controls: Table input >>> table output
3.3. Data Flow direction: Etl1_socom_data>>>>etl2_socom_data
Precautions:
① involving crawler incremental operations do not tick the crop table option
② Data connection problem Select the database in which the table is located in the table output
③ field mapping problem ensure that the field in the data flow corresponds to the number of fields in the physical table consistent

Kettle Conversion 2 screenshot

3.4, the SQL script to split the Com_industry to complete all fields cleaning registration funds field time relationship did not perform a detailed resolution of the adjustment code can be

Select a.*, Case #行业为 ' value is set to null when length (com_industry) =0 then null #其他的取第一个-delimiter before else Substring_index (com_industry, '-', 1 End as Com_industry1, case length (com_industry)-length (replace (com_industry, '-', ') =0 then null # ' Transport, warehousing and postal services- ' This value industry 2 is also set to null when length (com_industry)-length (replace (com_industry, '-', ')) =1 and length Substring_index (com_ Industry, '-', -1) =0 then null when length (com_industry)-length (replace (com_industry, '-', ')) =1 then-Substring_index ( Com_industry, '-', -1 else Substring_index (Substring_index (com_industry, '-', 2), '-', -1) end as Com_industry2 Length (com_industry)-length (replace (com_industry, '-', ')) <=1 then null when length (com_industry)-length (replace (Com_industry, '-', ')) =2 then Substring_index (com_industry, '-', -1) Else Substring_index (Substring_index, '-', 3), '-', -1 "end as Com_industry3, Case Length (com_industry)-length (replace (com_industry, '-', ')) <=2 then null else substring_ Index (Com_industry, '-', -1) end as Com_Industry4 from Etl1_socom_data as a 
Four, the cleaning effect quality inspection 4.1 crawler data source data and Web site data match

If the work itself is a crawler and data processing in the process, the crawl has actually been judged, this step can be omitted, if the docking upstream reptile colleagues, this step to judge first, otherwise cleaning is not diligent, generally require the crawler colleagues to store the requested URL for data quality 4.2 Calculation of data source of reptile and the quantity of each ETL Clean data table

Note: 3 table data should be equal in SQL script without aggregation filtering

4.2.1, SQL query The following table I am in the same database if you do not follow the same database from the database name that contains the table
Do not recommend the use of data when the volume is large

Select COUNT (1) from S_socom_data
UNION ALL
Select COUNT (1) from Etl1_socom_data
UNION ALL
Select Count (1) from Etl2_socom_data

4.2.2 The total output of the table according to the Kettle conversion after execution
Kettle Table Output Total data amount 4.3 view etl Cleaning quality

Ensure that the first two steps are correct, data processing is responsible for the ETL cleaning work self-examination start the field to clean the data source write a script check SOCOM site is mainly to the region and industry has been cleaned to other fields do replace extra field processing, so take a script check,
Find Page_url and website data for verification

Where this is written to make it easy to see how a field is cleaned

SELECT * from 
etl2_socom_data 
where com_district is null and length (com_industry)-length (replace (com_industry ,

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.