Event Data: Infobox and Section¶

# Total count of events (by all event types) in InfoBoxes for WP:M pages only
# Total count of events (by all event types) in the Main section for WP:M pages only
pm_infobox_events_query = """
SELECT in_infobox, action, count(*) AS total_event_count, count(*)/{} AS daily_average_event_count
FROM 
    citationusage 
WHERE wiki = 'enwiki'
    AND page_id IN (SELECT page_id FROM ryanmax.population_wpm_pages_with_extlinks)
    {}
    AND to_date(event_time) >= '{}'
    AND to_date(event_time) <= '{}'
    AND useragent_is_bot = FALSE
GROUP BY in_infobox, action
ORDER BY in_infobox, action
"""

pm_infobox_events = spark.sql(
    pm_infobox_events_query.format(
        days_in_study, event_exclusion_sql, start_date_string, end_date_string
    ))
pm_infobox_events.toPandas()

# Total count of events (by all event types) in InfoBoxes for W pages
# Total count of events (by all event types) in the Main section for W pages
w_infobox_events_query = """
SELECT in_infobox, action, count(*) AS total_event_count, count(*)/{} AS daily_average_event_count
FROM 
    citationusage 
WHERE wiki = 'enwiki'
    AND page_id IN (SELECT page_id FROM ryanmax.population_w_pages_with_extlinks)
    {}
    AND to_date(event_time) >= '{}'
    AND to_date(event_time) <= '{}'
    AND useragent_is_bot = FALSE
GROUP BY in_infobox, action
ORDER BY in_infobox, action
"""

w_infobox_events = spark.sql(
    w_infobox_events_query.format(
        days_in_study, event_exclusion_sql, start_date_string, end_date_string
    ))
w_infobox_events.toPandas()

# Infobox clicks that occurred under a section heading (e.g. External links)
# no limits
infobox_section_events_query = """
SELECT section_id, action, count(*) AS total_event_count, count(*)/{} AS daily_average_event_count
FROM
    citationusage
WHERE
    wiki = 'enwiki'
    AND in_infobox = TRUE
    {}
    AND useragent_is_bot = FALSE
GROUP BY section_id, action
ORDER BY total_event_count desc
"""

infobox_section_events = spark.sql(infobox_section_events_query.format(days_in_study,event_exclusion_sql))
infobox_section_events.show()

section_pda = pm_section_events_pandas.copy()
# replace 'NaN' section_h2 with 'missing'
section_pda.section_h2.fillna(value='-- Infobox event outside of a section --', inplace=True)
# limit to counts of 1K or more
section_pda['count'] = section_pda['count'].astype(int)
df_filtered = section_pda.query('count>=5').copy()
# set precision before pivot
df_filtered['count'] = df_filtered['count'].map(lambda x: '{0:.0f}'.format(x))
df_filtered.pivot(index='section_h2', columns='action', values='count')