I like you to bits, Blick, so here's the straight poop from my experience. For the record, when I first jumped into paid pixels I was nowhere near ready, and it's only through the grace of the almighty sun god that I managed to stay afloat long enough to have any success at all in it. Some of what I'm gonna post is negative; I'm not trying to discourage you or anybody else, because what you're talking about is entirely possible and a lot of people do it who are way dumber and less skilled than you or I.
Brief rundown of my experience: did a 2-ish year stint as a fulltime pixelist before enrolling in art school, now I only take pixel contracts part time, and take onsite web contracts full time. I don't miss full-time pixels much, although when I gave it up entirely I really missed it. It's probably the most addictive and most uniquely challenging job I've ever had.
For a person without self motivation, is freelancing really a good idea at all? Would being an in house artist be any different?
Without self motivation you're going to crash. Hard. Everyone has times when they just have no motivation (especially me, as my work ethic is nowhere near ironclad). Unfortunately, as a freelancer those times will also mean you have no food, gas, or electricity. The pressure of a development cycle isn't really any easier to deal with in-house, although starving to death isn't as big of an issue.
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?
While it's common to be more competent at one medium than another, for the most part art is art. if you think your drawing skills are weak, then your other skills are likely proportionally stunted as well. I'd put some serious effort into expanding your horizons before I considered a big project where you might be wearing multiple hats, although on a case-by-case basis the only person who can best judge if your skills are right for a job is teh u.
Are there any companies you would tell me to stay away from?
with a handful or exceptions, most of the "danger" companies come and go (or change names) so quickly that the best way to detect them is to develop your bullshitdar. The Goblins thread is a good place to start, although it's mostly just something you pick up. You're a smart lad, I wouldn't worry about it too much, although it does take some time.
For offers that require relocation, does the company generally pay for the move, does the artist or is the cost at least split?
depends wholly on the company, there are policies from "yes, in full with transient housing in a hotel" to "no, working for us is a priviledge, flee your former home now" to everything in between, including arrangements where you get a relocation budget and if you exceed it, then too bad.
What programs should I become proficient in?
whatever you need to get the job done asap. most will expect promotion, maybe gale, at the very least photoshop. In my experience people are willing to flex on what tools you use as logn as you deliver in the format they're looking for. If you pixel for long enough you'll eventually run into someone who wants you to use their crappy in-house editor, if not for the actual spriting then for assembly of animations and such, so be ready ;p
aside from those tools, feel free to use any additional ones that you feel comfortable with... it's kind of agreed upon among a lot of the artists i've spoken to that a lot of the "rules" go out the window when you're in the trenches of paid pixel art. while my hobby stuff is entirely pixel purist, i've been repeatedly guilty of using filters, photomanipulation, and adaptive palette optimization to get the job done in time. the only people who really give a shit are missing the point (two points actually, one point being the deadline, and the other being my rent :p)
What should I expect in terms of pay and work load?
There are way harder jobs that pay a lot less, but this isn't easy money, it's a labor of love. If I had to give any numbers, Adam's are pretty spot on. You should "expect" what you ask for; this is REALLY important. While skill, speed, and experience plays a big part in how much a client is willing to pay, how much they actually end up paying depends on what you ask for and how well you stick to that and convince them that you're worth it. You determine your own worth for the most part, and even people who try to lowball you will understand what good art actually costs... some people aren't out for good art though, and just want cheap art. That's usually what they get.
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?
My portfolio is incredibly small because once a piece gets to be what I consider old I toss it. IMO, even if you don't have a lot to show, quality takes major precedence over quantity. Fluffing your portfolio with pieces that aren't representative of your pro-game isn't worth it as it'll only take one bad piece to turn a prospective client off.
I suppose that's all my questions for now.
If you have any more, shoot. I'm by no means a beacon of knowledge on the topic but I'll be glad to offer any answers or better yet help :p