Patent Assignment Data Restructure
| Patent Assignment Data Restructure | |
|---|---|
| Project Information | |
| Project Title | Patent Data Restructure |
| Owner | Marcela Interiano, Sonia Zhang |
| Start Date | 201701 |
| Deadline | 201705 |
| Keywords | Patent, Data |
| Primary Billing | |
| Notes | |
| Has project status | Active |
| Subsumes: | Patent Data (Wiki Page), Patent Data Cleanup - June 2016, Patent Data Extraction Scripts (Tool), USPTO Bulk Data Processing |
| Copyright © 2016 edegan.com. All Rights Reserved. | |
In order to restructure the current patent dataset, the data requires rigorous cleaning. The primary areas for improvement are:
- 1. Clean ptoassignment table to unique keys.
- 2. Clean ptoproperties to remove nonutility patents. The patent numbers currently include:
- 7 digit patent numbers
- application numbers
- unknown numbers that cannot be matched to patent numbers in the patent table
- 20090108066
- 20100007288
- 20090108066
- 20100110022
- Design and Reissue patents ('%D%' or '%RE%')
- alphanumeric character strings
- 3. Restructure address information in ptoassignee table to extract meaningful information
- 4. Verify that cleaned patent documentids correspond to patent numbers or application numbers in the patent table
- 5. Restructure address information in ptoassignment table
- 6. Transform structure of the dataset
Contents
- 1 Semester Plan
- 2 Data Cleanup Progress
- 3 Restructure Address Information (First Stage)
- 4 Restructure Address Information (Second Stage)
Semester Plan
The final deliverable for the semester is a table with the following structure:
Reel No | Frame No | Invention Title | Filing Date | Patent No | Application No | Publication No | Match By
The schema of the table will be:
Column | Type | Modifiers -----------------+-----------------------+----------- Reel No | integer | Frame No | integer | Invention Title | character varying(500)| Filing Date | date | Patent No | integer | Application No | integer | Publication No | integer | Match By | integer |
Currently the pto tables contain varying identifiers for one invention title that is involved in a reassignment. The purpose of the table is to have each identifier for an invention title listed in a single row, making it easy to track the invention through various transactions. The Match By column will inform the user which identifier should be used to match to other tables in the patent database.
Data Cleanup Progress
Patent Number Cleanup
The goal is to only have assignment records on utility patents. The patents in ptoproperty include alphanumerics, which represent reissue and design patents as well as mistakes in the data input. Additionally, the documentids include application numbers or ids and publication numbers. The ptoproperty table stores the patent ids as character strings.
First the duplicates were dropped from the ptoproperty table creating ptoproperty_cleaned.
SELECT COUNT(*) FROM (SELECT DISTINCT * FROM ptoproperty) As T; --27266638 SELECT COUNT(*) FROM ptoproperty_cleaned; --27266638
Next, the Reissue and Design patents were removed.
SELECT COUNT(*) FROM ptoproperty WHERE documentid LIKE 'RE%'; --38512
SELECT COUNT(*) FROM ptoproperty WHERE documentid LIKE 'D%'; --1128247
Finally, all letters were removed from the data, resulting in the final version of ptoproperty_cleaned.
The ptoproperty_cleaned table contains application numbers, publication numbers, and patent numbers. The patents may also have two distinct publication numbers based on the year in which it was published. Based on length of documentid, the three types of id numbers were separated into three tables.
DROP TABLE ptoproperty_patent; CREATE TABLE ptoproperty_patent AS SELECT * FROM ptoproperty_cleaned WHERE length(documentid) = 7; --8696149
ALTER TABLE ptoproperty_patent RENAME COLUMN documentid TO patentno;
DROP TABLE ptoproperty_app; CREATE TABLE ptoproperty_app AS SELECT * FROM ptopropertynd WHERE length(documentid) = 8; --11577028 ALTER TABLE ptoproperty_app RENAME COLUMN documentid TO appno;
Ptotracking Tables
The purpose of the ptotracking tables is to track the ownership of patents based on the update date and filing dates of the assignment. These tables can be used to add missing information or track further refined tables such as ptoproperty_patent.
Ptotracking takes the reelno, frameno, and documentid key from the ptoproperty_cleaned table and joins the update dates and recorded dates corresponding to the transactions.
DROP TABLE ptotracking; CREATE TABLE ptotracking AS SELECT M1.reelno, M1.frameno, M1.documentid, M2.last_update_date, M2.recorded_date FROM ptoproperty_cleaned M1, ptoassignmentnd M2 WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno); --8699074
Ptotracking2 adds the assignee to the transaction, allowing the user to track ownership of the entity and of the patent.
DROP TABLE ptotracking2; CREATE TABLE ptotracking2 AS SELECT M1.reelno, M1.frameno, M1.documentid, M2.name, M1.last_update_date, M1.recorded_date FROM ptotracking M1, ptoassigneend M2 WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno); --9613927
US ONLY Patent Assignee Table
Note: Table made for Julia by Marcela Interiano
Table made in the patent database using the USPTO assignment data.
The first step was to include the last_update_date in with the data from the ptoproperty table. The ptoproperty table contains only the filing date, which is not useful as we are looking for the current patent holders. The table ptoproperty_patent was used for the patent numbers as this table was cleaned to include only patent numbers, no application or publication numbers.
DROP TABLE ptoproperty_patent_update; CREATE TABLE ptoproperty_patent_update AS SELECT M1.reelno, M1.frameno, M1.patentno, M2.last_update_date FROM ptoproperty_patent M1, ptotracking2 M2 WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno) AND (M1.patentno = M2.documentid);
Next, the minimum update date was taken, dropping any repetitions or later dates for the same patent assignee.
DROP TABLE ptoproperty_patent_minupdate; CREATE TABLE ptoproperty_patent_minupdate AS SELECT reelno, frameno, patentno, min(last_update_date) FROM ptoproperty_patent_update GROUP BY reelno, frameno, patentno, last_update_date;
The US only assignee table was used to construct the final table.
DROP TABLE ptoassignee_us_patent; CREATE TABLE ptoassignee_us_patent AS SELECT M1.reelno, M1.frameno, M2.name, M1.patentno, M1.last_update_date FROM ptoproperty_patent_minupdate M1, ptoassigneend_us_cleaned M2 WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);
The table below was made to join through using Sonia's zip codes for the ptoassignee data to get patent numbers from reelno and frameno.
DROP TABLE ptoassignee_us_distinct; CREATE TABLE ptoassignee_us_distinct AS SELECT DISTINCT reelno, frameno, patentno FROM ptoassignee_us_patent GROUP BY reelno, frameno, patentno; --5391413
The total number of distinct patent numbers in the ptoassignee data for only US assignees is 2345763.
SELECT COUNT(*) FROM (SELECT DISTINCT patentno FROM ptoassignee_us_patent) AS P; --2345763
Restructure Address Information (First Stage)
Note: This section was worked on by Sonia Zhang
The dbase is patent.
The table is ptoassigneend.
SQL code and other things are in:
E:/McNair/Projects/PatentAddress
IO files are on the dbase server in:
Z:/PatentAddress
Introduction
Currently done:
- Five features (addrline1, addrline2, city, state, postcode) in the table contain address information.
- Features addrline1, addrline2 and city are not cleaned. They can have suite, street, city, state and postcode information, or any combination of these
- The first object of this project is to extract postcode, state and city information from the three features above (Section 2.2.2).
- Then, we summarize the postcode, state and city information in the original table and those extracted from addresses to generate only one postcode, state and city for each record (Section 2.2.3).
- For now, we only focus on American patents.
Extract Address Information
So far these instructions apply to:
- Records identified as US (by zip code or country feature) ~3.5m records
- Those that well-formatted zip code (5-4) in addrline1, addrline2, and city (not postcode, etc) ~5,000 records
To get the record identified as US, we make the table:
- ptoassigneend_allus
This table contains all the U.S. patents extracted from ptoassigneend table. The rule to generate this table is 'country = 'UNITED STATES' ' if any of the features contains U.S. postcode.
Noise exists because of postcode errors.
The ptoassigneend_allus table may miss some U.S. patents which lose postcode records.
SQL code:
CREATE TABLE ptoassigneend_allus AS
SELECT * FROM ptoassigneend
WHERE city ~* '.*\d{5}[-]\d{4}.*' OR addrline1 ~* '.*\d{5}[-]\d{4}.*' OR
addrline2 ~* '.*\d{5}[-]\d{4}.*' OR postcode ~* '.*\d{5}[-]\d{4}.*' OR
country ~* 'UNITED STATES';
SELECT 3572682
Table "public.ptoassigneend_allus" Column | Type | Modifiers ----------| -----------------------|----------- reelno | integer | frameno | integer | name | character varying(500) | addrline1 | character varying(500) | addrline2 | character varying(500) | city | character varying(500) | state | character varying(500) | country | character varying(500) | postcode | character varying(80) |
Postcode
U.S. postcode should follow the pattern five digits - four digits. In this way, U.S. patents can be extracted by searching for postcode with regular expression.
Note: It is too risky to extract just 5 digit postcodes as these might street numbers, or other countries postcodes.
'(^|\s)\d{5}-\d{4}($|\s)'
For example,
city | postcode_extracted --------------------------------------|------------------- NEW YORK, NY 10022-3201 | 10022-3201 BEAVERTON, OREGON 97005-6453 | 97005-6453 SANTA BARBARA, CA 93130-3003 | 93130-3003 NEW YORK NY 10022-3201 | 10022-3201 SUNNYVALE, CA 94088-3453 | 94088-3453 94088-3470 | 94088-3470 CS 46510-35065 RENNES CEDEX | 46510-3506 NEW YORK, NY 10013-2412 | 10013-2412 OALKLAND, CA 94612-3550 | 94612-3550 OXFORD CT 06483-1011 | 06483-1011
The extracted postcode is in table ptoassigneend_us_extracted.
SQL code is in:
E:/McNair/Projects/PatentAddress/Functions.sql
State
The following patterns can be used to extract state information.
Note: a,b,c are to be run on city, addrline1, and addrline2. We use city as an example below.
a. ', State Postcode'
The state and postcode are always together, separated by a space. We can extract state information with regular expression
'([,]|[.])\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'
SQL Code:
WHEN city ~* '([,]|[.])\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'
THEN LTRIM(RTRIM(replace(regexp_replace(SUBSTRING(city, '[,]\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'), '\d{5}-\d{4}', ),',',)))
Examples:
city | state_city --------------------------------------|----------- NEW YORK, NY 10022-3201 | NY BEAVERTON, OREGON 97005-6453 | OREGON SANTA BARBARA, CA 93130-3003 | CA SUNNYVALE, CA 94088-3453 | CA NEW YORK, NY 10013-2412 | NY OALKLAND, CA 94612-3550 | CA SANTA CLARA, CA 95052-8090 | CA PEORIA, IL 61629-6490 | IL MIDVALE, UTAH 84047-1408 | UTAH OAKLAND, CA 94612-3550 | CA HARRISBURG, PA 17105-3608 | PA ROCHESTER, NY 14650-2201 | NY NEW YORK, NY 10013-2412 | NY HOUSTON, TEXAS 77256-6571 | TEXAS BROOKINGS, SOUTH DAKOTA 57006-0128 | SOUTH DAKOTA
b. '\s State(abbreviation) Postcode'
'(^|\s)\w{2}\s{1}\d{5}[-]\d{4}'
SQL code:
WHEN city ~* '(^|\s)\w{2}\s{1}\d{5}[-]\d{4}'
THEN regexp_replace(SUBSTRING(city, '\w{2}\s{1,}\d{5}[-]\d{4}'), '\d{5}-\d{4}', )
Examples:
NEW YORK NY 10022-3201 |NY WAUKEGAN IL 60085-2195 |IL
c. 'D.C.'
'D[.]C[.]\s\d{5}-\d{4}'
SQL code:
WHEN city ~* 'D[.]C[.]\s\d{5}-\d{4}'
THEN 'D.C.'
d. 'A CORP.* OF State'
This pattern is not reliable. When 'addrline1' looks this way, 'addrline2' always provide more detailed address information than 'addrline1'. Besides, a great part of state info extracted from 'A CORP.* OF [State]' doesn't match the state extracted from detailed 'addrline2'. In this way, we discard this pattern.
Examples:
addrline1 | addrline2 -----------------------------------------------------------------------------------------|------------------------------------------------------------------------------------- A CORP. OF NEW YORK | ONE LINCOLN FIRST SQUARE, ROCHESTER, NEW YORK, NY 14601-0054 A CORPORATION OF NY | 550 MADISON AVENUE, NEW YORK, NY 10022-3201 A NON-PROFIT CORP. OF WASHINGTON | 4225 ROOSEVELT WAY NE, STE. 303 SEATTLE, WA 98105-6099 A CORP. OF MN | 2611 NORTH SECOND ST., MINNEAPOLIS, MN 55411-1633 A CORPORATION OF DE | 612 WHEELER'S FARM RD. MILFORD, CT 06460-8719 A CORP. OF WA. | 411 FIRST AVENUE SOUTH SEATTLE, WASHINGTON 98104-2871 A CORPORATION OF OREGON | ONE BOWERMAN DRIVE, BEAVERTON, OREGON 97005-6453 A CORPORATION OF | ONE BOWERMAN DRIVE BEAVERTON, OR 97005-6453 A CORP. OF INDIANA | 4810 TECUMSEH LANE, POST OFFICE BOX 15190 EVANSVILLE, INDIANA 47716-0190 A CORPORATION OF PA | 7200 SUTER ROAD, COOPERSBURG, PA 18036-1299 A CORP. OF DE | SCARBORO & BEAR CREEK ROAD, P.O. BOX 2009 OAK RIDGE, TENNESSEE 37831-8014
If you're interested, the SQL code is
WHEN addrline1 ~* 'CORP.*OF(\s|$)' THEN LTRIM(RTRIM(SUBSTRING(regexp_replace(SUBSTRING(addrline1, 'CORP.*OF.*'),'CORP.*OF', ), 1, 18)))
- Summary
The extracted state records are stored in the table ptoassigneend_us_extracted.
SQL code is in:
E:/McNair/Projects/PatentAddress/Functions.sql
City
The following patterns can be used to extract city information.
a. '\s{2,} CityName [,] State Postcode'
SQL code:
CASE WHEN addrline1 ~* '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN LTRIM(RTRIM(REPLACE(SUBSTRING(SUBSTRING(addrline1, '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5} [-]\d{4}'),'.*[,]'),',',)))
Examples (in any of addrline1, addrline2, and city):
source |extracted ------------------------------------------------------------------------------------------------|--------------------- 800 CHRYSLER DR. EAST AUBURN HILLS, MICHIGAN 48326-2757 |AUBURN HILLS 550 MADISON AVENUE NEW YORK, NEW YORK 10022-3201 |NEW YORK P.O. BOX 15439 WILMINGTON, DE 19850-5439 |WILMINGTON
Some noise exists (just a little).
1313 N. MARKET STREET HERCULES PLAZAWILMINGTON, DE 19894-0001
b. '[,]\s{1,} CityName [,] State Postcode'
SQL code:
CASE WHEN addrline1 ~* '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN LTRIM(RTRIM(REPLACE(SUBSTRING(SUBSTRING(addrline1, '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'),'.*[,]'),',',)))
Example:
source |extracted ---------------------------------------------------------------------------|--------------------- 920 DISC DRIVE, SCOTTS VALLEY, CA 95067-0360 |SCOTTS VALLEY 550 MADISON AVENUE, NEW YORK, NY 10022-3201 |NEW YORK BALLSTON TOWER ONE 800 NORTH QUINCY STREET, ARLINGTON, VA 22217-5660 |ARLINGTON
c. 'CityName [,] State Postcode' (no leading spaces)
SQL code:
WHEN addrline1 ~* '^\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN REPLACE(SUBSTRING(SUBSTRING(addrline1, '^\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'), '.*[,]'),',', )
Examples:
PHILADELPHIA, PA 19104-3147 |PHILADELPHIA ROCHESTER, NY 14650-2201 |ROCHESTER
d. 'CityName State(abbreviation) Postcode' (no leading spaces)
SQL code:
WHEN addrline1 ~* '^\w{1,}\s{0,}\w{0,}\s{0,}\w{2}\s{0,}\d{5}[-]\d{4}'
THEN regexp_replace(SUBSTRING(addrline1, '^\w{1,}\s{0,1}\w{0,}\s{1,}\w{2}\s{0,}\d{5}[-]\d{4}'), '\w{2}\s\d{5}[-]\d{4}', )
Examples:
TARRYTOWN NY 10591-6706 |TARRYTOWN
e. Special cases
Examples:
BATON, ROUGE, LA 70809-4562 ('BATON ROUGH' is separated by a comma)
ST. PAUL, MIN 55133-3427 ('ST. PAUL' contains a dot)
QUINCY STREETARLINGTON, VA 22217-5660 (no space between street and city name :(
SQL code:
CASE WHEN addrline1 ~* 'BATON[,] ROUGE[,]\s{1}LA'
THEN 'BATON ROUGE'
WHEN addrline1 ~* 'ST[.]\s{1}PAUL'
THEN 'ST. PAUL'
WHEN addrline1 ~* 'ARLINGTON[,]\s{1}VA'
THEN 'ARLINGTON'
Noise:
- CityName State (full name) Postcode' (no leading spaces)
NEW YORK NEW YORK 10022-3201
This pattern can't be identified because of much noise.
- 'CityName Postcode' (no leading spaces)
LITTLE ELM 75068-3787 OAK RIDGE 37831-6498
This pattern can't be identified because of the noise:
MASSACHUSETTS 02780-7319 ('State'+'Postcode')
- no space between street and city name :(
BOX 87703CHICAGO, IL 60680-0703
SQL code is in:
E:/McNair/Projects/PatentAddress/Functions.sql
Output (Tables)
- ptoassigneend_us_extracted
Contain all the original features as well as city, state and postcode info extracted from features addrline1, addrline2 and city. See Section 2.2.1 for details.
Table "public.ptoassigneend_us_extracted"
Column | Type | Modifiers
---------------|------------------------|-----------
reelno | integer |
frameno | integer |
name | character varying(500) |
addrline1 | character varying(500) |
addrline2 | character varying(500) |
city | character varying(500) |
state | character varying(500) |
country | character varying(500) |
postcode | character varying(80) |
postqcode_city | text |
postcode_addr1 | text |
postcode_addr2 | text |
state_city | text |
state_addr1 | text |
state_addr2 | text |
city_addr1 | text |
city_addr2 | text |
city_city | text |
- postcode_city is the postcode extracted from 'city'
- postcode_addr1 is the postcode extracted from 'addrline1'
- postcode_addr2 is the postcode extracted from 'addrline2'
- state_city is the state name extracted from 'city'
- state_addr1 is the state name extracted from 'addrline1'
- state_addr2 is the state name extracted from 'addrline2'
- city_city is the city name extracted from 'city'
- city_addr1 is the city name extracted from 'addrline1'
- city_addr2 is the city name extracted from 'addrline2'
SQL code is in:
E:/McNair/Projects/PatentAddress/Functions.sql
Clean Address Info (Master Table)
Introduction
As mentioned in Section 2.2.2, city, state and postcode info are extracted from 'addrline1', 'addrline2' and 'city'. Original table also contains 'postcode', 'city' and 'state'. In this way, we have four candidates for city, state and postcode.
Info extracted from addresses and that in the original table are not necessarily consistent.
The object of this section is to pick out the best postcode, city and state for each record, and create a Master Table with original features and cleaned postcode, city, and state.
Postcode
Reminder: 'postcode_city' is the postcodes extracted from 'city'; 'postcode_addr1' is the postcodes extracted from 'addrline1'; 'postcode_addr2' is the postcodes extracted from 'addrline2'.
The 'postcode_city', 'postcode_addr1' and 'postcode_addr2' are all consistent!
Examples:
postcode_addr1 | postcode_addr2 ---------------|--------------- 90401-1708 | 90401-1708 37831-8243 | 37831-8243
The issue is the inconsistency between 'postcode' and ('postcode_city', 'postcode_addr1' and 'postcode_addr2').
- Inconsistency between 'postcode' and 'postcode_addr1'
Case 1: 'postcode_addr1' beats 'postcode' because 'addrline1' is detailed. For example:
addrline1 | postcode_addr1 | postcode -----------------------------------------------------------------------------------------------------------------------------------------------------------|----------------|----------- P.O. BOX 6 / 83707-0006 | 83707-0006 | 83716 BLDG. C01, M.S. A126 P.O. BOX 80028 LOS ANGELES, CA 90080-0028 | 90080-0028 | 90045 P. O. BOX 1407, HOUSTON, TEXAS 77251-1407 | 77251-1407 | 77042 841 3RD, LONGVIEW, WASHINGTON 98632-0189 | 98632-0189 | 95404 2210 W. OAKLAWN, 72762-6999 | 72762-6999 | 72765-2020 BLDG. CO1, M.S. A126, P.O. BOX 80028 LOS ANGELES, CA 90080-0028 | 90080-0028 | 90045 P.O. BOX 6 / 83707-0006 | 83707-0006 | 83716
- Inconsistency between 'postcode' and 'postcode_addr2'
Case 2: 'postcode_addr2' beats 'postcode' because 'addrline2' is detailed.
Example:
addrline2 | postcode_addr2 | postcode ------------------------------------------------------------------------------------------------------|----------------|---------- P.O. BOX 6 / 83707-0006 | 83707-0006 | 83716-9632 P.O. BOX 5800 - MS0161, ALBUQUERQUE, NEW MEXICO 87185-0161 | 87185-0161 | 87123-0161 P.O. BOX 674412, HOUSTON, TEXAS 77267-4412 | 77267-4412 | 77002 WASHINGTON, DC 20250-1400 | 20250-1400 | 61604 BOX 10076, HATTIESBURGH, MISSISSIPPI 39406-0076 | 39406-0076 | 39402 P.O. BOX 674412, HOUSTON, TEXAS 77267-4412 | 77267-4412 | 77032 P.O. BOX 6 / 83707-0006 | 83707-0006 | 83716-9632 P.O. BOX 6 / 83707-0006 | 83707-0006 | 83716-9632 P.O. BOX 27115-4191 | 27115-4191 | 27105-4191 P.O. BOX 674412, HOUSTON, TX 77267-4412 | 77267-4412 | 77002
Besides, I randomly picked some records and googled address and postcode. The results support 'postcode_addr2'.
Examples:
Micron Technology, Inc. 8000 South Federal Way Post Office Box 6 Boise 83707-0006 USA Tel: Ph: 208-368-4000
WebsterBank, NA 50 Kennedy Plaza 11th Floor Suite 110 (Mail Stop: PR-105) Providence, RI 02903
- Inconsistency between 'postcode' and 'postcode_city'
Case 3: 'postcode_city' beats 'postcode'.
city | state | postcode_city | postcode ----------------------|----------|---------------|---------- HAUPPAUGE, 11788-8847 | NEW YORK | 11788-8847 | 78759
- Output
SQL code is in:
E:/McNair/Projects/PatentAddress/Functions.sql
For records of which 'addrline1', 'addrline2' and 'city' don't contain postcode info, just clean the 'postcode' as the 'postcode_cleaned'
All the cleaned postcodes for U.S. patents are stored in ptoassigneend_us_cleaned (see feature postcode_cleaned).
State
Reminder: 'state_city' is the states extracted from 'city'; 'state_addr1' is the states extracted from 'addrline1'; 'state_addr2' is the states extracted from 'addrline2'.
The 'state_city', 'state_addr1' and 'state_addr2' are consistent.
Examples:
state_addr2 | state_city ------------|----------- ILLINOIS | ILLINOIS IL | IL IL | IL CT | CT MN | MN CA | CA
One reason for the inconsistency between 'state' and ('state_city', 'state_addr1' and 'state_addr2') is the co-existence of abbreviation and full form. So they are the same actually. Otherwise, 'state_city', 'state_addr1' and 'state_addr2' beat 'state' because they are detailed.
For records of which 'addrline1', 'addrline2' and 'city' don't contain state info, just take 'state' as the 'state_cleaned'.
- Output
SQL code is in:
E:/McNair/Projects/PatentAddress/Functions.sql
All the cleaned states for U.S. patents are stored in ptoassigneend_us_cleaned (see feature state_cleaned).
Note: We might want to convert state names to standard codes.
City
Reminder: 'city_city' is the city info extracted from 'city'; 'city_addr1' is the city info extracted from 'addrline1'; 'city_addr2' is the city info extracted from 'addrline2'.
The 'city_city', 'city_addr1' and 'city_addr2' are consistent.
Examples:
city_addr2 | city_city -------------|-------------- BOISE | BOISE THOMASVILLE | THOMASVILLE CARROLLTON | CARROLLTON CINCINNATI | CINCINNATI CINCINNATI | CINCINNATI PEORIA | PEORIA OAK RIDGE | OAK RIDGE CARROLLTON | CARROLLTON
'city_city', 'city_addr1' and 'city_addr2' beat 'city' because they are detailed.
For records of which 'addrline1', 'addrline2' and 'city' don't contain city info, just keep 'city' as the 'city_cleaned'.
- Output
SQL code is in:
E:/McNair/Projects/PatentAddress/Functions.sql
All the cleaned cities for U.S. patents are stored in ptoassigneend_us_cleaned. (see feature city_cleaned)
Output (Table)
- ptoassigneend_us_cleaned
Table "public.ptoassigneend_us_cleaned"
Column | Type | Modifiers
--------------------|------------------------|-----------
reelno | integer |
frameno | integer |
name | character varying(500) |
addrline1 | character varying(500) |
addrline2 | character varying(500) |
city | character varying(500) |
state | character varying(500) |
country | character varying(500) |
postcode | character varying(80) |
postcode_addr1 | text |
postcode_addr2 | text |
postcode_city | text |
state_addr1 | text |
state_addr2 | text |
state_city | text |
city_addr1 | text |
city_addr2 | text |
city_city | text |
postcode_cleaned | text |
postcode_f5_cleaned | text |
state_cleaned | text |
city_cleaned | text |
Feature postcode_cleaned, postcode_f5_cleaned (first five digits), state_cleaned and city_cleaned are cleaned postcode, state and city info.
Functions to Simplify SQL Code
Extraction
- Postcode
SQL function:
CREATE OR REPLACE FUNCTION ExtractPostcode(adr text) RETURNS text AS $$
BEGIN
RETURN SUBSTRING(adr, '\d{5}[-]\d{4}');
END;
$$ LANGUAGE plpgsql;
Explanation:
adr is the feature from which you want to extract postcode.
- State
SQL function:
CREATE OR REPLACE FUNCTION ExtractState(adr text) RETURNS text AS $$
SELECT
CASE WHEN adr ~* '([,]|[.])\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'
THEN LTRIM(RTRIM(replace(regexp_replace(SUBSTRING(adr, '[,]\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}[-]\d{4}'), '\d{5}-\d{4}', ),',',)))
WHEN adr ~* '(^|\s)\w{2}\s{1}\d{5}[-]\d{4}'
THEN regexp_replace(SUBSTRING(adr, '\w{2}\s{1,}\d{5}[-]\d{4}'), '\d{5}-\d{4}', )
WHEN adr ~* 'D[.]C[.]\s\d{5}-\d{4}'
THEN 'D.C.'
ELSE NULL END AS result;
$$ LANGUAGE SQL;
Explanation:
adr is the feature from which you want to extract state.
- City
SQL function:
CREATE OR REPLACE FUNCTION ExtractCity(adr text) RETURNS text AS $$
SELECT
CASE WHEN adr ~* '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN LTRIM(RTRIM(REPLACE(SUBSTRING(SUBSTRING(adr, '\s{2,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'),'.*[,]'),',',)))
WHEN adr ~* '[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN LTRIM(RTRIM(REPLACE(SUBSTRING(SUBSTRING(adr, '[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'),'[,].*[,]'),',',)))
WHEN adr ~* '^\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'
THEN REPLACE(SUBSTRING(SUBSTRING(adr, '^\w{1,}\s{0,}\w{0,}\s{0,}[,]\s{0,}\w{1,}\s{0,}\w{0,}\s{0,}\d{5}[-]\d{4}'), '.*[,]'),',', )
WHEN adr ~* '^\w{1,}\s{0,}\w{0,}\s{0,}\w{2}\s{0,}\d{5}[-]\d{4}'
THEN regexp_replace(SUBSTRING(adr, '^\w{1,}\s{0,1}\w{0,}\s{1,}\w{2}\s{0,}\d{5}[-]\d{4}'), '\w{2}\s\d{5}[-]\d{4}', )
WHEN adr ~* 'BATON[,] ROUGE[,]\s{1}LA'
THEN 'BATON ROUGE'
WHEN adr ~* 'ST[.]\s{1}PAUL'
THEN 'ST. PAUL'
WHEN adr ~* 'ARLINGTON[,]\s{1}VA'
THEN 'ARLINGTON'
ELSE NULL END AS result;
$$ LANGUAGE SQL;
Explanation:
adr is the feature from which you want to extract city.
With these functions, we can generate ptoassigneend_us_extracted, which is the final output in Section 2.2.1, with the following code:
CREATE TABLE ptoassigneend_us_extracted AS SELECT *, ExtractPostcode(city) postcode_city, ExtractPostcode(addrline1) postcode_addr1, ExtractPostcode(addrline2) postcode_addr2, ExtractState(city) state_city, ExtractState(addrline1) state_addr1, ExtractState(addrline2) state_addr2, ExtractCity(city) city_city, ExtractCity(addrline1) city_addr1, ExtractCity(addrline2) city_addr2 FROM ptoassigneend_missus;
All the SQL is in:
E:/McNair/Projects/PatentAddress/Functions.sql
Cleaning
- Clean Postcode
SQL function:
CREATE OR REPLACE FUNCTION PostcodeClean (text,text,text,text) RETURNS text AS $$
$pri1=$_[0];
$pri2=$_[1];
$pri3=$_[2];
$postcode=$_[3];
if ($pri1) {return $pri1;}
if ($pri2) {return $pri2;}
if ($pri3) {return $pri3;}
if ($postcode) {
if ($postcode = ~ '\d{5}-\d{4}') {return $postcode}
if ($postcode = ~ '\d{5}') {return $postcode}
}
return undef;
$$ LANGUAGE plperl;
Explanation:
$pri1 is the feature with the highest priority, which in this case (see Section 2.2.2.1 is postcode_addr1) $pri2 is the feature with the second highest priority, which in this case (see Section 2.2.2.1 is postcode_addr2) $pri3 is the feature with the third highest priority, which in this case (see Section 2.2.2.1 is postcode_city) $postcode is the feature postcode.
- Clean State
SQL function:
CREATE OR REPLACE FUNCTION StateClean (text,text,text,text) RETURNS text AS $$
$pri1=$_[0];
$pri2=$_[1];
$pri3=$_[2];
$state=$_[3];
if ($pri1) {return $pri1;}
if ($pri2) {return $pri2;}
if ($pri3) {return $pri3;}
if ($state) {return $state;}
return undef;
$$ LANGUAGE plperl;
Explanation:
$pri1 is the feature with the highest priority, which in this case (see Section 2.2.2.2 is state_addr1) $pri2 is the feature with the second highest priority, which in this case (see Section 2.2.2.2 is state_addr2) $pri3 is the feature with the third highest priority, which in this case (see Section 2.2.2.2 is state_city) $state is the feature state.
- Clean City
SQL function:
CREATE OR REPLACE FUNCTION CityClean (text,text,text,text) RETURNS text AS $$
$pri1=$_[0];
$pri2=$_[1];
$pri3=$_[2];
$city=$_[3];
if ($pri1) {return $pri1;}
if ($pri2) {return $pri2;}
if ($pri3) {return $pri3;}
if ($city) {return $city;}
return undef;
$$ LANGUAGE plperl;
Explanation:
$pri1 is the feature with the highest priority, which in this case (see Section 2.2.2.3 is city_addr1) $pri2 is the feature with the second highest priority, which in this case (see Section 2.2.2.3 is city_addr2) $pri3 is the feature with the third highest priority, which in this case (see Section 2.2.2.3 is city_city) $city is the feature city.
Issues
- Inconsistency between 'addrline' and 'country'
Example:
addrline2 | city | country
2882 SAND HILL ROAD MENLO PARK, CALIFORNIA 94025-7022 | TOKYO | JAPAN
2801 CENTERVILLE ROAD, P.O. BOX 15439 WILMINGTON, DE 19850-5439 | TOKYO | JAPAN
1-6, UCHISAIWAI-CHO 1-CHOME | CHIYODA-KU, TOKYO | JAPAN
MENLO PARK, CA 94025-7022 | TOKYO | JAPAN
| MINATO-KU, TOKYO 10585-8518 | JAPAN
1225 NORTH HIGHWAY 169, MINNEAPOLIS, MINNESOTA 55441-5058 | TOKYO | JAPAN
2882 SAND HILL ROAD MENLO PARK, CA 94025-7022 | TOKYO | JAPAN
3001 ORCHARD PARKWAY SAN JOSE, CALIFORNIA 95134-2088 | TOKYO 107 | JAPAN
3001 ORCHARD PARKWAY SAN JOSE, CA 95134-2088 | MINATO-KU, TOKYO 107 | JAPAN
3001 ORCHARD PARKWAY SAN JOSE, CALIFORNIA 95134-2088 | TOKYO 107 | JAPAN
3001 ORCHARD PARKWAY SAN JOSE, CA 95134-2088 | MINATO-KU, TOKYO 107 | JAPAN
- The post code and zip regex for other countries besides U.S. can be found here:
http://stackoverflow.com/questions/578406/what-is-the-ultimate-postal-code-and-zip-regex
Example:
"US", "\d{5}([ \-]\d{4})?"
"CA", "[ABCEGHJKLMNPRSTVXY]\d[ABCEGHJ-NPRSTV-Z][ ]?\d[ABCEGHJ-NPRSTV-Z]\d"
"DE", "\d{5}"
"JP", "\d{3}-\d{4}"
"FR", "\d{2}[ ]?\d{3}"
- The city feature needs to be standardized. For example, 'GRAND CAYMAN, CAYMAN ISLAND' and 'GRAND CAYMAN' indicate the same city.
- Both state name and its abbreviation exist.
Restructure Address Information (Second Stage)
Note: This section was worked on by Sonia Zhang
The dbase is patent.
The table is ptoassigneend.
SQL code and other things are in:
E:/McNair/Projects/PatentAddress/Cleaning_Step2.sql
IO files are on the dbase server in:
Z:/PatentAddress
To do
In no particular order:
- Remove city, state, zip, country from addrline1 & addrline2 to get clean addrlines.
- Maybe concatenate addrline1 and addrline to make addrline
- Identify clean data (e.g. City that is a city, zip that is a zip, state that is a state)
- By pattern, length, match to list
- Try some more patterns, perhaps with a slightly higher false positive rate, on the remaining uncleaned data
- Iterate!
Identify Clean Data
Output: ptoassigneend_us_identify0
As mentioned, the ptoassigneend_us_extracted is cleaned. Copy all the records in ptoassigneend_us_extracted to ptoassigneend_us_identify0.
Store remaining records in ptoassigneend_us_temp.
Output: ptoassigneend_us_identify1
The following section works on the remaining records which are stored in ptoassigneend_us_temp.
First, filter out records with city that is a city, zip that is a zip, state that is a state.
Note: The consistency between city and state or city and postcode is not checked in this section.
- ptoassigneend_us_citylist
Select clean city records in ptoassigneend_us_extracted and store them in ptoassigneend_us_citylist (775).
Since the city list is not long, I briefly cleaned the list by hand, and updated the ptoassigneend_us_citylist (730).
- zip that is a zip
Match the pattern 5-4 or 5 digits.
- state that is a state
Select distinct state records with
SELECT DISTINCT state FROM ptoassigneend_us_temp;
The output shows that all the records not null or not spaces are valid state names.
- city that is a city
One option to identify clean city is to find records that match ptoassigneend_us_citylist.
SQL Code:
SELECT city
FROM ptoassigneend_us_temp
WHERE city IN (
SELECT citylist
FROM ptoassigneend_us_citylist);
-- SELECT 2603422
- The table ptoassigneend_us_identify1 stores records that meet all the requirements above: zip with 5-4 or 5 digits, state not null or not spaces, and city in ptoasigneend_us_citylist.
SQL Code:
CREATE TABLE ptoassigneend_us_identify1 AS
SELECT *
FROM ptoassigneend_us_temp
WHERE city IN (
SELECT citylist
FROM ptoassigneend_us_citylist) AND
state IS NOT NULL AND state != AND
postcode ~* '\d{5}';
SELECT 2511356
Store remaining records in ptoassigneend_us_temp2.
Output: ptoassigneend_us_identify2
Part of 'city' contains comma. Remove comma, and then match 'city' with ptoassigneend_us_citylist.
SELECT *, replace(city, ',', ) clean_city FROM ptoassigneend_us_temp2; # SELECT 1055874
The output is ptoassigneend_us_identify2.
SQL code:
CREATE TABLE ptoassigneend_us_identify2 AS
SELECT *
FROM ptoassigneend_us_temp3
WHERE clean_city IN (
SELECT citylist
FROM ptoassigneend_us_citylist) AND
state IS NOT NULL AND state != AND
postcode ~* '\d{5}';
# SELECT 14508
Store remaining data (excluding data in ptoassigneend_us_identify0, ptoassigneend_us_identify1 & ptoassigneend_us_identify2) in ptoassigneend_temp3.
Clean Address: more patterns
Object: ptoassigneend_temp3
SQL code is in E:\McNair\Projects\PatentAddress\Cleaning_Step2.sql
Clean Postcode
Identifying five-digit postcode is risky because of the existence of P.O. BOX #, SUITE #, etc.
One option is to identify state and postcode together with the following SQL code: (take 'addrline1' as an example)
SQL function:
CREATE OR REPLACE FUNCTION ExtractPostcode2(adr text) RETURNS text AS $$
SELECT CASE WHEN (adr ~* '([,]|[.])\s\w{2,}\s{0,}\w{0,}\s{1,}\d{5}' OR
adr ~* '(^|\s)\w{2}\s{1}\d{5}') AND
NOT (adr ~* 'BO' OR adr ~* 'P[.]O') AND
NOT (adr ~* 'SUITE\s\d{5}')
THEN SUBSTRING(adr, '\d{5}')
ELSE NULL END AS result;
$$ LANGUAGE SQL;
Examples:
addrline1 | substring ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------- 1650 WEST BIG BEAVER ROAD TROY, MI 48084 | 48084 1114 AVE NY NY 10036 | 10036 GLENDALE, CA 91204 | 91204 14714 F. PERTHSHIRE 77079 | 14714 314 N. JACKSON STREET, JACKSON 49201 | 49201 LAGUNA HILLS, CA 92653 | 92653 1 ARAB, ALABAMA 35016 | 35016 1205 SIXTH ST. SOUTHEAST 33907 | 33907 767 FIFTH AVE., NEW YORK, NY 10153 | 10153
Even excluding the P.O. BOX # and SUITE #, noise exists.
After extracting postcode, next, the following function is used to get clean postcode.
The priority is 'postcode' if it is '\d{5}', postcode_addr1, postcode_addr2, postcode_city
CREATE OR REPLACE FUNCTION PostcodeClean2 (text,text,text,text) RETURNS text AS $$
$pri1=$_[0];
$pri2=$_[1];
$pri3=$_[2];
$postcode=$_[3];
if ($postcode) {return $postcode;}
if ($pri1) {return $pri1;}
if ($pri2) {return $pri2;}
if ($pri3) {return $pri3;}
return undef;
$$ LANGUAGE plperl;
The details and SQL function are in E:\McNair\Projects\PatentAddress\Cleang_Step2.sql
The output is table ptoassigneend_us_postex which include a new feature 'postcode_extracted'.
Clean city
'city' can be cleaned using the following patterns.
- Pattern 1: 'city' is like 'city name, state ID'
- Pattern 2: 'city' is like 'city name, state postcode (5 digits)'
- Pattern 3: 'city' is like 'city name,'
The SQL function is:
CREATE OR REPLACE FUNCTION ExtractCity2(adr text) RETURNS text AS $$
SELECT
CASE WHEN adr ~* '[,]\s{1,}\w{2}(\s{1,}|$|[.]|[,])' OR
adr ~* '[,]\s{1}\w{1}[.]\w{1}[.]'
THEN REPLACE(SUBSTRING(adr, '.*[,]'),',',)
WHEN adr ~* '[,].*\d{5}$'
THEN REPLACE(SUBSTRING(adr, '.*[,]'),',',)
WHEN adr ~* '.*[,]$'
THEN REPLACE(adr, ',', )
ELSE adr END AS result;
$$ LANGUAGE SQL;
The details and SQL function are in E:\McNair\Projects\PatentAddress\Cleang_Step2.sql
The output is table ptoassigneend_us_postex2 which include a new feature 'city_extracted' and 'postcode_extracted'.
CREATE TABLE ptoassigneend_us_postex2 AS SELECT *, ExtractCity2(city) city_extracted FROM ptoassigneend_us_postex;
Identify Clean Data (Round Two)
A new list of city is extracted in Section 4.3.2. This list, combined with 'ptoassigneend_us_citylist', creates a new city list 'ptoassigneend_us_citylist2' which can be used to identify clean data.
Since the city list is not long, I briefly cleaned the list by hand, and stored it in ptoassigneend_us_citylist2.
- Actually, we can buy a full list of U.S. cities online: https://www.uscitieslist.org/.
Output: ptoassigneend_us_identify3
Similar to Section 4.2, identify clean data that meets all the requirements: postcode_extracted with 5-4 or 5 digits, state not null or not spaces, and city_extracted in ptoasigneend_us_citylist2.
SQL Code:
CREATE TABLE ptoassigneend_us_identify3 AS
SELECT *
FROM ptoassigneend_us_postex2
WHERE city_extracted IN (
SELECT citylist
FROM ptoassigneend_us_citylist2) AND
state IS NOT NULL AND state != AND
postcode_extracted ~* '\d{5}';
SELECT 664524
- Output table: ptoassigneend_us_identify3
Table "public.ptoassigneend_us_identify3"
Column | Type | Modifiers
-------------------|------------------------|-----------
reelno | integer |
frameno | integer |
name | character varying(500) |
addrline1 | character varying(500) |
addrline2 | character varying(500) |
city | character varying(500) |
state | character varying(500) |
country | character varying(500) |
postcode | character varying(80) |
postcode_extracted | text |
city_extracted | text |
Remaining records are in table ptoassigneend_us_temp4.
Output: ptoassigneend_us_identify4
Some of the city records contain dots. Remove dots, and then match 'city' with ptoassigneend_us_citylist2. SQL Code:
CREATE TABLE ptoassigneend_us_identify4 AS
SELECT *
FROM ptoassigneend_us_temp5
WHERE city_extracted2 IN (
SELECT citylist
FROM ptoassigneend_us_citylist2) AND
state IS NOT NULL AND state != AND
postcode_extracted ~* '\d{5}';
SELECT 38
The remaining records are stored in ptoassigneend_us_temp5.
Output: ptoassigneend_us_identify5
One problem for the REMAINING records is that the postcode is missing.
If we relax the requirements for postcode, we'll get clean data (city that is a city, state that is a state) stored in ptoassigneend_us_identify4.
SQL code:
CREATE TABLE ptoassigneend_us_identify5 AS SELECT * FROM ptoassigneend_us_temp5 WHERE city_extracted2 IN ( SELECT citylist FROM ptoassigneend_us_citylist2) AND state IS NOT NULL AND state != ; SELECT 136958
Remaining records are in table ptoassigneend_us_temp6 (SELECT 239837).
Summary
Table Name | Records # ---------------------------|------------- ptoassigneend_allus | 3572605 ---------------------------|------------- ptoassigneend_us_identify0 | 5343 ptoassigneend_us_temp | 3567261 ptoassigneend_us_identify1 | 2511356 ptoassigneend_us_temp2 | 1055874 ptoassigneend_us_identify2 | 14508 ptoassigneend_us_temp3 | 1041366 ptoassigneend_us_identify3 | 664524 ptoassigneend_us_temp4 | 376835 ptoassigneend_us_identify4 | 38 ptoassigneend_us_temp5 | 376797 ptoassigneend_us_identify5 | 136958 ptoassigneend_us_temp6 | 239837
Union ptoassigneend_us_identify(0-4) to get ptoassigneend_us_identify_subtotal (3195769). 10.5% left in ptoassigneend_us_temp5.
ptoassigneend_us_identify5 doesn't contain clean passcode info, but contains clean city and state info. 6.7% data left in ptoassigneend_us_temp6.
Note:
About 60 records are missing. For example, the # of records in ptoassigneend_us_temp + # of records in ptoassigneend_us_identify0 != # ptoassigneend_allus.
