How to create burst detection files from MySQL
Here are the steps to generate a burst.in file that can be processed by the burst detection code in Java.
PowerPoint
D:\Research\Papers\Journals\2014\PNAS-PLOS-JASIST-ISSI\Structural Variation of Transformative Patents [Autosaved].pptx
Create two auxiliary tables in MySQL
Create two tables before using the sql script below.
TABLE 1: patent_citations_per_year
patent_id | year | citations |
3670344 | 1976 | 1 |
3583659 | 1976 | 3 |
SQL script:
CREATE TABLE patent_citations_per_year SELECT cite_to_patent_id as patent_id, cite_from_patent_gyr as year, count(*) as citations FROM patent_citations GROUP BY cited_patent_id, year ORDER BY year;
It took 18min on my W530 laptop.
TABLE 2: patent_total_citations
patent_id | total_citations |
4683202 | 3139 |
4683195 | 2735 |
- Generate burst.in with SQL script (1976-2013)
- Merge header+body burst.in; Remove last line SUB (1 min; 325MB) copy to CiteSpaceIII
- edu.drexel.citespace.terms.Burst.main (9 min 20 sec) 616k patents with citation bursts 34.8MB
- edu.drexel.citespace.util.timeseries.ViewBurstHistory main
- Citespace.io.data.uspto.googlebulk.mysql.USPTO.main Export 1,610 to WoS Format (4 min 42 sec)
generate burst.header and burst.body with sql
generate burst.in from mysql
Output Format
year uniq yearID
SQL Script
# need to adjust the beginning year, e.g. 1976
# need to adjust the ending year, e.g. 2014
use comets;
select concat_ws(' ', '#', year, count(*), year-1976) from patent_citations_per_year where year < 2014 group by year order by year
into outfile 'D:/research/papers/journals/2014/PNAS-PLOS-JASIST-ISSI/sql/burst_in_header' lines terminated by '\n';
select concat_ws(' ', year-1976, patent_id, citations) from patent_citations_per_year where year < 2014
into outfile 'D:/research/papers/journals/2014/PNAS-PLOS-JASIST-ISSI/sql/burst_in_body' lines terminated by '\n';
copy burst_in_header+burst_in_body burst.in
copy burst.in to D://CiteSpaceIII
run java with edu.drexel.citespace.terms.Burst as the main class (BurstDetection)