Currently, I'm working on a scientific project about insects, and I've been keeping a journal of the insects I've found so far. I now understand that listing the names of every insect I have been finding with each observation was a mistake. I am not authorized to share too much information because it is private, but I will add a representative sample of my situation to the table below:
| # of sample |
insect (family) |
| 1 |
Dermestidae, Histeridae |
| 2 |
Histeridae, Dichotumius |
| 3 |
Histeriade |
| 4 |
Dermestidae, Histeridae |
| 5 |
Cleridae, Dichotumius |
| 485 |
Histeriade |
| 486 |
Dermestidae, Histeridae |
| 487 |
Dermestidae, Cleridae |
| 488 |
Histeriade |
Something like the above table. In my actual table, I have cells with 5 or 6 diferent insects. The thing is:
- How can I search for all the different values? I mean, I want to create a table that contains all the different values and how many of them are... Something like the following table:
| Insect (family) |
Count |
| Cleridae |
54 |
| Histeridae |
154 |
| Dermestidae |
34 |
(There are at least 100 different insects and some of them just appear once, so it is impossible for me to search all the different names manually.
Furthermore, I was thinking about converting my table to a long structure. Something like the following;
Instead of this:
| # of sample |
insect (family) |
| 1 |
Dermestidae, Histeridae |
| 2 |
Histeridae, Dichotumius |
| 3 |
Histeriade |
| 4 |
Dermestidae, Histeridae |
| 5 |
Cleridae, Dichotumius |
I want this:
| # of sample |
insect (family) |
| 1 |
Dermestidae |
| 1 |
Histeridae |
| 2 |
Histeridae |
| 2 |
Dichotumius |
| 3 |
Histeriade |
| 4 |
Dermestidae |
| 4 |
Histeridae |
| 5 |
Cleridae |
| 5 |
Dichotumius |
I was thinking that this arrangement should be better than the one that I have now. I hope someone can help me with this issue.
