unique_devices_by_project_family(Aug23-Jan24).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a8136a3a-611c-4596-b6f9-f158a235eca1",
   "metadata": {},
   "source": [
    "# European Unique Devices by Project Family"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "165ccf4b-ae0c-4082-8e50-aaa7922c1568",
   "metadata": {},
   "source": [
    "## Task Description:\n",
    "\n",
    "Create the following datasets and export to a csv file. \n",
    "\n",
    "* Unique Devices by Project Family\n",
    "  * Requested fields: Project Family, 6-month average unique devices\n",
    "  * Filters: Only include countries in European Union (should be available in canonical data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10e90e8d-b2c9-421b-bbdc-f861f9718aaa",
   "metadata": {},
   "source": [
    "## Methodology and Data Sources: \n",
    "\n",
    "Unique devices per project family data comes from the [unique_devices_per_project_family_monthly](https://github.com/wikimedia/analytics-refinery/blob/master/oozie/unique_devices/per_project_family/monthly/unique_devices_per_project_family_monthly.hql). \n",
    "\n",
    "**Metric**: The `uniques_estimate` average over the last 6 months **(August 2023 through Jan 2024)**. Data limited to only countries within European Union.\n",
    "\n",
    "**Important note**: Unique devices by family metrics has been overcounted by approx ~5% globally between Feb 9, 2021 and June 30, 2022.  \n",
    "For more info, read [2021-02-09 Unique Devices By Family Overcount](https://wikitech.wikimedia.org/wiki/Analytics/Data_Lake/Data_Issues/2021-02-09_Unique_Devices_By_Family_Overcount)\n",
    "\n",
    "\n",
    "EU country data is based on data from [canonical_data](https://github.com/wikimedia-research/canonical-data) dataset. \n",
    "\n",
    "Other notes:\n",
    "* Used the new `is_eu` flag from canonical_data.countries to filter EU member countries.\n",
    "* The breakdown of Wikimedia projects will not be equal to Wikimedia project family which is a known consequence of [T325544](https://phabricator.wikimedia.org/T325544) . However, it is recommended to use unique_devices_per_domain_monthly numbers for the wikimedia project breakdown. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "28feb82c-e305-4b64-a73d-7e7c5016043f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import wmfdata as wmf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed6cd632-d988-4fad-bfaa-2cf3f4409171",
   "metadata": {},
   "source": [
    "## Unique Devices by Project Family"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4c57ad82-e1ee-42c4-9b0f-5a1c4c40170d",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''\n",
    "\n",
    "SELECT\n",
    "    project_family,\n",
    "    countries.name AS country, \n",
    "    ud.year,\n",
    "    ud.month,\n",
    "    SUM(ud.uniques_estimate) as unique_devices\n",
    "  FROM wmf.unique_devices_per_project_family_monthly ud,\n",
    "    canonical_data.countries AS countries  \n",
    "  WHERE \n",
    "    ud.country_code = countries.iso_code \n",
    "  -- only European Union countries\n",
    "    AND countries.is_eu  \n",
    "  -- review most recent 6 months available: August 2023 thru Jan 2024\n",
    "    AND (\n",
    "     (YEAR = 2023 AND MONTH > 07)\n",
    "      OR (YEAR = 2024 AND MONTH < 2))\n",
    "  GROUP BY \n",
    "    project_family,\n",
    "    name,\n",
    "    year,\n",
    "    month\n",
    "\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f5c13c40-a280-45ed-b58a-a3723f683f2c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SPARK_HOME: /usr/lib/spark3\n",
      "Using Hadoop client lib jars at 3.2.0, provided by Spark.\n",
      "PYSPARK_PYTHON=/opt/conda-analytics/bin/python3\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n",
      "24/02/06 18:12:52 WARN SparkConf: Note that spark.local.dir will be overridden by the value set by the cluster manager (via SPARK_LOCAL_DIRS in mesos/standalone/kubernetes and LOCAL_DIRS in YARN).\n",
      "24/02/06 18:12:53 WARN Utils: Service 'sparkDriver' could not bind on port 12000. Attempting port 12001.\n",
      "24/02/06 18:12:53 WARN Utils: Service 'SparkUI' could not bind on port 4040. Attempting port 4041.\n",
      "24/02/06 18:13:02 WARN Utils: Service 'org.apache.spark.network.netty.NettyBlockTransferService' could not bind on port 13000. Attempting port 13001.\n",
      "24/02/06 18:13:03 WARN YarnSchedulerBackend$YarnSchedulerEndpoint: Attempted to request executors before the AM has registered!\n",
      "24/02/06 18:13:07 WARN SessionState: METASTORE_FILTER_HOOK will be ignored, since hive.security.authorization.manager is set to instance of HiveAuthorizerFactory.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "unique_devices_byprojectfamily = wmf.spark.run(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ae14d85d-fdcc-4be3-ad97-a8d0dc9c1c1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_total = unique_devices_byprojectfamily.groupby(['project_family', 'year','month']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f5d50c3e-2084-48d7-a93f-ca5a015ba162",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_avg = (unique_devices_byprojectfamily_total.groupby(['project_family']).mean().\n",
    "                                          round().sort_values(by=['unique_devices'], ascending = False).reset_index())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb09d282-5378-436d-a4bf-b54f75583bd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_avg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8206394f-d56d-4ddf-ac35-edc2a467dc28",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_avg.to_csv('unique_devices_byprojectfamily_avg.csv', index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "243adce9-df73-48b0-ace7-ec05d7c033c6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ed5d4c05-30fe-407a-aed3-d7c07d460261",
   "metadata": {},
   "source": [
    "## Unique Devices by Project Family & European Country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "28ec49ce-c0cd-4c59-8b59-ac4bbf6d671c",
   "metadata": {},
   "outputs": [],
   "source": [
    "ud_byprojectfamily_country = unique_devices_byprojectfamily.groupby(['project_family', 'country','year','month']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "81eb964e-8b3e-4875-8ee9-286de0ec51aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_country_avg = (ud_byprojectfamily_country.groupby(['project_family', 'country']).mean().\n",
    "                                              round().sort_values(by=['unique_devices'], ascending = False).reset_index())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f4e7826-6e3d-48cc-9486-f292ef4e44d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_country_avg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "a107163b-b4c6-4985-955d-7a2aa59af336",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_byprojectfamily_country_avg.to_csv('unique_devices_byprojectfamily_country_avg.csv', index = True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b634949d-af17-488c-a738-34106f3b7d71",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f751a38a-e73d-416c-9bc4-644d27ab5a6d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "91a59668-31a0-47c8-be1a-a5868b412f57",
   "metadata": {},
   "source": [
    "### Just Wikimedia Projects\n",
    "Collect unique devices per project in the **wikimedia** domain and country per month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "22b99e56-4306-42e7-854a-e8d3f2d797e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''\n",
    "\n",
    "-- collect unique devices per project in the wikimedia domain and country per month\n",
    "\n",
    "SELECT\n",
    "    wikis.domain_name AS project,\n",
    "    countries.name AS country,\n",
    "    ud.year,\n",
    "    ud.month,\n",
    "    SUM(ud.uniques_estimate) as unique_devices\n",
    "FROM wmf.unique_devices_per_domain_monthly AS ud\n",
    "JOIN canonical_data.countries AS countries \n",
    "    ON ud.country_code = countries.iso_code\n",
    "    \n",
    "-- combine mobile and web domains\n",
    "-- Strip mobile subdomains so mobile and desktop sites are combined. \n",
    "LEFT JOIN canonical_data.wikis AS wikis ON\n",
    "REGEXP_REPLACE(\n",
    "    REGEXP_REPLACE(\n",
    "        -- The canonical domains for Wikidata and MediaWiki.org start with `www`, which \n",
    "        -- gets _replaced_ by the mobile subdomain. Combine the two possibilities for each site.\n",
    "        REGEXP_REPLACE(\n",
    "            REGEXP_REPLACE(domain, \"^m\\\\\\\\.wikidata\", \"www.wikidata\"),\n",
    "        \"^m\\\\\\\\.mediawiki\", \"www.mediawiki\"),\n",
    "    \"^m\\\\\\\\.\", \"\"),\n",
    "\"\\\\\\\\.m\\\\\\\\.\", \".\")= wikis.domain_name\n",
    "\n",
    "-- only European Union countries\n",
    "    WHERE countries.is_eu\n",
    "-- review most recent 6 months available: August 2023 thru Jan 2024\n",
    "    AND (\n",
    "     (YEAR = 2023 AND MONTH > 07)\n",
    "      OR (YEAR = 2024 AND MONTH < 2))\n",
    "  AND ud.domain LIKE '%wikimedia%'\n",
    "  GROUP BY \n",
    "    project,\n",
    "    name,\n",
    "    year,\n",
    "    month\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "9f7bbfbe-2e51-48a2-83c1-a98839d0863a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "unique_devices_wikimedia = wmf.spark.run(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d8f0ee9a-6e2e-4b30-9c7c-a1971f30c000",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_eu   = unique_devices_wikimedia.groupby(['project', 'year', 'month']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "fb085ff9-1288-4f15-b9f0-befc47da5bb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_eu_avg = (unique_devices_wikimedia_eu.groupby(['project']).mean().\n",
    "                                        round().sort_values(by=['unique_devices'], ascending = False).reset_index())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d77f3372-cd13-45dc-87ab-8c6755313583",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_eu_avg "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "4042a097-0d6d-4d99-9186-c01c4de962cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_eu_avg.to_csv('unique_devices_wikimedia_eu_avg.csv', index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8bcbc7e-16f7-4d8b-9639-8b0ca307cdc7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "d3116e9e-b64d-4860-94ff-abe5ee8bfc2d",
   "metadata": {},
   "source": [
    "### Roll up language wikimedia domain into 'wikimedia.org'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "eee5c101-efb4-41ce-b12a-acf8fd54d132",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = '''\n",
    "\n",
    "-- collect unique devices per project in the wikimedia domain and country per month\n",
    "\n",
    "SELECT\n",
    "    --wikis.domain_name AS project,\n",
    "    IF(rlike (wikis.database_code,'^.{2}wikimedia'),'wikimedia.org',wikis.domain_name) AS project,\n",
    "    countries.name AS country,\n",
    "    ud.year,\n",
    "    ud.month,\n",
    "    SUM(ud.uniques_estimate) as unique_devices\n",
    "FROM wmf.unique_devices_per_domain_monthly AS ud\n",
    "JOIN canonical_data.countries AS countries \n",
    "    ON ud.country_code = countries.iso_code\n",
    "-- combine mobile and web domains\n",
    "    \n",
    "-- Strip mobile subdomains so mobile and desktop sites are combined. \n",
    "LEFT JOIN canonical_data.wikis AS wikis ON\n",
    "REGEXP_REPLACE(\n",
    "    REGEXP_REPLACE(\n",
    "        -- The canonical domains for Wikidata and MediaWiki.org start with `www`, which \n",
    "        -- gets _replaced_ by the mobile subdomain. Combine the two possibilities for each site.\n",
    "        REGEXP_REPLACE(\n",
    "            REGEXP_REPLACE(domain, \"^m\\\\\\\\.wikidata\", \"www.wikidata\"),\n",
    "        \"^m\\\\\\\\.mediawiki\", \"www.mediawiki\"),\n",
    "    \"^m\\\\\\\\.\", \"\"),\n",
    "\"\\\\\\\\.m\\\\\\\\.\", \".\")= wikis.domain_name\n",
    "\n",
    "-- only European Union countries\n",
    "    WHERE countries.is_eu = true\n",
    "\n",
    "-- review most recent 6 months available: August 2023 thru Jan 2024\n",
    "    AND (\n",
    "     (YEAR = 2023 AND MONTH > 07)\n",
    "      OR (YEAR = 2024 AND MONTH < 2))\n",
    "  AND ud.domain LIKE '%wikimedia%'\n",
    "  GROUP BY \n",
    "    project,\n",
    "    name,\n",
    "    year,\n",
    "    month\n",
    "'''\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f62c4c05-3c13-41e2-827d-a11cb586cc8e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "unique_devices_wikimedia_rollup = wmf.spark.run(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05376ff3-2491-4424-b59d-18ee58db16e4",
   "metadata": {},
   "source": [
    "*Note*: The breakdown of Wikimedia projects will not be equal to Wikimedia project family which is a known consequence of [T325544](https://phabricator.wikimedia.org/T325544) . But according to [T301403#8478621](https://phabricator.wikimedia.org/T301403#8478621) we understand that we can trust the `unique_devices_per_domain_monthly` numbers and shouldnt report the `unique_devices_per_project_family_monthly` numbers for **wikimedia**, which is what we're doing here. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "2a14ce1d-7968-46af-9493-b4ac5ef44d99",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_rollup_eu   = unique_devices_wikimedia_rollup.groupby(['project', 'year', 'month']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "d1e54e1e-49bc-477c-be95-39d9f80bfe63",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_rollup_eu_avg = (unique_devices_wikimedia_rollup_eu.groupby(['project']).mean().\n",
    "                                        round().sort_values(by=['unique_devices'], ascending = False).reset_index())\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69b63fd2-9a75-4468-be52-84f69fa9dfcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_rollup_eu_avg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "25b780bb-b74b-4ac3-91f9-accd42026022",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_devices_wikimedia_rollup_eu_avg.to_csv('unique_devices_wikimedia_rollup_eu_avg.csv', index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d389191a-58ed-4024-9c3e-2ba46be0e73d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}