One of the rare ship explosion during the Napoleonic Wars was the Orient
We can have a look at the account of its explosion here:
https://en.wikipedia.org/wiki/Battle_of_the_Nile#Destruction_of_Orient
What we can remember is that the ship was not moving (easier to shoot at), there seems to have a fire already started (probably from paint not stored at the right place), the crew was unable to fight the fire efficiently because of the water pump damaged. Also a very special ammunition was stored close by (incendiary shell) which didn't help.
So all considered we should think that a ship exploding should be a rare event and a combination of many elements going against its crew ability to fight the fire.