Importing data from existing Excel sheets

[Roberto Salom]

Hi Juergen,

Is there an easy way to incorporate data that has already been entered into Excel (please see attachment)? Would it suffice to have the same columns generated with the RecordTable function? I imagine there would be some limitations into what you can do, since you can´t read the metadata from the pictures...

[Juergen Niedballa]

Hi Roberto,
the table you attached is very similar to what the function recordTable will give as output, and you can make it usable in camtrapR easily. The bare minimum you need in that record table is a station column (you have that), a species column (you got that too), and a Date/time column (which you can create easily from the two separate columns you have). This will give the essential information about which species was seen where and when.

Here's the steps you need to do:

1. load table into R, preferably from a csv file to avoid Excel causing a mess (could easily happen in your Time column with Excel interpreting as dates).

2. make a date/time column. Assuming you load this table as a data.frame called records, you can create that column with something like:
records$DateTimeOriginal <- strptime(paste(records$Date, records$Time, sep = " "), format = "%d-%b-%y %H:%M")     # i didn't test this. If it doesn't work check ?strptime

3. Create an equivalent to the camera trap station table that is returned by data(camtraps)   (note the Problem columns are not necessary, and station column values in this table must match values of station column in your record table. This is also detailed in the package vignette)

That should be it. By not extracting data from your images, you will not be able to get additional metadata information that are stored in the images, be it technical information or some tags you assigned to the images. But you probably don't necessarily need them and you'll be able to use most functions in the package this way.

[Roberto Salom]

Hi Juergen,

 I´ll try that and let you know how it goes. I tried a couple of times and I got an error message saying that the table doesn´t have a Station column which I clearly have as you saw. Not sure why it tells me that, but I´ll try again.

Best,

Roberto

[Juergen Niedballa]

Hi Roberto,
would you please send the code you ran and the error message it produced?
And please send the output of

str(records)      # records is the record table you showed above

Thank you,
Jürgen

[Roberto Salom]

Hi Jürgen,

 

 I didn´t get that error message anymore, now I´m getting a different one:

detHist <- detectionHistory(recordTable         = BIDprueba,

+                              camOp                = camop_problem,

+                              stationCol           = "Station",

+                              speciesCol           = "Species",

+                              recordDateTimeCol    = "DateTimeOriginal",

+                              species              = "Nasua narica",

+                              occasionLength       = 7, #numero de dias por ocasion

+                              day1                 = "survey",

+                              includeEffort        = TRUE,

+                              scaleEffort = FALSE,

+                              writecsv = TRUE,

+                              outDir = "C:/Users/Panthera01/Documents/Trabajo/DATOS_CAMARAS/BID_prueba"

+ )

