Replace leading 0's with number immediately after it within column of a dataframe

Question

I have a dataframe with a column like this:

He is an example of the dataframe (the real one has multiple columns however for this I will just post the important column):

structure(list(frame = c(0L, 0L, 0L, 0L, 473047L, 0L, 473049L,0L, 0L, 473051L, 0L, 0L, 473052L, 0L, 473055L)), row.names = c(NA, -15L), class = "data.frame")

The frame column denotes the frame number. The 0s in the column belong to the same frame as the large number that follows. Hence I want to change the first four 0s to be 473047, then the next singular 0 to be 473049, then the following two 0s to be 473051, and so on for my analysis.

Does anybody have an tips on how to do this in base R or using the tidyverse? I thought maybe a for loop/if statement combo could work through some kind of indexing procedure i.e.

for the length of the frame column
if frame > 0
count back up the column and replace the preceding 0s with that number

However I cannot think of a possible solution beyond this pseudo-code. Any help would be most appreciated!

Likely duplicate: [Replace NA with previous or next value, by group, using dplyr](https://stackoverflow.com/questions/40040834/replace-na-with-previous-or-next-value-by-group-using-dplyr) (Replace 0 with NA first) — Ian Campbell, Jun 01 '22 at 16:42

score 3 · Answer 1 · answered Jun 01 '22 at 17:05

You can try this, first replacing the zeros with NAs

library(dplyr)
library(tidyr)

dat %>%
  mutate(frame = ifelse(frame == 0, NA, frame)) %>%
  fill(frame, .direction = "up")
    frame
1  473047
2  473047
3  473047
4  473047
5  473047
6  473049
7  473049
8  473051
9  473051
10 473051
11 473052
12 473052
13 473052
14 473055
15 473055

score 2 · Answer 2 · answered Jun 01 '22 at 17:01

First replace the zeros with NA. After that you can use na.locf from zoo:

library(dplyr)
library(zoo)
df %>%
  na_if(0) %>%
  na.locf(., fromLast = TRUE)

Output:

score 1 · Answer 3 · answered Jun 01 '22 at 17:19

We could use na.locf like @Quinten did. But here with base R:

library(zoo)
df$frame<-na.locf(with(df,ifelse(frame==0,NA_real_,frame)),fromLast=TRUE)

    frame
1  473047
2  473047
3  473047
4  473047
5  473047
6  473049
7  473049
8  473051
9  473051
10 473051
11 473052
12 473052
13 473052
14 473055
15 473055

score 0 · Answer 4 · answered Jun 01 '22 at 17:03

check this if it works #this is using base R

    data <-structure(list(frame = c(0L, 0L, 0L, 0L, 473047L, 0L, 473049L,0L, 0L, 473051L, 0L, 0L, 473052L, 0L, 473055L)), row.names = c(NA, -15L), class = "data.frame")

index = 1
for(i in 1:length(data$frame)){
  
   if(data$frame[i] > 0){
     data$frame[index:i] <- data$frame[i]
     index = i
   }
  
}
print(data$frame)

Replace leading 0's with number immediately after it within column of a dataframe

4 Answers4