Is there a way to pass coefficients of many simple linear regression models to corresponding columns of data and then solve for "x"?

Question

I have 4 columns of measurement (distance) data collected using a device that measures a voltage gap on separate channels.

  CH_1   CH_2  CH_3  CH_4 
-40160  -38180  -63972  -54560
-40160  -38140  -63972  -54552
-40168  -38168  -63972  -54568
-40172  -38116  -63984  -54544
-40160  -38184  -63988  -54568
-40168  -38172  -63980  -54564
-40168  -38156  -63972  -54552
-40172  -38152  -63984  -54532
-40156  -38176  -63968  -54552
-40168  -38132  -63976  -54544
-40172  -38136  -63992  -54556
-40168  -38140  -63984  -54528

In order to convert these voltages to a more interpretable physical distance, the sensors were calibrated with standards of a known distance in mm. These were read into R as a dataframe called calibrations

   x     CH_1    CH_2    CH_3    CH_4
   1mm  -49000  -42000  -54000  -49000
   2mm  -46000  -36000  -46000  -44000
   3mm  -35000  -32000  -42000  -33000
   4mm  -30000  -28000  -38000  -27000
   5mm  -26000  -22000  -29000  -20000

With those values, using the broom package, I ran several simple linear regressions and stored them.

calibrations<-melt(calibrations, id.vars = "x")

lms<-calibrations %>% group_by(variable) %>% do(tidy(lm(value~x, data=.)))

This gives the output of:

 >lms
 >variable     term        estimate std.error statistic    p.value
  1 CH_1       (Intercept)  -55800.     2298.    -24.3  0.000153  
  2 CH_1       x              6200.      693.     8.95  0.00294   
  3 CH_2       (Intercept)  -46400.      766.    -60.6  0.00000991
  4 CH_2       x              4800.      231.     20.8  0.000244  
  5 CH_3       (Intercept)  -59200      1755.    -33.7  0.0000573 
  6 CH_3       x              5800       529.     11.0  0.00163   
  7 CH_4       (Intercept)  -57100.     1567.    -36.4  0.0000455 
  8 CH_4       x              7500.      473.     15.9  0.000544

My question is: How can I use lapply or similar to pass the linear model coefficent values to solve for "x" for all of the voltage values ("y") in the original dataset?

i.e. for CH_1 x=(y+55800)/6200. This would give a value of 2.52 mm for the first voltage recording of CH_1 - (x=(-40160+55800)/6200).

Secondly, is there a way to this automatically for all for voltage variables at the same time?

score 1 · Accepted Answer · answered Dec 19 '19 at 01:21

You can do this in several different ways. Here's one.

First, load in the necessary packages and reproduce your sample data

require(tidyverse)
require(reshape2)

measurements <- data.frame(
                     CH_1 = c(-40160, -40160, -40168, -40172, -40160, -40168,
                              -40168, -40172, -40156, -40168, -40172, -40168),
                     CH_2 = c(-38180, -38140, -38168, -38116, -38184, -38172,
                              -38156, -38152, -38176, -38132, -38136, -38140),
                     CH_3 = c(-63972, -63972, -63972, -63984, -63988, -63980,
                              -63972, -63984, -63968, -63976, -63992, -63984),
                     CH_4 = c(-54560, -54552, -54568, -54544, -54568, -54564,
                              -54552, -54532, -54552, -54544, -54556, -54528))
calibrations <- data.frame(
                     x = 1:5,
                     CH_1 = c(-49000, -46000, -35000, -30000, -26000),
                     CH_2 = c(-42000, -36000, -32000, -28000, -22000),
                     CH_3 = c(-54000, -46000, -42000, -38000, -29000),
                     CH_4 = c(-49000, -44000, -33000, -27000, -20000))

Then we get the dataframe of linear models. To make it easier, we'll extract just the columns we need then "unmelt" the dataframe with dcast

calibrations                        %>%
  melt(id.vars = "x")               %>%
  group_by(variable)                %>%
  do(tidy(lm(value ~ x, data = .))) %>%
  magrittr::extract( , 1:3)         %>%
  dcast(variable ~ term)             ->
  coefs

This is easier to work with:

  variable (Intercept)    x
1     CH_1      -55800 6200
2     CH_2      -46400 4800
3     CH_3      -59200 5800
4     CH_4      -57100 7500

Now we just apply your reverse fit formula to each column of the measurements, matching the correct equation to each column;

for(i in seq_along(coefs$x))
{
  CH <- which(names(measurements) == coefs$variable[i])
  measurements$new <- (measurements[[CH]] - coefs$`(Intercept)`[i])/ coefs$x[i]
  names(measurements)[length(measurements[1, ])] <-
    paste0("dist.", names(measurements[CH]), collapse = "")
}

Which I believe gives the desired result

     CH_1   CH_2   CH_3   CH_4 dist.CH_1 dist.CH_2  dist.CH_3 dist.CH_4
1  -40160 -38180 -63972 -54560  2.522581  1.712500 -0.8227586 0.3386667
2  -40160 -38140 -63972 -54552  2.522581  1.720833 -0.8227586 0.3397333
3  -40168 -38168 -63972 -54568  2.521290  1.715000 -0.8227586 0.3376000
4  -40172 -38116 -63984 -54544  2.520645  1.725833 -0.8248276 0.3408000
5  -40160 -38184 -63988 -54568  2.522581  1.711667 -0.8255172 0.3376000
6  -40168 -38172 -63980 -54564  2.521290  1.714167 -0.8241379 0.3381333
7  -40168 -38156 -63972 -54552  2.521290  1.717500 -0.8227586 0.3397333
8  -40172 -38152 -63984 -54532  2.520645  1.718333 -0.8248276 0.3424000
9  -40156 -38176 -63968 -54552  2.523226  1.713333 -0.8220690 0.3397333
10 -40168 -38132 -63976 -54544  2.521290  1.722500 -0.8234483 0.3408000
11 -40172 -38136 -63992 -54556  2.520645  1.721667 -0.8262069 0.3392000
12 -40168 -38140 -63984 -54528  2.521290  1.720833 -0.8248276 0.3429333

Is there a way to pass coefficients of many simple linear regression models to corresponding columns of data and then solve for "x"?

1 Answers1