Phase 2: Refining the solution

From: Developing big data solutions on Microsoft Azure HDInsight

After you find a question that does provide some useful insight from your data, it’s usual to attempt to refine it to maximize the relevance of the results, minimize the processing overhead, and therefore improve its value. This is generally the second phase of an analysis process.

Typically you will only refine solutions that you intend to repeat on a reasonably regular basis. Be sure that they are still correct and produce valid results after refinement. For example, compare the outputs of the original and the refined solutions.

Having determined that counting the number of occurrences of each word has the potential to reveal common topics of customer tweets, the analysts at Blue Yonder Airlines decided to refine the data processing solution to improve its accuracy and usefulness. The starting point for this refinement is the WordCount.pig Pig Latin script described in Phase 1: Initial data exploration.

Enhancing the Pig Latin code to exclude noise words

The results generated by the WordCount.pig script contain a large volume of everyday words such as a, the, of, and so on. These occur frequently in every tweet but provide little or no semantic value when trying to identify the most common topics discussed in the tweets. Additionally, there are some domain-specific words such as flight and airport that, within the context of discussions around airlines, are so common that their usefulness in identifying topics of conversation is minimal. These noise words (sometimes called stop words)**can be disregarded when analyzing the tweet content.

The analysts decided to create a file containing a list of words to be excluded from the analysis, and then use it to filter the results generated by the Pig Latin script. This is a common way to filter words out of searches and queries. An extract from the noise word file created by the analysts at Blue Yonder Airlines is shown below. This file was saved as noisewords.txt in the /twitterdata folder in the HDInsight cluster.

a
i
as
do
go
in
is
it
my
the
that
what
flight
airline

With the noise words file in place the analysts modified the WordCount.pig script to use a LEFT OUTER JOIN matching the words in the tweets with the words in the noise list, and to store the result in the file named noisewordcounts. Only words with no matching entry in the noise words file are now included in the aggregated results. The modified section of the script is shown below.

...

-- load the noise words file.
NoiseWords = LOAD '/twitterdata/noisewords.txt' AS noiseword:chararray;

-- join the noise words file using a left outer join.
JoinedWords = JOIN FilteredWords BY word LEFT OUTER, NoiseWords BY noiseword USING 'replicated';

-- filter the joined words so that only words with 
-- no matching noise word remain.
UsefulWords = FILTER JoinedWords BY noiseword IS NULL;

...

Partial results from this version of the script are shown below.

delayed       2749
entertainment 2064
delay         1720
bags          1718
service       1718
time          1375
vacation      1031
food          1030
wifi          1030
connection    688
seattle       688
bag           687

These results are more useful than the previous output, which included the noise words. However, the analysts have noticed that semantically equivalent words are counted separately. For example, in the results shown above, delayed and delay both indicate that customers are concerned about delays, while bags and bag both indicate concerns about baggage.

Using a user-defined function to find similar words

After some consideration, the analysts realized that multiple words with the same stem (for example delayed and delay), different words with the same meaning (for example baggage and luggage), and mistyped or misspelled words (for example bagage) will skew the results—potentially causing topics that are important to customers to be obscured because many differing terms are used as synonyms. To address this problem, the analysts initially decided to try to find similar words by calculating the Jaro distance between every word combination in the source data.

The Jaro distance is a value between 0 and 1 used in statistical analysis to indicate the level of similarity between two words. For more information about the algorithm used to calculate Jaro distance, see Jaro–Winkler distance on Wikipedia.

There is no built-in Pig function to calculate Jaro distance, so the analysts recruited the help of a Java developer to create a user-defined function. The code for the user-defined function is shown below.

package WordDistanceUDF;
...

public class WordDistance extends EvalFunc<Double>
{
  public Double exec(final Tuple input) throws IOException{
    if (input == null || input.size() == 0) return null;
    try
    {
      final String firstWord = (String)input.get(0);
      final String secondWord = (String)input.get(1);
      return getJaroSimilarity(firstWord, secondWord);
    }
    catch(Exception e)
    {
      throw new IOException("Caught exception processing input row ", e);
    }
  }

