Forem: Ken Hudak

Using Python & Alteryx to Find Social Network Influencers in Your Neighborhood (Part I)

Ken Hudak — Thu, 30 Jul 2020 12:33:47 +0000

After arriving in Canada (as a permanent resident, woot!) in 2018, the relevancy of many of the Twitter accounts I followed for current events in my old neighborhood kinda diminished. So I decided to set out and make a new list of influential users in my new neighborhood.

I bet there are easier ways to do this, but assuming I'd be biased and crazily start following all the local techorati and anyone mentioning Formula 1, I really needed a neutral tech solution. So a combo of Python and the Tweepy API would be a great impartial arbiter to nominate some influential local users by:

Wrangling a batch of tweets from the past couple of weeks from the Windsor area with Python, and
Use Alteryx to automate a workflow that scores and ranks local users based on # of favorites and retweets.

First I needed to look at tools available to locate tweeters by geographic location. Twitter has an amazing method to add latitude and longitude to a post. It would be super easy then to just make a geobounded box and grab all the tweets that came from within a box/city that I was interested in. BUT the specific geotagging thing got creepy and trackable and Twitter ultimately disabled it by default in 2014.

The feature is still there but now users need to opt-in and enable location. Only a few do.

Finding users via hashtag (such as a nickname like #RoseCity or aiport code #YQG) is effective, but not very inclusive. So, that leaves the Twitter API place object and the ID field as a decent method to narrow down a users location. And please note that Twitter docs refer to place_id as "a place associated with the user account." Close enough.

I've been getting the place_id using Twitter advanced search and finding someone who has geocoding enabled, and then clicking on their City/Province to get this style of string: https://twitter.com/places/15b0a643e7bd22c7.

Look for this:

There's a better way to do this, if you know of it please share in the comments! (or if you know an alternative to Yahoo!'s now defunct Where On Earth ID (WOEID) API.)

Ok. I've got the place_id alphanumeric string that I want to search with, so now it's time for the Python.

But first! Just in case you are reading this from the far distant future or using an virtual environment with who-knows-what-versions of installed Python libraries, I'll share what I am using right now in the year 2020 when this all worked.

Spider IDE 3.3.6
Python 3.7.5
Tweepy 3.8.0
Twitter API 1.1 - watch for the new (now delayed) version in 2020!
Pandas 0.25.3

First, you'll need Twitter developer keys if you don't already have them. This can take a couple of days.

Next, let's load the two simple libraries we'll need, tweepy for the API and pandas for the dataframes and exporting.

# Import Tweepy and Pandas Libraries.
# Install first if necessary.
import tweepy as tw
import pandas as pd

Next, authenticate with Oath using your new keys.

# Use your own Twitter.com Developer Keys inside the double-quotes
consumer_key = "Tk9...."
consumer_secret = "AOH..."
access_token = "550..."
access_token_secret = "y0l..."

Now we'll let Tweepy pass the variables above into the statements below, and then form the string for the search API.

# Authenticate with Twitter - nothing to change here since it uses the variables above
auth = tw.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)
api = tw.API(auth, wait_on_rate_limit=True, wait_on_rate_limit_notify=True)


# Declare what you want search_words to look for. Here I'm using Twitter's PlaceID for Windsor
search_variable = "place:15b0a643e7bd22c7 since:2019-06-01"

The free standard search API can go back 7 days, so obviously the 2019-06-01 date I requested didn't mean anything in the end. I don't really know cursor methods so I just ran this script at 7 days intervals as a workaround. But the important part is inserting the place_id you found for the target location, the language, and how many items to return.

# Uncomment filter:retweets to filter out ReTweets. I am interested in RT's so will comment out
twitter_search = search_variable #+ " -filter:retweets"

# Collect tweets in tweets variable. Will attempt to collect no more than 5000 rows.
tweets = tw.Cursor(api.search,
              q=twitter_search,
              lang="en",).items(5000)

This next loop will go through the tweets that meet the place_id criteria, create a list of a list, and include the specified fields such as user id, date tweet was sent, if the user was verified or not, etc, and store all that info in variable tweet_data.

