Manipulating MusicBee Library Data in a Spreadsheet

[frankz]

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.

Code

‘<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

Code

=COUNTIF(C:C,C2)

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

Code

=E2/F2

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

Code

=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

Code

=SUMIF(C:C,C2,D:D)

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

Next (J) is

Code

=I2/H2

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

Code

=G2*J2

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

I hope this helps someone somewhere.