{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Wikipedia Page Sizes\n",
"- Report out page character counts from wiki markup in XML dumps\n",
"- Character counts use pre-rendered, pre-transcluded text, but relative page sizes should serve our study well\n",
"- populate `page_lengths_w_date` table\n",
"- report page sizes for W and WP:M for both dump dates"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"# basic defaults, including study dates, common SQL exclusions and parquet files for anonymized data\n",
"%run -i 'data-defaults.py'"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"WIKIPEDIA_XML_DUMPS = ['enwiki-20190401-pages-articles-multistream.xml.bz2',\n",
" 'enwiki-20190420-pages-articles-multistream.xml.bz2']\n",
"\n",
"def page_length(entity, date):\n",
" page_text = entity.revision.text._VALUE\n",
" size = len(page_text)\n",
" return Row(page_id=entity.id, page_length=size, dt=date)\n",
"\n",
"page_lengths_rdd = sc.emptyRDD()\n",
"for file in WIKIPEDIA_XML_DUMPS:\n",
" wikipedia = sqlContext.read.format('com.databricks.spark.xml').options(rowTag='page').load(file)\n",
" dump_date = re.search(r'.*(\\d{8}).*',file).group(1)\n",
" articles = wikipedia\\\n",
" .filter(\"ns = '0'\")\\\n",
" .filter(\"redirect._title is null\") \\\n",
" .filter(\"revision.text._VALUE is not null\") \\\n",
" .filter(\"length(revision.text._VALUE) > 0\")\n",
" daily_page_lengths = sqlContext.createDataFrame(articles.rdd.map(lambda entity: page_length(entity, dump_date)))\n",
" page_lengths_rdd = page_lengths_rdd.union(daily_page_lengths.rdd)\n",
"\n",
"page_lengths_merged = sqlContext.createDataFrame(page_lengths_rdd)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"# save page lengths data to a table for later use\n",
"page_lengths_merged.registerTempTable(\"page_lengths_w_date\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Number of Wikipedia pages\n",
"- limited to namespace 0\n",
"- excludes redirect pages\n",
"- excludes pages without content"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+--------+-------+\n",
"| dt| pages|\n",
"+--------+-------+\n",
"|20190420|5847824|\n",
"|20190401|5839083|\n",
"+--------+-------+\n",
"\n"
]
}
],
"source": [
"# page count of all pages in Wikipedia namespace 0, excluding redirect pages and pages with no content\n",
"count_query = \"\"\"\n",
"SELECT dt, count(*) as pages\n",
"FROM \n",
" page_lengths_w_date\n",
"GROUP BY dt\n",
"\"\"\"\n",
"counts = spark.sql(count_query)\n",
"counts.show()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Sizes of W pages with external links"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+--------+-------------------+------------------+------------------+---------------+\n",
"| dt|average_page_length|median_page_length|stddev_page_length|iqr_page_length|\n",
"+--------+-------------------+------------------+------------------+---------------+\n",
"|20190420| 7676.41| 3865.0| 13632.44456286243| 5789.0|\n",
"|20190401| 7662.35| 3860.0|13599.185396839497| 5779.0|\n",
"+--------+-------------------+------------------+------------------+---------------+\n",
"\n"
]
}
],
"source": [
"# page_lengths of W pages w/ ext links\n",
"w_pl_query = \"\"\"\n",
"SELECT dt, CAST(AVG(page_length) AS DECIMAL(10,2)) AS average_page_length, \n",
" PERCENTILE(page_length,0.5) AS median_page_length,\n",
" STDDEV(page_length) as stddev_page_length,\n",
" (PERCENTILE(page_length,0.75) - PERCENTILE(page_length,0.25)) as iqr_page_length\n",
"FROM \n",
" page_lengths_w_date\n",
"WHERE page_id IN \n",
" (SELECT page_id FROM ryanmax.population_w_pages_with_extlinks)\n",
"GROUP BY dt\n",
"\"\"\"\n",
"\n",
"w_pl_links = spark.sql(w_pl_query)\n",
"w_pl_links.show()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Sizes of WP:M pages with external links"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+--------+-------------------+------------------+------------------+---------------+\n",
"| dt|average_page_length|median_page_length|stddev_page_length|iqr_page_length|\n",
"+--------+-------------------+------------------+------------------+---------------+\n",
"|20190420| 13084.95| 6628.0|19378.442855890593| 11640.0|\n",
"|20190401| 13057.77| 6607.0| 19351.96290494525| 11611.0|\n",
"+--------+-------------------+------------------+------------------+---------------+\n",
"\n"
]
}
],
"source": [
"# page_lengths of WP:M pages w/ ext links\n",
"pm_pl_query = \"\"\"\n",
"SELECT dt, CAST(AVG(page_length) AS DECIMAL(10,2)) AS average_page_length, \n",
" PERCENTILE(page_length,0.5) AS median_page_length,\n",
" STDDEV(page_length) as stddev_page_length,\n",
" (PERCENTILE(page_length,0.75) - PERCENTILE(page_length,0.25)) as iqr_page_length\n",
"FROM \n",
" page_lengths_w_date\n",
"WHERE page_id IN \n",
" (SELECT page_id FROM ryanmax.population_wpm_pages_with_extlinks)\n",
"GROUP BY dt\n",
"\"\"\"\n",
"\n",
"pm_pl_links = spark.sql(pm_pl_query)\n",
"pm_pl_links.show()\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"-------\n",
"## Statistical analysis\n",
"\n",
"Get W page lengths:"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [],
"source": [
"# Plots inline\n",
"%matplotlib inline"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" page_id | \n",
" page_length | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" 1677 | \n",
" 42747.5 | \n",
"
\n",
" \n",
" | 1 | \n",
" 1697 | \n",
" 24512.0 | \n",
"
\n",
" \n",
" | 2 | \n",
" 1806 | \n",
" 162745.5 | \n",
"
\n",
" \n",
"
\n",
"
\n",
"
\n",
" \n",
" \n",
" | \n",
" page_id | \n",
" page_length | \n",
"
\n",
" \n",
" \n",
" \n",
" | 0 | \n",
" 30663599 | \n",
" 19595.0 | \n",
"
\n",
" \n",
" | 1 | \n",
" 31017162 | \n",
" 4067.0 | \n",
"
\n",
" \n",
" | 2 | \n",
" 31546778 | \n",
" 7936.0 | \n",
"
\n",
" \n",
"
\n",
"