<jupyter_start><jupyter_text>Proportion of edits done by IP usersThis is part of [T231605](https://phabricator.wikimedia.org/T231605), covering the first part that looks at the proportion of edits done by IP users across projects.Specifically:* How many edits are made by IP editors on our projects?It would be preferable to have this broken down by project (e.g. Wikipedias, Wikisource, etc…) Breaking it down by wiki and project groupI'll reuse [Neil's code for getting canonical data about wikis](https://github.com/wikimedia-research/canonical-data/blob/master/generation/wikis.ipynb) to be able to get data by project.<jupyter_code>import json
import re

import pandas as pd
import numpy as np
import requests
from wmfdata import mariadb, hive

import tabulate
def get_dblist(list_name):
    list_url = ("https://noc.wikimedia.org/conf/dblists/" + list_name + ".dblist")
    list_content = requests.get(list_url).text.split("\n")
    return pd.Series(list_content)
wikis = mariadb.run("""
select
    site_global_key as database_code,
    concat(trim(leading "." from reverse(site_domain))) as domain_name,
    site_group as database_group,
    site_language as language_code
from enwiki.sites
""", "enwiki").sort_values("database_code").set_index("database_code")

wikis.head(10)
lang_urls = [
    "https://raw.githubusercontent.com/wikimedia/mediawiki-extensions-cldr/master/CldrNames/CldrNamesEn.php",
    "https://raw.githubusercontent.com/wikimedia/mediawiki-extensions-cldr/master/LocalNames/LocalNamesEn.php"
]

def get_lang_names(url):
    r = requests.get(url)
    m = re.search(r"languageNames = (\[[\s\S]+?\])", r.text)
    php_ln = m.group(1)
    
    json_ln = php_ln
    repl = [
        # Convert from PHP array format to JSON
        (" =>", ":"),
        ("\[", "{"),
        ("\]", "}"),
        # Trailing commas will cause problems
        (",\n}", "\n}"),
        # ...so will single quotes
        ("'", '"'),
        # ...and comments
        (r"/\*[\s\S]*?\*/", ""),
        (r"#(.*?)\n", ""),
        # One hack to deal with a single quote in a language name
        ('O"odham', "O'odham")
    ]
    for old, new in repl:
        json_ln = re.sub(old, new, json_ln)
    
    py_ln = json.loads(json_ln)
    return py_ln

langs = {}
for url in lang_urls:
    langs.update(get_lang_names(url))

# Add languages not included in the CLDR files
langs.update({
    "als": "Alsatian",
    "atj": "Atikamekw",
    "diq": "Zazaki",
    "fiu-vro": "Võro",
    "map-bms": "Banyumasan",
    "nah": "Nahuatl",
    "pih": "Norfuk-Pitkern",
    "rmy": "Vl<NAME>",
    "simple": "Simple English"
})

wikis["language_name"] = wikis["language_code"].apply(langs.get)

wikis.head(10)
closed = get_dblist("closed")
private = get_dblist("private")

def apply_to_index(df, true_list, true_label, false_label):
    idx_ser = df.index.to_series()
    return idx_ser.isin(true_list).apply(lambda x: true_label if x else false_label)

wikis = (
    wikis
    .assign(
        status=lambda df: apply_to_index(df, closed, "closed", "open"),
        visbility=lambda df: apply_to_index(df, private, "private", "public")
    )
)

wikis.head(10)<jupyter_output><empty_output><jupyter_text>With this data about wikis, we can then limit the analysis to only open and publicly available wikis. Determining time frameWe'd like to be able to get an overview as well as likely drilling down a bit. Therefore, I'll gather data for the previous year (September 2018 through August 2019) on a monthly basis. This data can then be combined to get quarterly, semi-annualy, and annual statistics. We can also use it to get range values for each wiki/project. DefinitionsLet's make some definitions and describe some limitations.1. We'll only measure this for wikis that are open and public per their definition in the `wikis` DataFrame we have gathered.2. We'll use the Data Lake to gather data, meaning that the Mediawiki History table is our authoritative source of data.3. We'll ignore all edits where `event_user_id IS NULL`, meaning the user has been revision deleted.4. We'll use the `event_user_is_anonymous` column to determine if an edit is by a non-registered user. If the value is `true` the user is non-registered, and `false` means the user is registered.5. We'll count bot edits separately by checking whether `event_user_is_bot_by`  or `event_user_is_bot_by_historical` is set. This means that a user that at some point has been labelled a bot will always be labelled a bot. While this is more in line with enwiki policy (bot accounts are separate, identifiable, and approved) rather than other wikis (e.g. where bots can run without a bot flag), we see it as unlikely that active accounts change their status on a regular basis.Update: it looks like `event_user_id` cannot be used in this way, the column is always `NULL` for anonymous edits. I've filed [T232171](https://phabricator.wikimedia.org/T232171) about this, not sure if my understanding of the documentation is correct.Update: the documentation had a bug, which is now fixed. I've switched to using `event_user_is_anonmyous` and updated the description above.I can identify revisions where the user has been deleted using `revision_deleted_parts`, it will contain `user` if that info is deleted. Then, I should be able to use `event_user_is_anonymous = true/false` to separate between edits by IPs and registered users. Data gatheringBased on the definitions above, we can write the Hive query below to gather data for all wikis using the Data Lake.<jupyter_code>edit_count_query = '''
SELECT wiki_db, DATE_FORMAT(event_timestamp, "yyyy-MM-01") AS log_month,
  SUM(1) AS num_total_edits,
  SUM(IF(event_user_is_anonymous = true, 1, 0)) AS num_ip_edits,
  SUM(IF(event_user_is_anonymous = false, 1, 0)) AS num_reg_edits,
  SUM(IF(event_user_is_anonymous = false
         AND (SIZE(event_user_is_bot_by) > 0
              OR SIZE(event_user_is_bot_by_historical) > 0), 1, 0)) AS num_bot_edits
FROM wmf.mediawiki_history
WHERE snapshot = "{snapshot}"
AND event_entity = "revision"
AND event_type = "create"
AND array_contains(revision_deleted_parts, 'user') = false -- skip revisions w/deleted user info
AND event_timestamp >= "{start_date}"
AND event_timestamp < "{end_date}"
AND wiki_db IN ({wiki_list})
GROUP BY wiki_db, DATE_FORMAT(event_timestamp, "yyyy-MM-01")
LIMIT 50000
'''
wikis.loc[(wikis.status == 'open') & (wikis.visbility == 'public')].index.tolist()[:10]
## NOTE: we limit the data gathering of edit counts to only open and public wikis here.
allprojects_edit_counts = hive.run(
    edit_count_query.format(
        snapshot = '2019-08',
        start_date = '2018-09-01',
        end_date = '2019-09-01',
        wiki_list = ', '.join(
            ['"{}"'.format(w) for w in wikis.loc[(wikis.status == 'open') &
                                                 (wikis.visbility == 'public')].index.tolist()])
    )
)
allprojects_edit_counts.loc[allprojects_edit_counts.wiki_db == 'enwiki']<jupyter_output><empty_output><jupyter_text>Yearly statsOver the past year, how many edits did we have across our projects (wikipedias, wikisources, etc), and of those, how many were IPs, registered users, and bots?<jupyter_code>wikis.head()
yearly_stats = (allprojects_edit_counts.set_index('wiki_db')
                .merge(wikis[['database_group']], left_index = True, right_index = True)
                .reset_index().groupby('database_group').sum())<jupyter_output><empty_output><jupyter_text>Note: it might be confusing to see all the wikimedia projects in the yearly stats. Those are the local chapter wikis. Would perhaps be useful if they had their own database group designation (e.g. wikimedia, but that might be reserved for the WMF).We want yearly counts and percentages. But, `num_total_edits` doesn't necessarily equal `num_ip_edits + num_reg_edits`.<jupyter_code>yearly_stats.loc[yearly_stats.num_total_edits == yearly_stats.num_ip_edits + yearly_stats.num_reg_edits]<jupyter_output><empty_output><jupyter_text>I've cleared the output, but there are several projects where it does match. While I can ponder on the conditions for why these might not equal each other, I'll instead use `num_total_edits` as the denominator, and note that things might not add up to 100.0%<jupyter_code>yearly_stats['prop_ip'] = 100 * yearly_stats.num_ip_edits / yearly_stats.num_total_edits
yearly_stats['prop_reg'] = 100 * yearly_stats.num_reg_edits / yearly_stats.num_total_edits
yearly_stats['prop_bots'] = 100 * yearly_stats.num_bot_edits / yearly_stats.num_total_edits
yearly_stats.dtypes<jupyter_output><empty_output><jupyter_text>What are the projects we're interested in? I propose the following, although I can see the Outreach wiki also being one of them.<jupyter_code>projects = ['commons', 'mediawiki', 'meta', 'wikibooks', 'wikidata', 'wikinews', 'wikipedia',
            'wikiquote', 'wikisource', 'wikiversity', 'wikivoyage', 'wiktionary']
