It’s safe to say that most people who have been to New York City or Montreal have probably had a bagel — the reliable, unabating food option as ubiquitous as the 24-hour restaurants and corner bodegas (or depanneurs in Montreal). Both cities are famous for their bagels, and each tend to claim theirs is better than the other’s. If you’re unaware of how contentious this debate is, just ask someone who has been to or lives in either city.

In 2008, when NASA officials asked Canadian astronaut Gregory Chamitoff what food item he wanted to bring aboard Discovery for a two-week flight into space, he requested fresh bagels from his cousin’s Montreal bagel shop, Fairmount Bagel.

For locals and tourists alike, Yelp may sway you to visit one shop over the other due to their business page attributes, namely the star rating and reviews left by visitors.

As an experiment, I wanted to see if I could determine which city bagel lovers prefer, and in order to answer this, I started an analysis and posed this question: Can one compare the same types of restaurants on Yelp (in my case, restaurants that serve bagels) between two places (in my case, New York City and Montreal) to determine which has the better product or service?

As someone who lives in Montreal and has visited New York and tried both types of bagels, I felt like a suitable candidate to take a deep dive into the analysis. To clarify the distinction, Montreal-style bagels are handmade and wood-fire baked, and in contrast to the New York-style bagel, are smaller, thinner, leaving a larger hole, and also sweeter with a denser texture. New York-style bagels tend to be much larger (like everything in America) with a softer, chewy interior.

Despite not being able to say which city has the better bagel after my investigation, I believe that with this new information, instead of relying on numeric attributes already existing on Yelp like star rating and number of reviews, my findings offers a look into the actual text data which has been converted into meaningful insights about the bagel experience at the given restaurant.

As far as I know, there haven’t been any data-driven approaches to answering the question I posed, however, many data scientists have used review data from Yelp to predict ratings of restaurants and to evaluate future development of businesses with various Natural Language Processing (NLP) techniques, which were helpful for me when looking at angles for how to approach my research question.

In order to start my investigation, I needed data. I decided to scrape the following attributes from Yelp to form my dataset:

• Restaurant name
• Customer name
• Customer location
• Review text
• Review rating (customer)
• Review date

I kept getting blocked from Yelp’s website when using Python’s web scraping libraries — Selenium and BeautifulSoup — and in the end I discovered a free Google Chrome extension called ‘Web Scraper’. I never once got blocked when using their service, and would recommend it.

I wanted to get a general idea of review and rating behaviour in both cities, so to begin my analysis I simply scraped a combined ~10,000 reviews & ratings from Yelp consisting of a variety of restaurant types. This wasn’t proving to be very helpful in terms of insights, so I dove right in to the bagel datasets, consisting of a combined ~7,300 reviews and ratings for New York and Montreal shops that sold bagels.

Once I read my CSV files containing the Yelp data into Python, I converted each of them into a Pandas DataFrame. Before my investigation, I made sure that each CSV consisted of the same columns as to make my cleaning process more streamlined.

I dropped unnecessary columns that were generated by the web scraper and renamed some columns for interpretability. There weren’t any null values or duplicates to take care of, however, I had to convert the ‘rating’ column since it was read in as a string. ‘5 star rating’ turned into a simple integer ‘5’.

I had a hunch during the scraping process that the star rating distribution would be very imbalanced, as I primarily only saw 5 and 4 star ratings. In the future, I would like to import more review data into an ML pipeline to balance out the dataset, as I believe this skewed results in the end. Moving forward, I binarized the rating column so that reviews with a rating of 4 and 5 become a 1 (positive) and every rating under 4 to become a 0 (negative). The class imbalance for Montreal was 25% negative and 75% positive, and for New York, 24% negative and 76% positive.

Looking at the review length distribution, there is a very similar distribution across the board for each star in each city, which gave me confidence that the model would be less biased.

Once I binarized the positive and negative reviews, I divided them into their own data frames with just the text column. Each review was then tokenized using TFIDF (short for term frequency-inverse document frequency) and 1-grams which were giving the most consistent results and clear topics. For topic modelling, I tried Latent Semantic Analysis (LSI), Latent Dirichlet Allocation (LDA) and Non-Negative Matrix Factorization (NMF). Who comes up with these names?!

The word weights for the negative review tokens where not very interpretable for LSI, however, I was able to determine the number of topics to use for my modelling based off of the topic strengths.

