# cf. http://paws-public.wmflabs.org/paws-public/User:Jtmorgan/ds4ux/paws-cheatsheet.ipynb
import os 
"""
Your db login credentials are stored in os.environ. 
DO NOT print or run os.environ, or it will expose your credentials in the Notebook
"""
import pymysql
import pandas as pd
import numpy as np
%matplotlib inline
import matplotlib.pyplot as plt
import matplotlib.dates
import seaborn as sns
from collections import defaultdict
import collections
from datetime import datetime
# reproduce Kaldari's backlog query
%%time
!date

conn2 = pymysql.connect(
    host=os.environ['MYSQL_HOST'],
    user=os.environ['MYSQL_USERNAME'],
    password=os.environ['MYSQL_PASSWORD'],
    database='enwiki_p',
    charset='utf8'
)

with conn2.cursor() as cur:
    cur.execute("""
    select COUNT(ptrp_page_id) AS reviewed_count from enwiki_p.pagetriage_page, enwiki_p.page 
    where ptrp_reviewed = 0 and page_id = ptrp_page_id and page_namespace = 0 AND page_is_redirect=0;""")
    df = cur.fetchall()
Wed May 24 22:29:31 UTC 2017
CPU times: user 8 ms, sys: 8 ms, total: 16 ms
Wall time: 5.53 s
%%time
#get count of unreviewed pages per creation day, by autoconfirmed status
!date

conn2 = pymysql.connect(
    host=os.environ['MYSQL_HOST'],
    user=os.environ['MYSQL_USERNAME'],
    password=os.environ['MYSQL_PASSWORD'],
    database='enwiki_p',
    charset='utf8'
)

with conn2.cursor() as cur:
    cur.execute("""
    select LEFT(ptrp_created,8) AS created_yyyymmdd, COUNT(ptrp_page_id) AS unreviewed_count, ptrpt_value
    FROM enwiki_p.pagetriage_page, enwiki_p.pagetriage_page_tags, enwiki_p.page 
    where ptrp_reviewed = 0 and page_id = ptrp_page_id and page_namespace = 0 AND page_is_redirect=0 AND ptrp_page_id = ptrpt_page_id AND ptrpt_tag_id = 13
    GROUP BY created_yyyymmdd, ptrpt_value ORDER BY created_yyyymmdd, ptrpt_value
    """)
    queryresult = cur.fetchall()
