Compute a new field called interaction count whose value is the sum of favorite count and retweet count.

ICT233 Copyright © 2021 Singapore University of Social Sciences (SUSS) Page 1 of 8
TMA – July Semester 2021
ICT233
Data Programming
Tutor-Marked Assignment
July 2021 Presentation
ICT233 Copyright © 2021 Singapore University of Social Sciences (SUSS) Page 2 of 8
TMA – July Semester 2021
TUTOR-MARKED ASSIGNMENT (TMA)
This assignment is worth 24% of the final mark for ICT233, Data Programming.
The cut-off date for this assignment is Monday, 13 Sept 2021, 2355 hrs.
Note to Students:
You are to include the following particulars in your submission: Course Code, Title of
the TMA, SUSS PI No., Your Name, and Submission Date.
Answer all questions. (Total 100 marks)
Question 1 (47 marks)
Objectives:
● Understand dataset with data scientist mind-set.
● Understand and design computation logic and routines in Python.
● Assess use of Python only and Python data structures to perform extract, load,
and transformation operations.
● Assess the design and use of database SQL and methods to perform extract, load,
transformation and calculation operations.
● Structure code in appropriate methods (functions), looping and conditions.
(a) From tweets.json, find and apply all entity types using Python code. For example,
hashtag is one entity type.
(2 marks)
(b) Create the tweets schema and store tweets in tweets.json file to a SQLite database.
The tweets schema contains the following fields: id, created_at, full_text,
favorite_count and retweet_count. The field id is the primary key.
(5 marks)
(c) Compose and create schema and store data.
(i) Create the entities schema with the following fields and requirements:
 id: primary key
 tweet_id: foreign key, which links to the id field of the tweets table
 type: possible values found in Question 1(a)
 value: contains entity text values, which appear in a tweet’s full_text
 start index and end index: stored the values found in the JSON key
indices
(2 marks)
ICT233 Copyright © 2021 Singapore University of Social Sciences (SUSS) Page 3 of 8
TMA – July Semester 2021
(ii) Extract entities from every tweet to store into the entities table in the
SQLite database. Note that the entities of a tweet can be extracted from
the JSON key entities of each tweet.
(8 marks)
(d) Use SQL statement(s) to identify all unique values in hashtags.
(3 marks)
(e) Use SQL statement(s) to find all tweets which have a value of hashtags appearing
more than 1 time.
(5 marks)
(f) Use SQL statement(s) to calculate how many tweets that each value of hashtags
appears in. Sort the tweet counts in descending order. Note that a hashtag which
appears more than 1 time in a tweet, count only 1time for that tweet.
(5 marks)
(g) Plot the tweet counts of the top 10 common hashtags obtained in Question 1(f)
on a bar chart
(5 marks)
(h) Compute a new field called interaction_count whose value is the sum of
favorite_count and retweet_count.
(2 marks)
(i) Note that the time zone of created_at from the JSON file is UTC+0. To
visualize data relationships respectively as required below and draw
insight(s) from the visualizations:
 the interaction_count and hour of the day (in Singapore time zone) of the
created_at
 the interaction_count and day of the week (in Singapore time zone) of
the created_at
(10 marks)
ICT233 Copyright © 2021 Singapore University of Social Sciences (SUSS) Page 4 of 8
TMA – July Semester 2021
Question 2 (25 marks)
Objectives:
● Design computation logic and routines in Python.
● Assess use of Pandas dataframes to perform extract, load, transformation and
calculation operations.
● Conduct visualization in an appropriate way.
(a) Copy the template in Table 1 into your program. Analyse and implement the
TWO (2) Python functions. These two functions perform the pre-processing
logic to prepare each tweet for the word cloud visualization subsequently. Read
the detailed instruction and steps in the provided template.
import nltk
from nltk.corpus import stopwords
nltk.download(‘stopwords’)
from spellchecker import SpellChecker
def remove_entities(tweet_id, tweet):
# MUST use start and end indices extracted in the
question 1c) to remove entities
# Output: the preprocessed tweet with all entities
(hashtags, urls, media and user mentions) removed
return “”
def preprocess_tweet(tweet_id, tweet, remove_entities):
# 1) Lower case the input tweet
# 2) Call the remove_entities function to remove
hashtags, media, urls and user mentions from the input
tweet
tweet = remove_entities(tweet_id, tweet)
# 3) Remove all special characters
# 4) Remove English stop words which are defined at
“from nltk.corpus import stopwords”
# 5) Use the pyspellchecker library:
https://pypi.org/project/pyspellchecker/ to remove
misspelled words

