# Creating ordered triplets from an Excel sheet

I have an Excel sheet of the form:

    " "  a  b  c
1   R  S  T
2   U  V  W
3   X  Y  Z


Where every entry is a number (except the blank quotes, which is just a blank cell). These are supposed to be {x,y,z} coords but Mathematica imports them as {" ",a,b,c},{1,R,S,T},.... I get that I need to transpose them but I'm not sure how. Essentially for this example the end result should be {a,1,R},{b,1,S},{c,1,T},{a,2,U}....

This is similar but without the extra lines which is kind of the issue I'm having: Import Excel sheet into 3D array?

-
Are all of the cell entries strings? –  David Carraher Apr 8 '13 at 20:44
No. I mentioned right after the example sheet that everything is a number (a float specifically) –  KPatnode Apr 8 '13 at 20:50
Thanks. What about a, b, c? Are they "a", "b", "c" or something else? –  David Carraher Apr 8 '13 at 20:57
Those too. Everything that isn't the little blank space in the corner is a number. I just used the letters to make it easier to keep track of when I rearranged them. There are no strings anywhere (except for that blank space, which needs to get ignored). –  KPatnode Apr 8 '13 at 21:00

s = {{" ", a, b, c},
{1, R, S, T},
{2, U, V, W},
{3, X, Y, Z}};


Traverse the rows and extract the triplets.

Table[{s[[1, j]], s[[i, 1]], s[[i, j]]},
{i, 2, Length[s]}, {j, 2, Length[First@s]}] // Flatten[#, 1] &

(* {{a, 1, R}, {b, 1, S}, {c, 1, T}, {a, 2, U}, {b, 2, V}, {c, 2, W}, *)
(*  {a, 3, X}, {b, 3, Y}, {c, 3, Z}} *)

-
Very nice and straight forward! –  Lou Apr 9 '13 at 12:22

This is not likely to be highly efficient, but I think it is pretty easy to follow, assuming you are familiar with Mathematica pattern and Part syntax:

in = {{" ","a","b","c"}, {1,"R","S","T"}, {2,"U","V","W"}, {3,"X","Y","Z"}};

f[x_][n_, {r_, c_}] := {x[[1, c]], x[[r, 1]], n}

Join @@ MapIndexed[f[in], in, {2}][[2 ;;, 2 ;;]]

{{"a", 1, "R"}, {"b", 1, "S"}, {"c", 1, "T"}, {"a", 2, "U"}, {"b", 2, "V"},
{"c", 2, "W"}, {"a", 3, "X"}, {"b", 3, "Y"}, {"c", 3, "Z"}}


Or as a one-liner:

