Assign a variable to a specific cell in a data frame, when the row position is dynamic

user_12350939 0
Viewed 900

The goal here is to automate a data processing pipeline, where I can read in a .csv file and run a script and output the processed file, ready for data plotting. All I'm doing is applying some operations to a few columns, but automating one step is unclear to me:

I need to divide values in one column by those in another, but before division I need to subtract a value from a specific cell. However in each different .csv that specific cell location will change, so I can't simply extract a column/row cell as a variable and use that. As an example data frame:

df<-

sampleid  t1    t2
a1        4     15
a2        3     18
a3        7     30
b1        6     17
blank     1     5

The function would then be

df <- df %>% mutate(ri=(t1-1)/(t2-1))

Where the "1" subtracted from t1 and t2 comes from the "blank" row "t1" value. I'd like to create a variable of the value of that cell, to substitute into that equation (occasionally it's something other than 1). The specific row in the csv/data frame will change based on the total sample number, so I can't choose a fixed cell every time.

Ideally it would just be 

df <- df %>% mutate(ri=(t1-x)/(t2-x))

Where "x" was assigned to that blank t1 value from the data frame.

Any thoughts on a nice way to do this? Thank you.

dplyr dataframe r
2 Answers

You can add variable x as a column and then use your formula (and then remove x again):

library(tidyverse)

df <- read.table(text = "sampleid  t1    t2
a1        4     15
a2        3     18
a3        7     30
b1        6     17
blank     1     5", header = TRUE, stringsAsFactors = FALSE)

df %>%
  mutate(x = df %>%
           filter(sampleid == "blank") %>%
           pull(t1)) %>%
  mutate(ri = (t1 - x) / (t2 - x)) %>%
  select(-x)

# sampleid t1 t2        ri
# 1       a1  4 1 0.2142857
# 2       a2  3 1 0.1176471
# 3       a3  7 1 0.2068966
# 4       b1  6 1 0.3125000
# 5    blank  1 1 0.0000000
user_61620110

You could use match : 

library(dplyr)
df %>% mutate(ri = {val = t1[match('blank', sampleid)];(t1- val)/(t2-val)})

#  sampleid t1 t2     ri
#1       a1  4 15 0.2143
#2       a2  3 18 0.1176
#3       a3  7 30 0.2069
#4       b1  6 17 0.3125
#5    blank  1  5 0.0000

Other variations could be with which.max,which or '==' if you only have one 'blank' value

df %>% mutate(ri = {val = t1[which.max(sampleid == 'blank')];(t1- val)/(t2-val)})

df %>% mutate(ri = {val = t1[which(sampleid == 'blank')[1]];(t1- val)/(t2-val)})

df %>% mutate(ri = {val = t1[sampleid == 'blank'];(t1- val)/(t2-val)})

You could use the same in base R with transform

transform(df, ri = {val = t1[which.max(sampleid == 'blank')];(t1- val)/(t2-val)})
user_39629140
Related