Linear Regression in Tableau. Part 2, Groups

In the first part of my articles on linear regression, “Linear Regression in Tableau, Part 1. Time Series”, I looked at time trends. Most often, however, regression is used to study the relationship between two quantities, for example, budget and profit. In this case, both values ​​have the same units of measurement (euros or dollars, for example). It is clear that budget are primary measure, and profit is secondary one, that is, it depends on bugget. In this article, I’ll talk about finding group trends using movie data as an example. We all know about movie budgets and the box office that makes films – this is often reported in the media. In addition, there are various rating systems, for example, IMDB or TMDB, in which movie ratings are determined on a ten-point scale and are based on the audience’s vote. The TMDB rating is quite an authoritative indicator, and on its basis the viewer can choose films so as not to waste time on bad films. Now let’s ask ourselves questions:

1. Will the film’s profit grow linearly as the film’s budget increases?
2. Is there a difference in the correlation of profit with the budget of films of different genres (action, fantasy, etc.)?
3. Will the trend be upward linear when comparing the TMDB rankings to the box office?

We asked ourselves some interesting questions or, in terms of statistics, built hypotheses. Now we must either confirm or deny them. I found The Movies Dataset at kaggle.com. This data contains information on 45,000 films by the end of 2017 in different languages, 26 million movie ratings from 270,000 viewers on the MovieLens resource. In addition, the data includes average TMDB movie ratings, box-office gross, movie budgets, and other metrics.

In this article, I will show you how to create a tool for finding relationships between metrics and breaking data into groups. The final visualization that we will be doing looks like this:

The screenshot shows a scatter plot that characterizes two movie rating systems: TMDB (ten-point scale) and MovieLens (five-point scale). The coefficiend of correlation between the two rating metrics is high in most groups, that is, viewers vote roughly the same for most films. But there are films that deviate a lot from trends.

1. Creating a Scatter Plot with Groups

The correlation between two values is almost always visualized using a scatter plot. To build it in Tableau, you need to connect it to data files. The data contains several files, we only need the files movies_metadata.csv, ratings.csv (this file stores reviews and ratings from the MovieLens resource) and the links.csv file to link the first two files:

Note: the ratings.csv file contains more than 20 million rows with each review – that’s a lot, so I reduced it to 5000 lines by aggregating the ratings and the number of voters. This was done in Tableau Prep.

The data contains the fields Budget and Revenue. Let’s make the Profit field:

[Revenue] – [Budget].

Now let’s make the Profit vs Budget scatter plot in Tableau:

By adding a trend line to the chart, we will see a strong positive trend, that is, as the budget increases, the profit increases. This is how we confirm our first hypothesis. But everything is not so simple, and we do not see details in the general trend. Let’s see the trends of films in different languages by adding the Original Language field to the granulation. We see different trends:

So we split all the films into groups. Let’s see the trend of a group of films in English:

This trend is positive, and in the general picture of English-language films, profits increase with the growth of the budget, that is, most films are profitable. Now let’s see the trend of films in Russian:

Here the trend is downward, movies become unprofitable when the budget grows. In this example, we made sure that the overall trend does not always correspond to the group trends.

Note: the initial data source contains complete information not for all movies, so I removed from the data movies with empty fields in the budget, genre, etc. As a result, 5230 movies remained.

2. Complex Groups

In the example above, one movie was in only one group (original language). In this section, consider an example where a movie is included in several groups. Let’s say we want to see trends by genre of films. One film can belong to several genres at once. For example, the movie “The Hobbit: An Unexpected Journey”. It’s fantasy, adventure and action at the same time. In the Genres field, it looks like this:

[{‘id’: 12, ‘name’: ‘Adventure’}, {‘id’: 14, ‘name’: ‘Fantasy’}, {‘id’: 28, ‘name’: ‘Action’}]

There can be up to six such combinations of genres in the data. Accordingly, when dividing into genres, you need to include one film several times in genre groups. But you cannot just add the Genres field to granulation – this way splitting into genres will not work correctly. Therefore, we modify the data. In the dataset, you need to put one movie and genre in each row, that is, one movie row with six genre values will turn into 6 rows. This can be done in Tableau Prep using Pivot operation. Let’s split the Genre field into columns according to the combination of symbols }, {.

CASE [Measure Y Parameter]
WHEN ‘Budget’ THEN [Budget]
WHEN ‘Revenue’ THEN [Revenue]
WHEN ‘Profit’ THEN [Profit]
WHEN ‘MovieLens Rating’ THEN [Rating]
WHEN ‘Vote Count’ THEN [Vote Count]
WHEN ‘Runtime’ THEN [Runtime]
END

Now you can choose a couple of any measures and watch linear trends: