<jupyter_start><jupyter_text>Mining COVID-19 Kaggle competition scientific papers to build an understanding of viruses Part 1. Acquiring and exploring the data**Author**: <NAME>  Imports<jupyter_code>import urllib, os, sys
from os.path import isfile
from tqdm import tqdm

import cudf
import pandas as pd
import json

pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)<jupyter_output><empty_output><jupyter_text>Get the dataThe part below downloads the cord-19 corupus published on 2020-08-24.<jupyter_code>date = '2020-08-24'
url = f'https://ai2-semanticscholar-cord-19.s3-us-west-2.amazonaws.com/historical_releases/cord-19_{date}.tar.gz'
data_dir = 'data'
tar_file = data_dir + '/cord-19.tar.gz'
extract = False

if not os.path.exists(data_dir):
    os.mkdir(data_dir)
    
if not os.path.exists(tar_file):
    with open(tar_file, 'wb') as file:
        with urllib.request.urlopen(url) as resp:
            length = int(resp.getheader('content-length'))
            blocksize = max(4096, length // 100)

            with tqdm(total=length, file=sys.stdout) as pbar:
                while True:
                    buff = resp.read(blocksize)
                    if not buff:
                        break
                    file.write(buff)
                    pbar.update(len(buff))
                    
if extract:
    import tarfile

    tf = tarfile.open(tar_file)
    tf.extractall(path=data_dir)
    
    doc_parses = tarfile.open(f'{data_dir}/{date}/document_parses.tar.gz')
    doc_parses.extractall(f'{data_dir}/{date}')<jupyter_output><empty_output><jupyter_text>Read the metadata<jupyter_code>metadata = cudf.read_csv(f'{data_dir}/{date}/metadata.csv')
metadata.head()
metadata.columns<jupyter_output><empty_output><jupyter_text>Blazing SQL<jupyter_code>from blazingsql import BlazingContext

# Start up BlazingSQL
bc = BlazingContext()

# Create table from CSV
bc.create_table('metadata', metadata)<jupyter_output><empty_output><jupyter_text>Data exploration  Article count<jupyter_code>print(f'Total number of articles: {len(metadata):,}.')
bc.sql('SELECT COUNT(*) FROM metadata')<jupyter_output><empty_output><jupyter_text>Source<jupyter_code>%%time
(
    metadata
    .groupby(by='source_x')
    .agg({'cord_uid': 'count'})
    .sort_values(by='cord_uid', ascending=False)
    .head(10)
)
%%time
bc.sql('''
    SELECT source_x
        , COUNT(*) AS cnt 
    FROM metadata 
    GROUP BY source_x 
    ORDER BY cnt DESC 
    LIMIT 10
''')<jupyter_output>CPU times: user 247 ms, sys: 4.58 ms, total: 251 ms
Wall time: 96.7 ms<jupyter_text>Journals<jupyter_code>metadata.groupby(by='journal').agg({'cord_uid': 'count'}).sort_values(by='cord_uid', ascending=False).head(10)
bc.sql('''
    SELECT journal
        , COUNT(*) AS CNT
    FROM metadata
    WHERE journal IS NOT NULL
    GROUP BY journal
    ORDER BY CNT DESC
    LIMIT 10
''')<jupyter_output><empty_output><jupyter_text>Publication years<jupyter_code>metadata['year_published'] = metadata.publish_time.str.extract('([0-9\.\-]{4})')[0].astype('int16')

(
    metadata
    .query('year_published < 2020')
    .groupby(by='year_published')
    .agg({'source_x': 'count'})
    .to_pandas()
    .plot(kind='bar', figsize=(18,9))
)
(
    metadata
    .groupby(by='year_published')
    .agg({'source_x': 'count'})
    .to_pandas()
    .plot(kind='bar', figsize=(18,9))
)
bc.create_table('metadata', bc.sql('''
    SELECT *
        , CAST(SUBSTRING(publish_time, 1, 4) AS INT) AS year_published
    FROM metadata
'''))
(
    bc.sql('''
        SELECT year_published
            , COUNT(*) AS CNT
        FROM metadata
        WHERE year_published < 2020
        GROUP BY year_published
        ORDER BY year_published ASC
    ''')
    .to_pandas()
    .set_index('year_published')
    .plot(kind='bar', figsize=(18,9))
)<jupyter_output><empty_output><jupyter_text>Coronavirus research accellerates in 2020<jupyter_code>metadata['prior_2020'] = metadata['year_published'] < 2020
metadata.groupby(by='prior_2020').agg({'source_x': 'count'}).to_pandas().plot(kind='barh')
bc.create_table('metadata', bc.sql('''
    SELECT *
        , CASE WHEN year_published < 2020 THEN 1 ELSE 0 END prior_2020
    FROM metadata
'''))

(
    bc.sql('''
        SELECT prior_2020
            , COUNT(*) AS CNT
        FROM metadata
        GROUP BY prior_2020
        ORDER BY prior_2020
    ''')
    .to_pandas()
    .set_index('prior_2020')
    .plot(kind='barh')
)


plt.savefig('prior_2020.png', dpi=300)<jupyter_output><empty_output><jupyter_text>Missing values<jupyter_code>cols = list(metadata.columns)[2:-3]

query_missing = 'SELECT '
query_missing += '\n    ,'.join([f'CASE WHEN {col} IS NULL THEN 1 ELSE 0 END AS {col}_miss' for col in cols])
query_missing += '\nFROM metadata'

query_unions = '\nUNION ALL \n    '.join([f"SELECT '{col}_miss' AS miss_flag, {col}_miss AS FLAG, COUNT(*) AS CNT FROM missing_flags GROUP BY {col}_miss" for col in cols])

bc.create_table('missing_flags', bc.sql(query_missing))
bc.create_table('missing_summary', bc.sql(query_unions))

row_cnt = float(len(metadata))

bc.sql(f'''
    SELECT *
        , CNT / {row_cnt} * 100.0 AS MISS_PCNT
    FROM missing_summary 
    WHERE FLAG = 1
    ORDER BY MISS_PCNT DESC
''')
print(query_missing)
print(query_unions)<jupyter_output>SELECT 'source_x_miss' AS miss_flag, source_x_miss AS FLAG, COUNT(*) AS CNT FROM missing_flags GROUP BY source_x_miss
UNION ALL 
    SELECT 'title_miss' AS miss_flag, title_miss AS FLAG, COUNT(*) AS CNT FROM missing_flags GROUP BY title_miss
UNION ALL 
    SELECT 'doi_miss' AS miss_flag, doi_miss AS FLAG, COUNT(*) AS CNT FROM missing_flags GROUP BY doi_miss
UNION ALL 
    SELECT 'pmcid_miss' AS miss_flag, pmcid_miss AS FLAG, COUNT(*) AS CNT FROM missing_flags GROUP BY pmcid_miss
UNION ALL 
    SELECT 'pubmed_id_miss' AS miss_flag, pubmed_id_miss AS FLAG, COUNT(*) AS CNT FROM missing_flags GROUP BY pubmed_id_miss
UNION ALL 
    SELECT 'license_miss' AS miss_flag, license_miss AS FLAG, COUNT(*) AS CNT FROM missing_flags GROUP BY license_miss
UNION ALL 
    SELECT 'abstract_miss' AS miss_flag, abstract_miss AS FLAG, COUNT(*) AS CNT FROM missing_flags GROUP BY abstract_miss
UNION ALL 
    SELECT 'publish_time_miss' AS miss_flag, publish_time_miss AS FLAG, COUNT(*) AS CNT FROM missing_flags[...]<jupyter_text>Remove observations with missing pdf parsed json<jupyter_code>bc.create_table('metadata_no_missing', bc.sql('SELECT * FROM metadata WHERE pdf_json_files IS NOT NULL'))
bc.sql('SELECT COUNT(*) FROM metadata_no_missing')
query_missing = 'SELECT '
query_missing += '\n    ,'.join([f'CASE WHEN {col} IS NULL THEN 1 ELSE 0 END AS {col}_miss' for col in cols])
query_missing += '\nFROM metadata_no_missing'

query_unions = '\nUNION ALL \n    '.join([f"SELECT '{col}_miss' AS miss_flag, {col}_miss AS FLAG, COUNT(*) AS CNT FROM missing_flags GROUP BY {col}_miss" for col in cols])

bc.create_table('missing_flags', bc.sql(query_missing))
bc.create_table('missing_summary', bc.sql(query_unions))

row_cnt = float(len(metadata))

bc.sql(f'''
    SELECT *
        , CNT / {row_cnt} * 100.0 AS MISS_PCNT
    FROM missing_summary 
    WHERE FLAG = 1
    ORDER BY MISS_PCNT DESC
''')<jupyter_output><empty_output><jupyter_text>Duplicates<jupyter_code>cols_to_check_for_dupes = ['url', 'title', 'doi', 'abstract', 'pdf_json_files', 'pmc_json_files']

col = cols_to_check_for_dupes[5]

select_pattern = '{c}_DIST, {c}_MISS, {c}_DIST + {c}_MISS AS {c}_TTL, CNT - {c}_DIST - {c}_MISS AS {c}_DUPES'
count_pattern  = 'COUNT(DISTINCT {c}) AS {c}_DIST, SUM(CASE WHEN {c} IS NULL THEN 1 ELSE 0 END) AS {c}_MISS'

query = f'SELECT CNT\n    , '
query += '\n    , '.join([select_pattern.format(c=c) for c in cols_to_check_for_dupes])
query += '\nFROM (\n    SELECT COUNT(*) AS CNT, '
query += '\n        ,'.join([count_pattern.format(c=c) for c in cols_to_check_for_dupes])
query += '\n    FROM metadata_no_missing\n) AS A'

bc.sql(query)
bc.create_table('duplicates', bc.sql(query))
import csv

query_dupes = 'SELECT '
query_dupes += ','.join(f'{c}_DUPES' for c in cols_to_check_for_dupes)
query_dupes += ' FROM duplicates'
bc.sql(query_dupes)
print(query)
bc.sql('SELECT pdf_json_files FROM metadata_no_missing GROUP BY pdf_json_files HAVING COUNT(*) > 1')<jupyter_output><empty_output><jupyter_text>We only want to keep one record with the same `pdf_json_files`. Let's check if the other fields are duplicates as well<jupyter_code>bc.sql('''
    SELECT A.*
    FROM metadata_no_missing AS A
    INNER JOIN (
        SELECT pdf_json_files FROM metadata_no_missing GROUP BY pdf_json_files HAVING COUNT(*) > 1
    ) AS B
    ON A.pdf_json_files = B.pdf_json_files
    ORDER BY A.pdf_json_files
''')<jupyter_output><empty_output><jupyter_text>The top 3 articles seem simply mislabled as the pmc_json_files point to different xml files and they seem to be 3 different articles. The remaining seem to be duplicated entries. Since the number is low we remove them all<jupyter_code>metadata_json_clean = bc.sql('''
    SELECT A.*
    FROM metadata_no_missing AS A
    INNER JOIN (
        SELECT pdf_json_files FROM metadata_no_missing GROUP BY pdf_json_files HAVING COUNT(*) = 1
    ) AS B
    ON A.pdf_json_files = B.pdf_json_files
    ORDER BY A.pdf_json_files
''')
                             
bc.create_table('metadata_json_clean', metadata_json_clean)<jupyter_output><empty_output><jupyter_text>Title duplicates<jupyter_code>bc.sql('''SELECT A.title
        , A.doi
        , A.abstract
        , A.authors
        , A.journal
        , A.year_published
    FROM metadata_json_clean AS A
    INNER JOIN (
        SELECT title FROM metadata_json_clean GROUP BY title HAVING COUNT(*) > 1
    ) AS B
    ON A.title = B.title
    ORDER BY A.title
    LIMIT 10''')
metadata_title_clean = metadata_json_clean.drop_duplicates(subset=['title'])
bc.create_table('metadata_title_clean', metadata_title_clean)
bc.sql('SELECT title FROM metadata_title_clean GROUP BY title HAVING COUNT(*) > 1')<jupyter_output><empty_output><jupyter_text>Missing JSON files<jupyter_code>from os import listdir
from os.path import isfile, join

pdf_json = f'{data_dir}/{date}/document_parses/pdf_json'
files = ['document_parses/pdf_json/' + f for f in listdir(pdf_json) if isfile(join(pdf_json, f))]
bc.create_table('files', cudf.DataFrame(files, columns=['pdf_json_files']))

bc.create_table('pdf_files_meta', bc.sql('''
    SELECT A.pdf_json_files AS meta_pdf_json
        , B.pdf_json_files AS folder_pdf_json
        , CASE WHEN A.pdf_json_files IS NULL THEN 1 ELSE 0 END meta_pdf_json_missing
        , CASE WHEN B.pdf_json_files IS NULL THEN 1 ELSE 0 END folder_pdf_json_missing
    FROM metadata_title_clean AS A
    FULL OUTER JOIN files AS B
        ON A.pdf_json_files = B.pdf_json_files
'''))

bc.sql('''
    SELECT meta_pdf_json_missing
        , folder_pdf_json_missing
        , COUNT(*) AS CNT
    FROM pdf_files_meta
    GROUP BY meta_pdf_json_missing
        , folder_pdf_json_missing
''').to_pandas().to_csv(f'{data_dir}/{date}/metadata_missing_json.csv', index=False, quoting=csv.QUOTE_NONNUMERIC)
bc.create_table('metadata_pdf_json_clean', bc.sql('''
    SELECT A.*
    FROM metadata_title_clean AS A
    INNER JOIN (
        SELECT meta_pdf_json
        FROM pdf_files_meta
        WHERE meta_pdf_json_missing = 0
            AND folder_pdf_json_missing = 0
    ) AS B
        ON A.pdf_json_files = B.meta_pdf_json
'''))

bc.sql('SELECT COUNT(*) AS CNT FROM metadata_pdf_json_clean')<jupyter_output><empty_output><jupyter_text>Save clean metadatafile<jupyter_code># metadata = cudf.read_csv(f'{data_dir}/{date}/metadata.csv')

clean_metadata = bc.sql('SELECT * FROM metadata_pdf_json_clean')
clean_metadata.to_csv(f'{data_dir}/{date}/metadata_clean.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11877069360726716, 'fraction_numerical': 0.014007980303930724, 'mean_word_length': 4.097360450021636, 'pattern_counts': {'":': 0, '<': 72, '<?xml version=': 0, '>': 72, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 1}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '472082', 'license': 'Apache-2.0', 'n_tokens_mistral': 4407, 'n_tokens_neox': 4060, 'n_words': 974, 'path': 'codes/covid/1_DataExploration.ipynb', 'repo_name': 'drabastomek/rapids-notebooks', 'size': '11783'}