Challenge #89: Analyzing Social Data

A solution to last week's Challenge has been posted HERE!

This week’s Challenge is a bit different from the norm. This week, you’ll pick your own Challenge!

We’ve gathered a week’s worth of Twitter data using 10 different hashtags that relate to the most recent meeting of the United Nations General Assembly. This meeting brought world leaders and diplomats together to discuss a variety of humanitarian concerns which, as you can imagine, generated engagement across social media networks like Twitter. For this challenge, we collected the most recent data on #hashtags related to the UN’s Sustainable Development Goals.

We’re curious to see what kinds of insights you, the Alteryx Community, derive from the datasets. Is your Challenge simply to cleanse and organize the data? Awesome! Is your Challenge to apply your knowledge of social media analysis to this dataset? Great! Is your Challenge to create an app to allow an end user to pull the relevant tweets by date or keyword? That’s fantastic! We’re excited to see how you Challenge yourself this week.

As a heads up: this week’s Challenge is only the beginning of our data mining adventure! This week, your Challenge is to prepare your data in any way, shape or form for next week’s Challenge: visualizing the data in a platform of your choice (Tableau, Alteryx, Plotly, GIF, etc). We look forward to seeing your data manipulation prowess on display this week, and your creative ways to visualize data the next week!

When you're ready, share your unique "solution" by posting your workflow and describing what your next steps will be for visually displaying your findings.

E_Logo_No UN Emblem-01.png

challenge_89_start_file.yxzp

challenge_89_christine_solution.yxzp

Parse

Apps

Join

Preparation

Advanced

Data Analysis

Intermediate

Data Preparation

Transform

Interface

Accepted answers

All comments

LordNeilLord

Hi @ChristineB,

The input files aren't working, can package the workflow up?

ChristineB

Thanks to everyone who alerted me that the input files were missing! My Challenge for this week: successfully sharing input data in a workflow Now, it's your turn!

Joe_Mako

The attached package has .yxdb files that were generated for the same keywords using the Alteryx Twitter Search app from the Gallery at https://gallery.alteryx.com/#!app/Twitter-Search/574db168a248970bdc36d527

The downside is the attached data files are from a different time frame, since the free API only searches 7 days back. The upside is no data loss issues in the ID or Unicode text.

challenge_89_start_file_yxdb.yxzp

vishalgupta

Standardised city and country using Wikipedia lists and created simple report on top trending hashtags, most re tweeted tweet and comparison on number of tweets from different countries/cities

challenge_89_Vishal.yxzp

patrick_digan

Spoiler

I cleaned up the data by using only one input tool, parsing out the hashtags, converting the date/time to Alteryx, removing nulls and bad characters (?), and summarizing the hashtags by input file.

challenge_89_start_file.yxmd

SeanAdams

Did some very simple data cleansing, and then used Tableau to look for patterns in the data.

Focussed on tweets over time (time of day, and retweets over time); and on the hashtags that got to the most followers

With this one - now caught up with the challenges; and with the leaders ( @NicoleJohnson ; @patrick_digan ; @estherb47 ; @LordNeilLord - and @nick_ceneviva is catching up very fast!)

Spoiler

challenge_89_SeanSolution.yxmd

MarqueeCrew

@patrick_digan I will try to get to this one later... @MarqueeCrew

NicoleJohnson

I went for the "sustainability is a form of artistic expression" route, and channeled King Triton in my workflow design (although lets be honest, Ariel was really the one with the penchant for reducing-reusing-recycling with her stash of treasure...). At any rate, after a little data cleanup I prepared a few branches illustrating the type of insights I expect to include in Weekly Challenge #90, but also sent the results to a .yxdb file for easy visualizing next week. Thanks for starting us on such an interesting challenge, @ChristineB - fun to be a little more creative this week!

Spoiler

Data cleanup included: Date formatting, Parsing the hashtags and then grouping by all fields to remove duplicate entries (based on information from the same tweet potentially appearing in more than one source file).

Possible insights: Top 5 Tweets based on # of Retweets, Top 10 Hashtags by order of popularity based on # Tweets, Top 5 Favorite Tweets, Most Active Tweeter, and Most Active Retweeter.

