Difference between revisions of "Troque Week 8"

Revision as of 08:49, 26 October 2015

Sources

• The methods described in Part 1 of this page are taken from this openwetware page.
• The methods for Part 2 have been adapted from this page.

Files created

• The Excel file created on Thursday (October 15, 2015) can be downloaded here.
• A more updated Excel file with the B-H p-value correction can be downloaded here.
• The tab delimited txt file can be seen here

Things to note

• Always save your work when you have a chance.
• For this assignment, my partner was Erich Yanoschik.
• I worked on the 2009 data while he worked on the 2010 data.
• On Thursday (October 22), we were assigned to analyze decreased expressions of our data using GenMAPP.

Part 1

Normalize the log ratios for the set of slides in the experiment

To scale and center the data (between chip normalization) I performed the following operations:

• Inserted a new Worksheet into my Excel file, and named it "scaled_centered".
• Selected all and copied and pasted everything from the "compiled_raw_data" worksheet into this new "scaled_centered".
• Inserted two rows in between the top row of headers and the first data row.
• In cell A2, typed "Average" and in cell A3, typed "StDev".
• I then computed the Average log ratio for each chip (each column of data). In cell B2, I typed the following equation:

=AVERAGE(B4:B5224)

(Note: We tried to do a keyboard shortcut using CTRL + Shift + Down buttons, but row 363 has a missing data so we had to manually type in "B5224" for the end of the all the data.)

and pressed "Enter". Excel then computed the average value of the cells specified in the range given inside the parentheses. Another approach for selecting all of the cells we needed was, instead of typing the cell designations, we could have clicked on the beginning cell, scrolled down to the bottom of the worksheet, and shift-clicked on the ending cell.

• I then computed the Standard Deviation of the log ratios on each chip (each column of data). In cell B3, I typed the following equation:

=STDEV(B4:B5224)

and pressed "Enter".

• I copied these two equations (cells B2 and B3) and pasted them into the empty cells in the rest of the columns. Excel automatically changed the equation to match the cell designations for those columns.
• Now that the average and standard deviations of the log ratios have been computed for each chip, it's time for the scaling and centering based on these values.
• I copied the column headings for all of your data columns and then pasted them to the right of the last data column so that I had a second set of headers above blank colums of cells. I edited the names of the columns so that they now read: A1_scaled_centered, A2_scaled_centered, etc.
• In cell N4, I typed the following equation:

=(B4-B$2)/B$3

In this case, I wanted the data in cell B4 to have the average subtracted from it (cell B2) and be divided by the standard deviation (cell B3). I used the dollar sign symbols in front of the "2" and "3" to tell Excel to always reference that row in the equation, even though I will paste it for the entire column of 5221 genes. This was important since we only want the first value (i.e. B4 into B5, etc. instead of B2 -> B3 or B3 -> B4)to change when we drag the equation down to the other cells in the column. Since B2 and B3 contained the average and standard deviations for the replicates and the values below them are the actual data for the replicates, we would want to fix the equation so that we are subtracting those fixed cells from the cells below them.

• I copied and pasted this equation into the entire column. One easy way to do this is to click on the original cell with the equation and position the cursor at the bottom right corner. The cursor then change into a thin black plus sign (not a chubby white one). Double clicking this when it does will make the formula be magically copied to the entire column of genes.
• I then copied and pastes the scaling and centering equation for each of the columns of data with the "_scaled_centered" column header.

Perform statistical analysis on the ratios

This step uses the scaled and centered data produced in the previous step. The following operations are what I executed:

• I inserted a new worksheet and name it "statistics" and copied the first column ("ID") from the "scaled_centered" worksheet into this new worksheet.
• I pasted the data into the first column of the new "statistics" worksheet.
• I went back to the "scaled_centered" worksheet and copied the columns that are designated "_scaled_centered".
• I then went to my new worksheet and clicked on the B1 cell and selected "Paste Special" from the Edit menu. A window opened; I clicked on the radio button for "Values" and clicked OK. This pasted the numerical result into my new worksheet instead of the equation which must make calculations on the fly.
• I then deleted Rows 2 and 3 where it says "Average" and "StDev" so that the data rows with gene IDs are immediately below the header row 1.
• Next, I went to a new column on the right of my worksheet and typed the header "Avg_LogFC_A", "Avg_LogFC_B", and "Avg_LogFC_C" into the top cell of the next three columns.
• Excel compute the average log fold change for the replicates for each patient when I typed the equation:

