2
$\begingroup$

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?

Improve this question
$\endgroup$
4
  • $\begingroup$ Are all of the cell entries strings? $\endgroup$
    – DavidC
    Apr 8, 2013 at 20:44
  • $\begingroup$ No. I mentioned right after the example sheet that everything is a number (a float specifically) $\endgroup$
    – Jess The Witch
    Apr 8, 2013 at 20:50
  • $\begingroup$ Thanks. What about a, b, c? Are they "a", "b", "c" or something else? $\endgroup$
    – DavidC
    Apr 8, 2013 at 20:57
  • $\begingroup$ 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). $\endgroup$
    – Jess The Witch
    Apr 8, 2013 at 21:00

5 Answers 5

Reset to default
1
$\begingroup$ 
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}} *)
Improve this answer
$\endgroup$
1
  • $\begingroup$ Very nice and straight forward! $\endgroup$
    – Lou
    Apr 9, 2013 at 12:22
0
$\begingroup$

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 ;;]]
Improve this answer
$\endgroup$
2
  • $\begingroup$ 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. $\endgroup$
    – Jess The Witch
    Apr 8, 2013 at 22:15
  • $\begingroup$ @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)) $\endgroup$
    – Mr.Wizard
    Apr 8, 2013 at 22:27
0
$\begingroup$

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

Improve this answer
$\endgroup$
5
  • $\begingroup$ 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. $\endgroup$
    – Jess The Witch
    Apr 8, 2013 at 21:47
  • $\begingroup$ @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. $\endgroup$
    – Mr.Wizard
    Apr 8, 2013 at 21:58
  • $\begingroup$ @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. $\endgroup$
    – Jess The Witch
    Apr 8, 2013 at 22:06
  • $\begingroup$ 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. $\endgroup$
    – Jess The Witch
    Apr 8, 2013 at 22:12
  • $\begingroup$ Kyle. I corrected the order of indices issue. Thanks for pointing it out. $\endgroup$
    – DavidC
    Apr 8, 2013 at 22:14
0
$\begingroup$

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.

Improve this answer
$\endgroup$
3
  • $\begingroup$ 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}} $\endgroup$
    – Jess The Witch
    Apr 8, 2013 at 21:25
  • $\begingroup$ 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}} $\endgroup$
    – Jess The Witch
    Apr 8, 2013 at 21:31
  • $\begingroup$ @Kyle I changed it on my machine, but didn't fix the bug on the code I posted. Fixed, now. $\endgroup$
    – rcollyer
    Apr 8, 2013 at 22:31
0
$\begingroup$

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

Improve this answer
$\endgroup$

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.