4
$\begingroup$

I have searched and have not found a solution.

n = Table[i^2 + j*3, {i, 3}, {j, 7}] // TableForm
Export["stuck.xls", n]
4  7  10  13  16  19  22
7  10 13  16  19  22  25
12 15 18  21  24  27  30

What I want is to have the cell columns and rows give me exactly what i and j were, like this:

    j   1   2   3   4   5   6   7
i                               
1       4   7   10  13  16  19  22
2       7   10  13  16  19  22  25
3       12  15  18  21  24  27  30

From this I can see that the result of i=3 and j=7 is 30.

Can anyone help with this task?

$\endgroup$
  • $\begingroup$ this did not paste like I expected. The columns should have been 7 wide (as shown) but the rows should have been 3 deep. The place where you see a number 2 should have been the start of a new row and where the number 3 shows should have been the start of the third row . . . .sorry for the confussion $\endgroup$ – John Jan 9 '15 at 5:35
  • 1
    $\begingroup$ I fixed your post for you. Welcome to the site. Please have a look at the faq, and you may also want to see the revised source code of your question that produced this formatting. $\endgroup$ – Oleksandr R. Jan 9 '15 at 5:40
  • $\begingroup$ Hi Oleksandr,That was a speedy reply . . .thank you much $\endgroup$ – John Jan 9 '15 at 5:46
  • $\begingroup$ sorry, hit return too soon . . .where exactly can i see the "revised code" you referenced . . .thx again $\endgroup$ – John Jan 9 '15 at 5:46
  • $\begingroup$ Click on the "edited <> ago" text to see that: here. $\endgroup$ – Oleksandr R. Jan 9 '15 at 5:50
1
$\begingroup$

You can pad n with required column and row headers before exporting to Excel:

padF = With[{d = Dimensions[#], ap = ArrayPad[#, {{1, 0}, {1, 0}}, ""]}, 
    Transpose@Prepend[Transpose[Join[{Prepend[Range@d[[2]], "j"]}, ap]], 
                      Join[{"", "i"}, Range@d[[1]]]]] &;

n = Table[i^2 + j*3, {i, 3}, {j, 7}];
nn = padF@n;
Export["stuck2.xlsx", nn]

enter image description here

$\endgroup$
4
$\begingroup$

You are looking for the TableHeadings option of TableForm.

table = Table[i^2 + j*3, {i, 1, 3, .5}, {j, 1, 7, .5}];
rowHeaders = Range[1, 3, .5];
columnHeaders = Range[1, 7, .5];
{rowVar, columnVar} = {"i", "j"};

TableForm[table, TableHeadings -> {rowHeaders, columnHeaders}]

If you want to show the row and column variable names then you have to pad the data with empty strings to make room for the extra labels.

TableForm[
  ArrayPad[table, {{1, 0}, {1, 0}}, ""],
  TableHeadings -> {{rowVar}~Join~rowHeaders,
                    {columnVar}~Join~columnHeaders}
]

Note: Data should be left as data (Table or List); separate from presentation (TableForm). That way you can choose to display it as a TableForm, MatrixForm, or easily work with the data the later on. In particular

(* Bad practice *)
n = Table[i^2 + j*3, {i, 3}, {j, 7}] // TableForm;
(* Fail *)
TableForm[n, TableHeadings -> {Range@3, Range@7}]

Edit: There used to be an answer by @kguler which addresses exporting the headers to Excel by prepending the headers to the data. This uses the same method, perhaps in a more easily readable format.

formatTable[table_, {rowHeaders_, columnHeaders_}, {rowVar_, columnVar_}] :=
  table //
  ArrayPad[#, {{1, 0}, {1, 0}}, ""] & //
  Prepend[#, {columnVar}~Join~columnHeaders] & //
  Transpose // 
  Prepend[#, {"", rowVar}~Join~rowHeaders] & //
  Transpose;

Export["stuck.xls", formatTable[table, {rowHeaders, columnHeaders}, {rowVar, columnVar}]]
$\endgroup$
  • $\begingroup$ Thanks Tim, This works as long as my increments are integers. But what about when my formula looks like this n = Table[i^2 + j*3, {i, 1, 3, .1}, {j, 1, 7, .1}]; $\endgroup$ – John Jan 9 '15 at 6:22
  • $\begingroup$ TableHeadings->{Range[1,3,.1],Range[1,7,.1]} $\endgroup$ – Timothy Wofford Jan 9 '15 at 6:25
  • $\begingroup$ Bingo, thank you very much and thank you as well kguler ! $\endgroup$ – John Jan 9 '15 at 6:34
  • $\begingroup$ Ok, sorry to bring this thread up again, i got the table to contain headers in Mathematica just fine but when I export to excel they were missing once again :-( I am new to all of this so i apologize for what are probably painfully mundane questions. Thanks in advance for the help all $\endgroup$ – John Jan 9 '15 at 23:43
  • $\begingroup$ i realize now that i did not mention the word "excel" in the question, i will be more clear in the future all $\endgroup$ – John Jan 10 '15 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.