Have you ever wanted to track your tweet activity on Twitter? Then this article is for you. I love how Django’s ORM makes it easy to model a database and modify it with views (i.e., the app’s functional back-end).

In this tutorial, we will be learning how to manage tweet activity using the official Twitter API and generate a heat map from tweets using Arctype. To achieve this, we will be tracking some very popular celebrities’ tweets to create a heatmap like the one in the image above.

Prerequisites

To fully understand this tutorial, you should have the following:

Python 3.7 or newer.
Arctype
Basic understanding of SQL.
Basic understanding of Django.
A Twitter developer account.
A text editor.

Setting up the Twitter API

To make use of the Twitter official API, you need to set up your Twitter developer account. Go to https://developer.twitter.com and signup if you haven’t. To sign up, you will need to provide detailed information on how you want to utilize your access to the Twitter API. After providing the information, you will need to wait for Twitter to verify and enable your account.

When your account is verified, go to your developer dashboard, as seen in the image above.

At this point, you need to create a project which can house multiple applications. First, click on create project, then give your project a name, use case, and a description.

Now you need to create an application. To do this, click on create a new app and provide a name for your application.

After creating your Twitter application, you will see a page with your application keys, as seen in the image below. Copy and save them somewhere you can easily retrieve them.

Go to the Project menu to then open your project. In the apps section, click on the key icon, then generate an access token and access secret token for your application at the bottom of the page displayed.

Click on the generate button, then copy and save the access_token and access_secret_token somewhere you can easily retrieve them.

Getting Started with Django

To create the Django application, run the following code in your command line:


# creating the project

python manage.py startproject TweetsDaily

# change to the project directory

cd TweetsDaily

#creating the app

python manage.py startapp App

Now, go to the settings.py file and App to the list of installed apps as seen below:


INSTALLED_APPS = [

    'django.contrib.admin',

    'django.contrib.auth',

    'django.contrib.contenttypes',

    'django.contrib.sessions',

    'django.contrib.messages',

    'django.contrib.staticfiles',

    'App'

]

Next, go to the urls.py file in the project directory and edit as seen in the code below:


from django.contrib import admin

from django.urls import path, include

from django.conf import settings

urlpatterns = [

    path('admin/', admin.site.urls),

    path('', include("App.urls")),

]

Finally, go to the App folder and create a new urls.py file. Add the python code below to the file and save:


from django.conf import settings

from django.conf.urls.static import static

from django.urls import path, include

from . import views

app_name = "App"

urlpatterns = [

    path("", views.index, name="index"),

]

In the code above, we defined the index URL and connected it to the index view, which we will define in our views.py file.

Building Our Django Models

Here we will define the table models in the models.py file as see in the python code below:


from django.db import models

# Create your models here.

from django.db import models

# Create your models here.

class Tweets(models.Model):

    username= models.TextField()

    tweet_number = models.IntegerField()

    created_at = models.IntegerField()

    time = models.IntegerField()

    retweet_count= models.IntegerField()

    def __str__(self):

        return self.tweet_number

After saving, we need to migrate the database using the below command in the project directory.


#making migrations

python manage.py makemigrations

#migrating the database

python manage.py migrate

Writing Our Django Views

The views.py file in the Django project is where we write the main logic for the application.

Importing the Required LIbraries

Copy and paste the python code below at the top of the views.py file to import the required libraries.


from django.shortcuts import render

from tweepy import OAuthHandler

from tweepy import API

from tweepy import Cursor

from datetime import datetime, date, time, timedelta

from collections import Counter

from django.http import HttpResponse

from . models import Tweets

Initializing Tweepy

Copy and paste the code below in the views.py file and provide your real keys to initialize Tweepy.


consumer_key="api_key"

consumer_secret="api_secret_key"

access_token="access_token"

access_token_secret="access_token_secret"

auth = OAuthHandler(consumer_key, consumer_secret)

auth.set_access_token(access_token, access_token_secret)