  private Double getJaroSimilarity(final String firstWord, final String secondWord)
  {
    double defMismatchScore = 0.0;
    if (firstWord != null && secondWord != null)
    {
      // get half the length of the string rounded up
      //(this is the distance used for acceptable transpositions)
      final int halflen = Math.min(firstWord.length(), secondWord.length()) / 2 + 1;
      //get common characters
      final StringBuilder comChars = getCommonCharacters(firstWord, secondWord, halflen);

      final int commonMatches = comChars.length();

      //check for zero in common
      if (commonMatches == 0) { return defMismatchScore; }

      final StringBuilder comCharsRev = getCommonCharacters(secondWord, firstWord, halflen);
      //check for same length common strings returning 0.0f is not the same
      if (commonMatches != comCharsRev.length()) { return defMismatchScore; }

      //get the number of transpositions
      int transpositions = 0;
      for (int i = 0; i < commonMatches; i++)
      {
        if (comChars.charAt(i) != comCharsRev.charAt(i)) { transpositions++; }
      }

      //calculate jaro metric
      transpositions = transpositions / 2;
      defMismatchScore = commonMatches / (3.0 * firstWord.length()) + 
      commonMatches / (3.0 * secondWord.length()) +
      (commonMatches - transpositions) / (3.0 * commonMatches);
    }  
    return defMismatchScore;
  }

  private static StringBuilder getCommonCharacters(final String firstWord,
                             final String secondWord, final int distanceSep)
  {
    if (firstWord != null && secondWord != null)
    {
      final StringBuilder returnCommons = new StringBuilder();
      final StringBuilder copy = new StringBuilder(secondWord);
      for (int i = 0; i < firstWord.length(); i++)         
      {
        final char character = firstWord.charAt(i);
        boolean foundIt = false;
        for (int j = Math.max(0, i - distanceSep);
         !foundIt && j < Math.min(i + distanceSep, secondWord.length()); 
         j++)
        {
          if (copy.charAt(j) == character)
          {
             foundIt = true;
             returnCommons.append(character);
             copy.setCharAt(j, '#');
          }
        }
      }
      return returnCommons;
    }
    return null;
  }
}

This function was compiled, packaged as WordDistanceUDF.jar, and saved on the HDInsight cluster. Next, the analysts modified the Pig Latin script that generates a list of all non-noise word combinations in the tweet source data to use the function to calculate the Jaro distance between each combination of words generated by the script. This modified section of the script is shown here.

-- register custom jar.
REGISTER WordDistanceUDF.jar;

...
...

-- sort by count.
SortedWords = ORDER WordList BY word;

-- create a duplicate set.
SortedWords2 = FOREACH SortedWords GENERATE word AS word:chararray;

-- cross join to create every combination of pairs.
CrossWords = CROSS SortedWords, SortedWords2;

-- find the Jaro distance.
WordDistances = FOREACH CrossWords GENERATE 
    SortedWords::word as word1:chararray, 
    SortedWords2::word as word2:chararray, 
    WordDistanceUDF.WordDistance(SortedWords::word, SortedWords2::word) AS jarodistance:double;

-- filter out word pairs with jaro distance less than 0.9.
MatchedWords = FILTER WordDistances BY jarodistance >= 0.9;

-- store the results as a file.
STORE MatchedWords INTO '/twitterdata/matchedwords';

The results include a row for every word matched to itself with a Jaro score of 1.0, and two rows for each combination of words with a score of 0.9 or above (one row for each possible word order). Some of the results in the output file are shown below.

bag      bag      1.0
bag      bags     0.9166666666666665
baggage  baggage  1.0
baggage  bagage   1.0
bags     bag      0.9166666666666665
bags     bags     1.0
delay    delay    1.0
delay    delayed  0.9047619047619047
delay    delays   0.9444444444444444
delayed  delay    0.9047619047619047
delayed  delayed  1.0
delays   delay    0.9444444444444444
delays   delays   1.0
seat     seat     1.0
seat     seats    0.9333333333333333
seated   seated   1.0
seats    seat     0.9333333333333333
seats    seat     1.0

Close examination of these results reveals that, while the code has successfully matched some words appropriately (for example, bag/bags, delay/delays, delay/delayed, and seat/seats), it has failed to match some others (for example, delays/delayed, bag/baggage, bagage/baggage, and seat/seated).

