Mass delete of unpopular tags

[jacr]

Hi all. I've searched somewhat, but cannot seem to find an answer to this one:

I've got a large collection that I've imported a lot of social metadata for. I've ended up with around 3000 odd tags, the majority (>2500) of which are only applied to a few books. I'd like to delete all unpopular tags, say all those that occur on less than 10 books. I can't find a way to do this though the Calibre UI without clicking "delete tag" 2500 times.

Any suggestion?

Much appreciated.

[Manichean]

You can use bulk metadata edit for editing the tags or doing a search & replace on the tags field. That will cut down the editing required a little, but I don't know of a way to delete all tags that are applied to less than X books.
The quality check plugin may be of interest to you, although that too will only select books based on tag count.

[jacr]

I found a way to do this. I did the following
1) Backed up the calibre database (metadata.db)
2) Downloaded SQLite Administrator
3) Ran the following SQL against the database:

delete from books_tags_link
where id in
(
select id from books_tags_link
where tag in
(
select tag from books_tags_link
group by tag
having count(tag) < 30
)
)

This deleted all tags that are on less than 30 books.

[Arkivaren]

Originally Posted by jacr

I found a way to do this. I did the following
1) Backed up the calibre database (metadata.db)
2) Downloaded SQLite Administrator
3) Ran the following SQL against the database:



This deleted all tags that are on less than 30 books.

You sir are a genius.

[theducks]

Originally Posted by jacr

I found a way to do this. I did the following
1) Backed up the calibre database (metadata.db)
2) Downloaded SQLite Administrator
3) Ran the following SQL against the database:



This deleted all tags that are on less than 30 books.

That deletes the tags from the books (link)
What cleans up the tags table?

[Starson17]

Originally Posted by jacr

This deleted all tags that are on less than 30 books.

I weep for the poor unwanted data consigned to the bit black hole of unrequited binary love

[Arkivaren]

Originally Posted by Starson17

I weep for the poor unwanted data consigned to the bit black hole of unrequited binary love

I don't hehe

My tags count went from 4500 something to approx. 350
and I only deleted tags used fewer than 6 times.

Btw I found that doing a:

SELECT COUNT(DISTINCT tag) FROM books_tags_link

prior to and after the deletion, gives a nice indication of what's been
done (for some reason SQLite Administrator doesn’t).

[Starson17]

Originally Posted by Arkivaren

I only deleted tags used fewer than 6 times.

So you kept the "Fiction" tag found on 1200 books and threw away the "Fiction - American Pre-Revolutionary War" tag found on only 4 books.

Personally, I'd have done it the other way - thrown away all general tags found on more than 6 books and kept the useful specific tags

I love wandering through my tag list and finding hidden bits of subject matter that some helpful librarian, author or publisher spent the time to tag for me.

Different strokes for different folks!

[HomeInMyShoes]

^This is really a question of indexing exhaustivity versus term specificity. Both sets of tags (frequent and infrequent) have their uses. Now if everything has the tag of book then it is useless.

My own opinion is that removing less frequent tags completely negates the importance of the long tail.

[Starson17]

Originally Posted by HomeInMyShoes

^This is really a question of indexing exhaustivity versus term specificity. Both sets of tags (frequent and infrequent) have their uses. Now if everything has the tag of book then it is useless.

My own opinion is that removing less frequent tags completely negates the importance of the long tail.

I love the long tail, but I understand why some want to reduce their tag list. I keep a custom genre column that serves the purpose of broad categorization.

Instead of throwing away the long tail, there were other options. They could have copied the tags into a custom tag-type column and thrown away the tail from that column. That way, a tag on 5 books might later have made it past the 6 book threshold when another book with that tag arrived and they ran the process again (copy all tags to a custom column and delete the long tail from the custom column).

Data is so hard to come by that throwing it away seems a crime.

[Acousticvillage]

Is there any way the genius above could give me a way to import / copy all the values/data in my Genres column into the Tags column? I want to delete the tags data and substitute it for genre data so that the catalogue function will show my genres....

[DoctorOhh]

Originally Posted by Acousticvillage

Is there any way the genius above could give me a way to import / copy all the values/data in my Genres column into the Tags column? I want to delete the tags data and substitute it for genre data so that the catalogue function will show my genres....

I replied earlier to this very question in this thread. Please refrain from posting the same question multiple times.

[Arkivaren]

Originally Posted by Starson17

I love the long tail, but I understand why some want to reduce their tag list. I keep a custom genre column that serves the purpose of broad categorization.

Instead of throwing away the long tail, there were other options. They could have copied the tags into a custom tag-type column and thrown away the tail from that column. That way, a tag on 5 books might later have made it past the 6 book threshold when another book with that tag arrived and they ran the process again (copy all tags to a custom column and delete the long tail from the custom column).

Data is so hard to come by that throwing it away seems a crime.

Ordinarily I'd agree, however, having permutation upon permutation of
misspelled/malformed tags, is not my idea of "data".

You mention librarians above…trust me, no sober librarian
would have added the tags I deleted hehe

But as you say, of course YMMV and it seems it does

Btw my approach above combined with the GoodReads plugin +
dwanthny's customization and the BISAC Subject list has worked
wonders in my library.

[Starson17]

Originally Posted by Arkivaren

Ordinarily I'd agree, however, having permutation upon permutation of
misspelled/malformed tags, is not my idea of "data".

There's a difference between fixing errors/making the tags consistent versus throwing away all data that applies to six or fewer books. As you say YMMV.

[HomeInMyShoes]

Originally Posted by Arkivaren

Ordinarily I'd agree, however, having permutation upon permutation of
misspelled/malformed tags, is not my idea of "data".

Completely valid, although sometimes misspelled isn't misspelled and the jargon tells us something different. Although, It's hard to discern tagger intent in many cases. It may be better to fix in my opinion than to throw away, there's no fix all occurences of a tag functionality within Calibre? I haven't used it enough to know. If not, it would be a good feature to ask for.

My own tagging is a mess sometimes. Some days I'll be a lumper and some days a splitter. Tags are an evolving beast with me.