<jupyter_start><jupyter_text>This analysis was requested to help inform some discussions about article creation dynamics on the English Wikipedia. See [T149021](https://phabricator.wikimedia.org/T149021) and [T149049](https://phabricator.wikimedia.org/T149049).<jupyter_code>import pandas as pd
import bokeh.plotting as bk
import bokeh
import datetime as dt
from IPython.display import display, HTML
#!/usr/bin/python
import pymysql
import pandas as pd
from impala.dbapi import connect as impala_conn
from impala.util import as_pandas

def try_decode(cell):
    try:
        return cell.decode(encoding = "utf-8")
    except AttributeError:
        return cell
    
def decode_data(d):
    return [{try_decode(key): try_decode(val) for key, val in item.items()} for item in d]

def query_db(query, db = "mariadb", fmt = "pandas"):
    if db not in ["mariadb", "hadoop"]:
        raise ValueError("The db should be `mariadb` or `hadoop`.")
    if fmt not in ["pandas", "raw"]:
        raise ValueError("The format should be either `pandas` or `raw`.")
    
    if db == "mariadb":
        try:
            conn = pymysql.connect(
                host = "analytics-store.eqiad.wmnet",
                read_default_file = '/etc/mysql/conf.d/research-client.cnf',
                charset = 'utf8mb4',
                db='staging',
                cursorclass=pymysql.cursors.DictCursor
            )
            if fmt == "pandas":
                result = pd.read_sql_query(query, conn)
                # Turn any binary data into strings
                result = result.applymap(try_decode)
            elif fmt == "raw":
                cursor = conn.cursor()
                cursor.execute(query)
                result = cursor.fetchall()
                result = decode_data(result)
        finally:
            conn.close()
        
    elif db == "hadoop":
        try:
            hive_conn = impala_conn(host='analytics1003.eqiad.wmnet', port=10000, auth_mechanism='PLAIN')
            hive_cursor = hive_conn.cursor()
            hive_cursor.execute(query)
            if fmt == "pandas":
                result = as_pandas(hive_cursor)
            elif fmt == "raw":
                result = hive_cursor.fetchall()
        finally:
            hive_conn.close()
    
    return result<jupyter_output><empty_output><jupyter_text>Survival of new articles over time Data from the following queries (surviving creations are held in the `revision` table, while deleted creations have been moved to the `archive` table):```select left(rev_timestamp, 6) as month, count(*) as surviving_creationsfrom enwiki.revisionleft join enwiki.page on rev_page = page_idwhere page_namespace = 0 andrev_parent_id = 0 andconvert(rev_comment using utf8) not like "%redir%" andrev_len > 100group by left(rev_timestamp, 6);``````select left(a.ar_timestamp, 6) as month, count(*) as deleted_creationsfrom enwiki.archive ainner join(select ar_title, min(ar_timestamp) as ar_timestampfrom enwiki.archivewherear_namespace = 0 andconvert(ar_comment using utf8) not like "%redir%" andar_len > 100group by ar_title) busing (ar_title, ar_timestamp)group by left(a.ar_timestamp, 6)```<jupyter_code>survived = pd.read_table("2016-10_enwiki_surviving_creations.tsv")
survived.head()
deleted = pd.read_table("2016-10_enwiki_deleted_creations.tsv")
deleted.head()
survival = survived.merge(deleted, on = "month")
survival["pct_survival"] = \
    survival["surviving_creations"] / \
    (survival["surviving_creations"] + survival["deleted_creations"])

# Convert month column to real date
survival["month"] = pd.to_datetime(survival["month"], format = "%Y%m")
survival.set_index(keys = "month", inplace = True)

# Get rid of incomplete data for November
survival.drop(pd.to_datetime("2016-11-01"), inplace = True)

survival.tail()
bokeh.io.output_notebook()

c = bk.figure(width = 800, height = 400, x_axis_type = "datetime", y_range = (0, 1))
c.line(survival.index, survival["pct_survival"], color = "navy", line_width = 2)
c.toolbar.active_drag = None
bk.show(c)<jupyter_output><empty_output><jupyter_text>Article creation by non-autoconfirmed editors  Data Lake The [Data Lake](https://wikitech.wikimedia.org/wiki/Analytics/Data_Lake) makes it possible to include data on deleted articles (I had previously tried this using the MariaDB replicas, but found it unable to handle the complexity of the query).This method gives the answer that 6.6% of article creations are done by non-autoconfirmed editors, which definitely seems too low. Possible explanations starting with the most likely in my view:1. There's simple error in the query1. There's 3. There's an error in the data1. This answer is in fact correct, meaning that ar<jupyter_code>dl_creations_hql = """
select
mediawiki_history.wiki_db,
substr(event_timestamp, 0, 8) as day,
count(*) as creations,
sum(
        if(
            ((unix_timestamp(event_timestamp, 'yyyyMMddHHmmss') -
            unix_timestamp(coalesce(event_user_creation_timestamp, '20050101000000'), 'yyyyMMddHHmmss'))
            < 345600) or
            (user_edits_by_hour.edit_count < 10),
        1, 0)
    )
  as non_autoconfirmed_creations

from wmf.mediawiki_history
       inner join
   (select wiki_db,
           event_user_id,
           hour,
           sum(edit_count) over (partition by wiki_db, event_user_id order by hour) as edit_count
      from (select wiki_db,
                   event_user_id,
                   substr(event_timestamp, 0, 10) as hour,
                   count(*) as edit_count
              from wmf.mediawiki_history
             where event_entity = 'revision'
               and event_type = 'create'
               and wiki_db = 'enwiki'
               and snapshot = '2017-04'
             group by wiki_db, event_user_id, substr(event_timestamp, 0, 10)
           ) user_edits_per_hour
   ) user_edits_by_hour    ON mediawiki_history.wiki_db = user_edits_by_hour.wiki_db
                          AND mediawiki_history.event_user_id = user_edits_by_hour.event_user_id
                          AND substr(event_timestamp, 0, 10) = user_edits_by_hour.hour
where event_entity = 'revision'
  and event_type = 'create'
  and page_namespace = 0
  and mediawiki_history.wiki_db = 'enwiki'
  and revision_parent_id = 0
  and event_comment not regexp "[Rr]edir"
  and snapshot = '2017-04'
  and event_timestamp > '20170101000000'

 group by mediawiki_history.wiki_db, substr(event_timestamp, 0, 8)
 order by mediawiki_history.wiki_db, day
 limit 100000
"""
dl_creations = query_db(dl_creations_hql, db = "hadoop")
dl_creations.tail(n = 20)
# The aggregate percentage of non-autoconfirmed creations over the entire period
(dl_creations["non_autoconfirmed_creations"].sum() / dl_creations["creations"].sum()) * 100<jupyter_output><empty_output><jupyter_text>Recent changes **Summary**: I estimate that about 87% of new articles on the English Wikipedia  are created by autoconfirmed users. (In this case, an article is a main-namespace page which is not a redirect.)This estimate is based on articles created in the week before the query was run, and does *not* include any articles created and then deleted by that time (anywhere from 0 to 7 days after their creation).<jupyter_code>page_creations = """
select 
    page_title,
    page_title_latest,
    page_id,
    event_timestamp,
    revision_id as creation_rev_id,
    revision_text_bytes as length_at_creation,
    event_user_id as creator_id,
    event_user_text as creator_name,
    user_creator_timestamp as creator_registration
from wmf.mediawiki_history
where 
    wiki_db = "enwiki" and
    event_entity = "revision" and
    event_type = "create"
    revision_parent_id = 0
    page_namespace = 0 and
    event_comment not regexp "[Rr]edir" and
    event_timestamp >= "201704" and
    event_timestamp < "201705"
    ;
"""
creations = pd.read_table(
    "2016-10_enwiki_article_creations.tsv",
    parse_dates = [2, 8])
creations.head()
four_d = dt.timedelta(days = 4)
creations["creator_autoconfirmed"] = (
    (creations["user_edit_count"] >= 10) &
    (creations["creation_timestamp"] >= (creations["user_registration"] + four_d))
    )<jupyter_output><empty_output><jupyter_text>However, this leaves a couple entries with null account creations dates because their accounts were created before Mediawiki started recording them. I'll manually set them to be autoconfirmed.<jupyter_code>null_reg = creations[ creations["user_registration"].isnull() ]
null_reg
creations.ix[null_reg.index, "creator_autoconfirmed"] = True
creations[ creations["user_registration"].isnull() ]["creator_autoconfirmed"]
creations.groupby("creator_autoconfirmed").size()<jupyter_output><empty_output><jupyter_text>So 88.1% of creations were by autoconfirmed users. But I think there are still a good number of redirect creations here, even though I filtered out most of them using the edit summary. What if we pull out everything where the inital size was less than 100 bytes and the user was autoconfirmed? From spot-checking, it looks like that should get most of them while not removing too many creations of real stubs.<jupyter_code>to_remove = creations[
    (creations["length_at_creation"] < 100) & 
    (creations["creator_autoconfirmed"] == True)
]

creations = creations.drop(to_remove.index)
creations.groupby("creator_autoconfirmed").size()<jupyter_output><empty_output><jupyter_text>That gives 86.9% of creations by autoconfirmed users. That's likely a better estimate, though it's not a large difference in any case. Ideally, I'd check the text of each revision to know for sure whether it was a redirect at the time of creation. But that would require a lot of API work, and this suggests that it wouldn't change the results much.  Example articles I'll pull out a linked list of the creations from 31 October.<jupyter_code>examples = creations[
    (creations["creation_timestamp"] >= "2016-10-31") &
    (creations["creation_timestamp"] < "2016-11-01")
]

ac_ex = examples[examples["creator_autoconfirmed"] == True]
non_ac_ex = examples[examples["creator_autoconfirmed"] == False]

def print_table(df):
    print("Printing {} rows".format(df.shape[0]))
    output = "<table><tr><th>Page</th><th>Initial version</th></tr>"
    for row in df.iterrows():
        table_row = """
            <tr>
                <td><a href='https://en.wikipedia.org/wiki/{title}'>{title}</a></td>
                <td><a href='http://en.wikipedia.org/wiki/Special:Diff/{rev_id}'>Special:Diff/{rev_id}</a></td>
            </tr>
            """
        output += table_row.format(title = row[1][0], rev_id = row[1][3])
    
    output += "</table>"

    display(HTML(output))<jupyter_output><empty_output><jupyter_text>Autoconfirmed creations<jupyter_code>print_table(ac_ex)<jupyter_output>Printing 695 rows<jupyter_text>Non-autoconfirmed creations<jupyter_code>print_table(non_ac_ex)<jupyter_output>Printing 104 rows
{'fraction_non_alphanumeric': 0.10051404442812557, 'fraction_numerical': 0.021296126308059483, 'mean_word_length': 3.714409346603202, 'pattern_counts': {'":': 6, '<': 58, '<?xml version=': 0, '>': 60, 'https://': 4, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '495625', 'license': 'MIT', 'n_tokens_mistral': 3621, 'n_tokens_neox': 3353, 'n_words': 931, 'path': 'Analysis.ipynb', 'repo_name': 'wikimedia-research/Editing-2016-10-enwiki-article-creation', 'size': '10894'}