# Iterate and print the information you are looking for in each collected tweet
for tweet in tweets:
    tweet_data = [[tweet.place, tweet.retweet_count, tweet.favorite_count, tweet.coordinates, tweet.user.created_at, tweet.user.screen_name, tweet.user.location, tweet.user.statuses_count, tweet.user.followers_count, tweet.user.verified, tweet.created_at] for tweet in tweets]

We loaded Pandas for a reason earlier, and now I'm going to set up a dataframe into columns and give them nicer names.

# Put the collected data tweet_text into Pandas DF for writing to table
tweet_text = pd.DataFrame(data=tweet_data, 
                          columns=['Place', 'RetweetCount', '# Faves Count', 'Coordinates', 'Join Date', 'user', "location", 'Tweets', 'followers', 'verified', 'tweeteddate'])

And lastly, use Pandas to export the tweet_text variable straight to Excel. Here you can name the Excel file AND the worksheet. Note, if you run this several times to get around the 7 day limit, be sure to change the name so you don't overwrite the historical data (learn from my mistakes!).

# This is a nifty Pandas tool to write out to an Excel file with a custom sheet name
tweet_text.to_excel("WindsorTwitter1_2020-07-29.xlsx", sheet_name="WindsorTwitter")

This first column in the dataframe, Place, returns this geographic info:

Place(_api=, id='15b0a643e7bd22c7', url='https://api.twitter.com/1.1/geo/id/15b0a643e7bd22c7.json', place_type='city', name='Windsor', full_name='Windsor, Ontario', country_code='CA', country='Canada', contained_within=[], bounding_box=BoundingBox(_api=, type='Polygon', coordinates=[[[-83.113623, 42.2339053], [-82.890548, 42.2339053], [-82.890548, 42.356225], [-83.113623, 42.356225]]]), attributes={})

And that's it. The complete code is on GitHub.
In the second part of this series I'll go through what I found in my Excel file and how it was cleaned up with Alteryx.

Intro to SQL for Election Results Analysis

Ken Hudak — Tue, 19 May 2020 00:41:58 +0000

This short tutorial will use SQL to perform an analysis to determine the winner of the 2018 Mayoral race in Windsor, Ontario. After all the debates and campaigning the votes have been cast and now it's time to count them up.

I started off by importing the 2018 Election CSV flat file from the City of Windsor's Open Data Catalogue into Microsoft SQL Server, and used SQL Server syntax to go through the steps here in this article.

It's a good idea to start off by conducting some initial data reconnaissance, formally known as exploratory data analysis (EDA), to see what we're dealing with. The table of election information, we'll call it Election2018, consists of 3057 rows made up of the following six columns of data:

Ward - a unique ID for a geographic area in the city
Poll - the voting location ID within a Ward
Poll_Name - name of the voting location
Contest_Title - title of the office the candidate is seeking
Candidate_Name - name of the individual candidates
Total_Votes - how many votes each candidate received at each poll

Part 1: What does the Election2018 table look like using Microsoft SQL Server?

We can use SQL to display a table of information that looks a lot like the familiar format we've grown to love/hate in Excel -- which is all about columns and rows. I'll need to use two key components of SQL to get started: the SELECT statement and the FROM command. With SELECT we'll be telling the database what columns we would like to work with. SELECT is pretty much mandatory, hence it's what we start almost every basic query with.

Using the SELECT command, I could ask the database to show me all of the columns by typing each of their individual names:

SELECT Ward, Poll, Poll_Name, Contest_Title, Candidate_Name, Total_Votes

And I also need to specify the table in which these columns are located. In this example since we're using the Election2018 table, we'll refer to it as that name using the FROM command. End the query with a semicolon. (Not every SQL platform requires the semicolon, but many do, so just make it standard practice to use it.)

So the complete query to look at all the columns and rows from table Election2018 would look like this:

SELECT Ward, Poll, Poll_Name, Contest_Title, Candidate_Name, Total_Votes
FROM Election2018;

While typing in all of the column names may sound like fun, SQL gives us a break from that with the asterisk (*). Using SELECT * means essentially the same thing as HEY, SELECT ALL THE COLUMNS. Much easier for our data analysis phase and it'll save some time.

