With a single SQL query and the Television News Ngram Dataset, its possible to run large-scale macro emotional assessments of television news over the past decade! 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 CNN, MSNBC and Fox news, just load the dictionary into a new table and apply it using the query at the end of this post!
For example, here are the raw daily "care_sent" scores for CNN over the past decade (see the scoring guide below to convert into the final scores):
To calculate meaningful emotional scores from this data, please see this scoring guide from the eMFD team!
For those just interested in seeing the scores, here are the daily scores from 2009 through 2020 for the three stations in CSV format! Scores have been updated through 5/8/2020 with the new formula below.
TECHNICAL DETAILS
Load the eMFD dictionary into a new BigQuery table with the following schema and call it "tone_emfd":
word:STRING,care_p:FLOAT,fairness_p:FLOAT,loyalty_p:FLOAT,authority_p:FLOAT,sanctity_p:FLOAT,care_sent:FLOAT,fairness_sent:FLOAT,loyalty_sent:FLOAT,authority_sent:FLOAT,sanctity_sent:FLOAT
Then run it against the television ngram dataset (the formula below has been updated to use SUM(COUNT) from the original COUNT(1) to count frequency rather than binary appearance.
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 DATE, word, sum(COUNT) cnt FROM `gdelt-bq.gdeltv2.iatv_1grams` WHERE DATE>=20090703 and station='CNN' GROUP BY DATE, word
) 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
That's all there is to it!