Evil Is Hammy, Nothing Is Scarier. Nightmare Moon, Discord, King Sombra, and Queen Chrysalis all made a LOT of noise about being insanely powerful villains from the moment they each announced their presence. In the castle, however, there was no such noise. There was no big entrance, no flashy display of the villain's powers, no hammy monologue. It was just a dark, empty, silent castle.
Furthermore, only two of the Mane Six were actually "so afraid of a standard issue Haunted Castle", and two more were merely frightened by the castle's traps suddenly coming to life and throwing them around. Prior to that, Rarity was pointedly ignoring the castle's scary bits to focus on retrieving the tapestries, and Fluttershy was more afraid of losing Rarity and Angel in the castle than she was of the castle itself. Twilight and Pinkie both saw nothing to fear in the castle, and so spent most of the episode separated from the main action.
They also probably feel a bit less formidable than before, as they no longer have the Elements of Harmony, without which they never could have defeated Discord or Nightmare Moon. And since it was Nightmare Moon's power that was allegedly wandering the castle...
Plus, Rainbow and Applejack were startled at the very beginning, and they got most of the actual scary stuff - creepy hallways and accidents that resulted in more Jump Scares for them. They got a bad first impression, it stuck, and they kept making more trouble for themselves. As someone else said above, Rarity was ignoring the scary parts and only admits that it's somewhat frightening; in fact, she only freaks out when the castle seems to be trying to keep her from repairing the tapestries, making her probably the least scared of the group (obviously discounting Twilight and Pinkie). And Fluttershy...well, she's Fluttershy.
Fridge Brilliance: Pinkie's ability to play the organ actually makes perfect sense given that she's shown to be proficient with a number of other instruments in previous episodes. She clearly has an affinity for music, so picking up how to play an organ wouldn't be very hard for her.