Filling rows with the correct data in R, joining successive rows

Question

Filling rows with the correct data in R, joining successive rows

Navigation

#1 by (4 votes)

3

I have a database with the second structure:

a=as.data.frame(tribble(  
  ~a, ~texto, ~x2007, ~x2008,  
  "a","aa",0,0,  
   0,"--",12,13,  
  "b","bb",1,2,  
  "c","cc", 0, 0,  
  0,"dd", 0,0,  
  0,"ee",7,8))

Lines beginning with zero are the continuation of lines that begin with letter, immediately preceding, and the data for the third and fourth columns are the data that is in the third and fourth column of the last line beginning with zero (the data of line 4 are at the end of line 6. In addition, the data in column 2 needs to be combined. The desired result would be:

  a texto x2007 x2008  
1 a aa --      12    13  
3 b    bb       1     2  
4 c cc dd ee    5     6

I've tried the following:

b=vector()  
for (i in 2:nrow(a)) {  
  if(a[i,1]==0) {  
    a[i-1,2]=paste(a[i-1,2],a[i,2])  
    a[i-1,3:4]=a[i,3:4]  
    b=c(b,i)  
    }  
}  
a=a[-b,]  #exclui linhas

but only works for two consecutive lines:

  a texto x2007 x2008
1 a aa --    12    13
3 b    bb     1     2
4 c cc dd     5     6

Can someone help me or have a simpler solution?

r dataframe

asked by anonymous 01.09.2018 / 22:28

1 answer

recover date from bash ubuntu Error in Conditional Operations in Language R

score 4 · Accepted Answer

One possible solution is as follows. Uses a function of the zoo package to modify the a column and after completion it returns to the zeros where they were.

zeros <- a$a == 0
is.na(a$a) <- zeros
a$a <- zoo::na.locf(a$a)

res <- lapply(split(a, a$a), function(DF){
  data.frame(a = DF$a[1],
             texto = paste(DF$texto, collapse = " "),
             x2007 = max(DF$x2007),
             x2008 = max(DF$x2008))
})
res <- do.call(rbind, res)
row.names(res) <- NULL
res
#  a    texto x2007 x2008
#1 a    aa --    12    13
#2 b       bb     1     2
#3 c cc dd ee     7     8

a$a[zeros] <- 0
a