Analyzing Spotify Top 2000 Songs: Data Manipulation &

.pdf

School

Griffith University *

*We aren’t endorsed by this school

Course

131

Subject

Business

Date

Jun 13, 2024

Type

pdf

Pages

Uploaded by EarlLapwingMaster1006

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 1 | P a g e Assessment 2 - Case Study - Spotify Top 2000 Length: 2000 Words Due: Friday 10 th May, 11:59PM Submission: Online (Canvas) Weight: 35% Instructions: • Data for this assessment can be found in the Excel file Dutch Top 2000 Spotify Songs.xlsx • This assessment covers the topics up to and including Hypothesis Testing. • You will be expected to manipulate the data using pivot tables, to create appropriate visuals (tables and graphs) to demonstrate outcomes and to conduct a range of statistical analyses (particularly hypothesis testing) to answer questions. • Should you decide to reference additional material there is no referencing standard specifically required so long as you are consistent. • Complete all calculations in the Excel file and save it as <Your_Name> Assessment 2.xlsx. This will be the file you upload to Canvas. • Separate worksheets have been identified for each question in the Excel File. Please complete each question on the nominated worksheet. It is recommended that you keep an unadjusted, original data set on the first worksheet Dutch Top 2000 which you can refer back to. Some questions may require you to adjust the data set, which will not be needed in later questions, so keep a clean copy on the first worksheet • In addition to the Excel file, you will be required to create a solution document where you will copy your final excel output for each question (Table, graph, formula or excel calculation solution) as well as the written explanation and conclusion for each question. You may use the original Assessment 2 document incorporating your solutions under each question, or you may create a new document, however ensure that each question is clearly identified. This document should be created in Word, but once you have completed the assessment please save it as a PDF using the title <Your_Name> Assessment 2.pdf • For those answers which require written responses these should be short and succinct, with reference to relevant data and provided in non technical language • When you copy your tables, graphs etc across to the solution document please include them in the section relevant to answering a particular question. Do not put them in appendices. Only copy over relevant output. Note that we need to see this in your solution so that we do not have to go searching for it in the Excel output. Any good report always includes the relevant information up front so it can be referred to immediately without the reading having to look elsewhere. • Please ensure you format and label all graphs, tables and output. • This assessment is worth 35% but you will be given a mark out of 70. Marks for the relevant parts are shown below. • Remember to upload both the Excel and PDF file as solutions to Canvas. • Have fun. Learn something about Dutch music preferences. Agree or disagree with the choices made. Leave the assessment with something you could say to your friends along the lines of “Did you know……..?” .

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 2 | P a g e Background Spotify is the largest music streaming service globally used by over 30% of streamers. Their ability to offer a wide range of services, including curating personal lists and making recommendations, relies on their massive music data base and the ability to analyse a vast range of data on both the consumer and the music itself. You have been provided with a data set containing information on 2000 (actually 1994) of the top Spotify Songs according to Dutch subscribers released from 1956 to 2019. 1 Being a Dutch data set there are a large number of tracks by Dutch artists. Information provided in the spreadsheet includes data on the following variables: 1. Index – a simple identifier 2. Title – name of the track 3. Artist – name of the artist 4. Genre – the first listed genre of the track 5. Year – Year of most recent release 6. Tempo – The Beats Per Minute (BPM) of the song 7. Energy – Spotify rating from 0 to 100 with values closer to 100 indicating a more energetic song 8. Danceability – Spotify rating from 0 to 100 with higher values indicating it is more danceable. 9. Loudness – The overall loudness in decibels (db) with higher values indicating louder song 10. Valence – score from 0 to 100 with the higher the score the more positive the mood of the song 11. Length – Duration of the song in seconds 12. Acousticness – score from 0 to 100 with the higher the score the more acoustic it is 13. Speechiness – score from 0 to 100 with the higher the score the more spoken words there are 14. Popularity – Score from 0 to 100 based on an algorithm that includes the number of times it is played. Songs with a higher score are more popular. Answer the following questions: General Popularity 1. What are the 5 most popular songs in this list? (2 Marks) Steps: - Copy the relevant columns (Title, Popularity) to Q1 work sheet - Use Sort option -> Sort by Popularity from Largest to Smallest - Use Filter option -> Top 5 Results: Title Popularity Dance Monkey 100 Memories 98 bad guy 95 All I Want for Christmas Is You 94 Believer 88 1 Thank you to Sumat Singh who scraped the Spotify Database to put together this set.

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 3 | P a g e 2. Who are the 5 most popular artists – note the artist must have at least 5 songs in the top 2000 to qualify? Show the top 20 most popular artists using a graph with the most popular on the left and with decreasing popularity. (4 Marks) Steps: - Copy Artist and Popularity columns to Q2 worksheet - Use Pivot Table to get Count and Average Popularity - Use Filter option to filter Count of Artist greater or equal to 5 to get only artists that have at least 5 songs - Use Sort option to sort by average of Popularity in descending order - Select the first 20 values and tick “Keep the Selected Items” box to make a graph Result: Row Labels Average of Popularity Ed Sheeran 78.83333333 Imagine Dragons 77.88888889 Red Hot Chili Peppers 76 Eminem 76 AC/DC 75.33333333 3. What are the 5 most popular genres among the Dutch top 2000? (3 Marks) Steps: - Copy Genre and Popularity columns to Q3 worksheet - Use Pivot Table to get Average Popularity - Use Top 10 filter and sort by descending order to get the 5 most popular genres Result: 64 66 68 70 72 74 76 78 80 Top 20 most popular Artists in descending order

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 4 | P a g e Row Labels Average of Popularity hip hop 73.76470588 Indie 70.75 Latin 70.66666667 Rap 68.16666667 Soul 67.2826087 Genre Preference 4. Is there any evidence at the 5% level of significance that there is a preference for Metal over Classic Rock? You should conduct this test in two ways: a. Is there a difference in the average popularity of Metal and Classic Rock Songs? (8 Marks) b. Is there a difference in the proportion of Metal and Classic Rock songs which have made it to the top 2000? (7 Marks) a. Firstly, the data were split into the popularity of Classic Rock and Metal. The table below shows the first 5 as examples. Classic Rock Metal 59 76 40 72 39 65 53 52 72 77 To be able to do our test of the difference between two means we need to know if the two population variances are equal or not Classic Rock Metal Mean 61.264151 Mean 65.51389 Standard Error 1.7137604 Standard Error 1.179174 Median 63 Median 67 Mode 58 Mode 72 Standard Deviation 12.476364 Standard Deviation 10.00562 Sample Variance 155.65965 Sample Variance 100.1125 Kurtosis -0.597112 Kurtosis 0.305633 Skewness -0.646568 Skewness -0.7269 Range 47 Range 46 Minimum 32 Minimum 37 Maximum 79 Maximum 83 Sum 3247 Sum 4717 Count 53 Count 72 Confidence Level(95.0%) 3.4389117 Confidence Level(95.0%) 2.351206 Using Excel Descriptive Statistics, we know that both n CR (53) and n M (72) ≥ 30, we can assume individual sampling distributions are normal, and hence sampling distribution of difference in sample means is normal We have:

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 5 | P a g e H 0 : σ 1 2 = σ 2 2 - Variances are equal H 1 : σ 1 2 ≠ σ 2 2 - Variances are not equal Decision Rule: Reject Ho if p – value < 0.05 F-Test Two-Sample for Variances Classic Rock Metal Mean 61.264151 65.51388889 Variance 155.65965 100.1124804 Observations 53 72 df 52 71 F 1.5548476 P(F<=f) one-tail 0.0419 F Critical one-tail 1.5217266 Using F.Test, we have one-tail p-value of 0.0419. Therefore, p-value for this test = 0.0419 x 2 = 0.0838 Since 0.0838 > 0.05, we cannot reject H 0 . Hence, conclude the variances are equal. Test to see if there is a difference in the average popularity of Metal and Classic Rock We have: 𝐻 0 : 𝜇 ? − 𝜇 ?𝑅 = 0 𝐻 1 : 𝜇 ? − 𝜇 ?𝑅 ≠ 0 Decision Rule: Reject H 0 if p – value < 0.05 Because we know the variances are equal, we can use T – Test: Two-Sample Assuming Equal Variances t-Test: Two-Sample Assuming Equal Variances Classic Rock Metal Mean 61.264151 65.51388889 Variance 155.65965 100.1124804 Observations 53 72 Pooled Variance 123.59584 Hypothesized Mean Difference 0 df 123 t Stat -2.112075 P(T<=t) one-tail 0.0183514 t Critical one-tail 1.6573364 P(T<=t) two-tail 0.0367028 t Critical two-tail 1.9794387 p-value for two tail returns 0.0367, which implies there is a 3.67% the null hypothesis is true p-value < 0.05, hence we can reject H 0 and conclude that there is a difference in the average popularity of Metal and Classic Rock.

