thr3ads.net - R help - [R] combining data from different datasets [Oct 2008]

If this information is useful, please help other people find it:
Share via:

Dr Eberhard W Lisse

2008-Oct-24 15:01 UTC

[R] combining data from different datasets

Hi,

I have two tables:


 > iso
     continent code code3 codenum                          country
1          EU   AD   AND      20         Andorra, Principality of
2          AS   AE   ARE     784             United Arab Emirates
3          AS   AF   AFG       4 Afghanistan, Islamic Republic of
4          NA   AG   ATG      28              Antigua and Barbuda
5          NA   AI   AIA     660                         Anguilla
159        AF   NA   NAM     516             Namibia, Republic of
...
246        AF   ZW   ZWE     716            Zimbabwe, Republic of



and

 > rawdata
    idno alter  sex cctld capacity topics content speakers
1     1    NA    M    AD        A      2       1        2
2     2    52    M  <NA>        A      3       2        2
3     3    39    F    NA        T      2       2        2
...

How do I make it print out something like

	alter	sex	country
1	NA	M	Andorra, Principality of
2	39	F	Namibia, Republic of
...

in other words do something like the following SQL:

SELECT r.alter, r.sex, i.country
FROM iso i, rawdata r
WHERE i.code = r.cctld

or even better:

	alter	sex	country
1	NA	M	Andorra, Principality of
2	52	M	<NA>
3	39	F	Namibia
...

And idea (where I can read up on this)?


el
--
Dr. Eberhard W. Lisse  \        / Obstetrician & Gynaecologist (Saar)
el at lisse.NA el108-ARIN /   *   |   Telephone: +264 81 124 6733 (cell)
PO Box 8421             \     / Please send DNS/NA-NiC related e-mail
Bachbrecht, Namibia     ;____/             to dns-admin at na-nic.com.na

Gabor Grothendieck

2008-Oct-24 15:16 UTC

head link

[R] combining data from different datasets