Error in detectionHistory(recordTable = BIDprueba, camOp = camop_problem,  : 

  at least 1 entry in recordDateTimeCol of recordTable could not be interpreted using recordDateTimeFormat

I´m attaching the record table used.

Thanks,

Roberto

[Roberto Salom]

Hi Juergen,

I found out what was the problem! I had created the DateTimeOriginal column in Excel and R didn´t like that, I used the strp code you provided and it worked nicely!

records$DateTimeOriginal <- strptime(paste(records$Date, records$Time, sep = " "), format = "%d/%m/%Y %H:%M:%S")

Thanks,

Roberto

[Samantha Baraoidan]

Hello,

Similar to Roberto, I am trying to use this package with an existing spreadsheet of data, rather than the raw photos themselves. I made the two required tables as you mentioned in this thread. Now, I am trying to figure out how to use the functions like "recordTable" on my imported data, rather than referencing a directory of images. I am specifically trying to work through the section on "temporal independence between records. Thank you for any help you can provide!

Best,

Sam

[patrick...@brown.edu]

Hi Samantha and Juergen,

First off thanks so much for working on this package! Secondly, I am trying to do the same thing as Samantha above with an existing Excel datasheet that was given to me for an analysis. I am primarily interested in trying to create a delta.time.mins column from existing date/time information so that I can filter out temporally non-independent records. Is this something I'm going to have to hard-code given that I don't have access to the original raw photographs? Thanks in advance for any suggestions! I've attached a sample datasheet below. 

Thank you! 

Patrick Freeman 

[Juergen Niedballa]

Hi Patrick & Samantha,

here's the function that does the job inside recordTable(). You can use it on your existing record tables. 

By popular demand, I'll add it as a standalone function in the next release with proper help file and all. 

For now it is just a quick fix and lacks some flexibility because it is normally called from within the recordTable function. Hence, it does not perform checks to ensure data consistency. It also does not return the data exactly like recordTable does because some of the computation is happening outside the attached function.

Input: 

- argument "cameraCol" is optional

- "minDeltaTime" is in minutes.

- "removeNonIndependentRecords" is TRUE by default and will remove all non-independent records from output. Set to FALSE to return all records.

output:

- input table minus the non-independent records

- additional "delta.time..." columns, but only "delta.time.secs" has data, the rest is NA still (divide by 60  /   60*60   /   60 * 60 * 24 to compute mins / hours / days)

- additional column "independent". records within "minDeltaTime" will be FALSE. Can be used to remove non-independent records

Also note I had to change your data slightly to run it (see below):

- assign values to "Species"

- format column "DateTimeOriginal"

So, source the function in the attached file, then you can run something like the following code to filter your records:

If you run into problems, let me know please. 

Jürgen

records <- read.csv("C:/.../camtrapR_example.csv",

                    stringsAsFactors = FALSE)

# create DateTimeOrginal column in proper format

records$DateTimeOriginal <- strptime(paste(as.Date(records$Date, format = "%m/%d/%y"), 

                                  records$Time), format = "%Y-%m-%d %H:%M:%S", tz = "UTC")

# assign a species 

records$Species <- "lion"

# 

records_filter1_min <- assessTemporalIndependence(intable = records,

                           deltaTimeComparedTo = "lastIndependentRecord",

                           columnOfInterest = "Species",

                           stationCol = "Camera.Trap.name",

                           cameraCol = "Camera.Serial.Number",

                           camerasIndependent = FALSE,

                           minDeltaTime = 1

                           )

records_filter30_min <- assessTemporalIndependence(intable = records,

                                                  deltaTimeComparedTo = "lastIndependentRecord",

                                                  columnOfInterest = "Species",

                                                  stationCol = "Camera.Trap.name",

                                                  cameraCol = "Camera.Serial.Number",

                                                  camerasIndependent = FALSE,

                                                  minDeltaTime = 30

)

View(records)

View(records_filter1_min)

View(records_filter30_min)

# assign 2 different species

records$Species <- c(rep("lion", times = 3),

                     rep("giraffe", times = nrow(records) - 3))

records_filter30_min_2_spec <- assessTemporalIndependence(intable = records,

                                              deltaTimeComparedTo = "lastIndependentRecord",

                                              columnOfInterest = "Species",

                                              stationCol = "Camera.Trap.name",

                                              cameraCol = "Camera.Serial.Number",

                                              camerasIndependent = FALSE,

                                              minDeltaTime = 30

)

View(records_filter30_min_2_spec)

# remove argument cameraCol, and set removeNonIndependentRecords = FALSE

records_filter30_min_2_spec_return_all <- assessTemporalIndependence(intable = records,

                                                          deltaTimeComparedTo = "lastIndependentRecord",

                                                          columnOfInterest = "Species",

                                                          stationCol = "Camera.Trap.name",

                                                          #  cameraCol = "Camera.Serial.Number",

                                                          camerasIndependent = FALSE,

                                                          minDeltaTime = 30,

                                                          removeNonIndependentRecords = FALSE

)

View(records_filter30_min_2_spec_return_all)

[Nubia Zoe Lara]

Hello, 

I am working with a csv file and I have similar problems with column "DateTimeOriginal". I followed the indication to create this column in R (from my "date" and "time" columns) . 

using: Spp_RT$DateTimeOriginal <- strptime(paste(Spp_RT$date, Spp_RT$time, sep = " "), format = "%Y-%m-%d %H:%M:%S", tz = "UTC")

However, it still doesn't work.

So, I decided first todeclare my column "date" as a date class variable, using: Spp_RT$date <- as.Date(Spp_RT$date)

My question is how can I declare my "time" column to be recognized like this... hh: mm: ss, before creating "DateTimeOriginal"? 

I think there could be my mistake, but a I dont´know ... I ´m trying everything to solve this! ;) 

Below, my script and a sample of my data base as attachment.

Thanks!!

My script:

Spp_RT <- read.csv("EXAMPLE.csv", sep = ",", header = T)

head(Spp_RT)

  

Spp_RT <- as.data.frame(Spp_RT)

str(Spp_RT)

head(Spp_RT)

#ajustar tipos de variables##

Spp_RT$camID <- as.character(Spp_RT$camID) # and the same for station and others.

Spp_RT$station <- as.character(Spp_RT$station)

Spp_RT$cuadro <- as.character(Spp_RT$cuadro)

Spp_RT$cuadro <- as.character(Spp_RT$cuadro)