Thu May 25 00:38:50 UTC 2017
CPU times: user 12 ms, sys: 12 ms, total: 24 ms
Wall time: 5.75 s
queryresult
((b'20060806', 1, b'1'),
 (b'20080609', 1, b'1'),
 (b'20080926', 1, b'1'),
 (b'20100507', 1, b'1'),
 (b'20111129', 1, b'1'),
 (b'20131209', 1, b'1'),
 (b'20131210', 1, b'1'),
 (b'20150305', 1, b'1'),
 (b'20150506', 1, b'1'),
 (b'20150910', 1, b'1'),
 (b'20160328', 1, b'1'),
 (b'20160330', 1, b'1'),
 (b'20160331', 1, b'1'),
 (b'20160401', 1, b'1'),
 (b'20160706', 1, b'1'),
 (b'20160728', 1, b'1'),
 (b'20161013', 1, b'1'),
 (b'20161121', 1, b'0'),
 (b'20161130', 1, b'1'),
 (b'20161215', 1, b'1'),
 (b'20161217', 18, b'1'),
 (b'20161218', 44, b'1'),
 (b'20161219', 84, b'1'),
 (b'20161220', 64, b'1'),
 (b'20161221', 81, b'1'),
 (b'20161222', 81, b'1'),
 (b'20161223', 68, b'1'),
 (b'20161224', 57, b'1'),
 (b'20161225', 70, b'1'),
 (b'20161226', 86, b'1'),
 (b'20161227', 115, b'1'),
 (b'20161228', 100, b'1'),
 (b'20161229', 170, b'1'),
 (b'20161230', 120, b'1'),
 (b'20161231', 64, b'1'),
 (b'20170101', 83, b'1'),
 (b'20170102', 106, b'1'),
 (b'20170103', 155, b'1'),
 (b'20170104', 114, b'1'),
 (b'20170105', 124, b'1'),
 (b'20170106', 123, b'1'),
 (b'20170107', 99, b'1'),
 (b'20170108', 140, b'1'),
 (b'20170109', 168, b'1'),
 (b'20170110', 78, b'1'),
 (b'20170111', 115, b'1'),
 (b'20170112', 124, b'1'),
 (b'20170113', 111, b'1'),
 (b'20170114', 77, b'1'),
 (b'20170115', 128, b'1'),
 (b'20170116', 103, b'1'),
 (b'20170117', 136, b'1'),
 (b'20170118', 146, b'1'),
 (b'20170119', 84, b'1'),
 (b'20170120', 33, b'1'),
 (b'20170121', 87, b'1'),
 (b'20170122', 39, b'1'),
 (b'20170123', 40, b'1'),
 (b'20170124', 55, b'1'),
 (b'20170125', 74, b'1'),
 (b'20170126', 39, b'1'),
 (b'20170127', 78, b'1'),
 (b'20170128', 106, b'1'),
 (b'20170129', 20, b'1'),
 (b'20170130', 69, b'1'),
 (b'20170131', 104, b'1'),
 (b'20170201', 82, b'1'),
 (b'20170202', 112, b'1'),
 (b'20170203', 130, b'1'),
 (b'20170204', 97, b'1'),
 (b'20170205', 37, b'1'),
 (b'20170206', 44, b'1'),
 (b'20170207', 77, b'1'),
 (b'20170208', 132, b'1'),
 (b'20170209', 123, b'1'),
 (b'20170210', 85, b'1'),
 (b'20170211', 105, b'1'),
 (b'20170212', 103, b'1'),
 (b'20170213', 117, b'1'),
 (b'20170214', 136, b'1'),
 (b'20170215', 97, b'1'),
 (b'20170216', 134, b'1'),
 (b'20170217', 111, b'1'),
 (b'20170218', 93, b'1'),
 (b'20170219', 118, b'1'),
 (b'20170220', 134, b'1'),
 (b'20170221', 68, b'1'),
 (b'20170222', 67, b'1'),
 (b'20170223', 94, b'1'),
 (b'20170224', 154, b'1'),
 (b'20170225', 131, b'1'),
 (b'20170226', 166, b'1'),
 (b'20170227', 124, b'1'),
 (b'20170228', 119, b'1'),
 (b'20170301', 92, b'1'),
 (b'20170302', 147, b'1'),
 (b'20170303', 156, b'1'),
 (b'20170304', 135, b'1'),
 (b'20170305', 162, b'1'),
 (b'20170306', 172, b'1'),
 (b'20170307', 169, b'1'),
 (b'20170308', 145, b'1'),
 (b'20170309', 143, b'1'),
 (b'20170310', 145, b'1'),
 (b'20170311', 176, b'1'),
 (b'20170312', 167, b'1'),
 (b'20170313', 145, b'1'),
 (b'20170314', 229, b'1'),
 (b'20170315', 160, b'1'),
 (b'20170316', 174, b'1'),
 (b'20170317', 141, b'1'),
 (b'20170318', 188, b'1'),
 (b'20170319', 177, b'1'),
 (b'20170320', 161, b'1'),
 (b'20170321', 140, b'1'),
 (b'20170322', 124, b'1'),
 (b'20170323', 1, b'0'),
 (b'20170323', 154, b'1'),
 (b'20170324', 4, b'0'),
 (b'20170324', 184, b'1'),
 (b'20170325', 2, b'0'),
 (b'20170325', 155, b'1'),
 (b'20170326', 3, b'0'),
 (b'20170326', 133, b'1'),
 (b'20170327', 5, b'0'),
 (b'20170327', 191, b'1'),
 (b'20170328', 9, b'0'),
 (b'20170328', 190, b'1'),
 (b'20170329', 11, b'0'),
 (b'20170329', 173, b'1'),
 (b'20170330', 17, b'0'),
 (b'20170330', 167, b'1'),
 (b'20170331', 9, b'0'),
 (b'20170331', 127, b'1'),
 (b'20170401', 2, b'0'),
 (b'20170401', 55, b'1'),
 (b'20170402', 7, b'0'),
 (b'20170402', 138, b'1'),
 (b'20170403', 7, b'0'),
 (b'20170403', 141, b'1'),
 (b'20170404', 15, b'0'),
 (b'20170404', 182, b'1'),
 (b'20170405', 14, b'0'),
 (b'20170405', 193, b'1'),
 (b'20170406', 14, b'0'),
 (b'20170406', 181, b'1'),
 (b'20170407', 18, b'0'),
 (b'20170407', 203, b'1'),
 (b'20170408', 9, b'0'),
 (b'20170408', 144, b'1'),
 (b'20170409', 7, b'0'),
 (b'20170409', 173, b'1'),
 (b'20170410', 17, b'0'),
 (b'20170410', 162, b'1'),
 (b'20170411', 8, b'0'),
 (b'20170411', 149, b'1'),
 (b'20170412', 9, b'0'),
 (b'20170412', 174, b'1'),
 (b'20170413', 3, b'0'),
 (b'20170413', 103, b'1'),
 (b'20170414', 155, b'1'),
 (b'20170415', 2, b'0'),
 (b'20170415', 163, b'1'),
 (b'20170416', 1, b'0'),
 (b'20170416', 123, b'1'),
 (b'20170417', 150, b'1'),
 (b'20170418', 4, b'0'),
 (b'20170418', 172, b'1'),
 (b'20170419', 4, b'0'),
 (b'20170419', 179, b'1'),
 (b'20170420', 2, b'0'),
 (b'20170420', 155, b'1'),
 (b'20170421', 6, b'0'),
 (b'20170421', 138, b'1'),
 (b'20170422', 5, b'0'),
 (b'20170422', 154, b'1'),
 (b'20170423', 3, b'0'),
 (b'20170423', 118, b'1'),
 (b'20170424', 8, b'0'),
 (b'20170424', 89, b'1'),
 (b'20170425', 14, b'0'),
 (b'20170425', 185, b'1'),
 (b'20170426', 10, b'0'),
 (b'20170426', 187, b'1'),
 (b'20170427', 6, b'0'),
 (b'20170427', 149, b'1'),
 (b'20170428', 19, b'0'),
 (b'20170428', 173, b'1'),
 (b'20170429', 7, b'0'),
 (b'20170429', 240, b'1'),
 (b'20170430', 8, b'0'),
 (b'20170430', 242, b'1'),
 (b'20170501', 15, b'0'),
 (b'20170501', 220, b'1'),
 (b'20170502', 19, b'0'),
 (b'20170502', 196, b'1'),
 (b'20170503', 15, b'0'),
 (b'20170503', 207, b'1'),
 (b'20170504', 21, b'0'),
 (b'20170504', 203, b'1'),
 (b'20170505', 26, b'0'),
 (b'20170505', 199, b'1'),
 (b'20170506', 8, b'0'),
 (b'20170506', 132, b'1'),
 (b'20170507', 18, b'0'),
 (b'20170507', 192, b'1'),
 (b'20170508', 24, b'0'),
 (b'20170508', 159, b'1'),
 (b'20170509', 29, b'0'),
 (b'20170509', 221, b'1'),
 (b'20170510', 24, b'0'),
 (b'20170510', 179, b'1'),
 (b'20170511', 21, b'0'),
 (b'20170511', 126, b'1'),
 (b'20170512', 23, b'0'),
 (b'20170512', 149, b'1'),
 (b'20170513', 13, b'0'),
 (b'20170513', 136, b'1'),
 (b'20170514', 18, b'0'),
 (b'20170514', 171, b'1'),
 (b'20170515', 16, b'0'),
 (b'20170515', 204, b'1'),
 (b'20170516', 15, b'0'),
 (b'20170516', 180, b'1'),
 (b'20170517', 38, b'0'),
 (b'20170517', 194, b'1'),
 (b'20170518', 33, b'0'),
 (b'20170518', 152, b'1'),
 (b'20170519', 38, b'0'),
 (b'20170519', 189, b'1'),
 (b'20170520', 39, b'0'),
 (b'20170520', 203, b'1'),
 (b'20170521', 21, b'0'),
 (b'20170521', 99, b'1'),
 (b'20170522', 27, b'0'),
 (b'20170522', 99, b'1'),
 (b'20170523', 64, b'0'),
 (b'20170523', 196, b'1'),
 (b'20170524', 67, b'0'),
 (b'20170524', 218, b'1'),
 (b'20170525', 4, b'0'),
 (b'20170525', 8, b'1'))