ariel quote.PNG

Cheers,

challenge_89_NicoleJohnson.yxmd

Kenda

Spoiler

I looked at which hashtag was used the most, which day had the most hashtags, and which person used the most hashtags.

challenge 89.PNG

challenge_89_BarnesK.yxzp

Simona

I might have missed some cleansing with this, but I guess I will realise it better when I will plug the data into Tableau - most likely tomorrow

I think it is an interesting challenge as at least myself I did not deal often with social data and they indeed have a peculiar structure!

Below my solution, I think I got them ready to visualise and analyse

Spoiler

First observation, I wonder why I did not just use 1 input tool, I guess I was lazy ahah.

I used regex for a couple of columns (hashtag and tweet) to remove some bad characters, removed null/ empty hashtags and also made sure to tag including the filename in each input so that I could know at the end where each row was coming from. I ended up grouping on some fields I think I will be interested to analyse.

challenge_89_start_file.yxmd

JefBus

Ok, this is my first submission for the Weekly Challenges,

I Guess mine is not as squeaky clean as most, I was doing some generic calcs and ETL for conventional charting, and suddenly realized this might be a very interesting challenge to try out Alteryx's network analysis tool, so from there, i got to this point ( the basic ETL are the orphaned flows on top) So started iterating and testing little other blocks, and got kind of messy, just like a kid having funn with paint

The Idea: visualize who were the primary influencers,

The solution, once visualised, is not that super handy, as i run into trouble graphing every tweet, so here i limited it to the first 1500 records, sorted chronologically.

The last pic is a Data-Dandelion of social importance

Chirag09goa

This is my first ever post on this community and second challenge since signing up yesterday

SeanAdams

Welcome Chirag - first post of many I hope!

LordNeilLord

I kept it simple

Spoiler

challenge_89_LNL.yxmd

estherb47

Playing catchup.....

I decided to focus my analysis on time and influencers. So, cleaned up the time and date fields to be actual time and date format, and then combined them into one field.

Created a new field to determine who was being retweeted, and then counted the number of times each was retweeted (higher number = more of an influencer).

Finally, parsed the unique tweets into rows based on hashtags for a hashtag analysis. Outputs all go into .tde format to be fed into a Tableau dashboard.

challenge_89_EHB_solution.yxmd

estherb47

Updated my workflow with a little fuzzy matching to normalize at least some of the hashtags

challenge_89_EHB_solution.yxmd

LandonG

Solution attached.

challenge_89_start_file.yxzp

philipmannering

Solution attached.

Spoiler

challenge_89 - Sustainable Development Goals.yxmd

ggruccio

I chose to focus on other unique hashtags per record - exported to .tde for Tableau dashboard.

challenge_89_gg_finish.yxzp

samN

Went pretty simple on this one

challenge_89.yxmd

jamielaird

I wanted to do some text analysis so I thought it would be interesting to focus in on the originally-authored tweets, to deal with duplication of tweets caused by RTs.

I built a data prep workflow that excludes RTs, and outputs two TDE files for use in Tableau: the first to use for a wordcloud, with tweet text parsed and prepared, and the second to use for applying filters to that wordcloud (i.e. the date, dataset, and other tweet-level metadata).

Spoiler

UPDATE: Better handling of non-words.

challenge_89_solution_JL.yxzp

jasperlch

Solution attached.

challenge_89_JL.yxzp

Natasha

Very minimal data prep, as I wanted to preserve as much as possible for exploring data in Tableau

Spoiler

challenge_89_Natasha.yxmd

TeePee

I'm stuck in a loop and have a very specific question. Hope someone can help. Of course, if my question is not OK, please feel free to remove!

Spoiler

I'm familiar with Twitter message contents (the "Tweet" field) containing non-ASCII characters and usually get around this in Excel by using UTF-8 encoding. However, in Alteryx nothing I've tried is working. I've tried both:
(1): (csv input option 11) Code Page = UTF-8 and (csv input option 7) Field Length = 254
and
(2) (formula tool): ConvertToCodePage([Tweet], 65001)

