How to copy in a column of a dataframe the value of the previous row only 1 time?

user_12919571 0
Viewed 94

Everything is in the question. To illustrate, I have the following example:

date <- c("01.02.2011","02.02.2011","03.02.2011","04.02.2011","05.02.2011","01.02.2011","02.02.2011","03.02.2011","04.02.2011","05.02.2011")
date <- as.Date(date, format="%d.%m.%Y")
ID <- c("A","A","A","A","A","B","B","B","B","B")
indicator <- c(NA,NA,NA,"2.025",NA,NA,"6.777",NA,NA,NA)

df <- data.frame(date, ID, indicator)

So I have one dataframe looking like this:

         date ID indicator
1  2011-02-01  A   <NA>
2  2011-02-02  A   <NA>
3  2011-02-03  A   <NA>
4  2011-02-04  A  2.025
5  2011-02-05  A   <NA>
6  2011-02-01  B   <NA>
7  2011-02-02  B  6.777
8  2011-02-03  B   <NA>
9  2011-02-04  B   <NA>
10 2011-02-05  B   <NA>

I would like to copy the values (so not NA) in the column indicator 1 time in the next row in order to obtain:

         date ID indicator
1  2011-02-01  A   <NA>
2  2011-02-02  A   <NA>
3  2011-02-03  A   <NA>
4  2011-02-04  A  2.025
5  2011-02-05  A  2.025
6  2011-02-01  B   <NA>
7  2011-02-02  B  6.777
8  2011-02-03  B  6.777
9  2011-02-04  B   <NA>
10 2011-02-05  B   <NA>

I tried with:

df[which(df$indicator != NA)+1, "indicator"] <-

but I don't manage to find what I should put after "<-" to retrieve the corresponding values? My dataframe has in fact around 20000 rows so I cannot do it manually.

If any editing is needed, do not hesitate to let me know. Thanks in advance for your help

row dataframe copy r replace
2 Answers

Here is a tidyverse approach. If the previous row indicator is not NA, then copy it. Otherwise, no change.

Note I mutate the indicator column to character as it comes out a factor from the example dataframe provided.

library(tidyverse)

df %>%
  group_by(ID) %>%
  mutate(indicator = as.character(indicator)) %>%
  mutate(indicator = ifelse(!is.na(lag(indicator)), lag(indicator), indicator))

Output

# A tibble: 10 x 3
# Groups:   ID [2]
   date       ID    indicator
   <date>     <fct> <chr>    
 1 2011-02-01 A     NA       
 2 2011-02-02 A     NA       
 3 2011-02-03 A     NA       
 4 2011-02-04 A     2.025    
 5 2011-02-05 A     2.025    
 6 2011-02-01 B     NA       
 7 2011-02-02 B     6.777    
 8 2011-02-03 B     6.777    
 9 2011-02-04 B     NA       
10 2011-02-05 B     NA
user_34606700

I found the following solution but it not might be the most optimal one

df.1 <- df[complete.cases(df),]
df.2 <- as.double(df.1[,3])

df[which(df$indicator !="NA")+1,"indicator"] <- df.2
user_129195710
Related