---
title: "2020 Beetleary Data Clean-Up"
author: "Phoebe Cook"
date: "20210414"
output: 
  html_document:
    toc: true
    toc_float: true
    theme: "lumen"
---

Date rendered: `r date()`

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
knitr::opts_knit$set(root.dir = "~/Dropbox/Experiment Beetles 2020/2020 Data Clean Up/")
```

#Objectives
The goal of this project is to find errors in the 2020 Beetleary Data after all the data has been entered into a File Maker Pro database. 

This code was directly modified from Robin Costello and Phoebe Cook's "2019 Beetleary Clean-up" script. 

Modifications were primarily made for specifics of the 2020 experimental set up (different dates, beetle IDs, etc), but also to the definitions of MatingPartners.

#Set-Up and Data
Run each chunk of this code separately. After running each chunk, look at the error csv, investigate the errors, and take notes on what should be done in Excel (it helps to sort the sheet by ScanID, then Condo, and then Time before investigating, to minimize flipping back and forth, and then sort by error type at the end of the process to make changes more organized). Make the changes in the "2020_error-correcting_script.Rmd" script. Then, upload the new cleaned up dataset here and run the next chunk. 

Loop through the body of this script three times, as indicated in the document outline headers. 

```{r}
# Note that you must first set your working directory.

#libraries
library(tidyverse)

#function that excludes
`%sans%` <- Negate(`%in%`)

#function that takes end of string
substrRight <- function(x, n){
  substr(x, nchar(x)-n+1, nchar(x))
}

#upload dataset
# surveys<-read.csv("all_raw_behavioural_data.csv")
# surveys<-read.csv("surveys_2020_edit_01_20210621_.csv")
# surveys<-read.csv("surveys_2020_edit_02_20210621_.csv")
# surveys<-read.csv("surveys_2020_edit_03_20210622_.csv")
# surveys<-read.csv("surveys_2020_edit_04_20210622_.csv")
# surveys<-read.csv("surveys_2020_edit_05_20210622_.csv")
# surveys<-read.csv("surveys_2020_edit_08_20210622_.csv")
# surveys<-read.csv("surveys_2020_edit_13_20210622_.csv")
# surveys<-read.csv("surveys_2020_edit_16_20210624_.csv")
# surveys<-read.csv("surveys_2020_edit_20_20210624_.csv")
# surveys<-read.csv("surveys_2020_edit_24_20210628_.csv")
# surveys<-read.csv("surveys_2020_edit_27_20210628_.csv")
# surveys<-read.csv("surveys_2020_edit_31_20210629_.csv")
# surveys<-read.csv("surveys_2020_edit_33_20210629_.csv")
# surveys<-read.csv("surveys_2020_edit_38_20210702_.csv")
# surveys<-read.csv("surveys_2020_edit_41_20210702_.csv")
# surveys<-read.csv("surveys_2020_edit_45_20210702_.csv")
# surveys<-read.csv("surveys_2020_edit_47_20210702_.csv")
# surveys<-read.csv("surveys_2020_edit_49_20210706_.csv")
# surveys<-read.csv("surveys_2020_edit_54_20210707_.csv")
# surveys<-read.csv("surveys_2020_edit_60_20210708_.csv") 
# surveys<-read.csv("surveys_2020_edit_61_20210708_.csv") 
# surveys<-read.csv("surveys_2020_edit_62_20210708_.csv") 
# surveys<-read.csv("surveys_2020_edit_67_20210709_.csv") 
# surveys<-read.csv("surveys_2020_edit_69_20210715_.csv") 
# surveys<-read.csv("surveys_2020_edit_71_20210715_.csv") 
# surveys<-read.csv("surveys_2020_edit_72_20210715_.csv") 
# surveys<-read.csv("surveys_2020_edit_73_20210715_.csv") 
# surveys<-read.csv("surveys_2020_edit_74_20210715_.csv") 
# surveys<-read.csv("surveys_2020_edit_75_20210715_.csv") 
# surveys<-read.csv("surveys_2020_edit_79_20210715_.csv") 
# surveys<-read.csv("20210715_OffMountainBeetleBase4.0_EditedOnce.csv")
# surveys<-read.csv("surveys_2020_edit2_01_20210715_.csv")
# surveys<-read.csv("surveys_2020_edit2_02_20210715_.csv")
# surveys<-read.csv("surveys_2020_edit2_03_20210719_.csv")
# surveys<-read.csv("surveys_2020_edit2_04_20210719_.csv")
# surveys<-read.csv("surveys_2020_edit2_05_20210719_.csv")
# surveys<-read.csv("surveys_2020_edit2_06_20210719_.csv")
# surveys<-read.csv("surveys_2020_edit2_08_20210719_.csv")
# surveys<-read.csv("surveys_2020_edit2_09_20210719_.csv")
# surveys<-read.csv("surveys_2020_edit2_10_20210719_.csv")
# surveys<-read.csv("20210719_OffMountainBeetleBase4.0_EditedTwice.csv")
surveys<-read.csv("20210726_OffMountainBeetleBase4.0_EditedTwiceAndNotes.csv")

# create objects for ease of coding
Condos <- c("1A", "1B", "2A", "2B", "3A", "3B", "4A", "4B", "5A", "5B", "6A", "6B")
# all 12 condos were used in 2020

Grid_Cells <- c("AS", "US", "M", "W", "LEG", "BACK", "A01", "A02", "A03", "A04", "A05", "A06", "A07", "A08", "A09", "A10", "A11", "A12", "A13", "A14", "A15", "A16", "A17", "A18", "B01", "B02", "B03", "B04", "B05", "B06", "B07", "B08", "B09", "B10", "B11", "B12", "B13", "B14", "B15", "B16", "B17", "B18", "C01", "C02", "C03", "C04", "C05", "C06", "C07", "C08", "C09", "C10", "C11", "C12", "C13", "C14", "C15", "C16", "C17", "C18")

Dates_Started <- c(20200803:20200823)
Dates_Surveyed <- c(Dates_Started, 20200824) 

Location <- c("T", "B", "E", "I", "C", "")

UKs <- c("UK", "UKM", "UKF")

Behaviors <- c("S", "M", "F", "L", "OS", "CRT", "COP", "G", "MCRT", "FCRT", "FG", "MG", "FCOP", "MCOP")

PartnerBehaviors <- c("CRT", "COP", "G", "MCRT", "FCRT", "FG", "MG", "FCOP", "MCOP") # added in 2020

MatingBehaviors <- c("CRT", "COP", "G") # changed in 2020


EvenBrackets <- c("A01", "A03", "A05", "A07", "A09", "A11", "A13", "A15", "A17", "C02", "C04", "C06", "C08", "C10", "C12", "C14", "C16", "C18")

ClumpedBrackets <- c("A01", "A02", "A09", "A10", "A17", "A18", "B01", "B02", "B09", "B10", "B17", "B18", "C01", "C02", "C09", "C10", "C17", "C18") 
# note that all condos were clumped in 2020

MaleNumbers <- c(2, 4)
FemaleNumbers <- c(3, 5)

RecordNumbers_2018 <- c(0:44460, 54883:54887)
RecordNumbers_2019 <- c(44468:54882, 54888:55006)
```


# Remove Past data
The edit .csvs are already trimmed, but new uploads from FileMaker should have past records removed so that we are not re-checking and changing the finalized version of that dataset. 
```{r}
surveys %>%
  filter(RecordNumber %sans% RecordNumbers_2018) %>%  
  filter(RecordNumber %sans% RecordNumbers_2019) %>%
  droplevels() -> surveys_2020
