Changes

{{Project|Has project output=Data,Tool,Content,How-to,Guide|Has sponsor=McNair ProjectsCenter

A <onlyinclude>The [[Reproducible Patent Data]] project is a continuation of the [[Redesigning Patent Database]] that project. It aims to write faster, more centralized code to deal with data from the United States Patent and Trademark Office (USPTO). By having an end-to-end pipeline we can easily reproduce or update data without worrying about unintentional side effects or missing data.See also the [[Patent Data]] umbrella project. </onlyinclude>

== Progress Quickstart ==

# <del>Downloader</del> ''done''To get up and running with the code, do the following: # <del>Splitter</del> ''done''Clone the git project (link at end of page) to your user directory# <delLaunch IntelliJ with >Parser</del> ''done''= Java 8 and Maven configured (default version installed on the RDP is setup to do this)# <del>Setup PostgreSQL JDBC</del> ''done''Open project in IntelliJ# <del>Create naive schema based on previous approaches</del> ''done''an empty database (see [[#Database]])# Run the table creation scripts in <delcode>Create new data structuressrc/db/schemas/</delcode> ''done''in your new database# Modify the constant <delcode>Database Insert (modify DATABASE_NAME</code>models/in </code> files with some mapping to database fields)E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\postgres\DatabaseHelper.java</delcode> ''done''# Run the Driver scripts in IntelliJ with the correct value for <delcode>Create tooling for minionsDATA_DIRECTORY</delcode> ''skipped''# (or run <delcode>Investigate parallel speedup (eRunInitialImport.g. multithread, mmap)java</delcode> ''done''which will do all of the data directories for that patent item type)# <del>Remove duplicate code through the addition of [Take a really, really long lunch...in total should take no more abstract classes</del> ''done''than five hours to load data on RDP]# Run scripts in <delcode>first 5 zipcode; centroid?src/db/constraints</delcode> to check data assumptions# That's it! ===Troubleshooting=== If you're new to IntelliJ (and even if you'hackily donere not) you might run into problems with importing the project.  ''# <del>patent id</del> 'Setting Up Project as a Maven project'doneish''# <del>Create XPath queries It should be clear if the project is not set up as a Maven project - when you right click on RunInitialImport.java, for reissueexample, design patents (only utility right now)</del> you won't see an option with a green triangle next to it that says "Run 'split offRunInitialImport.java'' (see ", and the green triangle in the top toolbar will be grayed out. If the project is not set up as a Maven project, you will not be able to run any of the code. To set up the project as a Maven project, when you import the project, follow the instructions at the following [[httphttps://mcnairwww.bakerinstitutejetbrains.orgcom/help/wikiidea/Equivalent_XPath_and_APS_Queries]maven.html#maven_import_project_start link]. * Note that when you click "Import Project", you should select the "Simpler Patent Data" folder, not the "src" folder within Simpler Patent Data, otherwise you won't get the pom.xml file that you need to let IntelliJ know that this is a Maven project.* On the second window (there will be several options with check boxes next to them)make sure "import Maven projects automatically" is selected* On the "Please select project SDK" window, make sure it says "1.8" in the "Name" slot. # <del>Create semantic parser * On the next window, enter a name for APS files</done> the project and enter a folder location. This should ensure that the project is set up as a Maven project.  '''Setting Up Your Data Source'see above''# Data Cleanup (reference [[Patent_Assignment_Data_Restructure|Marcela and Sonia's work]])# If you run into a message across the top that says something along the lines of "Configure Data Source Merger (''only USPTO granted", maintfee, assignment'' then you have not connected IntelliJ to a database. You will not USPTO applications or Harvard Dataverse or Lex Machina currently)# Setup pipeline script be able to complete all of these steps run the code located under src/db until you configure one. Start by clicking on the link in series# Add constraints the message, or if it doesn't appear, follow the instructions [https://www.jetbrains.com/help/idea/connecting-to -a-database tables, e.ghtml here] to open up the "Data Sources and Drivers" pop-up to add a PostgreSQL database. correct typesWhen you get to the dialogue asking about the host, database, foreign keysuser, not nulland password, lookup tablesdo the following to connect to the database on the RDP:  host: localhost database: whatever the constant DATABASE_NAME is set to - the default is patentsj user: postgres password: tabspaceenter Make sure to test the connection by clicking "Test Connection". Now you should be able to run the scripts under src/db.# Add deduplicationIf you're seeing issues such as "column [something] of relation [something] doesn't exist" but you've run the schema scripts, you probably have different database name under the data source than the one the constant DATABASE_NAME is set to. To change this, right click on the data source in the Database tab and select "Properties".

The git repository can be found at httpshttp://rdp.mcnaircenter.org/codebase/Repository/ReproduciblePatent

<code>"C:\Program Files\Java\jdk1.8.0_131\bin\java" "-javaagent:C:\Users\OliverC\IntelliJ IDEA 2017.1.3\lib\idea_rt.jar=62364:C:\Users\OliverC\IntelliJ IDEA 2017.1.3\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1[.8.0_131\jre\lib\charsets.jar;C:\Program Files\Java\jdk1contents truncated.8.0_131\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_131\jre\lib\rt.jar;E:\McNair\Projects\SimplerPatentData\target\classes;C:\Users\OliverC\.m2\repository\com\mashape\unirest\unirest-java\1.4.9\unirest-java-1.4.9.jar;C:\Users\OliverC\.m2\repository\org\apache\httpcomponents\httpclient\4.5.2\httpclient-4.5.2.jar;C:\Users\OliverC\.m2\repository\org\apache\httpcomponents\httpcore\4.4.4\httpcore-4.4.4.jar;C:\Users\OliverC\.m2\repository\commons-logging\commons-logging\1.2\commons-logging-1.2.jar;C:\Users\OliverC\.m2\repository\org\apache\httpcomponents\httpasyncclient\4.1.1\httpasyncclient-4.1.1.jar;C:\Users\OliverC\.m2\repository\org\apache\httpcomponents\httpcore-nio\4.4.4\httpcore-nio-4.4.4.jar;C:\Users\OliverC\.m2\repository\org\apache\httpcomponents\httpmime\4.5.2\httpmime-4.5.2.jar;C:\Users\OliverC\.m2\repository\org\json\json\20160212\json-20160212.jar;C:\Users\OliverC\.m2\repository\com\google\guava\guava\21.0\guava-21.0.jar;C:\Users\OliverC\.m2\repository\org\jsoup\jsoup\1.10.2\jsoup-1.10.2.jar;C:\Users\OliverC\.m2\repository\commons-codec\commons-codec\1.10\commons-codec-1.10.jar;C:\Users\OliverC\.m2\repository\org\jetbrains\annotations\15.0\annotations-15.0.jar;C:\Users\OliverC\.m2\repository\org\apache\commons\commons-lang3\3.5\commons-lang3-3.5.jar];C:\Users\OliverC\.m2\repository\org\postgresql\postgresql\42.1.1\postgresql-42.1.1.jar" org.bakerinstitute.mcnair.uspto_assignments.XmlDriver</code>

As it turns outFor the work by Joe, since many of the fields we care about are date and author data see the schemas are the same [[Patent Schema Reconciliation]] page  === Patents ("universal"Granted). However, it uses parts of the base schema that are unused in the granted patent data. From a short, cursory glance it seems like the meat of the join data appears in the === See <code><us-related-documents>E:\McNair\Projects\SimplerPatentData\data\examples\granted</code> tagfor extracted examples of what specific data is available for a sample of the data.

|+Granted Patent Data Formats

! scope="col" | Dates Used !! scope="col" | Format !! scope="col" | Location !! scope="col" | Supported by Parser? !! scope="col" | Utility !! scope="col" | Reissue !! scope="col" | Design !! scope="col" | Plant

|<code>data/extracts/granted/vintage</code>|style="background: yellowgreen; color: white;" | KindaYes|✓|~|~|~

|<code>data/extracts/granted/blunderyears</code>|style="background: redgreen; color: white;" | Only syntaxYes|✓|~|~|~

|<code>data/extracts/granted/modern</code>|style="background: yellowgreen; color: white;" | MaybeYes|✓|~|~|~

|<code>data/extracts/granted/modern</code>|style="background: yellowgreen; color: white;" | MaybeYes|✓|~|~|~

|<code>data/extracts/granted/modern</code>|style="background: yellowgreen; color: white;" | MaybeYes|✓|~|~|~

|✓~|✓~|~

|✓~|✓~|~

|✓~|✓~|~

# === Create an empty databaseon RDP === To create an empty database, run this command: <code>$ createdb --username=postgres patents_june_2017 database-name-goes-here # password is tabspaceenter</code># Create tables via script at === Abstraction Layer === Since writing raw SQL is a bit cumbersome and error-prone, I have added some abstraction layers that make it much easier to quickly add bulk data. By using Postgres's <code>CopyManager</code> class, we buffer SQL copy commands in memory (as many as possible) and then flush these rows. To understand how the abstraction layers work, see the code in <code>E:\McNair\Projects\SimplerPatentData\src\dbmain\java\org\bakerinstitute\NaiveSchema.sqlmcnair\postgres</code>#* Prior Example . See <code>E:\McNair\SoftwareProjects\SimplerPatentData\Scriptssrc\Patentmain\createTablesjava\org\bakerinstitute\mcnair\models\GrantedPatent.sqljava</code>#* Aim for '''an example of how to extend''' the abstraction layer to create a completely naive schema deal with as few constraints as possible--iteratively add more constraints in the futurecomplex scenarios. === New Table Checklist ===

Since writing raw * Create schema DDL SQL is a bit cumbersome and error-prone, I have added some abstraction layers that make it much easier to quickly add bulk data. By using Postgres's <code>CopyManager</code> class, we buffer SQL copy commands in memory (as many as possible) and then flush these rows. To understand how the abstraction layers work, see for the code new table in <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\postgresdb</code>. For * Run the schema creation* Create an enum with the same names for attributes as in the DDL (case-insensitive! prefer all-caps screaming snake case)* Create a concrete example, see class which subclasses <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\uspto_assignments\GeonamesZips.javaAbstractInsertableData</code> for * Inside that class, create a '''simple, self-contained example''' or static class which subclasses <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\models\GrantedPatent.javaAbstractTableMetadata</code> and has the proper values for getTableName(), getStringColumns(), getIntColumns()* (Optional) Implement builder pattern* (Optional) Create a custom databasehelper for '''an example of how complex extras (see PatentApplication and GrantedPatent for examples)* Write the data to extend''' the abstraction layer to deal with more complex scenarios.table (see DatabaseHelper for the pattern I use)

* Question: "In which zipcodes are To get the most patents granted?"** Hacky answer: <code>\COPY granular address data (select postcodestreet level, count(documentid) as c from june_2017_zipcode_join group by or at least postcode order by c desclevel) TO '/bulk/zipcodes-oliver/postcodes_rankedabout who owns patents, the path is not so straightforward because off the complicated mapping of ownership to a granted patent.This is the final part of this project that I am working on and it is all at the level of SQL.tsv' (format csv, delimiter E'\t')</code>** See <code>ZE:/zipcodes-oliver/postcodes_ranked.tsv\McNair\Projects\SimplerPatentData\src\db\joins</code>for my attempts to create a clean mapping.* Zipcode granularity--if possibleOptimistically speaking, finer detail wanted* Filter non-US, null addresses* Need to cleanup addresses* Extract zipcode or reverse locate to find zipthe data generated here should be superset of the data present in the Patent Assignment Data Restructure project.

* "The 4-5 digit reel number refers to the microfilm reel number Note that as of the assignment entry in physical USPTO records; similarly the 1-4 digit frame number refers to the location beginning of the assignment entry on the reel number in physical USPTO records. ThusAugust 2017, each assignment recorded with the USPTO this part '''has a unique reel number and frame number combination. While both reel number and frame number are sequential, there are missing values in the sequence because each only specifies the first page of the assignment records and records may have multiple pagesnot been completed." from https://www.uspto.gov/sites/default/files/documents/USPTO_Patents_Assignment_Dataset_WP.pdf, pg12, footnote 38'''

<nowiki>patent=# select count(*) from ptoproperty_patent as p, june_2017_zipcode_hotfix as a where p.reelno = a.reelno and p.frameno = a.frameno; count--------- 6107417(1 row)Intuition ===

patent=# select countUse <code>assignments_longform.last_update_date</code> to find current/latest (distinct concat(por first/earliest) date of assignment.frameno, pThen match with <code>properties.docid</code> on <code>reelno)) from ptoproperty_patent as p, june_2017_zipcode_hotfix as a where pframeno</code> to find patent application id.reelno = aWith this mapping to granted patents, we can discover the details of the original granted patent.And with the right date and reelno and p.frameno = a.frameno; count --------- 2507745 (1 row), we can match to the <code>assignees</nowikicode>table and get fine granularity addresses.

* [[Patent_Litigation_and_Review|Patent Litigation and Review by Marcela]]* [[Bag_of_Words_Analysis_of_Patent_Data|Bag of Words Analysis]]* [[Patent|Existing Database Schema]]

* [httpshttp://rdp.mcnaircenter.org/codebase/Repository/ReproduciblePatent Git Repository]