2
$\begingroup$

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 ;) )

$\endgroup$
4
$\begingroup$

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]]

enter image description here

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

$\endgroup$
  • 1
    $\begingroup$ Why not include a column for "day"? $\endgroup$ – DavidC Dec 3 '13 at 22:15
  • $\begingroup$ 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 $\endgroup$ – Wiebe Dec 4 '13 at 7:00
  • $\begingroup$ @user9022 you are welcome. If you go through the code line by line and look at the intermediate results, it should become less cryptic. $\endgroup$ – Yves Klett Dec 4 '13 at 7:29
  • $\begingroup$ @DavidCarraher I left that out because it would further obfuscate the code (plus it would call for an additional Table,MapIndexed or Range etc.). $\endgroup$ – Yves Klett Dec 4 '13 at 7:47
3
$\begingroup$

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

enter image description here

$\endgroup$
  • $\begingroup$ Thank you very much for this answer! $\endgroup$ – Wiebe Dec 4 '13 at 7:01

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.