StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTransform a data set to have one row by time-interval
primarykey
Id
14338100
data
AcceptedAnswerId
14341325
AnswerCount
1
ClosedDate
CommentCount
3
CommunityOwnedDate
CreationDate
2013-01-15T12:48:22.837
FavoriteCount
0
LastActivityDate
2015-07-09T12:38:55.183
LastEditDate
2015-07-09T12:38:55.183
LastEditorUserId
582829
OwnerUserId
1491449
ParentId
0
PostTypeId
1
Score
0
ViewCount
110
LastEditorDisplayName
text
Body
Example Here is some data for individual with <code>id = 1</code>: <pre><code>id time status -------------- 1 t status </code></pre> <code>t</code> is the time to some event, and <code>status</code> is either <code>1</code> if then event occurred or <code>0</code> if it did not occurred (in which case <code>t</code> is the duration of the study). Say that <code>t</code> lies between <code>a2</code> and <code>a3</code>. My goal is to transform my data into the following: <pre><code>id period start stop status --------------------------- 1 1 0 a1 0 1 2 a1 a2 0 1 3 a2 t status </code></pre> The total time of individual 1 is divided into three intervals where there is no event in <code>(0, a1)</code> and <code>(a1, a2)</code> Question Can you think of an efficient way to write an R-function that inputs a data set and a vector <code>a=(a1, a2, ..., aK)</code> and that outputs the transformed data set? <hr> EDIT Part 1 I have been asked a concrete example. Here is one: <pre><code> id time status -------------- 1 5 1 </code></pre> and <code>a1=1</code>, <code>a2=3</code>, <code>a3=7</code>. Part 2 I have also been asked to show my attempt. Here it is <pre><code>> data <- data.frame(id=1, time=5, status=1) > a <- c(1, 3, 7) > N <- nrow(data) > data$period <- ifelse(data$time < a[1], 1, + ifelse(data$time < a[2], 2, + ifelse(data$time < a[3], 3, 4))) > > > dataTemp1 <- data.frame(matrix(nrow=N, ncol=ncol(data))) > names(dataTemp1) <- names(data) > dataTemp2 <- data.frame(matrix(nrow=N, ncol=ncol(data))) > names(dataTemp2) <- names(data) > dataTemp3 <- data.frame(matrix(nrow=N, ncol=ncol(data))) > names(dataTemp3) <- names(data) > dataTemp4 <- data.frame(matrix(nrow=N, ncol=ncol(data))) > names(dataTemp4) <- names(data) > > for(j in 1:N) + { + if(data[j, "period"] == 1){ + data[j, "start"] <- 0 + data[j, "stop"] <- data[j, "time"] + } else if(data[j, "period"] == 2){ + dataTemp1[j, c("id", "time", "period")] <- + data[j, c("id", "time", "period")] + dataTemp1[j, "start"] <- 0 + dataTemp1[j, "stop"] <- a[1] + dataTemp1[j, "status"] <- 0 + + data[j, "start"] <- a[1] + data[j, "stop"] <- data[j, "time"] + } else if(data[j, "period"] == 3){ + dataTemp1[j, c("id", "time", "period")] <- + data[j, c("id", "time", "period")] + dataTemp1[j, "start"] <- 0 + dataTemp1[j, "stop"] <- a[1] + dataTemp1[j, "status"] <- 0 + + dataTemp2[j, c("id", "time", "period")] <- + data[j, c("id", "time", "period")] + dataTemp2[j, "start"] <- a[1] + dataTemp2[j, "stop"] <- a[2] + dataTemp2[j, "status"] <- 0 + + data[j, "start"] <- a[2] + data[j, "stop"] <- data[j, "time"] + } else if(data[j, "period"] == 4){ + dataTemp1[j, c("id", "time", "period")] <- + data[j, c("id", "time", "period")] + dataTemp1[j, "start"] <- 0 + dataTemp1[j, "stop"] <- a[1] + dataTemp1[j, "status"] <- 0 + + dataTemp2[j, c("id", "time", "period")] <- + data[j, c("id", "time", "period")] + dataTemp2[j, "start"] <- a[1] + dataTemp2[j, "stop"] <- a[2] + dataTemp2[j, "status"] <- 0 + + dataTemp3[j, c("id", "time", "period")] <- + data[j, c("id", "time", "period")] + dataTemp3[j, "start"] <- a[2] + dataTemp3[j, "stop"] <- a[3] + dataTemp3[j, "status"] <- 0 + + data[j, "start"] <- a[3] + data[j, "stop"] <- data[j, "time"] + } + } > > dataTemp1 <- dataTemp1[complete.cases(dataTemp1), ] > dataTemp2 <- dataTemp2[complete.cases(dataTemp2), ] > dataTemp3 <- dataTemp3[complete.cases(dataTemp3), ] > dataTemp4 <- dataTemp4[complete.cases(dataTemp4), ] > > data <- rbind(data, dataTemp1, dataTemp2, dataTemp3, dataTemp4) > data[, "period"] <- ifelse(data[, "start"] == 0, 1, + ifelse(data[, "start"] == a[1], 2, + ifelse(data[, "start"] == a[2], 3, + ifelse(data[, "start"] == a[3], 4, + 5)))) > data <- data[order(data$id, data$start), + c("id", "period", "start", "stop", "status")] > data id period start stop status 2 1 1 0 1 0 3 1 2 1 3 0 1 1 3 3 5 1 </code></pre>
Tags
<r><format>
Title
Transform a data set to have one row by time-interval
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USJoze
UserOwnerUserId
1. USuser7064
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.