Feb 01, 2023

Making Big Data Feel Small: Analysis of Hacker News Stories with BigQuery and Ibis (Part 1)

Marlene Mhangami

If you follow my work, you know I love analyzing data sets with open source tools like Ibis. I’m also interested in finding the most efficient ways to access and draw insights from big datasets with Python.

Lately, I’ve noticed an uptick in the Ibis community using Google BigQuery to source large datasets. This inspired me to take a closer look. As a refresher, BigQuery is a Google Cloud product for storing and accessing very large datasets quickly. The BigQuery Public Datasets Program makes useful big datasets available to the general public by storing and hosting them on their platform. One particularly interesting dataset available through the program is the Hacker News dataset; it contains all the stories and comments from Hacker News since its launch in 2006 to present (updated daily)

Hacker News (HN) is a social website focusing on computer science and entrepreneurship. It is estimated that the site attracts more than three million views per day across 300,000+ daily users (source.) For anyone involved in the world of technology, HN is a treasure trove of information and managing to get onto the front page could mean your blog post, product, etc., goes viral. Knowing all of this, I thought it would be helpful to explore the dataset and gather some insights.

A New Approach to Data Analysis

One of the more frustrating parts of using BigQuery for analyzing data is that to access the data you need, you must use SQL. For many data scientists that are accustomed to using Python for data analysis, this can be error prone and make code harder to test.

More recently, I’ve found Ibis is an excellent solution to this problem. Ibis provides a uniform Python interface for a large number of database engines— including BigQuery! In this article, I’ll show you what this looks like by connecting to BigQuery from my local dev environment and analyzing the Hacker News dataset in a Jupyter Notebook. You can find the notebook here if you’d like to access the code to follow along on your own. Let’s get started!

Import the Necessary Libraries

I started by installing ibis-framework and ibis-bigquery, which are the only libraries needed to access data from BigQuery. I then imported Ibis and set interactive mode to True. This makes sure the results of any queries are printed to the console.

!pip install --upgrade ibis-framework ibis-bigquery
import ibis
ibis.options.interactive = True

Access the BigQuery API

In order to access the BigQuery API, I created a Google Cloud account and set the correct authorization permissions. Here are the steps I took to do this (images sourced from pypinfo.)

Create project

1 - Go to https://bigquery.cloud.google.com.
2 - Sign up if you haven’t already. The first TB of queried data each month is free. Each additional TB is $5 (USD).
3 - Go to https://console.developers.google.com/cloud-resource-manager
and click CREATE PROJECT if you don’t already have one:

4 - This takes you to https://console.developers.google.com/projectcreate. Fill out the form and click CREATE. Any name is fine, but it’s best to pick a name associated with the project you want to build, for example hacker_news. This way you know what the project is designated for:

5 - The next page should show your new project. If not, reload the page and select from the top menu:

Enable BigQuery API

6 - Go to https://console.cloud.google.com/apis/api/bigquery-json.googleapis.com/overview and make sure the correct project is chosen using the drop-down on top. Click the ENABLE button:

7 - After enabling, click CREATE CREDENTIALS:

8 - Choose the “BigQuery API” and “No, I’m not using them”:

9 - Fill in a name, and select role “BigQuery User” (if the “BigQuery” is not an option in the list,
wait 15-20 minutes and try creating the credentials again), and select a JSON key:

10 - Click continue and the JSON will download to your computer. Note the download location. Move the file wherever you want.

11 - Set an environment variable GOOGLE_APPLICATION_CREDENTIALS that points to the file.
That’s it!

Connect to BigQuery Using Ibis

To connect to the Hacker News Dataset, I needed to give Ibis the Project ID I created above in step 4. It’s listed to the left of the Google Cloud Console screen, as seen below, highlighted in blue.

I also needed the Hacker News Dataset ID, which I found here. Using this information, here’s all the code I used to connect to the HN database.

hnconn = ibis.bigquery.connect(
project_id = 'your-project'
dataset_id = 'bigquery-public-data.hacker_news'
)

To start to analyze the data, I grabbed the full Hacker News table, containing comments, stories, scores, etc., from the database by running this line of code:

full = hnconn.table('full', database='bigquery-public-data.hacker_news')

