topic Re: Challenge #56: Parsing and Counting Hastags in Weekly Challenges

Challenge #56: Parsing and Counting Hashtags

JoeM — Tue, 13 Sep 2022 19:04:58 GMT

Last week's solution can be found HERE.

This week's challenge, by its title, sounds like a simple task. However, a quick look at the data will show that it's not going to be just a summarize tool. If you have been meaning to finally learn the RegEx tool, this exercise will be a great first foray with a relatively simple expression. In this exercise, we will take a set of very ugly data, parse it to find hashtags within a text field, find out how many times the hashtag was written, and what users used the hashtag.

If you would like to learn more about our RegEx Tool - check out our help on the tool. If you would like a simpler RegEx overview, check this interactive learning out.

Good luck!

Re: Challenge #56: Parsing and Counting Hastags

mceleavey — Mon, 06 Feb 2017 17:02:02 GMT

I'm not sure the results provided are correct as they show a #followme result against id_str_13, but that text does not contain a hashtag, unless I'm missing something?

That aside, my solution is as follows:

I first created a RecordID field, then used Regex to parse the text field using the following:

#.*?\>

This splits the text by a hashtag and the end of the attached word. I used split to rows to provide a row per hashtag. I then filtered out the null rows, summarised and provided a count of the hashtags per id_str and used crosstab to pivot the data.
I then replaced nulls with zeros using the "Data Cleansing" tool and created the total.

Re: Challenge #56: Parsing and Counting Hastags

MarqueeCrew — Mon, 06 Feb 2017 17:21:22 GMT

Of course I wanted to solve this without the use of RegEx (just being difficult). I don't agree that id_str_13 should have any counts.

Re: Challenge #56: Parsing and Counting Hastags

JoeM — Mon, 06 Feb 2017 19:12:11 GMT

Good catch @mceleavey and @MarqueeCrew, I have updated the start file to have the correct expected results.

Re: Challenge #56: Parsing and Counting Hastags

Max06270 — Mon, 06 Feb 2017 22:13:16 GMT

Nice challenge, have a great week guys!

Re: Challenge #56: Parsing and Counting Hashtags

Harbinger — Thu, 09 Feb 2017 02:18:46 GMT

I used \#[a-zA-Z\d]+ to grab and tokenize into rows. Biggest difference is I am using the multi-field formula tool to rename and to return binary results for hashtag/string flags. this is in contrast to a formula and dynamic rename. As always, there's more than one way to shine a penny!

I have attached my work for a deep dive-- fun stuff!

Re: Challenge #56: Parsing and Counting Hastags

jury_maggay — Mon, 13 Feb 2017 09:55:09 GMT

Hello,

I would like to know how the expression #.*?\> works. I was also trying to solve the challenge using RegEx tool but can't seem to figure out the correct expression to use. Appreciate your feedback on this :-)

Re: Challenge #56: Parsing and Counting Hastags

mceleavey — Mon, 13 Feb 2017 10:01:01 GMT

In regex the .*? part simply means "take everything in between". From the dropdown you'll see a list of Regex tools that allow you to then construct things like that seen in the example.

The # here, followed by the .*? simply means take everything from the # symbol to...

I then used the \> which means "the end of the word".

So, in speech terms, it literally translates to "take everything from the # symbol to the end of the word".

Regex really isn't as scary as it first appears. I myself had no Regex experience before using Alteryx but it's really easy once you get your head around it.

Try experimenting with the dropdown list:

Feel free to message me if you have any questions or if you have a specific issue with which you're struggling.

Re: Challenge #56: Parsing and Counting Hastags

jury_maggay — Mon, 13 Feb 2017 10:08:22 GMT

Thank you so much for the explanation. I will surely reach out to you guys here in the forum should I need help on anything about Alteryx. Thanks

Re: Challenge #56: Parsing and Counting Hastags

Harbinger — Mon, 13 Feb 2017 14:36:54 GMT

Also, a great reference for experimentation is regexr.com. You can paste your own test text in the text box and get real-time feedback on how your regular expression evaluates on that text.

Re: Challenge #56: Parsing and Counting Hastags

mceleavey — Mon, 13 Feb 2017 14:39:08 GMT

Good call.

Seconded.

Re: Challenge #56: Parsing and Counting Hastags

JoeM — Mon, 13 Feb 2017 15:47:35 GMT

The solution for Challenge #56 is now posted! It's great to see the many way this was solved!

Re: Challenge #56: Parsing and Counting Hastags

SeanAdams — Wed, 05 Apr 2017 17:22:34 GMT

kinda a mix between a few of the solutions posted above.

I used the text-to-column to split at the hash mark into rows, so that it can work over any number of hashtags in the text (probably a smarter way to do this using a tokenize in RegEx, but this was dead quick
Then regexed out the hashtag
then summarized it all up using a crosstab and a dynamic rename with formula

Re: Challenge #56: Parsing and Counting Hashtags

NicoleJohnson — Thu, 13 Apr 2017 20:11:28 GMT

My solution! :) [Edited to actually include the solution now... oops.]

Re: Challenge #56: Parsing and Counting Hashtags

estherb47 — Tue, 09 May 2017 00:48:22 GMT

another fun one!

Spoiler

Re: Challenge #56: Parsing and Counting Hashtags

Natasha — Wed, 10 May 2017 13:39:07 GMT

I don't use regex a lot, so got stuck for quite some time on rows with several hashtags.

Re: Challenge #56: Parsing and Counting Hashtags

LordNeilLord — Fri, 02 Jun 2017 16:51:23 GMT

I spent ages trying to get the RegEx right but once I had conquered that the rest was plain sailing:

Re: Challenge #56: Parsing and Counting Hastags

nick_ceneviva — Fri, 18 Aug 2017 13:33:35 GMT

Solution Attached

Re: Challenge #56: Parsing and Counting Hashtags

jamielaird — Sat, 09 Sep 2017 16:23:40 GMT

Here's my solution.

Re: Challenge #56: Parsing and Counting Hashtags

patrick_digan — Fri, 22 Sep 2017 20:10:09 GMT