Scientists are definitely nice imo, for the academy early, instant tech discoveries until about industrial age, and if all else fails a nice super specialist. In the oxford city with academy they'll produce up to 20 beakers!
Prophets aren't that bad either I think. Making shrines is one thing, but the super specialist is pretty good, too, can get some nice hammers to commerce cities.
Engineers are quite useful, but I find myself getting them quite rarely, since usually you can't have more than one engineer specialist until late (from forge), so the probabilities are low. I don't know what they do as super specialist, but wonder rushing rocks.
I'm not so sure about the exact benefits of the merchant super specialists - they give 1 food, which is probably the best thing any super specialist gives, and I suppose as much gold as the priests or a little more. It's been a while since I've used those, but especially for the food, they are pretty useful. Trade missions aren't so bad either. You don't have to travel the whole world, just go to the other end of your continent, through an allied civ's city, and get yourself some 2k gold.
Which leaves the artist. That's the one I haven't yet put to any really good use. Culture bombs are nice I guess, but don't help much on established borders. The super specialist just plain sucks. So, this is the one I've voted for.