CODEWITHSUNDEEP
r
stefaneke
stefaneke

Reputation: 43

Looking up values based on matching column name

I have a daily timeseries for a hydraulic pressure head from several monitoring wells in meters above sea level in a dataframe. For example:

hydraulic.head.mw = data.frame("date" = seq(as.Date("2020-01-01"), as.Date("2020-01-31."), by = "1 day"), "B51A1367" = rnorm(31, 11, 1), "B51A1368" = rnorm(31, 12, 1), "B51A1369" = rnorm(31, 10, 1))

In another dataframe I have the ground levels of those monitoring wells above sea level. For example:

ground.levels = data.frame("B51A1369" = 12, "B51A1368" = 13, "B51A1367" = 11)

Now I would like to have the hydraulic head for the entire column B51A1367 subtracted from the ground level value stored in ground.levels so I get the hydraulic head relative to the ground level. After this column is finished R moves to the next column, looks up the matching ground level, subtracts, and goes on to the next column.

Now I want R to achieve this based on matching the column names, not numerical indexing. So R starts out with the first column name from hydraulic.head.mw, looks for that same column name in ground.levels, takes the appropriate ground level value (which is in the first cell underneath the column header), and uses that number to subtract all the values in the column in hydraulic.head.mw from. Then R moves on to the next column until all columns have passed.

I would like to know how this can be achieved. Thank you.

Upvotes: 0

Views: 40

Answers (2)

Anoushiravan R
Anoushiravan R

Reputation: 21908

I hope this is what you have in mind:

library(dplyr)
library(purrr)

hydraulic.head.mw[1] %>%
  bind_cols(hydraulic.head.mw[-1] %>%
              imap_dfc(~ .x - ground.levels[[.y]]))

         date    B51A1367    B51A1368     B51A1369
1  2020-01-01 -0.46142021 -1.74871429 -2.452648793
2  2020-01-02  1.71382161  0.06047936 -2.234656944
3  2020-01-03 -0.11570839  0.12019048 -0.491225195
4  2020-01-04  0.05317141 -1.27937548 -1.744927525
5  2020-01-05  0.35258944 -0.92717032 -3.882610559
6  2020-01-06  0.57316511 -2.76092402 -4.535663871
7  2020-01-07 -1.00082886 -1.59522432 -2.890683663
8  2020-01-08 -1.35219506 -1.62313945 -1.477468554
9  2020-01-09  0.55320054 -0.89079517 -3.362480296
10 2020-01-10  0.08674063 -0.52586937  0.242346441
11 2020-01-11  1.37183428 -0.96520484 -2.793051770
12 2020-01-12 -0.63833081  0.75155974 -2.381431787
13 2020-01-13 -0.54790061 -0.34013724 -1.811665913
14 2020-01-14 -0.06519258 -1.20034125 -0.006102705
15 2020-01-15  1.21478696 -0.06604244 -1.225585795
16 2020-01-16  0.94666715 -1.87937005 -1.488483548
17 2020-01-17  0.75962640 -0.79240101 -2.351820061
18 2020-01-18 -1.79810493 -1.35113768 -1.126389832
19 2020-01-19  0.45044266 -0.57074678 -2.097019487
20 2020-01-20 -0.19429281  0.06355228 -2.525346388
21 2020-01-21  1.06640688 -0.43479347 -1.939315768
22 2020-01-22  0.62197062 -1.16977342 -1.228809026
23 2020-01-23  0.73909179 -0.29254173 -0.638173664
24 2020-01-24 -2.22778642 -0.12602574 -2.243869650
25 2020-01-25 -2.08132726  0.75559866 -2.613223931
26 2020-01-26  0.43944671 -2.19454589 -0.248829322
27 2020-01-27  1.02476483 -0.69068266 -1.910894847
28 2020-01-28 -0.80972940 -0.30956516 -2.864880165
29 2020-01-29 -0.64401921 -2.97380446 -2.831593140
30 2020-01-30  1.82055899 -1.30431603 -2.738413435
31 2020-01-31 -0.01449517  0.05918447 -3.253922936

Or in base R:

cbind(hydraulic.head.mw[1], 
      mapply(function(x, y) {
        x - ground.levels[[y]]
      }, hydraulic.head.mw[-1], names(hydraulic.head.mw)[-1]))

Upvotes: 1

Mohanasundaram
Mohanasundaram

Reputation: 2949

# Creating a new data frame for relative values
hydraulic.head.rel <- hydraulic.head.mw

# Looping by column names of ground.levels
for (i in colnames(ground.levels)) {
  hydraulic.head.rel[,i] <-  hydraulic.head.rel[,i] - ground.levels[,i]
}

Upvotes: 0

Related Questions