EXAMPLE - Countpattern Transform

Source:

The dataset below contains fictitious tweet information shortly after the release of an application called, "Myco ExampleApp".

DatetwitterIdisEmployeetweet
11/5/15lawrencetlu38141FALSEJust downloaded Myco ExampleApp! Transforming data in 5 mins!
11/5/15petramktng024TRUETry Myco ExampleApp, our new free data wrangling app! See www.example.com.
11/5/15joetri221TRUEProud to announce the release of Myco ExampleApp, the free version of our enterprise product. Check it out at www.example.com.
11/5/15datadaemon994FALSEGreat start with Myco ExampleApp. Super easy to use, and actually fun.
11/5/1599redballoons99FALSELiking this new ExampleApp! Good job, guys!
11/5/15bigdatadan7182FALSE@support, how can I find example datasets for use with your product?

There are two areas of analysis:

  • For non-employees, you want to know if they are mentioning the new product by name.
  • For employees, you want to know if they are including cross-references to the web site as part of their tweet.

Transform:

The following counts the occurrences of the string ExampleApp in the tweet column. Note the use of the ignoreCase parameter to capture capitalization differences:

countpattern col:tweet on:'ExampleApp' ignoreCase:true

For non-employees, you want to track if they have mentioned the product in their tweet:

derive value:IF(isEmployee=='FALSE' && countpattern_tweet=='1',true,false) as:'nonEmployeeExampleAppMentions'

The following counts the occurrences of example.com in their tweets:

countpattern col:tweet on:'example.com' ignoreCase:true

For employees, you want to track if they included the above cross-reference in their tweets:

derive value:IF(isEmployee=='TRUE' && countpattern_tweet1 == 1, true, false) as:'employeeWebsiteCrossRefs'

Results:

After you drop the two columns tabulating the counts, you end up with the following:

DatetwitterIdisEmployeetweet employeeWebsiteCrossRefs nonEmployeeExampleAppMentions
11/5/15lawrencetlu38141FALSEJust downloaded Myco ExampleApp! Transforming data in 5 mins!falsetrue
11/5/15petramktng024TRUETry Myco ExampleApp, our new free data wrangling app! See www.example.com.truefalse
11/5/15joetri221TRUEProud to announce the release of Myco ExampleApp, the free version of our enterprise product. Check it out at www.example.com.truefalse
11/5/15datadaemon994FALSEGreat start with Myco ExampleApp. Super easy to use, and actually fun.falsetrue
11/5/1599redballoons99FALSELiking this new ExampleApp! Good job, guys!falsetrue
11/5/15bigdatadan7182FALSE@support, how can I find example datasets for use with your product?falsefalse
Was this page helpful? Let us know how we did:

Send feedback about...

Google Cloud Dataprep Documentation