Visualizing Twitter Tokens- Hashtags, Smileys and URLs in R

Hello Readers!

Tweet, Tweet!

Welcome back to the blog. Today we will discuss how to visualize Twitter tokens trends in tweets, specifically:

• hashtags  #
• smileys  =)
• and URLs  http://youtu.be/iPSXDDg-aZw 

The data was obtained from the infochimps site, where they also host data sets for other platforms. For analysis I will be using RStudio.

Let us get started.

The Loading

Unpacked Zip File Content

Once we have downloaded and unzipped the 'tokens by month' data set, we can go ahead a read the tsv file (tab separated values) into R. Use the str() function to get an idea of the data structure.

Reading in Total Tokens by Hour tsv

Looking at the structure, we see that there are 3 columns with 67,992 observations consisting of "tweet_url", "smiley", or "hashtag". The X column denotes the 4 digit year, month, day, and hours in 24 hour format, followed by the count in the X1 column. Use the table() function to determine how many token measures we have of each, and also to check for spelling errors.

Token Measures

We see there are 18,401 hashtag measures, 25,137 smiley measures, and 24,454 tweet_url measures. Keep in mind these are not counts- they are just the number of times the tokens were measured in the data set. The actual counts are in column 3, X1. To obtain the total counts for each measure, we use tapply() to apply a function by index. The totals are shown above, with tweet_urls coming in top at 167,819,007! It seems that people are tweeting more internet links than hashtags and smileys put together.

The tokens() Function

Next we write the tokens() function. Keep in mind of the variables we have to track when we separate the all the information pertaining to the three different tokens. At the same time, I want to convert the dates into an usable Date.Time format. Putting it together, the result will be a list containing the date and count of the hashtag, smiley, and tweet_url tokens.

The first part of the function is shown below.

Token Function Part 1

 We start the function by initializing the variables we need to keep track of and using counters to progress the variables along through each successive record (h, u, and s) of their respective time and count variables in the for loop. We take the pertinent data from each type of token into their own variables to put in a list. 

Next, we take the date variables and convert them into Date.Time format using the strptime() function, shown below. Afterwards, we create the list and instruct the function to return the tokens list. Now run the tokens () function.

Token Function Part 2

Finally, use the completed tokens() function to create the t.tokens list.

The Plotting

Now that we have the tokens in a convenient list, we can visualize the token trends with a plot. Naturally, use the plot() function, a sample method is show below.

Plotting the Hashtag Count

 The second section will create the x axis labels consistent with the Date.Time values in the t.tokens$count.h variable. It will plot the year and month for better readability.

Hashtag Count Plot

We will add the two other tokens next using the lines() function. And to finalize the plot, we will add a legend to interpret which line is which token.

Adding Smiley and URL Count Lines

Now we have the finished plot!

Plot of All Three Token Counts Over Time

Note that starting in January of 2009, user activity, especially in URL content are beginning to spike above the previous background levels. Observe that drastic spikes in URL counts in July and October of 2009 also coincided with hashtag and smiley counts as well. The increases in content can be attributed to the increased in Twitter users, starting in 2009. 

Stay tuned for more Twitter analysis!

Thanks for reading!


Wayne

Importing Data into a Database Engine, SQL Server 2012

Hello Readers!

Welcome back. We need data to be able to query tables. So today we will walk through how to import data into a Database Engine in SQL Server Management Studio 2012. Click here for more info on SSMS 2012. 

Let us get started!

The Setup

First, open SQL Server Management Studio (SSMS). We are greeted with a dialogue box to connect to a server. I will use Windows Authentication to connect to my local server. 

Connect to a Server

After we have connected, we see the Object Explorer to the left of the screen. The Server will show a green arrow to indicate a successful connection. The Object Explorer allows us to view and manipulate the different utilities, tools, and capabilities in SSMS. The expanded explorer box is shown below. Note the green 'good to go' arrow by the server name.

Connection Established

Importing the Data

Now we need to import the data. It would be helpful if we had the target data already on the computer, and in this case, we will be importing the familiar AdventureWorks database which exists as an Access database file. We can also import it as an mdf (mirror disk file) from this link.

