Patent Assignment Data Restructure
In order to restructure the current patent dataset, the data requires rigorous cleaning. The primary areas for improvement are:
| 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. | |
- 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
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
Restructure Address Information
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
Extract Address Information
1. Introduction
- Five features (addrline1, addrline2, city, country, postcode) in the table contain address information.
- Features addrline1, addrline2 and city are not cleaned. They have city, country and postcode information.
- The object of this project is to extract city, country and postcode information from the three features above.
- By now, we only focus on cleaning American patents.
2. Postcode(U.S.)
U.S. post code follows the pattern [five digits - four digits]. In this way, U.S. patents can be extracted by searching for post code with regular expression
'(^|\s)\d{5}-\d{4}($|\s)'
For example,
city | postcode_city 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 post code records are stored in table ptoassigneend_missus_final.
SQL code is in:
E:/McNair/Projects/PatentAddress/RxPostcode.sql
3. State (U.S.)
Some patterns can be used to extract state information.
a. '[,] State Postcode'
The state and post code 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]'
SQL code:
WHEN addrline1 ~* 'CORP.*OF(\s|$)' LTRIM(RTRIM(SUBSTRING(regexp_replace(SUBSTRING(addrline1, 'CORP.*OF.*'),'CORP.*OF', ),1,18)))
Examples:
A CORPORATION OF NY A CORPORATION OF TEXAS A CORPORATION OF NEW YORK A CORP. OF INDIANA A CORP. OF OR A CORPORATION OF IL P.O. BOX 5018 CORPORATE SECRETARY'S OFFICE A CORP. OF NEW YORK A CORP. OF OHIO A CORP. OF CA. A CORP. OF DE.
Noise exists: ('DELAWARE' and 'CA' both exist.)
2200 MISSION COLLEGE BOULEVARD SANTA CLARA, CA 95052-8119 A CORP. OF DELAWARE
The extracted state records are stored in the table ptoassigneend_missus_final.
SQL code is in:
E:/McNair/Projects/PatentAddress/RxPostcode.sql
4. City (U.S.)
Some patterns can be used to extract city information.
Three lists of samples extracted from addrline1, addrline2 and city are used to summarize the patterns. They are in
Z:/PatentAddress/
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:
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:
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/RxCity.sql
5. Output (Tables)
- ptoassigneend_allus
This table contains all the U.S. patents extracted from ptoassigneend table. The rule to generate this table is 'country = 'UNITED STATES' ' or 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) |
- ptoassigneend_missus_final
State and postcode information extracted from addrline1, addrline2 and city columns are stored in this table. See section 2 and 3.
This table is a subset of ptoassigneend_allus table.
Table "public.ptoassigneend_missus_final"
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_city | text |
postcode_addr1 | text |
postcode_addr2 | text |
state_city | text |
state_addr1 | text |
state_addr2 | 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'.
- ptoassigneend_missus_city_final
City information are extracted from addrline1, addrline2 and city columns and are stored in ptoassigneend_city table. See section 4.
This table is a subset of ptoassigneend_allus table.
Table "public.ptoassigneend_missus_city_final" 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) | city_addr1 | text | city_addr2 | text | city_city | text |
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'.
Issues
- Write functions to simplify SQL code.
- Condense Address Information
The address information extracted from addrline1, addrline2 and city is not consistent. For example, the postcode extracted from addrline1 may be different from that extracted from city.
Examples:
| postcode | postcode_addr1 | postcode_addr2 | postcode_city | 77042 | 77251-1407 | | | 83716-9632 | | 83707-0006 | | 90045 | 90080-0028 | |
(postcode_city is the postcode extracted from 'city'; postcode_addr1 is the postcode extracted from 'addrline1'; postcode_addr2 is the postcode extracted from 'addrline2'.)
- Inconsistency between 'addrline' and 'country'(To do)
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.
