# Make a list of triplets from a table with empty cells

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

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?

• Can you link to a sample (publicly accessible!) spreadsheet for testing purposes? – J. M. is away Aug 17 '17 at 15:07

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.}}
*)

• 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. – Foad Aug 17 '17 at 15:56
• @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. – MarcoB Aug 17 '17 at 15:57
• 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 – Foad Aug 17 '17 at 16:00
• @Foad OK, glad to hear that. – MarcoB Aug 17 '17 at 16:01
tfm = Import["testformathematica.xlsx"][[1]]