Review Twitter Handles from Search in a Semi-Automated Fashion¶

Objective: Convert Twitter URLs to handles. Inspect the URLs and check for quality of match. If webpage did not contain a Twitter URL, search for the Twitter page on Google.¶

The previous step returned Twitter URLs from the university's webpages. Mostly through manual inspection I now determine: (1) if there were any errors in the URL collection and (2) whether there are missing URLs. If there was an error or the URL is missing, I search for the twitter link on Google and return the first two results. I manually determine what handles are for the college's main page and which is for the college's admissions page. Note that many colleges also have pages for their sports teams and other centers on campus. These URLs are discarded during the ETL process.

Note: This reconcilliation is a fairly manual process and this code is not a tutorial in the usual sense. Use it to guide your own ETL process.

The final result is a dataset of all the primary and admissions Twitter handles for each college in the Brookings data.

Set Up¶

As before, the main packages we are using are: pandas, BeautifulSoup, and selenium. Pandas is used for data management. Selenium is used to implement the Google searches and get around Google's bot detection. BeautifulSoup and requests fetch data from the webpages.

import pandas as pd
from bs4 import BeautifulSoup
import numpy as np
import os, requests, re, time

from selenium import webdriver
from selenium.webdriver.firefox.options import Options
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support.ui import Select
from selenium.common.exceptions import NoSuchElementException
from selenium.common.exceptions import NoAlertPresentException

File Locations¶

base_path = r"C:\Users\laure\Dropbox\!research\20181026_ihe_diversity"
sm_path = os.path.join(base_path,'data','social_media_links')
sm_py_path = os.path.join(base_path,'python_scraping','scrape_handles')
tw_path = os.path.join(base_path,'data','twitter_handles')

Import cross-notebook funtions¶

social_media_py = str(os.path.join(sm_py_path, 'social_media_urls.py'))
%run $social_media_py

Import social media file scraped previously¶

scraped_urls = pd.read_csv(os.path.join(sm_path, 'ihe_sm_pages_v2.csv'), engine="python", index_col=False, header=0, sep=';', skipinitialspace=True)
# Strip any unncessary whitespace
scraped_urls = scraped_urls.apply(lambda x: x.str.strip() if x.dtype == "object" else x)

scraped_urls.columns

Index(['unitid', 'instnm', 'url_main', 'url_admissions', 'twitter_links',
       'facebook_links', 'instagram_links'],
      dtype='object')

# Two things to correct: errors from Googling and schools 
# that did not list Twitter URLs on homepage
scraped_urls.twitter_links.value_counts().head(2)

['No Twitter found']    37
Error                   22
Name: twitter_links, dtype: int64

Search again for universities with errors¶

Schools to search for again.

re_google = scraped_urls.loc[scraped_urls.twitter_links=='Error']

Redo the search like in the previous step.

# Write csv header
if not os.path.isfile(os.path.join(sm_path, "re_google.csv")):
    with open(os.path.join(sm_path, "re_google.csv"), 'w') as f:
        print('unitid; instnm; url_main; url_admissions; twitter_links; facebook_links; instagram_links', file=f)

# Initialize headless Firefox browser
options = Options()
options.headless = True
driver = webdriver.Firefox(options=options)

