<jupyter_start><jupyter_text>Mongo connection:<jupyter_code>import pymongo
import pandas as pd
import pprint
import numpy as np
import csv, sys
from pymongo import MongoClient
client = MongoClient()
client = MongoClient('mongodb://heroku_zt5q0wf3:<EMAIL>:27883/heroku_zt5q0wf3')
db = client['heroku_zt5q0wf3']
records = db.records
records = pd.DataFrame(list(records.find()))
domains = db.domains
domains = pd.DataFrame(list(domains.find()))

records.to_csv('/Users/erikrood/desktop/records_12_14_17.csv')<jupyter_output><empty_output><jupyter_text>Main working push (as of 8/24/17)<jupyter_code>## 8/21/17 Working URL Scrape (proof of concept)

import pandas as pd
import numpy as np
import praw
import operator
import pandas as pd
import pymongo
from pymongo import MongoClient
import time

#set up DB
#local
#client = MongoClient('mongodb://localhost:27017/test')
#db = client.test

#set up praw
reddit = praw.Reddit(client_id='D7tXhzmCZKD1Ig',
                     client_secret='<KEY>',
                     user_agent='Script for top domains posted on reddit')

#create list of subreddits to include
#create list of subreddits to include
s_list = \
[
'funny',
'todayilearned',
'science',
'worldnews',
'pics',
'IAmA',
'gaming',
'videos',
'movies',
'blog',
'Music',
'aww',
'gifs',
'news',
'askscience',
'EarthPorn',
'books',
'television',
'LifeProTips',
'mildlyinteresting',
'space',
'Showerthoughts',
'DIY',
'Jokes',
'sports',
'gadgets',
'tifu',
'InternetIsBeautiful',
'nottheonion',
'photoshopbattles',
'food',
'history',
'Futurology',
'Documentaries',
'dataisbeautiful',
'personalfinance',
'listentothis',
'UpliftingNews',
'GetMotivated',
'OldSchoolCool',
'philosophy',
'Art',
'nosleep',
'creepy',
'WritingPrompts',
'TwoXChromosomes',
'Fitness',
'technology',
'WTF',
'bestof',
'AdviceAnimals',
'politics',
'atheism',
'europe',
'interestingasfuck',
'woahdude',
'leagueoflegends',
'pcmasterrace',
'BlackPeopleTwitter',
'gameofthrones',
'trees',
'reactiongifs',
'Unexpected',
'Overwatch',
'Android',
'oddlysatisfying',
'Games',
'programming',
'wholesomememes',
'nba',
'cringepics',
'facepalm',
'me_irl',
'reddit.com',
'sex',
'relationships',
'Frugal',
'pokemon',
'soccer',
'lifehacks',
'pokemongo',
'ImGoingToHellForThis',
'fffffffuuuuuuuuuuuu',
'tattoos',
'comics',
'malefashionadvice',
'OutOfTheLoop',
'StarWars',
'CrappyDesign',
'YouShouldKnow',
'AskHistorians',
'nfl',
'HistoryPorn',
'buildapc',
'RoastMe',
'FoodPorn',
'loseit',
'AnimalsBeingJerks',
'cringe',
'PS4',
'dankmemes',
'rickandmorty',
'Whatcouldgowrong',
'baseball',
'travel',
'hiphopheads',
'FiftyFifty',
'RoomPorn',
'NatureIsFuckingLit',
'wheredidthesodago',
'Eyebleach',
'anime',
'GlobalOffensive',
'hearthstone',
'Cooking',
'freebies',
'mildlyinfuriating',
'xboxone',
'HighQualityGifs',
'youtubehaiku',
'JusticePorn',
'GifRecipes',
'bodyweightfitness',
'The_Donald',
'conspiracy',
'Tinder',
'Minecraft',
'cats',
'AnimalsBeingBros',
'GameDeals',
'talesfromtechsupport',
'comicbooks',
'EatCheapAndHealthy',
'holdmybeer',
'nonononoyes',
'hockey',
'trashy',
'scifi',
'Steam',
'apple',
'skyrim',
'instant_regret',
'quityourbullshit',
'shittyaskscience',
'thatHappened',
'wallpapers',
'itookapicture',
'iamverysmart',
'wow',
'changemyview',
'AbandonedPorn',
'FanTheories',
'woodworking',
'trippinthroughtime',
'NetflixBestOf',
'investing',
'oldpeoplefacebook',
'AskMen',
'MakeupAddiction',
'Economics',
'pcgaming']
domains_sub = {}
domains = {}
domains_score = {}
domains_url = {}

for i in s_list:

    #pull in score
    subreddit = reddit.subreddit(i)   #input('enter subreddit name: /r/'))
    submissions = subreddit.top('month', limit=50)


    for s in submissions:
        if s.id in domains_score.keys():
            #(1) works for generating total score:
            domains_score[s.id] += s.score
        else:
            #(2)
            #works for generating total score:
            domains_score[s.id] = s.score

    df_score = pd.DataFrame.from_dict(domains_score, orient='index').reset_index()
    df_score.columns = ['id','score']
    
    #pull in domain
    subreddit = reddit.subreddit(i)   #input('enter subreddit name: /r/'))
    submissions = subreddit.top('month', limit=50)


    for s in submissions:
        if s.id in domains.keys():
            #(1) works for generating total score:
            domains[s.id] = s.domain
        else:
            #(2)
            #works for generating total score:
            domains[s.id] = s.domain

    df_domain = pd.DataFrame.from_dict(domains, orient='index').reset_index()
    df_domain.columns = ['id','domain']
    
    #pull in url  - does not work because I'm aggregating up for the rest of the above
    subreddit = reddit.subreddit(i)   #input('enter subreddit name: /r/'))
    submissions = subreddit.top('month', limit=50)


    for s in submissions:
        if s.id in domains_url.keys():
            #(1) works for generating total score:
            domains_url[s.id] = s.url
        else:
            #(2)
            #works for generating total score:
            domains_url[s.id] = s.url

    df_url = pd.DataFrame.from_dict(domains_url, orient='index').reset_index()
    df_url.columns = ['id','url']

    #pull in subreddit
    subreddit = reddit.subreddit(i)
    submissions = subreddit.top('month', limit=50)


    for s in submissions:
        if s.id in domains_sub.keys():
            #(1) works for generating total score:
            domains_sub[s.id] = s.subreddit.display_name
        else:
            #(2)
            #works for generating total score:
            domains_sub[s.id] = s.subreddit.display_name


