By Matthias Templ (ZHAW), Thoralf Mildenberger (ZHAW)<\/p>\n
By way of example, functionality of This blog post discusses some drawbacks of Joining various data sets can be done with many packages, but to the best of my knowledge this could be done with package data.table <\/a>. The syntax is clear and the computational speed is just amazing.<\/p>\n We could first join the first two data frames, then join the result with the third, and then join the results of that with the fourth table. In the following, a LEFT OUTER join is done using base When all the data frames are stored in one list, But is this computationally efficient<\/strong>?<\/p>\n We need a larger data set to get some insights.<\/p>\n We use the microbenchmark <\/a> package to assess the computational speed. First we put our code in functions as input for About 1.15 seconds for one merge. Not bad, but if we would increase the size of our data set slightly, the computation times would increase dramatically.<\/p>\n<\/div>\n It is relevant to ask, if more efficient code<\/strong> can be written that provides the same results. In addition, we ask the question if we can use better readable syntax<\/strong> than above?<\/p>\n Let\u2019s replace our merge<\/tt> and Reduce<\/tt> solutions with data.table <\/a> code.<\/p>\n We first call the package and store the data frames as data tables. In addition, we set a key for an efficient use of the binary search of the data.table <\/a> package.<\/p>\n The following But note, the syntax is much shorter and more user-friendly! This is all you need to write for the LEFT INNER JOIN for the given problem:<\/p>\n Lets compare the speed of all three approaches (pure merge, Reduce and data.table).<\/p>\n The solution with We can also combine lapply() and Reduce(), such as been carried out in the following example.<\/p>\n What is the aim of this code? It not straightforward to see this. First different powers of matrix P<\/span><\/strong> are applied, then the cumulative sum of resulting matrices is calculated. We can ask if we can do it with a more intuitive and more user-friendly code (just by using a simple for <\/tt>loop), to gain computational speed and to not store the whole list of matrix powers as above.<\/p>\n General note:<\/em> from This is also to some extend the case with the solution above, where lapply()<\/tt> and Reduce()<\/tt> are used.<\/p>\n<\/div>\n The following loop just updates a matrix by adding the previous state of the matrix with the powering of the matrix.<\/p>\n This truly gives the same result. Let\u2019s increase the dimension of P<\/span><\/strong> and the number of powers applied.<\/p>\n The speed is comparable but slightly faster for the simple for<\/tt> loop, naturally the code is (much) better readable and debugging of code would be easier when using a for<\/tt> loop.<\/p>\n Note that additional gain in computational speed can be obtained using C++ within R. This can be done, e.g. using package Rcpp <\/a>. However, in this case this is not straightforward to apply and either the environment of package Package data.table <\/a> is incredibly efficient in terms of computational speed. Use it whenever dealing with large (rectangular) data sets. Read its vignettes, tutorials and package manual. Note that \u2013 not used here \u2013 also package dplyr <\/a> is fast and very user-friendly.<\/p>\n<\/div>\n<\/div>\n<\/div>\n By Matthias Templ (ZHAW), Thoralf Mildenberger (ZHAW) By way of example, functionality of Reduce() is shown in in https:\/\/blog.zhaw.ch\/datascience\/r-reduce-applys-lesser-known-brother\/ . It\u2019s great to learn about how to use this function on interesting problems. If you are ready (equals if you read the first blog post on Reduce), we want to push you further on writing […]<\/p>\n","protected":false},"author":265,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[1,7,42],"tags":[44,43],"features":[],"class_list":["post-896","post","type-post","status-publish","format-standard","hentry","category-allgemein","category-blog","category-r","tag-programming","tag-r"],"yoast_head":"\nReduce()<\/code> is shown in in https:\/\/blog.zhaw.ch\/datascience\/r-reduce-applys-lesser-known-brother\/<\/a> . It\u2019s great to learn about how to use this function on interesting problems. If you are ready (equals if you read the first blog post on Reduce<\/tt>), we want to push you further on writing efficient code.<\/p>\nReduce()<\/code> and other base R<\/code> functionality might be extremely slow compared to data manipulation functions of package dplyr <\/a> or data.table <\/a>. In addition, compared to other software and languages, solving recursive problems with R<\/code>\u2019s Reduce()<\/code> is super-slow, see e.g. recursive problems at Rosetta Code <\/a>, and this is sometimes used to argue that R<\/code> is slow itself, which is simply not true.<\/p>\nReduce<\/code> and base R<\/code> related to user-friendliness of code and computational speed. We re-discuss two examples from https:\/\/blog.zhaw.ch\/datascience\/r-reduce-applys-lesser-known-brother\/<\/a><\/p>\nExample: Merging multiple data sets<\/h1>\n
The former merge() and Reduce() solutions with base R:<\/h2>\n
df1 <- data.frame(Customer_ID = c(1, 2, 3), Name = c(\"John\", \"Sue\", \"Ann\"))\r\ndf2 <- data.frame(Customer_ID = c(1, 3), Year_First_Order = c(2011, 2017))\r\ndf3 <- data.frame(Customer_ID = c(1, 2, 3), \r\n Date_Last_Order = c(\"2017-03-03\", \"2014-03-01\", \"2017-05-30\"),\r\n No_Items_Last_Order = c(3, 1, 1),\r\n Total_Amount_Last_Order = c(49, 25,25))\r\ndf4 <- data.frame(Customer_ID = c(2, 3), Interested_In_Promo = c(TRUE, FALSE))<\/code><\/pre>\nR<\/code>:<\/p>\ndf_merge <- merge(df1, df2, by = \"Customer_ID\", all.x = TRUE, all.y = FALSE)\r\ndf_merge <- merge(df_merge, df3, by = \"Customer_ID\", all.x = TRUE, all.y = FALSE)\r\ndf_merge <- merge(df_merge, df4, by = \"Customer_ID\", all.x = TRUE, all.y = FALSE)\r\ndf_merge<\/code><\/pre>\n## Customer_ID Name Year_First_Order Date_Last_Order No_Items_Last_Order\r\n## 1 1 John 2011 2017-03-03 3\r\n## 2 2 Sue NA 2014-03-01 1\r\n## 3 3 Ann 2017 2017-05-30 1\r\n## Total_Amount_Last_Order Interested_In_Promo\r\n## 1 49 NA\r\n## 2 25 TRUE\r\n## 3 25 FALSE<\/code><\/pre>\nReduce()<\/code> can be applied in the following manner:<\/p>\ndf_list <- list(df1, df2, df3, df4)\r\nReduce(function(d1, d2) merge(d1, d2, by = \"Customer_ID\", all.x = TRUE, all.y = FALSE), df_list)<\/code><\/pre>\n## Customer_ID Name Year_First_Order Date_Last_Order No_Items_Last_Order\r\n## 1 1 John 2011 2017-03-03 3\r\n## 2 2 Sue NA 2014-03-01 1\r\n## 3 3 Ann 2017 2017-05-30 1\r\n## Total_Amount_Last_Order Interested_In_Promo\r\n## 1 49 NA\r\n## 2 25 TRUE\r\n## 3 25 FALSE<\/code><\/pre>\nn <- 300000\r\ndf1 <- data.frame(Customer_ID = 1:n, Name = rep(c(\"John\", \"Sue\", \"Ann\"), n\/3))\r\ndf2 <- data.frame(Customer_ID = sample(1:n, size = n*2\/3), Year_First_Order = sample(2000:2017, size = n*2\/3, replace = TRUE))\r\ndf3 <- data.frame(Customer_ID = 1:n, \r\n Date_Last_Order = rep(c(\"2017-03-03\", \"2014-03-01\", \"2017-05-30\"),n\/3),\r\n No_Items_Last_Order = sample(1:10, size = n, replace = TRUE),\r\n Total_Amount_Last_Order = sample(20:50, size = n, replace = TRUE))\r\ndf4 <- data.frame(Customer_ID = sample(1:n, size = n*2\/3), Interested_In_Promo = sample(c(TRUE, FALSE), size = n*2\/3, replace = TRUE))<\/code><\/pre>\nmicrobenchmark<\/code>.<\/p>\nm1 <- function(df1, df2, df3, df4){\r\n df_merge <- merge(df1, df2, by = \"Customer_ID\", all.x = TRUE, all.y = FALSE)\r\n df_merge <- merge(df_merge, df3, by = \"Customer_ID\", all.x = TRUE, all.y = FALSE)\r\n df_merge <- merge(df_merge, df4, by = \"Customer_ID\", all.x = TRUE, all.y = FALSE)\r\n df_merge\r\n}\r\nm2 <- function(df1, df2, df3, df4){\r\n df_list <- list(df1, df2, df3, df4)\r\n Reduce(function(d1, d2) merge(d1, d2, by = \"Customer_ID\", all.x = TRUE, \r\n all.y = FALSE), df_list)\r\n}<\/code><\/pre>\nlibrary(\"microbenchmark\")\r\nmbm <- microbenchmark(\r\n mergeBase = m1(df1, df2, df3, df4),\r\n Reduce = m2(df1, df2, df3, df4),\r\n times = 10\r\n)\r\nmbm<\/code><\/pre>\n## Unit: seconds\r\n## expr min lq mean median uq max neval cld\r\n## mergeBase 1.028722 1.120588 1.126353 1.131829 1.152066 1.170740 10 a\r\n## Reduce 1.105373 1.144046 1.168305 1.158710 1.179812 1.306395 10 a<\/code><\/pre>\ndata.table\u2019s solution to the above problem<\/h2>\n
library(\"data.table\")\r\ndt1 <- data.table(df1)\r\ndt2 <- data.table(df2)\r\ndt3 <- data.table(df3)\r\ndt4 <- data.table(df4)\r\nsetkey(dt1, Customer_ID)\r\nsetkey(dt2, Customer_ID) \r\nsetkey(dt3, Customer_ID) \r\nsetkey(dt4, Customer_ID) <\/code><\/pre>\ndata.table<\/code> code gives us exactly the same solution as with our previous base R<\/code> code.<\/p>\nDT_cmb <- dt4[dt3][dt2][dt1]<\/code><\/pre>\nm3 <- function(dt1, dt2, dt3, dt4){\r\n setkey(dt1, Customer_ID)\r\n setkey(dt2, Customer_ID) \r\n setkey(dt3, Customer_ID) \r\n setkey(dt4, Customer_ID) \r\n DT_cmb <- dt4[dt3][dt2][dt1]\r\n DT_cmb\r\n}\r\nlibrary(\"microbenchmark\")\r\nmbm <- microbenchmark(\r\n mergeBase = m1(df1, df2, df3, df4),\r\n Reduce = m2(df1, df2, df3, df4),\r\n dt = m3(dt1, dt2, dt3, dt4),\r\n times = 10\r\n)\r\nmbm<\/code><\/pre>\n## Unit: milliseconds\r\n## expr min lq mean median uq max\r\n## mergeBase 1010.03602 1030.72226 1099.73585 1047.30145 1208.32011 1286.489\r\n## Reduce 968.37101 1039.04347 1097.75323 1048.05562 1201.69135 1284.099\r\n## dt 43.24658 50.42165 66.52315 53.34681 70.50837 140.710\r\n## neval cld\r\n## 10 b\r\n## 10 b\r\n## 10 a<\/code><\/pre>\ndata.table<\/code> is more than 15 times faster! and the code is much shorter and elegant. Note that the differences becomes even (much) larger if the data size increases.<\/p>\n<\/div>\nExample: Sums of matrix powers<\/h1>\n
The lapply\/Reduce code:<\/h2>\n
library(\"expm\")\r\nn <- 2\r\nP <- matrix(runif(n*n, 0.01, 0.02), ncol = n, nrow = n)\r\np1 <- function(vec, P){\r\n P_powers <- lapply(vec, function(k) P %^% k)\r\n Reduce(\"+\", P_powers)\r\n}\r\np1(0:2, P = P)<\/code><\/pre>\n## [,1] [,2]\r\n## [1,] 1.01718756 0.01206923\r\n## [2,] 0.01409565 1.01037824<\/code><\/pre>\nR<\/code> version 3.0.0 for<\/tt> loops are approx. equally fast than using the apply<\/code> family (at least for usual problems). Multiple use of the apply<\/code> family results in hard to read code, especially when several apply statements are given in one line of code, and debugging of code is more difficult.<\/p>\nThe simple for() loop to the problem:<\/h2>\n
p2 <- function(vec, P){\r\n P2 <- P %^% vec[1]\r\n for(i in vec[-1]){\r\n P2 <- P2 + P %^% i\r\n } \r\n P2\r\n}\r\np2(0:2, P = P)<\/code><\/pre>\n## [,1] [,2]\r\n## [1,] 1.01718756 0.01206923\r\n## [2,] 0.01409565 1.01037824<\/code><\/pre>\nn <- 10\r\nP <- matrix(runif(n*n, 0.01, 0.02), ncol = n, nrow = n)\r\nmbm <- microbenchmark(\r\n Reduce = p1(seq_len(10000), P = P),\r\n Simple = p2(seq_len(10000), P = P),\r\n times = 10\r\n)\r\nmbm<\/code><\/pre>\n## Unit: milliseconds\r\n## expr min lq mean median uq max neval cld\r\n## Reduce 122.9000 124.2832 131.1854 133.8611 135.4301 136.7512 10 b\r\n## Simple 109.7845 111.0879 113.7115 112.0759 113.4696 124.5798 10 a<\/code><\/pre>\nexpm<\/code> must be imported within a Rcpp<\/code> function or a powering of a matrix method must be first implemented in C++.<\/p>\n<\/div>\nFurther reading<\/h1>\n
<\/div>","protected":false},"excerpt":{"rendered":"