for index, row in re_google.iterrows():
    try:
        # Get URL from base webpage
        ihe_url = feeling_lucky(row['instnm'], driver=driver)

        # Get Twitter links
        main_twitter_links, main_facebook_links, main_instagram_links = get_handles(url=ihe_url, driver=driver)

        # Also search for admissions page to see if Twitter is different
        admissions_url = feeling_lucky(row['instnm'] + ' admissions', driver=driver)

        # Get Twitter links for admissions page
        adm_twitter_links, adm_facebook_links, adm_instagram_links = get_handles(url=admissions_url, driver=driver)

        # If no links found, add NA
        # Twitter
        if not main_twitter_links and not adm_twitter_links:
            main_twitter_links = ['No Twitter found']
        if not main_facebook_links and not adm_facebook_links:
            main_facebook_links = ['No Facebook found']
        if not main_instagram_links and not adm_instagram_links:
            main_instagram_links = ['No Instagram found']

        # Remove any duplicate links
        twitter_links = rm_dup(main_twitter_links + adm_twitter_links)
        facebook_links = rm_dup(main_facebook_links + adm_facebook_links)
        instagram_links = rm_dup(main_instagram_links + adm_instagram_links)

        # Pring on page and write to csv file
        print(str(row['unitid']) + ',' + row['instnm'] + ',' + ihe_url + ',' 
                  + admissions_url + ',' + str(twitter_links[0]) + ',' 
                  + str(facebook_links[0]) + ',' + str(instagram_links[0]))
        with open(os.path.join(sm_path, "re_google.csv"), 'a') as f:
            print(str(row['unitid']) + ';' + row['instnm'] + ';' + ihe_url + ';' 
                + admissions_url + ';' + str(twitter_links) + ';' 
                + str(facebook_links) + ';' + str(instagram_links), file=f)
    
    except:
        print("Error: " + str(row['unitid']) + ' - ' + row['instnm'])
        with open(os.path.join(sm_path, "re_google.csv"), 'a') as f:
            print(str(row['unitid']) + ';' + row['instnm'] + ';' + "Error" + ';' + "Error" + ';' + "Error" + ';' + "Error"+ ';' + "Error", file=f)
            
driver.quit()

re_google_df = pd.read_csv(os.path.join(sm_path, 're_google.csv'), engine="python", index_col=False, header=0, sep=';', skipinitialspace=True)
# Strip any unncessary whitespace
re_google_df = re_google_df.apply(lambda x: x.str.strip() if x.dtype == "object" else x)

Schools that were not found in re-google will be fixed manually.

manual_fix = re_google_df.loc[re_google_df.twitter_links=='Error']

# Only three schools
manual_fix

Google Search for Twitter Pages of Colleges without Twitter URLs on Main Website¶

find_twitter = scraped_urls.loc[scraped_urls.twitter_links=="['No Twitter found']"]

# Initialize headless Firefox browser
options = Options()
options.headless = True
driver = webdriver.Firefox(options=options)

for index, row in find_twitter.iterrows():
    try:
        # Search for institution + twitter on Google
        ihe_tw_url = feeling_lucky('twitter ' + row['instnm'], driver=driver)
        ihe_tw_url = ihe_tw_url.split('?')[0].split('#')[0]
        
        # Also search for admissions page to see if Twitter is different
        admissions_tw_url = feeling_lucky('twitter ' + row['instnm'] + ' admissions', driver=driver)
        admissions_tw_url = admissions_tw_url.split('?')[0].split('#')[0]
        
        # Put urls in list
        twitter_list = [ihe_tw_url, admissions_tw_url]
        
        # Remove any duplicate links
        twitter_links = rm_dup(twitter_list)

        # Print out URLs for manual checking
        print(row['instnm'] + ' ' + ihe_tw_url + ' ' + admissions_tw_url)
        
        # Update df
        find_twitter.at[index, 'twitter_links'] = twitter_links
        
    except:
        continue
            
driver.quit()

Combine original csv with new results from updated google search and twitter search¶

# Remove errors and twitter not found entries
updated_urls = scraped_urls.loc[(scraped_urls.twitter_links != 'Error') & (scraped_urls.twitter_links!="['No Twitter found']")]

# Append new data
updated_urls = updated_urls.append(re_google_df)
updated_urls = updated_urls.append(find_twitter)

updated_urls = updated_urls.loc[(updated_urls.twitter_links != 'Error') & (updated_urls.twitter_links!="['No Twitter found']")]

Manually review Twitter links¶

Remove links related to athletics, news, duplicates with different spellings.

# Print links to csv for manual review
for index, row in updated_urls.iterrows():
    show_links = str(row['twitter_links']).strip('[]')
    show_links = show_links.replace("'", '')
    show_links = show_links.replace(',', ';')
    with open(os.path.join(tw_path, "review_twitter.csv"), 'a') as f:
        print(str(row['unitid']) + ';' + row['instnm'] + ';' + show_links, file=f)

Google search for missing admissions pages¶

Unfortunately many schools appear not to put their admissions twitters directly on their admissions page. Do a google search for missing schools - manually examine the top two google results for the admissions page.

