<jupyter_start><jupyter_text>Notebook for selecting all comments ever in a particular set of subreddits<jupyter_code>from pyspark.sql import SQLContext
import pandas as pd
from pyspark.sql.functions import *
import pyspark.sql
sqlC = SQLContext(sc)
reddit = sqlC.read.parquet("all_comments.parquet")
reddit.printSchema()<jupyter_output>root
 |-- approved_at_utc: string (nullable = true)
 |-- approved_by: string (nullable = true)
 |-- archived: boolean (nullable = true)
 |-- author: string (nullable = true)
 |-- author_cakeday: boolean (nullable = true)
 |-- author_flair_css_class: string (nullable = true)
 |-- author_flair_text: string (nullable = true)
 |-- banned_at_utc: string (nullable = true)
 |-- banned_by: string (nullable = true)
 |-- body: string (nullable = true)
 |-- body_html: string (nullable = true)
 |-- can_gild: boolean (nullable = true)
 |-- can_mod_post: boolean (nullable = true)
 |-- collapsed: boolean (nullable = true)
 |-- collapsed_reason: string (nullable = true)
 |-- controversiality: long (nullable = true)
 |-- created: long (nullable = true)
 |-- created_utc: string (nullable = true)
 |-- distinguished: string (nullable = true)
 |-- downs: long (nullable = true)
 |-- edited: string (nullable = true)
 |-- gilded: long (nullable = true)
 |-- id: string (nullable = true)
 |-- is_submitter: bool[...]<jupyter_text>Select just the columns we want to work with- specifically, exclude columns that have sub-columns to them because they're a pain to work with if we don't need their data.<jupyter_code>reddit = reddit[['approved_at_utc',  'approved_by', 'archived', 'author', 
                 'author_cakeday', 'author_flair_css_class', 'author_flair_text',
                 'banned_at_utc', 'banned_by',  'body',  'can_gild',
                 'can_mod_post', 'collapsed',  'collapsed_reason', 'controversiality',
                 'created', 'created_utc', 'distinguished', 'downs',  'edited',
                 'gilded',  'id', 'is_submitter', 'likes', 'link_id', 'name', 
                 'num_reports', 'parent_id', 'removal_reason', 'replies', 
                 'report_reasons', 'retrieved_on', 'saved', 'score', 'score_hidden',
                 'stickied', 'subreddit', 'subreddit_id', 'ups']]<jupyter_output><empty_output><jupyter_text>Clean up text- Reddit lets users type almost anything. This can be a major pain down the road. To help, this code replaces all of the newlines, carriage returns, and tabs with spaces.     - yes, some information is lost this way.<jupyter_code>reddit = reddit.withColumn('body', regexp_replace('body', '\n', ' '))
reddit = reddit.withColumn('body', regexp_replace('body', '\r', ' '))
reddit = reddit.withColumn('body', regexp_replace('body', '\t', ' '))<jupyter_output><empty_output><jupyter_text>get a count of how many posts are in each subreddit- this can help us decide which ones to download<jupyter_code>tmp = reddit.groupby('subreddit').count()
tmp.show(10)<jupyter_output>+-------------------+--------+
|          subreddit|   count|
+-------------------+--------+
|              anime|11324720|
|         MensRights| 3527064|
|         MLBTheShow|  762202|
|             travel| 1647254|
|UnresolvedMysteries|  655796|
|           arumba07|   23084|
|           lacrosse|  123522|
|         QuotesPorn|  339011|
|         ArtHistory|   25659|
|            ukraina|  673181|
+-------------------+--------+
only showing top 10 rows<jupyter_text>List subreddits of interest, see their post count- case sensitive!<jupyter_code>li = ['politics', 
      'Republican', 'republicans', #'askaconservative', 'CollegeRepublicans', 'askarepublican',
      'democrats', #'AskDemocrats', 'Democrat', 'CollegeDemocrats',
      'GreenParty',
      'Libertarian', #'AskLibertarians',
      'Liberal', #'AskALiberal', 
      'progressive',
      'socialism', #'AskASocialist',
      'demsocialist',
      'communism', 'FULLCOMMUNISM', #'communists', 'Communist', 
      #'votethirdparty',
      'NeutralPolitics'
     ]

tmp.filter(tmp.subreddit.isin(li)).sort('count', ascending=False).show(50)
len(li)<jupyter_output><empty_output><jupyter_text>Select and save the data from each subrddit separately- First, filter (select where) the data so that we only have the subreddits we care about. Tell pyspark to cache that result for future reuse. This makes the rest of the searches after it faster.    - pyspark actually uses lazy evaluation, so this doesn't run until the first call to `short_reddit.filter()`. That means the first loop is slow, but the loops after it go faster. - Save everything to a tsv file, one per subreddit    - note the headers are off by default    - note this actually saves to a folder full of tsv files (one for each partition). That's a spark thing good for spark but bad for other programs. We'll have to merge them outside this script. (There are ways to merge them here, but if the file is bigger than the JVM memory it throws errors. Easier to fix outside where that doesn't happen.)- write settings    - `mode('overwrite')` replaces the files if they exist already. Saves us from "file already exists" errors.    - `header=True` is needed because by default CSVs don't have headers here.    - `escapeQuotes=False` seems like a strange option, but with it off, the files get read in by pandas okay. With it on pandas gives parsing errors.<jupyter_code>print("making short...")
short_reddit = reddit.filter(reddit.subreddit.isin(li))
short_reddit.cache()

for l in li:
    print 'making', l
    result = short_reddit.filter(short_reddit.subreddit == l)
    result.write.mode('overwrite').csv(l+'.tsv.parts', sep='\t', 
                                       header=True, escapeQuotes=False)
print('done!')<jupyter_output>making short...
making politics
making Republican
making republicans
making democrats
making GreenParty
making Libertarian
making Liberal
making progressive
making socialism
making demsocialist
making communism
making FULLCOMMUNISM
making NeutralPolitics
done!<jupyter_text>Same as above, but with a different list of subreddits<jupyter_code>li = ['cats', #'CatsonGlass', 'CatLoaf', 'Kittens', 'Meow_irl',
      'CatGifs', 'StartledCats', #'StuffOnCats', 
      'CatsStandingUp',
      'dogs', 'dogpictures', #'dogswithjobs', 
      'Dogtraining', #'DOG', 
      'puppies'#, 'dogswearinghats'
     ]

tmp.filter(tmp.subreddit.isin(li)).sort('count', ascending=False).show(20)
print("making short...")
short_reddit = reddit.filter(reddit.subreddit.isin(li))
short_reddit.cache()

for l in li:
    print 'making', l
    result = short_reddit.filter(short_reddit.subreddit == l)
    result.write.mode('overwrite').csv(l+'.tsv.parts', sep='\t', 
                                       header=True, escapeQuotes=False)
    #df = result.toPandas()
    #df.to_csv(l+'.tsv', sep='\t', index=False)
print('done!')
len(li)
result = reddit.filter(reddit.subreddit == 'CatGifs')
result.write.mode('overwrite').csv('CatGifs.tsv', sep='\t', header=True, escapeQuotes=False)
print 'done!'<jupyter_output>done!
{'fraction_non_alphanumeric': 0.1396608237138378, 'fraction_numerical': 0.010260795211628902, 'mean_word_length': 3.684913217623498, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '300673', 'license': 'MIT', 'n_tokens_mistral': 2282, 'n_tokens_neox': 2218, 'n_words': 639, 'path': 'select_subreddits.ipynb', 'repo_name': 'RyC37/um_reddit', 'size': '7017'}