The analysts experimented with the Jaro value used to filter the results, lowering it to achieve more matches. However, in doing so they found that the number of false positives increased. For example, lowering the filter score to 0.85 matched delays to delayed and seats to seated, but also matched seated to seattle.

Using a lookup file to combine matched words

The results generated by the user-defined function were not sufficiently accurate to rely on in a fully automated process for matching words, but they did provide a useful starting point for creating a custom lookup file for words that should be considered synonyms when analyzing the tweet contents. The analysts reviewed and extended the results produced by the function and created a tab-delimited text file, which was saved as synonyms.txt in the /twitterdata folder on the HDInsight cluster. The following is an extract from this file.

bag          bag
bag          bags
bag          bagage
bag          baggage
bag          luggage
bag          lugage
delay        delay
delay        delayed
delay        delays
drink        drink
drink        drinks
drink        drinking
drink        beverage
drink        beverages
...

The first column in this file contains the list of leading values that should be used to aggregate the results. The second column contains synonyms that should be converted to the leading values for aggregation.

With this synonyms file in place, the Pig Latin script used to count the words in the tweet contents was modified to use a LEFT OUTER JOIN between the words in the source tweets (after filtering out the noise words) and the words in the synonyms file to find the leading values for each matched word. A UNION clause is then used to combine the matched words with words that are not present in the synonyms file, and the results are saved into a file named synonymcounts. The modified section of the Pig Latin script is shown here.

...

-- Match synonyms.
Synonyms = LOAD '/twitterdata/synonyms.txt' AS (leadingvalue:chararray, synonym:chararray);
WordsAndSynonyms = JOIN UsefulWords BY word LEFT OUTER, Synonyms BY synonym USING 'replicated';
UnmatchedWords = FILTER WordsAndSynonyms BY synonym IS NULL;
UnmatchedWordList = FOREACH UnmatchedWords GENERATE word;
MatchedWords = FILTER WordsAndSynonyms BY synonym IS NOT NULL;
MatchedWordList = FOREACH MatchedWords GENERATE leadingvalue as word;
AllWords = UNION  MatchedWordList, UnmatchedWordList;

-- group by word.
GroupedWords = GROUP AllWords BY (word);

-- count mentions per group.
CountedWords = FOREACH GroupedWords GENERATE group as word, COUNT(AllWords) as count;
...

Partial results from this script are shown below.

delay         4812
bag           3777
seat          2404
service       1718
movie         1376
vacation      1375
time          1375
entertainment 1032
food          1030
wifi          1030
connection    688
seattle       688
drink         687

In these results, semantically equivalent words are combined into a single leading value for aggregation. For example the counts for seat, seats, and seated are combined as a single count for seat. This makes the results more useful in terms of identifying topics that are important to customers. For example, it is apparent from these results that the top three subjects that customers have tweeted about are delays, bags, and seats.

Moving from topic lists to sentiment analysis

At this point the team has a valuable and seemingly valid list of the most important topics that customers are discussing. However, the results do not include any indication of sentiment. While it’s reasonably safe to assume that the majority of people discussing delays (the top term) will not be happy customers, it’s more difficult to detect the sentiment for a top item such as bag or seat. It’s not possible to tell from this data if most customers are happy or are complaining.

Sentiment analysis is difficult. As an extreme example, tweets containing text such as “No wifi, thanks for that Blue Yonder!” or “Still in Chicago airport due to flight delays - what a great trip this is turning out to be!” are obviously negative to a human reader who appreciates sentiment modifiers such as satire. However, a simple analysis based on individual words that indicate positive and negative sentiment, such as thanks and great, would indicate overall positive sentiment.

There are solutions available that are specially designed to accomplish sentiment analysis, and the team might also investigate using the rudimentary sentiment analysis capabilities of the Twitter search API. However, they decided that the list of topics provided by the analysis they’ve done so far would probably be better employed in driving more focused customer research, such as targeted questionnaires and online surveys—perhaps through external agencies that specialize in this field.

Instead, the team decided to move forward by improving the stability of the current solution as described in Phase 3: Stabilizing the solution.

Next Topic | Previous Topic | Home | Community