Geek asks and answers

Rearrange data in R

I have several CSV files, for example:

site,run,id,payload,dir
1,1,1,528,1
1,1,1,540,2
1,1,3,532,1
# ... thousands more rows ...

(In the actual case I'm working with, there are three files with a total of 1,408,378 lines.) To plot the graph, I want to shuffle them into this format:

label,stream,dir,i,payload
A,1,1,1,586
A,1,1,2,586
A,1,1,3,586
# etc

where the "label" is derived from the CSV file name; “stream” is the serial number assigned to each combination “site”, “start” and “identifier” in one file (therefore it is unique only inside the “label”); "i" - line number in each "stream"; and 'dir' and 'payload' are taken directly from the source file. I also want to drop everything except the first 20 lines of each thread. I know in advance that every cell in the CSV file (except the header) is a positive integer, and that 'dir' only ever takes values ​​1 and 2.

plyr, R 6 . foreach parallelism plyr : 10 , , , , .

, script Python, :

import sys
def processOne(fname):
    clusters = {}
    nextCluster = 1
    with open(fname + ".csv", "r") as f:
        for line in f:
            line = line.strip()
            if line == "site,run,id,payload,dir": continue
            (site, run, id, payload, dir) = line.split(',')
            clind = ",".join((site,run,id))

            clust = clusters.setdefault(clind,
                                        { "i":nextCluster, "1":0, "2":0 })
            if clust["i"] == nextCluster:
                nextCluster += 1

            clust[dir] += 1
            if clust[dir] > 20: continue

            sys.stdout.write("{label},{i},{dir},{j},{payload}\n"
                             .format(label=fname,
                                     i=clust["i"],
                                     dir=dir,
                                     j=clust[dir],
                                     payload=payload))

sys.stdout.write("label,stream,dir,i,payload\n")
for fn in sys.argv[1:]: processOne(fn)

R script:

all <- read.csv(pipe("python preprocess.py A B C", open="r"))

.

, : R? , . , , - , . , R ggplot2, , , , matplotlib.

+3
2

R :

- "where" " CSV;"

filvec <- list.files(<path>)
for (fil in filvec) {  #all the statements will be in the loop body
  dat <- read.csv(fil)
  dat$label <- fil   # recycling will make all the elements the same character value

- "" - , "", "" "" ( "" );

 dat$stream <- as.numeric( with(dat, interaction(site, run, id) ) )

- "i" - "";

dat$i <- ave(dat$site,     # could be any column since we are not using its values
             dat$stream,   # 'ave' passes grouped vectors, returns same length vector
             FUN= function(x) 1:length(x) )

- " 'dir' 'payload' .

 # you can refer to them by name or column number

- " , 20 ".

 out <- dat[dat$i <= 20,     # logical test for the "first 20"
             c('label','stream','dir','i','payload') ]  # chooses columns desired
     }  # end of loop

"dat". ( .) :

  assign(paste(fil, "out", sep="_"), dat[dat$i <= 20,
                                          c('label','stream','dir','i','payload') ] )
+8

data.table .

, 500 000- data.frames , ~ 2 .

library(data.table)

## Create a list of three 500000 row data.frames
df <- expand.grid(site=1:2, run=1:2, id=1:2)
df <- data.frame(df, payload=1:1000, dir=rep(1, 5e5))
dfList <- list(df, df, df)
dfNames <- c("firstCSV", "secondCSV", "thirdCSV")

## Manipulate the data with data.table, and time the calculations
system.time({
outputList <-
    lapply(1:3, FUN = function(ii) {
        label <- dfNames[ii]
        df <- dfList[[ii]]
        dt <- data.table(df, key=c("site", "run", "id"))
        groups <- unique(dt[,key(dt), with=FALSE])
        groups[, stream := seq_len(nrow(groups))]
        dt <- dt[groups]
        # Note: The following line only keeps the first 3 (rather than 20) rows
        dt <- dt[, head(cbind(.SD, i=seq_len(.N)), 3), by=stream]
        dt <- cbind(label, dt[,c("stream", "dir", "i", "payload")])
        df <- as.data.frame(dt)
        return(df)
    })
output <- do.call(rbind, outputList)
})
##    user  system elapsed 
##    1.25    0.18    1.44 

## Have a look at the output
rbind(head(output,4), tail(output,4))

: 8/8/2012 ~ 25%, :

dt <- dt[, head(cbind(.SD, i=seq_len(.N)), 3), by=stream]

:

dt <- cbind(dt, i = dt[, list(i=seq_len(.N)), by=stream][[2]])
dt <- dt[i<=3,]  # Note: This only keeps the 1st 3 (rather than 20) rows
+6

More articles:


All Articles