extracting relevant calibration parameters for recalculation of values in multiple time series using a common ID in another file in R

Question

This is my first post so please excuse any errors in how to ask questions here. I'm pretty new to R and largely self taught so far.

I have around 100 time series where the values require correcting of the observed value with a calibration dataset. Each time series has a unique numeric ID that should match a numeric ID in the calibration dataset.

i.e. an example of a time series (i.e. ID of the time series in line 1, data in lines 2 onwards)

ID 1

Date Time Value

01/01/1900 00:00:30 340

01/01/1900 00:01:00 344

and an example of the calibration data:

ID intercept slope

1 -3 0.5

2 -3.1 0.52

3 -3.-5 0.45

I would like to add a new column to each time series where I recalculate the "Value" to a "CorrectedValue" using the intercept and slope info in the correct line of the calibration dataset. There has been some swapping around of equipment and so I would like to automate this to avoid errors in calculating the correct values across the 100 time series.

I tried the below but the code fails to retrieve only the correct values at the last line, so I am stuck. (I know I haven't converted the datetime values yet, I am hoping to do that in subsequent steps as I need to merge several datablocks once the values have been correctly recalculated.

The code runs, but the output is a file that is a shorter version of the calibration file, starting at the correct line for that matching time series but still retaining all subsequent lines set to NA. How do I remove the other, now defunct lines? Is there an easier solution?

Thanks for all your help.

Load necessary libraries

library(dplyr) library(lubridate)

Set the working directory to "D:\test_align"

setwd("D:/test_align")

Load the calibration data with proper data types

calibration_data <- read.csv("calibration.csv", header = TRUE, colClasses = c("numeric", "numeric", "numeric"))

Read the entire 'block1.csv' time series file as a text file

block1_text <- readLines("block1.csv")

Extract the value at [1, 2]

block1_logger <- as.numeric(strsplit(block1_text[1], ",")[[1]][2])

Load 'block1.csv', skipping row 1 with equipment number, and with correct data types

block1 <- read.csv("block1.csv", skip = 1, header = TRUE, colClasses = c("character","character","numeric"))

####not running correctly######

Pull out and store matching row in calibration_data to matching equipment number

matching_rows <- calibration_data[as.numeric(calibration_data$Column1) == block1_logger, ]