# Output: a list of words constituting the input tweet
# For example, with the tweet “#SUSSSustainability:
what are the three incorrect assumptions about”,
# the output is the list of words: [three, incorrect,
assumptions].
return []
assert preprocess_tweet(‘1408411651238371337’,
‘#SUSSSustainability: What are the
three incorrect assumptions about climate change? A/P Koh
Tieh Yong from SUSS Centre for University Core addresses
these issues with Karen Cheah, Founder and CEO of
AlterPacks: https://t.co/DtjRrIzPTK’,
remove_entities) == [‘three’,
‘incorrect’, ‘assumptions’, ‘climate’, ‘change’, ‘tieh’,
‘yong’, ‘suss’, ‘centre’, ‘university’, ‘core’, ‘addresses’,
‘issues’, ‘karen’, ‘founder’, ‘ceo’]
Table 2: Template contains 2 functions to be implemented
(20 marks)
ICT233 Copyright © 2021 Singapore University of Social Sciences (SUSS) Page 5 of 8
TMA – July Semester 2021
(b) Apply the function preprocess_tweet implemented in Question 2(a) to every
tweet in the database. The lists of pre-processed words are then concatenated into
a single text as the input of WordCloud library
(https://pypi.org/project/wordcloud/) to plot a word cloud visualization.
(5 marks)
Question 3 (28 marks)
Objectives:
● Perform simple exploratory data analysis.
● Design computation logic and routines in Python.
● Assess use of Python only and Python data structures to perform extract, load,
transformation, and calculation operations.
● Assess use of Pandas dataframes to perform extract, load, transformation and
calculation operations.
● Assess the design and use of database ORM and methods to perform extract,
load, transformation and calculation operations.
Use ORM (unless state otherwise) to compute tf–idf
(https://en.wikipedia.org/wiki/Tf%E2%80%93idf), which can be used as a feature to
classify tweets or to search tweets by user queries.
(a) Create and develop new SQLite table called tweet_word_pairs with 2 columns
tweet_id and word. Break the words column obtained in Q2(b) into multiple rows
to form pairs of column tweet_id and word. Insert (tweet_id, word) pairs
computed from the previous step into this tweet_word_pairs table.
Each row in the dataframe generated in Q2(b) corresponds to a tweet and the
words field of each row contains the list of pre-processed words computed from
the full_text. One of the rows in the dataframe generated in Q2(b) is shown in
Figure 1. Considering the above row as the example, we will have the expected
result show in Figure 2.
(6 marks)
Figure 1: One of the rows in the dataframe generated in Q2(b)
ICT233 Copyright © 2021 Singapore University of Social Sciences (SUSS) Page 6 of 8
TMA – July Semester 2021
Figure 2: Expected result of the given row
(b) Compute the number of times each word appear in each tweet and store the value
ftd(word, tweet) in the column called ftd. Example output format is shown in Figure
3.
(4 marks)
Figure 3: Sample output format for Q3(b)
(c) Apply and use dataframe to compute the largest ftd(word, tweet) per tweet. Example
output is shown in Figure 4.
𝑚𝑎𝑥{𝑓𝑡𝑑(𝑤𝑜𝑟𝑑 𝐵, 𝑡𝑤𝑒𝑒𝑡)
, 𝑓𝑜𝑟 𝑒𝑣𝑒𝑟𝑦 𝑤𝑜𝑟𝑑 𝐵 ∈ 𝑡ℎ𝑒 𝑡𝑤𝑒𝑒𝑡}
(2 marks)
Figure 4: Sample output format for Q3(c)
(d) Use dataframe to compute the term frequency per (word, tweet). Example output
is shown in Figure 5.
tf(word A, tweet) = 0.5 + 0.5 * 𝑓𝑡𝑑(𝑤𝑜𝑟𝑑 𝐴, 𝑡𝑤𝑒𝑒𝑡)
𝑚𝑎𝑥{𝑓𝑡𝑑(𝑤𝑜𝑟𝑑 𝐵, 𝑡𝑤𝑒𝑒𝑡)
, 𝑓𝑜𝑟 𝑒𝑣𝑒𝑟𝑦 𝑤𝑜𝑟𝑑 𝐵 ∈ 𝑡ℎ𝑒 𝑡𝑤𝑒𝑒𝑡}
(4 marks)
ICT233 Copyright © 2021 Singapore University of Social Sciences (SUSS) Page 7 of 8
TMA – July Semester 2021
Figure 5: Sample output format for Q3(d)
(e) Compute the number of unique tweets which the word appears for each word.
Example output is shown in Figure 6.
(4 marks)
Figure 6: Sample output format for Q3(e)
(f) Use dataframe to compute inverse document frequency. Example output is
shown in Figure 7.
idf(word A) = log 𝑛𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑢𝑛𝑖𝑞𝑢𝑒 𝑡𝑤𝑒𝑒𝑡𝑠
𝑛𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑢𝑛𝑖𝑞𝑢𝑒 𝑡𝑤𝑒𝑒𝑡𝑠 𝑤ℎ𝑖𝑐ℎ 𝑡ℎ𝑒 𝑤𝑜𝑟𝑑 𝐴 𝑎𝑝𝑝𝑒𝑎𝑟𝑠
(4 marks)
Figure 7: Sample output format for Q3(f)
(g) Use dataframe to compute term frequency-inverse document frequency.
Example output is shown in Figure 8.
ifidf(word A, tweet) = tf(word A, tweet) * idf(word A)
(4 marks)
ICT233 Copyright © 2021 Singapore University of Social Sciences (SUSS) Page 8 of 8
TMA – July Semester 2021
Figure 8: Sample output format for Q3(g)
—– END OF PAPER —–