<jupyter_start><jupyter_text>Get ULRs tweeted by US politicians<jupyter_code>import pandas as pd
import numpy as np
from os.path import join
from os import listdir

import sys
sys.path.append('../../../../utilities/twitter_functions')
from twitter_functions import extract_domain<jupyter_output><empty_output><jupyter_text>Extract shortened URLs<jupyter_code>src = "../../data/twitter"
fname = "PNAS_combined_US_politician_twitter_timelines_2010-11-06_to_2022-03-16_clean.csv.gzip"

df = pd.read_csv(join(src, fname),
                 compression="gzip",
                 usecols=["id", "author_id", "created_at", "expanded_urls"])
df["expanded_urls"] = df["expanded_urls"].fillna("[]")
df["expanded_urls"] = df["expanded_urls"].apply(lambda x: eval(x))
df["has_url"] = df["expanded_urls"].apply(lambda x: len(x) > 0)
df = df[df["has_url"]].drop(columns=["has_url"])
URLs = []
for url_list in df["expanded_urls"]:
    URLs.extend(url_list)
URLs = pd.DataFrame({"url":list(set(URLs))})
URLs["domain"] = URLs["url"].apply(extract_domain)
# shorteners from this repo: https://github.com/boutetnico/url-shorteners
# NOTE: update list periodically
shortener_dst = "../../../../utilities/url_shorteners/url-shorteners"
url_shorteners = list(np.loadtxt(join(shortener_dst, "list.txt"), dtype=str))

# add URL shorteners based on manual inspections of all URLs that appeared >100
# times in the dataset
url_shorteners.extend([
    "fb.me", "buff.ly", "nyti.ms", "wapo.st", "youtu.be", "1.usa.gov", "fxn.ws",
    "on.fb.me", "politi.co", "trib.al", "washex.am", "hill.cm", "cnb.cx",
    "hubs.ly", "cs.pn","n.pr", "conta.cc", "mi.tt", "usat.ly", "abcn.ws",
    "reut.rs", "cbsn.ws", "huff.to", "instagr.am", "bloom.bg", "fw.to", 
    "ift.tt", "strib.mn", "lat.ms", "afs.mn", "dpo.st", "mailchi.mp",
    "dailysign.al", "tmblr.co", "rub.io", "yhoo.it", "omny.fm", "chrl.ie",
    "tulsi.to", "apne.ws", "hrc.io", "ed.gr", "ti.me", "herit.ag", "indy.st",
    "ofa.bo", "trib.in", "azc.cc", "bsun.md", "wjcf.co", "bityl.co", 
    "go.shr.lc", "ow.ly"
])
shortened_urls = URLs[URLs["domain"].isin(url_shorteners)]
unshortened_urls = URLs[~URLs["domain"].isin(url_shorteners)]
# load existing unraveled URLs
src = '../../data/twitter/'
unraveled_urls = pd.read_csv(join(src, "unraveled_urls.csv.xz"), 
                             compression="xz")
new_shortened_urls = shortened_urls[~shortened_urls["url"].isin(unraveled_urls["url"])]
fname = "US_politician_URLs_4.csv.gzip"
dst = "../../data/twitter"
new_shortened_urls["url"].to_csv(join(dst, fname), compression="gzip", index=False)
! rsync -avze ssh ../../data/twitter/US_politician_URLs_4.csv.gzip jlasser@medea:/home/jlasser/Honesty-project/data/twitter<jupyter_output>sending incremental file list
url_list4.csv.gzip

sent 15,281 bytes  received 35 bytes  30,632.00 bytes/sec
total size is 15,164  speedup is 0.99<jupyter_text>Load unraveled URLs Unraveling is done with the `unravel_url` utility on the server.<jupyter_code>! rsync -avze ssh jlasser@medea:/home/jlasser/Honesty-project/data/twitter/US_politician_unraveled_urls_3/ ../../data/twitter/US_politician_unraveled_urls_3/
! rsync -avze ssh jlasser@medea:/home/jlasser/Honesty-project/data/twitter/US_politician_unraveled_urls_4/ ../../data/twitter/US_politician_unraveled_urls_4/
src = '../../data/twitter/US_politician_unraveled_urls_1'
files = listdir(src)
unraveled_urls = pd.DataFrame()
for f in files:
    tmp = pd.read_csv(join(src, f), compression="gzip")
    unraveled_urls = pd.concat([unraveled_urls, tmp])
src = '../../data/twitter/US_politician_unraveled_urls_2'
files = listdir(src)
for f in files:
    tmp = pd.read_csv(join(src, f), compression="gzip")
    unraveled_urls = pd.concat([unraveled_urls, tmp])
unraveled_urls = unraveled_urls.reset_index(drop=True)
src = '../../data/twitter/US_politician_unraveled_urls_3'
files = listdir(src)
for f in files:
    tmp = pd.read_csv(join(src, f), compression="gzip")
    unraveled_urls = pd.concat([unraveled_urls, tmp])
unraveled_urls = unraveled_urls.reset_index(drop=True)
src = '../../data/twitter/US_politician_unraveled_urls_4'
files = listdir(src)
for f in files:
    tmp = pd.read_csv(join(src, f), compression="gzip")
    unraveled_urls = pd.concat([unraveled_urls, tmp])
unraveled_urls = unraveled_urls.reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Add hosts from timeouts<jupyter_code>timeouts = len(unraveled_urls) - len(unraveled_urls["status_code"].dropna())
print("{} timeouts ({:1.2f}%)".format(\
        timeouts,
        (timeouts / len(unraveled_urls["status_code"].dropna()) * 100)))
def extract_host(unraveled_url):
    if unraveled_url == unraveled_url and unraveled_url.startswith("Cannot"):
        host = unraveled_url.split(" ")[4].split(":")[0]
        return host
    else:
        return unraveled_url
unraveled_urls["unraveled_url"] = unraveled_urls["unraveled_url"].apply(extract_host)
dst = '../../data/twitter'
unraveled_urls.to_csv(join(dst, "US_unraveled_urls.csv.xz"), index=False,
                      compression="xz")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17500985415845488, 'fraction_numerical': 0.012022073314938904, 'mean_word_length': 4.90803259604191, 'pattern_counts': {'":': 2, '<': 16, '<?xml version=': 0, '>': 18, 'https://': 1, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '306970', 'license': 'MIT', 'n_tokens_mistral': 2044, 'n_tokens_neox': 1968, 'n_words': 322, 'path': 'code/corpus_twitter/get_US_politician_tweeted_URLs.ipynb', 'repo_name': 'StephanLewandowsky/Honesty-project', 'size': '5074'}