Spp_RT$Short_sp <- as.character(Spp_RT$Short_sp)

Spp_RT$date <- as.Date(Spp_RT$date)

Spp_RT$DateTimeOriginal <- strptime(paste(Spp_RT$date, Spp_RT$time, sep = " "), format = "%Y-%m-%d %H:%M:%S", tz = "UTC")

head(Spp_RT)

DetHist_rf <- detectionHistory(recordTable = Spp_RT,

                 species = "Pan_on",

                 camOp = camop_problem,

                 output = "binary",

                 stationCol = "station",

                 speciesCol = "Short_sp",

                 recordDateTimeCol = "time",

                 recordDateTimeFormat = "%Y-%m-%d %H:%M:%S",

                 occasionLength = 7,

                 occasionStartTime = 6,

                 day1 = "station",

                 includeEffort = TRUE,

                 scaleEffort = TRUE, #because I have stations with 2 cameras

                 datesAsOccasionNames = FALSE,

                 timeZone = "America/Mexico_City",

                 writecsv = TRUE,

                 outDir = "C:/Users/NZLR/Documents/CARLOS/ultima base"

)

[Juergen Niedballa]

Hi Nubia, 

sorry, I missed this message. 

What error message did you get when you ran:

Spp_RT$DateTimeOriginal <- strptime(paste(Spp_RT$date, Spp_RT$time, sep = " "), format = "%Y-%m-%d %H:%M:%S", tz = "UTC")

I'd suggest you make both the "date" and "time" columns character, then use your command above to make it a date/time object?

Looking at your example, I believe the format argument is wrong. Try this:

Spp_RT$DateTimeOriginal <- strptime(paste(Spp_RT$date, Spp_RT$time, sep = " "), format = "%d/%m/%Y %H:%M:%S", tz = "UTC")

The date is formatted as "DD/MM/YYYY", hence "%d/%m/%Y" instead of "%Y-%m-%d".

Let me know if there's still problems:

Best,

Jürgen

[Juliana Vélez]

Hi all,

I did follow the approach suggested in the thread. I sourced the function, formatted time and date and run the function:

source("programs/assessTemporalIndependence.R")

# create DateTimeOrginal column in proper format

speciesRec$DateTimeOriginal <- strptime(speciesRec$DateTimeOriginal, format = "%Y-%m-%d %H:%M:%S", tz = "America/Bogota")

# assign a species
speciesRec$Species <- "terrestris"

#
records_filter60_min <- assessTemporalIndependence(intable = speciesRec,

                                                  deltaTimeComparedTo = "lastIndependentRecord",
                                                  columnOfInterest = "Species",

                                                  stationCol = "PointId",
                                                  cameraCol = "ctId",
                                                  camerasIndependent = TRUE,
                                                  minDeltaTime = 60
)

I already had DateTime in the same column, so I just gave the proper format and time zone. However although the function removes some records, it's keeping some non-independent records (e.g., 2019-10-26 15:27:24, 2019-10-26 15:26:13 and 2019-10-26 15:27:15), and giving them a delta.time.secs value of "Inf". Do you know which could be the issue?

Thanks for your help,

Juliana

[Juergen Niedballa]

Hi Juliana, 

first of all, instead of sourcing the function that I posted here on the group a long time ago, I'd recommend taking the function straight from the most recent version of package, like this:

camtrapR:::assessTemporalIndependence()

Regarding the time differences being "Inf", I am not sure what the problem is, I didn't encounter this before. My first thought was, could it have to do with the end of daylight saving time that night? But I am really not sure. Would you mind sharing a sample table please to reprododuce the problem (by email if you don't want to go public), I can then check what goes wrong.

And generally, this solution is only a workaround until I find the time to make a new function to filter existing record tables.

Best,

Jürgen

[yogita karpate]

Hi Jurgen,

I have my data organized in CSV file. I tried following your suggestions posted in this thread. I am not able to find the assessTemporalIndependence function in the latest version. Could you please suggest a way around?

Also, is there any difference between the the output from recordTable function and the assessTemporalIndependence function? As far as I understood both use the delta time feature to filter species data. Please correct me if I am wrong.

[Juergen Niedballa]

Hi, 

since it is an internal function, you need to access it with 

camtrapR:::assessTemporalIndependence()

Note that those are 3 colons. If it still doesn't work please share your code. 

And yes, assessTemporalIndependence does the temporal filtering. The results of the filtering should be the same, but the overall output might be a bit different (column order and content). That is because recordTable does lots of things, e.g. read and parse the metadata, which assessTemporalIndependence does not do. 

Best,

Jürgen