CODEWITHSUNDEEP
rdataframeconcatenationrows
EGazzoli
EGazzoli

Reputation: 109

looping over rows and changing missing values in R

I have this dataframe:

a=as.data.frame(tribble(  
  ~cod, ~text, ~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))

and I need to combine some lines (1 with 2, 4 with 5 and 5). The result should be:

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

I tryed:

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,]  #drop merged lines

but it only works with two consective lines

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

Any ideas?

Upvotes: 1

Views: 114

Answers (2)

AndS.
AndS.

Reputation: 8110

Here is another option. We fill in "cod" downward, then group by cod and summarize the data.

library(tidyverse)

a %>% 
  mutate(cod = ifelse(cod == 0, NA, cod)) %>%
  fill(cod) %>%
  group_by(cod) %>%
  summarise(text = paste(text, collapse = " "), x2007 = sum(x2007), x2008 = sum(x2008))

#> # A tibble: 3 x 4
#>   cod   text     x2007 x2008
#>   <chr> <chr>    <dbl> <dbl>
#> 1 a     aa --       12    13
#> 2 b     bb           1     2
#> 3 c     cc dd ee     7     8

Upvotes: 1

DanY
DanY

Reputation: 6073

You need a two-step process.

Step 1: replace the 0s in cod with the correct value by filling-down the column. The zoo package has a nice function that will do this, but to use it we need to first replace the 0s with NAs in cod.

a[a$cod == 0, "cod"] <- NA
a$cod <- zoo::na.locf(a$cod)

Step 2: combine the values in x2007 and x2008 for the rows that have the same value for cod. I don't know how you want to combine them, but I'll use sum in my example code.

result <- aggregate(a[ , c("x2007", "x2008")], by=a["cod"], FUN=sum)

Then we can print the result

> result
  cod x2007 x2008
1   a    12    13
2   b     1     2
3   c     7     8

Upvotes: 1

Related Questions