06-geo-oversample.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d04fb58b",
   "metadata": {},
   "source": [
    "As an addition to the general stratified random sampling process in 2022, we will oversample by continents to be able to conduct breakout analyses for contributors to large wikis by geographic region. These samples will be integrated into the larger dataset but will be weighted separately as they do not meet the criteria for random (and thus representative) selection into the sample. As an example for why this is necessary: even though there is a substantial active community of enwiki editors in India, the enwiki random sample is mostly occupied by editors from English speaking countries (which reflects the actual population of enwiki editors). The random sample can thus inform us broadly about the global enwiki community, while an additional over-sample is used to understand whether contributors from particular regions report certain experiences differently. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "60f08df7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import wmfdata as wmf\n",
    "import pandas as pd\n",
    "\n",
    "RANDOM_STATE = 55"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "848bed1a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "population (34144, 5)\n",
      "sampled users (25792, 5)\n"
     ]
    }
   ],
   "source": [
    "# listing sampled users from both the intial and the follow-up sample\n",
    "sampling_frame = pd.read_parquet('secrets/sampling-frame.parquet')\n",
    "print('population', sampling_frame.shape)\n",
    "sampled_users = pd.concat([pd.read_table('secrets/sampled_users.tsv'),\n",
    "                           pd.read_table('secrets/followup-sampled-users.tsv')])\n",
    "sampled_users.set_index('user_name', inplace=True)\n",
    "print('sampled users', sampled_users.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8b782a3c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "unsampled users (8352, 5)\n"
     ]
    }
   ],
   "source": [
    "# unsampled users (population - sampled users)\n",
    "unsampled_users = sampling_frame.drop(sampled_users.index)\n",
    "print('unsampled users', unsampled_users.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4467aa0f",
   "metadata": {},
   "source": [
    "## Unsampled users by continent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "67ee265c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_sql_tuple(i):\n",
    "   \n",
    "    if type(i) != list:\n",
    "        i = [x for x in i]\n",
    "\n",
    "    list_repr = repr(i)\n",
    "\n",
    "    return \"(\" + list_repr[1:-1] + \")\"\n",
    "\n",
    "sql_usernames = make_sql_tuple(unsampled_users.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44aa5818",
   "metadata": {},
   "outputs": [],
   "source": [
    "users_geo_query = f\"\"\"\n",
    "WITH users AS (\n",
    "    SELECT \n",
    "        user_name,\n",
    "        user_id, \n",
    "        wiki_db\n",
    "    FROM \n",
    "        wmf_raw.mediawiki_user\n",
    "    WHERE\n",
    "        user_editcount > 10\n",
    "        AND user_name in {sql_usernames}\n",
    "        AND snapshot = '2022-06'\n",
    ")\n",
    "\n",
    "SELECT \n",
    "    DISTINCT users.user_name,\n",
    "    edaily.user_fingerprint_or_id, \n",
    "    edaily.wiki_db, \n",
    "    edaily.country_code\n",
    "FROM \n",
    "    wmf.editors_daily edaily\n",
    "JOIN users \n",
    "    ON edaily.user_fingerprint_or_id = users.user_id AND edaily.wiki_db = users.wiki_db\n",
    "WHERE \n",
    "    user_is_anonymous = false\n",
    "    AND country_code IS NOT NULL\n",
    "    AND country_code <> '--'\n",
    "    AND SIZE(user_is_bot_by) = 0\n",
    "\"\"\"\n",
    "\n",
    "users_geodata = wmf.spark.run(users_geo_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e7dcdaf7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# number of times users log location by country\n",
    "# based on the geo-data if a user has location logged in only one country for all edits, that is considered their location\n",
    "# for users with location in two or more countries, we consider the country from where more than 50% of the edits have been made as their location\n",
    "n_pings = (users_geodata.groupby(['user_name', 'country_code'])\n",
    "           .count()[['user_fingerprint_or_id']]\n",
    "           .rename({'user_fingerprint_or_id': 'count'}, axis=1)\n",
    "           .reset_index())\n",
    "\n",
    "n_pings = (pd.merge(n_pings, (n_pings.groupby('user_name')\n",
    "                              .sum().reset_index()\n",
    "                              .rename({'count': 'total'}, axis=1)), \n",
    "                    on='user_name', how='left')\n",
    "           \n",
    "n_pings['perc'] = n_pings['count'] / n_pings['total']\n",
    "cleaned_geodata = n_pings[n_pings['perc'] > 0.5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b852bc31",
   "metadata": {},
   "outputs": [],
   "source": [
    "# for 2022, geo-oversample is only done for English Wikipedia\n",
    "unsampled_users = pd.merge(unsampled_users, cleaned_geodata[['user_name', 'country_code']], \n",
    "                           on='user_name', how='left')\n",
    "unsampled_users.country_code.isna().sum()\n",
    "unsampled_users.dropna(inplace=True)\n",
    "en_unsampled = unsampled_users.query(\"\"\"project_group == 'enwiki'\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d677d92f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "PySpark executors will use /usr/lib/anaconda-wmf/bin/python3.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>edit_bin</th>\n",
       "      <th>10-29</th>\n",
       "      <th>30-149</th>\n",
       "      <th>150-599</th>\n",
       "      <th>600-1199</th>\n",
       "      <th>1200+</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>maxmind_continent</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Africa</th>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>35</td>\n",
       "      <td>9</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asia</th>\n",
       "      <td>0</td>\n",
       "      <td>247</td>\n",
       "      <td>243</td>\n",
       "      <td>98</td>\n",
       "      <td>258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Europe</th>\n",
       "      <td>0</td>\n",
       "      <td>411</td>\n",
       "      <td>476</td>\n",
       "      <td>210</td>\n",
       "      <td>630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North America</th>\n",
       "      <td>0</td>\n",
       "      <td>725</td>\n",
       "      <td>851</td>\n",
       "      <td>320</td>\n",
       "      <td>964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oceania</th>\n",
       "      <td>0</td>\n",
       "      <td>105</td>\n",
       "      <td>87</td>\n",
       "      <td>44</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South America</th>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>39</td>\n",
       "      <td>16</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "edit_bin           10-29  30-149  150-599  600-1199  1200+\n",
       "maxmind_continent                                         \n",
       "Africa                 0      25       35         9     41\n",
       "Asia                   0     247      243        98    258\n",
       "Europe                 0     411      476       210    630\n",
       "North America          0     725      851       320    964\n",
       "Oceania                0     105       87        44    135\n",
       "South America          0      28       39        16     27"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# unsampled enwiki users by continent\n",
    "# countries-continents mapping from https://github.com/wikimedia-research/canonical-data/\n",
    "cd_countries = wmf.spark.run(\"\"\" SELECT * FROM canonical_data.countries \"\"\").rename({'iso_code': 'country_code'}, axis=1)\n",
    "en_unsampled = pd.merge(en_unsampled, cd_countries[['country_code', 'maxmind_continent']], \n",
    "                        on='country_code', how='left')\n",
    "unsampled_population = en_unsampled.groupby(['maxmind_continent', 'edit_bin']).size().unstack()\n",
    "unsampled_population"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e257b90f",
   "metadata": {},
   "source": [
    "## Sampling targets\n",
    "For 2022, it is all unsampled users from Africa, Asia and Oceania."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "30a08750",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>edit_bin</th>\n",
       "      <th>10-29</th>\n",
       "      <th>30-149</th>\n",
       "      <th>150-599</th>\n",
       "      <th>600-1199</th>\n",
       "      <th>1200+</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>maxmind_continent</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Africa</th>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>35</td>\n",
       "      <td>9</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asia</th>\n",
       "      <td>0</td>\n",
       "      <td>247</td>\n",
       "      <td>243</td>\n",
       "      <td>98</td>\n",
       "      <td>258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Europe</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North America</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oceania</th>\n",
       "      <td>0</td>\n",
       "      <td>105</td>\n",
       "      <td>87</td>\n",
       "      <td>44</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South America</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "edit_bin           10-29  30-149  150-599  600-1199  1200+\n",
       "maxmind_continent                                         \n",
       "Africa                 0      25       35         9     41\n",
       "Asia                   0     247      243        98    258\n",
       "Europe                 0       0        0         0      0\n",
       "North America          0       0        0         0      0\n",
       "Oceania                0     105       87        44    135\n",
       "South America          0       0        0         0      0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sampling_targets = unsampled_population.copy()\n",
    "sampling_targets.loc[['Europe', 'North America', 'South America'], :] = 0\n",
    "sampling_targets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "6c6c226d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>edit_bin</th>\n",
       "      <th>continent</th>\n",
       "      <th>sample_size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1200+</td>\n",
       "      <td>Asia</td>\n",
       "      <td>258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1200+</td>\n",
       "      <td>Europe</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1200+</td>\n",
       "      <td>North America</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1200+</td>\n",
       "      <td>Oceania</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1200+</td>\n",
       "      <td>South America</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   edit_bin      continent  sample_size\n",
       "25    1200+           Asia          258\n",
       "26    1200+         Europe            0\n",
       "27    1200+  North America            0\n",
       "28    1200+        Oceania          135\n",
       "29    1200+  South America            0"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "targets = (sampling_targets\n",
    "           .unstack()\n",
    "           .reset_index()\n",
    "           .rename({0: 'sample_size', 'maxmind_continent': 'continent'}, axis=1))\n",
    "targets.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00b94be0",
   "metadata": {},
   "source": [
    "## Pull and save samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "5bcf3636",
   "metadata": {},
   "outputs": [],
   "source": [
    "def sample_stratum(edit_bin, continent, target_size, df=en_unsampled):\n",
    "    stratum = df[(df.edit_bin == edit_bin) & (df.maxmind_continent == continent)]\n",
    "    return stratum.sample(n=target_size, random_state=RANDOM_STATE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "a02683d5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>edit_bin</th>\n",
       "      <th>10-29</th>\n",
       "      <th>30-149</th>\n",
       "      <th>150-599</th>\n",
       "      <th>600-1199</th>\n",
       "      <th>1200+</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>maxmind_continent</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Africa</th>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>35</td>\n",
       "      <td>9</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asia</th>\n",
       "      <td>0</td>\n",
       "      <td>247</td>\n",
       "      <td>243</td>\n",
       "      <td>98</td>\n",
       "      <td>258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oceania</th>\n",
       "      <td>0</td>\n",
       "      <td>105</td>\n",
       "      <td>87</td>\n",
       "      <td>44</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "edit_bin           10-29  30-149  150-599  600-1199  1200+\n",
       "maxmind_continent                                         \n",
       "Africa                 0      25       35         9     41\n",
       "Asia                   0     247      243        98    258\n",
       "Oceania                0     105       87        44    135"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geo_oversampled_users = pd.DataFrame(columns=en_unsampled.columns)\n",
    "for row in targets.itertuples(index=False):\n",
    "    geo_oversampled_users = pd.concat([geo_oversampled_users, sample_stratum(*row)])\n",
    "geo_oversampled_users = geo_oversampled_users.reset_index(drop=True)\n",
    "geo_oversampled_users.groupby(['maxmind_continent', 'edit_bin']).size().unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "c3463e78",
   "metadata": {},
   "outputs": [],
   "source": [
    "geo_oversampled_users.to_csv('secrets/geo_oversampled_users.tsv', sep='\\t', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}