<jupyter_start><jupyter_text>Downloading MEDLINE/PubMed Data and Posting to PostgreSQL    <NAME> - University of Virginia's Bicomplexity Institute  This notebook detail the process of downloading all of [PubMed's MEDLINE data](https://www.nlm.nih.gov/databases/download/pubmed_medline.html) and posting it to a PostgresSQL database ([UVA's Rivanna OpenOnDemand](https://rivanna-portal.hpc.virginia.edu/)). To do this, we use the terminal to download all of the data into Rivanna. Next, we use the [PubMedPortable](https://github.com/KerstenDoering/PubMedPortable) package through the Python shell to parse all of the data and build up a database.  Step 1: Download PubMed DataFirst, we download all of the data from [here](ftp://ftp.ncbi.nlm.nih.gov/pubmed/baseline/) using `wget`.<jupyter_code>cd /scratch/kb7hp/pubmed_new 
wget --recursive --no-parent ftp://ftp.ncbi.nlm.nih.gov/pubmed/baseline/<jupyter_output><empty_output><jupyter_text>Step 2: Download PubMedPortableSecond, we will clone [PubMedPortable package from GitHub](https://github.com/KerstenDoering/PubMedPortable).<jupyter_code>cd /home/kb7hp/git/
git clone https://github.com/KerstenDoering/PubMedPortable.git
cd PubMedPortable<jupyter_output><empty_output><jupyter_text>Step 3: Populate Tables in PostgreSQL Database Go to the [PubMedPortable](https://github.com/KerstenDoering/PubMedPortable/wikibuild-up-a-relational-database-in-postgresql) protocol:  - Skip the part on making a superuser named parser and use Rivanna login and pwd instead   - Since `PubMedPortable` is written with the login/pwd of parser/parser you have to update lines 704-750 of `PubMedDB.py`       - Add `import psycopg2 as pg` to the beginning of the file      - Update all the connections to: `con = 'postgresql+psycopg2://login:pwd@postgis1/sdad'`      - Update all the `print` statements to `print()` (e.g. line 728)Go to [Rivanna OpenOnDemand](https://rivanna-portal.hpc.virginia.edu/), click on Clusters > Rivanna Shell Access and then create a new schema using the following commands:<jupyter_code>psql -U login -d sdad -h postgis1
CREATE SCHEMA pubmed_2021;<jupyter_output><empty_output><jupyter_text>Then return to the Python terminal and run this to populate the new schema:<jupyter_code>cd /home/kb7hp/git/PubMedPortable
python PubMedDB.py -d pubmed_2021<jupyter_output><empty_output><jupyter_text>Go back to the Rivanna PostgreSQL shell to check if that worked:<jupyter_code>\dt pubmed_2021.*<jupyter_output><empty_output><jupyter_text>Looks like it did so now we can start parsing. Step 4: Testing MEDLINE Data Upload We don't want to start dumping all 1062 files, so let's just start with one. We will create a pm_0001 folder and download just one of the .xml files from PubMed. Next, we had to debug the `PubMedParser.py` file by updating all of the `con` and `print` statements as we did above and update `_next` to `__next__` on line 65. After doing this, we ran the following code to upload our first test file. Batch 1 (0001)Let's give this a try:<jupyter_code>cd /home/kb7hp/git/PubMedPortable/data
mkdir pm_0001 
cd pm_0001
wget ftp://ftp.ncbi.nlm.nih.gov/pubmed/baseline/pubmed21n0001.xml.gz
cd /home/kb7hp/git/PubMedPortable/
python PubMedParser.py -i data/pm_0001/<jupyter_output><empty_output><jupyter_text>It took about 8 minutes to run this one file.  Step 5: Uploading the Rest of the MEDLINE Dataset to PostgreSQL Database in Batches Let's add the rest of the data to Postgres. Ideally, we would just dump the whole thing at once, but Rivanna limits the amount of data we can store locally (for some reason `PubMedPortable` does not like absolute paths). Thus, we will only copy part of the data from the `\scratch` folder to our temporary local folders. Batch 2 (0002-0011)<jupyter_code># move all the .xml.gz files to their own folder
cd /scratch/kb7hp/
mkdir pubmed_gz
cd /scratch/kb7hp/pubmed_new/ftp.ncbi.nlm.nih.gov/pubmed/baseline/
mv *.gz /scratch/kb7hp/pubmed_gz

# and copy 10 of those files to that new folder 
cd /scratch/kb7hp/pubmed_gz/
cp pubmed21n{0002..0011}.xml.gz /home/kb7hp/git/PubMedPortable/data/pm_0002_0011

# and then we add those 10 files to our existing database 
cd /home/kb7hp/git/PubMedPortable/data/
python PubMedParser.py -i data/pm_0002_0011/ -c -p 4<jupyter_output><empty_output><jupyter_text>While I intially thought this process would take ~80 minutes, running these 10 files only look ~22 minutes because of the 4 cores that essentially cut the timing by a quarter. Thus, we spun an instance with 5 cores (1 extra as directed by the Rivanna admins) and ran the next ~90 files with this new allocation. When I checked the `pubmed_2021.tbl_abstract` table, we had 146,854 rows, which seemed low. Yet, the notification from the `PubMedParser.py` file indicated that all files were parsed. I would late come to realize that there are fewer abstracts than total records, which can be validated in the `pubmed_2021.tbl_medline_citation` table.  Batch 3 (0012-0100)Let's dump the next batch of citations (0012-0100). We will copy over the next batch of data and with multiprocessing accounted for this should take ~3 hours to complete.<jupyter_code>cd /scratch/kb7hp/pubmed_gz/
cp pubmed21n{0012..0100}.xml.gz /home/kb7hp/git/PubMedPortable/data/pm_0012_0100
cd /home/kb7hp/git/PubMedPortable/data/
python PubMedParser.py -i data/pm_0012_0100/ -c -p 4<jupyter_output><empty_output><jupyter_text>And indeed it did! We have loaded the first 100 files and it took just over 3 hours (13:19:19-16:22:52).  Batch 4 (0101-0500)Now, let's get a bit more ambitious. Given its now night time, we are can boost the allocation to 9 cores and try ~400 files. This should take around around 7 hours to complete (400 files * 8 mins/file with 8 cores).<jupyter_code># first we will clean up the local directory 
cd /home/kb7hp/git/PubMedPortable/data/
rm -r pm_0001 
rm -r pm_0002_0011
rm -r pm_0012_0100

# copy over our new files 
cd /scratch/kb7hp/pubmed_gz 
cp pubmed21n{0101..0500}.xml.gz /home/kb7hp/git/PubMedPortable/data/pm_0101_0500

# and then run the script for the next 400 files 
cd /home/kb7hp/git/PubMedPortable/
python PubMedParser.py -i data/pm_0101_0500/ -c -p 8<jupyter_output><empty_output><jupyter_text>After parsing the pm_101_500 files, I woke up to a minor error, but it looks like the program continued running up through the very last citation of the last file. I checked the `pubmed_2021.tbl_abstract` table and had 6,388,959 entries while `pubmed_2021.tbl_medline_citation` had 13,095,000, which almost half of the 26 million advertised on [MEDLINE's website](https://www.nlm.nih.gov/bsd/medline.html). Thus, it does seem like everything parsed without any serious problems. I decided to finsih up the rest of the file parsing since (1) I cannot address any problem in a systematic way and (2) a full database with problems is still better than a half database with problems.  Batch 5 (0501-0750)With the space limitations, let's take a conservative approach and post the next 250 files to the database (once again using 9 cores on Rivanna).<jupyter_code>cd /home/kb7hp/git/PubMedPortable/data
rm -r pm_0101_0500
mkdir pm_0501_0750
cd /scratch/kb7hp/pubmed_gz 
cp pubmed21n{0501..0750}.xml.gz /home/kb7hp/git/PubMedPortable/data/pm_0501_0750
cd /home/kb7hp/git/PubMedPortable/
python PubMedParser.py -i data/pm_0501_0750/ -c -p 8<jupyter_output><empty_output><jupyter_text>This took just over 4 hours (08:34:23-13:00:31) and worked flawlessly (no errors whatsoever). At this point, we have 12,158,748 abstracts in the `pubmed_2021.tbl_abstract` table.  Batch 6 (0751-0900)While I thought this would be the last batch, I ran out of space again trying to dump 750-1062. Let's do up to 900 and do the last batch later today.<jupyter_code>cd /home/kb7hp/git/PubMedPortable/data
rm -r pm_0501_0750
mkdir pm_0751_0900
cd /scratch/kb7hp/pubmed_gz
cp pubmed21n{0751..0900}.xml.gz /home/kb7hp/git/PubMedPortable/data/pm_0751_0900
cd /home/kb7hp/git/PubMedPortable/
python PubMedParser.py -i data/pm_0751_0900/ -c -p 8<jupyter_output><empty_output><jupyter_text>That took __ hours and once again ran without errors.  Batch 7 (0901-1062)We dumped the last batch with this code and we were done!<jupyter_code>cd /home/kb7hp/git/PubMedPortable/data
rm -r pm_0751_0900
mkdir pm_0901_1062
cd /scratch/kb7hp/pubmed_gz
cp pubmed21n{0901..1062}.xml.gz /home/kb7hp/git/PubMedPortable/data/pm_0901_1062
cd /home/kb7hp/git/PubMedPortable/
python PubMedParser.py -i data/pm_0901_1062/ -c -p 8
# started this around 8:50am<jupyter_output><empty_output><jupyter_text>On to the Next Step in Your Research ProjectOverall, this was a surprisingly easy process. A major kudos goes out to PubMedPortable for this fantastic package. Now, let's get to text mining!   References <NAME>., <NAME>., <NAME>., <NAME>., & <NAME>. (2016). PubMedPortable: a framework for supporting the development of text mining applications. Plos one, 11(10), e0163794. (Link to [Article](https://journals.plos.org/plosone/article?id=10.1371/journal.pone.0163794) and [GitHub Repo](https://github.com/KerstenDoering/PubMedPortable))National Library of Medicine. (2021). Download MEDLINE/PubMed Data. Link to [Data](ftp://ftp.ncbi.nlm.nih.gov/pubmed/baseline) and [Documentation](https://www.nlm.nih.gov/databases/download/pubmed_medline.html)<jupyter_code>SELECT xml_file_name, COUNT(fk_pmid) 
FROM pubmed_2021.tbl_pmids_in_file
GROUP BY xml_file_name; 

--- looks like there was some kind of problem parsing these files
--- affected 0816, 0829, 0865, 0866, 0875, 0879, 0884, 0886, 0891
--- all of the rest were in the high 29,000s or at 30000 
--- i think i parse 900:1062 and come back to these problems later 
--- the best approach would be to create a view where the fk_pmids of all   
--- those files is removed across all the tables and then started anew  
--- another way is just to remove all duplicates from each table later 
--- https://stackoverflow.com/questions/6583916/delete-duplicate-rows-from-small-table
    python PubMedParser.py -i data/pubmed/ -c 
# ran 0001-0100 first 
python PubMedParser.py -i data/pubmed_data/ -c -p 12
# took an hour and 10 mins 
# downloaded 0101-0500
python PubMedParser.py -i data/after400/ -c -p 12
python PubMedParser.py -i data/the700s/ -c -p 12<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08767496111975116, 'fraction_numerical': 0.06279160186625195, 'mean_word_length': 4.722469410456062, 'pattern_counts': {'":': 0, '<': 59, '<?xml version=': 0, '>': 60, 'https://': 12, 'lorem ipsum': 0, 'www.': 3, 'xml': 11}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 11, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '667984', 'license': 'MIT', 'n_tokens_mistral': 3902, 'n_tokens_neox': 3360, 'n_words': 1210, 'path': 'src/01_pubmed_db/.ipynb_checkpoints/02_pubmed_parser-checkpoint.ipynb', 'repo_name': 'brandonleekramer/the-growth-of-diversity', 'size': '10325'}