-: All right, so once we have our data all cleaned up,
we can actually start to analyze it.
And so let's think about some of the things we can do
when we're doing data analytics.
And to start, you can take the same steps
we started with data cleaning,
which is filtering and sorting.
Like get an initial sense of the minimum and maximum values,
not to identify things to delete or whatever,
but to literally find out the range of our data set, right?
We can also do things that look like summary statistics.
Always a great place to start with data analysis
is to literally get those summary values,
minimum, maximum, average, sum, et cetera,
of any of our numeric columns.
And even for the non-numeric columns,
maybe you're getting a count of things,
a count of like true/falses or things like that.
So, summary statistics guided by your questions, right?
I don't need to count the number of actors
in all these movies unless that's part of my question.
But if one of my questions of the dataset is
how many actors appeared in more than five of these movies?
Then I'm gonna be looking at data like that, right?
Always guided by your questions.
One of the other things that I always, always do
at the very beginning of just about
every data analytics project
is I like to look at distributions of my data set.
Distributions meaning the entire distribution of the values.
So for instance, run time.
I have these run time numbers of these different movies.
What is the distribution of the run times?
I bet most of them are clustered around two hours,
90 minutes to two hours and 15 minutes.
I'm just gonna throw numbers out there. I don't know.
I bet very few or more than three hours,
and I bet very few are under say, 80 minutes.
I'm guessing.
If I do a distribution,
I can actually see how big that spread is,
whether it's clustering at one end or the other, et cetera.
It gives me a sense of the data overall,
which is helpful on its own,
but then when I analyze it and I find,
ooh, this movie has a runtime of three hours and 47 minutes,
I know if that's weird or not, right?
So distributions are very, very, very powerful.
I also then can do deeper dives into all kinds of stuff.
Once I get my summary statistics, I do my distributions,
I do other things, essentially it's those five F-U's,
the Toyota 5 Whys of continuing to sort of dive
deeper and deeper and deeper.
Hopefully I discover insights that lead to new questions,
which I can then look into.
I can look into things like correlations also, right?
Is there a correlation between runtime and movie quality?
As an example.
Does time play a role?
Do the quality of movies change?
Are the newer ones better or worse than the older ones?
Are there any variances from the standards, right?
Any particularly long movies or short movies
over different time periods,
et cetera, et cetera, et cetera.
So, what we're gonna do next,
another sort of thing I want to teach you, is pivot tables.
Pivot tables are another really fantastic tool
that you can play with in order to find
insights in your data.
Again, PivotTables are built into Excel,
both on PC and Mac.
I'm gonna be on my Mac now,
so you'll see a slightly different interface
than we looked at before.
But pivot tables are a great way to bubble up insights
in your data.
And I have to start by saying
there's a secret to pivot tables.
Any of you who have ever tried
to create pivot tables before,
especially if you were trying to learn it on your own,
were probably confused.
I know when I first started using PivotTables, I was like,
"What the heck's going on here?"
It's very weird and confusing.
But I took a course on LinkedIn learning,
actually not one of my courses, somebody else's,
and it taught me the secret.
And the secret is amazing.
The first thing to think about with the pivot table
is what are you counting?
What are you doing math with?
That is your value.
Once you figure out what goes in the values part
of the pivot table, everything else is easy,
but sometimes what you're counting
isn't necessarily obvious.
And I think we'll find an example of that
when we play around with this stuff.
So I'm gonna show you how to answer some questions
in the IMDB dataset using PivotTables.
Starting off with we're gonna try to figure out
the total adjusted gross for the entire dataset.
If we look at all top 1,000 movies,
what is the total adjusted gross?
So, here's the data set.
And by the way, this is a data set
that I've already cleaned up.
And you'll notice that the cleaning that I did
for this final version, which by the way,
you all have access to, and as I mentioned before,
undergrads, you can use this data set for your exercises.
And so you'll notice a few things here.
First of all, let me clear any sorting and filtering
so that we can see all the data.
So, you'll notice that there's an index column.
I did use that feature in Power Query to create an index,
but you'll notice I do only have one row per movie,
because I actually ended up splitting the data
into two different tables.
So each movie in this primary table has, you know,
an index number, just so I can refer back to it numerically,
title of the movie, release year, runtime, IMDB rating,
I kept the overview in there, I could have deleted it.
Meta score, number of votes, gross CPI, adjusted gross,
and I have a duplicate of my year column
for reasons that I'll explain later.
Don't worry about it for now.
I created a separate table, I forgot, for each movie
so I can see the index number.
Let me zoom in on this so you can actually see it
like normal humans.
So movie zero corresponds with one of the movies
in this other list here.
So in fact, I can sort by the ID.
And movie zero is this Indian "Dil Chahta Hai" movie.
And so movies zero, that one, released in 2001,
and I have some of the same data in here.
Meta rating, IMDB rating, meta score,
gross, director, different actors,
and also CPI and the adjusted gross.
The reason I duplicated some of those values here
is cause I wanted to look at correlations
between the people and some of these values.
And so it was easier to do it in the same table.
There are other ways to do that too,
but it was just faster and easier to do it this way.
Long story short, that's the data that I have.
I'll show you this data set later on
to explain the full analysis that I did,
but let's just do some quick pivot tables.
So like I said, what are we trying to do right now?
We're trying to figure out the total adjusted gross.
Now you could say, "Well, if we go to the main dataset here,
and if I go to the adjusted gross column,
I could simply write a formula,
go all the way to the bottom, right,
write a formula equals sum adjusted gross,
and get the answer, right?"
Or I can even just select the whole column
and see the answer right here.
The sum, 115 billion, 677 million, et cetera.
That's true. I could just do it that way.
That'd be a simple, fast way to do it.
But I'm gonna use pivot tables to do it,
'cause we're trying to learn pivot tables, right?
So if I click anywhere inside of this table,
doesn't matter where,
I don't have to select the whole table,
as long as I'm clicked inside of it,
I can go to the insert tab up here and insert a pivot table.
And I'm gonna create it in a new worksheet,
and it's already selected the data that I want to work with.
And so I'm gonna say, OK.
So it creates a new worksheet
with an empty pivot table in there.
So once again, what am I trying to answer?
I want the total adjusted gross.
So what am I counting?
What am I doing math with?
Obviously the adjusted gross column in this case.
So, I can go down here.
The adjusted gross is one of my options.
That's my value.
So in the pivot table dialogue box over here,
there are four different boxes,
filters, columns, rows, and values.
When I figure out what I'm counting, doing math with,
that goes in the values area.
Click and drag, and boom.
Let me zoom in on this one also so you can see it.
I get the sum of the adjusted gross
and it's using scientific notation,
but I can always change that so it displays it
just as a number and I don't need any decimal places.
Say OK, and I can see the number there.
Now, maybe I'll want to change the formatting
so it uses commas as a thousand separator,
so I can actually read the number like a normal human.
All right, 115 billion plus,
which matches the number that I saw earlier.
So in other words, I have a pivot table,
it's a ridiculously simple pivot table
that tells me the sum of the adjusted gross.
Exactly the question.
Which brings up an important point.
Sometimes the phrasing of your questions
will or won't lend itself to exactly
what you see in your tools.
I said I want the total adjusted gross,
but what I'm doing in an Excel standpoint
is a sum of the adjusted gross column.
That's what we do, right?
We ask questions, we figure out which columns,
which variables apply to those questions,
and we figure out the action, the math that we're doing.
Are we adding it up? Are we doing an average?
Are we doing this plus this equals that?
Whatever the case may be.
So, we've already done our first pivot table.
So what else do I want to do?
Next challenge is I want the share
of adjusted gross by year.
So what am I counting now?
The share of adjusted gross by year.
I think I'm still counting the adjusted gross, right?
And I'm gonna translate some of the language here.
What do I mean by this share of?
I guess I mean the percentage, right?
The percentage, the proportional share,
those are the same idea, by year, organized by year.
So it sounds like what I want to do is I want to organize,
and I'm just going to keep building
on the existing pivot table, no reason to replace it.
So what I think I wanna do is I want to pull
the released year and drop that into the rows.
So I'm organizing the adjusted gross by year,
exactly as phrased in the request, right?
But as you'll see, this says the sum of the adjusted gross,
right, this is where I started from the sum, the total,
but the question says I want the share
of the adjusted gross by year.
And so I guess I do want the sum, the number,
but the share of.
So does this really answer my question the share?
I guess 82 million, 15 million, whatever.
I get the share, but usually, like I said before,
I don't really know what 1.6 million,
1.7 million is out of 115, or billion rather.
And so share usually implies percentage.
So how am I gonna show this as a percentage?
Well, it's actually built into the pivot table.
I can go over here with the values,
and if I just click on this little I,
I get this dialogue box.
So PivotTables, all the action happens
in like two or three or four different places.
Number one is this area on the right hand side,
where we drag things into those four boxes.
This is another one, this dialogue box,
the PivotTable Field Setting as it's called.
So I can summarize by the sum,
which is the default if it's a numeric column,
as you can can see, that's what it did automatically.
I can also get the count, the average,
the minimum maximum, et cetera.
I can also show data as, see this other tab over here.
Right now it's not performing a calculation,
but I can change this and have it do
a percentage of grand total.
If I do that and I say OK,
look, now it's broken into percentages.
So in our data set, 1920 represents 0%
of the adjusted gross.
1939 actually is like 3 1/2%,
for some reason a lot of good stuff happened there.
Or maybe one good movie, I'm not sure.
That's a whole question I could ask the data, right?
And more recent movies had a bigger impact
on adjusted gross, but not universally, okay?
So apparently I don't have any movies from 2020
in the data set, maybe,
or at least done with the gross factored in.
Long story short, I think this answers that question,
the share of adjusted gross by year.
Let's look at the next question.
The average adjusted gross by year.
I just mentioned this one.
In the pivot table,
I can look at Summarize By, this tab in here,
and instead of the sum, I can change it to average.
And I can say, boom, now the average, but as a percentage.
What's happening there? That's weird.
1929 is 59%, 1925 is 30%.
It looks like I'm getting,
in fact, this one is 149%.
What does this even mean?
Well, if I'm taking an average number
and dividing it by another average number,
that's a weird way of looking at it.
So I probably don't want to do
the percentage calculation anymore.
So let me turn that back to no calculation.
And now I can see the average of adjusted gross as numbers.
And yeah, now I can see it's just a number.
The average adjusted gross for this year is some number.
For another year, it's a different number,
et cetera, et cetera.
So sometimes percentages don't really make sense,
other times they do, fair enough.
How about the highest adjusted gross by year?
Once again, just go back into the field value settings,
instead of an average, look at the max.
Easy.
So you can see how this works.
PivotTables make it very easy
to quickly get some basic statistics
and even organize those statistics,
as we're doing here in this example by year.
How about the most common run time?
So this one's different.
This one, the question is what are we counting?
What are we doing math with?
And again, if this is a live session,
I would ask you all to answer this.
And some of you would answer, "Well, I guess runtime, right?
That's a numeric column. We must be counting that."
And you know what the answer is?
Nope. That's not what you're counting.
That's not what we're doing math with.
That's what we're organizing by.
The question is what is the most common run time?
Which run time happens most frequently?
In other words, what we're counting in this case is movies.
So, again, in data analytics it's really important
to translate what you're asking into different words
sometimes to understand what to do about it.
So another way of saying what's the most common runtime
is to ask the question, how many movies have each runtime?
Right? How many movies are an hour long?
How many movies are an hour and a half long?
Et cetera, et cetera, et cetera.
And then which one is the most common?
So we're counting movies.
So, I'm gonna leave this existing pivot table where it is.
I'm gonna create a new pivot table.
So I'm gonna go back to the source data.
I'm gonna click into it.
Oh, sorry, not the people one, I'm gonna go into this one.
And I'm gonna say Insert, PivotTable.
And I'm gonna drop it into an existing worksheet,
but I'm gonna click into it so it drops it
sort of next to this other one over here.
And so it creates a new pivot table
right next to the other one.
And so I'm counting movies.
So how about I count the index column?
That's my unique numeric representation of different movies.
Movie zero is that Indian movie,
movie one is a different movie,
et cetera, et cetera, et cetera.
But look what it's doing.
Because it's a numeric column, it's trying to add 'em up.
The sum of the index values is a useless number.
What I want is the count.
So once again, field value settings,
or field settings rather,
instead of the sum, let me change it to account.
And by the way, by default there's 999 movies, right?
Because it's the IMDB top 1,000, and don't forget,
we deleted "Apollo 13" cause it had that weird year.
It had that year of PG,
so I just deleted it for simplicity's sake.
So this is the total number of movies in the data set.
But what I want is the most common run time.
So I guess I want to organize by run time.
So lemme put the run time into the rows.
Okay.
Now I can see I have one movie
that had a run time of 45 minutes.
One movie with a runtime of 64 minutes,
et cetera, et cetera.
But what's the most common one?
Well, this is a really long list.
I could manually scroll through it,
look for double digit numbers,
and manually look for the biggest number,
but you know there's a better way to do that, right?
So maybe what I can do instead,
is sort by this column of course.
I can sort by clicking this little arrow over here,
right at the top of this Row Labels column.
And if I click that open, I can sort by not runtime,
but by the count of the index.
And I wanna sort in descending order.
And boom, there are 23 movies with a runtime of 130 minutes.
The most common runtime is 130 minutes.
End of discussion. Tied with this one.
There's also a lot of movies with a hundred minutes.
And there's some that are close behind,
and some others that are close behind that,
et cetera, et cetera, et cetera.
So long story short,
we've answered the question with a nice simple pivot table.
Now what about filtering? How do we filter our data?
There are a whole bunch of different ways.
So we're gonna look at the average adjusted gross
by year pivot table.
Do I still have that one?
This is a max. Let me go back and change that to an average.
'Cause that's what we said we're gonna look at.
Average of adjusted gross by year.
And I want to add a filter to it to find the average
adjusted gross for movies released in the 2000s.
Okay, so 2000 to 2009.
So how can I do that?
There's a whole bunch of different ways.
One way, see this box over here, Filters?
So I'm gonna drop the Dupe year into the filters.
This is one of the reasons I wanted this extra column,
'cause I can't have the year in both the rows
and the filters, for whatever weird reason.
So when I add it to the filter in the pivot table,
it added this little stuff up here in row one.
And so it shows me the name of the column,
and then it shows me this little dropdown,
and by default all of them are selected.
But I could just go in here and deselect all,
and then just manually select the 2000s.
So 2000, 2001, 2, 3,
4, 5, 6, 7, 8, and 9.
And so the question.
And so the question is answered, right?
The question was looking for the average adjusted gross
for the 2000s and there it is,
the average adjusted gross for the 2000s.
And of course I can see each individual year
if I do it this way as well.
So fine, that's nice.
That's one way to do it.
Adding the filter in the pivot fields.
I can also filter using the dropdown itself.
So let's say I get rid of this filter over here.
And by the way,
anything in the pivot table you want to get rid of,
you can just click it and drag it off to the side,
and let go of it, and poof it disappears.
So you can see now it's not filtered by year anymore.
The nice thing about PivotTables,
is I can filter right in here.
See this little dropdown where it says Row Labels?
Drop that open and I have the same filter.
So once again I can just deselect
and then manually select just the ones that I want.
2000, whoops, 1, 2, 3, et cetera.
I'm not gonna do all of it, 'cause it just takes time.
So that's nice. I can do it that way.
There's another way to do it,
which is I can actually use the between functionality,
which is kind of of sweet.
Instead of manually selecting,
what if I say I want to filter by label or value?
See these two things?
Now this gets a little weird.
By label means by the row labels,
by value means it'll filter by the adjusted gross column.
So keep that in mind.
I can actually filter from this little dropdown,
either one of those columns.
So I wanna filter by label, the actual row labels,
and I want to filter between
2000 and 2009.
And I say OK, and boom.
Once again it did the same thing.
So I can manually select, I can group, I can do between.
And as you saw in there, by the way,
I probably showed it too quickly.
Between isn't the only option.
I can filter less than, greater than, less than or equal to,
contains, like essentially a word search,
begins with, all kinds of different ways
of actually doing smarter and faster filtering.
Last but not least,
and I'm gonna clear this filter just to show it to you,
and maybe this isn't last but not least,
but certainly one of the last ones,
is I can filter, or not exactly filter,
but I can essentially use what's called group buy.
In this PivotTable Analyze tab,
there's this group selection button.
And if I have a year down here selected,
so the active component of the pivot table is the years,
and I click Group Selection and actually select it,
it's gonna group and by default
it's gonna say starting at the earliest year
and ending at the most recent year.
And it's gonna group, by default, by 10.
So if I just leave the defaults in place,
look what happened.
It's grouping by 1920 to 29, 30 to 39,
et cetera, et cetera, et cetera.
And so the question was by the thousands what's the average?
Boom, there's the number.
So, this is kind of nice,
But what if I wanted to compare the thousands,
the 2000s rather, to all the movies before
and all the movies after?
Now I have to like manually add these ones together.
That's kind of a pain.
Well the nice thing is that Group By
is a little bit smarter than that.
I can go back in here, Group Selection,
instead of starting at 1920,
if I say start a 2000 and end at 2009.
So I'm literally saying just group by that one section,
look what it does.
It actually said, "Okay, this is the one you care about,
but there's earlier stuff here.
What should we do with those?
I guess we should group it all together
and give you that number.
And what about the stuff after that?
We'll group all those together and give you that number."
So group I is a very powerful and useful feature,
definitely worth getting used to using.
So there are different ways to do stuff in PivotTables
and it's super fast and easy, click and drag, et cetera.
Three more things I want to talk about.
One is dates.
Now, in this particular data set, we don't have dates.
All we have is like the year.
So I can't demonstrate this, but suffice to say this,
if I'm in a pivot table, and we just did it,
and if I had an actual fully defined date in here,
like January 3rd, you know, 1992,
and that was what all the values were in the column.
if I dragged that into the rows as an example,
it would automatically, not only show me by date,
but I could even look at the values by year,
by quarter, and by month.
So years are treated very intelligently
in Microsoft software generally, PivotTables specifically.
So, check that out, play with that.
There's also things like the top X.
Let's say. Let me clear this grouping over here.
Let me go back to PivotTable Analyze group,
and I'm gonna ungroup.
And let's say that what I wanted to do
is to look at the top X. (coughs) Excuse me.
What I can go in here and actually, by value in this case,
I'm gonna say I wanna look at the top X of adjusted gross.
So I'm gonna go into By Value, adjusted gross.
Top 10, but really it's the top X,
and I wanna look at the top 10 items.
So in other words, it'll automatically give me
the 10 years that have the highest average adjusted gross.
So this is 10 items, the 10 years,
'cause that's the organizing principle here,
by adjusted gross.
I can also change it to the top percentage.
What are the top, the 10% of adjusted gross
you can account for in just these two years?
That's interesting.
So different ways of looking at your data,
and a bunch of different sort of functions,
all using built-in functionality in PivotTables.
Super cool, really powerful.
Last but not least, I do wanna show one other thing.
There's also the ability to create calculated fields.
So let's say I had a pivot table like this one,
the number of movies and the runtime.
And let's say I had, I wanted to create a calculated field,
a new field that somehow did some weird math.
For instance the runtime, I'm gonna make something up here.
The runtime, I wanna calculate the number of seconds
for runtime for some weird reason.
I can create a new field.
So I go into PivotTable Analyze,
and I go into Fields, Items, & Sets,
and I click on Calculated Field.
And this is sort of like we added a column
in Power BI when we were doing data cleaning.
I can create a calculated field and I can name it.
So I'm gonna call it run seconds, for whatever reason.
And the formula is going to be
take the run time
and multiply it by 60.
'Cause right now it's in minutes.
To turn minutes into seconds you multiply by 60.
And I say, OK, and look,
it creates run seconds, right?
So it actually turned, you know, the minutes into seconds
and I can see that number there.
So, what's cool about this too is that that field
then is in this PivotTable Fields box,
so I can use this field in other ways
within the pivot table.
Powerful stuff. Definitely worth checking out.
There's more stuff you can do in PivotTables too,
but definitely use something you should play with
and get comfortable doing.
You'll be able to do data analytics much more quickly
and easily with PivotTables.
All right, don't forget,
data analysis is about asking questions of your data.
It's not about jumping in and starting analyzing stuff.
Why are you looking at the numbers that way?
What question are you trying to answer?
And it's also about, like I keep saying,
being able to translate those questions into fields,
metrics, values that you have and the words
that help you understand what you're really trying to do.
Share. Ah, you mean percentage, right?
The average. Okay, I want the true average or the typical.
Ooh, how do we define typical?
Could be the average, could be the median,
it could be the mode, right?
So translation is a big, big, big part of this.
And like I said before, sometimes you're forming
explicit hypotheses which you're testing, but not always.
Very important in data analytics.
Sometimes you are neck deep in the data,
you're so focused on the data that you forget
to step back and ask those questions.
Really ask yourself, what am I trying to do?
Why am I trying to look at it this way?
You don't just explore data to explore data,
you're trying to really answer specific questions.
Sometimes you are exploring to find where the insights are,
but even then you're essentially answering questions
even if you're not defining it that way.
And don't forget, there are a million and one
questions to ask of your data,
but some of the key ones
are the ones that we talked about earlier.
What's good or bad? Should I be looking at the rate?
Et cetera, et cetera, et cetera.
So, what did I learn from the IMDB data?
Well here's the data set,
and I just wanna point out a couple things
before I even talk about what I found,
which is, you'll notice that I have the IMDB top 1,000 data
that you can see pulled in from Power Query.
And you'll notice I have one row per movie.
I ended up splitting up the data in a different way
than as I demonstrated in Power Query earlier.
I have one row per movie, I have the index.
So I actually did end up adding an index column.
So now each movie has a number associated with it,
and you'll see how I use that later.
I have the other fields released year, runtime,
IMDB rating, overview.
I didn't bother deleting that.
Meta score, votes, gross, CPI, adjusted gross.
And I have a column in here called Dupe Year,
which you'll see how I use that later on.
I also have the CPI table, again,
because it was in Power Query so it got pulled in.
And then I have these two relational tables,
which are essentially where I pulled out,
in this case the people, so the actors and directors,
and each of them is associated with one movie.
So movie zero is movie number zero.
And so if I sort this table by the index column.
Let me just show you that.
Movie zero is that Bollywood movie that we saw earlier,
"Dil Chahta Hai."
And so these people in this other table
are the four actors and the director for that movie.
You'll also notice in this people table
I have some of the other fields,
the release year, IMDB rating, et cetera.
The reason is because I wanted to do like correlations
between people and some of this data
and it was easier to have it in the table
rather than have it separately.
You could do it multiple ways,
it was easier just to pull those fields in here
and sort of duplicate 'em.
Long story short, I also have
a separate table for my genres.
I know when we're in Power Query, I essentially demonstrated
a way of unpivoting those columns.
I did unpivot them, but in a slightly different way.
I unpivoted them into this table,
where I once again have the index column
and then some of those other fields again.
But then I have, again, our genres in our multiple rows.
So similar idea, but the data does look different
from what I showed you earlier.
Okay, so what did I find in the data, was the question.
And the first thing I did
is what I've recommended you think about doing,
which is I ran a bunch of summary statistics.
So I looked at things like the runtime average,
IMDB rating, meta score, votes, adjusted gross,
all of these being average numbers.
I also did like min/max for some of these things,
and maybe even all of these things, I can't remember,
playing around with a lot of this stuff.
And, as you'll notice, my rows here are decades.
And you're seeing this after I did a bunch of work
and sort of played around with things.
Eventually I was looking at it I think over time,
generally year by year.
And I decided at one point to look at it by decade.
And that was sort of one of my first ah-has,
one of my first insights,
was that the adjusted gross revenues for the early 2000s,
not the early 2000s, but 2000 to 2009,
was a much lower number than for all of the other years.
So that became sort of the spark
that guided me through a bunch of the rest of my analysis.
Not everything,
but you'll notice I have this little hypothesis up here.
Something strange going on with the movies in that era.
Was it the recessions?
Or maybe people were on the webs?
It was early web years instead of going to the movies.
Or something else? Dot, dot, dot question mark.
I never figured out what it was.
I didn't do the full analysis here.
I started the analysis, 'cause you all can play with this
and run with the analysis that I've done,
or take it further.
It's entirely up to you.
And by the way, when when I say you all,
undergrads, again, are able to use this for your project,
graduate students need to use your own research.
Okay, so summary statistics, min/max, number of votes.
I noticed that there were a lot more IMDB votes
for that same time period, but again, not sure why.
Sometimes you'll notice I broke out just that decade
year by year to see if any interesting patterns
popped out at me because hey,
I'm noticing something happening.
Maybe if I look at that decade,
maybe I'll find some anomalies.
For instance, many fewer votes during this one year, 2003,
and, by the way, some really high number of votes
a few years after that.
What does all this mean?
I have no idea, but I'm just noticing things
when I'm running summary statistics.
Min/max, meta score, also much lower minimum meta score
for that decade.
The highest one doesn't even hit a hundred for some reason.
I dunno if that's significant,
but this certainly feels significant.
Again, looked at it by those years
to see if anything popped out.
And sure there was some at the bottom of the range,
some at the top of the range.
You know, nothing too earth shattering here.
I'm not gonna explain every finding here,
but feel free to poke around this.
You'll also notice that I did do some visualizations.
In this case, just a couple of box and whisker plots
to look at if we're looking at all of the votes cast,
counting how many votes were cast
for all the movies in the data set,
the median is a fairly low number,
a hundred thousand maybe ish.
And then you know the mid quartile range
is a decent but fairly small spread all below 500,000,
and then some real big outliers.
Adjusted gross, same thing.
Most movies, the median movie makes very little money,
and then some outliers way higher than that,
especially in terms of adjusted gross,
adjusted for inflation.
So that's my summary statistics.
That sort of get led me to my first hypothesis
that I really wanted to explore more.
So I also have this other tab here called 2000s.
And in this tab I just explored that decade for a while
and I looked at it from a bunch of different directions.
And again, I'm not gonna tell you everything that I found,
you can poke around this file and look at it yourself.
One thing I'll point out is I copied and pasted
some of the data over here to make it easier
to generate things like scatter plots.
You know, I'm not gonna go into why I did that,
but you know, essentially you just made it easier
to do it this way.
I did this scatterplot adjusted gross over time
to see if I could visually see any of those patterns.
And, you know, adjusted gross is sort of going up
when you look at the outliers,
but overall it just clusters really low,
like we saw in that box and whisker plot.
And I did look at a box and whiskers
of adjusted gross over time to see if there were
any patterns in the trends overall.
And clearly the Golden Age of cinema, the 1960s ish here.
Whoops. Now what happened there?
Interesting how huge the range is of adjusted gross.
Much larger than it is for more recent years.
So some of these movies, or some of these years had movies,
that really accumulated value over time
or accumulated gross revenues
and maybe partially due to inflation,
I'm not sure exactly what it means,
but more recently, much less so.
But again, the 2000s look a little bit smaller, lower,
overall than for instance the 2010s
and maybe the similar to the 1990s.
Long story short, not sure what I saw there.
I also looked at things like the key people.
So Clint Eastwood appears in 20 of the top 1,000 movies,
either as a director and/or an actor.
Robert De Niro close behind, et cetera, et cetera.
I started to look at those by decade again,
and did see the 2000s.
It's different names in the different decades.
No big surprise there.
There may have been one or two overlaps, I can't remember.
But long story short, one of the things I looked at
was it does appear that there are more repeat actors
and higher adjusted gross revenue for later time periods.
Sort of implying that like the importance of star power
increased over time, which feels fair and right,
and the data seems to bear that out.
I also noticed that the genres shifted over the decades.
So I sort of sorted by the number of movies
falling in particular genres.
And looked at the top 5 and/or the top 10 and noticed
that like for instance, comedy moved up over time,
you know, by one or two points.
Crime and thrillers went down.
And the associated revenue is also increased.
So I have like notes that are explaining
some of the key ideas here and what I noticed.
Long story short, did not find great insights
into why this stuff is happening,
but I certainly did bubble up
that there's something going on in that decade.
So you can run with this data set as it is,
there is potentially stuff to talk about here,
and certainly stuff to dig into a little bit deeper.
Maybe you'll find more insights
if you spend a little bit more time with this data set.
So, I hope this is helpful and I hope that you find
this project and this data set interesting to work with.
Plenty to start with here,
but clearly more room to play with.
Oh, and I completely forgot to mention,
I pointed out that I had this Dupe Year column over here
and that I was gonna explain it and I didn't.
So let me go back and explain that, which is this.
In the summary statistics,
I pointed out that I did all of these pivot tables
where I was grouping by decade,
and then I did some where I was grouping by year.
You will notice if you create pivot tables
and you use Group By on a certain field,
every pivot table you create will group by that in that way.
So all these pivot tables where I'm grouping by decade,
as soon as I created this pivot table,
this one was already here and already grouping by the year.
As soon as I created this one
and pulled the year into the rows,
it followed the exact same Group By.
So the Group By is sort of a universal thing.
And so when I wanted to do different grouping,
one by decade and one by actual year, I couldn't do it.
So I had to create that duplicate year column
and group that column by year.
And if I use that column, that field in another pivot table,
it's gonna group by year.
So that's why I have that duplicate there.
I might have done one other thing with it
that I can't remember,
but you'll probably notice it if you poke around the data.