The revised query looks like:

SELECT *
FROM Election2018;

And here is a small extract of the returned data that we get from the query:

Ward	Poll	Poll_Name	Contest_Title	Candidate_Name	Total_Votes
7	8	7-8 Forest Glade Arena	MAYOR	Drew DILKENS	468
7	8	7-8 Forest Glade Arena	MAYOR	Ernie LAMONT	7
7	8	7-8 Forest Glade Arena	MAYOR	Franz (Frank) DYCK	16
7	8	7-8 Forest Glade Arena	MAYOR	Matt MARCHAND	195
7	8	7-8 Forest Glade Arena	MAYOR	Tom HENSEL	10
1	5	1-5 Roseland Golf and Curling Club	COUNCILLOR WARD 1	Darcie RENAUD	380
1	5	1-5 Roseland Golf and Curling Club	COUNCILLOR WARD 1	Fred FRANCIS	438
1	5	1-5 Roseland Golf and Curling Club	COUNCILLOR WARD 1	Krysta GLOVASKY-RIDSDALE	33

Are the results as you expected? Kinda. We see Wards and Total Votes and Candidate Names, but it turns out the CSV file Election2018 also contains many more ballot races such as Ward Councillors and Trustees. They sure are important races but we're not concerned with those right now. So let's get rid of them.

Part 2: How do we show just the Mayoral race?

The WHERE clause in SQL works like a filter. We can use it to return only records that we're interested in. In this case it'll be the column Contest_Title and within that column we'd like to only see records returned that are part of the MAYOR contest.

Tack the WHERE clause to the query (always after the FROM statement) and we get a query that looks like this:

SELECT *
FROM Election2018
WHERE Contest_Title = 'MAYOR';

Mayor is in single quotes because it's value is a text string and the quotes communicate that to SQL, otherwise the field could be interpreted as a column, which will produce a "Invalid Column Name" error.

Part 3: Which Mayoral Candidate Won the Most Votes?

Just a couple of more lines of code and we'll know who became the next Mayor way back in 2018! Let's create a tidy table with only specific columns and vote totals from all the polling locations in order to publish the final results.

Start with SELECT. Use the Candidate_Name, Contest_Title, and Total_Votes columns. Name and title are easy, just type those two in separated by commas.

For calculating the total votes we'll need to also include the SUM() function in the SELECT statement. Inside the parenthesis of the function we'll need to enter the name of the numerical field to be summed -- in this case it will be Total_Votes. We'll be asking SQL to look at each polling location and sum up how many votes each candidate received.

Utilizing SUM(Total_Votes) in the SELECT statement is how to approach this. The query will add up all the votes from each Ward and return them in a column named something like 'Column3.' We can customize the name of this new column by using an alias. To give a field an alias just type "as" (no quotes) and then the new column name of your choosing. It'll look like this: SUM(Total_Votes) as TotalMayoralVotes.

Oh, and don't forget that FROM Election2018 command!!

SELECT Candidate_Name, Contest_Title, SUM(Total_Votes) as TotalMayoralVotes
FROM Election2018;

Next we add the WHERE filter described above to get Mayoral candidates only, and below that enter in a GROUP BY statement. GROUP BY will lump together all the votes for individual polling stations for each Mayoral candidate and list the total number of votes in the results next to their name.

SELECT Candidate_Name, Contest_Title, SUM(Total_Votes) as TotalMayoralVotes
FROM Election2018
WHERE Contest_Title = 'Mayor'
GROUP BY Candidate_Name, Contest_Title;

Lastly, I want to see the results in order from who had the most votes in the city to who had the least. Here we'll be using ORDER BY as the last line in the query. SQL returns ORDER BY values in ascending (lowest to highest) order by default, so I'll specify that the TotalMayoralVotes is descending by using DESC in the query (yes, you can ORDER BY on an alias). The final SQL code get stitched together just like this:

SELECT Candidate_Name, Contest_Title, SUM(Total_Votes) as TotalMayoralVotes
FROM Election2018
WHERE Contest_Title = 'Mayor'
GROUP BY Candidate_Name, Contest_Title
ORDER BY TotalMayoralVotes DESC;

