Difference between excel and scipy cumulative binomial distribution p values?

Question

I have this table (NumSucc = Number of successes, NumberTrials = Number of trials and Prob is the probability of success) :

Gene    NumSucc NumTrials   Prob
Gene1   16       26        0.9548
Gene2   16       26        0.9548
Gene3   12       21        0.9548
Gene4   17       27        0.9548
Gene5   17       27        0.9548
Gene6   17       27        0.9548
Gene7   8        15        0.9548
Gene8   10       17        0.9548

I want a cumulative Binomial distribution P value for each row. When I put this exact table into excel columns A-D, and then in column E type the function (e.g. for row 2):

=BINOMDIST(B2,C2,D2,1)

The output table looks like this:

Gene    NumSucc NumTrials   Prob    Binomial
Gene1   16  26  0.9548  9.68009E-08
Gene2   16  26  0.9548  9.68009E-08
Gene3   12  21  0.9548  1.40794E-07
Gene4   17  27  0.9548  1.47463E-07
Gene5   17  27  0.9548  1.47463E-07
Gene6   17  27  0.9548  1.47463E-07
Gene7   8   15  0.9548  1.79741E-06
Gene8   10  17  0.9548  5.01334E-06

Alternatively, when I put this exact table into Scipy with this code:

import glob
import os
import scipy
from scipy.stats.distributions import binom
import sys

def WriteBinomial(InputFile,output):
    open_input_file  = open(InputFile, 'r').readlines()[1:]
    for line in open_input_file:
        line = line.strip().split()
        GeneName,num_succ,num_trials,prob = line[0],int(line[1]),int(line[2]),float(line[3])
        print GeneName + "\t" + str(num_succ) + "\t" + str(num_trials) + "\t" + str(prob) + "\t" + str((binom.cdf(num_succ-1, num_trials, prob)))


WriteBinomial(sys.argv[1],sys.argv[2])

The output is:

GeneName    NumSucc NumTrials   Prob    Binomial
Gene1   16  26  0.9548  6.59829603211e-09
Gene2   16  26  0.9548  6.59829603211e-09
Gene3   12  21  0.9548  7.92014917046e-09
Gene4   17  27  0.9548  1.06754559723e-08
Gene5   17  27  0.9548  1.06754559723e-08
Gene6   17  27  0.9548  1.06754559723e-08
Gene7   8   15  0.9548  8.41770305586e-08
Gene8   10  17  0.9548  2.93060582331e-07

Does anyone know why the two methods do not give the same result?

score 0 · Answer 1 · answered Mar 07 '17 at 21:57

Your Python code has "num_succ-1" while your Excel formula don't have in "B2-1".

Python -> "binom.cdf(num_succ-1, num_trials, prob)" Excel -> "=BINOMDIST(B2,C2,D2,1)"

The code below should produce the same output as excel.

import glob
import os
import scipy
from scipy.stats.distributions import binom
import sys

def WriteBinomial(InputFile,output):
    open_input_file  = open(InputFile, 'r').readlines()[1:]
    for line in open_input_file:
        line = line.strip().split()
        GeneName,num_succ,num_trials,prob = line[0],int(line[1]),int(line[2]),float(line[3])
        print GeneName + "\t" + str(num_succ) + "\t" + str(num_trials) + "\t" + str(prob) + "\t" + str((binom.cdf(num_succ, num_trials, prob)))


WriteBinomial(sys.argv[1],sys.argv[2])

Difference between excel and scipy cumulative binomial distribution p values?

1 Answers1