New York, Washington, and San Francisco have the best public transit IMO. This is mainly because geography prevents these cities from sprawling. Chicago also has a surprisingly decent metro system despite the massive urban sprawl. I've heard that Boston's transit system is pretty good too, although I haven't been there in years so I don't know.
The worst public transit systems in my experience are found in geographically enormous cities in the southwest: Los Angeles, San Diego, Phoenix, and anywhere in Texas.