<jupyter_start><jupyter_text>We save the [clickstream-dump](https://dumps.wikimedia.org/other/clickstream/readme.html) in a more usable form to get all incoming and outgoing clicks for a given page.The format of the dumps is:    source, target, type, count    Here, we save two dictionaries, sources and targets, such that for a given page*:    - sources contains all entries where page* is the target    - targets contains all entries where page* is the source (only for entries where type=internal; otherwise we would have an entry for page*=other-search which would contain all pages)    We save the dictionaries as sqlite-tables using sqlitedict so we can query without loading all data into memory.<jupyter_code>import gzip  # necessary for decompressing dump file into text format
import os
from sqlitedict import SqliteDict
from collections import defaultdict
PATH_data = "data/clickstream-ptwiki-2021-04.tsv.gz"

N=0
with gzip.open(PATH_data,"rt") as fin:
    for line in fin:
        N+=1
print("There dump-file contains %s lines"%N)
## we save the sources and the targets as dictionaries 
## dict_sources[target-page] = [(source-page, type, count), ()]
dict_sources = defaultdict(list)
dict_targets = defaultdict(list)

with gzip.open(PATH_data,"rt") as fin:
    for line in fin:
        N+=1
        line_split = line.strip().split()
        page_source = str(line_split[0])
        page_target = str(line_split[1])
        link_type = str(line_split[2])
        link_count = int(line_split[3])
        
        
        dict_sources[page_target].append( (page_source,link_type,link_count) )
        if link_type == "link":
            dict_targets[page_source].append( (page_target,link_type,link_count) )
## save the dictionaries as sqlite-tables
## we only save the 10 souces/targets with most counts, respectively
db_sources = SqliteDict('sources.sqlite',flag="n", autocommit=False)
for k,v in dict_sources.items():
    if len(v)>0:
        v.sort(key=lambda a: a[2],reverse=True)
        db_sources[k] = v[:10]
db_sources.commit()

db_targets = SqliteDict('targets.sqlite',flag="n", autocommit=False)
for k,v in dict_targets.items():
    if len(v)>0:
        v.sort(key=lambda a: a[2],reverse=True)
        db_targets[k] = v[:10]
db_targets.commit()<jupyter_output><empty_output><jupyter_text>Check an example<jupyter_code>page_title = "Supremo_Tribunal_Federal"
print("selected page is: %s"%page_title)
print("Sources are: ", db_sources[page_title])
print("Targets are: ", db_targets[page_title])<jupyter_output>selected page is: Supremo_Tribunal_Federal
Sources are:  [('other-search', 'external', 123820), ('other-empty', 'external', 10490), ('Lista_de_ministros_do_Supremo_Tribunal_Federal', 'link', 642), ('other-external', 'external', 590), ('other-internal', 'external', 530), ('Nunes_Marques', 'link', 376), ('Luiz_Fux', 'link', 344), ('Lista_de_presidentes_do_Supremo_Tribunal_Federal', 'link', 322), ('Luís_Roberto_Barroso', 'link', 281), ('Marco_Aurélio_Mello', 'link', 255)]
Targets are:  [('Nunes_Marques', 'link', 2514), ('Ricardo_Lewandowski', 'link', 1880), ('Luiz_Fux', 'link', 1853), ('Lista_de_presidentes_do_Supremo_Tribunal_Federal', 'link', 1641), ('Lista_de_ministros_do_Supremo_Tribunal_Federal', 'link', 1583), ('Rosa_Weber', 'link', 1542), ('Marco_Aurélio_Mello', 'link', 1375), ('Dias_Toffoli', 'link', 1244), ('Alexandre_de_Moraes', 'link', 999), ('Gilmar_Mendes', 'link', 909)]
{'fraction_non_alphanumeric': 0.135885447106955, 'fraction_numerical': 0.028053769725306838, 'mean_word_length': 5.292279411764706, 'pattern_counts': {'":': 1, '<': 8, '<?xml version=': 0, '>': 10, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '625837', 'license': 'MIT', 'n_tokens_mistral': 1245, 'n_tokens_neox': 1143, 'n_words': 301, 'path': 'preprocessing_make-sqlitetable.ipynb', 'repo_name': 'martingerlach/wikinav-api', 'size': '3422'}