Learn to filter rows in R data.tables using the dt[i, j, by] syntax. Use row numbers, logical expressions, or the special variable n to select rows. Data.table automatically creates indices on filtered columns, speeding up repeated queries from 0.2 seconds to 0.002 seconds on large datasets.
Chapters
In this lesson, we will look at how to subset or filter rows from a data table using row numbers and logical expressions. The general form of a data table is dt I j by. It is read out loud as Take dt filter rows in I, then compute j grouped by. We will look at the first argument, I. Here. The functionality is similar to dataframes, but more convenient and enhanced. Rows can be filtered using row numbers as you would do in a data frame, except the first argument is always interpreted as a row operation, irrespective of whether or not you specify a comma. For example, to subset the third and the fourth rows, you can use the code as shown here. If you want to exclude a set of rows, you can use the negative sign or the exclamation mark as shown here. Datatable contains a few handy special symbols or variables that make many operations efficient. One such symbol is n, which is an integer vector of length 1. When used in the I argument, it returns the total number of rows in the data table. To get the last row, for example, you can simply do batrips square bracket n. Similarly, to get all but the last 10 rows, you can specify the I argument as 1 colon n minus 10. Since n is the total number of rows, you're essentially creating a continuous sequence of integers from one to the required row number. You can also construct expressions resulting in a logical vector in the I argument. This returns only those rows that evaluate to true. However, note how we refer to the columns in the data table as if they are variables. This is because within the scope of data table, that is within the two square brackets, columns are seen as if they are variables. This avoids unnecessary repetition of the dollar sign when referring to the column names while constructing expressions, and therefore avoids subtle and hard to track bugs. Also, the resulting expression is much cleaner and easier to read. Here's another example. In this example, we filter for rows where startterminal column is 58 and endterminal column is not 65. Again, note the concise and clear syntax of datatable. As mentioned in the first video, Datatable is fast. It automatically creates an index or or key of the columns used to subset the data so that any subsequent operations on that column are incredibly faster. You can have a look at all such indices using the indices function. Let's look at an example. First, we create a data table DT with 10 million rows. When we call indices on dt, we see that the result is null. Thus, at this point the data table has no indices. When we perform a subset operation for the first time, we can see that it takes about 0.2 seconds. Now, when we run indices on dt, again, notice that X is an index. So when we perform the same operation again, it takes only about 0.002 seconds. It's time for you to practice subsetting. Data table.
