How to assign number of repeats to dataframe based

问题:

I have a dataframe with individuals assigned a text id that concatenates a place-name with a personal id (see data, below). Ultimately, I need to do a transformation of the data set from "long" to "wide" (e.g., using "reshape") so that each individual comprises one row, only. In order to do that, I need to assign a "time" variable that reshape can use to identify time-varying covariates, etc. I have (probably bad) code to do this for individuals that repeat up to two times, but need to be able to identify up to 18 repeated occurrences. The code below works fine if I remove the line preceded by the hash, but only identifies up to two repeats. If I leave that line in (which would seem necessary for individuals repeated more than twice), R chokes, giving the following error (presumably because the first individual is repeated only twice):

Error in if (data$uid[i] == data$uid[i - 2]) { : 
  argument is of length zero

Can anyone help with this? Thanks in advance!

place <- rep("ny",10)
pid <- c(1,1,2,2,2,3,4,4,5,5)
uid<- paste(place,pid,sep="")
time <- rep(0,10)
data <- cbind(uid,time)
data <- as.data.frame(data)
data$time <- as.numeric(data$time)

#bad code
data$time[1] <- 1 #need to set first so that loop doesn't go to a row that doesn't exist     (i.e., row 0)
for (i in 2:NROW(data)){
    data$time[i] <- 1 #set first occurrence to 1
    if (data$uid[i] == data$uid[i-1]) {data$time[i] <- 2} #set second occurrence to 2, etc.
    #if (data$uid[i] == data$uid[i-2]) {data$time[i] <- 3}
    i <- i+1
}

回答1:

It's unclear what you are trying to do, but I think you're saying that you need to create a time index for each row by every unique uid. Is that right?

If so, give this a whirl

library(plyr)
ddply(data, "uid", transform, time = seq_along(uid))

Will give you something like:

   uid time
1  ny1    1
2  ny1    2
3  ny2    1
4  ny2    2
5  ny2    3
....

回答2:

Is this what you have in mind?

> d <- data.frame(uid = paste("ny",c(1,2,1,2,2,3,4,4,5,5),sep=""))
> out <- do.call(rbind, lapply(split(d, d$uid), function(x) {x$time <- 1:nrow(x); x}))
> rownames(out) <- NULL
> out
   uid time
1  ny1    1
2  ny1    2
3  ny2    1
4  ny2    2
5  ny2    3
6  ny3    1
7  ny4    1
8  ny4    2
9  ny5    1
10 ny5    2

回答3:

Using your data frame setup:

place <- rep("ny",10)
pid <- c(1,1,2,2,2,3,4,4,5,5)
uid<- paste(place,pid,sep="")
time <- rep(0,10)
data <- cbind(uid,time)
data <- as.data.frame(data)

You can use:

data$time <- sequence(table(data$uid))
data

To get:

> data
   uid time
1  ny1    1
2  ny1    2
3  ny2    1
4  ny2    2
5  ny2    3
6  ny3    1
7  ny4    1
8  ny4    2
9  ny5    1
10 ny5    2

NOTE: Your data.frame MUST be sorted by uid first for this to work.

回答4:

After trying the above solutions on large data sets, I decided to write my own loop for this. It was very time-consuming and still required the data to be broken into 50k-element vectors, but it did work in the end:

system.time( for(i in 2:length(data$uid)) {
if(data$uid[i]==data$uid[i-1]) data$repeats[i] <- data$repeats[i-1]+1
  if ((i %% 1000)== 0) { #helps to keep track of how far the loop has gotten
    print(i) }
    i+1
}
)

Thanks to all for your help.