merge info from 2 columns and form 2 new ones with half the rows r

Question

I could not find the solution to my problem anywhere in the forum, so I think is not a duplicate.

I have a dataset as it follows:

>   couple bird day calltype Number
1       E   m1  d1    tot_s      5
2       E   f1  d1    tot_s      2
3       E   m1  d3    tot_s      6
4       E   f1  d3    tot_s      5
5       E   m1  d5    tot_s      3
6       E   f1  d5    tot_s      6
7       E   m1  d7    tot_s      1
8       E   f1  d7    tot_s      7
9       F   m1  d1    tot_s      9
10      F   f1  d1    tot_s      5
11      F   m1  d3    tot_s     10
12      F   f1  d3    tot_s      8
13      F   m1  d5    tot_s      6
14      F   f1  d5    tot_s      7
15      F   m1  d7    tot_s      4
16      F   f1  d7    tot_s      5

And I would like to transform it to:

couple1 day1 calltype1 number_m1 number_f1
1       E   d1     tot_s         1         2
2       E   d3     tot_s         5         7
3       E   d5     tot_s         4         7
4       E   d7     tot_s         4         4
5       F   d1     tot_s         3         6
6       F   d3     tot_s         3         2
7       F   d5     tot_s         8         8
8       F   d7     tot_s         2         2

Reproducible example of the first daset:

couple<- rep(c("E","F"), each=8)
bird<- rep(c("m1","f1"), 4)
day <- rep(rep(c("d1","d3", "d5", "d7"), each=2),2)
calltype <- rep("tot_s", 16)
Number <- as.numeric(sample(1:10, replace = TRUE, 16))
dat<- data.frame(cbind(couple, bird, day, calltype, Number))

And also the second ones:

couple1<- rep(c("E","F"), each=4)
day1 <- rep(c("d1","d3", "d5", "d7"),2)
calltype1 <- rep("tot_s", 8)
number_m1 <- c(dat$Number[1], dat$Number[3], dat$Number[5], dat$Number[7], dat$Number[9], dat$Number[11], dat$Number[13], dat$Number[15] ) 
number_f1 <- c(dat$Number[2], dat$Number[4], dat$Number[6], dat$Number[8], dat$Number[10], dat$Number[12], dat$Number[14], dat$Number[16] )
dat1<- data.frame(cbind(couple1, day1, calltype1, number_m1, number_f1))

The strategy it could work is to merge the 2 columns and then somehow reshape, however I get stuck even with this simple task,

dat$bird_number<- cbind(dat$bird, as.numeric(levels(dat$Number))[dat$Number])

I guess because when I make the dataset all the columns are factors and I need the second one to be numeric...I cannot transform it not even following the directions given in the following link.

How to convert a factor to an integer\numeric without a loss of information?

How to make it numeric? how to then re-shape my dataset?

I tried several strategies and now seek for your help, looking forward to learn!

Have fun

Answer 1

This is actually a straightforward "long-to-wide" conversion. Here it the approach in base R:

reshape(dat, direction = "wide", idvar = c("couple", "day", "calltype"), 
    timevar = "bird")
##    couple day calltype Number.m1 Number.f1
## 1       E  d1    tot_s         9         6
## 3       E  d3    tot_s         8         2
## 5       E  d5    tot_s         4         9
## 7       E  d7    tot_s         2         1
## 9       F  d1    tot_s         1         3
## 11      F  d3    tot_s         9        10
## 13      F  d5    tot_s         3         9
## 15      F  d7    tot_s         2         6

Or, with dcast from the "data.table" package:

library(data.table)
dcast(as.data.table(dat), ... ~ bird, value.var = "Number")
##    couple day calltype f1 m1
## 1:      E  d1    tot_s  6  9
## 2:      E  d3    tot_s  2  8
## 3:      E  d5    tot_s  9  4
## 4:      E  d7    tot_s  1  2
## 5:      F  d1    tot_s  3  1
## 6:      F  d3    tot_s 10  9
## 7:      F  d5    tot_s  9  3
## 8:      F  d7    tot_s  6  2

Or, with spread from "tidyr":

spread(dat, bird, Number)
##   couple day calltype f1 m1
## 1      E  d1    tot_s  6  9
## 2      E  d3    tot_s  2  8
## 3      E  d5    tot_s  9  4
## 4      E  d7    tot_s  1  2
## 5      F  d1    tot_s  3  1
## 6      F  d3    tot_s 10  9
## 7      F  d5    tot_s  9  3
## 8      F  d7    tot_s  6  2