How do I avoid a slow loop with large data set?

Question

Consider this data set:

> DATA <- data.frame(Agreement_number = c(1,1,1,1,2,2,2,2),
+                    country = c("Canada","Canada", "USA", "USA", "Canada","Canada", "USA", "USA"), 
+                    action = c("signature", "ratification","signature", "ratification", "signature", "ratification","signature", "ratification"), 
+                    signature_date = c(2000,NA,2000,NA, 2001, NA, 2002, NA),
+                    ratification_date = c(NA, 2001, NA, 2002, NA, 2001, NA, 2002))
> DATA
Agreement_number country       action signature_date ratification_date
              1  Canada    signature           2000                NA
             1  Canada ratification             NA              2001
             1     USA    signature           2000                NA
             1     USA ratification             NA              2002
             2  Canada    signature           2001                NA
             2  Canada ratification             NA              2001
             2     USA    signature           2002                NA
             2     USA ratification             NA              2002

As you can see, half of the rows have duplicate information. For a small data set like this it is really easy to remove duplicates. I could use the coalesce function (dplyr package), get rid of the "action" column and then erase all the irrelevant rows. Though, there many other ways. The final result should look like this:

> DATA <- data.frame( Agreement_number = c(1,1,2,2),
+                     country = c("Canada", "USA", "Canada","USA"), 
+                     signature_date = c(2000,2000,2001,2002),
+                     ratification_date = c(2001, 2002, 2001, 2002))
> DATA
Agreement_number country signature_date ratification_date
             1  Canada           2000              2001
             1     USA           2000              2002
             2  Canada           2001              2001
             2     USA           2002              2002

The problem, is that my real data set is MUCH bigger (102000 x 270) and there are many more variables. The real data is also more irregular and there are more absent values. The coalesce function seems very slow. The best loop I could make so far still takes up to 5-10 minutes to run.

Is there a simple way of doing this which would be faster? I have the feeling that there must be some function in R for that kind of operation, but I couldn't find any.

Answer 1

I think you need dcast. The version in the data.table library calls itself "fast", and in my experience, it is speedy on large datasets.

First, let's create one column which is either the signature_date or ratification_date, depending on the action

library(data.table)
setDT(DATA)[, date := ifelse(action == "ratification", ratification_date, signature_date)]

Now, let's cast it so that the action are the columns and the value is the date

wide <- dcast(DATA, Agreement_number + country ~ action, value.var = 'date')

So wide looks like this

  Agreement_number country ratification signature
1                1  Canada         2001      2000
2                1     USA         2002      2000
3                2  Canada         2001      2001
4                2     USA         2002      2002