6
$\begingroup$

I have a Google spreadsheet where not all of the cells are filled. It looks like this: enter image description here

I want to import it as a list of lists like:

$\left\{ \left\{34,1,3 \right\},\left\{45,1,4 \right\},\left\{45,2,6 \right\}, ... \right\}$

so the first column is x, the first row is y and it is z in the cells.

Ho can I do that?

P.S.1. Here the link to the sample spreadsheet.

$\endgroup$
  • $\begingroup$ Can you link to a sample (publicly accessible!) spreadsheet for testing purposes? $\endgroup$ – J. M. will be back soon Aug 17 '17 at 15:07
  • $\begingroup$ @J.M. I added the link to the post $\endgroup$ – Foad Aug 17 '17 at 15:10
7
$\begingroup$
data = Import["testformathematica.xlsx"][[1]]

x = data[[2 ;;, 1]];
y = data[[1, 2 ;;]];
z = data[[2 ;;, 2 ;;]];

Table[
   {x[[i]], y[[j]], z[[i, j]]},
   {i, Range[Length[x]]}, {j, Range[Length[y]]}
]~Flatten~1~DeleteCases~{_, _, ""}

(* Out: 
{{34., 1., 3.}, {34., 3., 3.}, {45., 1., 4.}, {45., 2., 6.}, {56., 2., 4.}, 
 {56., 4., 8.}, {76., 3., 6.}, {34., 1., 5.}, {67., 2., 9.}, {67., 4., 7.}}
*)
$\endgroup$
  • $\begingroup$ for some reason your code works perfectly for the test spreadsheet but not my actual data. I'm trying to see what is the problem and come back and select this as the correct answer. $\endgroup$ – Foad Aug 17 '17 at 15:56
  • 1
    $\begingroup$ @Foad Could you share the actual data? Note that, if the actual data is not an excel file, you might not need the [[1]] selector when importing. $\endgroup$ – MarcoB Aug 17 '17 at 15:57
  • 1
    $\begingroup$ It is confidential data so I'm not allowed to do so. but the problem is solved. I exported the current sheet as CSV and then dragged and dropped it into a notebook. and problem was solved $\endgroup$ – Foad Aug 17 '17 at 16:00
  • $\begingroup$ @Foad OK, glad to hear that. $\endgroup$ – MarcoB Aug 17 '17 at 16:01
2
$\begingroup$
tfm = Import["testformathematica.xlsx"][[1]]

Thread/@Replace[Thread[{tfm[[1]], #}]&/@tfm[[2 ;;]], {{_, ""}:>(## &[]), {"", x_}:>x}, ∞]

{{{34., 1., 3.}, {34., 3., 3.}}, {{45., 1., 2.}, {45., 4., 6.}}, {{56., 2., 4.}, {56., 4., 8.}}, {{76., 3.}, {76., 6.}}, {{34., 1.}, {34., 5.}}, {{67., 2., 4.}, {67., 9., 7.}}}

$\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.