R: subset and ordering big data without the forloop framework
I have a long table with 97M rows. Each line contains information about the action taken by the person and the timestamp for that action in the form:
actions <- c("walk","sleep", "run","eat")
people <- c("John","Paul","Ringo","George")
timespan <- seq(1000,2000,1)
set.seed(28100)
df.in <- data.frame(who = sample(people, 10, replace=TRUE),
what = sample(actions, 10, replace=TRUE),
when = sample(timespan, 10, replace=TRUE))
df.in
# who what when
# 1 Paul eat 1834
# 2 Paul sleep 1295
# 3 Paul eat 1312
# 4 Ringo eat 1635
# 5 John sleep 1424
# 6 George run 1092
# 7 Paul walk 1849
# 8 John run 1854
# 9 George sleep 1036
# 10 Ringo walk 1823
Each action can be taken or not taken by a person, and actions can be taken in any order.
I'm interested in summarizing the sequence of actions in my dataset. Specifically, for each person, I want to find which action was taken first, second, third and fourth. In case the action is performed several times, I am only interested in the first occurrence . Then, if someone runs, eats, eats, sleeps, and runs, I'm interested in the summation, for example run
, eat
, sleep
.
df.out <- data.frame(who = factor(character(), levels=people),
action1 = factor(character(), levels=actions),
action2 = factor(character(), levels=actions),
action3 = factor(character(), levels=actions),
action4 = factor(character(), levels=actions))
I can get what I want with forloop:
for (person in people) {
tmp <- subset(df.in, who==person)
tmp <- tmp[order(tmp$when),]
chrono_list <- unique(tmp$what)
df.out <- rbind(df.out, data.frame(who = person,
action1 = chrono_list[1],
action2 = chrono_list[2],
action3 = chrono_list[3],
action4 = chrono_list[4]))
}
df.out
# who action1 action2 action3 action4
# 1 John sleep run <NA> <NA>
# 2 Paul sleep eat walk <NA>
# 3 Ringo eat walk <NA> <NA>
# 4 George sleep run <NA> <NA>
Is it possible to achieve this result without a loop in a more efficient way?
source to share
We could use dcast
from the devel version data.table
i.e. v1.9.5
... We can install it fromhere
library(data.table)#v1.9.5+
dcast(setDT(df.in)[order(when),action:= paste0('action', 1:.N) ,who],
who~action, value.var='what')
If you need a unique
"what" for every "who"
dcast(setDT(df.in)[, .SD[!duplicated(what)], who][order(when),
action:= paste0('action', 1:.N), who], who~action, value.var='what')
# who action1 action2 action3
#1: George sleep run NA
#2: John sleep run NA
#3: Paul sleep eat walk
#4: Ringo eat walk NA
Or use .I
will be slightly faster
ind <- setDT(df.in)[,.I[!duplicated(what)], who]$V1
dcast(df.in[ind][order(when),action:= paste0('action', 1:.N) ,who],
who~action, value.var='what')
Or using setorder
and unique
, which can be memory efficient since setorder
reordering the dataset by reference.
dcast(unique(setorder(setDT(df.in), who, when), by=c('who', 'what'))[,
action:= paste0('action', 1:.N), who], who~action, value.var='what')
# who action1 action2 action3
#1: George sleep run NA
#2: John sleep run NA
#3: Paul sleep eat walk
#4: Ringo eat walk NA
source to share
You can also use combo dplyr
+tidyr
library(dplyr)
library(tidyr)
df.in %>%
group_by(who) %>%
mutate(when = rank(when), when = paste0("action", when)) %>%
spread(key = when, value = what)
## who action1 action2 action3 action4
## 1 George sleep run NA NA
## 2 John sleep run NA NA
## 3 Paul sleep eat eat walk
## 4 Ringo eat walk NA NA
EDIT
If you only want the first occurrence of the columns what
, you can simply filter the data first
df.in %>%
arrange(when) %>%
group_by(who) %>%
filter(!duplicated(what)) %>%
mutate(when = rank(when), when = paste0("action", when)) %>%
spread(key = when, value = what)
## who action1 action2 action3
## 1 George sleep run NA
## 2 John sleep run NA
## 3 Paul sleep eat walk
## 4 Ringo eat walk NA
source to share
Here's a more traditional method split-apply-combine
. This is more idiomatic R code than a loop for
, although the {dplyr} and {data.table} solutions seem to be more common than this {base} R solution. This method uses dcast
from {reshape2}, but it can also use reshape()
{ base} R.
This method is probably not much faster than the loop for
asked in the question. I would be interested to know how the three methods given compare for a large dataset. I am a newbie and recently working on learning R data processing. Any feedback is appreciated.
library(reshape2)
#Split the data by person and apply the function
actions <- lapply(split(df.in, df.in$who), function(tmp) {
tmp <- tmp[order(tmp$when),]
dup <- duplicated(tmp$what)
df.out <- data.frame(who = tmp$who[!dup], what = tmp$what[!dup])
df.out$actionNo <- paste("action", c(1:nrow(df.out)), sep = "")
return(df.out)
})
#Combine the results
act_rbind <- do.call(rbind, actions)
act_cast <- dcast(act_rbind, who ~ actionNo, value.var = "what")
print(act_cast)
# who action1 action2 action3
# 1 George sleep run <NA>
# 2 John sleep run <NA>
# 3 Paul sleep eat walk
# 4 Ringo eat walk <NA>
source to share