print(
    tabulate.tabulate(
        yearly_stats.loc[projects]
        [['num_total_edits', 'num_ip_edits', 'prop_ip', 'num_reg_edits', 'prop_reg',
          'num_bot_edits', 'prop_bots']]
        .rename(
            columns = {'num_total_edits' : 'N Total edits',
                       'num_ip_edits' : 'N IP edits',
                       'prop_ip' : 'IP proportion',
                       'num_reg_edits' : 'N Registered edits',
                       'prop_reg' : 'Registered proportion',
                       'num_bot_edits' : 'N Bot edits',
                       'prop_bots' : 'Bot proportion'}
        ), headers = 'keys', tablefmt = 'github',
        floatfmt=["f", ".0f", ".0f", ".1f", ".0f", ".1f", ".0f", ".1f"]
    )
)
print(
    tabulate.tabulate(
        yearly_stats.loc[projects]
        [['num_total_edits', 'num_ip_edits', 'prop_ip', 'num_reg_edits', 'prop_reg',
          'num_bot_edits', 'prop_bots']]
        .rename(
            columns = {'num_total_edits' : 'N Total edits',
                       'num_ip_edits' : 'N IP edits',
                       'prop_ip' : 'IP proportion',
                       'num_reg_edits' : 'N Registered edits',
                       'prop_reg' : 'Registered proportion',
                       'num_bot_edits' : 'N Bot edits',
                       'prop_bots' : 'Bot proportion'}
        ), headers = 'keys', tablefmt = 'mediawiki',
        floatfmt=["", ".0f", ".0f", ".1f", ".0f", ".1f", ".0f", ".1f"]
    )
)
## I need this so I can replace all the scientific notation with the full integers.
yearly_stats.loc[projects]<jupyter_output><empty_output><jupyter_text>Per-project statisticsFor each of the overarching projects, we want to split it out by language, calculate monthly averages and provide min/max values as well.<jupyter_code>per_project_stats = (allprojects_edit_counts.set_index('wiki_db')
                     .merge(wikis[['database_group', 'language_name']],
                            left_index = True, right_index = True)
                     .reset_index().rename(columns = {'index': 'wiki_db'}))
