i have data set of gps coordinates, of times missing. want add missing rows in appropriate timestamp , na's lat , lon columns
this how data formatted:
timestamp lon lat id date time <time> <dbl> <dbl> <int> <date> <chr> 1 2012-08-01 03:59:00 36.92288 0.3508941 1 2012-08-01 03:59:00 2 2012-08-01 03:59:01 36.92288 0.3508901 1 2012-08-01 03:59:01 3 2012-08-01 03:59:02 36.92288 0.3508868 1 2012-08-01 03:59:02 4 2012-08-01 03:59:03 36.92288 0.3508828 1 2012-08-01 03:59:03 5 2012-08-01 03:59:05 36.92288 0.3508845 1 2012-08-01 03:59:05 6 2012-08-01 03:59:06 36.92288 0.3508866 1 2012-08-01 03:59:06 7 2012-08-01 03:59:07 36.92288 0.3508885 1 2012-08-01 03:59:07 8 2012-08-01 03:59:08 36.92288 0.3508903 1 2012-08-01 03:59:08 9 2012-08-01 03:59:09 36.92288 0.3508915 1 2012-08-01 03:59:09 as can see timestamp 2012-08-01 03:59:04 missing have @ end
timestamp lon lat id date time <time> <dbl> <dbl> <int> <date> <chr> 1 2012-08-01 03:59:00 36.92288 0.3508941 1 2012-08-01 03:59:00 2 2012-08-01 03:59:01 36.92288 0.3508901 1 2012-08-01 03:59:01 3 2012-08-01 03:59:02 36.92288 0.3508868 1 2012-08-01 03:59:02 4 2012-08-01 03:59:03 36.92288 0.3508828 1 2012-08-01 03:59:03 5 2012-08-01 03:59:04 na na 1 2012-08-01 03:59:04 6 2012-08-01 03:59:05 36.92288 0.3508845 1 2012-08-01 03:59:05 7 2012-08-01 03:59:06 36.92288 0.3508866 1 2012-08-01 03:59:06 8 2012-08-01 03:59:07 36.92288 0.3508885 1 2012-08-01 03:59:07 9 2012-08-01 03:59:08 36.92288 0.3508903 1 2012-08-01 03:59:08 10 2012-08-01 03:59:09 36.92288 0.3508915 1 2012-08-01 03:59:09 any appreciate!
use rbind.fill plyr package:
ts_data <- read.table(text="timestamp,lon,lat,id,date,time 2012-08-01 03:59:00,36.92288,0.3508941,1,2012-08-01,03:59:00 2012-08-01 03:59:01,36.92288,0.3508901,1,2012-08-01,03:59:01 2012-08-01 03:59:02,36.92288,0.3508868,1,2012-08-01,03:59:02 2012-08-01 03:59:03,36.92288,0.3508828,1,2012-08-01,03:59:03 2012-08-01 03:59:05,36.92288,0.3508845,1,2012-08-01,03:59:05 2012-08-01 03:59:06,36.92288,0.3508866,1,2012-08-01,03:59:06 2012-08-01 03:59:07,36.92288,0.3508885,1,2012-08-01,03:59:07 2012-08-01 03:59:08,36.92288,0.3508903,1,2012-08-01,03:59:08 2012-08-01 03:59:09,36.92288,0.3508915,1,2012-08-01,03:59:09", header=t, stringsasfactors=f, sep=",") ##convert timestamp datetime ts_data$timestamp <- ts_data$timestamp <- as.posixct(strftime(ts_data$timestamp)) ##get full sequence full_sequence <- seq(from=min(ts_data$timestamp), to=max(ts_data$timestamp), by="s") ##grab missing sequence missing_sequence <- full_sequence[!(full_sequence %in% ts_data$timestamp)] ##make data.frame out of missing sequence missing_df <- data.frame(timestamp = missing_sequence, id = rep(1,length(missing_sequence)), date = strftime(missing_sequence, format = "%y-%m-%d"), time=strftime(missing_sequence, format = "%h:%m:%s")) ##combine 2 new_ts_data <- plyr::rbind.fill(ts_data, missing_df) ##order timestamp new_ts_data <- new_ts_data[order(new_ts_data$timestamp),] desired output:
timestamp lon lat id date time 2012-08-01 03:59:00 36.92288 0.3508941 1 2012-08-01 03:59:00 2012-08-01 03:59:01 36.92288 0.3508901 1 2012-08-01 03:59:01 2012-08-01 03:59:02 36.92288 0.3508868 1 2012-08-01 03:59:02 2012-08-01 03:59:03 36.92288 0.3508828 1 2012-08-01 03:59:03 2012-08-01 03:59:04 na na 1 2012-08-01 03:59:04 2012-08-01 03:59:05 36.92288 0.3508845 1 2012-08-01 03:59:05 2012-08-01 03:59:06 36.92288 0.3508866 1 2012-08-01 03:59:06 2012-08-01 03:59:07 36.92288 0.3508885 1 2012-08-01 03:59:07 2012-08-01 03:59:08 36.92288 0.3508903 1 2012-08-01 03:59:08 2012-08-01 03:59:09 36.92288 0.3508915 1 2012-08-01 03:59:09 edit work appropriate dataset
Comments
Post a Comment