# Organise data in table

I have a large amount of data available (in excel), which is not very organised. I can do this by copy paste, but I am trying to do this using mathematica. Hopefully someone can help me.

I imported the data, and it is now organised in a matrix that looks like this:

test = {{{dog, 5}, {"", ""}, {"", ""}}, {{cat, 7}, {dog, 2}, {"", ""}}, {{dog,3},{horse, 2}, {cat, 1}}, {{horse, 7}, {cat, 2}, {dog, 1}}};
TableForm[test]


So on day one, they counted 5 dogs, but no other animals, on day 2 they counted 7 cats, and 2 dogs, day 3: one horse, 2 cats etc.. To analyse this data I want to create a table that gives for each day, the amount of animals but then organised in columns per animal (even if the animal is not present at that day) like this:

result = {{"dog", "cat", "horse"} , {5, "", ""}, {2, 7, ""}, {3, 1, 2}, {1, 2, 7}};
TableForm[result]


I tried somethings using pick and sort but could not figure out how to do this. Of course this example is very simple, but the real data are for a 978x15 table (and does not involve animals ;) )

Perhaps like this:

test = {{{dog, 5}, {"", ""}, {"", ""}}, {{cat, 7}, {dog, 2}, {"",
""}}, {{dog, 3}, {horse, 2}, {cat, 1}}, {{horse, 7}, {cat,
2}, {dog, 1}}};

rules = Map[#[[1]] -> #[[2]] &, test, {2}];
types = DeleteDuplicates[Cases[rules, _Symbol, Infinity]];
fill = # -> "" & /@ types;
tab = Transpose[# /. rules /. fill & /@ types];
Grid[Prepend[tab, types]]


The fill part could be left out if your list were more structured, but then more straightforward solutions would present themselves anyway.

• Why not include a column for "day"? Dec 3, 2013 at 22:15
• Thanks for your help! Could not have found that myself I think. Day could be included, but is not necessary for the purpose of the data Dec 4, 2013 at 7:00
• @user9022 you are welcome. If you go through the code line by line and look at the intermediate results, it should become less cryptic. Dec 4, 2013 at 7:29
• @DavidCarraher I left that out because it would further obfuscate the code (plus it would call for an additional Table,MapIndexed or Range etc.). Dec 4, 2013 at 7:47

Here's a one-liner....

Table[First[Cases[test[[i]], {animal, ct_} :> ct] /. {} -> {0}],
{i, Length@test}, {animal, {dog, cat, horse}}]~Prepend~{dog, cat, horse} // Grid


• Thank you very much for this answer! Dec 4, 2013 at 7:01

Using Lookup:

test = {{{dog, 5}, {"", ""}, {"", ""}}, {{cat, 7}, {dog, 2}, {"",
""}}, {{dog, 3}, {horse, 2}, {cat, 1}}, {{horse, 7}, {cat,
2}, {dog, 1}}};

rules = Rule @@@ # & /@ test /. HoldPattern["" -> ""] :> Nothing

Prepend[Transpose@(Lookup[rules, #, 0] & /@ {dog, cat, horse})
, ToString /@ {dog, cat, horse}
] // Grid


$$\begin{array}{ccc} \text{dog} & \text{cat} & \text{horse} \\ 5 & 0 & 0 \\ 2 & 7 & 0 \\ 3 & 1 & 2 \\ 1 & 2 & 7 \\ \end{array}$$