And the results are in!

Candidate_Name	Contest_Title	TotalMayoralVotes
Drew DILKENS	MAYOR	31201
Matt MARCHAND	MAYOR	18626
Ernie LAMONT	MAYOR	1121
Tom HENSEL	MAYOR	797
Franz (Frank) DYCK	MAYOR	785

Drew Dilkens was re-elected as Windsor's Mayor in 2018 by a 12,500 vote margin over the second place candidate Matt Marchand. Thanks SQL!

Some GDG Windsor-Essex DevFest Takeaways

Ken Hudak — Thu, 10 Oct 2019 01:06:04 +0000

Yes, there is a Tech Scene in Windsor, Ontario. More on that below the fold.

On October 5th, 2019 over 250 attendees, speakers, and organizers came together for the 3rd Windsor Essex DevFest on the campus of the University of Windsor.

Windsor, with a population of 234 thousand, sits just south of Detroit. So it's fair to to assume that the city has has been indoctrinated into the auto culture of the region. But like most manufacturing hubs that grew up on the hum of assembly lines and a community rhythm that revolved around three shifts, there is now a noticeable transformation towards a growing tech sector and knowledge economy as well.

Ok, you get the gist, onto the sessions--

The event got off to a great start with a keynote presentation from Amir Feizpour of aggregate intellect who spoke about recognizing that language is limited and imprecise and we should all be working to improve communication as a whole. How? Try to strive to put in some interpretive labor and streamline your message! This will reduce the burden on the listener to improve understanding (same theory applies to your code!).

On to the next session - Practical Machine Learning with Karthik Kuber. Karthik emphasized that 'learning to learn' while on the job is an essential part of becoming a better data scientist. And why is that? Because lots of assumptions in school projects do not actually work in real-world projects. (Personally, I'd paraphrase that to mean that the 'everything-is-fine-wonderful-Utopian-world-inside-of-DataCamp concept seems to fade into the distance once you get those first real-world data sets.)

Kuber recommends Data Analysts focus on these skill sets to address typical business problems:
▶ Working with various disparate data sources
▶ Working with large scale data
▶ Dealing with streaming and ever-increasing and changing data
▶ Working with incomplete and messy datasets
▶ Building models that can run in real-time and at scale

You know that saying “If you are the smartest person in the room, then you are in the wrong room?" Well, I was in the RIGHT room for a talk by Arafat Khan who ported a version of Tensorflow on Ruby. Brilliant and passionate, Khan was engaging and excited by the how well the community adopted his project.

My takeaway from him was the following: "You have to have something visible to make a point."

The rest was all above my paygrade, so take 4 minutes and read up on his Medium Post if the slide below speaks your language.

That brings us to lunch! Popped in line for a quick burger and hopped outside for some sunshine, which was still a welcomed sight for early October in Southern Ontario.

Now, I promised earlier we'd discuss Windsor's tech scene. And I didn't forget. This was a session I was really looking forward to. Hosted by Yvonne Pilon, the President and CEO of WEtech Alliance, her talk shed a lot of light on what the 3,500 workers who make up core tech talent in Windsor are up to.

WEtech engages with the technology and business sectors to illuminate tech efforts in the region and is a major hub for connecting the tech community to each other, the media, and academia. Be sure to check out Tech Connect as it is a one of kind resource for local jobs, initiatives, programs and events.

Lastly - Entrepreneurship! What if you wanted to take your coding and analysis skillset and go into business for yourself? Frank Abbruzzese, President of AlphaKOR Group, had some great tips for making that leap. Here we go:

▶ Get out of your comfort zone
▶ Get involved in the local community and events
▶ Plan effectively with goals, time, and outcomes in mind
▶ Manage your time and Eat the Frog First, i.e., do the thing you don't want to do, first thing!
▶ And, this is a big one, no one should ever have to come to you to ask for an update. Be proactive! Keep your employees and customers up to date on everything.

Well that about wraps it up. I'm only one person and couldn't attend the other 20 or so sessions, but head over to GDG Windsor DevFest to check out the posted slide decks and other info about the event.