def google_search(search_text, result_num, driver):
    driver.get("http://www.google.com/")
    time.sleep(1)
    driver.find_element_by_name("q").click
    driver.find_element_by_name("q").send_keys(search_text)
    time.sleep(0.5)
    driver.find_element_by_name("q").send_keys(Keys.TAB)
    time.sleep(0.5)
    driver.find_element_by_name("q").send_keys(Keys.ENTER)
    time.sleep(1)
    # Result 
    result_text = driver.find_element_by_xpath(r'(.//*[@class="g"][' + str(result_num) + r']//descendant::a)').text
    result_link = driver.find_element_by_xpath(r'(.//*[@class="g"][' + str(result_num) + r']//descendant::a' + r'//descendant::div[1])').text
    return result_text, result_link

if 1==2:
    options = Options()
    options.headless = False
    driver = webdriver.Firefox(options=options)
    result_text, result_link = google_search(search_text="twitter admissions University of Washington", result_num=1, driver=driver)
    print(result_text + " "  + result_link)
    driver.quit()

 driver.quit()

manual_review = pd.read_csv(os.path.join(tw_path, "manual_review.csv"), engine="python", header=0, sep=',')

find_adm_tw = manual_review.loc[manual_review.admissions_link.isnull()].copy()

pd.set_option('display.max_rows', 1000)
#find_adm_tw

# Create new cols in df
for new_col in ['likely_adm_link', 'likely_adm_text', 'google_text1', 'google_link1', 'google_text2', 'google_link2']:
    find_adm_tw[new_col] = ''

# Initialize headless Firefox browser
options = Options()
options.headless = True
driver = webdriver.Firefox(options=options)

for index, row in find_adm_tw.iterrows():
    # Search for twitter admissions + institution on Google

    # First result
    google_text1, google_link1 = google_search(search_text='twitter admissions ' + row['instnm'], result_num=1, driver=driver)

    # Second result
    google_text2, google_link2 = google_search(search_text='twitter admissions ' + row['instnm'], result_num=2, driver=driver)

    # Update df
    find_adm_tw.at[index, 'google_text1'] = google_text1
    find_adm_tw.at[index, 'google_link1'] = google_link1
    find_adm_tw.at[index, 'google_text2'] = google_text2
    find_adm_tw.at[index, 'google_link2'] = google_link2
    
    # If text contains admissions, put in likely link column
    if 'admission' in google_text1.lower():
        find_adm_tw.at[index, 'likely_adm_link'] = google_link1
        find_adm_tw.at[index, 'likely_adm_text'] = google_text1
    if 'admission' in google_text2.lower():
        find_adm_tw.at[index, 'likely_adm_link'] = google_link2
        find_adm_tw.at[index, 'likely_adm_text'] = google_text2
    
    # If no likely admission link, print google search results for review
    if ('admission' not in google_text1.lower()) and ('admission' not in google_text2.lower()):
        print("1: " + str(row['unitid']) + " " + row['instnm'] + " " + google_text1 + " " + google_link1)
        print("2: " + str(row['unitid']) + " " + row['instnm'] + " " + google_text2 + " " + google_link2)

driver.quit()

# Save in case of needing to reload
find_adm_tw.to_pickle(os.path.join(tw_path, "df_google_adm_tw"))

Review likely Admissions Twitter Pages and Make Replacements¶

# find_adm_tw[find_adm_tw.likely_adm_link != '']

find_adm_tw = pd.read_pickle(os.path.join(tw_path, "df_google_adm_tw"))

tw_df = manual_review.copy()

find_adm_tw = find_adm_tw[find_adm_tw.unitid.notnull()]

tw_df.unitid = tw_df.unitid.astype(int)
find_adm_tw.unitid = find_adm_tw.unitid.astype(int)

tw_df.set_index('unitid', inplace=True)

find_adm_tw.set_index('unitid', inplace=True)

find_adm_tw.head(2)

tw_df.head(5)