```
# ^ ^ ^ ^ ^ ^ RUN ^ ^ ^ ^ ^ ^
Run all chunks above here up every time to set up, then skip down to the chunk you need. 

#Number of Records
This code will create a table that shows the number of records in each condo during each scan. The number of records should not differ dramatically among condos and among scans. All condos should have at least one record for every scan. 

Use this table to also ensure:
+All the Trial IDs are represented
+In each Trial ID, all populations represented

Common Erros
+Records entered under the wrong condo
+Records entered under the wrong date
+Other obvious typos, date or condo entered incorrectly

```{r}
surveys_2020 %>%
  #count up the number of records in each condo for each scan
  count(ScanID, Condo) %>%
  #change the long format into a wide format for ease of viewing
  spread(Condo, n) -> NumberofRecordsTable

write.csv(NumberofRecordsTable, file=paste("NumberofRecordsTable", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Condo
Every entry needs a condo. Condo must be either 1A, 1B, 2A, 2B, 3A, 3B, 4A, 4B, 5A, 5B, 6A, 6B
```{r}
#find all condos that are na and do not match one of the possible options
surveys_2020 %>%
  filter(Condo %sans% Condos) -> CondoErrors

write.csv(CondoErrors, file=paste("CondoErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Date
Date should be entered as 8 numbers in the format YYYMMDD, within the correct ranges
Note from PAC: This was written as though Date.Started was character, but it's int. Significantly rewrote this whole chunk. 
```{r}
#Date.Started
#find all dates that are na
surveys_2020 %>%
  filter(is.na(Date.Started)) -> Date.StartedIsNA

#find typos by converting date to character and back to number
surveys_2020 %>%
  mutate(Date.Started = as.character(Date.Started)) %>%
  mutate(Date.Started = as.numeric(Date.Started)) %>%
  filter(is.na(Date.Started)) -> Date.StartedTypos

#find all dates that do not have 8 numbers
surveys_2020 %>%
  mutate(Date.Started = as.character(Date.Started)) %>%
  filter(str_length(Date.Started) != 8) %>%
  mutate(Date.Started = as.numeric(Date.Started)) -> Date.StartedWrongFormat

#find all dates that are not in the acceptable dates (for starting surveys)
surveys_2020 %>%
  filter(Date.Started %sans% Dates_Started) -> Date.StartedWrong

#combine date errors into one dataset
Date.StartedErrors <- bind_rows(Date.StartedIsNA, Date.StartedTypos, Date.StartedWrongFormat, Date.StartedWrong)
Date.StartedErrors <- distinct(Date.StartedErrors)
write.csv(Date.StartedErrors, file=paste("Date.StartedErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)

#Date_Surveys
#find all dates that are na
surveys_2020 %>%
  filter(is.na(Date_Surveys)) -> Date_SurveysIsNA

#find typos by converting date to character and back to number
surveys_2020 %>%
  mutate(Date_Surveys = as.character(Date_Surveys)) %>%
  mutate(Date_Surveys = as.numeric(Date_Surveys)) %>%
  filter(is.na(Date_Surveys)) -> Date_SurveysTypos

#find all dates that do not have 8 numbers
surveys_2020 %>%
  mutate(Date_Surveys = as.character(Date_Surveys)) %>%
  filter(str_length(Date_Surveys) != 8) %>%
  mutate(Date_Surveys = as.numeric(Date_Surveys)) -> Date_SurveysWrongFormat

#find all dates that are not in the acceptable dates (for conducting surveys)
surveys_2020 %>%
  filter(Date_Surveys %sans% Dates_Surveyed) -> Date_SurveysWrong

#combine date errors into one dataset
Date_SurveysErrors <- bind_rows(Date_SurveysIsNA, Date_SurveysTypos, Date_SurveysWrongFormat, Date_SurveysWrong)
Date_SurveysErrors <- distinct(Date_SurveysErrors)
write.csv(Date_SurveysErrors, file=paste("Date_SurveysErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

There should be 21 unique Start Dates.
```{r}
#count up the number of Dates. Make sure it is 21.
nlevels(as.factor(surveys_2020$Date.Started))
#if there are more or less than 21 levels, look at what the levels are
levels(as.factor(surveys_2020$Date.Started))
```

#Time
Time should be entered as 4 numbers in military time
```{r}
#find all times that are blank
surveys_2020 %>%
  filter(Time == "") %>%
  mutate(Time = as.character(Time)) -> TimeIsNA

#find typos and NAs by converting time to number 
surveys_2020 %>%
  mutate(Time = as.numeric(Time)) %>%
  filter(is.na(Time)) %>%
  #change back to character, so that can merge all time errors together later
  mutate(Time = as.character(Time)) -> TimeTypos

#find all times that do not have 4 numbers
surveys_2020 %>%
  filter(str_length(Time) != 4) %>%
  mutate(Time=as.character(Time)) ->  TimeIsNot4
# We decided not to include these as real errors after looking at a histogram--all fall between 600-959, and 0-200

#find all times that are not between 0000 and 2400
surveys_2020 %>%
  mutate(Time = as.numeric(Time)) %>%
  filter(Time <0 | Time >2400)  %>%
  mutate(Time=as.character(Time)) -> TimeOutofRange

#find all times where the minute is greater than or equal to 60
surveys_2020 %>%
  mutate(Time=as.character(Time)) %>%
  mutate(Min=substrRight(Time,2)) %>%
  mutate(Min=as.integer(Min)) %>%
  filter(Min>59) %>%
  select(-Min) %>%
  mutate(Time=as.character(Time)) -> MinutesOutofRange

#combine time errors into one dataset
TimeErrors <- bind_rows(TimeIsNA, TimeTypos, TimeOutofRange, MinutesOutofRange) # not including "TimeIsNot4" error
TimeErrors <- distinct(TimeErrors)
write.csv(TimeErrors, file=paste("TimeErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)

#look for other weird times
surveys_2020$Time <- as.numeric(as.character(surveys_2020$Time))
str(surveys_2020)
hist(surveys_2020$Time, xlim  = c(0,2359), breaks = 1000)
# added to the TimeErrors sheet by hand
# in the future, don't need to do this; they will get caught by the Trial ID section below
```

#Midnight Times
When the clock struck midnight, the actual date should change to the next date.
```{r}
#run this code AFTER fix times
#find records that did not switch date after midnight
surveys_2020 %>%
  #convert time to numeric
  mutate(Time = as.character(Time)) %>%
  mutate(Time = as.numeric(Time)) %>%
  #find times between midnight and 2am
  filter(Time >= 0000 & Time < 0200) %>%
  #find records where date did not change after midnight
  filter(Date.Started == Date_Surveys) -> MidnightErrors

write.csv(MidnightErrors, file=paste("MidnightErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Trial ID
Times should be in one of three Trial IDs, widely:
A = 0600 to 1100
B = 1350 to 1800
C = 2100 to 0200
```{r}
#AFTER fix times
#find times that are not within the A, B, or C trials
surveys_2020 %>%
  #change time to numeric
  mutate(Time = as.numeric(Time)) %>%
  #find times outside A, B, or C trials
  filter(Time < 600 & Time > 0200 | Time > 1100 & Time < 1350 | Time > 1800 & Time < 2100) -> TimeNotInTrials

#find times that do not match their Trial ID
surveys_2020 %>%
  #change time to numeric
  mutate(Time = as.numeric(Time)) %>%
  #find times that should be classified as "A" but that are not
  filter(Time > 600 & Time < 1100) %>%
  filter(Trial.ID != "A") -> TimeWrongTrialA
surveys_2020 %>%
  #change time to numeric
  mutate(Time = as.numeric(Time)) %>%
  #find times that should be classified as "B" but that are not
  filter(Time > 1350 & Time < 1800) %>%
  filter(Trial.ID !="B") -> TimeWrongTrialB
surveys_2020 %>%
  #change time to numeric
  mutate(Time = as.numeric(Time)) %>%
  #find times that should be classified as "C" but that are not
  filter(Time > 2100 | Time < 0200) %>%
  filter(Trial.ID !="C") -> TimeWrongTrialC
TimeWrongTrials <- bind_rows(TimeWrongTrialA, TimeWrongTrialB, TimeWrongTrialC)

#find Trial IDs with wrong times    
surveys_2020 %>%
  #change time to numeric
  mutate(Time = as.numeric(Time)) %>%
  #find records classified as "A" but with a time that is not in "A"
  filter(Trial.ID == "A") %>%
  filter(Time < 0600 & Time > 1100) -> WrongTrialA
surveys_2020 %>%
  #change time to numeric
  mutate(Time = as.numeric(Time)) %>%
  #find records classified as "B" but with a time that is not in "B"
  filter(Trial.ID == "B") %>%
  filter(Time < 1350 & Time > 1800) -> WrongTrialB
surveys_2020 %>%
  #change time to numeric
  mutate(Time = as.numeric(Time)) %>%
  #find records classified as "C" but with a time that is not in "C"
  filter(Trial.ID == "C") %>%
  filter(Time > 0200 & Time < 2100) -> WrongTrialC
WrongTrials <- bind_rows(WrongTrialA, WrongTrialB, WrongTrialC)

TrialErrors <- bind_rows(TimeWrongTrials,TimeNotInTrials, WrongTrials)
TrialErrors <- distinct(TrialErrors)
write.csv(TrialErrors, file=paste("TrialErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#ScanID
Every record should have a ScanID
```{r}
#find all ScanIDs that are na
surveys_2020 %>%
  filter(ScanID == "") -> ScanIDErrors

write.csv(ScanIDErrors, file=paste("ScanIDErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

The last character of the ScanID should match the TrialID
```{r}
#run this code AFTER fix trial ID
surveys_2020 %>%
  #subset for ending of ScanID
  mutate(ScanID = substr(ScanID, 10, 10)) %>%
  #find where ScanID does not match Trial ID
  filter(ScanID != Trial.ID) -> ScanIDMismatch

write.csv(ScanIDMismatch, file=paste("ScanIDMismatch", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

There should be 63 unique ScanIDs.
```{r}
#count up the number of ScanIDs. Make sure it is 63.
nlevels(as.factor(surveys_2020$ScanID))
#if there are more or less than 63 levels, look at what the levels are
levels(as.factor(surveys_2020$ScanID))
```

#Grid Cell
All records need a grid cell.

Grid cell must be either AS, US, M, W, LEG, BACK, A01, A02, A03, A04, A05, A06, A07, A08, A09, A10, A11, A12, A13, A14, A15, A16, A17, A18, B01, B02, B03, B04, B05, B06, B07, B08, B09, B10, B11, B12, B13, B14, B15, B16, B17, B18, C01, C02, C03, C04, C05, C06, C07, C08, C09, C10, C11, C12, C13, C14, C15, C16, C17, C18

*I don't think there were consistent methods for whether to indicate a beetle as on the Wall or on the Mulch when we moved the beetle to the Mulch. How should we deal with this?
```{r}
#Find all grid cells that are empty or do not match one of the possible options
surveys_2020 %>%
  filter(Grid_Cell %sans% Grid_Cells) -> GridCellErrors

write.csv(GridCellErrors, file=paste("GridCellErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Location
Location must either be T, B, E, I, C, or blank
```{r}
#find all locations that do not match one of the possible location options
surveys_2020 %>%
  filter(Location %sans% Location) -> LocationError
```

All records without a location, need to correspond to grid cells == AS, US, M, W, LEG, BACK
```{r}
#run this code AFTER fixing grid cell locations
surveys_2020 %>%
  #find all records without a location
  filter(Location == "") %>%
  #for records without location, makes sure that the grid cell corresponds to something off the grid
  filter(Grid_Cell %sans% c("AS", "US", "M", "W", "LEG", "BACK")) -> MissingLocation
```

Correspondingly, all records with grid cells == AS, US, M, W, LEG, BACK, should have a blank Location
```{r}
#run this code AFTER fixing grid cell locations
surveys_2020 %>%
  #find all records with a grid cell that corresponds to something off the grid
  filter(Grid_Cell %in% c("AS", "US", "M", "W", "LEG", "BACK")) %>%
  #for off-grid records, find records with a location
  filter(Location != "") -> WrongLocation

LocationErrors <- bind_rows(LocationError, MissingLocation, WrongLocation)
write.csv(LocationErrors, file=paste("LocationErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Beetle IDs
There should be no empty beetle IDs. 
```{r}
#find empty BeetleIDs
surveys_2020 %>%
  filter(X_fkfieldID_surveys == "") -> BeetleIDIsNA

write.csv(BeetleIDIsNA, file=paste("BeetleIDIsNA", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

All Beetle IDs should match a Beetle ID in that Condo.
```{r}
#Beetles_By_Condo contains IDs of all beetles in each condo.
#For the 2020 data, we only have one list, because beetles were not moved between condos. For an example with distinct time periods with different lists, see the 2019 script. 

Beetles_By_Condo <- read.csv("~/Dropbox/Experiment Beetles 2020/Experimental Design/Summer 2020 cage history - all beetles in cages during experiment.csv")

#Beetle IDs in each Condo
Beetles_1A <- subset(Beetles_By_Condo, Beetleary == "1A")
Beetles_1B <- subset(Beetles_By_Condo, Beetleary == "1B")
Beetles_2A <- subset(Beetles_By_Condo, Beetleary == "2A")
Beetles_2B <- subset(Beetles_By_Condo, Beetleary == "2B")
Beetles_3A <- subset(Beetles_By_Condo, Beetleary == "3A")
Beetles_3B <- subset(Beetles_By_Condo, Beetleary == "3B")
Beetles_4A <- subset(Beetles_By_Condo, Beetleary == "4A")
Beetles_4B <- subset(Beetles_By_Condo, Beetleary == "4B")
Beetles_5A <- subset(Beetles_By_Condo, Beetleary == "5A")
Beetles_5B <- subset(Beetles_By_Condo, Beetleary == "5B")
Beetles_6A <- subset(Beetles_By_Condo, Beetleary == "6A")
Beetles_6B <- subset(Beetles_By_Condo, Beetleary == "6B")

#for each condo, find the beetle IDs that do not match one of the possible beetle IDs
#run this code AFTER assigning a condo to every beetle (running the condo code)
#run this code AFTER checking all the dates and scan IDs (running the Date and ScanID code)

# make lists of Errors
surveys_2020 %>%
  filter(Condo == "1A") %>%
  anti_join(Beetles_1A, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle1AErrors
surveys_2020 %>%
  filter(Condo == "1B") %>%
  anti_join(Beetles_1B, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle1BErrors
surveys_2020 %>%
  filter(Condo == "2A") %>%
  anti_join(Beetles_2A, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle2AErrors
surveys_2020 %>%
  filter(Condo == "2B") %>%
  anti_join(Beetles_2B, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle2BErrors
surveys_2020 %>%
  filter(Condo == "3A") %>%
  anti_join(Beetles_3A, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle3AErrors
surveys_2020 %>%
  filter(Condo == "3B") %>%
  anti_join(Beetles_3B, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle3BErrors
surveys_2020 %>%
  filter(Condo == "4A") %>%
  anti_join(Beetles_4A, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle4AErrors
surveys_2020 %>%
  filter(Condo == "4B") %>%
  anti_join(Beetles_4B, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle4BErrors
surveys_2020 %>%
  filter(Condo == "5A") %>%
  anti_join(Beetles_5A, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle5AErrors
surveys_2020 %>%
  filter(Condo == "5B") %>%
  anti_join(Beetles_5B, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle5BErrors
surveys_2020 %>%
  filter(Condo == "6A") %>%
  anti_join(Beetles_6A, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle6AErrors
surveys_2020 %>%
  filter(Condo == "6B") %>%
  anti_join(Beetles_6B, by=c("X_fkfieldID_surveys" = "ID")) -> Beetle6BErrors

#merge errors from each condo into one sheet
BeetleIDErrors<-bind_rows(Beetle1AErrors, Beetle1BErrors, Beetle2AErrors, Beetle2BErrors, Beetle3AErrors, Beetle3BErrors, Beetle4AErrors, Beetle4BErrors, Beetle5AErrors, Beetle5BErrors, Beetle6AErrors, Beetle6BErrors)

BeetleIDErrors %>%
  filter(X_fkfieldID_surveys %sans% UKs) -> BeetleIDErrors

write.csv(BeetleIDErrors, file=paste("BeetleIDErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Location-Grid_Cell Mismatch
All records with location T, B, E, I should be on a cell with a bracket.
In 2020, all condos had a clumped distribution of fungus.
```{r}
#run this code AFTER fixing grid cells
#find all records with T, B, E, I locations that are not on grid cells with brackets
surveys_2020 %>%
  #first find all Locations that are T, B, E, I
  filter(Location %in% c("T", "B", "E", "I")) %>%
  #find cells that do not have brackets
  filter(Grid_Cell %sans% ClumpedBrackets) -> BracketLocationErrors

write.csv(BracketLocationErrors, file=paste("BracketLocationErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Partners
All partner Beetle IDs should match a Beetle ID in that Condo.
```{r}
#remove those without partners and with UK, UKF, and UKM partners
#could do this with UK, UKF, and UKM partners if want to check if UKs are just because of not understanding handwriting
surveys_2020 %>%
  filter(Partner != "" & Partner %sans% UKs) -> Beetles_with_partners

# Beetle IDs in each condo
# again, only doing this once because beetles were not moved--see 2019 script for an example of that
Beetles_By_Condo <- read.csv("~/Dropbox/Experiment Beetles 2020/Experimental Design/Summer 2020 cage history - all beetles in cages during experiment.csv")

#Beetle IDs in each Condo
Beetles_1A <- subset(Beetles_By_Condo, Beetleary == "1A")
Beetles_1B <- subset(Beetles_By_Condo, Beetleary == "1B")
Beetles_2A <- subset(Beetles_By_Condo, Beetleary == "2A")
Beetles_2B <- subset(Beetles_By_Condo, Beetleary == "2B")
Beetles_3A <- subset(Beetles_By_Condo, Beetleary == "3A")
Beetles_3B <- subset(Beetles_By_Condo, Beetleary == "3B")
Beetles_4A <- subset(Beetles_By_Condo, Beetleary == "4A")
Beetles_4B <- subset(Beetles_By_Condo, Beetleary == "4B")
Beetles_5A <- subset(Beetles_By_Condo, Beetleary == "5A")
Beetles_5B <- subset(Beetles_By_Condo, Beetleary == "5B")
Beetles_6A <- subset(Beetles_By_Condo, Beetleary == "6A")
Beetles_6B <- subset(Beetles_By_Condo, Beetleary == "6B")

#for each condo, find partners that are not in the list of potential partners
#run this code AFTER assigning a condo for each beetle
Beetles_with_partners %>%
  filter(Condo == "1A") %>%
  anti_join(Beetles_1A, by=c("Partner" = "ID")) -> Beetle1APartnerErrors
Beetles_with_partners %>%
  filter(Condo == "1B") %>%
  anti_join(Beetles_1B, by=c("Partner" = "ID")) -> Beetle1BPartnerErrors
Beetles_with_partners %>%
  filter(Condo == "2A") %>%
  anti_join(Beetles_2A, by=c("Partner" = "ID")) -> Beetle2APartnerErrors
Beetles_with_partners %>%
  filter(Condo == "2B") %>%
  anti_join(Beetles_2B, by=c("Partner" = "ID")) -> Beetle2BPartnerErrors
Beetles_with_partners %>%
  filter(Condo == "3A") %>%
  anti_join(Beetles_3A, by=c("Partner" = "ID")) -> Beetle3APartnerErrors
Beetles_with_partners %>%
  filter(Condo == "3B") %>%
  anti_join(Beetles_3B, by=c("Partner" = "ID")) -> Beetle3BPartnerErrors
Beetles_with_partners %>%
  filter(Condo == "4A") %>%
  anti_join(Beetles_4A, by=c("Partner" = "ID")) -> Beetle4APartnerErrors
Beetles_with_partners %>%
  filter(Condo == "4B") %>%
  anti_join(Beetles_4B, by=c("Partner" = "ID")) -> Beetle4BPartnerErrors
Beetles_with_partners %>%
  filter(Condo == "5A") %>%
  anti_join(Beetles_5A, by=c("Partner" = "ID")) -> Beetle5APartnerErrors
Beetles_with_partners %>%
  filter(Condo == "5B") %>%
  anti_join(Beetles_5B, by=c("Partner" = "ID")) -> Beetle5BPartnerErrors
Beetles_with_partners %>%
  filter(Condo == "6A") %>%
  anti_join(Beetles_6A, by=c("Partner" = "ID")) -> Beetle6APartnerErrors
Beetles_with_partners %>%
  filter(Condo == "6B") %>%
  anti_join(Beetles_6B, by=c("Partner" = "ID")) -> Beetle6BPartnerErrors


#combine all partner errors from each condo into one datasheet
BeetlePartnerIDErrors<-bind_rows(Beetle1APartnerErrors, Beetle1BPartnerErrors, Beetle2APartnerErrors, Beetle2BPartnerErrors, Beetle3APartnerErrors, Beetle3BPartnerErrors, Beetle4APartnerErrors, Beetle4BPartnerErrors, Beetle5APartnerErrors, Beetle5BPartnerErrors, Beetle6APartnerErrors, Beetle6BPartnerErrors)

BeetlePartnerIDErrors %>%
  filter(Partner %sans% UKs) -> BeetlePartnerIDErrors

write.csv(BeetlePartnerIDErrors, file=paste("BeetlePartnerIDErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Sex
All beetles should have a sex.
Beetle IDs that start with 2 or 4 should be male and beetle IDs that start with 3 or 5 should be female. The changes made in 2020 to allow for multiple starting #s are not the most elegant... UKMs and UKFs are still being assigned to 2 and 3, but I *think* that's just part of error-checking and doesn't change anything permanently.

```{r}
#find all records that do not have a sex
surveys_2020 %>%
  filter(Survey_Sex %sans% c("M", "F", "UK")) -> SexMislabels

write.csv(SexMislabels, file=paste("SexMislabels", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"))

#AFTER changing the mislabeled sex column, find all records that have mismatched sex and IDs
surveys_2020 %>%
  #find all Males
  filter(Survey_Sex == "M") %>%
  #substring the Beetle ID to the first character (2, 3, 4, 5, or U)
  mutate(FocalSex = substr(X_fkfieldID_surveys, 1, 1)) %>%
  #change U to 2 if UKM - this is a holdover from when males only started with 2
  mutate(FocalSex = ifelse(FocalSex == "U", substr(X_fkfieldID_surveys, 3, 3), FocalSex)) %>%
  mutate(FocalSex = ifelse(FocalSex == "F", 3, ifelse(FocalSex == "M", 2, FocalSex))) %>%
  #find all "Male" beetles that do not start with a 2 or a 4
  filter(FocalSex %sans% MaleNumbers) -> MaleMismatch

surveys_2020 %>%
  #find all Females
  filter(Survey_Sex == "F") %>%
  #substring the Beetle ID to the first number (2, 3, 4, 5, or U)
  mutate(FocalSex = substr(X_fkfieldID_surveys, 1, 1)) %>%
  #change U to 3 if UKF - this is a holdover from when females only started with 3
  mutate(FocalSex = ifelse(FocalSex == "U", substr(X_fkfieldID_surveys, 3, 3), FocalSex)) %>%
  mutate(FocalSex = ifelse(FocalSex == "F", 3, ifelse(FocalSex == "M", 2, FocalSex))) %>%
  #find all "Female" beetles that do not start with a 3
  filter(FocalSex %sans% FemaleNumbers) -> FemaleMismatch

surveys_2020 %>%
  #find all UK sex
  filter(Survey_Sex == "UK") %>%
  #find all records that do not have "UK" focal IDs
  filter(X_fkfieldID_surveys != "UK") -> UKMismatch

#combine the mismatch beetles into one dataset
SexMismatch<-bind_rows(UKMismatch, MaleMismatch, FemaleMismatch)

write.csv(SexMismatch, file=paste("SexMismatch", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Behavior
##No Behavior
Every entry needs a behavior.

Rule:
If a male has no social partners and does not have a behavior, assign "S" in "Replace Field Contents" in FMP without needing to reference the datasheets.
```{r}
#find all behaviors that are na
surveys_2020 %>%
  filter(Behavior == "") -> BehaviorIsNA

write.csv(BehaviorIsNA, file=paste("BehaviorIsNA", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

## Incorrect behavior
Every behavior should be one of the allowed options.
```{r}
#find all behaviors that are not one of the allowed options. 
surveys_2020 %>%
  filter(Behavior %sans% Behaviors) -> BehaviorIsWeird

write.csv(BehaviorIsWeird, file=paste("BehaviorIsWeird", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```


##Partner Behaviors
Many behaviors indicate that a beetle should have a partner. For ex, records with CRT, COP, G, F, MCRT, FCRT, FG, MG, FCOP, MCOP should have a partner.
2020 note: changed these to be called PartnerBehaviors, not MatingBehaviors, since same-sex CRTs, COPs, and Gs are considered touch interactions and not excluded from networks. 
```{r}
#find records that have behaviors that indicate partners that do not have partners
surveys_2020 %>%
  #find records with behaviors that indicate partners
  filter(Behavior %in% PartnerBehaviors | Behavior == "F") %>%
  #of records with these behaviors, find records that do not have partners
  filter(Partner == "") -> MissingPartners

write.csv(MissingPartners, file=paste("MissingPartners", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

##Sex-Specific Behaviors
Some behaviors are sex-specific. Males should never be laying or OS. Females should never be fighting. Males should never be FCRT, FG, FCOP. Females should never be MCRT, MG, MCOP.
```{r}
#find males with laying, OS, FCRT, FG, FCOP behaviors
#run this code AFTER fix sex errors
surveys_2020 %>%
  #find records that are males
  filter(Survey_Sex == "M") %>% 
  #find records with L, OS, FCRT, FG, FCOP behaviors
  filter(Behavior %in% c("L", "OS", "FCRT", "FG", "FCOP")) -> MaleBehaviorErrors

#find females with MCRT, MG, MCOP behaviors
surveys_2020 %>%
  #find records that are females
  filter(Survey_Sex == "F") %>%
  filter(Behavior %in% c("MCRT", "MG", "MCOP")) -> FemaleBehaviorErrors

SexSpecificBehaviorsErrors <- bind_rows(MaleBehaviorErrors, FemaleBehaviorErrors)
write.csv(SexSpecificBehaviorsErrors, file=paste("SexSpecificBehaviorsErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

##Missing Mating Behaviors
All records with a mating partner should have a mating behavior.
2020 note: after running this for the first time, removed same-sex interactions from the list of MatingBehaviors, meaning we are going to have some changes to make in Round 2. 
```{r}
#find records with a mating partner that do not have a mating behavior
surveys_2020 %>%
  #find records with a mating partner
  filter(PartnerInteraction == "Mating Partners") %>%
  #of records with a Mating Partner, find records that do not have a mating behavior
  filter(Behavior %sans% MatingBehaviors)  -> MissingMatingBehaviors

write.csv(MissingMatingBehaviors, file=paste("MissingMatingBehaviors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

##Missing Mating Partners
Conversely, most records with a (male-female) mating behavior should have a mating partner interaction. Note that this can throw errors for records that are correct if a mating partner was omitted for the same focal. 
2020 note: This initially flagged MCRTing pairs recorded as Touch Partners as incorrect, but that *is* the way we should be recording them. Changed MatingBehaviors to only include CRT, COP, and G. Some records in this first error sheet will not be dealt with, because they are not errors. 
```{r}
#find records with a mating behavior that do not have a mating partner interaction
surveys_2020 %>%
  #find records with a mating behavior
  filter(Behavior %in% MatingBehaviors) %>%
  #for records with mating behaviors, find records that do not have a mating partner within each beetle, ScanID, Condo
  group_by(X_fkfieldID_surveys, ScanID, Condo) %>%
  mutate(nMatingInteractions = length(which(PartnerInteraction == "Mating Partners"))) %>%
  filter(nMatingInteractions < 1) -> MissingMatingPartners

write.csv(MissingMatingPartners, file=paste("MissingMatingPartners", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

##Multiple Mating Partners
All records with a mating behavior (CRT, COP, G) should only have one mating partner.
2020 note: oreviously this included same-sex CRT, COP, and G
```{r}
#find records that have mating behaviors and more than one mating partner
surveys_2020 %>%
  #find records with mating behaviors
  filter(Behavior %in% MatingBehaviors) %>%
  #for records with mating behaviors, find records that have mating partners
  filter(PartnerInteraction == "Mating Partners") %>%
  #count the number of mating behaviors for each beetle, scanID, condo
  group_by(X_fkfieldID_surveys, ScanID, Condo) %>%
  mutate(nMatingPartners = length(PartnerInteraction)) %>%
  filter(nMatingPartners >1) -> MultipleMatingPartners

write.csv(MultipleMatingPartners, file=paste("MultipleMatingPartners", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

##Secondary Behaviors
Secondary behaviors should not be mating behaviors. If there is a secondary behavior, primary behavior should not be S or M.
```{r}
#find records with mating secondary behaviors
surveys_2020 %>%
  #find records with secondary behaviors
  filter(Secondary_Behavior != "") %>%
  #find records with mating secondary behaviors
  filter(Secondary_Behavior %in% MatingBehaviors) -> SecondaryMatingBehaviors

#find records with secondary behaviors and primary behaviors that are S or M
surveys_2020 %>%
  #find records with secondary behaviors
  filter(Secondary_Behavior != "") %>%
  filter(Behavior %in% c("S", "M")) -> BehaviorOrderErrors

SecondaryBehaviorErrors <- bind_rows(SecondaryMatingBehaviors, BehaviorOrderErrors)
write.csv(SecondaryBehaviorErrors, file=paste("SecondaryBehaviorErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

##Bracket-Specific Behaviors
All OS behaviors should occur on a cell with a bracket.

QUESTIONS FROM PAC: What about secondary behaviors? (ETA: added) What about Laying?
```{r}
#run this code AFTER fixing grid cells
#run this code AFTER fixing date.started
#run this code AFTER fixing Condo
#find all records with OS behavior that are not on grid cells with brackets
surveys_2020 %>%
  #first find all OS behaviors
  filter(Behavior == "OS" | Secondary_Behavior == "OS") %>%
  #find cells that do not have brackets
  filter(Grid_Cell %sans% ClumpedBrackets) -> BracketOSErrors

write.csv(BracketOSErrors, file=paste("BracketOSErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Partner Interactions
All records with a partner interaction should have a partner.
```{r}
#find all records that have a partner interaction but have no partner
surveys_2020 %>%
  #find all records that have a partner interaction
  filter(PartnerInteraction != "") %>%
  #of records with partner interactions, find all records that do not have a partner
  filter(Partner == "") -> PartnerInteractionErrors1
```

Conversely, all records with a partner should have a partner interaction.
```{r}
#find all records that have a partner but no partner interaction
surveys_2020 %>%
  #find all records that have a partner
  filter(Partner != "") %>%
  #of records with a partner, find all records that do not have a partner interaction
  filter(PartnerInteraction == "") -> PartnerInteractionErrors2

```

All records should have acceptable Partner Interactions.
```{r}
Interactions <- c("5 CM Partners", "Mating Partners", "Touch Partners")

#find all records that have a partner interaction that is not allowed (ie, more than one option selected)
surveys_2020 %>%
  #find all records that have a partner interaction
  filter(PartnerInteraction != "") %>%
  #of records with partner interactions, find all records that are not one of the three options
  filter(PartnerInteraction %sans% Interactions) -> PartnerInteractionErrors3

#combine the partner interaction errors
PartnerInteractionErrors<-rbind(PartnerInteractionErrors1, PartnerInteractionErrors2, PartnerInteractionErrors3)

write.csv(PartnerInteractionErrors, file=paste("PartnerInteractionErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```


#Duplicate Entries
All entries should only be recorded in the database 1x.
```{r}
#find duplicate rows
surveys_2020 %>%
  #group by ID, Partner, and ScanID
  group_by(X_fkfieldID_surveys, Partner, ScanID) %>%
  #find duplicates
  filter(n() > 1) -> DupEntries

#may want to remove UK from DupEntries, as it makes sense that we could see more than one UK on the same ScanID
DupEntries %>%
  filter(X_fkfieldID_surveys %sans% UKs) -> DupEntriesNoUK

write.csv(DupEntriesNoUK, file=paste("DupEntries", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Multiple Occurrences
A beetle may be recorded more than once during a scan (in a different location) when either a beetle ID/behavior/location was entered incorrectly or if a beetle moved during the scan. 

How should the beetlearies deal with multiple occurrences? It is much more likely that we saw a beetle twice within one scan than it is in wild populations.
```{r}
#run this code AFTER fixing Condo errors
#find a beetle that was seen more than once on a scan
surveys_2020 %>%
  #remove UK individuals because many UK individuals will be seen with different behaviors and at different locations within a scan
  filter(X_fkfieldID_surveys %sans% UKs) %>%
  #group by ID, ScanID, Condo
  #chose to include Condo because should use other code to find condo errors
  group_by(X_fkfieldID_surveys, ScanID, Condo) %>%
  #for each beetle in each ScanID, count the number of beetles with different behaviors
  mutate(nBehavior = length(unique(Behavior))) %>%
  #for each beetle in each ScanID, count the number of beetles with different locations
  mutate(nGrid_Cell = length(unique(Grid_Cell))) %>%
  #add the two Behavior and Grid_Cell counts together
  mutate(n = nBehavior + nGrid_Cell) %>%
  #find beetles with more than one behavior/location
  #this finds beetles with behavior typos, grid cell typos, and beetles that were seen more than once on a scan
  filter(n > 2) -> MultipleOccurrences

write.csv(MultipleOccurrences, file=paste("MultipleOccurrences", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Typos
During a scan, nothing other than a beetle's partner should change between the records. The above code currently only finds when a beetle's behavior or location changes. I could also ensure that date/trialID/location/sex does not change.
```{r}
# 2020 Update: the first time through, removed all records already marked to be deleted from the data before looking for errors (copying from the end of the Error Changes file). This makes the sheet MUCH easier to look through, but may also cause issues I'm not considering. To be safe, do NOT do this on the second and third rounds. 
#ToDelete <- c() 

surveys_2020 %>%
  #remove records already to be deleted
  #filter(RecordNumber %sans% ToDelete) %>%
  #remove UK individuals because many UK individuals will be seen with different sex within a scanID/ condo
  filter(X_fkfieldID_surveys %sans% UKs) %>%
  #group by ID, ScanID, Condo
  #chose to include Condo because should use other code to find condo errors
  group_by(X_fkfieldID_surveys, ScanID, Condo) %>%
  #for each beetle in each ScanID, count the number of beetles with different dates
  mutate(nDate.Started = length(unique(Date.Started))) %>%
  #for each beetle in each ScanID, count the number of beetles with different dates
  mutate(nDate_Surveys = length(unique(Date_Surveys))) %>%
  #for each beetle in each ScanID, count the number of beetles with different Trial.ID
  mutate(nTrial.ID = length(unique(Trial.ID))) %>%
  #for each beetle in each ScanID, count the number of beetles with different locations
  mutate(nLocation = length(unique(Location))) %>%
  #for each beetle in each ScanID, count the number of beetles with different sexes
  mutate(nSex = length(unique(Survey_Sex))) %>%
  #add all counts together
  mutate(n = nDate.Started + nDate_Surveys + nTrial.ID + nLocation + nSex) %>%
  #find beetles with more than one behavior/location
  #this finds beetles with date, time, trialID, location, sex typos
  filter(n > 5) -> Typos

write.csv(Typos, file=paste("Typos", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Extra Records without Partners
If a beetle has a partner interaction in a scan, the beetle should not have a record without partners in the same scan. 
```{r}
#run this code AFTER fixing partner interactions
#run this code AFTER fixing multiple occurrences
#run this code AFTER fixing duplicate entries
#find all records where the beetle has multiple records in a scan and does not have a partner interaction

# 2020 Update: the first time through, removed all records already marked to be deleted from the data before looking for errors (copying from the end of the Error Changes file). This makes the sheet MUCH easier to look through, but may also cause issues I'm not considering. To be safe, do NOT do this on the second and third rounds. 
#ToDelete <- c() 

surveys_2020 %>%
  #remove records already to be deleted
  #filter(RecordNumber %sans% ToDelete) %>%
  #remove UK individuals because many UK individuals will have multiple records in a scan because there are multiple UK inidviduals
  filter(X_fkfieldID_surveys %sans% UKs) %>%
  #group by ID, ScanID, Condo
  group_by(X_fkfieldID_surveys, ScanID, Condo) %>%
  #for each beetle in each ScanID/Condo, count the number of different records
  mutate(nRecords = length(X_fkfieldID_surveys)) %>% 
  #for beetles with more than one record, there should be no record without a partner
  filter(nRecords > 1) %>%
  filter(Partner == "") -> ExtraRecords

write.csv(ExtraRecords, file=paste("ExtraRecords", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Reciprocal Interactions
All records with partners should have reciprocal records (ex: ABC crt DEF and DEF crt ABC).
ADD in MissingMatingPartners before running this code!
```{r}
#find records that do not have reciprocals
#run this code AFTER fixing condos

# curiosity test after doing this the first time: merge in the missing records
# read.csv("MissingEntries_edit2_.csv") %>%
#   mutate(RecordNumber = NA) -> MissingEntries

# surveys_2020 %>% 
#   #select(!uniqueIDforduplicates) %>% # not sure why this column vanished in round 2
#   rbind(., MissingEntries) -> test

surveys_2020 %>%
  #first subset for only records with partners
  filter(Partner != "") %>% 
  #next find reciporcal records. to do this, make a column with the Focal and Partner ID in alphabetical order. to alphabetize Focal and Partner ID, need to convert Focal and Partner ID to characters
  mutate(X_fkfieldID_surveys = as.character(X_fkfieldID_surveys)) %>%
  mutate(Partner = as.character(Partner)) %>%
  mutate(InteractingID = ifelse(X_fkfieldID_surveys < Partner, paste(X_fkfieldID_surveys, Partner, sep="_"), paste(Partner, X_fkfieldID_surveys, sep="_"))) %>%
  #group by ScanID to find records without reciprocals
  group_by(InteractingID, ScanID, Condo) %>%
  #records without reciprocal records will have less than 2 records
  mutate(nInteractingID = length(InteractingID)) %>%
  filter(nInteractingID < 2) -> MissingReciprocal

write.csv(MissingReciprocal, file=paste("MissingReciprocal", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

All reciprocal interactions should have the same type of partner interaction (both touch partners, 5cm partners, or mating partners)
```{r}
#run this code AFTER fixing missing reciprocals and reciprocal errors
#find reciprocal records that do not have the same type of partner interaction

# # can also run with the missing entries added in (and additionally, could remove records marked for deletion)
# read.csv("MissingEntries_20210708.csv") %>%
#   mutate(RecordNumber = NA) -> MissingEntries
# 
# surveys_2020 %>% 
#   select(!uniqueIDforduplicates) %>% 
#   rbind(., MissingEntries) -> test

surveys_2020 %>%
  #first subset for only records with partners
  filter(Partner != "") %>%
  #next find reciporcal records. to do this, make a column with the Focal and Partner ID in alphabetical order. to alphabetize Focal and Partner ID, need to convert Focal and Partner ID to characters
  mutate(X_fkfieldID_surveys = as.character(X_fkfieldID_surveys)) %>%
  mutate(Partner = as.character(Partner)) %>%
  mutate(InteractingID = ifelse(X_fkfieldID_surveys < Partner, paste(X_fkfieldID_surveys, Partner, sep="_"), paste(Partner, X_fkfieldID_surveys, sep="_"))) %>%
  #group by ScanID to find reciprocal records
  group_by(InteractingID, ScanID, Condo) %>%
  #for reciprocal records, find records that do not have the same partner interaction
  mutate(count= n_distinct(PartnerInteraction)) %>%
  filter(count > 1) -> WrongReciprocalInteractions

write.csv(WrongReciprocalInteractions, file=paste("WrongReciprocalInteractions", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Self Interactions
Beetles should not interact with themselves.
```{r}
#find records that interact with themselves
surveys_2020 %>%
  #filter out UK beetles, because possible that UK beetles interact with each other
  filter(X_fkfieldID_surveys %sans% UKs) %>%
  #filter for records where Focal ID == Partner ID
  filter(as.character(X_fkfieldID_surveys) == as.character(Partner)) -> SelfInteractions
  
write.csv(SelfInteractions, file=paste("SelfInteractions", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Mating Interactions 
All records with a mating partner should have a partner that is the opposite sex. 2020 note: this has been updated, but not elegantly. 
```{r}
#run this code AFTER find sex errors
#find all records with a CRT, COP, G behavior that do not have a partner of the opposite sex
surveys_2020 %>%
  #find records with mating partners
  filter(PartnerInteraction == "Mating Partners") %>%
  #of records with mating partners, choose behaviors that are not same sex
  filter(Behavior == "COP" | Behavior == "CRT" | Behavior == "G") %>%
  #filter out all UK individuals because unsure of sex
  filter(X_fkfieldID_surveys != "UK") %>%
  filter(Partner != "UK") %>%
  #Partners do not have a sex in the dataset, give both Focal and Partner sex based on first letter of ID
  mutate(FocalSexU = substr(X_fkfieldID_surveys, 1, 1)) %>%
  mutate(PartnerSexU = substr(Partner, 1, 1)) %>%
  #change U to either 2 (M) or 3 (F) from last letter of ID
  mutate(FocalSex = ifelse(FocalSexU == "U", substr(X_fkfieldID_surveys, 3, 3), FocalSexU)) %>%
  mutate(FocalSex = ifelse(FocalSex == "F", 3, ifelse(FocalSex == "M", 2, FocalSex))) %>%
  mutate(PartnerSex = ifelse(PartnerSexU == "U", substr(Partner, 3, 3), PartnerSexU)) %>%
  mutate(PartnerSex = ifelse(PartnerSex == "F", 3, ifelse(PartnerSex == "M", 2, PartnerSex))) %>%
  #because all males start with "2" and all females start with "3", partner interactions with a male and female should equal five (M+F=5)
  mutate(FocalSex = as.numeric(FocalSex)) %>%
  mutate(PartnerSex = as.numeric(PartnerSex)) %>%
  mutate(MF = FocalSex + PartnerSex) %>%
  filter(MF %sans% c(5, 7, 9)) -> MatingSexMismatch # 2020 change: male and female numbers could sum to 5, 7, or 9

write.csv(MatingSexMismatch, file=paste("MatingSexMismatch", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

All records with a same-sex mating partner should have a partner that is the same sex. 2020 note: this has been updated, but not elegantly. 
```{r}
#run this code AFTER find sex errors
#find all records with a MCRT, MCOP, MG, FCRT, FCOP, FG behavior that do not have a partner of the same sex
surveys_2020 %>%
  #find records with mating partners
  filter(PartnerInteraction == "Mating Partners") %>%
  #of records with mating partners, choose behaviors that are not same sex
  filter(Behavior %in% c("MCRT", "MCOP", "MG", "FCRT", "FCOP", "FG")) %>%
  #filter out all UK individuals because unsure of sex
  filter(X_fkfieldID_surveys != "UK") %>%
  filter(Partner != "UK") %>%
  #Partners do not have a sex in the dataset, give both Focal and Partner sex based on first letter of ID
  mutate(FocalSexU = substr(X_fkfieldID_surveys, 1, 1)) %>%
  mutate(PartnerSexU = substr(Partner, 1, 1)) %>%
  #change U to either 2 (M) or 3 (F) from last letter of ID
  mutate(FocalSex = ifelse(FocalSexU == "U", substr(X_fkfieldID_surveys, 3, 3), FocalSexU)) %>%
  mutate(FocalSex = ifelse(FocalSex == "F", 3, ifelse(FocalSex == "M", 2, FocalSex))) %>%
  mutate(PartnerSex = ifelse(PartnerSexU == "U", substr(Partner, 3, 3), PartnerSexU)) %>%
  mutate(PartnerSex = ifelse(PartnerSex == "F", 3, ifelse(PartnerSex == "M", 2, PartnerSex))) %>%
  #because all males start with "2" and all females start with "3", partner interactions with a male and male should equal six and famale and female should equal 4
  mutate(FocalSex = as.numeric(FocalSex)) %>%
  mutate(PartnerSex = as.numeric(PartnerSex)) %>%
  mutate(MF = FocalSex + PartnerSex) %>%
  filter(MF %sans% c(4, 6, 8, 10)) -> MatingSameSexMismatch # 2020 change: two male or two female numbers could sum to 4, 6, 8, or 10

write.csv(MatingSameSexMismatch, file=paste("MatingSameSexMismatch", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

All records with mating partners should have reciprocal mating records
```{r}
#find records that do not have reciprocal mating records
#run this code AFTER fixing condos
#run this code AFTER fixing mating records
surveys_2020 %>%
  #first subset for only records with mating partners
  filter(PartnerInteraction == "Mating Partners") %>%
  #next find reciporcal records. to do this, make a column with the Focal and Partner ID in alphabetical order. to alphabetize Focal and Partner ID, need to convert Focal and Partner ID to characters
  mutate(X_fkfieldID_surveys = as.character(X_fkfieldID_surveys)) %>%
  mutate(Partner = as.character(Partner)) %>%
  mutate(InteractingID = ifelse(X_fkfieldID_surveys < Partner, paste(X_fkfieldID_surveys, Partner, sep="_"), paste(Partner, X_fkfieldID_surveys, sep="_"))) %>%
  #group by ScanID to find records without reciprocals
  group_by(InteractingID, ScanID, Condo) %>%
  #records without reciprocal records will have less than 2 records
  mutate(nInteractingID = length(InteractingID)) %>%
  filter(nInteractingID < 2) -> MissingMatingReciprocal

write.csv(MissingMatingReciprocal, file=paste("MissingMatingReciprocal", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)

#find records that do not have reciprocals
surveys_2020 %>%
  #first subset for only records with partners
  filter(PartnerInteraction == "Mating Partners") %>%
  #next find reciporcal records. to do this, make a column with the Focal and Partner ID in alphabetical order. to alphabetize Focal and Partner ID, need to convert Focal and Partner ID to characters
  mutate(X_fkfieldID_surveys = as.character(X_fkfieldID_surveys)) %>%
  mutate(Partner = as.character(Partner)) %>%
  mutate(InteractingID = ifelse(X_fkfieldID_surveys < Partner, paste(X_fkfieldID_surveys, Partner, sep="_"), paste(Partner, X_fkfieldID_surveys, sep="_"))) %>%
  #group by ScanID to find records without reciprocals
  dplyr::group_by(InteractingID, ScanID, Condo) %>%
  #records without reciprocal errors will have more than 2 records
  #remove UK because UK interactions may have more than 2 records
  #filter(Partner != "UK" & Partner != "UKM" & Partner != "UKF") %>%
  #filter(X_fkfieldID_surveys != "UK" & X_fkfieldID_surveys != "UKM" & X_fkfieldID_surveys != "UKF") %>%
  mutate(nInteractingID = length(InteractingID)) %>%
  filter(nInteractingID > 2) -> MatingReciprocalErrors

write.csv(MatingReciprocalErrors, file=paste("MatingReciprocalErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

All reciprocal mating interactions should be in same location.
```{r}
#find records with reciprocal mating records that are not in the same location
#run this code AFTER fixing condos
#run this code AFTER fixing mating records
#run this code AFTER fixing reciprocal mating records
surveys_2020 %>%
  #first subset for only records with mating partners
  filter(PartnerInteraction == "Mating Partners") %>%
  #next find reciporcal records. to do this, make a column with the Focal and Partner ID in alphabetical order. to alphabetize Focal and Partner ID, need to convert Focal and Partner ID to characters
  mutate(X_fkfieldID_surveys = as.character(X_fkfieldID_surveys)) %>%
  mutate(Partner = as.character(Partner)) %>%
  mutate(InteractingID = ifelse(X_fkfieldID_surveys < Partner, paste(X_fkfieldID_surveys, Partner, sep="_"), paste(Partner, X_fkfieldID_surveys, sep="_"))) %>% 
  #group by ScanID
  group_by(InteractingID, ScanID, Condo) %>%
  #find reciprocal records that are in different locations
  mutate(nLocation = length(unique(Location))) %>%
  #reciprocal records that are in different locations will have a nLocation > 1
  filter(nLocation > 1) -> MatingReciprocalLocationErrors

write.csv(MatingReciprocalLocationErrors, file=paste("MatingReciprocalLocationErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

All reciprocal mating interactions should be in same grid cell.
```{r}
#find records with reciprocal mating records that are not in the same grid cell
#run this code AFTER fixing condos
#run this code AFTER fixing mating records
#run this code AFTER fixing reciprocal mating records
surveys_2020 %>%
  #subset for only records with mating partners
  filter(PartnerInteraction == "Mating Partners") %>%
  #next find reciporcal records. to do this, make a column with the Focal and Partner ID in alphabetical order. to alphabetize Focal and Partner ID, need to convert Focal and Partner ID to characters
  mutate(X_fkfieldID_surveys = as.character(X_fkfieldID_surveys)) %>%
  mutate(Partner = as.character(Partner)) %>%
  mutate(InteractingID = ifelse(X_fkfieldID_surveys < Partner, paste(X_fkfieldID_surveys, Partner, sep="_"), paste(Partner, X_fkfieldID_surveys, sep="_"))) %>% 
  #group by ScanID
  group_by(InteractingID, ScanID, Condo) %>%
  #find reciprocal records that are in different grid cells
  mutate(nGridCell = length(unique(Grid_Cell))) %>%
  #reciprocal records that are in different locations will have a nLocation > 1
  filter(nGridCell > 1) -> MatingReciprocalGridCellErrors

write.csv(MatingReciprocalGridCellErrors, file=paste("MatingReciprocalGridCellErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

#Transitive Mating Interactions
Mating partners should share the same Touch and 5 CM partners
```{r}
#find records with reciprocal mating records
#run this code AFTER fixing condos
#run this code AFTER fixing mating records
#run this code AFTEr fixing reciprocal mating records

#ToDelete <- c(76524, 59664, 76470, 76471, 76466, 76467, 55122, 76440, 69772, 69773, 55183, 57043, 57176, 64030, 64417, 69770, 70574, 71560, 71666, 73648, 73736, 73737, 75295)

surveys_2020 %>%
  #remove records already to be deleted
  #filter(RecordNumber %sans% ToDelete) %>%
  #calculate the number of partners each ID has during each scan
  #remove records without parnters
  filter(Partner != "") %>%
  #group by ID and Scan ID
  group_by(X_fkfieldID_surveys, ScanID, Condo) %>%
  #find the number of partners each ID has during a scan
  mutate(nPartners = length(unique(Partner))) %>%
  #ungroup to move on
  ungroup() %>%
  #now subset for only records with mating partners
  filter(PartnerInteraction == "Mating Partners") %>%
  #next find reciprocal records. to do this, make a column with the Focal and Partner ID in alphabetical order. to alphabetize Focal and Partner ID, need to convert Focal and Partner ID to characters
  mutate(X_fkfieldID_surveys = as.character(X_fkfieldID_surveys)) %>%
  mutate(Partner = as.character(Partner)) %>%
  mutate(InteractingID = ifelse(X_fkfieldID_surveys < Partner, paste(X_fkfieldID_surveys, Partner, sep="_"), paste(Partner, X_fkfieldID_surveys, sep="_"))) %>%
  #group by Scan ID
  group_by(InteractingID, ScanID, Condo) %>%
  #find reciprocal mating records that have different number of partners
  mutate(count = n_distinct(nPartners)) %>%
  filter(count > 1) -> TransitiveErrors

#I kept UK, UKF, UKM in this code. This will produce some false positives.

write.csv(TransitiveErrors, file=paste("TransitiveErrors", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

# ****** IMPORT *******
At the end of one round of edits, import to FileMaker.

#Delete records
Select records in FMP, then read through notes to make sure all records should be marked for deletion

#Add missing records

# ===== REPEAT ========
Perform a second round of edits.

#Look at notes
After the second round of edits, read through every note in the Notes fields.
+including location notes
+Do any Ls need to be OS especially during early scans?
+When do we first notice that a beetle died? DEAD?
+read through every note
```{r}
#read through notes
surveys_2020 %>%
  #append location notes to notes 
  mutate(AllNotes = paste(Notes, LocationNotes, sep=",")) %>% 
  #read through notes
  filter(AllNotes != ",NA") -> Notes

write.csv(Notes, file=paste("Notes", format(Sys.time(), "%Y%m%d"), ".csv", sep="_"), row.names=FALSE)
```

Do any Ls need to be OS?
```{r}
#find all Ls that have a secondary behavior M
surveys_2020 %>%
  filter(Behavior == "L") %>%
  filter(Secondary_Behavior == "M") -> OSs
```

Do all MCRT notes mention which male is on top?
```{r}
#find all MCRTs and read through the notes
surveys_2020 %>%
  filter(Behavior == "MCRT") -> MCRTs

#Nope! not much we can do about this
```

#Double check deaths - make sure beetle is not seen after it died.

#Confirm that beetles didn't move.
Make tables in the BeetleID and PartnerID error sheets to find beetles that we incorrectly recorded in the same condo multiple times. Check to see if these beetles were no longer seen in the correct (home) condo.
```{r}
read.csv("BeetleIDErrors_20210622_.csv") %>%
  select(Condo, X_fkfieldID_surveys) %>%
  rename("Beetle" = X_fkfieldID_surveys) -> IDErrors 

read.csv("BeetlePartnerIDErrors_20210624_.csv") %>%
  select(Condo, Partner) %>%
  rename("Beetle" = Partner) -> PartnerIDErrors 

rbind(IDErrors, PartnerIDErrors) -> AllErrors 

table(AllErrors$Condo, AllErrors$Beetle)
```

#Look at Post-Its

#Sleuth
When almost all the male or female beetles in a condo are found, we can sleuth to figure out any UKs (not doing this in 2019).

#Things to Consider When Analyzing this Data
+How should we deal with dead beetles? Do you remove beetles that died from the dataset entirely when making social networks?
+How should we deal with UK beetles?
+How should we deal with beetles that moved?

# ===== REPEAT ========
After dealing with notes, deaths, etc, run a final round of error-checking and edits. 