auth_api = API(auth,wait_on_rate_limit=True)

We also need to create a list of the usernames of all celebrities whose tweets we want to analyze, as seen in the code below.


username=["Cristiano","BarackObama","rihanna","TheRock"]

Creating the Index View

Copy and paste the python code below in the views.py file to build the index view. This view is triggered by loading the application in the browser:


def index(request):

    tweet_count = 0

    for i in username:

        try:

            for status in Cursor(auth_api.user_timeline, id=i).items():

                tweet_count = tweet_count + 1

                if status.created_at.year == 2021 and status.created_at.month == 5:

                    tweets_save= Tweets.objects.create(username=i,tweet_number=tweet_count,created_at=status.created_at.day,time=status.created_at.hour,retweet_count=status.retweet_count)

                    tweets_save.save()

        except:

            pass

    return HttpResponse('<h1>Loaded Tweets Data</h1>')

In the code above, we are making a request to the Twitter API using Tweepy to retrieve all the user’s tweets. We are now running a loop through the user’s tweets to check for all the tweets made in May 2021. Finally, we will save all the tweets and the number of times the user’s followers retweeted them to the database. The applications will then send an HttpResponse “Loaded Tweets Data” to the application when run.

To run your application, copy and paste the below commands in your command line’s project directory:


#running the application

python manage.py runserver

If the application is running, you should see the result as seen below.

Go to http://127.0.0.1:8000 to view the running application and trigger the index view to save all the tweet data in the database.

Connecting Arctype to a MySQL Database

To make use of Arctype, we need to connect our database and set up a workspace. To do this, follow the instructions below.

Step 1

Open Arctype, click on “I have an existing database”, and select your database type. For this tutorial, we are using MySQL.

Step 2

Provide the required information for the database and click Save.

Step 3

You should now see the database in Arctype. Next, go to the Tweets table. You should have something similar to the image below.

Compare Retweets between Accounts Using MySQL

We currently have multiple tweet data for May in the year 2021 saved in the Tweet table. To make a heat map of the data, we need to sum up the retweet count for the tweets created each day in the month of may and group all the tweets that the user’s followers retweeted by the date and username. In addition, because Barack Obama and Rihanna are two of the most-followed people on Twitter, we should factor in the size of each account's following to get a clearer picture in our data.

To achieve this, click on the new query button, then save and run the SQL query below:


SELECT

  username,

  (

    CASE

      WHEN (username = 'rihanna') 

              THEN (SUM(retweet_count) / 102300000 * 100)

      WHEN (username = 'BarackObama') 

              THEN (SUM(retweet_count) / 129600000 * 100)

      WHEN (username = 'TheRock') 

              THEN (SUM(retweet_count) / 15200000 * 100)

      WHEN (username = 'Cristiano') 

              THEN (SUM(retweet_count) / 92400000 * 100)

    END

  ) AS tweet_heat,

  CONCAT('May ', created_at)

FROM

  app_tweets

GROUP BY

  created_at,

  username

ORDER BY

  created_at ASC

Running the code above will provide you with data, as seen in the image below.

Build a Twitter Heat Map Chart in Arctype

To create a heat map chart for the tweet data above, follow the instructions below:

Create a New Arctype Dashboard

Create a new tab, then select the New Dashboard button in the top menu. Then, click on the + Add button, then select ‘chart.’

Get Chart Data from a Saved Query

Click on Select Chart Data on the blank dashboard provided.

Edit the title and select the query you saved above (in my case, TweetsDailyStats) as the data source.

Assign Result Columns to Heat Map Axes

Select ‘Heat map’ as chart type, then drag and drop data arranged as seen in the image below.

Customizing an Arctype Heat Map

Finally, go to the advanced tab, then enter “Retweets Per Day” as the y-axis value.

After following the above instructions, you should have a heat map similar to the one in the image below.

The heat map above displays the retweets activity for every tweet made by the four celebrities in May 2021 grouped by the day the celebrity created the tweet.