# False positive admissions pages - Google didn't find the admissions twitter for the correct college
false_positive = [212054, 196413, 144740, 243744, 202480, 117946, 140951, 
    188429, 130226, 181002, 366711, 151306, 166452, 182670, 164739, 151102, 216010,
    160038, 219709, 201104, 195544, 196042, 194541, 176053, 209056, 154688, 130697,
    221971, 209825, 130183, 141097, 190770, 162007, 220613, 164173, 216694, 191676,
    153108, 159993]

for index, row in tw_df.iterrows():
        
    # Replace the admissions link with the likely admissions link, as long as 
    # the school is not in the falso positive list
    if (index not in false_positive) and (index in find_adm_tw.index):
        tw_df.at[index, 'admissions_link'] = find_adm_tw.at[index, 'likely_adm_link']

# Admissions pages without 'admissions' in the twitter page title 
# Manually found from first two google results
in1 = [110583, 122755, 186867]
in2 = [229027, 207388, 204024, 126562, 169716]

for index, row in find_adm_tw.iterrows():
        
    # If index is in the first list, replace with the first google result
    if (index in in1):
        tw_df.at[index, 'admissions_link'] = find_adm_tw.at[index, 'google_link1']
        
    # If index is in the second list, replace with the second google result
    if (index in in1):
        tw_df.at[index, 'admissions_link'] = find_adm_tw.at[index, 'google_link2']

# Found from totally manual google search
tw_found_manually = pd.DataFrame([
    [151379, 'https://twitter.com/iusadmissions'],
    [144962, 'https://twitter.com/ec_admissions'],
    [212054, 'https://twitter.com/DrexelAdmission'],
    [196413, 'https://twitter.com/GoSyracuseU'],
    [144740, 'https://twitter.com/DePaulAdmission'],
    [243744, 'https://twitter.com/engagestanford?lang=en'],
    [202480, 'https://twitter.com/DaytonAdmission'],
    [181002, 'https://twitter.com/ChooseCreighton'],
    [164739, 'https://twitter.com/UGABentley'],
    [176053, 'https://twitter.com/mc_admissions'],
    ], columns = ['unitid', 'tw_adm_link'])

all_found_manually = pd.DataFrame([
    [104717,'https://twitter.com/gcu', ''],
    [232265,'https://twitter.com/_hamptonu','https://twitter.com/applyhamptonu'],
    [100654, 'https://twitter.com/aamuedu', ''],
    [221838, 'https://twitter.com/TSUedu', 'https://twitter.com/tsuadmissions']
    ], columns = ['unitid', 'tw_main_link', 'tw_adm_link'])

tw_found_manually.set_index('unitid', inplace=True)
all_found_manually.set_index('unitid', inplace=True)

for index, row in tw_df.iterrows():
        
    # If index is in the manual twitter df, replace with the link from there
    if (index in tw_found_manually.index):
        tw_df.at[index, 'admissions_link'] = tw_found_manually.at[index, 'tw_adm_link']
        
    # If index is in the all manual df, replace with the links from there
    if (index in all_found_manually.index):
        tw_df.at[index, 'main_link'] = all_found_manually.at[index, 'tw_main_link']
        tw_df.at[index, 'admissions_link'] = all_found_manually.at[index, 'tw_adm_link']

Extract Handles from URLS¶

ex1 = "https://twitter.com/somehandle?somequery"
ex2 = "http://twitter.com/somehandle?somequery"
ex3 = ""

def extract_handle(url):
    no_query = str(url).split('?')[0]
    try:
        handle = re.search(r'.*twitter.com/([^/?]+)', no_query).group(1)
    except:
        handle = ''
    return handle

if 1 == 1:
    print(extract_handle(ex1))
    print(extract_handle(ex2))
    print(extract_handle(ex3))

somehandle
somehandle

# Extract main and admissions handles
tw_df['main_handle'] = tw_df.main_link.apply(extract_handle)
tw_df['adm_handle'] = tw_df.admissions_link.apply(extract_handle)

tw_df[['instnm','main_handle','adm_handle']]

tw_df[['instnm','main_handle','adm_handle']].to_pickle(os.path.join(tw_path, "tw_df_final"))

	instnm	main_link	admissions_link	likely_adm_link	likely_adm_text	google_text1	google_link1	google_text2	google_link2
