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.