I was hopeful about LDA, but in the end due to my class imbalance and not enough negative review data, the overlap in meaning between positive and negative reviews made it difficult to make sense of the topics, so in the end I decided to go with NMF, which gave the most interpretable results and distinct topics.

I moved forward with two different sets of topics for negative and positive reviews. This differed between Montreal and New York, but not by much.

The process going forward consisted of mapping all of the reviews to topics, normalizing the topic weights to sum up to 1, and averaging the topic distributions for all reviews of a bagel shop to map that topic to the restaurant.

The process is seen clearly with my interactive Flourish visualization you can see here:

The newly modelled data is used to show topic strengths for both positive and negative reviews.

I set out to determine which city has the better bagel but as I spent more time with the data and started my modelling, it became clear to me that review data doesn’t really reflect the question of which city has the better bagel, as they are too conflated with other things like the various topics seen in my topic modelling.

Despite not being able to say which city has the better bagel after my investigation, I believe that with this new information, instead of relying on numeric attributes already existing on Yelp like star rating and number of reviews, my findings offers a look into the actual text data which has been converted into meaningful insights about the bagel experience at the given restaurant.

My next steps with this project are to look more into the customer metadata on a larger Yelp dataset to analyze the different review behaviour between locals and tourists. It would also be helpful to spend more time working on better tokenization (with more data) to see a greater variety of topics. Because of the overlap in meaning between negative and positive reviews, I could also try creating a singular topic space for all reviews instead of just positive and negative.

View the code on my GitHub: https://github.com/setadrift/capstone_1

Thanks,

Duncan Anderson
LinkedIn: https://www.linkedin.com/in/duncan-kg-anderson/
Twitter: @duncanand

Using SQL effectively is a critical skill for any data scientist and is one of the most tested in interviews. In our own attempts at improving at this skill, we found an incredibly useful article by Mrinal Gupta (original article linked here) that covered 10 high quality questions with most SQL concepts tested in interviews. After the article, however difficult a practice question we tried, it paled in comparison to those 10 and it was surprising to see the sudden jump in our abilities! To best benefit from the aforementioned article, we had to do 3 preparatory steps:

1. Prepare practice data (In the original article, the training data wasn’t readily available. We had to manually make SQL tables & enter in the data to try our test queries).
2. Write down our step by step thought process (Some answer queries were very intimidating at first glance. It took a long while to figure out the step by step thought process behind them).
3. Outdated keywords replacement (Some SQL keywords used in the original article were outdated possibly due to version changes. We had to find replacements for the current version).

In this article, we share our preparatory steps (with the 10 key questions) for a smoother learning experience. We hope you find it as beneficial as we did!

Q1. Given two tables below, write a query to display the comparison result (higher/lower/same) of the average salary of employees in a department to the company’s average salary.

First, let’s create the necessary tables.

Now that we have our tables set up, let’s do the initial JOIN.

Step #1. JOIN

Now the two tables are joined together using the shared ‘employee_id’ column:

Step #2. Now that we have the two tables joined, consider the intermediary columns needed for our final table. Select and engineer those.

With this step, we achieve a few things. By using the DATE_FORMAT keyword, we allow our window functions to be averaged by month. Then we alias these statements for clarity. Here is the output of the above statement. If that seems a bit unclear, we suggest quickly Googling what Window Functions are, because they will show up a lot in the proceeding questions.

Step #3. Let’s convert our existing code into a sub-query & pipe into a blank final query.

We are creating the temporary tables using the WITH keyword, as this allows us to query aliases. Here’s the output from the above code:

Step #4. With that, all that’s left is to do the relevant selections & feature engineering for our final display. We’ll use the DISTINCT keyword to remove the duplicate columns from the result set and set our conditions using the CASE WHEN clause.

Now we can display the comparison result (higher/lower/same) of the average salary of employees in a department to the company’s average salary.

Q2. Write an SQL query to report the students (student_id, student_name) being “quiet” in ALL exams. A “quiet” student is the one who took at least one exam and didn’t score neither the high score nor the low score.

Solution 2:

First, let’s create the necessary tables.

Step #1. Join (We do an inner join since no interest in children with no exams)

The tables are now joined on student_id.

Step #2. Next we create all the intermediary columns needed.

Step#3. Convert previous query to a sub-query so we can access intermediary columns.

Step#4. Consider final output & use relevant ‘selecting’ & ‘filtering’ to produce.

