EX101x - 7.1 - Movie Example DelftX Data Analysis to the MAX English English (auto-generated)

EX101x - 7.1 - Movie ExampleYouTube↗ IA↗(long shot)
Education 10.2K views 1 likes 0 dislikes 18 May 2015

DelftX Data Analysis to the MAX 1K subscribers N/A YouTube↗

Click to Play Video

In previous videos, we've talked about problems with spreadsheets and how to

in previous videos we've talked about problems with spreadsheets and how to

address them with testing.

address them with testing but

But spreadsheets just aren't always the right solutions -

spreadsheets just aren't always the

there are problems that you can come up with in your

right solutions there are problems that you can come up with in your work that

work that are just not solvable with a spreadsheet.

are just not solvable with a spreadsheet

And those are the types of problems that we're going to talk about today -

and those are the type of problems that we're going to talk about today when not

when not to use a spreadsheet. So over the past few weeks you've learned a lot

to use your spreadsheet so over the past

few weeks you've learned a lot about me

and the things I like, like teaching and crafts beer,

and the things I like like teaching and

but there is one of my hobbies that we haven't talked about, and that's going to

craft beer but there's one of my hobbies that we haven't talked about and that's

the movies. I really like the movies! I even have a subscription

going to the movies I really like the movies I even have a subscription to the

to the movie theater in my city that I can go to all movies

movie theater in my city that I can go

unlimited. So this week's exercises will be all about

to all movies unlimited so this week's exercises will be all about a little

a little movie database I've made in a spreadsheet, where we have movies,

movie database I've made in a spreadsheet where we have movies the

the studios that made the movies, the rating of the movie, so when you can see it,

studios that made the movies the rating of the movie so when you can see it the

the year that the movie has been released in, and

year that the movie has been released in

the type of movie, the genre, so the action, adventure... And we have

and the type of movie to genre so in

action-adventure and we have a lot of

In addition to the movies in our database we also have actors

data in addition to the movies in our database we also have actors and the

and movies that they have played in. So, here you can see that Andre 3000

movies that they have played in so here you can see that annually 3000's played

has played in a Volcano movie. So it is a nice little database for movie information

in a volcano movie so this is a nice little database with movie information

that we can analyse. So there are a lot

that we can analyze so there are lots of

of analyses we can do on this data. For instance, we can analyse the

analyses we could do on this data for

studios that actors have worked for.

instance we could analyze the studio's that actors have worked for we could be

We could be interested in the question: "what actor in our database

interested in the question what actor in

has worked for the most different, the the highest number of different studios?"

our database has worked for the most different the highest number of different Studios so we can get the

So we can get the information in from a studio with a VLOOKUP,

information in from a studio with if you

this should all be easy for you if you've watched all of the videos.

look up this is true it will be easy for you if you've watched all of the videos

So we add a VLOOKUP, and we get the information of

so we add a vlookup and we get the

every studio associated with a movie. And then we can select

information of every studio associated with a movie and then we can select all

all of that data, there it is, we can select all of this data

of that data there it is we can select

and add it to a pivot table. This should also

all of this data and add it to a pivot table

be a piece of cake if you've watched all videos. So,

there should also be a piece of cake if you've watched all videos so we add our

we add our data to the data model, because we're going to want to use a

data to the data model because we're

going to want to use these things count

to calculate the different number of studios

to calculate the different number of

that people have worked for. So, here's our pivot table,

studios that people have worked for so here's our pivot table we dragged in

we drag in the name of the actor, there it is,

name of the actor there it is and then

and then we drag in the studio,

we drag in the studio and we get the

and we get the number of studios that people have

number of studios that people have

worked for. And there's one little

worked for and this one little thing

thing that we still need to change, it's:

that we still need to change is we need

we need to go to pivot table properties and we say "we don't want to

to go to the pivottable properties and

have the count,

we say we don't want to have the counts

but we want to scroll down, we want to have the Distinct Count".

but we want to scroll down we want to have two distinct counts and if this is