Start by right clicking the database to where the data will be imported. Now from Tasks, click Import Data... towards the bottom.

Import Data Button

In step, we are in the SQL Server Import and Export Wizard. As shown below. Select the appropriate data source and locate the file on the hard drive:

Select the Data Source and Location

After choosing the input specifications, we need to choose the output Destination. I will import the data into the POWER (short for PowerPivot) database.

Choosing the Destination

And then for the nitty-gritty of specifying which tables to copy. We have two options: manual selection or SQL query. Select either, I will select manual because I will be copying all the data.

Specify Which Selection Method

After which, we can check the boxes of the tables we require. Clicking the box at the top left will select all of the tables. Click Next.

Selecting Tables

We are then given a review of the selected tables before they are imported into the database. The table attributes and types are shown to verify the correct tables have been selected, below.

Data Type Review

Clicking Next and Finish, we now have the tables in the POWER database. The Object Explorer below reflects the newly imported tables.

Object Explorer with New Tables

And with the new query window open, we can now query tables that we require!

Blank Query Window

That concludes this post on how to import data into a database in SQL Server Management Studio 2012. Future posts will include SQL querying and use of the SSMS Analysis Services to analyze the data. Also, I will include a post on using R to connect to SQL Server to retrieve tables. Please look forward to the new posts!

Thanks for reading!

Wayne

P4.4: Data Modeling in PowerPivot: Relationship JOIN Types

Hello Readers,

In this post we will continue our discussion (from P4.3) on the types of JOINS we can use when creating relationships, especially through SQL queries. As usual, we will be using the AdventureWorks dataset from the Ferrari book. Let us begin.

JOINS

When we join two tables together, we have to determine what we want in the resulting table. Whether we need all of the rows in a specific table or just the matches in both, specific joins enable us to manipulate the merging of two tables in different ways. Subsequently, the left and right tables we choose have positional distinctions when we select a specific type of join method. 

Detailed below are the different types of joins. (Diagrams from stackoverflow.)

INNER JOIN

When two tables are joined using an inner join, only the rows in table A which match the related rows in table B will be included in the resulting table. This is why in the previous post, P4.4, the joining of DimProduct with 606 entries and DimProductSubcategory tables yielded only 397 entries. There were only 397 product rows which had both an EnglishProductCategoryName and EnglishProductSubcategoryName.

LEFT OUTER JOIN

The left join is similar, but includes the all the entries in table A, and records NULL for entries in table A with no corresponding entries in table B.

RIGHT OUTER JOIN

The right outer join is the mirror image of the left outer join. All entries in Table B will be included, and those rows without a corresponding row in Table A will show NULL.

FULL OUTER JOIN

Next is the full outer join where both Tables A and B are preserved, with entries in Table A with none in B, and entries in Table B without entries in A will show as NULL.

LEFT EXCLUDING JOIN

Using left exclusion join we can modify the join to yield only entries in Table A which do not match in the right Table B.

RIGHT EXCLUDING JOIN

Likewise with a right excluding join, the all entries in Table will be included except for those which have corresponding entries relative to entries in Table A.


Here is a helpful link!


Thanks for reading,


Wayne Liu

R: Regex Telephone Number Matching

Hello Readers,


Firstly, here is an encouraging message from President Obama, calling for more students to learn code! Hooray, SCIENCE!


Now back to the post. Here we shall discuss using regular expressions to match North American telephone numbers in RegExr. For matching text from HTML5, check out this post.

Usual telephone numbers include a three digit area code followed by a seven digit number sequence, which can be separated by hyphen, period or even spaces. Sometimes the area code can be enclosed in parenthesis as well. So how do we match and extract the numbers that we require? So we turn to the flexibility of Regular Expressions to describe the target string we want to match. We will be using RegExr program, found here. Let us get started.

RegExr

The picture of the RegExr window, above, shows a variety of telephone numbers, some separated with hyphens, some with periods, and others just with spaces. So how can we match the digits in the phone numbers? To demonstrate, we can first use:

