<jupyter_start><jupyter_text>Humans by groupAuthor: <NAME> This notebook is designed to estimate the number of humans who are in a particular user group. It can only run in the internal analytics cluster, as it requires access to unredacted `user` table, as that is where system-ness of an account can be easily determined.The "is human" estimation is done by:* ignoring known system accounts* ignoring bot accountsThis is based on [a report made in April 2021](https://people.wikimedia.org/~urbanecm/growth-team/sysops-per-wiki-20210429.html) for the Growth team.<jupyter_code>from wmfdata import mariadb, utils
import pandas as pd

pd.set_option('display.max_rows', None)
dfs = []

private = utils.get_dblist('private')
closed = utils.get_dblist('closed')
for wiki in utils.get_dblist('all'):
    if wiki in private or wiki in closed:
        continue
    
    if wiki == 'labtestwiki':
        continue
    
    try:
        dfs.append(mariadb.run('''
        SELECT
            DATABASE() AS wiki,
            ug_expiry IS NULL AS permanent,
            ug_group,
            COUNT(*) AS users
        FROM user_groups
        -- we need the user table, to be able to exclude system accounts
        JOIN user ON user_id=ug_user
        WHERE
            -- Ignore system accounts
            user_token NOT LIKE "%INVALID%" AND

            -- Ignore bots
            ug_user NOT IN (
                SELECT ug_user FROM user_groups WHERE ug_group="bot"
            )
        GROUP BY
            ug_expiry IS NULL,
            ug_group
        ''', wiki))
    except ValueError:
        pass

df = pd.concat(dfs).reset_index(drop=True).fillna(0)
dfPivot = df.loc[df.permanent==1][['wiki', 'ug_group', 'users']].pivot_table(index='wiki', columns=['ug_group'], values='users', fill_value=0)
dfPivot.to_json('/home/urbanecm/Documents/steward/2021-wikimedia-humans-per-group/data/permanent-users-per-group.json')
dfPivot = df.loc[df.permanent==0][['wiki', 'ug_group', 'users']].pivot_table(index='wiki', columns=['ug_group'], values='users', fill_value=0)
dfPivot.to_json('/home/urbanecm/Documents/steward/2021-wikimedia-humans-per-group/data/temporary-users-per-group.json')
dfPivot = df[['wiki', 'ug_group', 'users']].groupby(['wiki', 'ug_group']).sum().pivot_table(index='wiki', columns=['ug_group'], values='users', fill_value=0)
dfPivot.to_json('/home/urbanecm/Documents/steward/2021-wikimedia-humans-per-group/data/total-users-per-group.json')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12454655380894801, 'fraction_numerical': 0.012091898428053204, 'mean_word_length': 3.369718309859155, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 6, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '496131', 'license': 'Apache-2.0', 'n_tokens_mistral': 834, 'n_tokens_neox': 789, 'n_words': 198, 'path': 'humans_by_group.ipynb', 'repo_name': 'urbanecm/2021-wikimedia-humans-per-group', 'size': '2489'}