unitid
154022	Ashford University	https://twitter.com/AshfordU	NaN			Ashford University (@AshfordU) \| Twitter\nhttp...	https://twitter.com/ashfordu?lang=en	BridgepointEducation (@Bridgepoint_Ed) \| Twitt...	https://twitter.com/bridgepoint_ed?lang=en
236948	University of Washington-Seattle Campus	https://twitter.com/UW	NaN	https://twitter.com/seattleuadm?lang=en	Seattle U Admissions (@SeattleUAdm) \| Twitter\...	U. of Washington (@UW) \| Twitter\nhttps://twit...	https://twitter.com/uw?lang=en	Seattle U Admissions (@SeattleUAdm) \| Twitter\...	https://twitter.com/seattleuadm?lang=en

	instnm	main_link	admissions_link
unitid
154022	Ashford University	https://twitter.com/AshfordU	NaN
132903	University of Central Florida	https://www.twitter.com/ucf	https://twitter.com/ucfadmissions
134130	University of Florida	https://twitter.com/UF/	https://twitter.com/UFAdmissions
193900	New York University	https://twitter.com/nyuniversity	https://twitter.com/meetnyu
232557	Liberty University	https://twitter.com/libertyu	https://twitter.com/ExperienceLU

	unitid	instnm	url_main	url_admissions	twitter_links	facebook_links	instagram_links
0	104717	Grand Canyon University	Error	Error	Error	Error	Error
6	232265	Hampton University	Error	Error	Error	Error	Error
7	100654	Alabama A & M University	Error	Error	Error	Error	Error

	instnm	main_handle	adm_handle
unitid
154022	Ashford University	AshfordU
132903	University of Central Florida	ucf	ucfadmissions
134130	University of Florida	UF	UFAdmissions
193900	New York University	nyuniversity	meetnyu
232557	Liberty University	libertyu	ExperienceLU
228778	The University of Texas at Austin	UTAustin	bealonghorn
204796	Ohio State University-Main Campus	OhioState	beavervip
170976	University of Michigan-Ann Arbor	umich	UMichAdmissions
174066	University of Minnesota-Twin Cities	UMNews	uofmadmissions
104151	Arizona State University-Tempe	asu	FutureSunDevils
123961	University of Southern California	usc	USCAdmission
236948	University of Washington-Seattle Campus	UW	seattleuadm
228723	Texas A & M University-College Station	tamu	aggiebound
214777	Pennsylvania State University-Main Campus	penn_state	PSU_Admissions
145637	University of Illinois at Urbana-Champaign	Illinois_Alma	hashtag
134097	Florida State University	floridastate	fsuadmissions
110635	University of California-Berkeley	UCBerkeley	CalAdmissions
240444	University of Wisconsin-Madison	uwmadison	UWAdmissions
133951	Florida International University	fiu
110662	University of California-Los Angeles	ucla	uclaadmission
190150	Columbia University in the City of New York	columbia	hamiltonhall
151351	Indiana University-Bloomington	IUBloomington	IUAdmissions
163286	University of Maryland-College Park	UofMaryland	ApplyMaryland
171100	Michigan State University	michiganstateu	msu_admissions
137351	University of South Florida-Main Campus	USouthFlorida	AdmissionsUSF
243780	Purdue University-Main Campus	lifeatpurdue	PurdueAdmission
110644	University of California-Davis	ucdavis	ucd_admissions
139959	University of Georgia	universityofga	ugaadmissions
163204	University of Maryland-University College	umuc	applymaryland
215293	University of Pittsburgh-Pittsburgh Campus	PittTweet	pittadmissions
104179	University of Arizona	uofa	uaadmissions
186380	Rutgers University-New Brunswick	rutgersnb	apply2rutgers
228769	The University of Texas at Arlington	utarlington	utarlington
164988	Boston University	BU_Tweets	applytobu
131469	George Washington University	Gwtweets	gwadmissions
199120	University of North Carolina at Chapel Hill	unc	UNCAdmissions
110583	California State University-Long Beach	csulb	csulboutreach
215062	University of Pennsylvania	Penn	previewingpenn
110608	California State University-Northridge	csunorthridge