per_project_stats = per_project_stats.loc[per_project_stats.database_group.isin(projects)]
per_project_stats.head()
def monthly_overview(group):
    aggs = {
        'mean_total_edits' : group.num_total_edits.mean(),
        'mean_ip_edits' : group.num_ip_edits.mean(),
        'mean_reg_edits' : group.num_reg_edits.mean(),
        'mean_bot_edits' : group.num_bot_edits.mean(),
        'mean_prop_ip_edits' : 100 * group.num_ip_edits.mean() / group.num_total_edits.mean(),
        'min_prop_ip_edits' : 100 * np.min(group.num_ip_edits / group.num_total_edits),
        'max_prop_ip_edits' : 100 * np.max(group.num_ip_edits / group.num_total_edits),
        'mean_prop_reg_edits' : 100 * group.num_reg_edits.mean() / group.num_total_edits.mean(),
        'min_prop_reg_edits' : 100 * np.min(group.num_reg_edits / group.num_total_edits),
        'max_prop_reg_edits' : 100 * np.max(group.num_reg_edits / group.num_total_edits),
        'mean_prop_bot_edits' : 100 * group.num_bot_edits.mean() / group.num_total_edits.mean(),
        'min_prop_bot_edits' : 100 * np.min(group.num_bot_edits / group.num_total_edits),
        'max_prop_bot_edits' : 100 * np.max(group.num_bot_edits / group.num_total_edits),
    }
    return(pd.Series(aggs, index = aggs.keys()))
