R vs Excel: inconsistent results after filtering

Question

this is my first post so please let me know if it doesn't meet standards or if anything is hard to follow. Thanks!

Given a data.table (or data.frame) of differential gene expression results (from CuffDiff), I want to filter these results by a q-value cutoff of <= 0.05 and fold change of >= 2 or fold change <= 0.5, producing 2 tables (one up, one down regulated genes). I am splitting these results after filtering by sample1_sample2 comparisons using data.table split() function, which seems to be doing fine, but lets imagine we're just talking about one up and one down list to keep things simple:

The problem is that I am getting inconsistent gene list lengths when I apply the same filters to the unfiltered data in Excel using the filter tool from the Data tab.. but ONLY for the up-regulated gene list! (fold change >= 2)

In R I tried filtering by data.table first, then another try with the filter() function from tidyverse. Same results as compared to the Excel filter.

Steps in R: -using data.table

# calculate fold change
cuffdat[, fold_change := 2^`log2(fold_change)`]
# filter by q <= 0.05
cuff_filt <- cuffdat[q_value <= 0.05]
# filter by fold change and separate into up and down-regulated lists
cuff_filt_up <- cuff_filt[fold_change >= 2]
cuff_filt_down <- cuff_filt[fold_change <= 0.5]

-using tidyverse

cuff_filt_tidy_up <- cuffdat %>% filter(q_value <= 0.05) %>% filter(fold_change >= 2)
cuff_filt_tidy_down <- cuffdat %>% filter(q_value <= 0.05) %>% filter(fold_change <= 0.5)

The results are written to an excel file with write.xlsx() from the openxlsx package.

Then I see that about 5 out of 10 lists of up-regulated (fold_change >= 2) genes have a different number of rows between R and Excel, usually 10 or under, BUT all of the Down regulated lists are consistent.

Am I making some mistake with the >= 2 fold change filter that's right under my nose and I can't see it? I made sure the number columns were of numeric type in both R and Excel attempts, still same results. Also made sure I was using greater-than-equal for both R and Excel, and the same q-value cutoff (<= 0.05).

Any help would be greatly appreciated, thank you for your time!

Pavlos

dput of data.table filtered for q <= 0.05 and fold_change >= 2 that is inconsistent with Excel results:

