In this video, we will learn how to specify a subset of data within an Excel worksheet that we wish to import using ReadMatrix. There are several reasons why we might wish to import only a subset of data from an Excel worksheet. One reason is to avoid the problem of importing non-numeric data as NAN.
which then we have to deal with when we process the data. Another reason is that we may only need a subset of the data in order to accomplish our processing goals. Just as we used a name-value pair to specify the sheet that we wish to read, we will use a name-value pair to specify the subset of data that we wish to read from a sheet. In this case, the name is range, but the value can take one of several different forms. ReadMatrix reads data into a matrix, which is inherently rectangular, so we will always be reading some sort of rectangular block of information from the worksheet.
One way of specifying a rectangular block is to give two opposite. corners of that block. This can be either of the two pairs of opposite corners, and they can be specified in either order.
So there are four possible combinations for any rectangular block. We'll see an example or two later in this video. Another method is to specify the top left corner of the rectangular block.
In this case, read matrix will determine the bottom most row that contains data and the right most column that contains data. We can specify a subset of columns to read. In this case, read matrix once again will determine what the lower extent of the data is and as in the case when we did not specify a range, it will ignore leading blank rows and leading headers that are non-numeric.
We can specify the rows we wish to import. In this case, read matrix will ignore leading blank columns, but just as when we did not specify a range, it will not ignore leading text columns. So it would import those as NAN.
Again, in this case, it will determine what the rightmost column is that contains data and read to that point. There are also a couple of options for specifying the range, which we will not cover here. If you are interested, please refer to the MATLAB documentation on ReadMatrix.
For a first simple example, let's go back to our worksheet Demo1, which contained only numeric data in a rectangular block beginning in cell A1. The plan is to read the last three columns of data. from this worksheet.
There are several ways to accomplish this. One method is to simply specify the top left corner, C1, and as we see, it does indeed import just the last three columns. Another method is to specify opposing corners.
Now, as mentioned earlier, there are four possibilities, which I have listed on the screen here, but I will choose E1, C4. Note that the two corners are separated by a colon. Again, we get the same results.
Yet another possibility is to simply specify the columns and let MATLAB determine where the bottom is. In this case, you would simply specify the range as C, colon, E. And as we see, this works again the same way. Next, let's try to read only the values that are not on the outer edge of that block of data. Specifying opposing corners is really the only feasible way to do this.
And again, there are four possibilities shown here. But here, I will choose what is probably the most common case, which would be B2 colon D3. As we see...
this does indeed import just those interior values from that rectangular block of numbers. Note that we cannot just specify the columns B, colon, D, because then it would read the values that are in rows 1 and 4. Similarly, we can't just specify the upper left corner, because in this case, read matrix determines the lower right corner, and would end up reading part of column E and part of the lower row, row 4. You can try those at home if you want to verify that claim. As you hopefully remember from previous videos, ReadMatrix will not ignore columns of non-numeric values to the left of the numeric data being read.
For example, when we read in all of the data from sheet demo 6, it imported the leading column of headers in column A as NaN. To avoid having all of those not a numbers floating around in our variable, we can simply tell read matrix using the range name value pair not to import that column at all. One method would be to specify the opposite corners of the block of data. One possibility would be B3 colon F6, but there are, of course, three other possibilities there.
In this case, we also need to specify the sheet since this is not the first sheet in the workbook. As we see, it did indeed import only the numeric data and did not bring in that first column of text as NAN. Note that we could have actually specified B1 colon F6, but when it saw that rows 1 and 2 were purely text information, it would have ignored them and not brought them in.
You can do the same thing by specifying only the upper left corner, B3, in which case readmatrix itself determines that the lower right corner is F6. Yet another option is to simply specify the columns, B colon F. In this case, readmatrix determines the lower extent, And again, it ignores the text headers at the top.
In the next video, we will continue our investigation into reading only a subset of data from an Excel workbook of the block we wish to read. And this munchkin, come on, stop it. Another method is to specify the top left corner.
Munchkin, this isn't going to work. If you're going to scratch, you're going to have to go out. That's the way it is.