1. Literal Strings

  By using the actual numbers, we can manually match the telephone numbers. For example, if we type 498, then RegExpr matches the sequence of digits 498 twice, as shown below.

RegExpr Matching 498

2. Shorthand

  By using the power of regular expressions, we can incorporate shorthand, which is a special type of character. A backlash lowercase d denotes any digit. The opposite is true when the d is capitalized to D, as any character that is not a digit is matched, such as hyphens, periods, parenthesis, etc. Both are shown below.

Matches Any Digit- Shorthand

And the reverse (conveniently the capitalized letter D):

Matches Any Non-Digit- Shorthand

3. Quantifiers

Since telephone numbers follow a pattern of 3 digits, 3 digits, and 4 digits, we can tell the match to loop for a certain amount of times by using a quantifier. For example, using curly brackets {n} to surround a number after a regular expression will instruct RegExr to match that expression n times. This is shown below, as \d (any number) is matched exactly three times. Note how in the last set of 4 digits, only the first 3 are matched.

Shorthand Matching with a Quantifier

To match the set of 4 numbers as well, we can use {n1, n2} as a quantifier where RegExr will match expression from the range n1 to n2. Additionally, the quantifier ? matches 0 or 1 of the preceding token, whereas the + will match 1 or more. The asterisk * will match 0 or more times.

More Quantifiers- It Works!

The ? after both parenthesis allow them to match when they are and are not there, so it will match the area code as well as the other digits in the phone number. The digit sets are set to match {3, 4} three to four times, and the optional period will match any character, whether it be a hyphen, period, or white space. Then the whole expression has to match at least once or more with the + sign.

4. Putting It Together


However, there are many different ways to match a specific string. Below is another method which uses anchors.

Simply put, anchors do not match a specific character in the string, it matches the position. So while we can use the carrot sign, ^, to match the beginning of a line, it will not return any value without an expression. Likewise, the $ matches the end of a line.

See how the ^ and the $ constrain the first set of tokens to match the area code with or without the parenthesis at the start of the line, and that the last set of 4 digits have to end at the end of the line. The non-digits are matched with the \D token.

Another Method Using Anchors

Now we have seen the power and flexibility of regular expressions and how it can match numbers and different symbols. They will be very important when analyzing text data as well, especially making sense of data in HTML5 format.

We will continue to solve more regular expression problems in future posts. Stay tuned!
As always, thanks for reading,


Wayne

P4.3: Data Modeling in PowerPivot: SQL Query Denormalization

Hello Readers,

This post is a continuation from the last post of understanding normalization in data modeling using PowerPivot. As usual, we will be using the AdventureWorks companion material from the Ferrari book. Let us get started.

SQL Querying in PowerPivot

In the previous post I demonstrated how to denormalize a table in PowerPivot by creating calculated columns using the RELATED command. The RELATED command is an easy tool to understand denormalization. However, with SQL queries we can normalize with ease in a large database. Here we will query in SQL the necessary columns and tables to denormalize a data model. The denormalization process will be shifted outside of PowerPivot versus the previous method of denormalizing within PowerPivot using the RELATED command.

A SQL query is a statement written in SQL language used in many modern relational databases. PowerPivot has a built-in SQL query designer, of which we will discuss further.

Begin by adding AdventureWorks data from SQL Server. In the Home tab of PowerPivot, click from Database and From SQL Server option.

Fig. 1: Add External Data from SQL Server in PowerPivot

The Table Import Wizard will appear. Type in the Server name and select the Database name from the drop-down menu. In this case, the AdventureWorks tables are located in the POWER Database.

Fig. 2: Connecting to SQL Server Database

After a connection is established, select Write a query because we will demonstrate how to use SQL queries to denormalize a data model.

Fig. 3: Use Query Method

Next we are given the option to enter the SQL query. Knowing SQL, we can type in:

SELECT
  DimProduct.ProductKey
  ,DimProductCategory.EnglishProductCategoryName
  ,DimProductSubcategory.EnglishProductSubcategoryName
