CODEWITHSUNDEEP
rdata-manipulation
histelheim
histelheim

Reputation: 5088

Creating rows from an id variable in R

I have data structured in this format (longer, but still abbreviated, dataset can be found here):

pull_req_id    user         action     created_at
12359          arthurnn     opened     1380126837
12359          rafaelfranca discussed  1380127245
12359          arthurnn     discussed  1380127676
12357          JuanitoFatas opened     1380122817
12357          JuanitoFatas opened     1380122822
12357          senny        reviewed   1380171899
...

Now, I want to rearrange this dataframe so that it looks as follows:

12359, opened, discussed, discussed
12357, opened, opened, reviewed

Meaning that all rows that have the same "pull_req_id" should be on a single row, and that row should essentially be a vector, starting with "pull_req_id", followed by the strings in "action", ordered by the integers in "created_at" (which are seconds since the epoch, earlier should be first (to the left), and later should follow (to the right).

How can I accomplish this in R?

Upvotes: 0

Views: 72

Answers (3)

Sven Hohenstein
Sven Hohenstein

Reputation: 81683

# read data
dat <- read.csv("http://pastebin.com/raw.php?i=VqgaLWqZ")

# create vector with 'action' values
vec <- with(dat, tapply(action, pull_req_id, FUN = paste, collapse = ", "))

# add 'pull_req_id' values
vec2 <-  paste(names(vec), vec, sep = ", ")

# create one-column data frame
dat2 <- data.frame(vec2)

head(dat2, 3)    
#                                          vec2
# 1                            12146, discussed
# 2 12147, opened, discussed, closed, discussed
# 3               12148, merged, opened, closed

You can also write the data to a csv file:

write.table(dat2, "filename.csv", 
            row.names = FALSE, col.names = FALSE, quote = FALSE)

The first four lines of the resulting file:

12146, discussed
12147, opened, discussed, closed, discussed
12148, merged, opened, closed
12149, discussed, referenced, referenced, closed

Upvotes: 4

histelheim
histelheim

Reputation: 5088

Here's an attempt that accomplishes something similar:

dat <- read.csv("http://pastebin.com/raw.php?i=VqgaLWqZ")
data.split <- split(data$action, data$pull_req_id)
data.split

list.to.df <- function(arg.list) {
  max.len  <- max(sapply(arg.list, length))
  arg.list <- lapply(arg.list, `length<-`, max.len)
  as.data.frame(arg.list)
}

df.out <- list.to.df(data.split)

Upvotes: 0

jlhoward
jlhoward

Reputation: 59345

Similar to @SvenHohenstein, but seems simpler.

df <-read.csv(header=T, file="http://pastebin.com/download.php?i=VqgaLWqZ",stringsAsFactors=F)

df <- df[order(df$pull_req_id,df$created_at),]  # correct order of actions
z <- aggregate(action~pull_req_id,df,function(x){paste(x,collapse=",")})
write.csv(z,"outfile.csv", quote=F)
head(z,4)
#   pull_req_id                                 action
# 1       12146                              discussed
# 2       12147      opened,discussed,discussed,closed
# 3       12148                   opened,merged,closed
# 4       12149 referenced,closed,referenced,discussed

The spaces are not really there: it's just the way R lines up the columns. Look at the csv file.

Upvotes: 3

Related Questions