Q3. Write a query to display the records which have 3 or
more consecutive rows and the amount of people more than 100 (inclusive).

Solution 3:

First, let’s create the necessary tables.

Step#1. Make a column that identifies 100+ streaks.

Step#2. Select other relevant intermediary columns & make into a sub-query so we can access them.

Step#3. We then make a table that counts the streak size from t1 for each streak.

Step#4. We filter out only streaks with 3+ streak_size.

Step#5. Let’s inner join this to t1 so we get all the entries of the shortlisted streak_num.

Step# 6. Then make relevant selections as needed for final output.

Q4. Write an SQL query to find how many users visited the bank and didn’t do any transactions, how many visited the bank and did one transaction and so on.

First, let’s create the necessary tables.

Step#1. Let us start off by finding the # of visits by day by user_id.

Step#2. We then calculate the number of transactions by day by user_id.

Step#3. Let us do a left join so we can maintain total visits & wrangle data together.

Step#4. We select only date column, num_visits column & replace null num_trans by 0.

Step#5. Let’s convert our previous result into a sub query then make appropriate bins using another query t2. We will have to use the RECURSIVE clause for t2 to correctly make it function.

Step#6. Join t1 & t2 appropriately, and we have our desired output!

Q5. Write an SQL query to generate a report of period_state for each continuous interval of days in the period from 2019–01–01 to 2019–12–31.

Let’s start off by making our tables.

Step#1. Let’s make a column that records each streak in the year 2019. State 1 added to simply signify success state.

Step#2. Let’s find the start & end date of each streak.

Step#3. Let’s repeat the process for failed state.

Step#4. Let’s combine t1 & t2 using UNION ALL & order by date.

Step#5. Let’s use case when to replace 1 & 0 with succeeded & failed, and we have our desired output!

Q6. Write an SQL query to report how many units in each category have been ordered on each day of the week.

First, create the schema and tables:

Step #1. Join the tables with a RIGHT JOIN, as every item will not have an order.

If you look at the inputted values for the results table, we see that ‘T-Shirt’ has no values, but all of our items in the table are there.

Step #2.1. Let’s create a temporary table and join the necessary columns from the orders and items tables and test with the 1st day of the week, Monday.

For the output, we should expect to see the sum of all items just for the Monday.

Step #2.2. Great, now that we know that works, let’s fetch all the days of the week.

Output:

Step #3. What’s wrong with the above output? We don’t want to see multiple item_categories appearing, so let’s use DISTINCT on the item_category column.

Now we can see how many units in each category have been ordered on each day of the week.

Q7. Write an SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).

First let’s create the schema and tables:

Step #1. We’ll need to JOIN the two tables on the respective id’s.

We have the following results to work with. What data do we care about from our joined table?

Step #2. Well, we’ll need the employee name, department name, and salary

Step #3. Great, so we have the info we’ll need in the end. Let’s turn our existing code into a subquery, and set a condition to return only top 3 values.

In IT dept, Max earns the highest salary, Randy and Joe have a tie for second, and Will earns the third highest. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.

Q8. Write an SQL query to compute moving average of how much customer paid in a 7 days window (current day + 6 days before) .

First let’s create the schema and ‘customers’ table we need.

Step #1. Let’s start by aggregating the amount with SUM and grouping by visited_on in order to see the window of time

So now we can see the amount of customers who visited each day.

Step #2. We need to find the rolling average. We want to know the total amount during this time period, as well as the average amount. Each 7 day period out of the 10 days should equate to 4 possibilities per day.

Step #3. In order to SELECT visited_on in this statement, we’ll need to make our first query a temporary table, as well as this one.

Now we can see the moving average and the total amount of how much a customer paid in a 7 day window (current day + 6 days before).

Q9. Write a query to find the shortest distance between these points rounded to 2 decimals.

First let’s create the table:

Step#1. First let’s use a CROSS JOIN, which is a type of join that generates a paired combination of each row of the first table with each row of the second table.

Step#2. Now that we have the cross join taken care of, let’s find the shortest distance between each coordinate from every other.

Step#3. We can clearly see from the output that the shortest distance is 1, but to keep things clean, we’ll finish this question off by sorting by ASC and limiting 1 so only the shortest distance is displayed.

The above query will output 1, which is what we expected.

Q10. Write an SQL query to find all numbers that appear at least three times consecutively.

