## CS 2034 Data Analytics: Principles and Tools

03 Apr

CS 2034 Data Analytics: Principles and Tools
Assignment 3
This assignment tests a variety of skills relating to
Data Munging
Textual Analysis
Visualization
Data Presentation
In this assignment, you will pre-process, analyze, and present data relating to a variety of health issues.
The data set includes about 5000 entries from Twitter. You will act as a data analyst and do the
following:
Remove duplicate tweets from the dataset
Calculate the sentiment of each tweet
Calculate the sentiment of each group
Visualize the hierarchical structure of the data, the rank of groups based on sentiment, and the
rank of groups based on tweet volume.
Create an infographic that summarizes your findings
1. Data Munging
Download the tweetsSample.csv file (from OWL) that contains the tweets, and store it as an
xlsm file
(YourUserID_Assign3.xlsm)
If you fail to save the file as .xlsm some of your work will be lost (e.g., functions/calculations)
Name the worksheet rawData
First use Excel’s remove duplicate feature (found in the Data toolbar) to remove the duplicate
tweets based on column A (text). This should reduce the tweets from 5000 to about 3600.
Within Excel, sort the data by column A (text)
Create a new column titled isDup (column F)
Create a new module and in it create a VBA function isDup that determines how similar a
tweet is to another.
Function isDup(tweet1 As String, tweet2 As String, threshold As
Double) As Boolean
The threshold is a percentage of the number of words that they must have in common. It is
based on the total number of words in the first tweet. This threshold value is fixed at
0.7 for this
assignment. This means that 70% of the words must be the same. So, if the first tweet has 100
words, at least 70 of those words must be in common with the second tweet for isDup to be
True. If it is less than 70 words like 56 or 34 then the tweet is not deemed a duplicate.
Note:
threshold is passed in as a parameter and should not be hardcoded in VBA as 0.7

Use the isDup function (in the worksheet rawData) to determine whether a tweet is like the
tweets that follows it. Check to see whether the tweet directly after it is similar. For each cell in
column F (isDup), call the isDup function. (there should be about 200 – 250 TRUE values)
Copy all the data values in the rawData into a new worksheet and name it processedData.
Sort the isDup column and delete all the rows that have TRUE as the value.
Delete the time and id columns
At this point processedData should have four columns and approximately 3380 – 3400 rows.
2. Sentiment Analysis
Copy the data in the keyword.csv file (downloaded from OWL) and add it as a new sheet, with
the name
Next, create a VBA function that determines the sentiment of each tweet based on its contents
Function sentimentCalc(tweet1 As String) As Integer
This function should check each word in the tweet and if the word exists as one of the keywords
in the positive list or negative list it should impact the overall sentiment value. The positive list
and negative list words exist in the
keywords sheet. Access the keywords as ranges within your
VBA code. The case of the word is inconsequential. For instance, happy, HAPPY, or happy are all
treated as positive words regardless of their case.
If the word is in the positive list, it should increase the sentimentValue by 10, if it is in the
negative list it should decrease it by 10
For instance, if the positive list includes “happy”, “cure”, “free” and the negative list
includes “pain”, “sick”, “ill”:
If the Tweet is “I am Happy that there is a cure”. The sentiment value will be 10 + 10 =
20
If the Tweet is “I am happy but in pain, tired of being SICK” The sentiment value will be
10 – 10 – 10 = -10
Use this function in your processedData worksheet to calculate the sentiment value for each
tweet (store in Column E)
Create a function with the header:
Function sentimentCategory(sentVal As Integer) As String
o if the sentiment value is greater than 0, the Sentiment Category is “Positive”
o If the sentiment value is less than 0, its category is “Negative”.
o If the sentiment value is equal to 0, its category is “Neutral”.
Use the above function to determine the category for each tweet (store in Column F)
3. Descriptive Analysis
Next you will analyze the sentiment of the tweets. You will use built-in excel functions (not VBA).
For most of the tasks below there are multiple ways to do it, two of which are described below.

The easiest way (but not the most effective) is to manually determine the range for each topic and use
this range inside excel functions (your data must be sorted first by topic and then by group to do this).
Another alternative is to use the AVERAGEIF(S)/COUNTIF(S) function and create a separate column with
only one instance of each topic/group.
First create a new worksheet called analyzedData. A screen shot of what the layout of this worksheet
should look like is shown below in Figure 1.
TASK A: Calculate the overall sentiment value for each topic.
o Overall Sentiment for Topic: average of the sentiment values for the tweets within that
topic
(There are over 39 topics in the dataset)
TASK B: Calculate the overall sentiment value for each group.
o Overall Sentiment for Group: average of the sentiment values for the tweets within each
group
(There are about 8 groups in the dataset)
TASK C: Determine the total number of Positive, Negative, and Neutral Tweets in the dataset
TASK D: Determine the number of Positive, Negative and Neutral Tweets for each group
TASK E: Determine the number of Positive, Negative and Neutral Tweets for each topic
Figure 1: Layout of AnalyzedData worksheet
4. Visual Representations
Create visual representations using visualization software (i.e., RAW, Tableau, or Excel).
First, decide on which representation is best suitable for each task
(based on the representations you
have been exposed to in class, lab 8, and the ones that are available within the visualization software),
then format and transform your data so it can be visualized. This task is not trivial as transforming data
into a structured format is part of the representation process.
When using RAW, it is important that
after you determine the visual representation to be used that you use the sample data to understand the
structure your data needs to be in.
You need to provide five visualizations. One visualization for each of the following tasks:
1. Show the hierarchical structure of the tweet topics and groups
2. Show the hierarchical structure and volume of tweets based on the topics and groups
3. Show the rank of groups based on the overall group sentiment value (sorted by rank)
4. Show the rank of topics based on the overall topic sentiment value (sorted by rank)
5. Show the flow of tweets (between group and topic) based on the number of positive number of
tweets.
You don’t need to use all the 3 visualization applications. Just determine which ones have the
5. Presentation
You will not be graded on your graphic skills but on how you communicate the results.
Using at least 3 of the visual representations you have created in Part 4, create an infographic
using Microsoft Word, Paint, Adobe Photoshop, other software available to you, or one of the
infographic tools below:
o Visual.ly
o Easel.ly
o Piktochart
o Venngage
Stinks
and search the web.
o Explain the data set, and the images you included from part 4.
o Have at least 3 facts.
o Have a title and at least 2 sub sections.
6. Submit via OWL
Submit the following (Make sure you attach all files to your assignment)
YourUserID_Assign3.xlsm (PART 1 – 3)
o This should include the first sheet with the original data set and the isDup
function being called
(rawData)
o The second sheet will be the imported keyword data (keywords)
o The third sheet which uses the sentimentCalc and sentimentCategory functions
(processedData)
o The fourth sheet which provides the summary information (analyzedData)
YourUserID_Assign3.pdf (PART 4)
o A PDF that includes all five images.
YourUserID_Assign3Infographic.png, YourUserID_Assign3Infographic.jpg or
YourUserID_Assign3Infographic.pdf (PART 5)
o A PDF, PNG, or JPEG image for your infographic.
What You Will Be Assessed On: Completion of tasks outlined above