Other Heat Map Examples

Visualizing User Tweets

Create a new query with the SQL query below. Then, run and save the query.


WITH alldates AS(

  SELECT

    distinct created_at

  FROM

    app_tweets

  ORDER BY

    1

),

alltimes AS(

  SELECT

    distinct time

  FROM

    app_tweets

  ORDER BY

    1

),

timetable AS(

  SELECT

    *

  FROM

    alldates,

    alltimes

  ORDER BY

    1

)

SELECT

  CONCAT('May ',t.created_at) AS day,

  CONCAT(t.time,':00') AS hour,

  COUNT(a.tweet_number) as tweets

FROM

  timetable t

LEFT OUTER JOIN app_tweets a ON (a.username = 'BarackObama' AND a.created_at = t.created_at AND a.time = t.time)

GROUP BY t.time, t.created_at

ORDER BY t.created_at, t.time

Create a new heat map chart using the query data you saved above. Then, configure the heat map to have the day field in the x-axis, hour in the y-axis, and the tweets in the z-axis.

The heat map above displays every tweet made by one of the celebrities (i.e., Barack Obama) in May 2021, grouped by the day and the hour of the day the user created the tweet.

Querying for a Twitter User's Retweets

Create a new query with the SQL query below. Then, run and save the query.


WITH alldates AS(

  SELECT

    distinct created_at

  FROM

    app_tweets

  ORDER BY

    1

),

alltimes AS(

  SELECT

    distinct time

  FROM

    app_tweets

  ORDER BY

    1

),

timetable AS(

  SELECT

    *

  FROM

    alldates,

    alltimes

  ORDER BY

    1

)

SELECT

  CONCAT('May ',t.created_at) AS day,

  CONCAT(t.time,':00') AS hour,

  SUM(a.retweet_count) as retweets

FROM

  timetable t

LEFT OUTER JOIN app_tweets a ON (a.username = 'BarackObama' AND a.created_at = t.created_at AND a.time = t.time)

GROUP BY t.time, t.created_at

ORDER BY t.created_at, t.time

Create a new heat map chart using the query data you saved above. Then, configure the heat map to have the day field in the x-axis, hour in the y-axis, and the retweets in the z-axis.

The heat map above displays the retweets activity for every tweet made by one of the celebrities (i.e., Barack Obama) in May 2021, grouped by the day and the hour of the day the user created the tweet.

Visualize Tweets-Retweets Ratio

Create a new query with the SQL query below. Then, run and save the query:


WITH alldates AS(

  SELECT

    distinct created_at

  FROM

    app_tweets

  ORDER BY

    1

),

alltimes AS(

  SELECT

    distinct time

  FROM

    app_tweets

  ORDER BY

    1

),

timetable AS(

  SELECT

    *

  FROM

    alldates,

    alltimes

  ORDER BY

    1

)

SELECT

  CONCAT('May ',t.created_at) AS day,

  CONCAT(t.time,':00') AS hour,

  CAST((SUM(a.retweet_count)/COUNT(tweet_number)) AS UNSIGNED) as ratio

FROM

  timetable t

LEFT OUTER JOIN app_tweets a ON (a.username = 'BarackObama' AND a.created_at = t.created_at AND a.time = t.time)

GROUP BY t.time, t.created_at

ORDER BY t.created_at, t.time

Create a new heat map chart using the query data you saved above. Then, configure the heat map to have the day field in the x-axis, hour in the y-axis, and the ratio in the z-axis.

Conclusion

In this article, you learned how to track tweet data with Tweepy and visualize them as a heat map on a dashboard with Arctype. In addition, you saw how easy it is to run SQL queries on your database using Arctype and got the chance to explore some of its core features and functionalities.

The source code of the dashboard application and the links to the Arctype SQL dashboard are available on Github. If you have any questions, don't hesitate to contact me on Twitter: @LordChuks3.

Blog

Building A Tweets Heat Map Charts with Tweepy, Django and Arctype

Chukslord