Step#1. Since we’re interested in getting the consecutive numbers to see which appear 3x in a row, we’ll use the LAG function to fetch data from the previous row, and LEAD for the subsequent row.

Notice that the first row for prev is null, since the row from which the value (num) should be obtained doesn’t exist. The inverse is true for ‘next’.

Step#2. Let’s turn our SELECT statement into subquery to enable WINDOW function condition.

Step#3. Let’s finish this off by using DISTINCT to return the numbers that meet our condition (as we see, it’s simply 1 from the previous query).

We hope you enjoyed our article and are feeling much more confident about SQL. For next steps, we recommend just free-styling some medium & high difficulty data science SQL interview questions. Stratascratch has several such questions available in their free tier (at least at the time of writing this article). Then after that, considering projects where you can apply your new found SQL skills. Thanks again for engaging with our work. It makes us happy to know it helped someone out, like our past-selves!

Sincerely,
Duncan Anderson & Ammar Khan.
Ammar’s LinkedIn
Duncan’s LinkedIn

For almost a year, I’ve been very interested in transforming my traditional practice of journalism into a more data oriented approach. What better way to do this than to speak directly with data scientists and get some advice from them, right? Indeed.

Can you please just start by saying your name, a surprising fact about yourself, and what it is you do exactly with U Squared?

My name is Daniel Usvyat. One surprising fact is where my father is from. Most people have never heard of the place. It’s kind of an unusual place, politically charged, recognized as a country by only six other countries. So he’s originally from the Soviet Union and the independent state is Abkhazia.

Before U SQUARED, I used to work at a management consulting company. I did that for a few years but after 1 year I started to get sick of it. There is more to life than living out of a suitcase, traveling week by week. I was learning lots of skills but also building up a client base, and so I wanted to do it on my own on a smaller scale. So I went into freelance consulting in data science, and with like-minded people to myself, Istarted talking about creating some kind of brand, which we could do projects from and that’s kind of how U SQUARED was born. So we’ve grown, worked with lots of interesting clients in media, finance, insurance, and more recently the public sector. It’s been an interesting story from the get go to now. I’ve really enjoyed it. I like working on challenging projects and feeling rewarded by giving clients what we do best.

How many clients have you worked with?

Big clients? Around 6 projects. I’ve also worked with smaller startups, advising them on how they can use AI, and advisory or proof of concepts.

Have you partnered or worked with any data journalists or news organizations?

Recently I did a project with The Guardian — I didn’t work directly with journalists per se, but I was doing some stuff with NLP (Natural Language Processing), which allows them to essentially extract more information from news articles. Before this, the main thing we were looking at was keyword tags, which were human derived. Maybe it was something like “Sport” or “Brexit” but that was used to kind of cluster or group articles together. We were finding more intelligent ways of extracting additional meta-data from articles to use as a way to find commonalities in articles and understand what people like, and what drives engagement around journalism. So that was actually a really cool and interesting project.

How do you see machine learning and AI influencing journalism in the future?

I’d say it’s more data mining than AI/ML. The Washington Post a few months ago was showing the spread of COVID in an interactive way. Using some kind of simulation to show like, if you change this, how would that likely imprint a spread of the virus. And what I like about the Guardian is that they do good visualizations on elections and broad events.

For a journalist like myself looking to become equipped with some tools to produce solid data journalism, how would you structure a self-learning data science environment? So far I’ve been learning Python and I’m wondering what I should learn next.

Understanding basic data retrieval methods like how to scrape information from websites is essential. If you want to go through Google, different news sources and collect info from different topics, how would you do that? I’d say the best way to do it is using Python. It’s probably the easiest entry point and easiest thing to learn. Start with the problem and ask yourself — “What am I trying to solve?” And then ask… “Is that achievable with python through existing packages?” Probably. Then understand what package you need to use, which is as simple as going online to see if people are sharing their work and have done something similar before. Take that code and look at how you can adapt it and solve your problem and bend it to your use case and needs.

This allows you to save a lot of time rather than building from stuff that exists. No point in repeating the same thing again. So once you’ve done that, it’s a question of doing some very small collections. So if you want to collect info on the current US election, you’d map out all the different news sources you’re interested in and try it on this and see how it works. If it works, great, if not, look at what’s going wrong and adapt it. After that, you’ll look at spinning it out across everything and once you’ve done that, you look at what you have to do next.

