<jupyter_start><jupyter_text>histogram rather than groupsSELECT    subreddit,    created_year as year,    INTEGER(ROUND(number, -1)) AS bin,    count(*) as count,     sum(nauthor) as nauthorFROM (  SELECT    author,    subreddit,    year(sec_to_timestamp(created_utc)) as created_year,    count(*) as nauthor,    integer(REGEXP_EXTRACT(author, r'(\d+)')) as number  FROM [fh-bigquery:reddit_comments.2007]  WHERE    (REGEXP_MATCH(author, r'\d{4}') )  GROUP BY    author, subreddit, number,created_year  ORDER BY    nauthor DESC)WHERE  (number > 1900) and (number < 2020)GROUP BY    bin, subreddit, yearorder by    year desc, subreddit desc, bin asc do it allTABLE_QUERY([fh-bigquery:reddit_comments], "table_id CONTAINS '20' AND LENGTH(table_id)< 5" )<jupyter_code># df = pd.read_csv('/Users/ajmendez/data/reddit/subreddit_numbers.csv')
df = pd.read_csv('/Users/ajmendez/data/reddit/subreddit_numbers_v1.csv')<jupyter_output><empty_output><jupyter_text>Feature Building* ntotal / nunique all for simple filtering to "good" data where there is enough data points to make some assessment of the ages<jupyter_code># Update the unique, and totals for simple filtering.
df['ntotal'] = df['nunique'] = 0

for (subreddit, year), d in df.groupby(['subreddit', 'year']):
    isgood = (df['subreddit'] == subreddit) & (df['year'] == year)
    df.loc[isgood, ['ntotal', 'nunique']]  = np.sum(d['count']), len(d)
df['nyear'] = 0
for subreddit, d in df.groupby('subreddit'):
    isgood = (df['subreddit'] == subreddit)
    df.loc[isgood, 'nyear'] = len(np.unique(d['year']))
#     y = d[(d['nunique'] > 3) & (d['ntotal'] > 10)].groupby('year')
#     df.loc[isgood, ['nyear', 'ngoodyear']] = [len(np.unique(d['year'])), len(y)]
df['ngoodyear'] = 0
isgood = (df['ntotal'] > 10) & (df['nunique'] > 5)
for subreddit,d in df[isgood].groupby('subreddit'):
    df.loc[isgood & (df['subreddit'] == subreddit), 'ngoodyear'] = len(np.unique(d['year']))
df.to_csv('/Users/ajmendez/data/reddit/subreddit_numbers_v1.csv')
yearnorm = matplotlib.colors.Normalize(2007,2016)
bins = np.arange(1900, 2031, 10)

V,L = np.histogram(df['bin'], bins=bins, weights=df['count'])
setup(figsize=(8,6))
for k,(year, d) in enumerate(numbers.groupby('year'),1):
    ax = setup(subplt=(2,1,1), xlabel='Year', ylabel='Fraction', xticks=False)
    if k == 1:
        pylab.plot(L[:-1], V*1.0/np.sum(V), lw=2, color='k')

    v,l = np.histogram(d['bin'], bins=bins, weights=d['count'])
    pylab.plot(l[:-1], v*1.0/np.sum(v),  '-s', alpha=0.7,
                   color=pylab.cm.Spectral(yearnorm(year)), label=year)
    ax = setup(subplt=(2,1,2), xlabel='Year', ylabel='Relative Fraction')
    if k == 1:
        pylab.axhline(0, color='k', lw=2)
    pylab.plot(l[:-1], v*1.0/np.sum(v) / (V*1.0 / np.sum(V)) - 1,  '-s', alpha=0.7, 
                   color=pylab.cm.Spectral(yearnorm(year)), label=year)
    
legend(loc=2)
pylab.tight_layout()
## yearnorm = matplotlib.colors.Normalize(2007,2016)
bins = np.arange(1900, 2006, 10)

V,L = np.histogram(df['bin'], bins=bins, weights=df['count'])
setup(figsize=(12,12))
for k,(year, d) in enumerate(df.groupby('year'),1):
    print year
    ax = setup(subplt=(2,1,1), xlabel='Age', ylabel='Fraction', xticks=False)
    if k == 1:
        pylab.plot(2014-L[:-1], V*1.0/np.sum(V), lw=2, color='k')

    v,l = np.histogram(d['bin'], bins=bins, weights=d['count'])
    pylab.plot(year-l[:-1], v*1.0/np.sum(v),  '-s', alpha=0.7,
                   color=pylab.cm.Spectral(yearnorm(year)), label=year)
    ax = setup(subplt=(2,1,2), xlabel='Age', ylabel='Relative Fraction')
    if k == 1:
        pylab.axhline(0, color='k', lw=2)
    pylab.plot(year-l[:-1], v*1.0/np.sum(v) / (V*1.0 / np.sum(V)) - 1,  '-s', alpha=0.7, 
                   color=pylab.cm.Spectral(yearnorm(year)), label=year)
    
legend(loc=2)
pylab.tight_layout()
# numbers = df[(df['nyear'] > 7)] # too much noise
numbers = df[ (df['ntotal'] > 100) & (df['nunique'] > 10) & (df['ngoodyear'] > 7)]


tmp = np.unique(numbers['subreddit'])
print tmp, len(tmp)
numbers
yearnorm = matplotlib.colors.Normalize(2007,2016)
setup(figsize=(18,6))
for k,(subreddit, d) in enumerate(numbers.groupby('subreddit'),1):
    ax = setup(subplt=(1,3,k), title=subreddit, xlabel='Year', ylabel='Fraction')
#     pylab.axvspan(1990, 2020, color='0.5', alpha=0.3, zorder=-2),
    for i, (year, e) in enumerate(d.groupby('year')):
        pylab.plot(e['year'] - e['bin'], (e['count'])/np.sum(e['count']),  '-s',
                   color=pylab.cm.Blues_r(yearnorm(year)), label=year)
    legend(loc=2)
#     break
pylab.tight_layout()
# numbers = df[(df['nyear'] > 7)] # too much noise
numbers = df[ (df['ntotal'] > 100) & (df['nunique'] > 10) & (df['ngoodyear'] > 6)]


tmp = np.unique(numbers['subreddit'])
print tmp, len(tmp)
numbers
yearnorm = matplotlib.colors.Normalize(2007,2014)
setup(figsize=(18,18))
for k,(subreddit, d) in enumerate(numbers.groupby('subreddit'),1):
    ax = setup(subplt=(5,5,k), title=subreddit)
    pylab.axvspan(-20, 20, color='0.5', alpha=0.3, zorder=-2),
    for i, (year, e) in enumerate(d.groupby('year')):
        pylab.plot(e['year'] - e['bin'], (e['count'])/np.sum(e['count']),  '-s',
                   color=pylab.cm.jet(yearnorm(year)), label=year)
    legend(loc=2)
#     break
pylab.tight_layout()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.20881189983054038, 'fraction_numerical': 0.0374694031255884, 'mean_word_length': 4.312, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 22, 'https://': 0, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '428512', 'license': 'MIT', 'n_tokens_mistral': 2247, 'n_tokens_neox': 2112, 'n_words': 392, 'path': 'reddit/subreddit_ages.ipynb', 'repo_name': 'ajmendez/explore', 'size': '5311'}