In both cases I STILL see lots of question marks.

My question is just this: have these non-standard characters irreversibly lost their meaning in the csv files provided? OR am I doing something wrong? Has anyone here managed to convert the question marks to kanji or emoji or whatever it is they were originally meant to be?

Thanks in advance

MikeSp

Hello @TeePee

Spoiler

I can confirm that the CSV files do not have the proper encoding to be able to read these wide characters back in, I'm afraid. These are typically emojis or other wide characters as you suspect.

kcgreen

I noticed many tweets had a "We" phrase such as "We must...." and "We need...." I pulled out the we phrases and did some counts a few different ways. I could spend hours playing with this but for Challenge #90, I'm going to just make a word cloud to show some of the more popular "We" phrases in the data..

Spoiler

challenge_89_start_file.yxmd

Elena_Caric

That was fun, gave the Charting Tool a go. May bring over to Tableau later

challenge_89_start_file.yxmd

JosephSerpis

Challenge Completed

Challenge_89_Joe_Serpis.yxmd

Ozzy_Campos

Definitely enjoyed this one, cleaned the data, removed '?', and did some sorting based on favorite original tweets and counting hastags. Definitely a lot more that could be done within tweets themselves to do analysis, and lots of chaos to figure out. Sustainable Development goals played a huge part in all the tweets, and certain countries showed up more than others.

challenge_89_Ozzy_Campos.yxmd

kelly_gilbert

Here's my solution for week #89. I found a few interesting issues while exploring the data (in the spoiler tag).

For next week (#90): I'm going to challenge myself to use Alteryx's reporting tools, so I'm going to keep the analysis pretty basic. I'm going to look at:

The frequency of each hashtag
The count of distinct users using each hashtag
The timing (did the hashtags peak at different times?)

Spoiler

Some findings from exploration in week 89:

1 - if a tweet had multiple hashtags, the tweet may be duplicated across the files. The ID field is unique.
2 - all of the csv files have the same schema.
3 - hashtags may not always be capitalized in the same way; you may want to convert to all upper or all lowercase if using case-sensitive formulas/tools
4 - the Tweet field is sometimes truncated, and in some cases, the hashtags were cut off. If the hashtag does not appear in the Tweet field, then it also does not appear in the Hashtag field. As a result, sometimes the Hashtag field is null.
5 -since we know the tweets were harvested based on hashtag, then we know that every tweet in the file should contain that file's hashtag. For example, every tweet in the 'globalgoals' file should contain the #globalgoals hashtag. We can rebuild the Hashtag field to include the 10 hashtags of interest, but if any other hashtags were truncated, we don't know about them.

Here is an example. This tweet (ID# 914489241266278000) does have the #act4sdgs hashtag, but it is truncated from the Tweet field and thus not present in the Hashtag field in the act4sdgs csv file.

challenge_89_solution_klg.yxzp

danilang

Simple data clean up

Spoiler

challenge_89_solution_dl.yxmd

kat

Spoiler

Challenge #89.yxzp

Vinutha

Kept it simple. Just data cleansing ans summarizing. Can also show tweets by region, unique users, etc.

challenge_89_start_file_Solved.yxmd

DavidP

I kept mine pretty simple - will decide how to summarize, sort and sample the data in the next challenge.

1. Parsed the hashtags to rows

2. Combined date and time

3. Replaced all null hashtags with filename

4. Changed all hashtags to the same case

5. Filtered out where hashtags contain ?

6. Removed dups based on ID, Tweet and Hashtag

challenge_89_DavidP.yxmd

dsmdavid

I wanted to use the cognitive service analytics tool, but it seems Azure services are no longer free, so, a chance of brushing my rusty python and use the new tool

Spoiler

First bringing all the tweets into a yxdb file:

Then a bit of processing, removing duplicates, and getting their "polarity"

Thanks to Zoe Wilkinson Saldaña for the detailed how-to on Python and Vader

david#89.yxzp

PeterH23

@JefBus Your solution to challenge #89 is awesome. Great job.

Quick Links

This months top contributors

mceleavey 382

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262