Best game to mod

Left 4 Dead 2 and Killing Floor.

grand theft auto games is the best games to mod of all time then skyrim and tf2

SimCity 4

Gothic II incl. the addon night of the raven. TONS of mods. Most of them german, but some are english too

Skyrim is best for modding.

Left 4 Dead 2.

HL3 .. the community for this game is awesome and they have great mods already

Counter-Strike

The Total War Series!

GTA 4, Crusader Kings 2 has some good mods. The multi player mod for Just Cause 2 is amazing.

half life 2

Every single GTA :D

Morrowind
That is still the best part of the TES series, and with mods, it gets amazing (this come from someone who started with Daggerfall, Morrowind was not my first TES game).

Vampire The Masquerade: Bloodlines. Alright, so you only need one mod, but it's a good one.

Hearts of Iron 2 & 3 and Crusader Kings 2.

Civilization 4 & 5 (2 is also good in this regard, but not on steam)

Dungeons of Dredmor

Legend of Grimrock

IL-2 Sturmovik

Should keep you occupied for a few years.

Half Life 1, 2, GTA IV, Minecraft, Garry's Mod.

Elder Scrolls V: Skyrim, for sure :)

GTA IV for sure ;]

Call of Duty 37

The STALKER games have beautiful, atmospheric environments and there are lots of good graphical mods out there like Atmosfear and the Absolute texture packs that really add to it. I really liked AMK Weather's Mist preset, the thick blanketing fog was really fitting in the Zone. Have played them all through multiple times with different mods and it's a new experience every time.

Gary's mod? Since that game is basically all about modding / making your own

Garry's mod.

Doom I/II still has a pretty freaking large modding community, so I'd really suggest getting that. FO3 has a good number of mods. There's also minecraft, though I'd still wait till they release the modding API so stuff stops breaking and you don't need to hunt down versions to get stuff to work.

As everyone else has said, the HL series is pretty good for mods, since it pretty much never seems to stop. You could also check out moddb.com, it has a pretty massive amount of them for a lot of games.

Little Fighter 2