merging tables by columns AND row names (coordinates)

merge(Table1, Table2, 
   by = intersect(c("XCOORD", "YCOORD"), 
   c("XCOORD", "YCOORD")), all = TRUE)

It might not handle the amount of data you have, but,
if your tables are normal dataframes, it would do the
job with a smaller dataset. It doesn't work with
Spatial*DataFrames (yet?).

Mikkel

--- Roger Bivand <Roger.Bivand at nhh.no> wrote:

On Fri, 8 Sep 2006, Michael Sumner wrote:

Hello, I can think of a couple of simple-minded

approaches that would 

take some time - either relying on direct

string-matching for the unique 

coordinates, or by some contrived overlay.

However, there's probably far better approaches -

a couple of questions:

Can you predefine the set of all unique

coordinates without reading all 

the tables from file? 
 - if so you might simplify the identification of

each individual 

coordinate, for matching the records

Are the coordinates (intended to be) on a regular

grid?  (This seems 

unlikely, although it is nearly true given your X

coordinates).

The key question is what the data are. To me they
look like a global 
regular grid with some slippage in the print() - the
underlying diff() of 
the unique x's and y's is almost certainly regular.
I'm not sure why they 
are in text files either (model output?). But some
bits of the grid may be 
missing, the question being whether this is regular.
If as an earlier 
response indicated different data sets have
different grid cells 
missing, then we need the overall grid to start
with, then grab the row 
and column indices (and/or grid index), and attach
these to the data rows. 

If the solution needs to be robust, and have a
longer term utility, I 
would go for using MySQL, Terralib, and aRT. The
data representation is 
that of the Terralib Cell object, so the question
would be how to upload 
to the database from the text files.

aRT is at:

http://www.est.ufpr.br/aRT/

By the way, 1M by 100 by 8 bytes is pushing 32-bit R
- but handing off a 
lot of the data storage to a database relieves this
greatly.

Roger

Cheers, Mike.


isidora k wrote:

Hi everyone!
I have 100 tables of the form:
XCOORD,YCOORD,OBSERVATION
27.47500,42.52641,177
27.48788,42.52641,177
27.50075,42.52641,179
27.51362,42.52641,178
27.52650,42.52641,180
27.53937,42.52641,178
27.55225,42.52641,181
27.56512,42.52641,177
27.57800,42.52641,181
27.59087,42.52641,181
27.60375,42.52641,180
27.61662,42.52641,181
..., ..., ...
with approximately 1000000 observations for

each. All

these tables have the same xcoord and ycoord and

I

would like to get a table of the form
XCOORD,YCOORD,OBSERVATION1,OBSERVATION2,... 
27.47500,42.52641,177,233,...
27.48788,42.52641,177,345,...
27.50075,42.52641,179,233,...
27.51362,42.52641,178,123,...
27.52650,42.52641,180,178,...
27.53937,42.52641,178,...,...
27.55225,42.52641,181,...
27.56512,42.52641,177,...
27.57800,42.52641,181,...
27.59087,42.52641,181,...
27.60375,42.52641,180,...
27.61662,42.52641,181,...
In other words I would like to merge all the

tables

taking into account the common row names of

their

xcoords AND ycoords.
Not all tables have the same number of

observations

which means that not all pairs of x and y coords
match.
Is there a way to do this in R?
I would be grateful for any advice.
Many thanks
Isidora

-- 
Roger Bivand
Economic Geography Section, Department of Economics,
Norwegian School of
Economics and Business Administration, Helleveien
30, N-5045 Bergen,
Norway. voice: +47 55 95 93 55; fax +47 55 95 95 43
e-mail: Roger.Bivand at nhh.no