by Hong Ooi
Sr. Data Scientist, Microsoft
The dplyr package is a popular toolkit for data transformation and manipulation. Over the last year and a half, dplyr has become a hot topic in the R community, for the way in which it streamlines and simplifies many common data manipulation tasks.
Out of the box, dplyr supports data frames, data tables (from the data.table package), and the following SQL databases: MySQL/MariaDB, SQLite, and PostgreSQL. However, a feature of dplyr is that it’s extensible: by writing a specific backend, you can make it work with many other kinds of data sources. For example the development version of the RSQLServer package implements a dplyr backend for Microsoft SQL Server.
The dplyrXdf package implements such a backend for the xdf file format, a technology supplied as part of Revolution R Enterprise. All of the data transformation and modelling functions provided with Revolution R Enterprise support xdf files, which allow you to break R’s memory barrier: by storing the data on disk, rather than in memory, they make it possible to work with multi-gigabyte or terabyte-sized datasets.
dplyrXdf brings the benefits of dplyr to xdf files, including support for pipeline notation, all major verbs, and the ability to incorporate xdfs into dplyr pipelines. It also provides some additional benefits which are more specific to working with xdfs:
The RevoScaleR functions require keeping track of where your data is saved. In some situations, writing a function’s output to the same file as its input is allowed, while in others, it causes problems. You can often end up with many different version of the data scattered around your filesystem, introducing reproducibility problems and making it difficult to keep track of changes. dplyrXdf abstracts this task of file management away, so that you can focus on the data itself.
Related to the above, the source xdf to a dplyrXdf pipeline is never modified. This provides a measure of security, so that even if there are bugs in your code (maybe you meant to use a
mutaterather than a
transmute), the original data is safe.
Consistency of interface: functions like
rxSummaryuse formulas in different ways, because they are designed to do slightly different things. Similarly, many RevoScaleR functions use factors but don’t automatically create those factors; or they require handholding when trying to combine factor with non-factor data. With dplyrXdf, you don’t have to remember which formula syntax goes with which function, or create factors yourself. If you do have to create factors, it provides a new verb (
factorise) to streamline this as well.
The verbs in dplyrXdf all read from xdf files and write to xdf files. The data is thus never read entirely into memory, so a dplyrXdf pipeline will work with datasets that are arbitrarily large.
The package is available for download from Github, at https://github.com/RevolutionAnalytics/dplyrXdf. If you have the devtools package installed, you can download and install it from within R using the command
Note that dplyrXdf is a shell on top of the functionality provided by Revolution R Enterprise, which is the commercial (paid) distribution of R from Revolution Analytics. You must be an RRE customer to make use of dplyrXdf. It will not work with Revolution R Open or standard open source R, as these distributions don’t support xdf files.
A sample dplyrXdf pipeline
For this example I’ll use the flights dataset from the nycflights13 package. This is one of the datasets used in the dplyr vignettes, and crops up in many other places besides.
Consider a simple task: get the average delay and total distance covered (in kilometers) in the first half of 2013, by carrier, sorted by descending delay. This isn’t very complicated, conceptually speaking: we want to do a row selection, then some basic transformations, followed by a summary, and then order the output by one of the columns.
This translates into the following sequence of RevoScaleR function calls:
# select the rows flights_rx1 <- rxDataStep(flightsXdf, outFile="flights_rx1.xdf", rowSelection=month <= 6 & year == 2013, overwrite=TRUE) # variable transformations flights_rx2 <- rxDataStep(flights_rx1, outFile="flights_rx2.xdf", transforms=list(dist_km=distance*1.6093, delay=(arr_delay + dep_delay)/2), overwrite=TRUE) # convert carrier into a factor variable (or rxSummary will complain) flights_rx3 <- rxFactors(flights_rx2, factorInfo="carrier", outFile="flights_rx3.xdf", overwrite=TRUE) # use rxSummary to get the summary table(s) (could also use rxCube twice) flights_rx4 <- rxSummary(~delay:carrier + dist_km:carrier, data=flights_rx3, summaryStats=c("mean", "sum")) # extract the desired tables from the rxSummary output flights_rx4_1 <- flights_rx4$categorical[][c("carrier", "Means")] names(flights_rx4_1) <- "mean_delay" flights_rx4_2 <- flights_rx4$categorical[][c("carrier", "Sum")] names(flights_rx4_2) <- "sum_dist" # merge the tables together flights_rx5 <- merge(flights_rx4_1, flights_rx4_2, by="carrier", all=TRUE) # sort the results flights_rx5 <- flights_rx5[order(flights_rx5$mean_delay, decreasing=TRUE), ] head(flights_rx5) #> carrier mean_delay sum_dist #> 11 OO 72.16667 2750.294 #> 7 F9 24.81886 873367.110 #> 6 EV 21.80613 23400472.426 #> 16 YV 20.78378 128401.219 #> 8 FL 15.61127 1979744.767 #> 1 9E 13.85877 7160918.303
The equivalent in dplyrXdf would be the following pipeline:
flightsSmry <- flightsXdf %>% filter(month <= 6, year == 2013) %>% mutate(dist_km=distance*1.6093, delay=(arr_delay + dep_delay)/2) %>% group_by(carrier) %>% summarise(mean_delay=mean(delay), sum_dist=sum(dist_km)) %>% arrange(desc(mean_delay)) head(flightsSmry) #> carrier mean_delay sum_dist #> 1 OO 72.16667 2750.294 #> 2 F9 24.81886 873367.110 #> 3 EV 21.80613 23400472.426 #> 4 YV 20.78378 128401.219 #> 5 FL 15.61127 1979744.767 #> 6 9E 13.85877 7160918.303
Even with this very straightforward example, dplyrXdf hides the complexity of calling RevoScaleR functions while retaining their power. In particular, note the following:
There is no need to keep track of input and output file locations: the verbs in the dplyrXdf pipeline will automatically create files and reuse them as needed. Files that are no longer used will be deleted, so there won’t be multiple orphaned files cluttering up your hard disk.
summariseverb is much simpler to work with than the RevoScaleR
rxSummaryfunction. It doesn’t require scanning through a list of output objects to find the information you’re after, and it accepts grouping variables of any type (numeric, character or factor).
The pipeline notation makes it clear at a glance what is the sequence of operations being carried out. This is one of the major benefits of dplyr, and is now also available for those working with xdf files.
dplyrXdf supports all the basic dplyr single-table verbs:
selectto choose rows and columns
transmuteto do data transformation
group_byto define groups
doto carry out computations on grouped data
arrangeto sort by variables
renameto rename columns
distinctto drop duplicates
Under the hood, they work by translating your pipeline into calls to the base RevoScaleR functions for working with xdf files: for example,
rxDataStep to compute transformations;
rxSort, and so on.
Most of these verbs work exactly as they do in dplyr. Thus if you know how to use dplyr, then you also know how to use the bulk of dplyrXdf.
dplyrXdf supports the main table-join verbs from dplyr:
full_join. The syntax is the same as for the dplyr versions, including joining on non-matching column names. The underlying implementation uses
rxMerge with the appropriate arguments for each type of join.
For example, one of the joins in the dplyr two-table verbs vignette joins the flights table with the airports table, using the columns
dest (in flights) and
faa (in airports). The same code in dplyr also works in dplyrXdf:
airportsXdf <- rxDataFrameToXdf(airports, "airports.xdf", overwrite=TRUE) flightsJoin <- left_join( flightsXdf %>% select(year:day, hour, origin, dest, tailnum, carrier), airportsXdf, by=c("dest"="faa"))
head(flightsJoin) #> year month day hour origin dest tailnum carrier faa #> 1 2013 10 1 19 JFK ABQ N554JB B6 ABQ #> 2 2013 10 2 20 JFK ABQ N607JB B6 ABQ #> 3 2013 10 3 19 JFK ABQ N591JB B6 ABQ #> 4 2013 10 4 20 JFK ABQ N662JB B6 ABQ #> 5 2013 10 5 19 JFK ABQ N580JB B6 ABQ #> 6 2013 10 6 19 JFK ABQ N507JB B6 ABQ #> name lat lon alt tz dst #> 1 Albuquerque International Sunport 35.04022 -106.6092 5355 -7 A #> 2 Albuquerque International Sunport 35.04022 -106.6092 5355 -7 A #> 3 Albuquerque International Sunport 35.04022 -106.6092 5355 -7 A #> 4 Albuquerque International Sunport 35.04022 -106.6092 5355 -7 A #> 5 Albuquerque International Sunport 35.04022 -106.6092 5355 -7 A #> 6 Albuquerque International Sunport 35.04022 -106.6092 5355 -7 A
Tbls and file management
To facilitate the task of file management, dplyrXdf defines a new
tbl_xdf class that extends the
RxXdfData class. This is what allows it to keep track of which data sources should remain untouched, and which can be modified or overwritten as part of a pipeline. To the base RevoScaleR functions, a
tbl_xdf object is just a normal xdf data source; thus, existing code dealing with xdfs should work with minimal modification. However, the verbs implemented in dplyrXdf will recognise when they are passed a
tbl_xdf, as opposed to a normal xdf, in which case they will delete their input file after writing the output file. Thus there is always only one file that represents the latest stage of a pipeline.
To create a
tbl_xdf yourself, just call the
tbl function on an xdf data source:
flightsTbl <- tbl(flightsXdf) flightsTbl #> tbl_xdf Source #> "C:\Users\hongooi\Documents\revoDplyr\blog\flights.xdf" #> fileSystem: #> fileSystemType: native
However this should rarely (if ever) be necessary, as dplyrXdf will automatically create a tbl for you if you pass a raw xdf to a pipeline.
dplyrXdf creates its output files in R’s temporary directory, so when you close your R session, these files will be deleted. This saves you having to manually delete files that are no longer in use, but it does mean that you must copy the output of your pipeline to a permanent location if you want to keep it around. You can use
rxXdfFileName for this purpose:
# same dplyrXdf pipeline from before flightsSmry <- flightsXdf %>% filter(month <= 6, year == 2013) %>% mutate(dist_km=distance*1.6093, delay=(arr_delay + dep_delay)/2) %>% group_by(carrier) %>% summarise(mean_delay=mean(delay), sum_dist=sum(dist_km)) %>% arrange(desc(mean_delay)) # store the result of the pipeline where it won't get deleted outFile <- "./flightsSmry.xdf" file.copy(rxXdfFileName(flightsSmry), outFile, overwrite=TRUE)
Non-xdf and non-local data sources
Despite the name, dplyrXdf supports all file data sources defined by RevoScaleR, not just xdf files. This includes delimited text (
RxTextData), SAS datasets (
RxSasData) and SPSS datasets (
RxSpssData). If you pass one of these data sources to a dplyrXdf pipeline, it will import the data to an xdf file first before executing the rest of the pipeline.
For the moment, dplyrXdf only supports files stored in the local filesystem. Support for datasets stored in HDFS (using the
RxHdfsFileSystem class) may appear in a future version. For data stored in a SQL database, consider using the dplyr backend for that database, if available.
This article has been a quick executive-summary introduction to dplyrXdf. In a following article, I’ll discuss the differences between dplyr and dplyrXdf; while most verbs work the same way in both packages, there will be areas where they diverge (mostly due to the structural difficulties of working with big data).
Like dplyr, dplyrXdf is a package under active development. If you have any suggestions on features to add (including bits of dplyr that have been left out) or bugs that need fixing, please contact me at email@example.com.