Join @@ MapIndexed[{in[[1, #2[[2]]]], in[[#2[[1]], 1]], #} &, in, {2}][[2 ;;, 2 ;;]]

-
As far as I can tell both the expanded and the one-liner work. Just ran it on the test set and then on my actual data and both times it looks right. Everything in the right place and nothing missing. –  KPatnode Apr 8 '13 at 22:15
@Kyle Great. :-) Let me know if you need an explanation of the code. The basic idea is to use the coordinates of each element, provided by MapIndexed, to then extract the matching column and row headers. This is basically what the other answers do too, but I think my way is cleaner. If you are not familiar with function definitions of the form f[x_][n_, {r_, c_}] :=  the one-liner may be easier to understand; they do the same thing. (Regarding the definition you may read (1) and (2)) –  Mr.Wizard Apr 8 '13 at 22:27

Here's another idea.

rawData = {{" ", "a", "b", "c"}, {1, "R", "S", "T"}, {2, "U", "V", "W"}, {3, "X", "Y", "Z"}}


Separate the data from the spreadsheet headers

data = Rest[rawData] /. {_?NumberQ, tail__} :> {tail}


{{"R", "S", "T"}, {"U", "V", "W"}, {"X", "Y", "Z"}}
{"a", "b", "c"}
{1, 2, 3}

Replace the information as required.

Most[ArrayRules[data]] /. ({c_, r_} -> d_) :> {colHeaders[[r]], rowHeaders[[c]], d}


{{"a", 1, "R"}, {"b", 1, "S"}, {"c", 1, "T"}, {"a", 2, "U"}, {"b", 2, "V"}, {"c", 2, "W"}, {"a", 3, "X"}, {"b", 3, "Y"}, {"c", 3, "Z"}}

-
Same problem as rcollyer's. The end result is supposed to be: {a,1,R},{b,1,S},{c,1,T},{a,2,U}... BUT Swapping a and b in your last line such that Most[ArrayRules[data]] /. ({a_, b_} -> d_) :> {colHeaders[[b]], rowHeaders[[a]], d} Does work! Thanks! Make that change and I'll mark it accepted. –  KPatnode Apr 8 '13 at 21:47
@Kyle as I've said to many new users, I recommend waiting 24 hours before you Accept an answer to give everyone a chance to read and respond. –  Mr.Wizard Apr 8 '13 at 21:58
@Mr.Wizard Thanks for the advice. As it turns out, I spoke too soon anyway. This code ignores any point that has a 0 as one of the coords. –  KPatnode Apr 8 '13 at 22:06
I meant if any of those symbols corresponds to a 0 (as in a,b,c,1,2,3,R,S,T,U), it will ignore all points in that column/row. So if a was 0, then any point that included a would be ignored. –  KPatnode Apr 8 '13 at 22:12
Kyle. I corrected the order of indices issue. Thanks for pointing it out. –  David Carraher Apr 8 '13 at 22:14

If the characters are loaded in as strings, then this works:

{M[[1, #2]], M[[#1, 1]], M[[##]]} & @@@ Select[Position[M, _String], #[[1]] != 1 &]


where M is the matrix. But, if the characters are symbols, then a little different processing is required

{M[[1, #2]], M[[#1, 1]], M[[##]]} & @@@ Select[
Position[M, _Symbol, Heads -> False], #[[1]] != 1 &]


Note, that I had to add the option Heads -> False to Position, otherwise it would capture List, also.

Edit: as it appears I misread the post, and R, etc. are numeric, this is simple to accomplish, also.

{M[[1, #2]], M[[#1, 1]], M[[##]]} & @@@ Select[
Position[M, _?NumericQ], #[[2]] != 1 &]


All return

{{a, 1, R}, {b, 1, S}, {c, 1, T},
{a, 2, U}, {b, 2, V}, {c, 2, W},
{a, 3, X}, {b, 3, Y}, {c, 3, Z}}


with substitutions as appropriate.

-
Not quite. When I put it in (using numbers in Mathematica but for consistency substituting letters here). code{{a,1,R},{a,2,S},{a,3,T}, code {b,1,U},{b,2,V},{b,3,W}, code {c,1,X},{c,2,Y},{c,3,Z}} –  KPatnode Apr 8 '13 at 21:25
Double posting for clarity's sake since I couldn't get the mini-markdown down in the 5 minutes they gave me: {{a,1,R},{a,2,S},{a,3,T}, {b,1,U},{b,2,V},{b,3,W}, {c,1,X},{c,2,Y},{c,3,Z}} –  KPatnode Apr 8 '13 at 21:31
@Kyle I changed it on my machine, but didn't fix the bug on the code I posted. Fixed, now. –  rcollyer Apr 8 '13 at 22:31

For fun

data = {{" ", "a", "b", "c"}, {1, "R", "S", "T"}, {2, "U", "V",
"W"}, {3, "X", "Y", "Z"}}
data2 = {{" ", "COLLUMN1", "COLLUMN2", "COLLUMN3"}, {"ROW1", "R", "S",
"T"}, {"ROW2" , "U", "V", "W"}, {"ROW3", "X", "Y", "Z"}}

cheader = data[[1, 2 ;; All]];
rheader = data[[2 ;; All, 1]];
Flatten[
data[[1 + #, 2 ;; All]]}] & /@ Range[rheaderlenght], 1]
]


f[data] and f[data2] gives:

{{"a", 1, "R"}, {"b", 1, "S"}, {"c", 1, "T"}, {"a", 2, "U"}, {"b", 2, "V"}, {"c", 2, "W"}, {"a", 3, "X"}, {"b", 3, "Y"}, {"c", 3, "Z"}}

and

{{"COLLUMN1", "ROW1", "R"}, {"COLLUMN2", "ROW1", "S"}, {"COLLUMN3", "ROW1", "T"}, {"COLLUMN1", "ROW2", "U"}, {"COLLUMN2", "ROW2", "V"}, {"COLLUMN3", "ROW2", "W"}, {"COLLUMN1", "ROW3", "X"}, {"COLLUMN2", "ROW3", "Y"}, {"COLLUMN3", "ROW3", "Z"}}

-