# convert into Pandas dataframe
labels = ['page creation date', 'unreviewed pages', 'autoconfirmed'] # todo: should come from cur instead
df = pd.DataFrame(list(queryresult), columns=labels)
convertfetchalltstodt = lambda x: pd.to_datetime(x.decode())
df['page creation date'] = df['page creation date'].apply(convertfetchalltstodt)
df.set_index('page creation date', inplace = True)
df_pivot = df.pivot(columns='autoconfirmed', values='unreviewed pages')
df_pivot.columns = ['by users who are still new (not autoconfirmed) today', 'by users who are autoconfirmed today']
df_pivot
by users who are still new (not autoconfirmed) today by users who are autoconfirmed today
page creation date
2006-08-06 NaN 1.0
2008-06-09 NaN 1.0
2008-09-26 NaN 1.0
2010-05-07 NaN 1.0
2011-11-29 NaN 1.0
2013-12-09 NaN 1.0
2013-12-10 NaN 1.0
2015-03-05 NaN 1.0
2015-05-06 NaN 1.0
2015-09-10 NaN 1.0
2016-03-28 NaN 1.0
2016-03-30 NaN 1.0
2016-03-31 NaN 1.0
2016-04-01 NaN 1.0
2016-07-06 NaN 1.0
2016-07-28 NaN 1.0
2016-10-13 NaN 1.0
2016-11-21 1.0 NaN
2016-11-30 NaN 1.0
2016-12-15 NaN 1.0
2016-12-17 NaN 18.0
2016-12-18 NaN 44.0
2016-12-19 NaN 84.0
2016-12-20 NaN 64.0
2016-12-21 NaN 81.0
2016-12-22 NaN 81.0
2016-12-23 NaN 68.0
2016-12-24 NaN 57.0
2016-12-25 NaN 70.0
2016-12-26 NaN 86.0
... ... ...
2017-04-26 10.0 187.0
2017-04-27 6.0 149.0
2017-04-28 19.0 173.0
2017-04-29 7.0 240.0
2017-04-30 8.0 242.0
2017-05-01 15.0 220.0
2017-05-02 19.0 196.0
2017-05-03 15.0 207.0
2017-05-04 21.0 203.0
2017-05-05 26.0 199.0
2017-05-06 8.0 132.0
2017-05-07 18.0 192.0
2017-05-08 24.0 159.0
2017-05-09 29.0 221.0
2017-05-10 24.0 179.0
2017-05-11 21.0 126.0
2017-05-12 23.0 149.0
2017-05-13 13.0 136.0
2017-05-14 18.0 171.0
2017-05-15 16.0 204.0
2017-05-16 15.0 180.0
2017-05-17 38.0 194.0
2017-05-18 33.0 152.0
2017-05-19 38.0 189.0
2017-05-20 39.0 203.0
2017-05-21 21.0 99.0
2017-05-22 27.0 99.0
2017-05-23 64.0 196.0
2017-05-24 67.0 218.0
2017-05-25 4.0 8.0