And if this is done calculating... Takes a while...

done calculating takes so I'll now we

Now we can sort and then we can see that Christopher Lee has worked for the

can sort and then we can see that Christopher Lee has worked for the

highest number of different studios, I think it's 42. Yes, there it is! It's 42!

highest number of different studios I think it's 42 yes there it is it's 42 so

So, we have a lot of data in our database and we can analyse it in different ways,

we have a lot of data in our database and we can analyze it in different ways

using VLOOKUP and using pivot table, we know that by now.

using if you look up and using pivot table we know that by now

But let's try to ask ourself a different question:

but let's try to ask ourselves a

we would like to know for all of the actors not the number of studios that they have

different question we would like to know for all of the actors not the number of

worked for, but we'd like to know for every actor

studios that they have worked for but we like to know for every actor their bacon

their Bacon number. I thought Bacon number was like a very familiar concept,

number I thought bacon number was like a very familiar concept I did the same

I did the same exercise in my class, here in Delft, for 40 students,

exercise in my class here in Delft for

and I said: who knows what a Bacon number is? And, like

40 students and I said who knows what a bacon number is like two hands went up

two hands went up... So apparently, it's a little bit of a movie-geek thing, so I'll

so apparently it's a little bit of a

explain to you what it is.

movie geek thing so I'll explain to you

An actor's Bacon number is the number of steps that an

what it is an actor's bacon number is the number of steps that an actor is

actor is removed from Kevin Bacon. So Kevin Bacon himself has Bacon number 1,

removed from Kevin Bacon so Kevin Bacon himself has bacon number

and someone that has played in a movie with him, for instance,

one and someone that has played in a movie with him for instance at Harris in

Ed Harris in Apollo 13, he will have Bacon number 2.

Apollo 13 he will have bacon number 2

Someone that has played in a movie with Ed Harris,

someone that has played in a movie with

like Nicolas Cage in one of my favourite movies, The Rock,

Ed Harris like Nicolas Cage in one of my favorite movies The Rock will have bacon

will have Bacon number 3, because his distance to Kevin Bacon

number three because his distance to

is 2 hops via Ed Harris. So in this way we can chart everyone who has played in

Kevin Bacon is two hops fire at Harris so in this way we can chart everyone who

has played in movies based on their

based on their distance to Kevin Bacon. So the number of hops, the number of steps

distance to Kevin Bacon so the number of

from Kevin Bacon

hops the number of steps from Kevin

defines the Bacon number. We should be able to calculate this information in

Bacon defines the bacon number we should be able to calculate this information in

our little movie database, right? Because we

our later movie database right because

have the information -

we have the information we know who has

we know who has played in what movie, but

played in one movie but how to get this

how to get this out? How are we going to process the information in such a way that we get a

out how are we going to process the formation in such a way that we get a

nice little list

nice little list of each actor with

of each actor with their Bacon number?

their bacon number so I challenge you

So I challenge you before you watch the rest of the videos

before you watch the rest of the videos

to try this. And don't try it on this huge database,

to try this and don't try it on this huge database but try it on this little

but try it on this little database. We will put this online,

database we will put this online in the

in the course website, it's just easier to play with such a little spreadsheet

course website it's just easier to play with such a

than with an entire list of

little spreadsheet than with an entire a

thousands and thousands of actors. So, please don't watch the next video yet,

list of thousands and thousands of actors so please don't watch the next

download this, play with it, and try to see if you can come up with a way

video yet slave download this play with it and try to see if you can come up

to analyse the Bacon number for each actor. Good luck!

with a way to analyze the bacon number for each actor good luck

Similar videos:

Ex101x - 7.2 - The Exel way
DelftX Data Analysis to the MAX
9.2K views 1 likes 0 dislikes 18 May 201532% match

Ex101x - 5.3 - Filter
DelftX Data Analysis to the MAX
9.5K views 2 likes 0 dislikes 04 May 201527% match

Settings Privacy About Contact Donate