dplyr - Creating a loop to fill an empty dataframe in R

Question

I have a dataframe called all.cols2 where I am attempting to fill empty columns. The column headers are the names of my sites and I am trying to calculate water depth over time at each site. Here is what it looks like:

       Date     Time  cor_water_depth  Levee.slope   Levee.slope.1  

1 2015-12-01 15:05:33           0.088        <NA>          <NA>          
2 2015-12-01 15:25:33           0.079        <NA>          <NA>          
3 2015-12-01 15:45:33           0.080        <NA>          <NA>          
4 2015-12-01 16:05:33           0.076        <NA>          <NA>          
5 2015-12-01 16:25:33           0.080        <NA>          <NA>          
6 2015-12-01 16:45:33           0.075        <NA>          <NA>          
7 2015-12-01 17:05:33           0.070        <NA>          <NA>
8 2015-12-01 17:25:33           0.074        <NA>          <NA>
9 2015-12-01 17:45:33           0.083        <NA>          <NA>
10 2015-12-01 18:05:33           0.105        <NA>          <NA>
11 2015-12-01 18:25:33           0.146        <NA>          <NA>
12 2015-12-01 18:45:33           0.179        <NA>          <NA>

I have another data frame called survey that has relative elevations that I am using to calculate water depths at each of my locations. Here it is:

# A tibble: 6 x 3

 Description      elev_above_sealevel rel_well_elevation
  <chr>                          <dbl>              <dbl>
1 Levee.slope                    1.78              0.909 
2 Levee.slope.1                  1.49              0.627 
3 Levee.slope.2                  1.28              0.413 
4 Levee.slope.3                  1.05              0.187 
5 Levee.slope.4                  0.913             0.0459
6 Hummock.Collar.3               0.956             0.0890

I need to subtract the rel_well_elevation for each site from the cor_water_depth for the whole period of record (which is 20,000+ rows). I've been doing this one site at a time. The code I've been using looks like this:

survey[1,] #obtain needed relative well elevation value
all.cols2 <- mutate(all.cols2, Levee.slope = cor_water_depth - 0.909) #calculate new column values
survey[2,]
all.cols2 <- mutate(all.cols2, Levee.slope.1 = cor_water_depth - 0.627)
survey[3,]
all.cols2 <- mutate(all.cols2, Levee.slope.2 = cor_water_depth - 0.413)
survey[4,]
all.cols2 <- mutate(all.cols2, Levee.slope.3 = cor_water_depth - 0.187)

and I repeat the above for each empty column in all.cols2. It populates the columns like this:

 Date           Time cor_water_depth Levee.slope Levee.slope.1 
1 2015-12-01 15:05:33           0.088      -0.821        -0.539        
2 2015-12-01 15:25:33           0.079      -0.830        -0.548        
3 2015-12-01 15:45:33           0.080      -0.829        -0.547        
4 2015-12-01 16:05:33           0.076      -0.833        -0.551        
5 2015-12-01 16:25:33           0.080      -0.829        -0.547        
6 2015-12-01 16:45:33           0.075      -0.834        -0.552        
7 2015-12-01 17:05:33           0.070      -0.839        -0.557
8 2015-12-01 17:25:33           0.074      -0.835        -0.553
9 2015-12-01 17:45:33           0.083      -0.826        -0.544
10 2015-12-01 18:05:33           0.105      -0.804        -0.522
11 2015-12-01 18:25:33           0.146      -0.763        -0.481
12 2015-12-01 18:45:33           0.179      -0.730        -0.448

My actual dataset has 90+ columns representing 90+ locations, so I am wondering if there is a faster way to do this?

EDIT: this function can calculate the water depths at each location:

depth <- function(x){
      wl <- all.cols2$cor_water_depth
      elev <- survey$rel_well_elevation
      wl - elev[x]
}

Is there a way to put this in a loop to fill my columns in all.cols2?

question from:https://stackoverflow.com/questions/65835508/creating-a-loop-to-fill-an-empty-dataframe-in-r

Answer 1

Bind single-value columns to your data.frame and mutate across them

Like this:

(I can't guarantee it will work because your problem is not truly reproducible)

## Extract rel_well... vector from survey
rel_well_elevation <- survey["rel_well_elevation"]

## Use 'description' as vector names
names(rel_well_elevation) <- survey["Description"]

## Turn the vector into a named list
rel_well_elevation <- as.list(rel_well_elevation)

## Bind your list to the data frame
## A data frame is just a list of columns,
## and the new values will be recycled to match
## the lenght of the other vectors

all.cols2 <- 
  cbind(all.cols2, rel_well_elevation)

## Now we use dplyr to mutate across every column

require("dplyr")

all.cols2 <-
  allcos2 %>% 
  ## Mutate across every new column
  mutate(across(all_of(names(rel_well_elevation)),
                function(x){
                  # subtract the new col from cor_water_depth
                  cor_water_depth - x
                }))