I can't comment on the professional pixel art community, but speaking in terms of a general creative environment, here's my advice/answers:
For a person without self motivation, is freelancing really a good idea at all? Would being an in house artist be any different?
Personally, I'm either extremely motivated and active and excited or I'm dead bored. There's no inbetween for me. Among friends of mine who work in the creative industry, from graphic design to animation to game development, that sort of thinking seems to be the norm (or close to it). The difference between motivation and laziness is getting interested in a project - and getting interested in a shit project is a skill that is
critical if you want to be able to make a living in any creative field. Different things work for different people - I won't talk about that too much, it's enough to spawn a whole different topic.
The upside to freelance work is that you have complete control over the projects that you take on. If you only want to work on things that call to you, if you only want to work on projects that you don't have to struggle to sustain interest with, then you can do that. If you don't want to live on the street, though, you need to learn how to save money. You have to know the average amount of time that you'll go without work, and you have to save up enough money to sustain your basic needs during that period. Freelance work is amazing and terrifying all at the same time.
Being in-house is different. You'll likely work in a more specific realm of your field. If you're able to figure out a decent way to maintain interest in crap projects, you have everything to gain from being in-house. This isn't to say that everything you work on in-house will be crap - you'll definitely get to work on some gems - but be prepared for the worst at the start of every project and you can't go wrong. I make this sound far more grim than it actually is, don't let that description scare you - it's just a good state of mind to be in.
What it boils down to is that, regardless of what creative field you're in, you usually end up doing crap to pay the bills, and then you do work that you really care about on your own time (and occasionally you get a chance to do it at your job). Unless you're incredibly lucky, you will not find a creative field to work in where you get to follow your own vision and create your dream work without having to make heavy compromises along the way.
Personally - I've found that the best way to work, for me, is to work in-house at a place that I don't hate, and to do freelance jobs that I enjoy on the side. I'm really happy doing this. Most of my friends and colleagues do the same, and it keeps them happy and sane.
My pixel art skills are decent right now, but my drawing is surprisingly weak, would that matter or should I start working on drawing too?
In my position, I can get away with not drawing (or with doing crap drawings). In most creative fields, you need to be able to draw. It's never a bad skill to have, it's always worth getting better at. I'm constantly trying to get better at drawing, even though it's not a skill that I actively or frequently use - and what I've found is that a lot of the things I learn about drawing can be applied to other steps of the creative process.
Are there any companies you would tell me to stay away from?
Can't really comment on this one. In general, though, it's better to shop around for as long as you possibly can. If you're going to take a staff position somewhere, make sure it's the absolute best that you can do, either artistically or financially or both.
For offers that require relocation, does the company generally pay for the move, does the artist or is the cost at least split?
Depends on the company. In my experience, companies will only help you pay for a move if you've been employed for a set period of time. They usually won't help someone move
before they've become an employee.
What programs should I become proficient in?
This varies by industry, but industry standards across the board tend to be Adobe products for 2D. Each profession has its own special tools though, so as far as pixel art goes, I can't say what people will expect you to know.
What should I expect in terms of pay and work load?
I can't talk about specific pixel art rates. I know that for animators and graphic designers, a $300 day rate is fairly standard (in Los Angeles, at least). In general, if you're a freelancer, expect not to sleep much. In general, remember that the faster you can work (while keeping your work above a certain level of quality), the more money you'll make. Learn every shortcut you can, and try to develop the most efficient process that you can.
What would you say is the best for constructing a portfolio, a diverse set of mediocre pieces or the ones I feel are the best, but probably would neglect diversity? Perhaps just throw everything in chronologically to show a line of improvement?
Always put your best work in your portfolio. Put the work that you're proud of in there. If you desperately need a job, and you know that a certain employer is going to look for a certain type of work, then it's alright to stick a couple of more mediocre (but focused) pieces in there. Whatever you do, do not tailor your whole portfolio around what you think an employer wants to see. This seems like it's a good idea, but I've never seen it go well, ever.
Case in point, a good friend of mine was trying to get a job in a certain field that had a certain aesthetic. He completely restructured his portfolio (going so far as to create new pieces) in order to fit that aesthetic. He didn't get a job at any of the places he applied to. On the other hand, my portfolio is absolutely crazy, and absolutely not tailored to or suited for any commercial work - but it gets me commercial work time and time again. In general (and this is just my experience, mind you), the people who are going to be hiring you in a creative field are not going to be stupid. They'll be able to see the brilliance in your work if it's there, and they'll be able to figure out what you're generally capable of based on it.
I'm sure that isn't exactly what you were looking for, but for what it is, that's the advice I have.
In general, I'd say that the best piece of advice that I can offer is that, the sooner you accept the fact that shit work pays the bills and great work doesn't, the better off you'll be. It doesn't mean that you should stop doing great work (by any means!), it doesn't mean that you should stop looking for it, it just means that you shouldn't expect for it to pay your bills.