Bollywood Movie Data Analysis: Descriptive Statistics in Excel

[Music] foreign [Music]

I am going to use a data set which is basically on Bollywood movies to explain how you can get descriptive statistics

such as measures of central tendency measures of variation and measures of shape let me first explain the data set

this data set is on Bollywood movies we have the release date of the movie the name of the movie and the release date

so we have four different seasons one is long weekend then we have normal season then we have long weekend and we have

festive season so these are the four different seasons during which the movies gets released then we have the

genre of movies such as romance Thriller comedy action drama and so on we have the budget of the movie this is in

crores of rupees and we have the box office collection again in crores of rupees we have YouTube views for that

movie YouTube likes and YouTube dislikes these are basically the number number of counts of views likes and dislikes now

if you look at this data set you have a combination of nominal scale as well as ratio scale for example release date and

Joiner they're all nominal scale and budget box office collection YouTube views YouTube likes and YouTube dislikes

or basically ratio skills or numerical values now I am going to start with the measures of central tendency if you look

at measures of central tendency there are three measures the first one is mean then we have median then the third is

mode so let us see how we can actually calculate mean median and mode remember most of these measures are valid only

for ratio scale for example average or mean we can calculate only for the ratio scale that means for Budget box office

collection YouTube likes YouTube dislikes and so on so let me first calculate the mean value for for this

variables mean in case of ratio scale is calculated using an Excel function

called average so all we have to do is enter average and here the data is from colon

F2 till F 150 so f 2 till F 150 so that is the average so the average budget for a Bollywood movie is

29.44 crores now I can actually copy this function for remaining columns so I can just drag it

I have the average for the remaining columns and the next column is basically the box office collection so the average

box office collection is 55.66 crores similarly you have the average YouTube use likes and dislikes

the average values now the next is I can calculate the median value for each of these values or features so the function

is median again we have to enter the array so this is from F to till f

150 ok and

when I copy this I get median values for all of them so the median budget is 21 crores that means 50 percent of the

movies have a budget of less than or equal to 21 crores and if you look at the box office collection the median

value is 28 crores that means 50 percent the box office collection is less than or equal to 28 crores and the remaining

50 percent and the remaining 50 percent it is more than 50 crores and similarly you have the median values for YouTube

views likes and dislikes I can go and also calculate more so here if you calculate more

again F two to f 150 I am going to drag that for the

remaining features so ah if you look at for budget the more distance that means the

highest frequency of budget is 10 there are many movies where the budget is 10 crores and similarly for box office

collection the mode is 40 that is the highest so the variable budget we are getting

more as 10 that means there are more number of movies for which the budget is done similarly

for box office collection the mode is 40 that means that has the highest frequency among the box office

Collections and it is interesting to note that for the YouTube likes when we entered the Excel function mode and we

chose that array we are getting n a that means there is no mode because each record there appears only once right

then you have YouTube likes and dislikes so here for both these variables the more values one that means one appears

more number of times in these two cases for both YouTube likes and dislikes now remember in descriptive statistics mode

can be used for nominal scale also I can use more to calculate during which season large number of movies are

released and what kind of genre of the movies is produced in high frequency by Bollywood I can use the mode function to

calculate we can use mode to calculate the highest number of frequencies in both season as

well as the genre of the movie but since it is in a language we have to create dummy code before we can go on use mode

the next measure is measure of variation the measure of variation again has many different measures the first one is

range which gives us the maximum and minimum value or minimum and maximum value you can do a trick to calculate

the minimum value in the data and maximum value in the data using quartile function we know that the quartiles

divide the data into 25 percent and so on 25 percent 50 percent 75 percent and the final 100 percent so what I can do

is if I want to calculate the minimum value I can obviously use the minimum function

in this I can say what is the minimum in the budget which is f two to F 150

so the minimum is two I can also use the quartile function which is quartile

the array is F2 to F 150 and when I write 0 it gives me the minimum value you can see that again the value is 2

right similarly I can now calculate the minimum value in each of these cases the remaining

variables such as box office collection YouTube views YouTube likes and YouTube dislikes and I can also now go and

calculate the maximum value so which is Max F2 colon F 150

so that gives me the maximum value here the maximum budget is hundred and fifty and similarly you have for other

variables as well the maximum box office collection is 735 I think this is for the movie PK

and I can also calculate maximum using the quartile function so I can enter quartile

F2 colon f one fifty comma 4 will give me the maximum value again it is 150 right so I can use this

function also to calculate the maximum value in the data so now if I want to calculate the range

the range is nothing but maximum value minus minimum value which is this

minus that so the range or

so the range for box office budget ok the range for box office budget is 148 the range for box office collection is

734.99 and so on so we have the range range is one of the measures of variation now we can go and calculate

other measures of variation such as the variance and the standard deviation so when we have variance we can actually

calculate variance for the population and also the variance for the sample so if I want to calculate the variance for

population we can calculate population variance using a function called

v a r dot V so here we will have F two colon F 150 so that gives us the variance for the