Preview the Dataset

Now that I had access to the table, I wanted to get a better understanding of the data by doing some EDA. To do so I called the head method, in the same way I would with a pandas dataframe.

full.head()

Here’s what the resulting table looked like:

I also had a look at how many of each type of submission was in the dataset by running:

full.type.value_counts()

Next, I wanted to know what time interval the data was collected in:

#find what dates the data spans
years = full.timestamp.year()
print(f'this table shows HN posts from {years.min()} to {years.max()}')

This table shows HN posts from 2006 to 2022

Finally, here’s how many rows are in the table:

full.count()

33620611

There are over 33 million rows of data! Since Ibis takes advantage of lazy evaluation, this entire dataset is not loaded into local memory unless I need to use it. This makes it very efficient when working with large datasets like this. To learn more about how Ibis works under the hood, check out my post ‘An Introduction to Ibis for Python Programmers.’

Clean the Data

Next we’ll clean our data, which, in this case, means removing all of the comments that don’t have text in them and all the stories that don’t contain text or a title.

Cleaning the comments

#filter the comments and print the number of comments
full_comments = full.filter(full.type == 'comment')
print(f'Before cleaning we had {full_comments.count()} comments')

#clean the comments and print the number of comments after cleaning
clean_comments = full_comments[full_comments['text'].notnull() | full_comments['title'].notnull()]
print(f'After cleaning we have {clean_comments.count()} comments')

Before cleaning we had 28897394 comments ...
After cleaning we have 28050701 comments ...

Cleaning the stories

#filter the stories and print the number of stories
full_stories = full.filter(full.type == 'story')
print(f'Before cleaning we had {full_stories.count()} stories')

#clean the stories and print the number of stories after cleaning
clean_stories= full_stories[full_stories['title'].notnull()]
print(f'After cleaning we have {clean_stories.count()} stories')

Before cleaning we had 4691514 comments ...
After cleaning we have 4405643 comments ...

Wrangle the Data

Join the comments and story data

After removing unusable comments and stories, we want to join the comments and story tables so we can better analyze what the comments mean about the story. We’ll carry out some sentiment analysis later on to figure out which kinds of articles got positive comments and which ones did not.

We’ll use the inner join method in Ibis to join the clean stories and clean comments. We can then map the story id to the comment parent id:

#join comments and stories
stories_and_comments = clean_stories.inner_join(clean_comments, predicates=clean_stories['id'] == clean_comments['parent'])

Here’s what the full line of code looks like:

stories_and_comments = clean_stories.inner_join(clean_comments,
predicates=clean_stories['id'] == clean_comments['parent'])

Now, we’ll choose only the columns we want to use, which in this case are: the story id; the story title; any text accompanying the title on HN; the comment text; and the score the story got.

stry_and_cmnts = stories_and_comments[['id_x','title_x','text_x','text_y','score_x']]
stry_and_cmnts

10000 rows x 5 columns

Then we’ll run a groupby to find how many comments each story has.

num_comments = stry_and_cmnts.text_y.count()
stories_grouped = stry_and_cmnts.group_by('title_x').aggregate(comments=num_comments)

stories_grouped

10000 rows x 2 columns

At this point, I’m also a bit curious about how many stories we’re dealing with, so we’ll check that:

stories_grouped_numbers = stories_grouped.aggregate(comments=num_comments)
stories_grouped_numbers.count()

Great, it looks like we’re working with over one million stories!

Analyzing the data

Next, let’s have a closer look at some things the data tells us. We’ll start by finding out what score a story has to have to be considered good, average, or not so good. Note that we’ll determine this statistically.

The lowest score a story could have is:

min_score = stry_and_cmnts.score_x.min()
min_score

-1

The highest score a story so far has attained:

max_score = stry_and_cmnts.score_x.max()
max_score

6015

For a story to be considered “average” it should have a score of about 185 like we can see below:

avg_score= stry_and_cmnts.score_x.mean()
avg_score

185.62919906694896

What Are People Writing About on HN?

Most Popular Stories

To help us begin to understand which stories are the most popular on HN, let’s take the titles and arrange them in descending order based on their score.

