Great question! The answer to this question is related to a subtle difference between heat and energy in general. Now, of course, heat really is just a form of energy - when we talk about heat flowing from something hot to something cold, we're really talking about a flow of energy, and you know that to heat something up you have to put energy into it in some way. But here's the thing: whether or not an object (like a tank of water) will heat up when you put energy into it depends on the form of energy you give it (for a silly example, a stick of TNT contains a lot of energy, but if I just plop it into a tank of water without lighting it, the water won't heat up at all!). Basically, to heat up some "stuff" you need to apply energy in such a way that the internal energy of the "stuff" increases (to, say, make the molecules in your tank of water move around faster).

That's what's going on here - you're right that purple light has a higher energy than red light. But it turns out that water is much better at absorbing the energy from red light than from purple. Why? Well, it has to do with how water molecules vibrate in response to an applied electric field (remember that light is just a vibrating electromagnetic field!). It turns out that when red light hits water, it tends to be absorbed by the water molecules, making them vibrate so that the water heats up. But when purple light hits water, it tends to just bounce off of the water molecules without getting absorbed, so it doesn't heat up the water very much. In fact, this is why water looks blue (and also why colors like red, orange, and yellow tend to disappear when you dive deep in water): redder wavelengths get absorbed by the water, while bluer ones scatter.

In fact, you probably experience the difference between the absorption of red and purple light every day. When you stand in the sun, the light feels warm. Why? That's because the water in your body is absorbing the infrared light coming from the sun. But you've probably heard people say that you need UV (ultraviolet) protection when you stand in the sun. That's because even though ultraviolet light isn't absorbed by water very well, it has so much energy that it can actually tear apart the DNA molecules in your skin cells (which can lead to skin cancer). So even though it doesn't heat you up as much, ultraviolet light actually does have way more energy than infrared light!

This is a great question. Most colored bulbs are just bulbs that emit white light with paint applied to the glass so that all but the desired color is blocked out. This means that the heat generated by the bulb depends on how much heat it takes to produce the light.

How much heat it takes to produce light, and how efficient the bulb is (i.e. how much of the energy gets "wasted" as heat) depends on what kind of bulb you're using. For example, incandescent bulbs use electricity to heat a metal filament inside of a bulb, which in turn gives off light. They are about 10% efficient, which means about 10% of the energy used goes into generating light and the other 90% or so becomes heat. Fluorescent bulbs, on the other hand, have a gas that generates light when it is electrified. These bulbs are roughly 30% efficient. Regardless of which type of light bulb you used, as long as both were the same kind, you should not in theory see a difference in heat generation between the two colors.

Then why in practice did your red bulb transfer more heat to the water? This may have been due to slight differences that arise in the manufacturing of the light bulbs as well as their age. I hope that helps!