1

(I would prefer using R if possible)

I have a table with coordinates (in latitude, longitude), time information (YYYY-mm-dd HH:MM:SS) and ID information (example provided). I sometimes have multiple coordinates in a one minute window for one ID. I would like to produce a table with continuous time (by="min") with:

  • NA: if no coordinates are recorded at this particular time this particular ID.
  • coordinate: if there is only one coordinate in the one minute window.
  • centroid: if there are multiple coordinates

Example coordinate table:

Coordinates<-data.frame(GMT_Date= c("2017-04-16 15:15:00","2017-04-16 15:15:00","2017-04-16 15:15:00","2017-04-16 16:33:00","2017-04-16 16:33:00","2017-04-16 16:33:00","2017-04-16 16:33:00"),
                        Latitude= c(15.0,15.2,15.4,16.0,16.5,16.3,16.2),
                        Longitude= c(30.2,30.1,30.2,31.5,31.6,31.8,31.9),
                        ID= c("id1","id1","id1","id2","id2","id1","id1"))

Example continuous time table:

startDate<-as.POSIXct("2017-04-16 00:01:00",format="%Y-%m-%d %H:%M")
endDate<-as.POSIXct("2017-05-23 23:59:00", format="%Y-%m-%d %H:%M")
time.table<-data.frame(GMT_date= seq(startDate,endDate,by="min"))
head(time.table)

Expected final output:

output.expected<-data.frame(GMT_date=c("2017-04-16 00:01:00","2017-04-16 15:15:00","2017-04-16 16:33:00"),
id1_centroid= c("NA","Latitude, Longitude", "Latitude, Longitude"),
id2_centroid=c("NA","NA","Latitude, Longitude"))
1

Initial sketch using aggregate:

# Test table for raw coordinates
raw.coord<-data.frame(tm= c("2017-04-16 15:15:01","2017-04-16 15:15:02",
                        "2017-04-16 15:15:00",
                        "2017-04-16 16:33:00","2017-04-16 16:33:01",
                        "2017-04-16 16:33:02","2017-04-16 16:33:04"),
                  lat= c(15.0,15.2,15.4,16.0,16.5,16.3,16.2),
                  lon= c(30.2,30.1,30.2,31.5,31.6,31.8,NA),
                  id= c("id1","id1","id1","id2","id2","id1","id1"))

# Create plain factors with minute granularity ..the merge key
raw.coord$ptm <- trunc(as.POSIXct(raw.coord$tm),unit="mins")

# Declare time factors for the aggregation ..should be improved
raw.coord$yr  <- (raw.coord$ptm)$year+1900
raw.coord$mo  <- (raw.coord$ptm)$mon+1
raw.coord$dy  <- (raw.coord$ptm)$mday
raw.coord$hr  <- (raw.coord$ptm)$hour
raw.coord$mn  <- (raw.coord$ptm)$min

# Aggregate positions with all time columns 
# for the choosen factors
avg.coord <- data.frame(
 tm  = unique(raw.coord$ptm),
 lat = aggregate(lat ~ yr+mo+dy+hr+mn, data = raw.coord, FUN=mean)$lat,
 lon = aggregate(lon ~ yr+mo+dy+hr+mn, data = raw.coord, FUN=mean)$lon
)
head(avg.coord)

# Construct the interval table with all timestamps between
# I change the interval because your setup produces 
# of too many NA's for an example 
sdt<-as.POSIXct("2017-04-16 15:14:00",format="%Y-%m-%d %H:%M")
edt<-as.POSIXct("2017-04-16 16:35:00", format="%Y-%m-%d %H:%M")
trj.coord <- data.frame(tm= seq(sdt,edt,by="min"))
trj.coord <- merge( trj.coord, avg.coord, by="tm", all=TRUE)

# Show the results
head(trj.coord)
tail(trj.coord)

Results

> head(trj.coord)
                   tm  lat      lon
1 2017-04-16 15:14:00   NA       NA
2 2017-04-16 15:15:00 15.2 30.16667
3 2017-04-16 15:16:00   NA       NA
4 2017-04-16 15:17:00   NA       NA
5 2017-04-16 15:18:00   NA       NA
6 2017-04-16 15:19:00   NA       NA
> tail(trj.coord)
                    tm   lat      lon
77 2017-04-16 16:30:00    NA       NA
78 2017-04-16 16:31:00    NA       NA
79 2017-04-16 16:32:00    NA       NA
80 2017-04-16 16:33:00 16.25 31.63333
81 2017-04-16 16:34:00    NA       NA
82 2017-04-16 16:35:00    NA       NA

I'm shure there is a R-way to avoid the expansion of the datetime fields yr+mo+dy+hr+mn and use the timestamps tm as a factor.

EDIT: To answer the ID context in the comment below, you cold bring in the ID with an non unique setup per minute into one field. I suggest to collect the and sort the ID's in this function:

# paste together the sorted qunique ID's 
punique<-function (id) { 
   return (paste(sort(unique(id)),collapse=' '))
}; 

The rest works like the other aggregations in the intermediate step for table avg.coord:

 id = aggregate(id ~ yr+mo+dy+hr+mn, data = raw.coord, FUN=punique)$id...

The other possibility is to treat the ID's as a factor in the aggregation:

 id = aggregate(id ~ yr+mo+dy+hr+mn+id, data = raw.coord, FUN=unique)$id...

but get an different granularity (addressing) you expected in your question.

  • Hi! Thanks for this solution. Do you have any idea how to add the ID as aggregation condition? – Luc Sep 11 '17 at 6:04
  • @Luc" I've add the ID comment to the answer. – huckfinn Sep 15 '17 at 2:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.