R: Replace multiple values in multiple columns of dataframes with values in another column

Question

I am trying to achieve something similar to this and this question but instead of replacing with a single value I would like to replace based on another column.

The data is from particular sites that have 'yes' when measurements were taken on a specific date and I now would like the water level of the river on those particular dates. My data looks something like this (but with many more sites and dates):

date <- c('2000-01-01','2000-01-02','2000-01-03','2000-01-04','2000-01-05','2000-01-06','2000-01-07','2000-01-08','2000-01-09','2000-01-10')
date <- as.Date(date)
Site1 <- c('yes','','yes','','yes','','yes','','','')
Site2 <- c('yes','yes','yes','','','','','yes','','yes')
Site3 <- c('','','','','','','yes','yes','','yes')
waterlevel <- c(24,58,2,38,18,59,20,98,16,88)
df <- data.frame(date,Site1,Site2,Site3,waterlevel)

which gives this dataframe:

         date Site1 Site2 Site3 waterlevel
1  2000-01-01   yes   yes               24
2  2000-01-02         yes               58
3  2000-01-03   yes   yes                2
4  2000-01-04                           38
5  2000-01-05   yes                     18
6  2000-01-06                           59
7  2000-01-07   yes         yes         20
8  2000-01-08         yes   yes         98
9  2000-01-09                           16
10 2000-01-10         yes   yes         88

and I would like the output dataframe to look like this:

         date Site1 Site2 Site3 waterlevel
1  2000-01-01   24   24              24
2  2000-01-02        58              58
3  2000-01-03    2    2               2
4  2000-01-04                        38
5  2000-01-05   18                   18
6  2000-01-06                        59
7  2000-01-07   20         20        20
8  2000-01-08         98   98        98
9  2000-01-09                        16
10 2000-01-10         88   88        88

I have tried using the solution code to the above questions with a match argument with the water level in a different dataframe but the solutions did not work:

sel <- grepl("Site",names(df))
df[sel] <- lapply(df[sel], function(x) replace(x,x %in% 2:4, df2$waterlevel[match(df$date,df2$date)]) )

Any help would be much appreciated.

score 2 · Answer 1 · answered Oct 06 '15 at 12:52

df[sel] <- lapply(df[sel], 
                  function(x, value) ifelse(x == "yes", value, NA), 
                  value = df$waterlevel)

#         date Site1 Site2 Site3 waterlevel
#1  2000-01-01    24    24    NA         24
#2  2000-01-02    NA    58    NA         58
#3  2000-01-03     2     2    NA          2
#4  2000-01-04    NA    NA    NA         38
#5  2000-01-05    18    NA    NA         18
#6  2000-01-06    NA    NA    NA         59
#7  2000-01-07    20    NA    20         20
#8  2000-01-08    NA    98    98         98
#9  2000-01-09    NA    NA    NA         16
#10 2000-01-10    NA    88    88         88

@PierreLafortune Why would you want to turn these columns into class `character`? — Roland, Oct 06 '15 at 12:55

score 2 · Accepted Answer · answered Oct 06 '15 at 12:54

Using data.table package. Easier here to process in the long format and coma back again to wide format ( original one).

dcast(date+waterlevel~variable,   ## put again in the wide format
       data=melt(setDT(df),id=c("date","waterlevel"))[ ## long format
       value=="yes",value:=waterlevel]) ## filter and process one variable

         date waterlevel Site1 Site2 Site3
 1: 2000-01-01         24    24    24      
 2: 2000-01-02         58          58      
 3: 2000-01-03          2     2     2      
 4: 2000-01-04         38                  
 5: 2000-01-05         18    18            
 6: 2000-01-06         59                  
 7: 2000-01-07         20    20          20
 8: 2000-01-08         98          98    98
 9: 2000-01-09         16                  
10: 2000-01-10         88          88    88

score 0 · Answer 3 · answered Feb 06 '19 at 21:19

Using dplyr you could do something like this:

library("dplyr")
df <- df %>% 
  mutate_at(vars(starts_with("Site")),
            function(x) ifelse(x == "yes", .$waterlevel, x))

df
#          date Site1 Site2 Site3 waterlevel
# 1  2000-01-01    24    24               24
# 2  2000-01-02          58               58
# 3  2000-01-03     2     2                2
# 4  2000-01-04                           38
# 5  2000-01-05    18                     18
# 6  2000-01-06                           59
# 7  2000-01-07    20          20         20
# 8  2000-01-08          98    98         98
# 9  2000-01-09                           16
# 10 2000-01-10          88    88         88

R: Replace multiple values in multiple columns of dataframes with values in another column

3 Answers3