A quick overview in case you don't know ^^ I won't go into the history of tsundere characters, just the basic meaning/characteristics.
“tsuntsun” -> to turn away in disgust + “deredere” -> to become sweet and loving
The word refers to an anime character of any gender who is initially unfriendly or hostile, but is actually quite affectionate and cute. Basically, they are quite affection and lovely, but they bury those feelings under a mountain of bitchiness for some reason.
So, Why Do We Love Them?
The "Contrast" Effect
Basically, if a character is kind from the start, we expect them to be kind. If they are mean and stay mean, we expect this.
A character who starts out seeming very mean, and then mellows out shows us a contrast in their personality, and in our minds that represents progress! This is sort of psychologically stimulating to us, so we become interested in the character more than we might have if they didn't go through some sort of personality change. You can read more about why this might happen here :)
Basically, their small changes are stimulating to our minds, so we find them more attractive!
"Contrast" Effect Example
Most of us probably know Louise from Zero no Tsukaima, right?
Louise was very cold to Saito at first because he wasn't what she was looking for, but as she grew to get over that, he was able to see her cuter side (that was hiding behind the MEAN) and after 4 seasons of being together (proximity definitely had something to do with this one)....let's just say their relationship changes a bit :)
So We Like Them Because They're Mean?
Pretty much! Them fluctuating between mean and adorable makes our baseline of how we perceive them different than other characters, so they become super moe or sweet in our eyes after they show that there is a different side to them. Annnnnd it works. Thus why the anime industry abuses this trope so much! I mean just look at how many of them there are in the image above!
Taiga Aisaka from Toradora is another great example of the contrast theory of why we like tsundere. Taiga is SO violent in her reactions, because she really can't come to terms with her feelings so her level of "mean" seems to be totally out of control! So in the moments where we do see her softer side (related to both her feelings and her dark past) we really notice it, and we really fall for her tsundere actions!