180 rows × 2 columns

# drop some spurious and/or irrelevant data from before December 2016
df_pivot = df_pivot.drop(df_pivot[df_pivot.index < '2016-12-01'].index)
# print the entire dataframe unabridged:
print(df_pivot.to_string())
                    by users who are still new (not autoconfirmed) today  by users who are autoconfirmed today
page creation date                                                                                            
2016-12-15                                                        NaN                                      1.0
2016-12-17                                                        NaN                                     18.0
2016-12-18                                                        NaN                                     44.0
2016-12-19                                                        NaN                                     84.0
2016-12-20                                                        NaN                                     64.0
2016-12-21                                                        NaN                                     81.0
2016-12-22                                                        NaN                                     81.0
2016-12-23                                                        NaN                                     68.0
2016-12-24                                                        NaN                                     57.0
2016-12-25                                                        NaN                                     70.0
2016-12-26                                                        NaN                                     86.0
2016-12-27                                                        NaN                                    115.0
2016-12-28                                                        NaN                                    100.0
2016-12-29                                                        NaN                                    170.0
2016-12-30                                                        NaN                                    120.0
2016-12-31                                                        NaN                                     64.0
2017-01-01                                                        NaN                                     83.0
2017-01-02                                                        NaN                                    106.0
2017-01-03                                                        NaN                                    155.0
2017-01-04                                                        NaN                                    114.0
2017-01-05                                                        NaN                                    124.0
2017-01-06                                                        NaN                                    123.0
2017-01-07                                                        NaN                                     99.0
2017-01-08                                                        NaN                                    140.0
2017-01-09                                                        NaN                                    168.0
2017-01-10                                                        NaN                                     78.0
2017-01-11                                                        NaN                                    115.0
2017-01-12                                                        NaN                                    124.0
2017-01-13                                                        NaN                                    111.0
2017-01-14                                                        NaN                                     77.0
2017-01-15                                                        NaN                                    128.0
2017-01-16                                                        NaN                                    103.0
2017-01-17                                                        NaN                                    136.0
2017-01-18                                                        NaN                                    146.0
2017-01-19                                                        NaN                                     84.0
2017-01-20                                                        NaN                                     33.0
2017-01-21                                                        NaN                                     87.0
2017-01-22                                                        NaN                                     39.0
2017-01-23                                                        NaN                                     40.0
2017-01-24                                                        NaN                                     55.0
2017-01-25                                                        NaN                                     74.0
2017-01-26                                                        NaN                                     39.0
2017-01-27                                                        NaN                                     78.0
2017-01-28                                                        NaN                                    106.0
2017-01-29                                                        NaN                                     20.0
2017-01-30                                                        NaN                                     69.0
2017-01-31                                                        NaN                                    104.0
2017-02-01                                                        NaN                                     82.0
2017-02-02                                                        NaN                                    112.0
2017-02-03                                                        NaN                                    130.0
2017-02-04                                                        NaN                                     97.0
2017-02-05                                                        NaN                                     37.0
2017-02-06                                                        NaN                                     44.0
2017-02-07                                                        NaN                                     77.0
2017-02-08                                                        NaN                                    132.0
2017-02-09                                                        NaN                                    123.0
2017-02-10                                                        NaN                                     85.0
2017-02-11                                                        NaN                                    105.0
2017-02-12                                                        NaN                                    103.0
2017-02-13                                                        NaN                                    117.0
2017-02-14                                                        NaN                                    136.0
2017-02-15                                                        NaN                                     97.0
2017-02-16                                                        NaN                                    134.0
2017-02-17                                                        NaN                                    111.0
2017-02-18                                                        NaN                                     93.0
2017-02-19                                                        NaN                                    118.0
2017-02-20                                                        NaN                                    134.0
2017-02-21                                                        NaN                                     68.0
2017-02-22                                                        NaN                                     67.0
2017-02-23                                                        NaN                                     94.0
2017-02-24                                                        NaN                                    154.0
2017-02-25                                                        NaN                                    131.0
2017-02-26                                                        NaN                                    166.0
2017-02-27                                                        NaN                                    124.0
2017-02-28                                                        NaN                                    119.0
2017-03-01                                                        NaN                                     92.0
2017-03-02                                                        NaN                                    147.0
2017-03-03                                                        NaN                                    156.0
2017-03-04                                                        NaN                                    135.0
2017-03-05                                                        NaN                                    162.0
2017-03-06                                                        NaN                                    172.0
2017-03-07                                                        NaN                                    169.0
2017-03-08                                                        NaN                                    145.0
2017-03-09                                                        NaN                                    143.0
2017-03-10                                                        NaN                                    145.0
2017-03-11                                                        NaN                                    176.0
2017-03-12                                                        NaN                                    167.0
2017-03-13                                                        NaN                                    145.0
2017-03-14                                                        NaN                                    229.0
2017-03-15                                                        NaN                                    160.0
2017-03-16                                                        NaN                                    174.0
2017-03-17                                                        NaN                                    141.0
2017-03-18                                                        NaN                                    188.0
2017-03-19                                                        NaN                                    177.0
2017-03-20                                                        NaN                                    161.0
2017-03-21                                                        NaN                                    140.0
2017-03-22                                                        NaN                                    124.0
2017-03-23                                                        1.0                                    154.0
2017-03-24                                                        4.0                                    184.0
2017-03-25                                                        2.0                                    155.0
2017-03-26                                                        3.0                                    133.0
2017-03-27                                                        5.0                                    191.0
2017-03-28                                                        9.0                                    190.0
2017-03-29                                                       11.0                                    173.0
2017-03-30                                                       17.0                                    167.0
2017-03-31                                                        9.0                                    127.0
2017-04-01                                                        2.0                                     55.0
2017-04-02                                                        7.0                                    138.0
2017-04-03                                                        7.0                                    141.0
2017-04-04                                                       15.0                                    182.0
2017-04-05                                                       14.0                                    193.0
2017-04-06                                                       14.0                                    181.0
2017-04-07                                                       18.0                                    203.0
2017-04-08                                                        9.0                                    144.0
2017-04-09                                                        7.0                                    173.0
2017-04-10                                                       17.0                                    162.0
2017-04-11                                                        8.0                                    149.0
2017-04-12                                                        9.0                                    174.0
2017-04-13                                                        3.0                                    103.0
2017-04-14                                                        NaN                                    155.0
2017-04-15                                                        2.0                                    163.0
2017-04-16                                                        1.0                                    123.0
2017-04-17                                                        NaN                                    150.0
2017-04-18                                                        4.0                                    172.0
2017-04-19                                                        4.0                                    179.0
2017-04-20                                                        2.0                                    155.0
2017-04-21                                                        6.0                                    138.0
2017-04-22                                                        5.0                                    154.0
2017-04-23                                                        3.0                                    118.0
2017-04-24                                                        8.0                                     89.0
2017-04-25                                                       14.0                                    185.0
2017-04-26                                                       10.0                                    187.0
2017-04-27                                                        6.0                                    149.0
2017-04-28                                                       19.0                                    173.0
2017-04-29                                                        7.0                                    240.0
2017-04-30                                                        8.0                                    242.0
2017-05-01                                                       15.0                                    220.0
2017-05-02                                                       19.0                                    196.0
2017-05-03                                                       15.0                                    207.0
2017-05-04                                                       21.0                                    203.0
2017-05-05                                                       26.0                                    199.0
2017-05-06                                                        8.0                                    132.0
2017-05-07                                                       18.0                                    192.0
2017-05-08                                                       24.0                                    159.0
2017-05-09                                                       29.0                                    221.0
2017-05-10                                                       24.0                                    179.0
2017-05-11                                                       21.0                                    126.0
2017-05-12                                                       23.0                                    149.0
2017-05-13                                                       13.0                                    136.0
2017-05-14                                                       18.0                                    171.0
2017-05-15                                                       16.0                                    204.0
2017-05-16                                                       15.0                                    180.0
2017-05-17                                                       38.0                                    194.0
2017-05-18                                                       33.0                                    152.0
2017-05-19                                                       38.0                                    189.0
2017-05-20                                                       39.0                                    203.0
2017-05-21                                                       21.0                                     99.0
2017-05-22                                                       27.0                                     99.0
2017-05-23                                                       64.0                                    196.0
2017-05-24                                                       67.0                                    218.0
2017-05-25                                                        4.0                                      8.0
df_pivot.describe()
by users who are still new (not autoconfirmed) today by users who are autoconfirmed today
count 62.000000 161.000000
mean 14.935484 131.024845
std 13.377092 50.021739
min 1.000000 1.000000
25% 6.000000 97.000000
50% 12.000000 135.000000
75% 19.000000 169.000000
max 67.000000 242.000000
dfpxlabels = df_pivot
dfpxlabels['xlabel'] = np.nan
for i in dfpxlabels.index:
    if i.day == 1:
        dfpxlabels.loc[i, 'xlabel'] = i.strftime('%Y-%m-%d')
    else:
        dfpxlabels.loc[i, 'xlabel'] = ''
