With a single SQL query and the Online News Ngram Dataset, its possible to run large-scale macro emotional assessments of online news over the past year like we did for television news! For example, Extended Moral Foundations Dictionary (eMFD) from the Media Neuroscience Lab at the University of California Santa Barbara assesses the moral foundations invoked in text. To compute the daily eMFD scores for English language online news just load the dictionary into a new table and apply it using the query at the end of this post!
To calculate meaningful emotional scores from this data, please see this scoring guide from the eMFD team!
TECHNICAL DETAILS
Creating these scores involves the same formula as we used for television news:
select DATE, sum(cnt) cnt,
sum(care_p) / sum(cnt) care_p,
sum(fairness_p) / sum(cnt) fairness_p,
sum(loyalty_p) / sum(cnt) loyalty_p,
sum(authority_p) / sum(cnt) authority_p,
sum(sanctity_p) / sum(cnt) sanctity_p,
sum(care_sent) / sum(cnt) care_sent,
sum(fairness_sent) / sum(cnt) fairness_sent,
sum(loyalty_sent) / sum(cnt) loyalty_sent,
sum(authority_sent) / sum(cnt) authority_sent,
sum(sanctity_sent) / sum(cnt) sanctity_sent
from (
select DATE, a.word word, a.cnt cnt, b.care_p*a.cnt care_p, b.fairness_p*a.cnt fairness_p, b.loyalty_p*a.cnt loyalty_p, b.authority_p*a.cnt authority_p, b.sanctity_p*a.cnt sanctity_p, b.care_sent*a.cnt care_sent, b.fairness_sent*a.cnt fairness_sent, b.loyalty_sent*a.cnt loyalty_sent, b.authority_sent*a.cnt authority_sent, b.sanctity_sent*a.cnt sanctity_sent from (
SELECT substr(CAST(DATE AS STRING),0,8) DATE, ngram word, SUM(COUNT) cnt FROM `gdelt-bq.gdeltv2.web_1grams` WHERE DATE>=20200101000000 and lang='ENGLISH' GROUP BY DATE, ngram
) a JOIN (
SELECT word, care_p, fairness_p, loyalty_p, authority_p, sanctity_p, care_sent, fairness_sent, loyalty_sent, authority_sent, sanctity_sent FROM `[YOURTABLE].tone_emfd`
) b on a.word = b.word
) group by DATE order by DATE asc