title: R remove duplicated values based on a column and replace other column values with the mean of duplicated rows
body: <p>I'm working with a data.frame which has 6 environmental variables of interest which are georeferenced by location.  The problem I have is that some of the locations are duplicated but all the environmental variables are unique measurements. </p>
  
  <p>Unfortunately, the modelling I want to do with these data will not work if there are duplicate locations. But I do not wish to arbitrarily throw away data by keeping only one duplicated row. </p>
  
  <p>So, I'm looking for a method of taking the means for each of the 6 variables for each set of duplicates and then ascribing that mean to each variable and the location thereby preserving the information from the multiple measurements. </p>
  
  <p>I've attempted this in several ways but I can't quite seem to get it right! </p>
  
  <p>The data I'm working with can be downloaded here:<br>
  (<a href="https://www.dropbox.com/sh/xnwp3zz5abnilyo/AABRVJZ0kTmWk0T9Fcp4-bVSa?dl=0/" rel="nofollow noreferrer">https://www.dropbox.com/sh/xnwp3zz5abnilyo/AABRVJZ0kTmWk0T9Fcp4-bVSa?dl=0/</a>)</p>
  
  <p>This is how I've attempted this :</p>
  
  <pre><code>    library(rgdal)
      library(sp)
      library(maptools)
  
   #load data 
  
  hs1&lt;- readOGR (".", "Hollicombe_S1_L1-5_A1.2")
  
  #remove columns we're not interested in
  
  hs1&lt;- subset(hs1, select = -c(1:16, 23:24)
  </code></pre>
  
  <p>So I start with hs1 - a SPDF with 552 obs and 6 variables...</p>
  
  <pre><code>    #check for duplicate location (present if lengths differ)
  length(hs1@coords) 
  [1] 1104  
  length(unique(hs1@coords))
  [1] 730
  #duplicates confirmed
  
  hs1.d &lt;- hs1[duplicated(hs1@coords),] # creates new SPDF with only duplicated locations (?)
  hs1.u &lt;- hs1[!duplicated(hs1@coords),] # creates new SPDF with only unique locations
  
  # coerce duplicated locations SPDF to an ordinary data frame 
  
  hs1.md&lt;- as.data.frame(hs1.d)  
  
  # combine the X&amp;Y into a single "location"
  hs1.md &lt;- within(hs1.md,  
    Location &lt;- paste(coords.x1, coords.x2, sep = ",")) 
  
  # aggregate duplicate locations and calculate a mean value for each
  
  means_by_location&lt;-  aggregate (cbind(BioArea,BioVolume,MeanBioHei,MaxBioheig,PerArIn, PerVolIn)~Location,  hs1.md, mean)
  
  #split location back to X&amp;Y
  
  lat_long &lt;- strsplit(means_by_location$Location, ",") 
  means_by_location$coords.x1 &lt;- sapply(lat_long, function(x) x[1]) #adds X data back
  means_by_location$coords.x2 &lt;- sapply(lat_long, function(x) x[2])#adds Y data back
  means_by_location$coords.x1 &lt;- as.numeric (means_by_location$coords.x1) #converts to numeric
  means_by_location$coords.x2 &lt;- as.numeric (means_by_location$coords.x2)#converts to numeric
  
  # add spatial information back in to create SPDF
  
  coordinates(means_by_location) = ~coords.x1+coords.x2 # adds the locations 
  proj4string(means_by_location) = CRS(proj4string(hs1)) # sets the CRS
  
  # hs1.md as SPDF containing single rows for previously duplicated locations 
  # with mean values for each variable
  
  hs1.md &lt;- subset(means_by_location, select = -(1))  
  
  #merge hs1.md and hs1.u to create new SPDF without duplicates
  
  hs1 &lt;- spRbind (hs1.u, hs1.md)
  </code></pre>
  
  <p>So hs1 is now a SPDF with 543 obs (i.e. 9 observations have been removed).</p>
  
  <p>But there still remain duplicate locations and the number of unique locations remains the same :</p>
  
  <pre><code>length(hs1@coords) # total number of locations
  </code></pre>
  
  <p>[1] 1086</p>
  
  <pre><code>length(unique(hs1@coords)) #number of unique locations
  </code></pre>
  
  <p>[1] 730</p>
  
  <p>I suspect I've incorrectly seperated the unique from the duplicated observations somewhere but my knowledge of R is not sufficient enough me to spot this. Can anyone see where I have gone wrong? Or does anybody know an alternative way I can achieve this? </p>