df_subreddit = pd.DataFrame.from_dict(domains_sub, orient='index').reset_index()
df_subreddit.columns = ['id','subreddit']


#post processing
df_sub_score = df_subreddit.merge(df_score, how='left', on="id")
df_final = df_sub_score.merge(df_domain, how='left', on='id')
#df3 = df_submissions_score.merge(df_subreddit, how='left', on='domain')
#df3 = df3.sort_values(by='submissions', ascending=0)
#df3['avg_score'] = (df3.score/df3.submissions)
# Create a list to store current date
date = []
# For each row in the column,
for row in df_final['id']:
    # Append a letter grade
    date.append(time.strftime("%m/%d/%Y"))
# Create a column from the list
df_final['update_date'] = date

df_final['self_check'] = df_final['domain'].str[:4]
df_final = df_final[df_final.self_check != 'self']
df_final['url'] = ['www.reddit.com/']+df_final['id'].astype(str) 

df_final.head()
import fnmatch
# wildcard search notes: https://stackoverflow.com/questions/11427138/python-wildcard-search-in-string
# Classify domains as image platform, video platform, etc
classify = []

# For each row in the column,
for row in df_final['domain']:
    # image/video
    if row in ['i.redd.it','m.imgur.com','i.imgur.com','imgur.com',\
               'gfycat.com','media.giphy.com','instagram.com','gfycat.com',\
               'c1.staticflickr.com','68.media.tumblr.com','media.tumblr.com',\
               'flickr.com','zippy.gfycat.com', 'v.redd.it','i.imgflip.com']:
        classify.append('image/video')
    # image/video
    elif row in ['youtube.com','youtu.be','streamable.com','m.youtube.com',\
                 'clips.twitch.tv','netflix.com']:
        # Append a letter grade
        classify.append('image/video')
    #social    
    elif row in ['twitter.com','facebook.com']:
        # Append a letter grade
        classify.append('social')
    else:
        # Append a failing grade
        classify.append('other')

# Create a column from the list
df_final['classify'] = classify
df_final.head()
df_final['subreddit'] = df_final['subreddit'].str.lower()
df_pivot_avg = df_final.groupby(['domain', 'subreddit','classify']).mean().reset_index()
df_pivot_avg.columns = [['domain','subreddit','classify', 'avg_score']]
df_pivot_avg.avg_score = df_pivot_avg.avg_score.astype(int)
df_pivot_avg.tail(5)
df_pivot_count = df_final[['domain','subreddit','classify', 'score']]
df_pivot_count = df_pivot_count.groupby(['domain', 'subreddit','classify']).count().reset_index()
df_pivot_count.columns = [['domain','subreddit','classify', 'submissions']]
df_pivot_count.head(5)
df_pivot_final = pd.merge(df_pivot_count, df_pivot_avg, how='left', on=['domain','subreddit', 'classify'])
df_pivot_final['total_upvotes'] = (df_pivot_final.submissions*df_pivot_final.avg_score)
date = []
# For each row in the column,
for row in df_pivot_final['domain']:
    # Append a letter grade
    date.append(time.strftime("%m/%d/%Y"))
# Create a column from the list
df_pivot_final['update_date'] = date
df_pivot_final.head()<jupyter_output><empty_output><jupyter_text>taking subreddit out for now, if I want to pull in, just skip straight to pushing to DB, adding subreddit field in there<jupyter_code>df_pivot_final_nosub = df_pivot_final[['domain','classify','submissions','total_upvotes']]
df_pivot_final_nosub = df_pivot_final_nosub.groupby(['domain','classify']).sum().reset_index()

# Create a list to store current date
date = []
# For each row in the column,
for row in df_pivot_final_nosub['domain']:
    # Append a letter grade
    date.append(time.strftime("%m/%d/%Y"))
# Create a column from the list
df_pivot_final_nosub['update_date'] = date
df_pivot_final_nosub['avg_score'] = (df_pivot_final_nosub.total_upvotes/df_pivot_final_nosub.submissions)
df_pivot_final_nosub.avg_score = df_pivot_final_nosub.avg_score.astype(int)
df_pivot_final_nosub.head(10)
#clear old domains from collection before dropping new ones in
result = db.domains.delete_many({})
#push to collection
for index, row in df_pivot_final_nosub.iterrows():
    dic = {'domain': row['domain'],
       'submissions': row['submissions'],
       'avg_score': row['avg_score'],
       'total_upvotes': row['total_upvotes'],
       'update_date': row['update_date'],
       'classify': row['classify']
      }
    result = db.domains.insert_one(dic)
#df_pivot_final_nosub.to_csv('/Users/erikrood/desktop/df_pivot_final_nosub.csv')<jupyter_output><empty_output>