FROM
  DimProductSubcategory
  INNER JOIN DimProductCategory
  ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
  INNER JOIN DimProduct
  ON DimProductSubcategory.ProductSubcategoryKey = DimProduct.ProductSubcategoryKey

But for now, click the Design button as show below in Figure 4 to use the SQL query designer.

Fig. 4: Select PowerPivot Query Designer

With the SQL query Designer open, select the ProductKey from DimProduct, EnglishProductCategoryName from DimProductCategory, and EnglishProductSubcategoryName from DimProductSubcategory.

Fig. 5:  SQL Query Designer

In the Relationships section, the DimProductSubcategory has the Primary Keys to the categories and subcategories to the DimProduct and DimProductCategory tables. This illustrates the one-to-many relationship, indicated by the left and right tables. The Join Fields are shown in Figure 5 as well. Now click OK, and we get SQL query statement designed by the point and click SQL query designer (Figure 6.)

Fig. 6: SQL Query Statement from SQL Query Designer

Hitting Finish, the Table Import Wizard implements the query statement and imports the specified table into the PowerPivot database.

Fig. 7: Query Table

Now we have denormalized a data model by using SQL queries to retrieve columns from different tables into one table. Note that 397 rows were imported. This is less than the 606 rows in the DimProduct. Where did the other 209 rows go? The 397 rows from the query is a result of the type of relationship between the tables, in this case is an INNER JOIN, which only returns rows with matching values in both tables. The next post will discuss the different types of JOINS.


Thanks for reading,

Wayne

P4.2: Data Modeling in PowerPivot: Normalization and Denormalization

Hi Readers, 
Today we will discuss normalization and denormalization of tables in data models. As usual, we will being using the companion material from the Ferrari book. We will be continuing on the data model from the previous post. Let us get started.

Normalization

In the previous post we discussed relationships between tables in the data model with products, product categories, and product subcategories. That particular data model is considered to be normalized. What does it mean to be normalized? Instead of having one detailed table with all the category and subcategory information for each product, the category and subcategory information is referenced in their own tables. This way, the categories and subcategories are not repeated multiple times with each product and allow for efficient data storage. Normalization increases the number of tables, but decreases the overall size of the database by removing redundancies.

Fig. 1: Normalized Product Data Model

Inside the DimProduct table, instead of a product subcategory column listing the subcategories, it has numbers referencing the DimProductSubcategory table. Below in Figure 2, we see that the subcategories are only listed once (which is why the ProductSubcategoryKey column must be an unique Primary Key.) This reduces redundancy of listing the each subcategory multiple times for the products in the DimProduct table.

Fig. 2: DimProductSubcategory Table

Although another table (DimProductSubcategory) was created by normalizing, it occupies less overall physical hard drive space. However, viewing the product data model information as a set of normalized tables from the user perspective is difficult and not logical. Once a database is normalized for storage, the required data must be denormalized for readability when retrieved to be analyzed.

Denormalizing (Reducing Normalization)

For this product data model, we can easily create calculated columns for the product categories and subcategories in the product table and hide the product category and subcategory tables in the model. This would reduce the normalization, thus increasing the complexity and physical storage size of the product table.

In the DimProduct table in PowerPivot, create a calculated column for the ProductCategory using the DAX expression:

  =RELATED(DimProductCategory[EnglishProductName])

as shown below:

Fig. 3: RELATED DAX Expression for ProductCategory

And again for the ProductSubcategory column with the DAX expression:

  =RELATED(DimProductSubcategory[EnglishProductSubcategoryName])

again, shown below:

Fig. 4: RELATED DAX Expression for ProductSubcategory

Now the DimProduct table has decreased normalization and more redundancy, with product categories and subcategories being repeated multiple times, but is more readable for the user. To finish, we can hide the other two tables which are now not required since the relevant data is in our desired product table.

To hide the DimProductCategory and DimProductSubcategory tables, in the PowerPivot Diagram View, right-click the table and select Hide from Client Tools. Normalized tables which are needed for the data model, but can be safely hidden are called technical tables. 

Fig. 5: Hide from Client Tools, Table

