Dear all, I have a dataframe of 3 columns, consisting of 'longitude', 'latitude' and a corresponding 'value'. Where identical 'longitude' and 'latitude' pairs occur more than once, I want their corresponding 'value' to be summed and the 'pair' to only appear once. For example: long lat value 10 20 5 6 2 3 27 -3 9 10 20 10 4 -1 0 6 2 9 would be converted to something like: long lat value 10 20 15 6 2 12 27 -3 9 4 -1 0 ...as rows 1 and 4, 2 and 6 respectively are matched with respect to the 'long' and 'lat' columns. Their values in column 3 are then summed and reported as one row in the new dataframe. How would I go about coding this in R? Many thanks, Steve _________________________________________________________________ Are you a PC?? Upload your PC story and show the world
Try this: aggregate(dt$value, list(long = dt$long, lat = dt$lat), FUN = sum) On Mon, Jan 12, 2009 at 1:52 PM, Steve Murray <smurray444@hotmail.com>wrote:> > Dear all, > > > > I have a dataframe of 3 columns, consisting of 'longitude', 'latitude' > and a corresponding 'value'. Where identical 'longitude' and 'latitude' > pairs occur more than once, I want their corresponding 'value' to be > summed and the 'pair' to only appear once. > > > > For example: > > > > long lat value > > 10 20 5 > > 6 2 3 > > 27 -3 9 > > 10 20 10 > > 4 -1 0 > > 6 2 9 > > > > > > would be converted to something like: > > > > long lat value > > > 10 20 15 > > > 6 2 12 > > > 27 -3 9 > > > 4 -1 0 > > > > > > > ...as rows 1 and 4, 2 and 6 respectively are matched with respect to > the 'long' and 'lat' columns. Their values in column 3 are then summed > and reported as one row in the new dataframe. > > > > How would I go about coding this in R? > > > > Many thanks, > > > > Steve > > > _________________________________________________________________ > Are you a PC? Upload your PC story and show the world > > ______________________________________________ > R-help@r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide > http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >-- Henrique Dallazuanna Curitiba-Paraná-Brasil 25° 25' 40" S 49° 16' 22" O [[alternative HTML version deleted]]
try aggregate(), e.g.,
dat <- read.table(textConnection("long lat value
10 20 5
6 2 3
27 -3 9
10 20 10
4 -1 0
6 2 9"), header = TRUE)
closeAllConnections()
aggregate(dat["value"], list(Long = dat$long, Lat = dat$lat), sum)
I hope it helps.
Best,
Dimitris
Steve Murray wrote:> Dear all,
>
>
>
> I have a dataframe of 3 columns, consisting of 'longitude',
'latitude'
> and a corresponding 'value'. Where identical 'longitude'
and 'latitude'
> pairs occur more than once, I want their corresponding 'value' to
be
> summed and the 'pair' to only appear once.
>
>
>
> For example:
>
>
>
> long lat value
>
> 10 20 5
>
> 6 2 3
>
> 27 -3 9
>
> 10 20 10
>
> 4 -1 0
>
> 6 2 9
>
>
>
>
>
> would be converted to something like:
>
>
>
> long lat value
>
>
> 10 20 15
>
>
> 6 2 12
>
>
> 27 -3 9
>
>
> 4 -1 0
>
>
>
>
>
>
> ...as rows 1 and 4, 2 and 6 respectively are matched with respect to
> the 'long' and 'lat' columns. Their values in column 3 are
then summed
> and reported as one row in the new dataframe.
>
>
>
> How would I go about coding this in R?
>
>
>
> Many thanks,
>
>
>
> Steve
>
>
> _________________________________________________________________
> Are you a PC? Upload your PC story and show the world
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
--
Dimitris Rizopoulos
Assistant Professor
Department of Biostatistics
Erasmus Medical Center
Address: PO Box 2040, 3000 CA Rotterdam, the Netherlands
Tel: +31/(0)10/7043478
Fax: +31/(0)10/7043014
One of undoubtedly many ways: # kind of a pain to delete the blank rows. Try to give us full example next time? > txt<- "long lat value + 10 20 5 + 6 2 3 + 27 -3 9 + 10 20 10 + 4 -1 0 + 6 2 9 + " > DF2 <- read.table(textConnection(txt), header=TRUE) > DF3 <- data.frame(with(DF2,list(lon=long, lat=lat, value=ave(value,long, lat, FUN=sum)))) > DF3 lon lat value 1 10 20 15 2 6 2 12 3 27 -3 9 4 10 20 15 5 4 -1 0 6 6 2 12 If you want the column name to be "long", the fix is obvious. -- David Winsemius On Jan 12, 2009, at 10:52 AM, Steve Murray wrote:> > Dear all, > > > > I have a dataframe of 3 columns, consisting of 'longitude', 'latitude' > and a corresponding 'value'. Where identical 'longitude' and > 'latitude' > pairs occur more than once, I want their corresponding 'value' to be > summed and the 'pair' to only appear once. > > > > For example: > > > > long lat value > > 10 20 5 > > 6 2 3 > > 27 -3 9 > > 10 20 10 > > 4 -1 0 > > 6 2 9 > > > > > > would be converted to something like: > > > > long lat value > > > 10 20 15 > > > 6 2 12 > > > 27 -3 9 > > > 4 -1 0 > > > > > > > ...as rows 1 and 4, 2 and 6 respectively are matched with respect to > the 'long' and 'lat' columns. Their values in column 3 are then summed > and reported as one row in the new dataframe. > > > > How would I go about coding this in R? > > > > Many thanks, > > > > Steve > > > _________________________________________________________________ > Are you a PC? Upload your PC story and show the world > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.