Too much mods could be a waste of time, but one shouldn't underestimate the work of non professionnal develloper.
Counter Strike & Team Fortress were amateur mods for Half-Life before getting the right to be commercially released.
Same for the ongoing Black Mesa mod, that put HL1 storyline in a modern engine with a lot of gameplay improvements.
Duke Nukem reloaded is an amateur game, that 3D Realms was afraid to release because they considered it better that Duke Nukem Forever. (they hired the team for another game they released)
On a more personnal way, i much prefer playing or replaying Fo1 Fixt, Fo2 RP or Fo2 Van Buren Mod, than replaying Fo3...
I was also amazed by Point Of View, the mod for HL1, playing as a vortigaunt.