Temporal COP15 sentiment analysis

Posted on Updated on

Nielsen2009responsible_cop15tw

I finally managed to construct a plot of the temporal evolution of sentiments in relation to COP15 tweets. As I have written before we are presently downloading posting from Twitter based on a query on COP15 — the United Nations Climate Change Conference held here in Copenhagen. So far we have over 70’000 individual COP15 tweets. Each of these tweets is automatically scored for valence within our simple sentiment analysis, and by joining the valence of each tweet to its date a plot can be made which shows the number of positive and negative COP15 each day — with neutral postings excluded. Not surpricing there is an increase in posts as we got nearer and nearer to the opening with a present peak the day that COP15 began: Monday 7 December. The ratio between positive and negative tweets is not that varying over time. The weekend has a low number of postings.

The tweets are stored in a SQLite. I am not so familiar with the SQL database query language, and so far the best SQL I could construct to count the positive and negative tweets per day looks like this:

SELECT * FROM (SELECT strftime('%Y-%m-%d', created_at) AS timestamp_positive, COUNT(*) FROM tweets, tweets_sentiment WHERE query LIKE 'cop15' AND tweets.id = tweets_sentiment.id AND valence > 2 AND ambivalence < 3 GROUP BY timestamp_positive JOIN (SELECT strftime('%Y-%m-%d', created_at) AS timestamp_negative, COUNT(*) FROM tweets, tweets_sentiment WHERE query LIKE 'cop15' AND tweets.id = tweets_sentiment.id AND valence < -2 AND ambivalence < 3 GROUP BY timestamp_negative) ON timestamp_positive = timestamp_negative;

I guess there might be a more effective way than this statement, since (as far as I can determine) the SQL is running more than one time over all tweets.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s