Introduction

In this tutorial, we will learn 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.

Creating a Django Application

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 Django Data 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 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, hourin 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.