6 ms·

Boost Your Data Munging with R

minimaxir 10y ago
While data.table does have impressive benchmarks, the rise of dplyr in the years since with its significantly more readable and concise syntax makes life a lot easier for data sets that are not super large. (https://cran.rstudio.com/web/packages/dplyr/vignettes/introduction.html https://cran.rstudio.com/web/packages/dplyr/vignettes/introd...) The vignette linked above describes some compatability between dplyr syntax and data.table, but I admit I have not tested that. Nowadays, if you are working with multigigabyte datasets, it may be worth looking into Spark/SparkR (https://spark.apache.org/docs/latest/sparkr.html https://spark.apache.org/docs/latest/sparkr.html) for manipulating big data in a scalable manner instead, as there is feature parity with data.table + analytical tools [GLM] as a bonus. (The syntax is still messy, to my annoyance)
- nerdponx 10y ago
  Nowadays I almost always use both data.table and dplyr together in my projects. It's a really expressive combination.
  - claytonjy 10y ago
    Do you use data.table syntax, or do you use dplyr with the tbl_dt class to abstract that away? I was a data.table acolyte for years, but after being forced to learn dplyr I can't imagine going back. I'm hoping if I need update-by-reference in the future I can use tbl_dt but I haven't played with it yet.
    - nerdponx 10y ago
      If I'm using them together, I'll start off a pipeline with a big DT expression, then pipe it through a few dplyr verbs as a kind of "post processing." I find that some tasks are frustrating with one package but easy with the other, so more frequently I just have some "dplyr lines" and some "data.table lines" in my scripts. And the compatibility between the two has improved a lot, so you don't get so many "invalid selref" warnings and unexpected behavior when you combine them in pipelines
- localhost 10y ago
  You can rent an r3.8xlarge instance with 344GB RAM on AWS for $2.66/hr. If that isn't enough, an x1.32xlarge instance with ~2TB RAM is $13.34/hr. Assuming your data is already in AWS, of course. Of course those machines have a crap ton of CPU power (I'm explicitly not saying cores because I'm mystified by what vCPU really means) as well which you will be hard pressed to take advantage of but if it's RAM you need they're super-cheap. If you prefer Azure, a G5 instance with 448GB RAM is available for $9.65/hr. Not a lot of need for Spark/SparkR for the vast majority of data sets given the cheapness of compute these days.
  - qaq 10y ago
    vCPU is a hyperthread
  - michaelsbradley 10y ago
    The CPU power would not be too hard to utilize, if one's task is amenable to fairly coarse-grained parallelization, which is rather easy to implement in R scripts with the help of foreach[1] and doFuture[2]. Such an approach worked to great effect for me recently, when I needed to perform a `zoo::rollapply`[3] across a time series with tens of millions of rows. The speedup when throwing more cores (7, on my laptop) at it is roughly linear. If I ever need to scale up the analysis to hundreds of millions of rows, the 128 vCPUs of an x1 EC2 instance would be well worth the $$/hour. [1] https://cran.r-project.org/web/packages/foreach/index.html https://cran.r-project.org/web/packages/foreach/index.html [2] https://cran.r-project.org/web/packages/doFuture/index.html https://cran.r-project.org/web/packages/doFuture/index.html [3] http://www.rdocumentation.org/topics/1505094 http://www.rdocumentation.org/topics/1505094
    - huac 10y ago
      Shameless self promotion: my `slide_apply` function shows 5-10x improvement over `zoo::rollapply` (despite no Rcpp call, somehow...) https://gist.github.com/stillmatic/fadfd3269b900e1fd7ee https://gist.github.com/stillmatic/fadfd3269b900e1fd7ee if your function has a well-defined rolling form, e.g. rolling mean or standard deviation, you should use a pre-optimized function for that. package `catools` has a bunch of useful ones.
- i000 10y ago
  The syntax is very concise and cryptic, but what is messy about dt[i,j,k]? data.table transformed the way I do analysis. Honestly probably 75% of my lines have are dt[] calls. It allows me to analyze on 500M row data.sets routinely on a powerful workstation interactively.
  - IndianAstronaut 10y ago
    How much memory does a 500m row data set use in data.table?
- akg_67 10y ago
  While I like and use dplyr time to time, I wish it could handle POSIXlt datetime. Also, it seems to barf on some dataframes just because the df contained a column format, dplyr doesn't support even if it is not being used in processing.
  - huac 10y ago
    just use lubridate, it has a lot less headache to it. my beef is with the new tibble-ish format of dplyr. the format doesn't support lists as cells, unlike data frames. right now, `tbl_df` is a good in-between, but since dplyr is converging to the tibble/feather format, it's gonna be rough
denzil_correa 10y ago
In Python, you have pandas and then Python is great for text processing in general. Is there anything that R provides more for data munging?
- minimaxir 10y ago
  R has a robust package ecosystem for data analysis, even better than Python's suite. ETL is just one step of the pipeline.
- eanzenberg 10y ago
  R is generally better for data science, so it's nice to be able to use 1 ecosystem for a whole project. But yes, I too have in the past used python for data munging and R for machine learning.
- nerdponx 10y ago
  Purpose-built syntax. Pandas feels like a hack by comparison.
- sgt101 10y ago
  It's a bit lame, but I am so in love with rstudio... I can't think of a python IDE that's near for data science. Simple things like being able to see all the entities you've created in your session and being able to jump back and forth through plots are really nice.
  - danielecook 10y ago
    Rodeo is a good start: http://blog.yhat.com/posts/rodeo-native.html http://blog.yhat.com/posts/rodeo-native.html
    - rz2k 10y ago
      Also a silly reason: for me the text rendering in Rodeo on a Mac without a retina display is a lot worse than the text rendering in RStudio.
  - 10y ago
elchief 10y ago
The only good solution to ETL/munging/wrangling is streaming. Loading everything into memory is not a good idea.
hackaflocka 10y ago
How to convert Wide format data to Long format data (process called 'reshaping')is very very important to understand. Certain statistical tests cannot be performed if not in Long format. The article mentions Reshape2. I also love TidyR. It's much easier to use than Reshape2 when dealing with a large number of variables (i.e., columns).
- nerdponx 10y ago
  I find Tidyr more frustrating than useful a lot of the time.
  - hackaflocka 10y ago
    Interesting, I'll have to watch out for it then. I found it more helpful than Reshape2 when trying to convert a dataset with 55 variables from wide to long format. 8 of those variables needed to be collapsed into 1 variable. With Reshape2, I had to identify all the ones I didn't want in the final result, with TidyR I just needed to identify the ones I wanted. Hence it was easier with TidyR.
mikeskim 10y ago
I went from barely using data.table to only using data.table for basically everything in less than a few years. I think this is the trend given it's faster than basically everything: https://github.com/Rdatatable/data.table/wiki/Benchmarks-%3A-Grouping https://github.com/Rdatatable/data.table/wiki/Benchmarks-%3A...
stewbrew 10y ago
Does any of these support out of memory data? IIRC dplyr can use a db as backend but I could be wrong. Anyway, I wonder why not just use a db & sql for more demanding data munging?
- IndianAstronaut 10y ago
  I routinely do my data munging in SQL/Hive/Pig. I think R is more of a complement to that. Mainly for more data munging that needs some programmatic manipulation and of course advanced statistical analysis.
- gnufx 10y ago
  Presumably that depends at least on the type of data and whether you have a suitable database to use. As another approach, for R there's an interface to parallel NetCDF in the pbdR system that you might use for distributed processing on an HPC system. There doesn't seem to be anything like pdbR for Python, for instance.

While data.table does have impressive benchmarks, the rise of dplyr in the years since with its significantly more readable and concise syntax makes life a lot easier for data sets that are not super large. (https://cran.rstudio.com/web/packages/dplyr/vignettes/introduction.html https://cran.rstudio.com/web/packages/dplyr/vignettes/introd...) The vignette linked above describes some compatability between dplyr syntax and data.table, but I admit I have not tested that. Nowadays, if you are working with multigigabyte datasets, it may be worth looking into Spark/SparkR (https://spark.apache.org/docs/latest/sparkr.html https://spark.apache.org/docs/latest/sparkr.html) for manipulating big data in a scalable manner instead, as there is feature parity with data.table + analytical tools [GLM] as a bonus. (The syntax is still messy, to my annoyance)

nerdponx 10y ago
Nowadays I almost always use both data.table and dplyr together in my projects. It's a really expressive combination.
- claytonjy 10y ago
  Do you use data.table syntax, or do you use dplyr with the tbl_dt class to abstract that away? I was a data.table acolyte for years, but after being forced to learn dplyr I can't imagine going back. I'm hoping if I need update-by-reference in the future I can use tbl_dt but I haven't played with it yet.
  - nerdponx 10y ago
    If I'm using them together, I'll start off a pipeline with a big DT expression, then pipe it through a few dplyr verbs as a kind of "post processing." I find that some tasks are frustrating with one package but easy with the other, so more frequently I just have some "dplyr lines" and some "data.table lines" in my scripts. And the compatibility between the two has improved a lot, so you don't get so many "invalid selref" warnings and unexpected behavior when you combine them in pipelines
localhost 10y ago
You can rent an r3.8xlarge instance with 344GB RAM on AWS for $2.66/hr. If that isn't enough, an x1.32xlarge instance with ~2TB RAM is $13.34/hr. Assuming your data is already in AWS, of course. Of course those machines have a crap ton of CPU power (I'm explicitly not saying cores because I'm mystified by what vCPU really means) as well which you will be hard pressed to take advantage of but if it's RAM you need they're super-cheap. If you prefer Azure, a G5 instance with 448GB RAM is available for $9.65/hr. Not a lot of need for Spark/SparkR for the vast majority of data sets given the cheapness of compute these days.
- qaq 10y ago
  vCPU is a hyperthread
- michaelsbradley 10y ago
  The CPU power would not be too hard to utilize, if one's task is amenable to fairly coarse-grained parallelization, which is rather easy to implement in R scripts with the help of foreach[1] and doFuture[2]. Such an approach worked to great effect for me recently, when I needed to perform a `zoo::rollapply`[3] across a time series with tens of millions of rows. The speedup when throwing more cores (7, on my laptop) at it is roughly linear. If I ever need to scale up the analysis to hundreds of millions of rows, the 128 vCPUs of an x1 EC2 instance would be well worth the $$/hour. [1] https://cran.r-project.org/web/packages/foreach/index.html https://cran.r-project.org/web/packages/foreach/index.html [2] https://cran.r-project.org/web/packages/doFuture/index.html https://cran.r-project.org/web/packages/doFuture/index.html [3] http://www.rdocumentation.org/topics/1505094 http://www.rdocumentation.org/topics/1505094
  - huac 10y ago
    Shameless self promotion: my `slide_apply` function shows 5-10x improvement over `zoo::rollapply` (despite no Rcpp call, somehow...) https://gist.github.com/stillmatic/fadfd3269b900e1fd7ee https://gist.github.com/stillmatic/fadfd3269b900e1fd7ee if your function has a well-defined rolling form, e.g. rolling mean or standard deviation, you should use a pre-optimized function for that. package `catools` has a bunch of useful ones.
i000 10y ago
The syntax is very concise and cryptic, but what is messy about dt[i,j,k]? data.table transformed the way I do analysis. Honestly probably 75% of my lines have are dt[] calls. It allows me to analyze on 500M row data.sets routinely on a powerful workstation interactively.
- IndianAstronaut 10y ago
  How much memory does a 500m row data set use in data.table?
akg_67 10y ago
While I like and use dplyr time to time, I wish it could handle POSIXlt datetime. Also, it seems to barf on some dataframes just because the df contained a column format, dplyr doesn't support even if it is not being used in processing.
- huac 10y ago
  just use lubridate, it has a lot less headache to it. my beef is with the new tibble-ish format of dplyr. the format doesn't support lists as cells, unlike data frames. right now, `tbl_df` is a good in-between, but since dplyr is converging to the tibble/feather format, it's gonna be rough

denzil_correa 10y ago

In Python, you have pandas and then Python is great for text processing in general. Is there anything that R provides more for data munging?

minimaxir 10y ago
R has a robust package ecosystem for data analysis, even better than Python's suite. ETL is just one step of the pipeline.
eanzenberg 10y ago
R is generally better for data science, so it's nice to be able to use 1 ecosystem for a whole project. But yes, I too have in the past used python for data munging and R for machine learning.
nerdponx 10y ago
Purpose-built syntax. Pandas feels like a hack by comparison.
sgt101 10y ago
It's a bit lame, but I am so in love with rstudio... I can't think of a python IDE that's near for data science. Simple things like being able to see all the entities you've created in your session and being able to jump back and forth through plots are really nice.
- danielecook 10y ago
  Rodeo is a good start: http://blog.yhat.com/posts/rodeo-native.html http://blog.yhat.com/posts/rodeo-native.html
  - rz2k 10y ago
    Also a silly reason: for me the text rendering in Rodeo on a Mac without a retina display is a lot worse than the text rendering in RStudio.
- 10y ago

elchief 10y ago

The only good solution to ETL/munging/wrangling is streaming. Loading everything into memory is not a good idea.

hackaflocka 10y ago

How to convert Wide format data to Long format data (process called 'reshaping')is very very important to understand. Certain statistical tests cannot be performed if not in Long format. The article mentions Reshape2. I also love TidyR. It's much easier to use than Reshape2 when dealing with a large number of variables (i.e., columns).

nerdponx 10y ago
I find Tidyr more frustrating than useful a lot of the time.
- hackaflocka 10y ago
  Interesting, I'll have to watch out for it then. I found it more helpful than Reshape2 when trying to convert a dataset with 55 variables from wide to long format. 8 of those variables needed to be collapsed into 1 variable. With Reshape2, I had to identify all the ones I didn't want in the final result, with TidyR I just needed to identify the ones I wanted. Hence it was easier with TidyR.

mikeskim 10y ago

I went from barely using data.table to only using data.table for basically everything in less than a few years. I think this is the trend given it's faster than basically everything: https://github.com/Rdatatable/data.table/wiki/Benchmarks-%3A-Grouping https://github.com/Rdatatable/data.table/wiki/Benchmarks-%3A...

stewbrew 10y ago

Does any of these support out of memory data? IIRC dplyr can use a db as backend but I could be wrong. Anyway, I wonder why not just use a db & sql for more demanding data munging?

IndianAstronaut 10y ago
I routinely do my data munging in SQL/Hive/Pig. I think R is more of a complement to that. Mainly for more data munging that needs some programmatic manipulation and of course advanced statistical analysis.
gnufx 10y ago
Presumably that depends at least on the type of data and whether you have a suitable database to use. As another approach, for R there's an interface to parallel NetCDF in the pbdR system that you might use for distributed processing on an HPC system. There doesn't seem to be anything like pdbR for Python, for instance.