# How to efficiently do data transformation in R? [closed]

My research generates raw data from titrations that needs a substantial number of calculations before I get the numbers in a form that I can analyze. Most of these calculations are relatively simple unit conversions etc... Typically I create a spreadsheet in Excel to perform all of these calculations and then only import the finalized values into R for analysis.

I would like to move all of the data processing into R so that the data are all in one place therefore easier to work with.

My attempts at doing these calculations in R has resulted in the creation of lots of objects that are the result of the calculations, such as:

mlTitr <- endTitr - beginTitr
val <- mlTitr * std - blnk
conVal <- val * convFac
.
.
.


I then stitch all the new objects together into a data frame but this approach seems inefficient.

Is there a better way to do this in R?

(or am I barking up the wrong tree and this is really the task that a spreadsheets were created for)?

• Doing the calculations in R instead of Excel should be preferable because that makes the calculations both documented and reproducible. Commented Jun 7, 2012 at 21:53

I prefer R to Excel for cleaning data. It is certainly possible to do heavy data cleaning in Excel, but I find the mixture of the spreadsheet, Excel equations, and Visual Basic to be tedious and less productive than R.

When I need to repeatedly clean data, I usually write an R function, which only needs to be written once, can be re-run tomorrow or a year from now, is reproducible, and can easily be given to collaborators or passed on to the next generation. I also find it easier to find bugs in an R script compared to hunting through Excel cell equations.

You did not specify what your data look like, but consider these very unrealistic titration data as an example:

## Example data
n <- 100
beginTitr <- rnorm(n, mean = 10, sd = 1)
endTitr <- beginTitr - rnorm(n, mean = 2, sd = 1)

# Variables
titrData <- data.frame(beginTitr, endTitr)

# Constants
std <- 0.00500
blnk <- 1e-5
convFac <- 2


Then, you could write a function to clean the data, such as:

## Function to clean data
cleanData <- function(x, std, blnk, convFac)
{
mlTitr <- x$endTitr - x$beginTitr
val <- mlTitr * std - blnk
conVal <- val * convFac

out <- data.frame(x, mlTitr, std, blnk, val, convFac, conVal)

return(out)
}


And then you could call the function on your data whenever needed:

## Clean data
titrDataClean <- cleanData(x = titrData, std = std, blnk = blnk, convFac = convFac)

## View data