Analyzing Streaming Tweets with Python and PostgreSQL: A Comprehensive Guide

ByJordan Brown

Twitter has become a valuable source of real-time information and opinions on every conceivable topic. At any given moment, the Twitterverse is abuzz with millions of users sharing their thoughts, reactions, and hot takes in 280 characters or less. For data scientists and analysts, this firehose of streaming tweet data presents a golden opportunity to understand public sentiment, track emerging trends, and gain insights into how people are reacting to current events as they unfold.

In this guide, we‘ll walk through how to build a system for streaming and analyzing tweets using Python and PostgreSQL. We‘ll cover everything from setting up API access, to extracting data from tweets, storing it in a database, and performing analysis to derive insights. Whether you‘re a data professional looking to add social media analysis to your toolbox, or a developer interested in working with streaming data, this post will provide you with a solid foundation. Let‘s dive in!

Setting up Twitter API Access

The first step is to set up a Twitter Developer account and create a new application to obtain the necessary API keys and access tokens. Here‘s how to do it:

  1. Go to the Twitter Developer Platform and click "Apply" to create a new developer account. You‘ll need to provide some information about your intended use case.

  2. Once your developer account is approved, create a new Project and within that an Application to obtain the API key, API secret key, Access token and Access token secret required to authenticate with the Twitter API.

  3. Keep these keys secure and never share them publicly, as they provide access to your Twitter account and data.

Streaming Tweets with Tweepy

To stream tweets in real-time, we‘ll use the excellent Tweepy Python library which makes working with the Twitter API a breeze. First install Tweepy via pip:

pip install tweepy

Then use your API keys to create an authenticated API client:

import tweepy

consumer_key = "YOUR_API_KEY"
consumer_secret = "YOUR_API_SECRET"
access_token = "YOUR_ACCESS_TOKEN"
access_token_secret = "YOUR_ACCESS_SECRET"

auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)
api = tweepy.API(auth)

To stream tweets, we create a custom Stream object by subclassing tweepy.Stream. We override the on_status method which is called whenever a new tweet comes in. Here we can extract the data we want from the tweet and insert it into our database.

We can also add an on_error method to handle any errors that occur during streaming:

class MyStream(tweepy.Stream):    
    def on_status(self, status):
        print(status.text)
        # Extract data and store in DB here

    def on_error(self, status_code):
        if status_code == 420:
            return False

stream = MyStream(consumer_key, consumer_secret, access_token, access_token_secret)
stream.filter(track=[‘python‘, ‘data science‘, ‘machine learning‘])

This will stream all tweets containing the keywords "python", "data science" or "machine learning". The track parameter can be a single string or a list of strings.

Designing the Database Schema

To store the tweet data, we‘ll use a PostgreSQL database. We can design a simple schema with tables for Users, Tweets, Entities and other metadata. Here‘s an example:

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(255),
  screen_name VARCHAR(255),
  location VARCHAR(255), 
  followers_count INT,
  friends_count INT,
  created_at TIMESTAMP 
);

CREATE TABLE tweets (
  id BIGINT PRIMARY KEY,
  user_id BIGINT REFERENCES users(id),
  text VARCHAR(500),
  created_at TIMESTAMP,
  retweet_count INT,
  favorite_count INT,
  lang VARCHAR(20),
  contains_keyword VARCHAR(100)
);

CREATE TABLE entities (
  id SERIAL PRIMARY KEY,
  tweet_id BIGINT REFERENCES tweets(id),
  text VARCHAR(255),
  type VARCHAR(20)
);

The users table will store data about each unique Twitter user, with their ID acting as the primary key. The tweets table has a foreign key reference to the users table, along with the text content, creation date, language, and stats on retweets and favorites.

The entities table is used to store hashtags and user mentions extracted from the tweet text. This allows us to normalize the data model and easily query for tweets based on hashtags for example. More tables could be added to capture additional metadata.

Storing Data from Streaming Tweets

With our database set up, we can enhance the on_status method of our MyStream class to extract the relevant data from each incoming tweet and insert it into the appropriate tables:

def on_status(self, status):
    user = status.user
    user_data = [user.id, user.name, user.screen_name, user.location,         
                 user.followers_count, user.friends_count, user.created_at]

    tweet_text = status.text
    tweet_data = [status.id, user.id, tweet_text, status.created_at, 
                  status.retweet_count, status.favorite_count, status.lang]

    hashtags = [hashtag[‘text‘] for hashtag in status.entities[‘hashtags‘]]
    mentions = [mention[‘screen_name‘] for mention in status.entities[‘user_mentions‘]]

    # Insert data into database tables
    cur = db_conn.cursor()
    cur.execute("INSERT INTO users VALUES (%s, %s, %s, %s, %s, %s, %s)", user_data)
    cur.execute("INSERT INTO tweets VALUES (%s, %s, %s, %s, %s, %s, %s)", tweet_data)

    for hashtag in hashtags:
        cur.execute("INSERT INTO entities (tweet_id, text, type) VALUES (%s, %s, %s)", 
                    (status.id, hashtag, "hashtag"))

    for mention in mentions:
        cur.execute("INSERT INTO entities (tweet_id, text, type) VALUES (%s, %s, %s)", 
                    (status.id, mention, "mention"))

    db_conn.commit()

