2

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?

Community
  • 1
Jess The Witch
  • 23
  • 2
  • Are all of the cell entries strings? – DavidC Apr 08 '13 at 20:44
  • No. I mentioned right after the example sheet that everything is a number (a float specifically) – Jess The Witch Apr 08 '13 at 20:50
  • Thanks. What about a, b, c? Are they "a", "b", "c" or something else? – DavidC Apr 08 '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). – Jess The Witch Apr 08 '13 at 21:00

5 Answers5

1
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}} *)
BoLe
  • 5,819
  • 15
  • 33
0

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.

rcollyer
  • 33,976
  • 7
  • 92
  • 191
  • 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}} – Jess The Witch Apr 08 '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}} – Jess The Witch Apr 08 '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 08 '13 at 22:31
0

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}
colHeaders = Rest@rawData[[1]]
rowHeaders = Rest@Transpose[rawData][[1]]

{{"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"}}

DavidC
  • 16,724
  • 1
  • 42
  • 94
  • 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.

     – Jess The Witch Apr 08 '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 08 '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.

     – Jess The Witch Apr 08 '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. – Jess The Witch Apr 08 '13 at 22:12
  • Kyle. I corrected the order of indices issue. Thanks for pointing it out. – DavidC Apr 08 '13 at 22:14
0

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 ;;]]
Mr.Wizard
  • 271,378
  • 34
  • 587
  • 1,371
  • 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. – Jess The Witch Apr 08 '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 08 '13 at 22:27
0

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"}}


f[data_] := Module[{cheader, rheader, rheaderlenght},
  cheader = data[[1, 2 ;; All]];
  rheader = data[[2 ;; All, 1]];
  rheaderlenght = rheader // Length;
  Flatten[
   Transpose[{ cheader, PadLeft[{}, rheaderlenght, rheader[[#]]],
       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"}}

Lou
  • 3,822
  • 23
  • 26