Patent Assignment Data Restructure

In order to restructure the current patent dataset, the data requires rigorous cleaning. The primary areas for improvement are:

McNair Project

Patent Assignment Data Restructure
Project Information
Project Title	Patent Data Restructure
Owner	Marcela Interiano
Start Date	201701
Deadline	201705
Keywords	Patent
Primary Billing
Notes
Has project status	Active
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

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 addrline1 and addrline2 columns include post code, city and state information while the state, post code and country columns may have missing values. Besides, some city records also include post code and country information.

The basic idea to extract information from addrline1 and addrline2 is to search for post code following a specific pattern using regular expression. The state information is always ahead of post code.

U.S. postcode is like [five digits - four digits]. In this way, I created a table named 'ptoassigneend_missus'. Then, using the method above to extract useful address information.

The SQL code is as follows:

UPDATE ptoassigneend_missus SET postcode_city= SUBSTRING(city, '\d{5}[-]\d{4}') WHERE city ~* '.*\d{5}[-]\d{4}.*';

UPDATE ptoassigneend_missus SET state_city = SUBSTRING(city, '\w{2,}\s{0,}\d{5}[-]\d{4}') WHERE city ~* '.*\s{1,}\w{2}\s{0,}\d{5}[-]\d{4}.*';

UPDATE ptoassigneend_missus SET city_city = SUBSTRING(city, '\w{3,}\s{0,1}\w{0,}\s{0,}[,]') WHERE city ~* '.*\w{2}\s{1,}\d{5}[-]\d{4}.*';

From addrline1, addrline2 and city, I extracted city, post code and state respectively and stored in 'city_addr1', 'city_addr2', 'city_city'.