Love 'em to death. They just make games better in general, and fix the many mistakes made by developers. Be they graphical, bug fixes, minor skins or even complete overhauls, mods can add a plethora of options to games, give life to mediocre ones and make more fun the already fantastic ones. Though I can't see a reason that anyone would hate mods, that seems like one of those things that someone would hate because it's popular. Especially considering that they're completely avoidable and easy to ignore.
As for my favorites? Hard to say. On the console front, though this is considered hacking, it's really the same thing in this case, there's Super Smash Bros. Brawl. Being able to pit Bayonnetta against Cloud Strife to the boss theme The Almighty from Persona 4 on a ship from Monster Hunter is just plain enjoyable. Though this game isn't as customized as others, it's interesting to see how you can personalize your game and really add flare. When I discovered modding for Brawl it added SO many hours to the experience.
On the PC, my two biggest examples are easily The Sims and Elder Scrolls. Among the plethora of games on the PC, these two have some of the biggest modding communities you will ever find. I could go on and on about how many ambitious mods have made me awe at the heights these games could reach, but I'll just keep it safe and say that these two (and most other games with mod support) are largely definitive on the PC for mods alone (and though I'm a PC gamer, among other platforms, I'm not a "PC mustard race" sayer; I'm just a guy who loves mods). Skyrim, specifically, would have been a day-one return for me on console, having preordered the game, but I bought it with full knowledge that the modding community would make the game much larger and more fun than the vanilla release, and man was I right.