Aim

What I had to do was copying ang pasting some cells into another Excel file (used by other lab members), in which for each gene I had the following table:

As you can see by comparing the tables, the needed work is time-consuming and error-prone, since the rows are organized by the name of the sample and not by gene and each sample/gene combination is in duplicate. This means that every time I had to filter out by gene, carefully select the right cells, and paste them in the other file; you can clearly imagine how long it used take and how many mistakes had been made.

For all these reasons, one night I decided to write a function that takes the PCR file and returns an Excel file with one gene table per sheet, all in ~20 seconds (and you will now see why it takes so much time).

Function command and required packages

Main steps

Let’s see roughly how the function is organized, without going into the details of the code, as this is not the aim of this post.

Since the function has no input, you have to somehow tell it which file to take: for this reason, the first line of code asks the user to choose the file of interest (via file.choose ()) and save the name/path into a character variable. The table is then imported into R; as you have noticed before, the table contains header rows and final rows that are not needed and that must be removed in such a way as to obtain an ordered table, with values in all the cells and with right column names: this is the first operation that the function implements on the table.

Subsequently, the user is asked to indicate which columns to keep (Sample, Target, Ct mean and Ct sd), in order to extrapolate them and work on a smaller dataset, including only the required data. In this regard, since I only need Ct mean and sd, one duplicate for each gene/target combination is deleted.

At this point everything is ready, thanks to a for loop in which one gene is taken into account in each iteration, the final Excel file is created and saved in the same folder as the original file.

Possible improvements

This is an example of how working more and more with R, you can notice inaccuracies and rookie “mistakes” - which are normal - and already have an idea of how you can improve your work, but this will be the content of a future post.