Import bird data

library(lubridate)

## 
## Attaching package: 'lubridate'

## The following object is masked from 'package:base':
## 
##     date

library(aqm)

## 
## Attaching package: 'aqm'

## The following object is masked from 'package:stats':
## 
##     dt

library(date)
library(tidyr)
library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:lubridate':
## 
##     intersect, setdiff, union

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(readxl)

## Make a vector for the names that consists of combination of month and tide
mn<-rep(month.name[c(10,11,12,1,2,3)],each=2)
td<-rep(c("Hi","Lw"),times=6)

nm<-paste(mn,td,sep="_")
nm<-c("Species",nm)

## Read the sheets

sf <- read_excel("6541.3 DPW Winter Bird Summary MuckingsStanford WharfSiteX 2018-19 V1.xlsx", 
    sheet = "Stanford Wharf", col_names = FALSE, 
    skip = 4)[,1:13]


mn <- read_excel("6541.3 DPW Winter Bird Summary MuckingsStanford WharfSiteX 2018-19 V1.xlsx", 
    sheet = "Mucking North", col_names = FALSE, 
    skip = 4)[,1:13]

ms <- read_excel("6541.3 DPW Winter Bird Summary MuckingsStanford WharfSiteX 2018-19 V1.xlsx", 
    sheet = "Mucking South", col_names = FALSE, 
    skip = 4)[,1:13]


sff <- read_excel("6541.3 DPW Winter Bird Summary MuckingsStanford WharfSiteX 2018-19 V1.xlsx", 
    sheet = "SFF excluding Shades", col_names = FALSE, 
    skip = 4)[,1:13]


### A function for stacking and separating

f<-function(x){
names(x)<-nm
x %>% pivot_longer(cols=2:13) %>% separate(name, into=c("Month","Tide")) ->x
x[is.na(x)]<-0
x
}



sf<-f(sf)
sf$Site<-"SW"
ms<-f(ms)
ms$Site<-"NM"
mn<-f(mn)
mn$Site<-"NM"
sff<-f(sff)
sff$Site<-"SF"

d<-rbind(sf,ms,mn,sff)
d %>% group_by(Site,Species,Month) %>% summarise(value=max(value)) -> d

d$Year<-2018
load("data_to_2018.rda")
data<-bind_rows(d,data)

## Warning in bind_rows_(x, .id): binding character and factor vector,
## coercing into character vector

## Warning in bind_rows_(x, .id): binding character and factor vector,
## coercing into character vector

## Warning in bind_rows_(x, .id): binding character and factor vector,
## coercing into character vector

key_sp<-c("Avocet","Bar-tailed godwit","Black-tailed godwit","Cormorant","Curlew","Dunlin","Golden plover","Grey plover","Knot","Lapwing","Mallard","Oystercatcher","Pintail","Redshank","Ringed plover","Shelduck","Teal","Turnstone","Wigeon")

data$Species<-as.character(data$Species)
data %>% filter(Species %in% key_sp) -> data
data$Species<-as.factor(data$Species)

aqm::dt(data)

data %>% group_by(Year,Month) %>% summarise(sum=sum(value)) %>% group_by(Year) %>% summarise(Month=Month[which(sum==max(sum))],Assemblage=max(sum)) -> peak_month

data$Year_Month<-paste(data$Year,data$Month)
peak_year_months<-paste(peak_month$Year,peak_month$Month)

data %>% filter(Year_Month %in% peak_year_months) %>%
group_by(Year,Species) %>% summarise(Sum=sum(value)) -> peak_species
  
peak_species %>% spread(Year,Sum,fill=0) %>% dt()

write.csv(peak_species,file="data/table1.csv",row.names = FALSE)

data %>% group_by(Year,Month,Species) %>% summarise(msum=sum(value)) %>% group_by(Year,Species) %>% summarise(Sum=max(msum)) -> species_peaks



species_peaks %>% spread(Year,Sum,fill=0) %>% dt()

write.csv(species_peaks,file="data/table2.csv",row.names=FALSE)