BSB131 APPLIED BUSINESS ANALYTICS | Assessment 2 6 | P a g e b. We can divide the sample into 2 groups: • Group 1 – Metal songs that made it into top 2000 • Group 2 – Classic Rock songs that made it into top 2000 We are asked to test to see if there is a difference – two tail test We have: H 0 : p 1 − p 2 = 0 H 1 : p 1 − p 2 ≠ 0 Decision Rule: Reject H 0 if p – value < 0.05 We can use the z distribution provided x 1 , (n 1 – x 1 ), x 2 , (n 2 -x 2 ) all have to be ≥ 10 From our question n 1 = 1994 x 1 = 72 (n 1 – x 1 = 1922) n 2 = 1994 x 2 = 53 (n 2 – x 2 = 1941) In this case all of x 1 , (n 1 – x 1 ), x 2 , (n 2 -x 2 ) are well over 10 and hence the sampling distribution of the difference in sample proportions will be normally distributed We need to calculate (p ̂ 1 − p ̂ 2 ) , (p 1 − p 2 ) σ (p ̂ 1 −p ̂ 2 ) p ̂ 1 = x 1 n 1 = 72 1994 = 0.0361 p ̂ 2 = x 2 n 2 = 53 1994 = 0.0266 p ̂ 1 − p ̂ 2 = 0.0361 − 0.0266 = 0.0095 From H 0 , we have p 1 − p 2 = 0 σ (p ̂ 1 −p ̂ 2 ) = √p ̂q ̂ ( 1 n 1 + 1 n 2 ) Where p ̂ = x 1 + x 2 n 1 + n 2 p ̂ = 72 + 53 1994 + 1994 = 0.0313 σ (p ̂ 1 −p ̂ 2 ) = √ (0.0313)(0.9687) ( 1 1994 + 1 1994 ) = 0.00552

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version