<jupyter_start><jupyter_text>Host Label Validation and ExplorationAfter plotting every feature's density distribution by relevance, the top 10 that are “most wrong” in either part of the value range were manually checked (i.e. videos often repeated across articles, yet labeled as "relevant"). This way, some mislabeled data could be identified and corrected. This was done iteratively until the top 10 "most wrong" labels in any value range were determined to be correctly labeled.<jupyter_code>import pandas as pd
import psycopg2

%load_ext autoreload
%autoreload 2
# Load the dataset from the database 
# TODO this is not DRY yet
# TODO don't use "most wrong" terminology
conn = psycopg2.connect(database="gdelt_social_video", user="postgres")
c = conn.cursor()

# Just work with youtube for now
platform = "youtube"
samples = pd.read_sql_query('''SELECT h.*, lh.twitter_relevant, lh.facebook_relevant, lh.youtube_relevant 
                                FROM hosts h RIGHT JOIN labeled_hosts lh  ON h.hostname=lh.hostname 
                                WHERE lh.%s_relevant <> -1''' % platform,con=conn)

samples = samples[["hostname", 
                   "article_count", 
                   "%s_video_sum" % platform, 
                   "%s_video_sum_distinct" % platform, 
                   "%s_video_count" % platform, 
                   "%s_relevant" % platform]]

# Compute the remaining interesting features
# Average number of videos per article, including articles without videos
samples["%s_video_average" % platform] = samples["%s_video_sum" % platform] / samples["article_count"]
# Average distinct videos per article
samples["%s_video_average_distinct" % platform] = samples["%s_video_sum_distinct" % platform] / samples["article_count"]
# Total videos to distinct videos
samples["%s_video_distinct_to_sum" % platform] = samples["%s_video_sum_distinct" % platform] / samples["%s_video_sum" % platform]
# Percentage of articles with videos
samples["%s_video_percentage" % platform] = samples["%s_video_count" % platform] / samples["article_count"]

samples.head()
# Print 10 hosts that are "most wrong"
samples[samples["youtube_relevant"] != 1].sort_values("youtube_video_count", ascending=True).head(10)
# Manually look at the articles from a host
articles = pd.read_sql_query("SELECT DISTINCT website_url FROM found_videos WHERE hostname='www.premiumtimesng.com' AND platform='youtube'",con=conn)
for row in articles.iterrows():
    print(row[1][0])<jupyter_output>https://www.premiumtimesng.com/business/275723-nupeng-suspends-21-day-ultimatum-to-oil-and-gas-sector.html
https://www.premiumtimesng.com/business/276006-amcon-gets-senates-backing-to-publish-debtors-list.html
https://www.premiumtimesng.com/business/277848-timeline-for-9mobile-acquisition-extended.html
https://www.premiumtimesng.com/business/business-news/275315-labour-criticises-new-tariffs-on-cigarette-alcohol-tobacco.html
https://www.premiumtimesng.com/business/business-news/275576-economist-magazine-ranks-lagos-business-school-among-worlds-50-top-business-institutions.html
https://www.premiumtimesng.com/business/business-news/276973-sec-advises-investors-on-shares-warns-against-ponzi-schemes.html
https://www.premiumtimesng.com/business/business-news/277078-why-exxonmobil-sacked-spy-police-officers-official.html
https://www.premiumtimesng.com/business/business-news/277748-konga-to-re-launch-pay-on-delivery.html
https://www.premiumtimesng.com/business/business-news/277804-dangote-don[...]
{'fraction_non_alphanumeric': 0.113909559244419, 'fraction_numerical': 0.021179164281625643, 'mean_word_length': 4.609951845906902, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 9, 'lorem ipsum': 0, 'www.': 10, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '12512', 'license': 'Apache-2.0', 'n_tokens_mistral': 1166, 'n_tokens_neox': 1061, 'n_words': 233, 'path': 'notebooks/Source Labels Validation and Exploration.ipynb', 'repo_name': 'ClaasM/GDELTSocialVideo', 'size': '3494'}