structure(list(gene = c("ABLIM3", "ACVRL1", "ADAMDEC1", "ADAP2", 
"ADM", "AIF1L", "ANXA3", "ARHGEF10L", "ASGR1", "ATP5EP2", "AZU1", 
"BAMBI", "BEX1", "BPI", "BST1", "C11orf45", "C1QA", "C1orf228", 
"C1orf54", "CALB1", "CALD1", "CCL13", "CCL2", "CCL3", "CCL5", 
"CCL7", "CCL8", "CD163", "CD180", "CD300E", "CD93", "CDC42EP1", 
"CEACAM4", "CEACAM6", "CELA2A", "CELA2B", "CFP", "CLDN10", "CLEC1B", 
"CLEC4A", "CLEC5A", "CLEC7A", "COL15A1", "COL17A1", "COL23A1", 
"CRTAM", "CSTA", "CTSG", "CTSH", "CXCL1", "CXCL10", "CXCL3", 
"CXCL8", "CYP27A1", "CYYR1", "DEFA3", "DEFA4", "DFNA5", "ECSCR", 
"ELANE", "EMP1", "FAM26E", "FCER2", "FCGR1B", "FCGR3A", "FCN1", 
"FGD2", "FGF22", "FGFR1", "FLT1", "FOLR3", "FPR3", "GALNT14", 
"GGTA1P", "GLT1D1", "GLT8D2", "GPBAR1", "GPNMB", "GPR37", "HBE1", 
"HBG1", "HGF", "HIST2H2AA4", "HIST2H3A", "HIST2H4A", "HNMT", 
"HTRA1", "HTRA4", "IGFBP2", "IGFBP5", "IGFL2", "IL6", "IL7R", 
"IRAK2", "IRF8", "IRG1", "KCNJ16", "KCNMB1", "KRT79", "LAMP3", 
"LGMN", "LILRB1", "LILRB4", "LINC00211", "LINC00599", "LINC01272", 
"LINC01554", "LOC101929076", "LOC101929371", "LRP3", "LYZ", "MARCKS", 
"MARCO", "MATN2", "MCEMP1", "MEFV", "METTL7B", "MGAM", "MPEG1", 
"MPO", "MS4A4A", "MS4A6A", "MS4A7", "MSR1", "MYOM1", "NAT8B", 
"NME8", "NNMT", "NRG1", "NRP1", "NXF3", "OLR1", "PALD1", "PCOLCE2", 
"PDE4B", "PID1", "PLA2G7", "PPARG", "PPBP", "PRTN3", "PTCRA", 
"PTGIR", "PTGR1", "PTPRB", "PTPRO", "RAB39A", "RASSF4", "REN", 
"RETN", "RGS13", "RND1", "RPS16", "RPS17", "S100A12", "S100A8", 
"S100A9", "S100P", "S100Z", "S1PR3", "SCGB3A1", "SEC14L5", "SEPT10", 
"SERPINA1", "SERPINE1", "SGK1", "SHROOM4", "SIGLEC1", "SIGLEC9", 
"SLAMF1", "SLAMF7", "SLAMF8", "SLC2A5", "SLC35F3", "SLC7A7", 
"SLPI", "SMIM24", "SNORD116-14", "SOWAHC", "SPARC", "SPR", "STEAP4", 
"STOX2", "SULT1C2", "TACSTD2", "TDRD6", "THBS4", "TLR8", "TMEM158", 
"TMEM40", "TMEM45B", "TMEM52B", "TNF", "TNNT1", "TREM1", "TREML1", 
"TRIM40", "UGT3A2", "VCAN", "VSTM1", "ZG16B"), sample_1 = c("Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", "Day7_WT", 
"Day7_WT"), sample_2 = c("Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", "Day7_P385L", 
"Day7_P385L", "Day7_P385L"), value_1 = c(0.196317, 1.09146, 20.8197, 
0.621022, 0.873852, 0.239717, 21.2973, 0.552663, 0.410193, 0, 
196.848, 0.396366, 68.9018, 41.2329, 16.7361, 0.214353, 3.55599, 
8.74624, 1.08134, 0.399986, 0.6367, 0.706357, 28.1959, 1.55592, 
3.797, 1.94343, 0.578803, 1.22134, 0.604323, 0.35601, 0.878815, 
0.110819, 6.49893, 18.7182, 0.144987, 0.0716135, 2.6455, 0.603466, 
1.78848, 2.11726, 5.30953, 5.84686, 0.150989, 0.555573, 1.15995, 
0.199626, 47.6079, 271.548, 11.9672, 0.291714, 2.93827, 0.836771, 
4.63867, 0.237466, 0.284529, 17.7825, 10.9124, 0.522299, 0.562429, 
782.655, 7.06801, 0.156979, 0.664455, 1.8181, 0.260624, 5.50522, 
0.931271, 0, 0.290243, 0.310714, 1.3432, 0.790291, 1.84662, 1.16774, 
0.271535, 0.113489, 0.321571, 2.98619, 0.163897, 9.42726, 101.271, 
10.029, 6.65174, 4.08239, 1.58604, 0.899613, 0.192766, 1.28342, 
17.5503, 1.15971, 0.262154, 0.71209, 2.85253, 0.831977, 3.14351, 
0.0991417, 0.13456, 0.115604, 0.242635, 0.366147, 1.03823, 0.1519, 
2.49728, 0.221398, 0.317481, 0.471214, 0.1555, 0.0780108, 0.949722, 
3.39944, 2383.57, 5.08801, 0.0907437, 0.814164, 7.79311, 0.692013, 
0.602081, 0.195118, 0.510745, 728.918, 0.39708, 3.32267, 2.21729, 
0.447134, 0.366244, 0.204145, 0.354241, 0.143136, 0.227235, 1.48332, 
2.33952, 0.18941, 0.518775, 0.935541, 1.33145, 0.707636, 1.95249, 
1.07082, 42.1945, 381.344, 0.204003, 0.952851, 2.58545, 0.114845, 
0.580007, 0.451847, 3.82407, 0.0717851, 10.5769, 0.276201, 0.250173, 
296.959, 1.83607, 1.59106, 197.05, 283.342, 28.1712, 0.648204, 
0.974485, 2.43853, 0.130732, 0.24136, 1.63849, 1.72731, 4.37642, 
0.184318, 0.122259, 0.852032, 0.397234, 0.621078, 1.13032, 7.06435, 
0.265075, 1.03216, 13.7835, 5.59732, 0, 0.250413, 5.37537, 1.13913, 
0.447285, 0.547311, 0.21776, 0.302822, 0.111735, 2.2128, 1.46586, 
0.147455, 0.514018, 0.323362, 0.191044, 0.655298, 0.675996, 3.88774, 
0.388337, 0.218507, 0.146101, 6.62815, 3.739, 1.20073), value_2 = c(0.45324, 
2.50031, 43.1896, 1.37394, 1.92193, 0.587226, 45.5257, 1.14778, 
0.830821, 0.959745, 623.059, 1.02135, 143.798, 117.852, 37.1248, 
0.673406, 10.062, 18.3545, 2.20377, 1.43024, 1.49143, 1.6134, 
108.017, 3.33157, 7.692, 5.63897, 2.15207, 2.69161, 2.77923, 
1.14163, 2.34804, 0.381354, 15.8248, 67.7769, 1.08107, 0.987541, 
6.02725, 1.69545, 4.26655, 5.72015, 16.2719, 16.485, 0.452521, 
2.28894, 2.65065, 0.535928, 108.571, 552.764, 39.5988, 0.944952, 
6.8774, 2.0748, 10.0979, 0.580245, 0.796667, 172.562, 124.344, 
1.13476, 1.30508, 2721.9, 15.2263, 0.394521, 1.61213, 3.66241, 
0.726946, 11.4552, 2.19551, 0.505434, 0.635273, 0.752423, 3.65331, 
1.83181, 4.50106, 2.88836, 0.7528, 0.295539, 0.750386, 7.07191, 
0.398168, 22.0268, 222.866, 21.1652, 19.6471, 11.6842, 4.972, 
3.10357, 0.492323, 2.68307, 41.6834, 4.42447, 0.924586, 1.71136, 
7.25159, 1.6908, 6.5702, 0.326422, 0.278229, 0.559898, 0.653446, 
0.734408, 2.11568, 0.3392, 5.12233, 0.479956, 0.875594, 1.05421, 
0.418637, 0.280914, 2.69293, 7.35105, 5011.98, 11.1341, 0.316893, 
1.89383, 17.2269, 1.50445, 1.4986, 0.459178, 1.3315, 1958.92, 
0.927596, 9.36493, 5.71188, 0.940312, 0.737134, 0.720338, 0.738963, 
0.513854, 0.458352, 3.61355, 4.96238, 0.467575, 1.10506, 4.05077, 
2.90378, 1.8309, 5.81191, 2.28018, 102.355, 1126.06, 0.554947, 
1.97704, 5.32462, 0.325342, 1.31397, 1.40889, 9.03869, 0.452779, 
29.6162, 0.664511, 0.560313, 616.394, 15.1201, 4.84659, 459.573, 
663.471, 56.7901, 1.38745, 2.06871, 6.59829, 0.297108, 0.520698, 
4.1477, 3.57958, 10.2514, 0.509629, 0.285103, 1.73761, 1.01281, 
1.59825, 2.36676, 20.8266, 0.644454, 2.42696, 41.5507, 15.5206, 
133.307, 0.700605, 11.4562, 2.2809, 1.42092, 1.23192, 0.993941, 
1.13313, 0.295785, 4.61433, 3.02764, 0.384398, 1.13122, 0.861308, 
0.703129, 1.34023, 1.94952, 8.3316, 0.798568, 0.490072, 0.377865, 
14.4932, 10.4012, 3.06693), `log2(fold_change)` = c(1.20709, 
1.19585, 1.05273, 1.1456, 1.1371, 1.29258, 1.09601, 1.05437, 
1.01824, Inf, 1.66229, 1.36557, 1.06143, 1.51511, 1.14941, 1.65149, 
1.5006, 1.0694, 1.02716, 1.83824, 1.22801, 1.19163, 1.93771, 
1.09844, 1.0185, 1.53683, 1.89458, 1.14, 2.20129, 1.6811, 1.41782, 
1.78293, 1.28391, 1.85635, 2.89847, 3.78554, 1.18796, 1.49032, 
1.25434, 1.43386, 1.61573, 1.49542, 1.58354, 2.04263, 1.19228, 
1.42474, 1.18936, 1.02546, 1.72637, 1.69569, 1.2269, 1.31007, 
1.12227, 1.28894, 1.4854, 3.27858, 3.51029, 1.11944, 1.21439, 
1.79816, 1.10719, 1.32953, 1.27872, 1.01036, 1.47988, 1.05713, 
1.23729, Inf, 1.13012, 1.27596, 1.44353, 1.21281, 1.28538, 1.30653, 
1.47113, 1.3808, 1.22249, 1.24379, 1.28059, 1.22435, 1.13796, 
1.07752, 1.50991, 1.51708, 1.6484, 1.78655, 1.35275, 1.0639, 
1.24798, 1.93174, 1.81839, 1.26501, 1.34606, 1.02309, 1.06356, 
1.71917, 1.04803, 2.27598, 1.42928, 1.00416, 1.02699, 1.15901, 
1.03645, 1.11626, 1.46359, 1.16171, 1.42878, 1.84838, 1.5036, 
1.11266, 1.07226, 1.12981, 1.80413, 1.21792, 1.14439, 1.12036, 
1.31558, 1.23471, 1.38238, 1.42623, 1.22407, 1.49493, 1.36517, 
1.07243, 1.00912, 1.81908, 1.06077, 1.84397, 1.01227, 1.28459, 
1.08482, 1.30368, 1.09095, 2.11432, 1.12493, 1.37148, 1.5737, 
1.09043, 1.27845, 1.56212, 1.44376, 1.05302, 1.04226, 1.50228, 
1.17979, 1.64066, 1.241, 2.65705, 1.48547, 1.26657, 1.1633, 1.05359, 
2.85003, 1.60698, 1.22173, 1.22749, 1.01142, 1.09792, 1.08602, 
1.43608, 1.18438, 1.10926, 1.33995, 1.05127, 1.228, 1.46725, 
1.22155, 1.02812, 1.3503, 1.36365, 1.06618, 1.5598, 1.28167, 
1.23348, 1.59193, 1.47138, Inf, 1.48429, 1.0917, 1.00167, 1.66756, 
1.17047, 2.19042, 1.90378, 1.40447, 1.06025, 1.04644, 1.38233, 
1.13799, 1.41338, 1.87989, 1.03225, 1.52803, 1.09966, 1.04011, 
1.16532, 1.37091, 1.12869, 1.47603, 1.35288), test_stat = c(2.00658, 
3.12429, 2.424, 2.43753, 2.28625, 2.28247, 3.58784, 2.39823, 
1.54227, NaN, 5.61646, 2.27268, 3.52845, 5.13617, 3.68527, 2.86682, 
3.69143, 3.25166, 1.36873, 3.55905, 2.94807, 1.77855, 6.02128, 
2.02959, 2.62256, 3.12736, 3.19038, 3.00659, 4.87099, 3.41242, 
3.93737, 2.1148, 3.58978, 6.25633, 2.98712, 2.89908, 3.03393, 
2.75607, 2.5763, 3.25455, 5.15589, 2.97127, 2.89412, 5.17953, 
2.98921, 1.98099, 3.77133, 3.46914, 5.39893, 2.25766, 2.85099, 
2.36709, 3.18018, 2.0055, 2.72056, 8.9142, 9.7305, 2.15298, 1.55361, 
5.57314, 3.52214, 1.85323, 2.34815, 2.43331, 2.27055, 2.89937, 
2.92172, NaN, 2.33767, 2.02357, 2.69521, 2.69065, 3.41086, 2.76604, 
2.55088, 1.5522, 1.91425, 3.57149, 2.10078, 3.33414, 3.6941, 
3.16165, 3.58832, 2.74836, 2.09147, 2.33757, 1.92201, 2.49338, 
4.02273, 5.55087, 1.85676, 2.14516, 4.13423, 2.44174, 3.00543, 
1.95182, 1.54677, 2.08118, 2.17697, 1.96473, 2.25559, 1.48895, 
1.91592, 1.60909, 2.71248, 2.11492, 1.8125, 1.93759, 2.44829, 
3.36387, 2.69839, 3.6309, 1.89572, 3.00854, 3.20829, 2.56388, 
1.70861, 2.5025, 2.8625, 3.72582, 1.95305, 3.98376, 3.74537, 
2.14768, 2.24282, 1.7964, 1.82806, 2.20893, 1.2864, 3.44472, 
2.79631, 1.52063, 2.48732, 4.798, 2.96266, 2.99639, 3.50158, 
2.37866, 4.27389, 5.1125, 1.23164, 2.28288, 2.26708, 2.98269, 
2.71301, 2.97294, 1.60518, 2.52803, 3.64556, 1.76986, 1.62913, 
3.14814, 4.61991, 2.47169, 4.08856, 4.12746, 2.91727, 1.81595, 
2.52568, 2.47716, 2.12052, 1.89482, 3.60961, 2.83426, 3.65856, 
3.07015, 2.10828, 2.06137, 2.4479, 2.90173, 2.59276, 4.71028, 
2.2375, 2.82056, 4.37469, 3.80344, NaN, 2.97173, 3.47577, 2.07544, 
3.81824, 2.74719, 3.79967, 3.20849, 1.51488, 2.93369, 2.86607, 
1.72571, 2.08725, 2.40333, 2.53293, 1.91473, 2.6328, 2.95305, 
1.49653, 1.68442, 1.75777, 3.76893, 3.60025, 2.42658), p_value = c(0.00095, 
5e-05, 5e-05, 5e-05, 3e-04, 5e-05, 5e-05, 5e-05, 0.00735, 5e-05, 
5e-05, 0.00035, 5e-05, 5e-05, 5e-05, 5e-05, 5e-05, 5e-05, 0.0153, 
5e-05, 5e-05, 0.00335, 5e-05, 0.0012, 5e-05, 5e-05, 5e-05, 5e-05, 
5e-05, 5e-05, 5e-05, 0.00275, 5e-05, 5e-05, 0.0011, 0.0081, 5e-05, 
5e-05, 5e-05, 5e-05, 5e-05, 5e-05, 5e-05, 5e-05, 5e-05, 0.0012, 
5e-05, 5e-05, 5e-05, 6e-04, 5e-05, 0.00045, 5e-05, 0.002, 5e-05, 
5e-05, 5e-05, 0.00055, 0.00895, 5e-05, 5e-05, 0.00615, 0.00015, 
5e-05, 2e-04, 5e-05, 5e-05, 5e-05, 1e-04, 2e-04, 5e-05, 5e-05, 
5e-05, 5e-05, 5e-05, 0.0134, 0.00135, 5e-05, 0.00095, 5e-05, 
5e-05, 5e-05, 5e-05, 5e-05, 0.00115, 0.00015, 0.00215, 5e-05, 
5e-05, 5e-05, 0.0047, 9e-04, 5e-05, 5e-05, 5e-05, 0.0036, 0.00215, 
0.00225, 5e-04, 0.00095, 0.00015, 0.0056, 6e-04, 0.0068, 1e-04, 
4e-04, 0.00435, 0.0047, 3e-04, 5e-05, 5e-05, 5e-05, 0.0058, 5e-05, 
5e-05, 1e-04, 0.00685, 1e-04, 5e-05, 5e-05, 0.00135, 5e-05, 5e-05, 
3e-04, 1e-04, 0.00695, 0.00275, 0.00185, 0.00475, 5e-05, 5e-05, 
0.0078, 0.00015, 5e-05, 5e-05, 5e-05, 5e-05, 1e-04, 5e-05, 5e-05, 
0.01345, 5e-05, 5e-05, 5e-05, 5e-05, 5e-05, 0.007, 0.0029, 5e-05, 
0.0052, 0.0071, 5e-05, 0.00025, 1e-04, 5e-05, 5e-05, 5e-05, 0.00265, 
5e-05, 5e-05, 5e-04, 0.0016, 5e-05, 5e-05, 5e-05, 5e-05, 0.0011, 
0.00065, 2e-04, 5e-05, 5e-05, 5e-05, 0.00045, 5e-05, 5e-05, 5e-05, 
0.01215, 5e-05, 5e-05, 2e-04, 5e-05, 5e-05, 5e-05, 5e-05, 0.01275, 
5e-05, 5e-05, 0.00775, 5e-04, 1e-04, 2e-04, 0.0017, 5e-05, 5e-05, 
0.0105, 0.0051, 0.0022, 5e-05, 5e-05, 1e-04), q_value = c(0.0042003, 
0.000285792, 0.000285792, 0.000285792, 0.00149098, 0.000285792, 
0.000285792, 0.000285792, 0.0253213, 0.000285792, 0.000285792, 
0.00171472, 0.000285792, 0.000285792, 0.000285792, 0.000285792, 
0.000285792, 0.000285792, 0.0475363, 0.000285792, 0.000285792, 
0.0127844, 0.000285792, 0.00516979, 0.000285792, 0.000285792, 
0.000285792, 0.000285792, 0.000285792, 0.000285792, 0.000285792, 
0.0107526, 0.000285792, 0.000285792, 0.00478529, 0.0275369, 0.000285792, 
0.000285792, 0.000285792, 0.000285792, 0.000285792, 0.000285792, 
0.000285792, 0.000285792, 0.000285792, 0.00516979, 0.000285792, 
0.000285792, 0.000285792, 0.00278595, 0.000285792, 0.00215195, 
0.000285792, 0.00812306, 0.000285792, 0.000285792, 0.000285792, 
0.0025768, 0.0300158, 0.000285792, 0.000285792, 0.021703, 0.000792744, 
0.000285792, 0.00103153, 0.000285792, 0.000285792, 0.000285792, 
0.000545769, 0.00103153, 0.000285792, 0.000285792, 0.000285792, 
0.000285792, 0.000285792, 0.0424478, 0.00573932, 0.000285792, 
0.0042003, 0.000285792, 0.000285792, 0.000285792, 0.000285792, 
0.000285792, 0.00497806, 0.000792744, 0.00865833, 0.000285792, 
0.000285792, 0.000285792, 0.0171816, 0.00400226, 0.000285792, 
0.000285792, 0.000285792, 0.0136142, 0.00865833, 0.0090128, 0.00236561, 
0.0042003, 0.000792744, 0.0200045, 0.00278595, 0.0236748, 0.000545769, 
0.00193503, 0.0160615, 0.0171816, 0.00149098, 0.000285792, 0.000285792, 
0.000285792, 0.020627, 0.000285792, 0.000285792, 0.000545769, 
0.023826, 0.000545769, 0.000285792, 0.000285792, 0.00573932, 
0.000285792, 0.000285792, 0.00149098, 0.000545769, 0.0241239, 
0.0107526, 0.00758166, 0.0173405, 0.000285792, 0.000285792, 0.0266545, 
0.000792744, 0.000285792, 0.000285792, 0.000285792, 0.000285792, 
0.000545769, 0.000285792, 0.000285792, 0.0425826, 0.000285792, 
0.000285792, 0.000285792, 0.000285792, 0.000285792, 0.0242703, 
0.0112665, 0.000285792, 0.0187596, 0.0245698, 0.000285792, 0.00126356, 
0.000545769, 0.000285792, 0.000285792, 0.000285792, 0.0104107, 
0.000285792, 0.000285792, 0.00236561, 0.006668, 0.000285792, 
0.000285792, 0.000285792, 0.000285792, 0.00478529, 0.00299287, 
0.00103153, 0.000285792, 0.000285792, 0.000285792, 0.00215195, 
0.000285792, 0.000285792, 0.000285792, 0.0390354, 0.000285792, 
0.000285792, 0.00103153, 0.000285792, 0.000285792, 0.000285792, 
0.000285792, 0.0406769, 0.000285792, 0.000285792, 0.0265047, 
0.00236561, 0.000545769, 0.00103153, 0.00703736, 0.000285792, 
0.000285792, 0.034456, 0.0184481, 0.00883654, 0.000285792, 0.000285792, 
0.000545769), fold_change = c(2.30871485566876, 2.29079760459197, 
2.07445160372441, 2.21238121881941, 2.19938474307908, 2.44965741127896, 
2.13762679927055, 2.07681110102276, 2.02544653048882, Inf, 3.16518538320374, 
2.57678112841665, 2.08699913132124, 2.85820619850681, 2.2182315976387, 
3.14157930616448, 2.82960367915901, 2.09856041899164, 2.03800840306513, 
3.57573544257798, 2.34243659891459, 2.284106626408, 3.83097072178747, 
2.14123033989948, 2.02581158584617, 2.90156248984729, 3.71813718202978, 
2.20381023175322, 4.5989037372092, 3.20672358303539, 2.6718147830246, 
3.44124354821656, 2.4349801306227, 3.62090418181883, 7.45635216305727, 
13.7898991415732, 2.27830358558026, 2.80951285226128, 2.38557988812945, 
2.70168598310707, 3.06466631524195, 2.81946219777292, 2.99704345072917, 
4.11995905591424, 2.28513595261415, 2.68466113698341, 2.28051553824233, 
2.03560832978275, 3.30894199097148, 3.23931776930728, 2.34063503686857, 
2.47953570478214, 2.17689225003702, 2.44348457956698, 2.79994793398733, 
9.70400302375208, 11.3946918121503, 2.17262622949865, 2.32042649909708, 
3.47776391533374, 2.15425644489321, 2.51320786487872, 2.42623618794283, 
2.0144137000668, 2.78925531931653, 2.08078802260224, 2.357552665982, 
Inf, 2.18876945167508, 2.42159902598884, 2.7198554881876, 2.3178866227572, 
2.43746246049362, 2.47345902466331, 2.77238957766824, 2.60412734540823, 
2.33349115688014, 2.36819848149628, 2.42938307848435, 2.33650155928769, 
2.2006962016317, 2.11040516418897, 2.84792272298791, 2.86211174482046, 
3.13485779445302, 3.44988913391344, 2.55398490818306, 2.09057528775145, 
2.37508640622967, 3.81515058040535, 3.5268739134304, 2.40328875732423, 
2.5421691022536, 2.03226706144957, 2.09008265983796, 3.29246932644883, 
2.06770446875457, 4.84326519342455, 2.69312277236306, 2.00577530706733, 
2.03776826845119, 2.23304140217502, 2.05117417052333, 2.16784258332078, 
2.75793795293665, 2.23722444638262, 2.69218956885596, 3.60095607144083, 
2.83549379655089, 2.16243984462348, 2.10272473343799, 2.18829918899484, 
3.49218502812462, 2.32611109253172, 2.21052645470794, 2.17401214511345, 
2.48902375616379, 2.35334037562828, 2.60698087645056, 2.68743525857218, 
2.33604813223885, 2.81850475226273, 2.57606679201964, 2.1029725226493, 
2.01268305030316, 3.52856112033918, 2.08604459528505, 3.58996555065788, 
2.01708237090597, 2.43612810488093, 2.12111082266887, 2.46857760502335, 
2.13014258039134, 4.32985886502834, 2.18090964436733, 2.58735856232568, 
2.9766714645419, 2.12937493753454, 2.42578216096095, 2.95287441434017, 
2.72028913259084, 2.07486863671876, 2.05945128416722, 2.83290063586317, 
2.26543798738013, 3.11808444475856, 2.36362309386873, 6.30741998718128, 
2.80008379160436, 2.40588886203887, 2.23969145963501, 2.07568856660972, 
7.2101536302576, 3.0461352507126, 2.3322622164661, 2.34159245132597, 
2.01589430621481, 2.14045870132923, 2.12287584700376, 2.70584650193043, 
2.27265705936133, 2.15734962216724, 2.53142545389945, 2.07235333121472, 
2.34242036243762, 2.76494349974408, 2.3319712464418, 2.03936498855291, 
2.5496513846185, 2.57335411926508, 2.09388179393958, 2.94812970869304, 
2.43120239300172, 2.35133484084729, 3.01452354997332, 2.77287003780052, 
Inf, 2.7977945011341, 2.13125024502472, 2.00231645203565, 3.17676858662151, 
2.250850130045, 4.56438346167388, 3.74192333302239, 2.64720514366221, 
2.08529284408902, 2.06542689864352, 2.60689052694399, 2.20074196429853, 
2.66360471672167, 3.68046996998894, 2.04521143865008, 2.88391771167986, 
2.14304181480205, 2.05638443881326, 2.24282957640845, 2.5863365146746, 
2.18660101722396, 2.78182178917644, 2.55421505591909)), row.names = c(NA, 
-200L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x00000204c7761ef0>)

The one thing you could do to make this a lot easier is to use dput to share data instead of an image. dput(cuffdat[955629:955632,]) will output code that we can use to create a data frame just like the one in your image. Then, we will have the ability to test your code directly on your data. — Tanner33
– Tanner33, Commented Oct 11, 2020 at 16:58
Also I suggest reduce the problem to a representative example with simpler problem definition. Which means reduce code to the minimum possible needed to replicate the problem. Have you read the reprex guidelines of stack overflow? If not read this first, stackoverflow.com/help/minimal-reproducible-example — Lazarus Thurston
– Lazarus Thurston, Commented Oct 11, 2020 at 17:01
Thank you both for the suggestions, I just added the dput output for one of the tables giving the issue, I will remove the image.. I just used it to show the format of what the table looks like, and I will have a reprex shortly. — Pavlos Bousounis
– Pavlos Bousounis, Commented Oct 11, 2020 at 17:11

Pavlos Bousounis · Accepted Answer · 2020-10-11 18:23:27Z

0

I think I found the issue, I was removing the rows with log2foldchange == Inf or -Inf from the up and down regulated lists in R, but they were still present in the unfiltered list I was doing the Excel filtering with. It appears these remained in the down-regulated lists because they were being turned into 0s, but they were being turned in NaNs in the up-regulated lists and being thrown out by Excel. I will update my answer when I have more details, but everything seems to be working well now. Thanks!

answered Oct 11, 2020 at 18:23

Pavlos Bousounis

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

R vs Excel: inconsistent results after filtering

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related