Patent Assignment Data Restructure

McNair Project

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

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

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. Tables

• ptoassigneend_allus

This table contains all the U.S. patents extracted from ptoassigneend table. The rule to extract this table is 'country = 'UNITED STATES' ' or any of the features contains U.S. postcode.

The SQL code is:

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)  |

The ptoassigneend_allus table may miss some U.S. patents.

• ptoassigneend_missus_final

State and postcode information are extracted from addrline1, addrline2 and city columns and are stored in ptoassigneend_missus_final table. See section 3 and 4.

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_city

City information are extracted from addrline1, addrline2 and city columns and are stored in ptoassigneend_city table. See section 5.

This table is a subset of ptoassigneend_allus table.

3. 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

'4. State (U.S.)

There are some patterns that can be used to extract city information.

• '(^|\s)CITY NAME[,] STATE POSTCODE'

The state and post code are always together, separated by a space. So we can also extract state information with regular expression too.

For example,

                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

• 'CITY NAME STATE(Abbreviation) POSTCODE'

For example:

NEW YORK NY 10022-3201
WAUKEGAN IL 60085-2195

The extracted state records are stored in table ptoassigneend_missus_final.

SQL code is in:

E:/McNair/Projects/PatentAddress/RxPostcode.sql

5. City (U.S.)

There are some patterns that 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/

• '\s{2,} CITY NAME[,]STATE POSTCODE'

800 CHRYSLER DR. EAST                                       AUBURN HILLS, MICHIGAN  48326-2757
550 MADISON AVENUE                                          NEW YORK, NEW YORK 10022-3201
P.O. BOX 15439                                              WILMINGTON, DE  19850-5439

• '[,]\s{1,} CITY NAME[,] STATE POSTCODE'

920 DISC DRIVE, SCOTTS VALLEY, CA 95067-0360
550 MADISON AVENUE, NEW YORK, NY 10022-3201
BALLSTON TOWER ONE 800 NORTH QUINCY STREET, ARLINGTON, VA   22217-5660

• 'CITY NAME [,] STATE POSTCODE' (no leading spaces)

PHILADELPHIA, PA 19104-3147
ROCHESTER, NY 14650-2201 

Feature city is quite different from addrline1 and addrline2. Some of the records just contain the city information. Some contain city, state and postcode information, only a small part of the records just contain street names.

TARRYTOWN NY 10591-6706
ESLOV
SANTA CLARA, CA 95052-8090
PEORIA, IL  61629-6490
S-190 70 FJARDHUNDRA

• Noise:

      800 NORTH QUINCY STREETARLINGTON, VA  22217-5660 (street name instead of city name)
	BATON, ROUGE, LA 70809-4562 (the city name is separated by a comma)
        ST. PAUL, MIN 55133-3427 (special)
IRVINE CA 92713-9658(no comma between city and state)
QUINCY STREETARLINGTON, VA 22217-5660 (no space between street and city name :(
BOX 87703CHICAGO, IL  60680-0703

SQL code is in:

E:/McNair/Projects/PatentAddress/CityPatterns.sql

6. Issues

• 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

For 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.

• Some state and country features don't match.

For 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

• Both state name and its abbreviation exist.

• Some records are 'A CORP. OF CA.' or 'A CORP. OF NY' or 'A CORPORATION OF CA', which may be another method to extract state information.

• '556627-0772 BERGFOTSGATAN 5 A'. This record is also included in the table!!!!