<jupyter_start><jupyter_text>Working with Archives Unleashed Parquet DerivativesIn this notebook, we'll setup an environment, then download a dataset of web archive collection derivatives that were produced with the [Archives Unleashed Toolkit](https://github.com/archivesunleashed/aut/). These derivatives are in the [Apache Parquet](https://parquet.apache.org/) format, which is a [columnar storage](http://en.wikipedia.org/wiki/Column-oriented_DBMS) format. These derivatives are generally small enough to work with on your local machine, and can be easily converted to Pandas DataFrames as demonstrated below.This notebook is useful for exploring the following derivatives. **[Binary Analysis](https://github.com/archivesunleashed/aut-docs/blob/master/current/binary-analysis.mdbinary-analysis)**- [Audio](https://github.com/archivesunleashed/aut-docs/blob/master/current/binary-analysis.mdextract-audio-information)- [Images](https://github.com/archivesunleashed/aut-docs/blob/master/current/binary-analysis.mdextract-image-information)- [PDFs](https://github.com/archivesunleashed/aut-docs/blob/master/current/binary-analysis.mdextract-pdf-information)- [Presentation program files](https://github.com/archivesunleashed/aut-docs/blob/master/current/binary-analysis.mdextract-presentation-program-files-information)- [Spreadsheets](https://github.com/archivesunleashed/aut-docs/blob/master/current/binary-analysis.mdextract-spreadsheet-information)- [Text files](https://github.com/archivesunleashed/aut-docs/blob/master/current/binary-analysis.mdextract-text-files-information)- [Videos](https://github.com/archivesunleashed/aut-docs/blob/master/current/binary-analysis.mdextract-video-information)- [Word processor files](https://github.com/archivesunleashed/aut-docs/blob/master/current/binary-analysis.mdextract-word-processor-files-information)**Web Pages**`.webpages().select($"crawl_date", $"url", $"mime_type_web_server", $"mime_type_tika", RemoveHTMLDF(RemoveHTTPHeaderDF(($"content"))).alias("content"))` Produces a DataFrame with the following columns:  - `crawl_date`  - `url`  - `mime_type_web_server`  - `mime_type_tika`  - `content`As the `webpages` derivative is especially rich - it contains the full text of all webpages - we have a separate notebook for [text analysis](https://github.com/archivesunleashed/notebooks/blob/master/parquet_text_analyis.ipynb) here.**Web Graph**`.webgraph()` Produces a DataFrame with the following columns:  - `crawl_date`  - `src`  - `dest`  - `anchor`**Image Links**`.imageLinks()`Produces a DataFrame with the following columns:  - `src`  - `image_url`**Domains**`.webpages().groupBy(ExtractDomainDF($"url").alias("url")).count().sort($"count".desc)`Produces a DataFrame with the following columns:- domain- countWe recommend running through the notebook with the provided sample dataset. You may then want to substitute it with your own dataset.  DatasetFirst, we will need a web archive dataset to work with.In this example notebook we use a dataset from [Bibliothèque et Archives nationales du Québec](https://www.banq.qc.ca/accueil/). It is a web archive collection of the Ministry of Environment of Québec (2011-2014), that has been  processed by the [Archives Unleashed Toolkit](https://github.com/archivesunleashed/aut/). Merci beaucoup BAnQ![![DOI](https://zenodo.org/badge/DOI/10.5281/zenodo.3605525.svg)](https://doi.org/10.5281/zenodo.3605525)Curious about the size the derivative Parquet output compared to the size of the web archive collection?The total size of all 12 Parquet deriatives is 594M, with `webpages` being the largest (528M) since it has a column with full text (`content`).```2.5M	./videos344K	./domains1.7M	./word-processor-files24K	./presentation-program-files1.7M	./spreadsheets880K	./audio4.4M	./images528M	./webpages1.7M	./text-files3.9M	./pdfs29M	./webgraph22M	./imagelinks594M	.```The total size of the web archive collection is 165G. The derivative file is thus a 99.68% decrease!The following command downloads all of the parquets file from the Zenodo data repository. To run a 'cell,' you can click the play button next to the cell or you can press your shift key and enter key at the same time.Whenever you see code snippets like this, you should do the same thing to run it.<jupyter_code>%%capture

