Scottish leader says he recognises ‘anger’ and ‘frustration’ after humiliating by-election defeat and devastating poll