per_project_stats_agg = per_project_stats.groupby(['database_group', 'language_name']).apply(monthly_overview)
per_project_stats_agg = per_project_stats_agg.reset_index().set_index('database_group')<jupyter_output><empty_output><jupyter_text>Note: It might be possible to get numbers formatted properly in MediaWiki by converting from a number to a string (so it's `"{{formatnum:\d+}}"`) and then perhaps use `stralign` in `tabulate.tabulate` to right-align the relevant columns. Will have to try that for a later iteration.<jupyter_code>def print_project_group(df, group_name, table_format = 'mediawiki', precision = 1):
    '''
    From the aggregated project statistics, print out a table of the monthly average statistics
    using the given table format (default is a wikitable). Expects the DataFrame `df` to have
    an index so that `group_name` matches.
    '''
    
    column_order = ['language_name', 'mean_total_edits', 'mean_ip_edits', 'mean_prop_ip_edits',
                    'min_prop_ip_edits', 'max_prop_ip_edits']
    
    column_renaming = {
        'language_name' : 'Language',
        'mean_total_edits' : 'Monthly total average',
        'mean_ip_edits' : 'Monthly IP average',
        'mean_prop_ip_edits' : 'IP % average',
        'min_prop_ip_edits' : 'Min. IP %',
        'max_prop_ip_edits' : 'Max. IP %',
    }

    print(
        tabulate.tabulate(
            df.loc[group_name].reset_index()[column_order].rename(columns = column_renaming),
            headers = 'keys', tablefmt = table_format, showindex = False, numalign = 'right',
            floatfmt = ['', '.1f', '.1f', '.1f', '.1f', '.1f']
        )
    )
projects
print_project_group(per_project_stats_agg, ['commons'])
print_project_group(per_project_stats_agg, ['mediawiki'])
print_project_group(per_project_stats_agg, ['meta'])
print_project_group(per_project_stats_agg, ['wikidata'])
print_project_group(per_project_stats_agg, 'wikibooks')
print_project_group(per_project_stats_agg, 'wikinews')
print_project_group(per_project_stats_agg, 'wikipedia')
print_project_group(per_project_stats_agg, 'wikiquote')
print_project_group(per_project_stats_agg, 'wikisource')
print_project_group(per_project_stats_agg, 'wikiversity')
print_project_group(per_project_stats_agg, 'wikivoyage')
print_project_group(per_project_stats_agg, 'wiktionary')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11565276828434723, 'fraction_numerical': 0.009979494190020506, 'mean_word_length': 3.860797342192691, 'pattern_counts': {'":': 10, '<': 35, '<?xml version=': 0, '>': 38, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '107783', 'license': 'MIT', 'n_tokens_mistral': 4938, 'n_tokens_neox': 4741, 'n_words': 1281, 'path': 'prop_edits.ipynb', 'repo_name': 'wikimedia-research/AHT-IP-edits-2019', 'size': '14633'}