the step-by-step data analysis

note: code will be highlighted in gold

click to go to a step: 1 2 3 4 5

Who were the top EDM artists I listened to? I could best determine this by seeing the artists who I had the most songs from on my main EDM playlist).

Here is my main EDM playlist that has over 1.1k songs:

step 1: export the playlist

I first used an open-source tool called Exportify to convert this playlist into a csv file.

note: you should be able to connect your spotify account to this web app

Once I saw my list of playlists on Spotify:

I searched through my tens of playlists (I have a lot) using the arrow buttons on the top and bottom of the page, then clicked 'export' on the playlist I was looking for: in this case, it was a playlist titled "slide into my E'DM's."

I downloaded the outputted .csv file and then took a look at it on Google Sheets:

step 2: clean/sort the data

What I care most about analyzing is the artist name, because I want to see how often each artist's name shows up on my playlist as a way for me to determine who I listen to the most/who my top artists are. In this case, that would be Column C (highlighted below).

I isolated the column with the artist name(s) and put it on another Google sheet/tab. Then, I went over to 'data' and split the text into columns to see which artist was the main artist and which artist(s) were featured on each song on my playlist.

step 3: measuring frequency with "countif"

I now want to see how many times an artist's name shows up in Column 1, or the first artist that is credited for each song on my playlist (since usually this artist is the main artist).

To count this, I made a separate column titled "Count (Artist 1)", and then typed in the following command: =COUNTIF(A$2:A$1126, A2)

Note: "A$1126" could be "A$700" or "A$10" or any other number. this number should match the length of your playlist. if your playlist has 1000 songs total, it should be "A$1000".

----- Quick explanation of the logic behind this function -----

The function =COUNTIF() takes in 2 arguments. The 1st argument is the range of data you're wanting to compare a data point to, and the 2nd argument is that specific data point. The above function essentially looks at all cells inclusively between A2 and A1126 (the 1st argument - there are 1126 songs in my playlist and A1 is just the title of the column) and sees how many times A2 (the 2nd argument) matches with another cell in that range of cells.

The dollar symbol ($) is so that you can apply this function to the rest of your column (i.e. comparing A3 with A2 through A1126, A4 with A2 through A1126 etc.) Without shifting that range A2:A1126. In Google Sheets, if you don't put the dollar sign in between the column letter and number, the range will shift if you apply this function to an entire column (i.e. without the $ sign, you would be comparing A3 with A3 through A1127 etc.)

----- End of quick explanation -----

Once I have applied that function to the whole column, I now have a list of the number of times each first artist listed on each song have repeated within the playlist. I can do this for the second artist listed as well, but to keep things simple, I will keep my rudimentary analysis to Artist 1 only.

step 4: filter the data

I can now use the 'filter' function on google sheets to order the artist name column by how often they repeat from greatest to least number of times.

Now I can see who is featured most among all the songs on this playlist!

In this playlist, my top artist is actually WRLD, an artist on the Canadian label Monstercat. This actually came as a slight surprise to me, because I wouldn't automatically list him on my top 10 favorite artists despite the fact this songs on this playlist feature him most as the main artist.

step 5: using pivot tables instead of 'filter'

An easier way to find out each unique artist I listen to the most is to use Google Sheet's pivot tables!

I can do this by going to 'Data' and select 'Pivot table'. It's best to create a pivot table on a new sheet to avoid erasing any hard work done on the current sheet.

From here, I went to 'Rows' and select 'Artist Name' and 'Values' to select 'Count (Artist 1)'. I had 'Count (artist 1)' be summarized by 'COUNTA' and show as 'Default'.

I then sorted 'Artist Name' by 'COUNTA of Count (Artist 1)' and have this be in descending order (greatest to least). This will give the number of times an artist shows up in a playlist, and WRLD once again is shown to repeat 30 times in my whole playlist.