Changes

{{Project|Has project output=Data|Has sponsor=McNair ProjectsCenter

||Has keywords=Patent,Data|Has project status=ActiveSubsume|Does subsume=Patent Data (Wiki Page), Patent Data Cleanup - June 2016, Patent Data Extraction Scripts (Tool), USPTO Bulk Data Processing,

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.

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 separate 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;  DROP TABLE ptoproperty_pub; CREATE TABLE ptoproperty_pub AS SELECT * FROM ptopropertynd WHERE length(documentid) =Restructure Address Information11; --6217864 ALTER TABLE ptoproperty_pub RENAME COLUMN documentid TO pubno; All three tables contain the following information and differ only on the type of patent id.   Table "public.ptoproperty_patent" Column | Type | Modifiers -----------------+------------------------+----------- reelno | integer | frameno | integer | documentid | character varying(20) | country | character varying(500) | kind | character varying(3) | filingdate | date | invention_title | character varying(500) | ===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 In making the ptotracking table, it is important to note that the classification of documentids as B1 and B2 causes duplicates in the entries. B1 and B2 classifications mean that the patent was granted with and without a published application.  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 The document ids in the PTO assignment data had not yet been verified as matching to the main patent table in our database (psql patent). The document ids in the PTO assignment data are stored as character strings whereas the patents in the patent table are stored as integers. Unlike the ptoprpoperty_cleaned table, all patent numbers in the patent table are unique.  The following two tables were made in order to verify that the documentids in the ptoproperty_cleaned table match to the patent table.   DROP TABLE edcheck; CREATE TABLE edcheck AS SELECT CAST (documentid AS INT) FROM ptotracking2;   SELECT COUNT(DISTINCT documentid) FROM edcheck; --2343765  DROP TABLE edcheck2; CREATE TABLE edcheck2 AS SELECT M1.documentid, M2.patent FROM edcheck M1, patent M2 WHERE (M1.documentid = M2.patent); --2238305  DROP TABLE edcheck; CREATE TABLE edcheck AS SELECT DISTINCT documentid FROM ptotracking2; --2343765  DROP TABLE edcheck2; CREATE TABLE edcheck2 AS SELECT CAST(documentid AS INT)FROM edcheck; --2343765  DROP TABLE edcheck3; CREATE TABLE edcheck3 AS SELECT M1.documentid, M2.patent FROM edcheck2 M1, patent M2 WHERE M1.documentid = M2.patent; --2238305 Based on the iterations of these tables, we could conclude that our original patent data forms the majority of the patents undergoing reassignments or transactions. ===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 ===Current Assignee using Recorded Date=== Each assignment has three dates: filingdate, recorded_date, last_update_date. The filingdate corresponds to the filing of the assignment with the USPTO. The recorded_date is the date the transaction was recorded. The last_update_date is the date the USPTO verifies that the assignment still holds. In the ptoassignee_us_patent table, the last_update_date is used to find the current assignee.  Prior to Sonia's work with the ptoassignee table address data, the table ptoassignee_current was made using the most recent recorded_date. This method though is flawed given that additional transactions could have current previously that are still in effect as patents can have multiple assignees. These codes can be used for constructing similar tables using the address data Sonia has cleaned in the following sections of this project.  To begin with, the ptoproperty_patent table was cleaned to drop all duplicates. Then the table was matched with the assignee table.   DROP TABLE ptoassigneev1; CREATE TABLE ptoassigneev1 AS SELECT M1.reelno, M1.frameno, M1.documentid, M1.country, M1.filingdate, M2.last_update_date, M2.recorded_date FROM ptoproperty_patent2 M1, ptoassignmentnd M2 WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);   DROP TABLE ptoassigneev2; CREATE TABLE ptoassigneev2 AS SELECT M1.reelno, M1.frameno, M1.documentid, M2.name, M1.country, M1.last_update_date, M1.recorded_date, M2.addrline1, M2.addrline2, M2.city, M2.state, M2.postcode FROM ptoassigneev1 M1, ptoassigneend M2 WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno); --9634942 Once all the location and address fields from the ptoassignee table have been added to the ptoproperty_patent fields, the max recorded_date was identified from the ptoassignee_patent table and from ptoassigneev2 for comparison.   DROP TABLE datecheck; CREATE TABLE datecheck AS SELECT documentid, max(recorded_date) as recorded_date FROM ptoassignee_patent GROUP BY documentid; --2343765  DROP TABLE datecheck; CREATE TABLE datecheck AS SELECT documentid, max(recorded_date) as recorded_date FROM ptoassigneev2 GROUP BY documentid; --4374885  DROP TABLE ptoassignee_patent; CREATE TABLE ptoassignee_patent AS SELECT M1.reelno, M1.frameno, M1.documentid, M1.name, M1.last_update_date, M1.recorded_date, M2.addrline1, M2.addrline2, M2.city, M2.state, M2.country, M2.postcode FROM ptotracking2 M1, ptoassigneend M2 WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno); --16581236  DROP TABLE ptoassignee_current; CREATE TABLE ptoassignee_current AS SELECT M1.reelno, M1.frameno, M2.documentid, M2.recorded_date FROM ptoassignee_patent M1, datecheck M2 WHERE (M1.documentid = M2.documentid) AND (M1.recorded_date = M2.recorded_date); --6729698 A final version of the ptoassignee_current table was made using ptoassigneev2 given the larger pool of documentids included in the table by matching using documentid and recorded dates from datecheck.   DROP TABLE ptoassignee_current; CREATE TABLE ptoassignee_current AS SELECT M1.reelno, M1.frameno, M2.documentid, M1.name, M1.last_update_date, M2.recorded_date, M1.addrline1, M1.addrline2, M1.country, M1.city, M1.state, M1.postcode FROM ptoassigneev2 M1, datecheck M2 WHERE (M1.documentid = M2.documentid) AND (M1.recorded_date = M2.recorded_date); --4994869 These codes should be used to recreate this table using Sonia's updated address information. ===Matching Application and Publication Numbers===The ptoproperty_cleaned documentids to verify the kind of different patents as specified in the ptoproperty tables.  First the table ptopropertynd was made, including only the distinct documentids in ptoproperty_cleaned.   DROP ptopropertynd; CREATE TABLE ptopropertynd AS SELECT DISTINCT * FROM ptoproperty; --27266638 By creating this table, I also address the duplications caused by the kind XO. ===Final Table (name TBD)===  CREATE TABLE patpub AS (SELECT ptoproperty_patent.reelno, ptoproperty_patent.frameno, ptoproperty_patent.invention_title, ptoproperty_patent.patentno, ptoproperty_pub.pubno FROM ptoproperty_patent LEFT JOIN ptoproperty_pub ON (ptoproperty_patent.reelno = ptoproperty_pub.reelno) AND (ptoproperty_patent.frameno = ptoproperty_pub.frameno) AND (ptoproperty_patent.invention_title = ptoproperty_pub.invention_title) UNION SELECT ptoproperty_pub.reelno, ptoproperty_pub.frameno, ptoproperty_pub.invention_title, ptoproperty_patent.patentno, ptoproperty_pub.pubno FROM ptoproperty_patent RIGHT JOIN ptoproperty_pub ON (ptoproperty_patent.reelno = ptoproperty_pub.reelno) AND (ptoproperty_patent.frameno = ptoproperty_pub.frameno) AND (ptoproperty_patent.invention_title = ptoproperty_pub.invention_title));  CREATE TABLE patapp AS (SELECT ptoproperty_patent.reelno, ptoproperty_patent.frameno, ptoproperty_patent.invention_title, ptoproperty_patent.patentno, ptoproperty_app.appno FROM ptoproperty_patent LEFT JOIN ptoproperty_app ON (ptoproperty_patent.reelno = ptoproperty_app.reelno) AND (ptoproperty_patent.frameno = ptoproperty_app.frameno) AND (ptoproperty_patent.invention_title = ptoproperty_app.invention_title) UNION SELECT ptoproperty_app.reelno, ptoproperty_app.frameno, ptoproperty_app.invention_title, ptoproperty_patent.patentno, ptoproperty_app.appno FROM ptoproperty_patent RIGHT JOIN ptoproperty_app ON (ptoproperty_patent.reelno = ptoproperty_app.reelno) AND (ptoproperty_patent.frameno = ptoproperty_app.frameno) AND (ptoproperty_patent.invention_title = ptoproperty_app.invention_title));

E:/McNair/Projects/PatentAddress/RestructureAddressInfo(First Stage).sql

===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='''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.

=====2U. Postcode(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.

U.S. post code follows the pattern [five digits - four digits]. In this wayNote: It is too risky to extract just 5 digit postcodes as these might street numbers, U.Sor other countries postcodes. patents can be extracted by searching for post code with regular expression

city | postcode_citypostcode_extracted --------------------------------------|-------------------

The extracted post code records are stored postcode is in table ptoassigneend_missus_finalptoassigneend_us_extracted.

E:/McNair/Projects/PatentAddress/RxPostcodeRestructureAddressInfo(First Stage).sql

=====3. State (U.S.)=====

Some 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. '[,] State Postcode''''

'''a. ', State Postcode'''' The state and post code postcode are always together, separated by a space. We can extract state information with regular expression

'''d. 'A CORP.* OF [State]''''

SQL code: WHEN This pattern is not reliable. When 'addrline1 ~* 'CORP.*OF(\s|$)looks this way, 'addrline2' always provide more detailed address information than ' THEN LTRIM(RTRIM(regexp_replace(SUBSTRING(addrline1, 'CORP.*OF.*')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.

Noise exists: ( 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'DELAWARE' and 'CA' both existS FARM RD.) MILFORD, CT 06460-8719 2200 MISSION COLLEGE BOULEVARD SANTA CLARAA CORP. OF WA. | 411 FIRST AVENUE SOUTH SEATTLE, CA WASHINGTON 98104-2871 95052A CORPORATION OF OREGON | ONE BOWERMAN DRIVE, BEAVERTON, OREGON 97005-8119 6453 A CORPORATION OF | ONE BOWERMAN DRIVE BEAVERTON, OR 97005-6453 A CORP. OF DELAWARE 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_missus_finalptoassigneend_us_extracted.

E:/McNair/Projects/PatentAddress/RxPostcodeRestructureAddressInfo(First Stage).sql

====='''4. City (U.S.)'''=====

Some The following 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/

Examples(in any of addrline1, addrline2, and city): source |extracted ------------------------------------------------------------------------------------------------|---------------------

BATON, ROUGE, LA 70809-4562 ('BATON ROUGH' is separated by a comma) ST. PAUL, MIN 55133-3427 ('ST. PAUL' contains a dot)

MASSACHUSETTS 02780-7319 ('State '+'Postcode')

E:/McNair/Projects/PatentAddress/RxCity.sql ====='''5. Output RestructureAddressInfo(TablesFirst Stage)'''===== *'''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.sql

SQL code: CREATE TABLE ptoassigneend_allus AS SELECT * FROM ptoassigneend WHERE Contain all the original features as well as city ~* '.*\d{5}[-]\d{4}.*' OR , state and postcode info extracted from features addrline1 ~* '.*\d{5}[-]\d{4}.*' OR , addrline2 ~* 'and city.*\d{5}[-]\d{4}See Section 2.*' OR postcode ~* '2.*\d{5}[-]\d{4}1 for details.*' 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_finalptoassigneend_us_extracted"

postcode_city postqcode_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'

state_city SQL code 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'in: E:/McNair/Projects/PatentAddress/RestructureAddressInfo(First Stage).sql

This table is a subset of ptoassigneend_allus tableAs mentioned in Section 2. Table "public2.ptoassigneend_missus_city_final" Column | Type | Modifiers reelno | integer | frameno | integer | name | character varying(500) | 2, city, state and postcode info are extracted from 'addrline1 | character varying(500) | ', 'addrline2 | character varying(500) | ' and 'city | character varying(500) | '. Original table also contains 'postcode', 'city' and 'state'. In this way, we have four candidates for city, state | character varying(500) | country | character varying(500) | and 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 Info extracted from 'addrline1'; city_addr2 is addresses and that in the city name extracted from 'addrline2'original table are not necessarily consistent.

The address information Reminder: 'postcode_city' is the postcodes extracted from addrline1, addrline2 and 'city '; 'postcode_addr1' is not consistent. For example, the postcode postcodes extracted from 'addrline1 may be different from that '; 'postcode_addr2' is the postcodes extracted from city'addrline2'.  The 'postcode_city', 'postcode_addr1' and 'postcode_addr2' are all consistent!

| postcode | postcode_addr1 | postcode_addr2 | postcode_city ---------------| 77042 | 77251-1407 | |-------------- | 8371690401-9632 1708 | | 8370790401-0006 |1708 | 90045 | 9008037831-0028 8243 | |37831-8243

(postcode_city The issue is the inconsistency between 'postcode extracted from 'cityand ('postcode_city', '; postcode_addr1 is the postcode extracted from 'addrline1and '; postcode_addr2 is the postcode extracted from 'addrline2').)

* 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:  Schema E:/McNair/Projects/PatentAddress/RestructureAddressInfo(First Stage).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 ------------| Name ----------- ILLINOIS | Type ILLINOIS IL | OwnerIL 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/RestructureAddressInfo(First Stage).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 public -------------| ptoassigneend_city -------------- BOISE | table BOISE THOMASVILLE | researcherTHOMASVILLE public CARROLLTON | ptoassigneend_postcode CARROLLTON CINCINNATI | table CINCINNATI CINCINNATI | researcherCINCINNATI public PEORIA | ptoassigneend_state PEORIA OAK RIDGE | table OAK RIDGE CARROLLTON | researcherCARROLLTON '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

E:/McNair/Projects/PatentAddress/RestructureAddressInfo(First Stage).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/RestructureAddressInfo(First Stage).sql ==== Cleaning ==== * Clean PostcodeSQL 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 StateSQL 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 CitySQL 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 ====' '''

* ''' 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/RestructureAddressInfo(Second Stage).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 in Section 3, the ptoassigneend_us_extracted is clean. 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 ptoassigneend_us_temp. 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  Copy clean city records in ptoassigneend_us_extracted to 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 5d-4d 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 method to identify clean city is to find city 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 and 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 at the end. 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_us_temp3. === Clean Address: more patterns === Table: ptoassigneend_us_temp3  SQL code is in E:\McNair\Projects\PatentAddress\RestructureAddressInfo(Second Stage).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 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 Noise still exists. After extracting postcode, 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\RestructureAddressInfo(Second Stage).sql The output is table ptoassigneend_us_postex which include a new feature 'postcode_extracted'. ====Clean 'city'==== 'city' is cleaned using following patterns. *Pattern 1: 'city' ~ 'city name, state ID' *Pattern 2: 'city' ~ 'city name, state postcode (5 digits)' *Pattern 3: 'city' ~ '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\RestructureAddressInfo(Second Stage).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 clean 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 it by hand, and stored it in ptoassigneend_us_citylist2. * Actually, we can find 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 and 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 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. ===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_identify_subtotal | 3195769 ----------------------------------------------------|------------- ptoassigneend_us_candid1 (city and state are clean) | 136958 ptoassigneend_us_candid2 (postcode is clean) | 184123  ====Output: ptoassigneend_us_identify_subtotal==== Union ptoassigneend_us_identify(0-4) to generate ptoassigneend_us_identify_subtotal with clean city, state and postcode. This table contains 89.5% of all the records in ptoassigneend_allus. 10.5% left in ptoassigneend_us_temp5.  Table "public.ptoassigneend_us_identify_subtotal" Column | Type | Modifiers -----------------|------------------------|----------- reelno | integer | frameno | integer | name | character varying(500) | addrline1 | character varying(500) | addrline2 | character varying(500) | city_cleaned | text | state_cleaned | text | country | character varying(500) | postcode_cleaned | text | ====Output: ptoassigneend_us_candid1==== One problem of records in ptoassigneend_us_temp5 is that the postcode is missing.  ptoassigneend_us_candid1 is a subset of ptoassigneend_us_temp5. It contains clean city and state info, but postcode is missing.  SQL code: CREATE TABLE ptoassigneend_us_candid1 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). ====Output: ptoassigneend_us_candid2====ptoassigneend_us_candid2 is also a subset of ptoassigneend_us_temp5. It contains clean postcode info, but city and state are not identified.  I randomly checked the city_extracted in ptoassigneend_us_candid2, and it is quite clean. Some city records are misspelt, such as 'Oklahama City'. We may identify clean city based on the length of records.

* Both state name and its abbreviation existNote: About 60 records are missing. For example, the # of records in ptoassigneend_us_temp + # of records in ptoassigneend_us_identify0 != # ptoassigneend_allus.

====To do====* Information extracted Remove city, state, zip, country from addrline1, & addrline2 to get clean addrlines.* Maybe concatenate addrline1 and city is not consistent. Which one is the priority?addrline to make addrline