Now that we've defined and formulated our question, it's time to gather the data. 1
So let's have a think about the kind of data that we're looking to gather. 2
First off, we need data on our feature, namely the movie budgets in US dollars. 3
The second thing that we need is data on our target, namely the movie revenue in US dollars. 4
So, where would we go about finding this kind of data? 5
Well lucky for us, there exists a website called "The Numbers" and their slogan is "Where Data and the Movie 6
Business Meet". 7
This sounds perfect for our project. 8
So let's check it out. 9
So this is what the website looks like. 10
If we go to Movies > Budgets and Finances, then we get the performance records of various films, and they've 11
even got some tables here on some common searches or common questions that people might be interested 12
in. 13
For example, they've got the biggest budget films right here. 14
And if we scroll down, they've got the movies with the lowest budgets to earn one million dollars at 15
the U.S. box office. 16
And I think a notable film here is El Mariachi, which apparently only had a production budget of seven 17
thousand dollars but made about two million in the U.S. box office. 18
If we click on this link here where it says "the complete list", then we get the entire table of all the 19
films in the database right here. 20
And this table is actually enormous. 21
It's very, very long. 22
There's quite a few different entries here. 23
And what I've done is I've selected all of these and I've copy- pasted them into a spreadsheet. Now, to 24
save you this kind of tedious work, 25
I've made a CSV file available under the lesson resources. 26
All you have to do is download it and open it in Excel or in Google Sheets. 27
And once you open this file, what you should see is the movies sorted by their rank. 28
So at place number one, we've got Avatar then a Star Wars film then Pirates Of The Caribbean. 29
All of these films are sorted by their production budget, 30
so column D. And if we scroll down, then we can see that there's a lot of entries in this file. 31
As a matter of fact, there's about 5000 entries in this CSV file. 32
So, this completes our data gathering step and we can move on to the next part of the workflow, namely 33
cleaning the data. 34
So, let's take a closer look at what we've actually got. 35
If I scroll down here, then I can see there's a lot of numbers, but occasionally we might spot something 36
a little bit odd. 37
For example, here in columns E and F we have two big fat zeros. Scrolling up, 38
I can see these are the columns for Worldwide Gross and Domestic Gross and yet this movie entitled Singularity 39
with a production value of 175 million dollars has zero revenue. 40
And the reason for this might be found in this column, our second column here. 41
What we have here is the projected release date. 42
So, end of 2020 is when this movie is due to be released according to this dataset. 43
And what they've done is they've put a placeholder of zero into these two columns instead of removing 44
this movie entirely. 45
They've included it in the data set with one hundred and seventy five million dollars production budget, 46
but because it hasn't made any revenue to date, they've added some zeros here. 47
Now, this might actually be a little bit of a problem for us, because we're looking to make a prediction 48
for our movie revenue. 49
And if our dataset is going to include films that have not been released yet and have big fat zeros 50
for the revenue, it might make our estimates a little bit unreliable. 51
Right? 52
So, one thing that might actually be worth doing, is checking how many films, out of the five thousand 53
three hundred, actually have a revenue of zero. 54
And we can do this by sorting by Worldwide Gross. 55
If I select my data here and then open the menu and you go to Data > Sort Range, then I can actually sort 56
by column number E. 57
And, since we don't want to include that first row in the sort, I'm just going to tick this box here and 58
then click Sort, and what we see here is that there's quite a few films that have a zero entry for Worldwide 59
Gross and Domestic Gross. 60
Now, why might that be? 61
Well, for some of them the release date is in the future, but looking at this column, we see that not every 62
film that has a 0 here is due to be released in the future. 63
So for example, this one here "Black Water Transit" was supposedly released at the end of 2008, and this 64
film also has a big fat zero in Worldwide Gross. Why might that be? 65
Now, one possible answer is looking at the description of the website. 66
So, if we go here then we see this line: "The data, to the best of our knowledge, is accurate, 67
but there are gaps and disputed figures". 68
So, some of these zeros might be gaps. 69
It might just be that the data is incomplete for some films, but I've actually looked up this Blackwater 70
Transit film and on Wikipedia I can see that in post-production there were actually quite a few lawsuits 71
and there was quite a lot of back and forth between the production company and the filmmakers and the 72
producers and so on. 73
So this film actually never got released and this release date here of end of 2008 was probably a planned 74
release date, but the movie actually never came out. 75
Now, this might be something that happened to quite a few of these films, and they might have a 0 for 76
various other reasons, too. 77
But, this poses a big problem for our analysis. If we're looking to predict movie revenue based on the 78
budget, then having these zero entries here is going to be a problem. 79
So, what I would suggest, given that some of these films were probably not released, other ones the data 80
might not be available, is that we should actually exclude the films that have 0 here for Worldwide 81
Gross and Domestic Gross. 82
We should select all of these entries and delete them from our dataset. 83
So if I come down here to row 358 and scroll all the way up, then I can select all of these rows, I can 84
right click and I can delete the rows. 85
We're going to exclude these rows from our analysis. 86
The next thing that we're going to do is, we're actually going to have to do some calculations and these 87
involve the Production Budget and the Worldwide Gross. 88
These other columns we're actually not going to use. 89
We're not going to use the Domestic Gross. 90
We're not going to use the Movie Title, or the Rank or the Release Date. 91
So what I would suggest is that, as part of the data cleaning process, we're going to delete these entire 92
columns. 93
So, we're going to delete these columns and we're going to delete this column right here. 94
That way we can trim our spreadsheet to what actually matters for our analysis. 95
The next thing that we need to do is we need to have a think about these special characters that might 96
be in the dataset, and by special characters I mean anything other than a number. 97
So, we're gonna be doing some calculations with all of this data and those calculations might be troubled 98
by the fact that there's a dollar sign here, or a comma. 99
This type of formatting could cause problems. 100
So, it's better to remove it and have a pure number in these cells, rather than all this extra stuff. 101
And the way that we can format all these numbers and get rid of these symbols is by selecting the columns, 102
going to Format > Number and then scrolling down where it says More Formats and going to Custom Number Format. 103
And here we can pick the second one down which is this decimal number and click Apply. 104
Now we've got no more dollar signs in our columns, so we're almost done cleaning our data. 105
The last thing I'm going to do is, I'm going to rename my column headings. 106
And the only reason I'm doing that is because I'm going to remove spaces and these custom symbols here - 107
the parentheses and the dollar signs from the headings to make our lives a little bit easier on. I find 108
that, as soon as I have special symbols or white spaces in my headings, I make a lot more typos when I've 109
referred to particular columns. 110
So for this one, I'm just gonna call this production_budget_usd, and this one here 111
I'm going to call worldwide_gross_usd. With these two names for our headings, I've 112
got very expressive headings, and yet there's no whitespace and there's no special characters. 113
This will make our life a little bit easier down the line. 114
So, in summary, for the data cleaning, what we've done is: we've looked for missing data, we've looked for 115
incomplete data, we've looked for inaccurate data, and we've done some tidying up by checking the formatting 116
of our data in detail. 117
And the reason we've done all of this is so that we don't have problems when we provide this data to 118
our machine learning algorithm down the line. 119
Now we can move on to the next step, which is exploring and visualizing our data. 120
Mind you, if we spot any more problems during this next step we might have to do a little bit more cleaning 121
as well. 122
So, you'll find that very often in practice, you're going to be doing the cleaning and the exploration 123
side by side. 124
I'll see you in the next lesson. 125
Take care.