titles = stry_and_cmnts[['id_x','title_x', 'score_x']].distinct()
sorted_titles = titles.sort_by(ibis.desc('score_x'))
sorted_titles

10000 rows x 3 columns

The top story is about the passing of Stephan Hawking, certainly worth its position. I wanted to see a little more context about the second story, “A Message to Our Customers”, so I pulled its details from the full stry_and_cmnts table I made earlier to check if I could learn more.

stry_popular = stry_and_cmnts[stry_and_cmnts.id_x == 11116274]
stry_popular

213 rows x 5 columns

Unfortunately, even though this story has a title, there was no summary text uploaded to HN. However, there were a bunch of comments that all have something to do with Apple. Looks like something to do with the FBI and passcodes, etc… Interesting!

Most Common Topics

I also wanted to know which topics or words appear the most on HN. To do this I used the natural language processing module from SpaCy to parse out the most defining word from each title. I also needed to use a function I created on my own (a User Defined Function, or “UDF”) that took each title from the ‘title_x’ column and returned the main entity of the sentence. Here’s what that function looked like:

import spacy
nlp = spacy.load("en_core_web_sm")


def find_entity(title):
   parsed_title = nlp(title)
   entity_arr = []
   for num, entity in enumerate(parsed_title.ents):
       entity_arr.append(entity.text)
   return  ' '.join(entity_arr)

Unfortunately, Ibis does not provide full support for user defined functions, yet — but to get around this, I can call execute on the sorted titles column to return a pandas Series.

Here’s how that works: since Ibis uses lazy evaluation sorted_titles[“title_x”] is an Ibis expression that isn’t actually pulling any data into memory. Like I mentioned earlier, this is very efficient and, for the most, it’s what’s been happening before this point. Once I explicitly call execute Ibis knows to load the specified columns into memory and returns the result as a pandas dataframe. We can then use the pandas apply function on it to use our UDF.

This isn’t quite as efficient, but allowed me to apply my UDF and I’m able to switch right back to an Ibis table once that’s done! This is also better in terms of efficiency than if we had just used a pandas dataframe from the start. We are only pulling the sorted title names into memory and not all 33 million rows.

Here’s the code I used to return the sorted titles column as a pandas Series:

new_table = sorted_titles["title_x"].execute()


#this is a pandas dataframe
type(new_table)

pandas.core.series.Series

Here’s the code to apply the find_entity function I made above:

entity_col = new_table.apply(find_entity)

Next I wanted to get the value count of each entity and plot this out. I also limited the results to the top 24 entities.

top_entities = entity_col.value_counts()
top_entities = top_entities[1:25:]

Lets preview this table:

top_entities

Name: title_x, dtype: int64

Great! To visualize the results better I decided to use matplotlib to plot a graph of the words or phrases that appear the most.

import matplotlib.pyplot as plt
top_entities.plot(x="entity",y=["counts"],
                    kind="bar",figsize=(14,6),
                    title='Top stories topic').set_ylabel("counts")

Text(0, 0.5, 'counts')

I then made a word cloud to help visualize this even more:

from wordcloud import WordCloud
cloud = WordCloud(width=1440, height= 1080,max_words= 200).generate_from_frequencies(top_entities)
plt.figure(figsize=(20, 15))
plt.imshow(cloud)
plt.axis('off');
plt.show()

Google is by far the most mentioned entity followed by Apple, Amazon, and GitHub, with YouTube and Facebook not far behind. This seems to corroborate what we know about FAANG (Facebook, Apple, Amazon, Netflix, and Google) being some of the most popular companies in tech.

To keep working with the data in the most efficient way possible, I converted the top_entities pandas table back into an Ibis table using the following line of code:

top_entities = ibis.memtable(top_entities)

type(top_entities)

ibis.expr.types.relations.Table

Using Ibis, I’ve already been able to draw out some insights from our BigQuery dataset! I’d still like to run some sentiment analysis on the data to figure out what kind of posts have negative comments and which posts tend to get good ones. I’ll share the steps for this in a Part 2 blog. In the meantime, you can test out the code using the Jupyter notebook here. Voltron Data offers support for developers and enterprises working with Ibis. If you’re interested in learning more, check out our subscription options.