Okay. Let's turn our attention now to working with
Excel documents. What I'd like to use is an example
here, [SOUND] is data from the Electric Reliability Council of
Texas. This is an organization that manages the flow of
electricity to millions of Texas customers. They also provide
a lot of publicly available datasets. On things like load,
or the amount of electricity that's used by their customers.
This is one such example for the calendar year 2013.
A lot of organizations publish this type of
data. And often they will publish it as Excel
documents. What that means for us is that we
have to have a way of programmatically working with
Excel because many times, we'll want to process dozens
or perhaps hundreds of files all of which have
been published as Excel. What I'd like to introduce
you to here is a python module called XLRD.
XLRD allows us to work with Excel files whether it's the old XLS format or the
new XLSX format. This module will allow us to read-in all of the data from
an excel workbook and, work with it in any way that we need to in a
Python program. There's also an XLWT module, which
allows us to create Excel files, if we
need to do that programatically. Okay so the first thing
that I want to do before we dive in and
take a look at this example code is actually go
through and run it. Okay. What I've done with this program
is produce output that illustrates a lot of the features
that you might want to work with when using XRLD one
processing excel data. So, one of the things I would
like to illustrate here, is how we can read our data
from an Excel file entirely into python list and work
with it. How we go about working with rows and
columns and individual cells in an Excel file using the
XLRD module. And then finally, a little bit of information about
dates. This is primarily because of the way that dates
are represented in XML that we need to talk about this.
So let's go back and look at this code. Here
we're simply specifying which file it is that we're going to load.
And in this case, it's an XLS file, an old
format Microsoft Excel file. Okay? And then, the bulk of the
work that we're doing here is actually done in the Parse
File function. Alright so this is the command we use to
open a workbook and you note that we're using the same
variable name her okay? Then we need to specify each sheet
we'd like to work with. So here we're selecting the sheet
zero and you'll find that XLRD is entirely zero based indexing.
So the very first column is actually column
zero, the first row is actually row zero. Okay,
here's an example of doing a list comprehension,
and in this case, what we're doing is essentially
looping through all of the rows and columns and reading all of
that data into a Python list. What I'm doing here, then,
is simply printing out the value of Row Three and Column Two
for this list that we've just generated. Now if I
scroll down a little bit, we can see that following the
list comprehension piece, what we're doing here is looping through the
entire sheet one row at a time, and then moving across
the columns. And the way that I've set this up,
is that once we get to row 50, what we're going
to do is essentially is print out the values in that
particular row. One column at a time. Now again, this is
a piece of code that does nothing more than
simply illustrate the functions and methods that you'll want
to work with when using XLRD. In the next
piece of our example code, I'm illustrating how to work
with rows and columns in XLRD. Alright, in this
case what we're going to do here is we're
going to simply grab the number of rows for
this particular sheet. And then we'll print that out, alright.
This is an illustration of how to check the
data type, or the value type, for a particular cell.
Using the cell type method, for sheet, for objects
of type sheet, okay? Then cell value. This actually gets
the value that's stored in that cell as the
appropriate Python value. Whether it's a floating point value or
something else. And then finally this is actually [SOUND]
a pretty cool method. Here, we can slice the values
out of a particular column. What that means it that
we could say, okay, I want three values from this particular
column and I want to to start on row one, so
actually the second row down. And I want to go through
rows one, two, and three. Up to row four but
not including row four. And I want to take those three
values sliced out of that particular column. Okay. And then here
we are doing something very similar to what we did here.
Which is actually checking the type of the value in
the given cell. But in this case we have a
date in this cell Okay. So, we're going to pull out
the cell value. Now, it turns out that, in the old
Excel format, dates were represented simply as floating point numbers.
So what we can do is use the XLRD date
as tuple method to get us that time in a
way that allows us to work with it as a date
in Python. Okay, let's go look at that output one
more time. Okay, so here's our list comprehension. We're simply
printing out the value at row 3, column 2 from
the list into which we read all of the excel data.
So here we can see where I was looping through
the rows and columns and printed out all of the values
on row fifty Okay, and then number of rows in
this sheet happens to be more than 7200. Alright? Now note
here that the type of data in cell two,
is specified as two. You can look up, if you
want to, in the XLRD documentation Exactly what all of
these different type identifiers reference. In this case it's a
floating point number. We can see that the value that
we've got there is 1,036 and change, okay? And here's
the output from having taking a slice out of Column
Three. Okay, so here's that piece where we were working
with dates. Now XLRD does distinguish the fact that that
particular cell holds a date. But here's the time, represented
as a floating point number. Okay? What we can do
is convert this to a Python datetime tuple Using that XLRD
method that I showed you. And this gives us a
much better representation of the data that we're interested in. So,
in this case, what we've got is, row one, column
zero. So it's that very first cell at the top, okay?
This one here, right? And note that what we're pulling out there
as the value is, in fact, year 2013, January 1st, 1 AM.