Author Topic: Manipulating MusicBee Library Data in a Spreadsheet  (Read 1252 times)


  • Hero Member
  • *****
  • Posts: 2916
I wanted to do a deep dive on data from my MusicBee Library as it related to a specific playlist. I basically wanted to prioritize a list of albums by playlist inclusion and ratings.  Luckily, MusicBee provides facilities to export most library data for things like cross-tabulating.

This isn’t really a step-by-step, because I think the specifics are pretty tailored to my needs. It’s more an example of the possibilities.

In Musicbee:
I created a filter that would collect all of the files I wanted to study.  These were mp3 files in a specific playlist where Album Track Count for the albums they were on was 6 or greater.  I could have also created an auto-playlist to the same effect.

I selected the fields I wanted to study and entered them in the following format under Edit→Edit Preferences→Tags(2)...’export to clipboard’ Tags.

‘<Album Artist>’;’<Album>’;’<Album Artist> <Album>’;<Rating>;<Album Track Count>

The third field, <Album Artist> <Album>, is a unique key I realized I needed in case some album artists had identically named albums (Greatest Hits, Gold, etc).

To export the data, I ran the filter, selected all files, and did right-click→Send To→Clipboard.

In Libre Calc:
I use Libre Calc for my spreadsheet needs.  With the tags in the clipboard, CTRL-V (or Edit→Paste) opens the Libre Calc “Text Import” dialogue.

Separator Option: Semicolon - String Delimiter: '

Now I’ve got 5 columns of data matching the 5 fields I exported from my MB library in the spreadsheet, one row for each song that met the filter.

Here are the things I wanted to calculate as an illustration of what’s possible. I know that some of these formulas can be combined into one calculation, but I’m not that good at it so I like everything in its own column.

My first new column (F) is a formula
This counts each row that matches the uniquely keyed field, giving me a count of songs from that album that are in the list.

Next (G) is
This gives me the ratio of songs in the list to total songs from the album. This is one of my main pieces of data.  I format this as a percentage for readability.

Next (H) is
=COUNTIFS(C:C,C2,D:D,"<>No Rating")
Unrated songs say “No Rating” in the list.  I want a count of songs from each album that have ratings. This counts how many rated songs there are (D) for each value in the keyed field (C).

Next (I) is
This gives me a sum of ratings (D) for each album (C).

Next (J) is
This gives me an average rating (among rated tracks) for the album in C.

I copy these formulas down to each row in the spreadsheet.

At this point I have all the data I need for each album and I want to remove duplicate rows to just show one line for each album.  Getting rid of duplicates will change the calculations, so I just want raw numbers to remain.  

I copy columns F through J and paste them as “numbers only” in columns K through O.  Then I delete F through J so K through O shift left to become the new F through J.

To remove duplicate rows select all of column C (the key row). Data→More Filters→Standard Filter.  Change the first field name to none.  Click + next to Options and select “No Duplications”

This just hides the duplicate rows, but I don’t want them there at all, so I copy everything to a new sheet.

I want to use the percentage of total album that appears in the playlist and the average rating across rated songs to create a Desirability score.

Desirability is Percent of Album in List times Average Rating, so Column K reads

Copy to all rows, sort descending, and I’m done.

I hope this helps someone somewhere.
A smile is happiness you'll find right under your nose.