=AVERAGE(B2:E2)

into cell N2. I copied this equation and pasted it into the rest of the column.

• I created the equation for patients B and C as well and pasted it into their respective columns.
• I then needed to compute the average of the averages. I typed the header "Avg_LogFC_all" into the first cell in the next empty column and created the equation that will compute the average of the three previous averages I calculated and pasted it into this entire column.
• I inserted a new column next to the "Avg_LogFC_all" column that I computed in the previous step and labeled the column "Tstat". This will compute a T statistic that tells whether the scaled and centered average log ratio is significantly different than 0 (no change). Then I entered the equation:

=AVERAGE(N2:P2)/(STDEV(N2:P2)/SQRT(number of replicates))

(NOTE: in this case the number of replicates is 3.) Next, I copied the equation and pasted it into all rows in that column.

• I labeled the top cell in the next column "Pvalue". In the cell below the label, I entered the equation:

=TDIST(ABS(R2),degrees of freedom,2)

The number of degrees of freedom is the number of replicates minus one, so in our case there are 2 degrees of freedom. I copied the equation and pasted it into all rows in that column.

Calculate the Bonferroni p value Correction

• Before doing the following, I selected all of the first row and clicked on "Sort & Filter" -> "Filter" for the sanity check portion of the assignment. On the dropdown button for the Pvalue header, I went to "Number Filters", then selected "Less Than" and entered "0.05" for the text box next to "is less than". In the bottom left corner of Excel, I got 948 results.
• Then, I performed adjustments to the p value to correct for the multiple testing problem. I went ahead and labeled the next two columns to the right with the same label, Bonferroni_Pvalue.
• The equation for this is =(Pvalue)*5221, (in this case, the Pvalue = cell S2) Upon completion of this single computation, I used the trick to copy the formula throughout the column.
• Then I replaced any corrected p value that is greater than 1 by the number 1 by typing the following formula into the first cell below the second Bonferroni_Pvalue header: =IF(T2>1,1,T2). I also used the trick to copy the formula throughout this column.

Checkpoint: The Excel file created from doing the procedures above can be located here.

Calculate the Benjamini & Hochberg p value Correction

• For this part, I inserted yet another worksheet and named it "B-H_Pvalue".
• I copied and pasted the "ID" column from my previous worksheet into the first column of this new worksheet.
• I inserted a new column on the very left and named it "MasterIndex". I needed to create a numerical index of genes so that I can always sort them back into the same order.
  • This is done by typing a "1" in cell A2 and a "2" in cell A3 and performing the trick for doing it for all the remaining columns:
  • I selected both cells with "1" and "2" and hovered my mouse over the bottom-right corner of the selection until it makes a thin black + sign. Double-clicking on the + sign would then fill the entire column with a series of numbers from 1 to 5221 (the number of genes on the microarray).
• For the following, I used Paste special > Paste values so that the values (instead of references to the other columns) are pasted. I copied the unadjusted p values from my previous worksheet and pasted it into Column C.
• I then selected all of columns A, B, and C, sorted by ascending values on Column C, and finally clicked the sort button from A to Z on the toolbar, in the window that appears, sort by column C, smallest to largest.
• Next, I typed the header "Rank" in cell D1. This is for creating a series of numbers in ascending order from 1 to 5221 in this column. This is the p value rank, smallest to largest. Same with the "MasterIndex"I typed "1" into cell D2 and "2" into cell D3, selected both cells D2 and D3, and double-clicked on the plus sign on the lower right-hand corner of my selection to fill the column with a series of numbers from 1 to 5221.
• Now I could calculate the Benjamini and Hochberg p value correction. I typed B-H_Pvalue in cell E1. I also entered the following formula in cell E2: =(C2*5221)/D2, which I then copied to the entire column.
• I also typed "B-H_Pvalue" into cell F1.
• With this, I typed the following formula into cell F2: =IF(E2>1,1,E2) and pressed enter. I copied that equation to the entire column.
• Select columns A through F. Now sort them by your MasterIndex in Column A in ascending order.
• Copy column F and use Paste special > Paste values to paste it into the next column on the right of your "statistics" sheet.

