Hello, and welcome to my Q and A video series
on the pandas library in Python.
So the question for today is
how do I read a tabular data file into pandas.
Great question.
So what is tabular data?
Quick reminder, tabular data is just data
that looks like a table, meaning data with rows and columns.
Looks like an Excel spreadsheet.
That's all a tabular data file is.
Common formats like CSV, comma separated value,
that's tabular data.
Okay, so if we're going to read it into pandas,
we first need to import pandas.
And the conventional import is import pandas as pd.
And then we're going to use a example data set
that I've picked out,
and that dataset is a dataset of Chipotle orders,
the Chipotle restaurant chain online orders.
And we're going to use to read it in,
we're going to use the read_table function.
So pd.read_table
and then for me, what I could do
is to say data/chipotle.tsv.
Now chipotle.tsv is the name of the file
and it's in the data subdirectory
relative to my working directory.
So read_table, the first argument is the name of the file
or its path and I can use a relative path here.
Now this would work for me, but it's not going to work for you
and so I'm going to give you something that will work for you.
So I want you to type this along with me.
http://bit.ly/chiporders
So read_table can read directly from a URL
and at this URL, I have put the data file.
And we can even take a look at it.
So I am just going to put, I'm going to run that URL in my browser
and what will come up is the underlying data file.
So, when we run this,
we get back a perfectly formatted data file.
Looks great.
So we've got the index on the side,
we've got some column headers up top,
and we've got the body if you will, the data itself,
kind of in the middle.
So this turned out perfectly.
Now, why did it turn out perfectly?
Well, because this file, the underlying data file,
happened to be formatted perfectly
that it would work with the read_table default parameters.
Meaning by default read_table assumes
that your file is tab separated,
and this data file, chipotle.tsv is tab separated.
Another thing is that it assumes
that the first row is a header row,
and this file does have a header row,
which is this right here, as you can see in the file.
So this file, we got lucky, this one worked perfectly,
it's how we like it so we're going to save it
as a DataFrame object called orders,
and then you can always look at the first five rows
just to remind yourself of that
without overflowing the screen.
Alright, so that was an easy case.
Let's take a harder case.
So this time, we're going to say pd.read_table
and we're going to pass it the URL, another bit.ly link,
bit.ly/movieusers
bit.ly/movieusers.
These are users of a website that rated movies,
it's demographic information.
So we'll run this, and this time around,
you can tell something is clearly wrong
because it did not... it put everything in one column.
And you don't even have to open up the file
to notice that there are these pipe characters
that are separating the fields and that's the problem.
So we need to communicate to pandas
that there is this pipe separator.
Now how do I tell that to read_table?
Well what you want to do is Google pandas read_table
and you will be taken to this page,
which is the documentation,
and you will find things like the second argument here
is called sep, and it's the delimiter to use,
and the default, as I mentioned, is the tab character.
So we need to instead tell pandas
our separator is the pipe character.
So when we run that,
you see this looks a lot better than before,
because now each of the fields are in their own column.
Now that's not the only problem.
There's also this problem that the first row
is being interpreted as the header row,
and it's not the header row.
So that's just a row of data.
So we need to tell pandas that there is no header.
header=None
And that's great.
So it's now replaced it with default values,
which are just integers,
but at least now the data is in the data section
of the DataFrame, if you will.
So, there's one more thing here
we probably actually want to have column names,
so we can define them.
So I'm going to create a Python list called user_cols
and it's going to be a list, and it's a list of strings
and I happen to know what each of these columns represent
so I'll just type these in here.
The first column is user_id, the next is age,
the third is gender, the fourth is occupation,
and the fifth is zip_code.
So I'm just creating a Python list of strings of column names,
and then I'm going to pass that to read_table.
So I'm going to say names=user_cols.
So I'm passing that list
as the value of the argument names,
so that pandas knows to use those strings
as the column names.
So we'll run that.
And now it looks perfect.
And this is ready for us to work with.
We can save it as an object like before,
so this is now a DataFrame object,
and then anytime we want to look at it,
we can just print it out,
or look at the first five rows or whatnot.
So, that is how you work through using read_table.
If you have tabular data, just trying reading it.
See what happens.
If it doesn't look right,
just iterate through that process
until you get it right.
Look at the read_table documentation
and you'll be able to figure out
which arguments you need to tweak
in order to get it to work.
So, I'm always going to end these videos with a bonus tip.
The bonus tip for today is just a read_table argument
that I think is super useful.
Actually two arguments.
One is skiprows and the other is skipfooter.
So what do we use these for?
Well, let's pretend you have a data file
and someone has inserted some text or some notes
at the top or the bottom of the file,
and it's not really data, it's just notes about the file,
but they put it in the file itself,
you can use this to tell pandas
please skip those certain rows at the top or at the bottom
so that it can just focus on the actual data part
of the file, okay?
Alright, thanks so much for joining me.
Please click subscribe
if you want to see more videos like this,
and please let me know your pandas question
in the comment section below and I will probably answer it.
I might even make a video about it.
Thanks so much and I look forward to seeing you soon.