Difference between revisions of "Troque Week 8"

Revision as of 21:33, 19 October 2015

Source

• The methods described in this page are taken from this openwetware page.

Excel Files Update

• 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.

Things to remember

• Always save your work when you have a chance.

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:

• Insert a new Worksheet into my Excel file, and named it "scaled_centered".
• Go back to the "compiled_raw_data" worksheet, Select All and Copy. I went to my new "scaled_centered" worksheet, click on the upper, left-hand cell (cell A1) and Paste.
• Insert two rows in between the top row of headers and the first data row.
• In cell A2, type "Average" and in cell A3, type "StDev".
• I then computed the Average log ratio for each chip (each column of data). In cell B2, type 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 data.)

and press "Enter". Excel is computing the average value of the cells specified in the range given inside the parentheses. Instead of typing the cell designations, you can click on the beginning cell, scroll down to the bottom of the worksheet, and shift-click on the ending cell.

• You will now compute the Standard Deviation of the log ratios on each chip (each column of data). In cell B3, type the following equation:

=STDEV(B4:B5224)

and press "Enter".

• Excel will now do some work for you. Copy these two equations (cells B2 and B3) and paste them into the empty cells in the rest of the columns. Excel will automatically change the equation to match the cell designations for those columns.
• You have now computed the average and standard deviation of the log ratios for each chip. Now we will actually do the scaling and centering based on these values.
• Copy the column headings for all of your data columns and then paste them to the right of the last data column so that you have a second set of headers above blank colums of cells. Edit the names of the columns so that they now read: A1_scaled_centered, A2_scaled_centered, etc.
• In cell N4, type the following equation:

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

In this case, we want the data in cell B4 to have the average subtracted from it (cell B2) and be divided by the standard deviation (cell B3). We use the dollar sign symbols in front of the "2" and "3" to tell Excel to always reference that row in the equation, even though we will paste it for the entire column of 5221 genes. Why is this important?

• Copy and paste this equation into the entire column. One easy way to do this is to click on the original cell with your equation and position your cursor at the bottom right corner. You should see your cursor change to a thin black plus sign (not a chubby white one). When it does, double click, and the formula will magically be copied to the entire column of genes.
• Copy and paste the scaling and centering equation for each of the columns of data with the "_scaled_centered" column header. Be sure that your equation is correct for the column you are calculating.

Perform statistical analysis on the ratios

We are going to perform this step on the scaled and centered data you produced in the previous step.

• Insert a new worksheet and name it "statistics".
• Go back to the "scaling_centering" worksheet and copy the first column ("ID").
• Paste the data into the first column of your new "statistics" worksheet.
• Go back to the "scaling_centering" worksheet and copy the columns that are designated "_scaled_centered".
• Go to your new worksheet and click on the B1 cell. Select "Paste Special" from the Edit menu. A window will open: click on the radio button for "Values" and click OK. This will paste the numerical result into your new worksheet instead of the equation which must make calculations on the fly.
• Delete Rows 2 and 3 where it says "Average" and "StDev" so that your data rows with gene IDs are immediately below the header row 1.
• Go to a new column on the right of your worksheet. Type the header "Avg_LogFC_A", "Avg_LogFC_B", and "Avg_LogFC_C" into the top cell of the next three columns.
• Compute the average log fold change for the replicates for each patient by typing the equation:

=AVERAGE(B2:E2)

into cell N2. Copy this equation and paste it into the rest of the column.

• Create the equation for patients B and C and paste it into their respective columns.
• Now you will compute the average of the averages. Type the header "Avg_LogFC_all" into the first cell in the next empty column. Create the equation that will compute the average of the three previous averages you calculated and paste it into this entire column.
• Insert a new column next to the "Avg_LogFC_all" column that you computed in the previous step. Label the column "Tstat". This will compute a T statistic that tells us whether the scaled and centered average log ratio is significantly different than 0 (no change). Enter the equation:

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

(NOTE: in this case the number of replicates is 3. Be careful that you are using the correct number of parentheses.) Copy the equation and paste it into all rows in that column.

• Label the top cell in the next column "Pvalue". In the cell below the label, enter 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. Copy the equation and paste 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". On the dropdown button for the Pvalue header, go to "Number Filters". Then select "Less Than" and enter "0.05" for the text box next to "is less than". You should get 948 results in the bottom left corner
• Now we will perform adjustments to the p value to correct for the multiple testing problem. Label the next two columns to the right with the same label, Bonferroni_Pvalue.
• Type the equation =(Pvalue)*5221, (in this case, the Pvalue = in cell S2) Upon completion of this single computation, use the trick to copy the formula throughout the column.
• Replace 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). Use the trick to copy the formula throughout the column.

The Excel file can be located here.

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