box office budget that gives the budget for the movies so the variance population variance is 792.

if you want to calculate sample variance then we use the function f a r dot s and again F2 colon F 150

so the sample variance is 797.384 remember whenever we calculate population variance the formula is

summation I equal to 1 to n x i minus mu whole Square divided by n whereas whenever we calculate variance of the

sample the formula is x i minus X bar whole Square divided by n minus 1. so since we are dividing with n minus 1 we

are getting slightly higher value here compared to the population variance which is 792 to account for ah bias in

estimation we can use the same function to calculate population variance and Sample

variance for the remaining variables such as the box office collection YouTube views YouTube likes and YouTube

dislikes we can go and calculate the standard deviation I can take simply the square

root of this but there is also a function in Excel so if you want to calculate population standard deviation

the population standard deviation is calculated in Excel using the function s d d e v dot p

and again we entered the entire array so your population standard deviation is 28.14

and if I want to calculate standard deviation from the sample or the sample standard

deviation then it is sdd EV dot s two colon f one fifty so now I can actually do the same for

other variables I can just copy this so now I have the population standard deviation and Sample standard deviation

for the box office collection YouTube likes YouTube views likes and dislikes so we have looked at the

the measures of variation range variance and standard deviation I am going to now discuss percentile percentile is the

value which gives us the value by which certain percentage of the data will lie for example I can use the function

percentile in Excel so when I enter percentile and we are looking at the array which is

budget and when I write 0.1 it basically gives the 10th percentile

value so when we enter we are getting the 10th percentile as 5 crores that means 10

percent of the movies budget is less than or equal to 5 crores similarly I can

do this for the remaining variables and if you look at here the box office collection 10 percent of the movies are

box office collection less than or equal to two crores similarly for the YouTube views likes as well as dislikes now I

can change this and say I want to calculate 90th percentile so it is percentile

F2 colon F 150 comma 0.9 so here what we are getting is 62 that means 62 percent

we are getting the 90th percentile as 62 that means 90 percent of the movies have budget Which is less than or equal to 62

crores similarly for box office collection the 90th percentile is 121 that means

for 90 percent of the movies the box office collection is less than or equal to 121 crores similarly for the

remaining variables now quartiles are nothing but a specific percentile for example quartile one is nothing but 25

percent quartile 2 is 50 percent which is same as median and quartile 3 is 75 percent so if you want to calculate

quartile 1 which is basically ah 25 percent I can either use percentile and say the value

is 0.25 or I can say quartile and the value is one so let us use both these functions so first I am using percentile

f 2 colon F 150 and 0.25 so here what we have is the value is 11 that means 25 percent of the movie

budget is less than or equal to 11 crore so here to calculate this value I use the percentile function in Excel but I

can also use quartile function so when I use quartile this is quartile again f 2 colon F 150 and I am going to say 1. so

here again you see that the value is 11 we have the value of the first quartile that is the value by which 25 percent of

the data Falls below that so I am going to copy this for the remaining so if you look at

the box office collection the first quartile is 8.78 that means 25 percent of the movies have box office collection

less than 8.78 crores we can go and calculate now the second

quartile remember this is same as your median so I can use

quartile F2 colon F 150 comma two so 21 which is same as median

that is 50 percent of the movies budget is less than or equal to 21 crores and here

for 50 percent of the movies the box office collection is less than or equal to 28 crores we can go and

calculate quartile three so I can use the quartile function so quartile 3 for box office budget is

35 crores that means for 75 percent of the movies the budget is less than or equal to 35

crores and 75 percent of the movies the box office collection is less than or equal to

57.45 crores so we looked at percentile and quartile now I'm going to discuss the measures of

shape which are nothing but skewness and kurtosis skewness looks at the length of the distribution or the tail of the

distribution whether it has a long tail or short tail whereas kurtosis looks at thickness of the tail whether it is the

thickness is low or the thickness is high so we can use ah two different functions Q which is used for

calculating skewness and cut k u r t which is used for calculating kurtosis so when you want to calculate skewness

the function is skew we can also calculate skewness for population or skewness so I am going to

just use Q f 2 colon F 150 so here it is 2.05 whenever it is positive that means it is a right skewed

distribution the tail is long at the right side and let us see the skewness for all other variables and here all of

them are ah positive that means all these variables are right skewed if you have a negative

value that means it's a left skewed if it is 0 then it is same as normal distribution

now let me calculate the kurtosis so the curtosis the function is k u r t this one actually gives us excess

kurtosis so basically it compares the kurtosis value with normal distribution and checks whether the ketosis is more

than normal distribution or less than normal distribution the curtosis for normal distribution is three so let us

calculate for all these variables the kotosis value F2 colon f one fifty so here you are getting 4.6 so that

means much steeper than normal distribution so it has a peak which is much longer than normal distribution in

fact all of them have a high P comparator normal distribution if it is less than 3 then it is lower than normal

distribution so it is a very flat curve if it is less than three if it is more than three then it is higher than the

normal distribution foreign [Music]