Section ID Count and Event Data¶

# basic defaults, including study dates, common SQL exclusions and parquet files for anonymized data
%run -i 'data-defaults.py'

# "raw" section data from captured events to show extent of the capture issue described above
pm_section_events_raw_query = """
SELECT section_id, action, count(*) count
FROM 
    citationusage 
WHERE page_id IN (SELECT page_id FROM ryanmax.population_wpm_pages_with_extlinks)
    AND wiki = 'enwiki'
    {}
    AND to_date(event_time) >= '{}'
    AND to_date(event_time) <= '{}'
    AND useragent_is_bot = FALSE
GROUP BY section_id, action
ORDER BY count desc
LIMIT 100
"""
pm_section_events_raw = spark.sql(
    pm_section_events_raw_query.format(
        event_exclusion_sql, start_date_string, end_date_string
    ))
pm_section_events_raw_pandas = pm_section_events_raw.toPandas()

# Total count of events (by all event types) for each top-level (H2) section ID for WP:M pages only
pm_section_events_query = """
SELECT population_wpm_sections.section_h2, action, count(*) count, count(*)/{} AS daily_average
FROM 
    citationusage
    LEFT JOIN ryanmax.population_wpm_sections 
        ON 
        population_wpm_sections.page_id = citationusage.page_id 
        AND population_wpm_sections.section_id = citationusage.section_id
WHERE
    wiki = 'enwiki'
    AND citationusage.page_id IN (SELECT page_id FROM ryanmax.population_wpm_pages_with_extlinks)
    {}
    AND to_date(citationusage.event_time) >= '{}'
    AND to_date(citationusage.event_time) <= '{}'
    AND useragent_is_bot = FALSE
GROUP BY population_wpm_sections.section_h2, action
ORDER BY count desc
"""
pm_section_events = spark.sql(
    pm_section_events_query.format(
        days_in_study,
        event_exclusion_sql, start_date_string, end_date_string
    ))
pm_section_events_pandas = pm_section_events.toPandas()

# count of events by event type and access mode for each top-level (H2) section ID for WP:M pages only
pm_section_events_mode_query = """
SELECT population_wpm_sections.section_h2, action, mode, count(*) count
FROM 
    citationusage
    LEFT JOIN ryanmax.population_wpm_sections 
        ON 
        population_wpm_sections.page_id = citationusage.page_id 
        AND population_wpm_sections.section_id = citationusage.section_id
WHERE
    wiki = 'enwiki'
    AND citationusage.page_id IN (SELECT page_id FROM ryanmax.population_wpm_pages_with_extlinks)
    {}
    AND to_date(citationusage.event_time) >= '{}'
    AND to_date(citationusage.event_time) <= '{}'
    AND useragent_is_bot = FALSE
GROUP BY population_wpm_sections.section_h2, action, mode
ORDER BY count desc
"""
pm_section_events_mode = spark.sql(
    pm_section_events_mode_query.format(
        event_exclusion_sql, start_date_string, end_date_string
    ))
pm_section_events_mode_pandas = pm_section_events_mode.toPandas()

# event counts for pages with Adverse_effects section H2s
adverse_events_query = """
SELECT citationusage.page_id, population_page_titles_20190420.page_title, count(*) AS event_count
FROM 
    citationusage
    LEFT JOIN ryanmax.population_wpm_sections 
        ON 
        population_wpm_sections.page_id = citationusage.page_id 
        AND population_wpm_sections.section_id = citationusage.section_id
    LEFT JOIN ryanmax.population_page_titles_20190420
        ON
        population_page_titles_20190420.page_id = citationusage.page_id 
WHERE
    wiki = 'enwiki'
    AND population_wpm_sections.section_h2 = 'Adverse_effects'
    AND citationusage.page_id IN (SELECT page_id FROM ryanmax.population_wpm_pages_with_extlinks)
    {}
    AND to_date(citationusage.event_time) >= '{}'
    AND to_date(citationusage.event_time) <= '{}'
    AND useragent_is_bot = FALSE
GROUP BY citationusage.page_id, population_page_titles_20190420.page_title
ORDER BY event_count desc
"""
adverse_events = spark.sql(
    adverse_events_query.format(
        event_exclusion_sql, start_date_string, end_date_string
    ))
adverse_events_pandas = adverse_events.toPandas()