Create a matrix of the number of times each species interacts with (eats) each forage species

[niamhailbhe]

I want to create a matrix in R that counts the number of times each species interacts with the different forages.

Species	Sex	Behaviour	Forage
B._lucorum_agg.	Worker	Foraging	T._pratense
B._pascuorum	Worker	Foraging	T._pratense
B._muscorum	Worker	Foraging	T._pratense
B._muscorum	Worker	Foraging	T._pratense
B._jonellus	Worker	Foraging	C.capillaris
B._muscorum	Worker	Foraging	C.capillaris
B._pascuorum	Worker	Foraging	L.corniculatus

The end goal is to create a table like

	T._pratense	C.capillaris	L.corniculatus
B._lucorum_agg.	1	0	0
B._pascuorum	1	0	1
B._muscorum	2	1	0
B._jonellus	0	1	0

[Yarnabrina]

Welcome to the community!

You can do something like this:

dataset <- data.frame(Species = c("B._lucorum_agg.", "B._pascuorum", "B._muscorum", "B._muscorum", "B._jonellus", "B._muscorum", "B._pascuorum"),
                      Sex = c("Worker", "Worker", "Worker", "Worker", "Worker", "Worker", "Worker"),
                      Behaviour = c("Foraging", "Foraging", "Foraging", "Foraging", "Foraging", "Foraging", "Foraging"),
                      Forage = c("T._pratense", "T._pratense", "T._pratense", "T._pratense", "C.capillaris", "C.capillaris", "L.corniculatus"),
                      stringsAsFactors = FALSE)

with(dataset, by(Forage, Species, table))
#> Species: B._jonellus
#> 
#>   C.capillaris L.corniculatus    T._pratense 
#>              1              0              0 
#> -------------------------------------------------------- 
#> Species: B._lucorum_agg.
#> 
#>   C.capillaris L.corniculatus    T._pratense 
#>              0              0              1 
#> -------------------------------------------------------- 
#> Species: B._muscorum
#> 
#>   C.capillaris L.corniculatus    T._pratense 
#>              1              0              2 
#> -------------------------------------------------------- 
#> Species: B._pascuorum
#> 
#>   C.capillaris L.corniculatus    T._pratense 
#>              0              1              1

^{Created on 2019-07-08 by the reprex package (v0.3.0)}

If you want to use it further, then you can convert it to a matrix using do.call(rbind, r), where r is the object where you'll store the result of by.

Also, please check the expected output you have provided. The 3rd row seems to be wrong, as T._pratense occurs twice with B._muscorum.

Hope this helps.

[niamhailbhe]

Sorry, my fault. This above is a small sample of my data. Each line is a separate observation so I have multiple observations of bees foraging on the same plant species. The goal is to count how many time each interaction occurs.

i.e I will have multiple times that B._pascuorum forages on T._pratense etc

[niamhailbhe]

Yes it does work, thank you. However, there is 250+ observations - will I have to hand write each observation
Total r novice

[andresrcs]

Yarnabrina is providing you an example using sample data, you don't have to manually type observations, you just have to replace the name of the sample data (i.e. dataset) for the name of your real data.