# Export to excel or CSV file with column and rows. List manipulation required

ListA = {{{n1, p1, a1}, {n1, p2, a2}, {n1, p3, a3}, {n1 p4, a4}, {n1 p5,a5},
{n1,p6, a6}},{{n2, p1, b1}, {n2, p2, b2}, {n2, p3, b3}, {n2, p4, b4},
{n2, p5, b5}, {n2, p6, b6}}, {{n3, p1, c1}, {n3, p2, c2}, {n3, p3, c3},
{n3, p4, c4}, {n3, p5, c5}, {n3,p6, c6}},.......etc...........
{{n6, p1, f1}, {n6, p2, f2}, {n6, p3, f3}, {n6, p4, f4}, {n6, p5, f5},
{n6, p6, f6}}}


I want to export ListA into CSV file so ns are the columns and the ps are the rows. Then the values A-F at each cell at n column and p row. Something like Export["MathematicaData.CSV", ListA /@ {1, 2, 3}], but this does not work as intended.

Here is an example set of numbers:

 {{{0.674, 0.573, -0.111}, {0.674, 0.912, -0.109}, {0.674,
1.251, -0.107}, {0.674, 1.59, -0.106}, {0.674,
1.93, -0.105}}, {{0.99, 0.573, -0.11}, {0.99,
0.912, -0.108}, {0.99, 1.251, -0.107}, {0.99, 1.59, -0.105}, {0.99,
1.93, -0.104}}, {{1.307, 0.573, -0.109}, {1.307,
0.912, -0.108}, {1.307, 1.251, -0.106}, {1.307,
1.59, -0.105}, {1.307, 1.93, -0.103}}, {{1.623,
0.573, -0.109}, {1.623, 0.912, -0.107}, {1.623,
1.251, -0.106}, {1.623, 1.59, -0.104}, {1.623,
1.93, -0.103}}, {{1.939, 0.573, -0.108}, {1.939,
0.912, -0.107}, {1.939, 1.251, -0.105}, {1.939,
1.59, -0.104}, {1.939, 1.93, -0.103}}}

• Oh yes forgot, I want to remove all curly brackets on export as well! – SPIL Apr 28 '16 at 15:29
• Can you give a small, numeric sample? – march Apr 28 '16 at 15:57
• I've just updated with a set of number. – SPIL Apr 29 '16 at 9:13

This

ListA = {
{{n1, p1, a1}, {n1, p2, a2}, {n1, p3, a3}, {n1, p4, a4}},
{{n2, p1, b1}, {n2, p2, b2}, {n2, p3, b3}, {n2, p4, b4}},
{{n3, p1, c1}, {n3, p2, c2}, {n3, p3, c3}, {n3, p4, c4}}};
Export["MathematicaData.CSV", Transpose[Map[Last, ListA, {2}]]]


gives this

a1,b1,c1,f1
a2,b2,c2,f2
a3,b3,c3,f3
a4,b4,c4,f4


in your csv file.

Note you are also missing some commas in your example, but I suspect those are just typos.

EDIT: Revised to include his requested row and column labels

collabels = Map[#[[1, 1]] &, ListA];
rowlabels = Join[{{0}}, Map[{#[[2]]} &, ListA[[1]]]];
Join[{collabels}, Transpose[Map[Last, ListA, {2}]]]}, 1]]


which produces

0, n1,n2,n3
p1,a1,b1,c1
p2,a2,b2,c2
p3,a3,b3,c3
p4,a4,b4,c4

• Thanks that works great. But the first column needs to be the "n"s and the first row the "p"s. Please can you update with this? Thanks – SPIL Apr 28 '16 at 16:11
• @SPIL. What goes in upper left corner then? A zero? – march Apr 28 '16 at 16:12
• Yes a zero or blank is fine. Thanks – SPIL Apr 29 '16 at 8:57
• @SPIL labels added. From your original wording I would never have guessed you wanted labels. In the future you might consider posting something like: Given this input in this form I'd like to get output in this form. The example can't be too complicated, but it needs to be just complicated enough that whatever anyone answers will be certain to do all that you need. – Bill Apr 29 '16 at 20:02
• Hi Bill, Thanks for your feedback. I'll take that on board for future posts. – SPIL May 3 '16 at 14:04

Update

Export["filename.csv", ArrayFlatten[{{0, {list[[1, All, 2]]}}, {List /@ list[[All, 1, 1]], list[[All, All, 3]]}}]]


OP

I'm assuming all the ns and ps are integers. In that case, do

Export["filename.csv", SparseArray[{#1, #2} -> #3 & @@@ Flatten[list, 1]]]


This works for the ns and ps in any order. If they are actually in the correct order, then Bill's solution above is fine.

Note: I might have the columns and rows switched. In that case, change {#1, #2} in the code above to {#2, #1}.

• No they are real numbers. But thanks anyway, your method could be useful in the future. – SPIL Apr 28 '16 at 16:10
• @SPIL. Try the updated post. See if it works. You might need to put Transpose@ in front of ArrayFlatten. – march Apr 28 '16 at 16:21
• That works! Thanks for your efforts. – SPIL Apr 29 '16 at 9:19