Charting The Emotions Of Television News: BigQuery + TV NGrams

The new Television News Ngram Datasets make an incredible range of new analyses possible. With just a single line of SQL and Google BigQuery, it is possible to perform sentiment mining across a decade of television news. Simple create a new BigQuery table and fill it with a list of words from any sentiment dictionary of interest, then run the query below to apply it to CNN's coverage over the past decade, computing the density of those words in its closed captioning, all within just 9.3 seconds!

SELECT DATE, SUM(COUNT) TOTWORDS, SUM(TONECOUNT) TOTTONEWORDS, SUM(TONECOUNT) / SUM(COUNT) * 100 perc_anxiety from (
SELECT DATE, WORD, COUNT, COUNT TONECOUNT FROM `gdelt-bq.gdeltv2.iatv_1grams` WHERE STATION='CNN' and WORD in (SELECT WORD FROM `YOUR-SENTIMENT-TABLE`)
UNION ALL
SELECT DATE, WORD, COUNT, 0 TONECOUNT FROM `gdelt-bq.gdeltv2.iatv_1grams` WHERE STATION='CNN' and WORD not in (SELECT WORD FROM `YOUR-SENTIMENT-TABLE`)
) group by DATE order by DATE asc