meinplot = df_pivot.plot(kind='bar', stacked=True, width=1, figsize=(16,8))
meinplot.set_xticklabels(dfpxlabels.xlabel, rotation=70)
plt.title('English Wikipedia new page review backlog', fontsize=24)
plt.ylabel('Number of pages from this day\nstill unreviewed on May 25, 2017', fontsize=16)
plt.rc('legend', fontsize=16)
plt.rc('xtick', labelsize=16)
plt.rc('axes', labelsize=16)
/srv/paws/lib/python3.4/site-packages/matplotlib/font_manager.py:1297: UserWarning: findfont: Font family ['sans-serif'] not found. Falling back to DejaVu Sans
  (prop.get_family(), self.defaultFamily[fontext]))

scratchpad

df_pivot['by users who are autoconfirmed today'].plot()
plt.title('English Wikipedia new page review backlog - autoconfirmed', fontsize=17)
plt.ylabel('Number of pages still unreviewed on May 25, 2017')
# plt.xticks( df_pivot.loc[ df_pivot.index.day == 1 ].index, df_pivot.loc[ df_pivot.index.day == 1].index.strftime('%Y-%m-%d'))
<matplotlib.text.Text at 0x7fc215527ba8>
/srv/paws/lib/python3.4/site-packages/matplotlib/font_manager.py:1297: UserWarning: findfont: Font family ['sans-serif'] not found. Falling back to DejaVu Sans
  (prop.get_family(), self.defaultFamily[fontext]))
fix, ax = plt.subplots()
ax.stackplot(df_pivot.index, df_pivot['by users who are still new (not autoconfirmed) today'], df_pivot['by users who are autoconfirmed today'])
[<matplotlib.collections.PolyCollection at 0x7fc215b51c88>,
 <matplotlib.collections.PolyCollection at 0x7fc218290320>]
/srv/paws/lib/python3.4/site-packages/matplotlib/font_manager.py:1297: UserWarning: findfont: Font family ['sans-serif'] not found. Falling back to DejaVu Sans
  (prop.get_family(), self.defaultFamily[fontext]))
months = matplotlib.dates.MonthLocator(range(1, 13), bymonthday=1, interval=1)
months
<matplotlib.dates.MonthLocator at 0x7fc214e4dc50>