Additionally, the ProductSubcategoryKey, as the Foreign Key in the DimProduct table, is now not required because the table has been denormalized with the actual subcategory column in the table. Yes, it can be hidden too (along with ProductKey and ProductAlternateKey.)

Fig. 6: Hide from Client Tools, Column

Hiding technical tables and technical columns, as well as removing normalization are important steps to improving the usability of a data model. Though relational databases use normalization,  denormalized tables offer ease for analysis and querying, while a normalized data model removes redundancy, occupies less disk space, and improves database operational performance.

Either option can be right or wrong depending on the scenario, and the operations we would like to perform on it. In the next post, I will discuss denormalizing with SQL queries.


Thanks for reading,


Wayne

P4.1: Data Modeling in PowerPivot: Relationships

When we wanted to merge two table together, an option would be to use the VLOOKUP function. To analyze the many tables in a data model, VLOOKUP is inefficient and is replaced with relationships, an 'automatic VLOOKUP', which allows us to link entire tables at a time instead of referencing single columns.

As usual, we will be using the Adventure Works companion material (Chapter 4) from the Ferrari book.

Creating the Data Model

Instead of using VLOOKUP multiple times to add columns into the DimProduct table, we will use relationships to merge all three tables together. Opening the PowerPivot window in Diagram View from the workbook we see that the relationships between the tables have already been formed, because PowerPivot detected existing relationships (will be elaborated in further posts.)

Fig. 1: PowerPivot Diagram View

The arrow lines are representations of relationships in Figure 1. The arrow starts at DimProduct, the source table and points to DimProductSubcategory, the target table, and the connection is finished as DimProductSubcategory is linked to the DimProductCategory table. In the measure field towards the bottom half in grid view of the DimProduct table, we can add a calculated field for the number of products, NumOfProducts. 

Fig. 2: Creating NumOfProducts Calculated Field

Now we can create a PivotTable tabulating the number of products for each ProductCategory from the DimProduct table. The ProductCategory is actually the EnglishProductCategoryName field in the DimProductCategory table, two relationships away, connected by the ProductSubcategoryKey and the ProductCategoryKey.

Fig. 3: ProductCategory PivotTable

Data models are much more efficient in performance compared to VLOOKUP, especially when dealing with large data sets for computational power, and in multiple tables for a chain of relationships. Also, the NumOfProduct field calculation is available for any PivotTable using the data model, so we do not have to create a new definition every time.

So a data model is a set a tables connected by relationships, sometimes with calculated fields or columns. Simple yet powerful.

Notes on Relationships

Though these relationships will not help much in your love life, the following details on PowerPivot relationships will make your life easier when creating and understanding data models.

Looking at the Diagram View of the tables and relationships, we focus on the relationship arrow from DimProduct to DimProductSubcategory.

• DimProduct is the source table (the beginning of the relationship)
• DimProductSubcategory is the target table (where the values relate to source table)
• ProductSubcategoryKey column is the Foreign Key in source table (contains values which is searched in target table for related row)
• ProductSubcategoryKey column is the Primary Key in the target table (needs to have unique values in each row)

These are shown highlighted in blue below:

Fig. 4: DimProduct and DimProductSubcategory Relationship

A specific product can only have one subcategory, whereas a subcategory may have many products. That is why the target table (DimProductSubcategory) must have an unique Primary Key (ProductSubcategory) for the products in the DimProduct table. This type of relationship is called "one-to-many", with the target table being "one side", and the source table being the "many side" of the relationship.

We can test the relationship by recreating it. Click the arrow and press Delete. Then in the Design tab, click Create Relationship.

Fig. 5: Creating a Relationship

In the drop-down boxes, we would want to specify the DimProduct and DimProductSubcategory as tables, with the ProductSubcategoryKey as columns from both. However, if we accidentally flip the tables, and confuse the "one-to-many" to "many-to-one" tables, PowerPivot automatically detects the columns in the tables and lets us know it will create the relationship in the opposite direction.

So now you know the inner workings of relationships in data models. Some DAX functions work only when invoked on the correct side of a relationship, so sides do matter. Stay tuned for more on data modeling!


Thanks for reading,

Wayne