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

Improve this question
$\endgroup$

3 Answers 3

Reset to default
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.

Improve this answer
$\endgroup$
4
  • 1
    $\begingroup$ Why not include a column for "day"? $\endgroup$
    – DavidC
    Dec 3, 2013 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, 2013 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, 2013 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, 2013 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

Improve this answer
$\endgroup$
1
  • $\begingroup$ Thank you very much for this answer! $\endgroup$
    – Wiebe
    Dec 4, 2013 at 7:01
1
$\begingroup$

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

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.