[PYTHON] Aggregate Spotify views ranking and save to Excel

Introduction

I think there are many people who love Spotify when listening to music. It's very useful as a feature, but unfortunately I can't see the number of views.

Instead, Spotify has a service that allows you to download personal data such as playback history. This time, I would like to use this personal data to aggregate the number of views and save it in Excel. ʻOpenPyXL` is used, so if you haven't installed it, please install it in advance.

The code used this time is created by referring to the following article. Please download the personal data in advance by referring to the following articles.

Reference article: Arrange songs played on Spotify in order of the number of views

code

count.py

import json
import collections
import openpyxl

#Read json data
with open('StreamingHistory0.json', 'r', encoding='utf-8') as f:
    d = json.load(f)

list = []

search_all = str(input('Search for all? (y/n) >> '))

#Take out the song title and add it to the list
#Whole search
if search_all == "y":
    for i in d:
        list.append(i['trackName'])
#Specified search
else:
    artist = str(input('Enter the artist name. >> '))
    for i in d:
        if(i['artistName'] == artist):
            list.append(i['trackName'])

#Get elements in order of appearance
c = collections.Counter(list)
c_list = c.most_common()

#Create a workbook
book = openpyxl.Workbook()

#Get the sheet and rename it
sheet = book.active
sheet.title = 'Play Count'

#Match the length of the column to the song title
max_length = 0

for index, row in enumerate(c_list):
    if index <= 10: #Ranking to be aggregated
        sheet.append(row)
        if len(row[0]) > max_length:
            max_length = len(row[0])

sheet.column_dimensions['A'].width = max_length

#Save workbook
book.save('SpotifyCount.xlsx')

In addition to the output to Excel, I tried to specify the encoding and format it. In the above code, the ranking of aggregation is up to 10th, but please change this if necessary.

Execution result

In the case of a full search, the overall ranking is recorded, and in the case of a specified search, the ranking of the specified artist is recorded on the Excel sheet.