!curl -L "https://zenodo.org/record/3598450/files/environnement-qc.tar.gz?download=1" > environment-qc-parquet.tar.gz
!tar -xzf environment-qc-parquet.tar.gz<jupyter_output><empty_output><jupyter_text>The following command provides a list of all the downloaded parquet files. You should see a list of all the different derivatives here - note that they line up with the list provided at the beginning of this notebook.<jupyter_code>!ls -1 parquet<jupyter_output>audio
domains
imagelinks
images
pdfs
presentation-program-files
spreadsheets
text-files
videos
webgraph
webpages
word-processor-files<jupyter_text>EnvironmentNext, we'll setup our environment so we can work with the Parquet output with [Pandas](https://pandas.pydata.org).<jupyter_code>import numpy as np
import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq<jupyter_output><empty_output><jupyter_text>Loading our Archives Unleashed Datasets as DataFramesNext, we'll load up our datasets to work with and show a preview of each. We'll load the network, domains, web graph, and images. The remainder of the binary datasets (audio, video, spreadsheets, etc.) will all follow the same pattern as the images dataset, except that they do not have the height and width columns. A useful exercise when trying to learn how to use this would be to swap out images for audio, for example, and see how you can explore these other file types.We've provided a [separate notebook](https://github.com/archivesunleashed/notebooks/blob/master/parquet_text_analyis.ipynb) to work with the pages dataset  because it tends to be resource intensive.  ImagesThe following commands create a variable called `images` that contain a DataFrame with all of the image information from the web archive. **Reminder:** If you want to look at a differnt derivative, you can, for instance, replace out `images` for `audio`.<jupyter_code>images_parquet = pq.read_table('parquet/images')
images = images_parquet.to_pandas()
images<jupyter_output><empty_output><jupyter_text>Data Table DisplayColab includes an extension that renders pandas dataframes into interactive displays that can be filtered, sorted, and explored dynamically.Data table display for Pandas dataframes can be enabled by running:```python%load_ext google.colab.data_table```and disabled by running```python%unload_ext google.colab.data_table```Note: There appears to be a limit to how many rows can be used in the Data Table display. It doesn't work with all examples in this notebook.<jupyter_code>%load_ext google.colab.data_table<jupyter_output><empty_output><jupyter_text>Web GraphThe next data that we will explore will be the "web graph." This is a DataFrame containing all the hyperlinks within a collection - from `src` (or the page that _contains_ the link) to `dest` (or the page that the link is linking _to_). It also includes the date when this link was crawled, as well as the `anchor` text (what the user clicks on to visit).<jupyter_code>webgraph_parquet = pq.read_table('parquet/webgraph')
webgraph = webgraph_parquet.to_pandas()
webgraph<jupyter_output><empty_output><jupyter_text>DomainsThis derivative contains basic information about what's been collected in the crawl. Specifically we can analyze how often pages from each domain appear.Note: You can also take of the Data Table extension to experiment with interacting with the DataFrame.<jupyter_code>domains_parquet = pq.read_table('parquet/domains')
domains = domains_parquet.to_pandas()
domains<jupyter_output><empty_output><jupyter_text>Data AnalysisNow that we have all of our datasets loaded up, we can begin to work with them!  Counting total files, and unique files  Count number of rows (how many images are in the web archive collection).<jupyter_code>images.count()<jupyter_output><empty_output><jupyter_text>How many unique images are in the collection? We can see if an image is unique or not by computing an [MD5 hash](https://en.wikipedia.org/wiki/MD5MD5_hashes) of it. The exact same image might be called `example.jpg` and `foo.jpg` - by computing the hash, we can see that even with different file names, they are actually the same image!<jupyter_code>len(images.md5.unique())<jupyter_output><empty_output><jupyter_text>What are the top 10 most occurring images in the collection?Here we discover which image (or images) occur most frequently.<jupyter_code>images['md5'].value_counts().head(10)<jupyter_output><empty_output><jupyter_text>What's the information around all of the occurances of `b09dc3225d5e1377c52c06feddc33bfe`?What, you mean you don't know what `b09dc3225d5e1377c52c06feddc33bfe` means? Let's find those images in the DataFrame table - we can here see the real file name (`erosion.jpg`) and more importantly, its URL within the web archive.Note: You can also take of the Data Table extension to experiment with interacting with the DataFrame.<jupyter_code>images.loc[images['md5'] == 'b09dc3225d5e1377c52c06feddc33bfe']<jupyter_output><empty_output><jupyter_text>What does `b09dc3225d5e1377c52c06feddc33bfe` look like?We can extract the binary from the web archive using our [binary extraction functions](https://github.com/archivesunleashed/aut-docs-new/blob/master/current/image-analysis.mdscala-df).```scalaimport io.archivesunleashed._import io.archivesunleashed.df._val df = RecordLoader  .loadArchives("example.arc.gz", sc)  .extractImageDetailsDF();df.select($"bytes", $"extension")  .saveToDisk("bytes", "/path/to/export/directory/your-preferred-filename-prefix", $"extension")```**But**, since we don't have access to the WARC files here, just the Parquet derivatives, we can make do  by trying to display a live web version of the image or a replay URL. In this case, BANQ's replay service is available at [https://waext.banq.qc.ca](http://waext.banq.qc.ca).<jupyter_code>pd.options.display.max_colwidth = None
one_image = images.loc[images['md5'] == 'b09dc3225d5e1377c52c06feddc33bfe'].head(1)
one_image['url']<jupyter_output><empty_output><jupyter_text>![fish](http://waext.banq.qc.ca/wayback/20121127103149/http://www.mddefp.gouv.qc.ca/poissons/st-charles/erosion.jpg) Unfortunately, while we can tell you this is such a popular image within the web archive, we can't tell you _why_. That's where the researcher comes in!  What are the top 10 most occuring filenames in the collection?Note that this is of course different than the MD5 results up above. Here we are focusing _just_ on filename. So `carte-p.jpg` for example, might actually be referring to different images who happen to have the same name.<jupyter_code>top_filenames = images['filename'].value_counts().head(10)
top_filenames<jupyter_output><empty_output><jupyter_text>Let's plot it!We'll plot the data first with panadas plot functionality, and then plot the data with [Altair](https://altair-viz.github.io/).<jupyter_code>top_filenames_chart = top_filenames.plot.bar(figsize=(25,10))
top_filenames_chart.set_title("Top Filenames", fontsize=22)
top_filenames_chart.set_xlabel("Filename", fontsize=20)
top_filenames_chart.set_ylabel("Count", fontsize=20)<jupyter_output><empty_output><jupyter_text>Now let's setup Altair, and plot the data with Altair. We'll use Altair for the remaining plots we create since the plots can be easily exported as a PNG or SVG.<jupyter_code>import altair as alt
top_filenames_altair = images['filename'].value_counts().head(10).rename_axis('Filename').reset_index(name='Count')

filenames_bar = alt.Chart(top_filenames_altair).mark_bar().encode(
    x='Filename:O',
    y='Count:Q'
)

filenames_rule = alt.Chart(top_filenames_altair).mark_rule(color='red').encode(
    y='mean(Count):Q'
)


filenames_text = filenames_bar.mark_text(
    align='center',
    baseline='bottom'
).encode(
    text='Count:Q'
)

(filenames_bar + filenames_rule + filenames_text).properties(width=1400, height=700, title='Top Filenames')<jupyter_output><empty_output><jupyter_text>How about a MIME type distribution?What _kind_ of image files are present? We can discover this by checking their "media type", or [MIME type](https://en.wikipedia.org/wiki/Media_type).<jupyter_code>image_mime_types = images['mime_type_tika'].value_counts().head(5).rename_axis('MIME Type').reset_index(name='Count')

image_mimes_bar = alt.Chart(image_mime_types).mark_bar().encode(
    x='MIME Type:O',
    y='Count:Q'
)

image_mime_rule = alt.Chart(image_mime_types).mark_rule(color='red').encode(
    y='mean(Count):Q'
)

image_mime_text = image_mimes_bar.mark_text(
    align='center',
    baseline='bottom'
).encode(
    text='Count:Q'
)

(image_mimes_bar + image_mime_rule + image_mime_text).properties(width=1400, height=700, title='Images MIME Type Distribution')<jupyter_output><empty_output><jupyter_text>How about the distribution of the top 10 domains?Here we can see which domains are the most frequent within the web archive.<jupyter_code>top_domains = domains.sort_values('count', ascending=False).head(10)

top_domains_bar = alt.Chart(top_domains).mark_bar().encode(
    x=alt.X('url:O', title='Domain'),
    y=alt.Y('count:Q', title='Count, Mean of Count')
)

top_domains_rule = alt.Chart(top_domains).mark_rule(color='red').encode(
    y='mean(count):Q'
)

top_domains_text = top_domains_bar.mark_text(
    align='center',
    baseline='bottom'
).encode(
    text='count:Q'
)

(top_domains_bar + top_domains_rule + top_domains_text).properties(width=1400, height=700, title='Domains Distribution')<jupyter_output><empty_output><jupyter_text>Top Level Domain AnalysisNow let's create a new column, `tld`, which is based off an existing column, 'Domain'. This example should give you an idea of how you can expand these datasets to do further research and analysis. A [top-level domain](https://en.wikipedia.org/wiki/Top-level_domain) refers to the highest domain in an address - i.e. `.ca`, `.com`, `.org`, or yes, even `.pizza`.Things get a bit complicated, however, in some national TLDs. While `qc.ca` (the domain for Quebec) isn't really a top-level domain, it has many of the features of one as people can directly register under it. Below, we'll use the command `suffix` to include this. > You can learn more about suffixes at https://publicsuffix.org.We'll take the `Domain` column and extract the `tld` from it with [`tldextract`](https://github.com/john-kurkowski/tldextract).First we'll add the [`tldextract`](https://github.com/john-kurkowski/tldextract) library to the notebook. Then, we'll create the new column.<jupyter_code>%%capture

!pip install tldextract
import tldextract

domains['tld'] = domains.apply(lambda row: tldextract.extract(row.url).suffix, axis=1)
domains<jupyter_output><empty_output><jupyter_text>Next, let's count the distict TLDs.<jupyter_code>tld_count = domains['tld'].value_counts()
tld_count<jupyter_output><empty_output><jupyter_text>Next, we'll plot the TLD count.<jupyter_code>tld_count = domains['tld'].value_counts().rename_axis('TLD').reset_index(name='Count')

tld_bar = alt.Chart(tld_count).mark_bar().encode(
    x='TLD:O',
    y='Count:Q'
)

tld_rule = alt.Chart(tld_count).mark_rule(color='red').encode(
    y='mean(Count):Q'
)

tld_text = tld_bar.mark_text(
    align='center',
    baseline='bottom'
).encode(
    text='Count:Q'
)

(tld_bar + tld_rule + tld_text).properties(width=1400, height=700, title='Top Level Domain Distribution')<jupyter_output><empty_output><jupyter_text>Examining the Web Graph Remember the hyperlink web graph? Let's look at the web graph columns again.<jupyter_code>webgraph<jupyter_output><empty_output><jupyter_text>What are the most frequent crawl dates?<jupyter_code>crawl_dates = webgraph['crawl_date'].value_counts().rename_axis('Crawl Dates').reset_index(name='Count')

area = alt.Chart(crawl_dates).mark_area(
    color="lightgreen",
    line=True
).encode(
    x='Crawl Dates:O',
    y='Count:Q',
)

count_mean = alt.Chart(crawl_dates).mark_rule(color='red').encode(
    y='mean(Count):Q'
)

(area + count_mean).properties(width=1400, height=700, title='Crawl Date Frequency')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10973440471078942, 'fraction_numerical': 0.015763969821505245, 'mean_word_length': 6.067186822713481, 'pattern_counts': {'":': 0, '<': 88, '<?xml version=': 0, '>': 90, 'https://': 28, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '572836', 'license': 'Apache-2.0', 'n_tokens_mistral': 5324, 'n_tokens_neox': 5055, 'n_words': 1485, 'path': 'Parquet Examples/parquet_pandas_example.ipynb', 'repo_name': 'archivesunleashed/notebooks', 'size': '16303'}