This extracts the key data points from the tweet and its associated user, and inserts them into the users, tweets and entities tables. Using parameter substitution with %s prevents SQL injection attacks.

We‘ve established a connection db_conn to our PostgreSQL database using the psycopg2 library. Remember to close the cursor and connection when done streaming.

Analyzing Tweets with SQL and Python

With a database full of tweet data, the fun part begins – analysis! We can use SQL queries to aggregate the data in various ways and uncover insights. Here are a few examples:

Most popular hashtags:

SELECT e.text, count(*) AS count 
FROM entities e
WHERE e.type = ‘hashtag‘
GROUP BY e.text
ORDER BY count DESC
LIMIT 10;

Users with the most followers:

SELECT name, screen_name, followers_count
FROM users
ORDER BY followers_count DESC
LIMIT 10;

Tweets with the most favorites and retweets:

SELECT t.text, t.retweet_count, t.favorite_count
FROM tweets t
ORDER BY t.retweet_count + t.favorite_count DESC
LIMIT 10;

To make the analysis more interactive and visual, we can use Python libraries like Pandas to load the results of our SQL queries into a DataFrame and manipulate further:

import pandas as pd
import matplotlib.pyplot as plt

cur = db_conn.cursor()
cur.execute("SELECT e.text, count(*) AS count FROM entities e WHERE e.type = ‘hashtag‘ GROUP BY e.text ORDER BY count DESC LIMIT 10")
hashtag_counts = pd.DataFrame(cur.fetchall(), columns=[‘hashtag‘, ‘count‘])

hashtag_counts.set_index(‘hashtag‘).plot.bar(figsize=(16,8))
plt.ylabel(‘Hash tag count‘)
plt.show()

This creates a bar chart of the top 10 hashtags. We could also use Pandas‘ .rolling() function to show how mention of particular hashtags changes over time, or the .apply() function to classify tweets into positive or negative sentiment for example.

Sentiment Analysis on Tweet Text

Speaking of sentiment analysis, this is a valuable technique to gauge public opinion from tweet text. In addition to showing the raw content of tweets, we can attempt to classify their sentiment to see an overall positivity or negativity score on a topic.

There are a few approaches to sentiment analysis, from simple rules-based techniques to advanced deep learning models. One easy to use Python library is VADER (Valence Aware Dictionary and sEntiment Reasoner) which can give the sentiment of a piece of text a score between -1 (very negative) to +1 (very positive).

from vaderSentiment.vaderSentiment import SentimentIntensityAnalyzer

tweets_df = pd.read_sql_query("SELECT text FROM tweets", db_conn)

sid_obj = SentimentIntensityAnalyzer()
tweets_df[‘sentiment‘] = tweets_df[‘text‘].apply(lambda x: sid_obj.polarity_scores(x)[‘compound‘])
tweets_df[‘sentiment_label‘] = tweets_df[‘sentiment‘].apply(lambda x: ‘positive‘ if x >= 0.05 else (‘negative‘ if x <= -0.05 else ‘neutral‘))

print(tweets_df[‘sentiment_label‘].value_counts(normalize=True))

This calculates the sentiment of each tweet, classifying them as positive, negative or neutral. We can see the overall breakdown by using value_counts with normalize=True to get the percentage in each class.

For more advanced NLP techniques like emotion analysis, named entity recognition or summarization, check out the NLTK and spaCy libraries, or newer transformer-based models from Hugging Face.

Best Practices and Tips

When building a tweet streaming and analysis pipeline, there are a few best practices and things to watch out for:

  • Make sure to handle errors and unexpected data gracefully. Tweets can contain oddities like emojis, special characters and malformed text. Use try/except blocks and clean the text before analysis.

  • Prevent data loss by committing tweet inserts to the database regularly, in small batches. This way if the script crashes you only lose a small amount of data. You can adjust Postgres‘ write-ahead log settings for extra durability.

  • Use connection pooling when accessing the database from multiple concurrent Python threads or processes. This avoids overwhelming the database with too many connections. The queries library provides a simple connection pool.

  • Consider using an ETL (extract, transform, load) pipeline with a tool like Apache Airflow to run sentiment analysis and load the results into summary tables. This avoids doing expensive computation on the fly.

  • To scale the system, look into using a message queue like RabbitMQ or Apache Kafka as an intermediary buffer between the streaming API and the database writes. This decouples the components and makes the system more robust to spikes in tweet volume.

Conclusion

Twitter provides a wealth of real-time information on public discourse and opinions. By combining the Twitter API, Python and PostgreSQL we‘ve seen how to build a scalable and flexible system for streaming, storing and analyzing tweet data.

The possibilities for deriving insights are endless – tracking brand mentions, measuring customer satisfaction, understanding how news events are shaping public opinion, and even predicting future trends. Python‘s rich data science libraries make it easy to go from raw tweet data to interactive visualizations, dashboards and predictive models.

I hope this guide has provided you with a solid foundation for working with streaming data and inspired you to build your own tweet analysis pipeline. Feel free to extend the code examples and adapt them to your own use case and requirements. With the right tools and techniques, anyone can become a social media data wrangler and uncover valuable insights from the chaos of the Twitterverse. Happy analyzing!

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

You May Like to Read,

Similar Posts