I am doing the classic split-apply-recombine thing in R. My data set is a bunch of firms over time. The applying I am doing is running a regression for each firm and returning the residuals, therefore, I am not aggregating by firm. plyr
is great for this but it takes a very very long time to run when the number of firms is large. Is there a way to do this with data.table
?
Sample Data:
dte, id, val1, val2
2001-10-02, 1, 10, 25
2001-10-03, 1, 11, 24
2001-10-04, 1, 12, 23
2001-10-02, 2, 13, 22
2001-10-03, 2, 14, 21
I need to split by each id (namely 1 and 2). Run a regression, return the residuals and append it as a column to my data. Is there a way to do this using data.table
?
DWin's answer is correct for v1.8.0 (as currently on CRAN). But in v1.8.1 (on R-Forge repository),
:=
now works by group. It works for non-contiguous groups too so there is no need tosetkey
first for it to line up.To upgrade to v1.8.1 just install from the R-Forge repo. (R 2.15.0+ is needed when installing any binary package from R-Forge) :
or install from source if you can't upgrade to latest R.
data.table
itself only needs R 2.12.0+.Extending to the 1MM case :
The example above only has 2 groups, is quite small at under 40MB, and
Rprof
shows 96% of the time is spent inlm
. So in these cases:=
by group is not for a speed advantage really, but more for the convenience; i.e., less code needed to write and no superfluous columns added to the output. As size grows, the avoidance of copies comes into it and speed advantages start to show. Especially,transform
inj
will slow down terribly as the number of groups increases.I'm guessing this needs to be sorted by "id" to line up properly. Luckily that happens automatically when you set the key:
EDIT from Matthew : This is all correct for v1.8.0 on CRAN. With the small addition that
transform
inj
is the subject of data.table wiki point 2: "For speed don'ttransform()
by group,cbind()
afterwards". But,:=
now works by group in v1.8.1 and is both simple and fast. See my answer for illustration (but no need to vote for it).Well, I voted for it. Here is the console command to install v 1.8.1on a Mac (if you have the proper XCode tools avaialble, since it only there in source):
(For some reason I could not get the Mac GUI Package Installer to read r-forge as a repository.)