Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am trying to develop a function that will calculate a mean lat/long for my missing or zeros location (lat/long) data in my dataset.

I have locations for many SETS within the same TRIP, which tends to be close in space. However, in many cases, I am missing the location information for some of my SETS. I would like to replace these NAs or Zeros values with the mean lat/long calculated from the remaining SETS within that TRIP.

> head(newdata[,c(3,12,26:27)])#This is my data
        TRIP_ID SET_NO SH_LATITUDE SH_LONGITUDE
13365 100020848      3    41.88983    -66.29183
13373 100020848      6    41.93317    -66.29767
13430 100020848     11    42.01200    -66.45217
13442 100020848      7    41.92383    -66.29733
23207 100020848      4    41.94817    -66.29750
24669 100020848      9    41.88300    -66.30567

So for example, SH_LONGITUDE has 17239 SET_NO with missing values from different TRIP_ID.

> look<-newdata[newdata$SH_LONGITUDE==0,]
> dim(look)
[1] 17239   130
> head(table(look$TRIP_ID))

100020997 100021109 100021204 100021306 100021337 100021340 
        1         2         1         1         2         1 

I am a newbie with user-defined functions, but this is what I would like to be able to call for.

replacebymean<-function(dat, x...?) {
    if dat[dat$x==0, ] or dat[is.na(dat$x), ] 
    then "find these NAs/zeros associated TRIP_ID" and 
          calculate the mean lat/long for each TRIP_ID as  
             mean(dat$x[dat$TRIP_ID=="e.g.,100022478"], na.rm=T) and 
          replace these NAs/zeros by this mean value.
}
share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

Here's how I'd replace zero and NA values with trip means:

# Strings ain't factors
options(stringsAsFactors = FALSE)


# Set up some fake data
fakedat <- data.frame(trip_id = c(rep("Trip A", 10), rep("Trip B", 10)),
                      set_no = c(1:10, 1:10),
                      sh_lat = rnorm(n = 20, mean = 41, sd = 1),
                      sh_long = rnorm(n = 20, mean = 66, sd = 1)
)


# Set a few in each trip to zero and NA
fakedat$sh_lat[c(3, 7, 17)] <- 0
fakedat$sh_lat[c(6, 14, 20)] <- NA
fakedat$sh_long[c(4, 5, 13)] <- 0
fakedat$sh_long[c(2, 8, 19)] <- NA


# Function to replace zero and NA by the mean value of the vector
replacebymean <- function(x, replacevals = c(0, NA)) {

    # Get the mean of the vector, excluding the values you intend to replace
    vecmean <- mean(x[!x %in% replacevals])

    # Replace all of those values with the vector mean
    x.fill <- x
    x.fill[x.fill %in% replacevals] <- vecmean

    # Return the filled vector
    x.fill

}


# Use some kind of split-apply-combine to iterate over the trips
# ddply from the plyr package makes it pretty easy to do this in one pass
library(plyr)

filldat <- ddply(.data = fakedat, .var = "trip_id", .fun = function(x) {

    # Inside of the function(x) call, x represents the trip_id subsets - 
    # ddply will take care of the iteration and recombination,
    # you just have to worry about writing code for one subset

    # Fill both lat and long - you could overwrite them instead of making
    # new variables, but this is more transparent
    x$sh_lat_fill <- replacebymean(x$sh_lat)
    x$sh_long_fill <- replacebymean(x$sh_long)

    # Return
    x

})

I think this is what you were going for, but I was a little confused by the question. Here are a couple for you:

  • Do you actually have records for the sets with missing info? The discontinuity of the set numbers in your example data makes me wonder if you aren't missing entire records - quite a different problem.

  • "Trip" implies sequential visits, in which case it might make more difference to average the adjacent sets. For example, if the Set 5 latitude was missing, would it make more sense to take the average of the latitudes of Sets 4 and 6?

share|improve this answer
    
Thank you @Matt Parker for this answer! To answer you question 1) Yes, I do, but I also have a lot of missing records for sets. 2) Agreed, it would make a lot of sense to average lat/long of the adjacent sets. However, might be difficult to do since I'm missing a lot of records. Would you have suggestions? –  GodinA Oct 15 '12 at 12:43
1  
@GodinA I think that your best bet in that regard would be to start thinking of your data as two correlated time series. I don't have much experience with time series data myself, but the topic of missing data in time series comes up a lot. Check out this search on stats.stackexchange.com - I think that'll get you started, at least. –  Matt Parker Oct 16 '12 at 15:28
add comment

You can use the is.na function to select for values which are NA. Eg :

> t<-c(1:10,NA,1:5)
> t
 [1]  1  2  3  4  5  6  7  8  9 10 NA  1  2  3  4  5
> mean(t)
[1] NA
> mean(t, na.rm=T)
[1] 4.666667
> t[is.na(t) ]<-mean(t, na.rm=T)
> t
 [1]  1.000000  2.000000  3.000000  4.000000  5.000000  6.000000  7.000000
 [8]  8.000000  9.000000 10.000000  4.714286  1.000000  2.000000  3.000000
[15]  4.000000  5.000000

So in your case:

dat$x[dat$TRIP_ID=="e.g.,100022478"&is.na(dat$x)] <-mean(dat$x[dat$TRIP_ID=="e.g.,100022478"], na.rm=T)

You could use a loop to iterate over your TRIP_ID values:

for(trip in unique(dat$TRIP_ID)){
dat$x[dat$TRIP_ID==trip&is.na(dat$x)] <-mean(dat$x[dat$TRIP_ID==trip], na.rm=T)
}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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