Here are two solutions.  The first uses the R merge command
and the second uses the R sqldf package.  See ?merge
and http://sqldf.googlecode.com
Note that alter is an sql keyword so I have changed it
to alt for the second example:
> merge(iso, rawdata)[c("alt", "sex",
"country")]  alter sex country
1    NA   M Andorra
2    39   F Namibia> library(sqldf)
> sqldf("select alt, sex, country from iso join rawdata
using(country)")  alt sex country
1  NA   M Andorra
2  39   F Namibia


On Fri, Oct 24, 2008 at 11:01 AM, Dr Eberhard W Lisse <el at lisse.na>
wrote:> Hi,
>
> I have two tables:
>
>
>> iso
>    continent code code3 codenum                          country
> 1          EU   AD   AND      20         Andorra, Principality of
> 2          AS   AE   ARE     784             United Arab Emirates
> 3          AS   AF   AFG       4 Afghanistan, Islamic Republic of
> 4          NA   AG   ATG      28              Antigua and Barbuda
> 5          NA   AI   AIA     660                         Anguilla
> 159        AF   NA   NAM     516             Namibia, Republic of
> ...
> 246        AF   ZW   ZWE     716            Zimbabwe, Republic of
>
>
>
> and
>
>> rawdata
>   idno alter  sex cctld capacity topics content speakers
> 1     1    NA    M    AD        A      2       1        2
> 2     2    52    M  <NA>        A      3       2        2
> 3     3    39    F    NA        T      2       2        2
> ...
>
> How do I make it print out something like
>
>        alter   sex     country
> 1       NA      M       Andorra, Principality of
> 2       39      F       Namibia, Republic of
> ...
>
> in other words do something like the following SQL:
>
> SELECT r.alter, r.sex, i.country
> FROM iso i, rawdata r
> WHERE i.code = r.cctld
>
> or even better:
>
>        alter   sex     country
> 1       NA      M       Andorra, Principality of
> 2       52      M       <NA>
> 3       39      F       Namibia
> ...
>
> And idea (where I can read up on this)?
>
>
> el
> --
> Dr. Eberhard W. Lisse  \        / Obstetrician & Gynaecologist (Saar)
> el at lisse.NA el108-ARIN /   *   |   Telephone: +264 81 124 6733 (cell)
> PO Box 8421             \     / Please send DNS/NA-NiC related e-mail
> Bachbrecht, Namibia     ;____/             to dns-admin at na-nic.com.na
>
> ______________________________________________
> 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.
>

Dr Eberhard W Lisse

2008-Oct-24 15:37 UTC

head link

[R] combining data from different datasets

Gabor,

Thank you,

On 24 Oct 2008, at 17:16 , Gabor Grothendieck wrote:
> Here are two solutions.  The first uses the R merge command
> and the second uses the R sqldf package.  See ?merge
> and http://sqldf.googlecode.com
> Note that alter is an sql keyword so I have changed it
> to alt for the second example:
>
>> merge(iso, rawdata)[c("alt", "sex",
"country")]
>  alter sex country
> 1    NA   M Andorra
> 2    39   F Namibia
merge(rawdata, iso, by.x = "cctld", by.y = "code", all.x =
TRUE)
	[c("alter","sex", "cctld", "country")]

all.x = TRUE even pulls the cases with missing values
>
>> library(sqldf)
>
>> sqldf("select alt, sex, country from iso join rawdata  
>> using(country)")
>  alt sex country
> 1  NA   M Andorra
> 2  39   F Namibia
This looks very cool.

But I must still make a plan with regards to country = "NA" (Namibia)
or continent = "NA" (North America)

But there are the vignettes.

el

Gabor Grothendieck

2008-Oct-24 16:24 UTC

head link

[R] combining data from different datasets

On Fri, Oct 24, 2008 at 11:37 AM, Dr Eberhard W Lisse <el at lisse.na>
wrote:>
> This looks very cool.
>
> But I must still make a plan with regards to country = "NA"
(Namibia)
> or continent = "NA" (North America)
>
> But there are the vignettes.
>
> el
>
NA and "NA" are not the same:
> DF <- data.frame(x = c("a", "NA", NA))
> DF     x
1    a
2   NA
3 <NA>>
> is.na(NA)
[1] TRUE> is.na("NA")[1] FALSE

Daniel Malter

2008-Oct-24 19:24 UTC

head link

[R] combining data from different datasets

?merge.

It looks though that your "iso" has no identifier variable whereas the
"rawdata" has, so you probably cannot merge it unless/until you have
an
identifier in "iso". If you know that all data is already ordered
accordingly, you can just bind the columns together in a new object like
this:

newdata=data.frame(rawdata$alter,rawdata$sex,iso$country)
newdata #return newdata

Cheers,
Daniel


-------------------------
cuncta stricte discussurus
-------------------------

-----Urspr?ngliche Nachricht-----
Von: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] Im
Auftrag von Dr Eberhard W Lisse
Gesendet: Friday, October 24, 2008 11:02 AM
An: R-help Mailing List
Cc: Dr Eberhard W Lisse
Betreff: [R] combining data from different datasets

Hi,

I have two tables:


 > iso
     continent code code3 codenum                          country
1          EU   AD   AND      20         Andorra, Principality of
2          AS   AE   ARE     784             United Arab Emirates
3          AS   AF   AFG       4 Afghanistan, Islamic Republic of
4          NA   AG   ATG      28              Antigua and Barbuda
5          NA   AI   AIA     660                         Anguilla
159        AF   NA   NAM     516             Namibia, Republic of
...
246        AF   ZW   ZWE     716            Zimbabwe, Republic of



and

 > rawdata
    idno alter  sex cctld capacity topics content speakers
1     1    NA    M    AD        A      2       1        2
2     2    52    M  <NA>        A      3       2        2
3     3    39    F    NA        T      2       2        2
...

How do I make it print out something like

	alter	sex	country
1	NA	M	Andorra, Principality of
2	39	F	Namibia, Republic of
...

in other words do something like the following SQL:

SELECT r.alter, r.sex, i.country
FROM iso i, rawdata r
WHERE i.code = r.cctld

or even better:

	alter	sex	country
1	NA	M	Andorra, Principality of
2	52	M	<NA>
3	39	F	Namibia
...

And idea (where I can read up on this)?


el
--
Dr. Eberhard W. Lisse  \        / Obstetrician & Gynaecologist (Saar)
el at lisse.NA el108-ARIN /   *   |   Telephone: +264 81 124 6733 (cell)
PO Box 8421             \     / Please send DNS/NA-NiC related e-mail
Bachbrecht, Namibia     ;____/             to dns-admin at na-nic.com.na

______________________________________________
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.

Dr Eberhard W Lisse

2008-Oct-24 21:45 UTC

head link

[R] combining data from different datasets

Daniel,

the parameter all.x=TRUE is required.

greetings, el

On 24 Oct 2008, at 21:24 , Daniel Malter wrote:
> ?merge.
>
> It looks though that your "iso" has no identifier variable
whereas the
> "rawdata" has, so you probably cannot merge it unless/until you
have
> an
> identifier in "iso".

Steven McKinney

2008-Oct-25 01:32 UTC

head link

[R] combining data from different datasets

If you are using regular R graphs
(i.e. not lattice or other library
graphics) try setting the margins
with the mar argument to par()

e.g.
par(mar = c(5, 10, 5, 1))

The four numbers specify the amount
of margin room on the bottom, left, top, right
respectively.  Set the left margin value large
enough to give your labels enough room.

HTH

Steven McKinney

Statistician
Molecular Oncology and Breast Cancer Program
British Columbia Cancer Research Centre

email: smckinney +at+ bccrc +dot+ ca

tel: 604-675-8000 x7561

BCCRC
Molecular Oncology
675 West 10th Ave, Floor 4
Vancouver B.C. 
V5Z 1L3
Canada

-----Original Message-----
From: r-help-bounces at r-project.org on behalf of Dr Eberhard W Lisse
Sent: Fri 10/24/2008 2:44 PM
To: R-help Mailing List
Cc: Dr Eberhard W Lisse
Subject: Re: [R] combining data from different datasets

It's Complicated? :-)-O

I pull the data from a postgresql table, but I am getting
there, thank you the help.

Another question, I am barplotting the continents horizontally,
ie the more participants the lrger the bars are. I have managed
the make the labels (names of the nontinents) to be horizontally
but the longer ones (North and South America) flow off the left
side (right justified).

So, how do I make the plot smaller, and move it to the right so
that the complete names appear? I can make the plot smaller in
inches but that doesn't scale if I enlarge the window.

Pointers to read will be fine, but so far I haven't found it :-)-O

el

On 24 Oct 2008, at 18:24 , Gabor Grothendieck wrote:
> On Fri, Oct 24, 2008 at 11:37 AM, Dr Eberhard W Lisse <el at
lisse.na>
> wrote:
>>
>> This looks very cool.
>>
>> But I must still make a plan with regards to country = "NA"
(Namibia)
>> or continent = "NA" (North America)
>>
>> But there are the vignettes.
>>
>> el
>>
>
> NA and "NA" are not the same:
>
>> DF <- data.frame(x = c("a", "NA", NA))
>> DF
>     x
> 1    a
> 2   NA
> 3 <NA>
>>
>> is.na(NA)
> [1] TRUE
>> is.na("NA")
> [1] FALSE
>
--
Dr. Eberhard W. Lisse  \        / Obstetrician & Gynaecologist (Saar)
el at lisse.NA el108-ARIN /   *   |   Telephone: +264 81 124 6733 (cell)
PO Box 8421             \     / Please send DNS/NA-NiC related e-mail
Bachbrecht, Namibia     ;____/             to dns-admin at na-nic.com.na

______________________________________________
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.

Dr Eberhard W Lisse

2008-Oct-26 07:47 UTC

head link

[R] combining data from different datasets

Yes, it does. Thanks.

el

On 25 Oct 2008, at 03:32 , Steven McKinney wrote:
> If you are using regular R graphs
> (i.e. not lattice or other library
> graphics) try setting the margins
> with the mar argument to par()
>
> e.g.
> par(mar = c(5, 10, 5, 1))
>
> The four numbers specify the amount
> of margin room on the bottom, left, top, right
> respectively.  Set the left margin value large
> enough to give your labels enough room.
>
>
> HTH
>
>
> Steven McKinney

Reasonably Related Threads

Search for more reasonably related threads

R help - Oct 2008 - combining data from different datasets

[R] combining data from different datasets

[R] combining data from different datasets

[R] combining data from different datasets

[R] combining data from different datasets

[R] combining data from different datasets

[R] combining data from different datasets

[R] combining data from different datasets

[R] combining data from different datasets

Reasonably Related Threads