I like to make it very simple and not get too caught up on the details. So thinking — “What do I have to do first? Ok, I’ll do that. What next? Okay I’ll do that.” Otherwise you can get lost in the cycle in trying to find the perfect solution and never really starting. So I guess to start, start small and build up and learn as you go along.

You don’t need a PHD in math or data science, you just need a willingness to learn, curiosity and resilience — sometimes you’re going to have a lot of bugs and it might be quite painful, and you might feel like you have no clue what you’re doing but you may suddenly have a breakthrough and keep on pushing on.

Sounds a bit ironic, but I’m a bit opposed to studying data science as a degree. I think it’s not really necessary — all of the info is online for free. You can do your own custom projects and get a job in data science. I think there’s too much emphasis on getting a degree. I’m a big advocate on personal projects and learning that way, as opposed to academic learning.

The market is exploding with data viz charts and visuals — some good, some not so good. What are the common pitfalls of these visualizations, and what should data journalists keep in mind when creating a data visualization.

That’s a very good point — It’s very important to not present information in misleading ways. So it’s always good to have transparency in the information that’s presented so when you are presenting these charts it needs to be very clear how they should be used. It should be user friendly, and shouldn’t need instructions… You should just be able to look at it and not misunderstand information. I guess, my opinion in the way with journalism is, is that there is always some bias — like the newspaper’s political viewpoint. I’ve never seen a newspaper that’s not biased in some way, whether it’s good or bad, it depends on whoever is looking at it. The Guardian is a more left leaning, more liberal, and has a more present view point and it represents younger generations on the viewpoint of the world which is good in my opinion. If you read some newspapers on the right wing it’s a bit more conservative, a bit cringe sometimes to read those, and feels like a kind of past generation representation of the world which doesn’t really exist anymore.

So I feel like when it comes to AI and ML the same bias can be perpetrated on the newspapers’ inherent bias they have, or whatever news source they produce.

Is data journalism less biased than traditional journalism?

Generally I would like to say that it is, but from experience I’ve seen it sometimes may not be. I feel like you can always take data sets and adjust it to suit your view point. This is what politicians do all the time — they take the same data set and make it say two different things because they have two different metrics or two cuts of data. I’ve heard people often say stuff like “We want to show this, and if we take this measure will it still make sense…?” I mean, you’re just massaging the numbers now, so it’s always down to the beholder of the info, the analyst — or on the receiving end, in consulting you have a client who is paying for the project, they may have a certain belief and unless you want to get fired, you’re going to find a way to support that belief. It sounds bad and that’s the reality unfortunately. I like to think that if you’re working with someone more open minded and with less of an ego, than hopefully what you’re saying will be true but unfortunately people have egos and agendas.

How important is it to link back to the original source and show we got the data?

In my opinion it’s good to have a high level understanding of how the data is processed and where it’s coming from. If I’m honest, the main important point is where the data comes from first of all, because if it comes from a suspect source or unreliable source that’s the first point of failure. After that, you have all the processing and data wrangling and the next step is what kind of average are you taking? The mean, or the medium? That’s quite important and then of course, understanding the core statistics of the data. What is the spread? Is it evenly distributed? This is quite important to understand. After that, they’re doing some kind of prediction from it, and they’re kind of changing some kind of interpretation.

If I have some time series data with financial news I’m using it to extrapolate what’s going to happen in 3 months time. If you don’t understand what’s done to get that result, then it can be dangerous. You can say something completely insane that doesn’t actually make sense. Just because somebody has made a prediction, but come up with a terrible prediction, understanding the efficacy of what’s being done is important and necessary.

Do you foresee data becoming more readily available from businesses, governments and organizations?

I think it hopefully will, because it would give people more ability have more understanding of what’s going on. You have, which is an interesting source, google data which has a bunch of random data sets you can search through. Google Trends is good to understand what’s trending alongside Twitter and other things especially with news and popularly of incoming trends.

A growing trend is alternative data, which is basically finding disparate data sets that aren’t used for something and using it to make predictions. So this is quite commonly used now in finance or the stock market as a basic as new sentiment, or age distribution of a country and seeing how that can influence equity prices or the future value of a company. Finding disparate data sets that aren’t used for something and using it to make predictions can be used in journalism to understand what is happening in society around injustice and inequality. You can use all these measures that are out there to get a better picture and not just a simplified measure on gender or race inequality. There’s all sorts of measures you can take in and understand on a deeper level.