# Filtering the output from Table and export it into excel

I have the following functions

f1[c1_,c2_,p1_,n_,i_]:= Sum[
Binomial[n,j]  p1^j (1-p1)^(n-j),
{j,0,c1}] + Sum[
Binomial[n,k] p1^k (1-p1)^(n-k+n i),
{k,c1+1,c2}]


and

f2[c1_,c2_,p2_,n_,i_]:=Sum[
Binomial[n,j] p2^j (1-p2)^(n-j),
{j,0,c1}] + Sum[
Binomial[n,k] p2^k (1-p2)^(n-k+n i),
{k,c1+1,c2}]


(actually the functions are identical. Both are kept for "historical" reasons) From the output of

Table[Table[Table[{f1[c1, c2, 0.01, n, 5], f2[c1, c2, 0.05, n, 5]}, {c2,
c1 + 1, 6, 1}], {c1, 0, 4, 1}], {n, 175, 185}]


I need only such values which satisfy $f_1\geq 0.95$ and $f_2\leq0.05$ with there parameters (value of $c_1$,$c_2$ and $n$). And how to export this output in excel.

• @mikuszefski If you change $f_1$ and $f_2$ in 'Mathematica' code then it may be more useful. I am facing some issue's to do that. – SAAN Mar 27 '15 at 9:20
• Ok...I did thid as you mixed traditional and standard form. Will change it to plain code – mikuszefski Mar 27 '15 at 9:25
• Hi Ali, can you check your f1 and f2. The first p2^k in f2 does not make sense. Moreover, if I correct this to p2^j, I do not see any difference between f1 and f2. – mikuszefski Mar 27 '15 at 9:44
• Is it that you used = instead of := in your function definition that you couldn't just use {..., f[..., 0.01, ...], f[..., 0.05, ...], ...}? – mikuszefski Mar 27 '15 at 9:50
• There's still a comma missing before the iterator at the end of the f2 function. – TransferOrbit Mar 27 '15 at 9:51

First of all I think that one function definition is ok:

f[c1_,c2_,p_,n_,i_]:= Sum[
Binomial[n,j]  p^j (1-p)^(n-j),
{j,0,c1}] + Sum[
Binomial[n,j] p^j (1-p)^(n-j+n i),
{j,c1+1,c2}]


Then use the k_v's answer in the form

t=Flatten[
Table[
If[(a=f[c1, c2, 0.01, n, 5])≥0.95 && (b=f[c1, c2, 0.05, n, 5])≤0.05,
{c1,c2,n,a,b}],
{c1, 0, 4, 1}, {c2, c1 + 1, 6, 1}, {n, 175, 185}
]/.Null->Sequence[]/.{}-> Sequence[],
2]


as it seems that you want to keep track of the input parameters as well. Flattening provides a single list of parameter-solution vectors, which one exports again as said by k_v using

Export["myFile.xls",t]

• I am unable to find output from the above code. – SAAN Mar 27 '15 at 13:40
• Check with Directory[] to which directory Mathematica is writing by default. Look at the answer of zentient for setting a linux path. In Windows would be something like Export["C:\\users\\YOURUSERNAME\\Desktop\myFile.xls",t]. Change "YOURUSERNAME" by your real user name. And note that "users" may be different as well, if you are not using an english operating system (in Spanish, e.g., it is Usuarios, in German Benutzer, etc.). Also note the double backslash required to provide the system required bqckslash from a Mathematica string. – mikuszefski Mar 29 '15 at 11:40
• I got the excel file but empty and in Mathematica output it is just { }. Thats the issue... – SAAN Mar 29 '15 at 12:13
• @Ali, looking at the answer from @zentient, it seems that there are no values in your given range. So if the both conditions f1>0.95 and f2<0.05 are never fulfilled at the same time, your list will be empty. Check the values produced by your functions and according parameters. Maybe a parameter value is incorrect? – mikuszefski Apr 1 '15 at 9:28
• @Ali Ok, got it, your original post had (1-p)^(n-j-n i), which-as I said-becomes quite large due to the negative exponent. The code you commented now has (1-p)^(n-j+n i), such that the exponent stays positive and the values way below 1. I'll edit my answer and the post accordingly. – mikuszefski 12 mins ago – mikuszefski Apr 2 '15 at 12:47

You can generate a full Table of values as you stated

myTable = Table[
{c1, c2, n, f1[c1, c2, 0.01, n, 5], f2[c1, c2, 0.05, n, 5]},
{c1, 0, 4, 1},
{c2, c1 + 1, 6, 1},
{n, 175, 185}
];


Then make a selection of the values you wish using Select, Partition and Flatten

mySelection = Select[
Partition[Flatten[myTable], 5],
#[] >= 0.95 && #[] <= 10^17 &
]


Note that I changed the bound for the f2 function here as it seems to produce very different values than your original limits seemed to anticipate.

Finally Export the result to Excel

Export["~/Desktop/mySelect.xlsx", mySelection]


You will have to change the path for your OS if it's not Unix-based.

• I like the Select[] alternative but sort of prefer Flatten[..., 2] over Partition[Flatten[ ], 5] – mikuszefski Mar 27 '15 at 11:17
• @zentient $f_1$ and $f_2$ are the probabilities, so always lies between 0 and 1. – SAAN Mar 27 '15 at 13:22

firs of all there's no need to use nested Table, and you may use values selection directly inside the table evaluation

t=Table[
If[f1[c1, c2, 0.01, n, 5]≥0.95 && f2[c1, c2, 0.05, n, 5]≤0.05,
{f1[c1, c2, 0.01, n, 5], f2[c1, c2, 0.05, n, 5]}],
{c1, 0, 4, 1}, {c2, c1 + 1, 6, 1}, {n, 175, 185}]/.Null->Sequence[]/.{}-> Sequence[]


this way let not to brake the structure of output

to export - Export["table.xls",t]

• Thanks for your answer, I have huge interval of 'n' for specimen here I give small interval of 'n'. So in output I need parametric value's e.g 'c1','c2' and 'n' for each favourable output. Also usual 'Export' function is meaning less in that case. – SAAN Mar 27 '15 at 9:10
• Isn't Mathematica evaluating the f's twice without necessity? Could one do something like If[ ( (a=f1[...])>...) && ( (b=f2[...])<...),{a,b}], to be more efficient? – mikuszefski Mar 27 '15 at 10:37
• @mikuszefski I can't see any reason to define two similar functions too, but as far as they are used with different arguments unification doesn't matter for time-efficiency. Compile and ParallelTable would give best resoult on my opinion – k_v Mar 27 '15 at 10:52
• I was not talking about f1 vs f2, I meant that you first evaluate the specific set of parameters to check the condition, then evaluate the exact same set to put the result into the list. – mikuszefski Mar 27 '15 at 11:02
• @mikuszefski it's a good idea – k_v Mar 27 '15 at 11:15