Incandescent light bulbs are most likely to burn out when they have just been turned on. The metallic filament of an incandescent bulb works by resisting the flow of electricity. This resistance causes the filament to get very hot, to the point that it gives off both heat and light. Over many heating and cooling cycles, the material of the filament becomes increasingly brittle.
Most conductive materials carry current better when they are cold. Thus when you first turn an incandescent bulb on, the initial flow of electricity, called the inrush current, is quite high; as the filament heats up its resistance increases. During this initial warm up period the filament is more fragile than normal because it is changing both in resistance (which is dropping) and in physical size (which is expanding).
Bulbs that are kept on continuously, such as the Centennial lightbulb, which has been burning for 110 years, tend to last much longer than bulbs that are turned on and off frequently.