For records of which addrline1, addrline2 and city don't contain state info, just take feature state as the state_cleaned.
* Output
SQL code is in:
E:/McNair/Projects/PatentAddress/StateClean.sql
States extracted from addrline1, addrline2 and city are stored in ptoassigneend_state.
All the cleaned states for U.S. patents are stored in ptoassigneend_us_statecleaned. (# 3572605)
=====City=====
Reminder: city_city is the cities extracted from 'city'; city_addr1 is the cities extracted from 'addrline1'; city_addr2 is the cities 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
Since city_city is extracted from feature city and is cleaned, city_city beats city.
* Output