Prepare file for GenMAPP

• For the actual worksheet to feed into the GenMAPP program, I inserted a new worksheet and named it "forGenMAPP".
• I then went back to the "statistics" worksheet and chose Select All and Copy.
• In the new sheet, I clicked on cell A1 and selected Paste Special, clicked on the Values radio button, and clicked OK. The following steps are to now format this worksheet for import into GenMAPP.
• I selected Columns B through Q (all the fold changes), and selected the menu item Format > Cells. Under the number tab, I selected 2 decimal places and clicked OK.
• Next, I selected all the columns containing the p values. I selected the menu item Format > Cells, and ender the number tab, selected 4 decimal places.
• Since they are no longer needed, I deleteed the left-most Bonferroni p value column, preserving the one that shows the result of my "if" statement.
• I then inserted a column to the right of the "ID" column. I named the header at the top cell of this column "SystemCode"and filled the entire column (each cell) with the letter "N" using the trick to copy values to the rest of the column.
• Then, I selected the menu item File > Save As, and chose "Text (Tab-delimited) (*.txt)" from the file type drop-down menu. (This will be the file type that I fed into GenMAPP). Excel made me click through a couple of warnings because it doesn't like the user going all independent and choosing a different file type than the native .xls so I just clicked OK in all of them. The new *.txt file is now ready for import into GenMAPP.
  • I uploaded both the .xls and .txt files (seen at the checkpoint below) that I have just created into my journal page in the class wiki and added my initials to differentiate it from the other students' files so that they don't overwrite it.

Checkpoint: The files created can be found here (Excel) and here (txt).

Part 2

I will be working on the 2009 data.

Each time you launch GenMAPP, you need to make sure that the correct Gene Database (.gdb) is loaded.

• Look in the lower left-hand corner of the window to see which Gene Database has been selected.
• If you need to change the Gene Database, select Data > Choose Gene Database. Navigate to the directory C:\GenMAPP 2 Data\Gene Databases and choose the correct one for your species.
• For the exercise today, you will need to download the appropriate Vibrio cholerae Gene Database.
  • Half of the class will use the Vc-Std_External_20090622.gdb Gene Database that was initially created by the Fall 2008 Biological Databases class.
    • To download this Gene Database, follow this link to the XMLPipeDB SourceForge Download page.
  • Half of the class will use a new Vc-Std_External_20101022.gdb Gene Database that was created by Drs. Dahlquist and Dionisio a year later.
    • To download this Gene Database, follow this link to the XMLPipeDB SourceForge Download page.
  • The members of a pair should each choose a different gene database.
• Click on the link for the Gene Database to which you have been assigned, download the file, and save it into the folder C:\GenMAPP 2 Data\Gene Databases, and extract it.

Checkpoint: There were 772 errors in the 2009 data file. The gex file is located here. The EX.txt file can be found here.

Files created:

• Updated gex file
• GO txt file
• GO Excel file
• GMF file
• VC0647 - exoribonuclease result

Assignment Links

Weekly Assignments

• Week 1
• Week 2
• Week 3
• Week 4
• Week 5
• Week 6
• Week 7
• Week 8
• Week 9
• Week 10
• Week 11
• Week 12
• No Week 13 Assignment
• Week 14
• Week 15

Individual Journal Entries

• Week 1 - This is technically the user page.
• Week 2
• Week 3
• Week 4
• Week 5
• Week 6
• Week 7
• Week 8
• Week 9
• Week 10
• Week 11
• Week 12
• No Week 13 Assignment
• Week 14
• Week 15

Shared Journal Entries

• Week 1
• Week 2
• Week 3
• Week 4
• Week 5
• Week 6
• Week 7
• Week 8
• Week 9
• Week 10
• Week 11
• Week 12
• No Week 13 Assignment
• Week 14
• Week 15