copy the columns based on the code

[MSousa]

  Hello,

I have two different dataset, and wanted to join the two.
   For example I have a table of codes of  cities, and other with with the codes of travels, [source for the destine].
what he wanted was to have a new data.frame with all the information

city<-data.frame(city="Barcelona",cod=1)
city<-rbind(city,data.frame(city="Madrid",cod=2))
city<-rbind(city,data.frame(city="Lisbon",cod=3)))
city<-rbind(city,data.frame(city="Milan",cod=4))
city<-rbind(city,data.frame(city="London",cod=5))

travel<-data.frame(pos=1,Source=1,Destine=2)
travel<-rbind(travel,data.frame(pos=1,Source=1,Destine=3))
travel<-rbind(travel,data.frame(pos=2,Source=3,Destine=4))
travel<-rbind(travel,data.frame(pos=3,Source=2,Destine=4))
travel<-rbind(travel,data.frame(pos=4,Source=1,Destine=3))

#for example
pos Source     city       Destine  city_destine
1      1       Barcelona   2       Madrid
1      1       Barcelona   3       Lisbon
2      3       Lisbon      4       Milan
3      2       Madrid      4       Milan

which the fastest way to do this.

    Thanks.

   

[Sarah Goslee]

It isn't quite clear to me that cod in data frame city and pos in
travel are are actually the same index, but if so then you can easily
use merge() for this task.

Sarah

On Mon, Mar 26, 2012 at 5:41 AM, MSousa <[hidden email]> wrote:

>
>  Hello,
>
> I have two different dataset, and wanted to join the two.
>   For example I have a table of codes of  cities, and other with with the
> codes of travels, [source for the destine].
> what he wanted was to have a new data.frame with all the information
>
> city<-data.frame(city="Barcelona",cod=1)
> city<-rbind(city,data.frame(city="Madrid",cod=2))
> city<-rbind(city,data.frame(city="Lisbon",cod=3)))
> city<-rbind(city,data.frame(city="Milan",cod=4))
> city<-rbind(city,data.frame(city="London",cod=5))
>
> travel<-data.frame(pos=1,Source=1,Destine=2)
> travel<-rbind(travel,data.frame(pos=1,Source=1,Destine=3))
> travel<-rbind(travel,data.frame(pos=2,Source=3,Destine=4))
> travel<-rbind(travel,data.frame(pos=3,Source=2,Destine=4))
> travel<-rbind(travel,data.frame(pos=4,Source=1,Destine=3))
>
> #for example
> pos Source     city       Destine  city_destine
> 1      1       Barcelona   2       Madrid
> 1      1       Barcelona   3       Lisbon
> 2      3       Lisbon      4       Milan
> 3      2       Madrid      4       Milan
>
> which the fastest way to do this.
>
>    Thanks.
>
>

--
Sarah Goslee
http://www.functionaldiversity.org

______________________________________________
[hidden email] 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.

[MSousa]

Thanks for the reply.

    I am using the function you gave me.

complete.travel<-merge(travel, city, by.x = "Source", by.y = "cod", all = TRUE)
complete.travel<-merge(travel, city, by.x = "Destine", by.y = "cod", all = TRUE
                       

The problem is that it gives the result that I want
   The idea is based on the column of source and intended Identify the cities and put a new data structure

The idea is something like this.    
pos Source     city       Destine  city_destine
1      1       Barcelona   2       Madrid
1      1       Barcelona   3       Lisbon
2      3       Lisbon      4       Milan
3      2       Madrid      4       Milan

[Rui Barradas]

Hello,

>
> The problem is that it gives the result that I want
>

Oh dear, it shouldn't?

>
>  The idea is based on the column of source and intended Identify the cities and put a new data structure
>
> The idea is something like this.    
> pos Source     city       Destine  city_destine
> 1      1       Barcelona   2       Madrid
> 1      1       Barcelona   3       Lisbon
> 2      3       Lisbon      4       Milan
> 3      2       Madrid      4       Milan
>

Try the following.

temp1 <- merge(travel, city, by.x='Source', by.y='cod')
temp2 <- merge(temp1, city, by.x='Destine', by.y='cod')
temp2[, c(3, 2, 4, 1, 5)]

The general idea is obvious, I believe: you want to relate 'cod/city' from table 'city' with
two columns of table 'travel', one is source and the other is destine. So you need to merge the tables twice.

Hope this helps,

Rui Barradas

[MSousa]

CONTENTS DELETED

The author has deleted this message.

[MSousa]

thanks,

 is working

[Michael Weylandt]

"The problem is that it gives the result that I want"

I'm not sure that's what one would usually identify as a
problem....can you say a little more specifically what you are looking
to do?

Michael

On Mon, Mar 26, 2012 at 10:12 AM, MSousa <[hidden email]> wrote:

> Thanks for the reply.
>
>    I am using the function you gave me.
>
> complete.travel<-merge(travel, city, by.x = "Source", by.y = "cod", all =
> TRUE)
> complete.travel<-merge(travel, city, by.x = "Destine", by.y = "cod", all =
> TRUE
>
>
> The problem is that it gives the result that I want
>   The idea is based on the column of source and intended Identify the
> cities and put a new data structure
>
> The idea is something like this.
> pos Source     city       Destine  city_destine
> 1      1       Barcelona   2       Madrid
> 1      1       Barcelona   3       Lisbon
> 2      3       Lisbon      4       Milan
> 3      2       Madrid      4       Milan
>
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/copy-the-columns-based-on-the-code-tp4505253p4505939.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] 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.

______________________________________________
[hidden email] 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.

[Sarah Goslee]

"The problem is that it gives the result that I want."

That's a new sort of problem.

You show two different merge() commands. What do you expect to happen,
vs what does happen?

Sarah

On Mon, Mar 26, 2012 at 10:12 AM, MSousa <[hidden email]> wrote:

> Thanks for the reply.
>
>    I am using the function you gave me.
>
> complete.travel<-merge(travel, city, by.x = "Source", by.y = "cod", all =
> TRUE)
> complete.travel<-merge(travel, city, by.x = "Destine", by.y = "cod", all =
> TRUE
>
>
> The problem is that it gives the result that I want
>   The idea is based on the column of source and intended Identify the
> cities and put a new data structure
>
> The idea is something like this.
> pos Source     city       Destine  city_destine
> 1      1       Barcelona   2       Madrid
> 1      1       Barcelona   3       Lisbon
> 2      3       Lisbon      4       Milan
> 3      2       Madrid      4       Milan
>
>

--
Sarah Goslee
http://www.functionaldiversity.org

______________________________________________
[hidden email] 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.

[Bert Gunter]

Fortunes candidate?!
-- Bert

On Mon, Mar 26, 2012 at 10:24 AM, Sarah Goslee <[hidden email]> wrote:
< The OP wrote>
 "The problem is that it gives the result that I want."

<Sarah's reply>:  That's a new sort of problem.




--

Bert Gunter
Genentech Nonclinical Biostatistics

Internal Contact Info:
Phone: [phone]
Website:
http://pharmadevelopment.roche.com/index/pdb/pdb-functional-groups/pdb-biostatistics/pdb-ncb-home.htm

______________________________________________
[hidden email] 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.

[Sarah Goslee]

Then there's something missing in what you tell us.


# this is an awkward and inefficient way of constructing a data frame
city<-data.frame(city="Barcelona",cod=1)
city<-rbind(city,data.frame(city="Madrid",cod=2))
city<-rbind(city,data.frame(city="Lisbon",cod=3))
city<-rbind(city,data.frame(city="Milan",cod=4))
city<-rbind(city,data.frame(city="London",cod=5))

travel<-data.frame(pos=1,Source=1,Destine=2)
travel<-rbind(travel,data.frame(pos=1,Source=1,Destine=3))
travel<-rbind(travel,data.frame(pos=2,Source=3,Destine=4))
travel<-rbind(travel,data.frame(pos=3,Source=2,Destine=4))
travel<-rbind(travel,data.frame(pos=4,Source=1,Destine=3))


temp <- merge(travel, city, by.x="Source", by.y="cod")
result <- merge(temp, city, by.x="Destine", by.y="cod")

> temp
  Source pos Destine      city
1      1   1       2 Barcelona
2      1   1       3 Barcelona
3      1   4       3 Barcelona
4      2   3       4    Madrid
5      3   2       4    Lisbon
> result
  Destine Source pos    city.x city.y
1       2      1   1 Barcelona Madrid
2       3      1   1 Barcelona Lisbon
3       3      1   4 Barcelona Lisbon
4       4      2   3    Madrid  Milan
5       4      3   2    Lisbon  Milan

On Mon, Mar 26, 2012 at 11:08 AM, MSousa <[hidden email]> wrote:

> thanks for the reply,
>
>   But does not the results I need. What is confusing is that when making
> the first merge it gives only two cities when it should take three
>
>
> travel<-data.frame(pos=1,Source=1,Destine=2)
> travel<-rbind(travel,data.frame(pos=1,*Source=1*,Destine=3))
> travel<-rbind(travel,data.frame(pos=2,*Source=3*,Destine=4))
> travel<-rbind(travel,data.frame(pos=3,*Source=2*,Destine=4))
> travel<-rbind(travel,data.frame(pos=4,*Source=1*,Destine=3))
>
> temp1 <- merge(travel, city, by.x='Source', by.y='cod')
>>temp1  ()
> Source pos Destine      city
> 1      *1*   1       2 Barcelona
> 2      1   1       3 Barcelona
> 3      1   4       3 Barcelona
> 4      *2 *  3       4    Madrid
>

--
Sarah Goslee
http://www.functionaldiversity.org

______________________________________________
[hidden email] 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.

[Igor Sosa Mayor]

:)

yes! I agree!

On Mon, Mar 26, 2012 at 10:51:17AM -0700, Bert Gunter wrote:

> Fortunes candidate?!
> -- Bert
>
> On Mon, Mar 26, 2012 at 10:24 AM, Sarah Goslee <[hidden email]> wrote:
> < The OP wrote>
>  "The problem is that it gives the result that I want."
>
> <Sarah's reply>:  That's a new sort of problem.
>
>
>
>
> --
>
> Bert Gunter
> Genentech Nonclinical Biostatistics
>
> Internal Contact Info:
> Phone: [phone]
> Website:
> http://pharmadevelopment.roche.com/index/pdb/pdb-functional-groups/pdb-biostatistics/pdb-ncb-home.htm
>
> ______________________________________________
> [hidden email] 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.

--
:: Igor Sosa Mayor     :: [hidden email] ::
:: GnuPG: 0x1C1E2890   :: http://www.gnupg.org/      ::
:: jabberid: rogorido  ::                            ::

______________________________________________
[hidden email] 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.

[MSousa]

Hello,

this code, works perfectly
   temp <- merge(travel, city, by.x="Source", by.y="cod")
   result <- merge(temp, city, by.x="Destine", by.y="cod")

The problem was the construction of the data frame, had a parenthesis in city<-rbind(city,data.frame(city="Lisbon",cod=3))),

I tried to delete the post, but i don't could.
  As I have little experience in R, I still do some mistakes.
I use read.table to load the data frame, the way in the post, it was quickly that  i found to describe the problem.
  The forum has been a great help for me.

Thanks

[jholtman]

yet another way:

> city<-data.frame(city="Barcelona",cod=1)
> city<-rbind(city,data.frame(city="Madrid",cod=2))
> city<-rbind(city,data.frame(city="Lisbon",cod=3))
> city<-rbind(city,data.frame(city="Milan",cod=4))
> city<-rbind(city,data.frame(city="London",cod=5))
>
> travel<-data.frame(pos=1,Source=1,Destine=2)
> travel<-rbind(travel,data.frame(pos=1,Source=1,Destine=3))
> travel<-rbind(travel,data.frame(pos=2,Source=3,Destine=4))
> travel<-rbind(travel,data.frame(pos=3,Source=2,Destine=4))
> travel<-rbind(travel,data.frame(pos=4,Source=1,Destine=3))
>
> travel$city <- city$city[match(travel$Source, city$cod)]
> travel$city_destine <- city$city[match(travel$Destine, city$cod)]
>
> travel

  pos Source Destine      city city_destine
1   1      1       2 Barcelona       Madrid
2   1      1       3 Barcelona       Lisbon
3   2      3       4    Lisbon        Milan
4   3      2       4    Madrid        Milan
5   4      1       3 Barcelona       Lisbon
>


On Tue, Mar 27, 2012 at 12:15 PM, MSousa <[hidden email]> wrote:

>
>
> Hello,
>
> this code, works perfectly
>   temp <- merge(travel, city, by.x="Source", by.y="cod")
>   result <- merge(temp, city, by.x="Destine", by.y="cod")
>
> The problem was the construction of the data frame, had a parenthesis in
> city<-rbind(city,data.frame(city="Lisbon",cod=3))),
>
> I tried to delete the post, but i don't could.
>  As I have little experience in R, I still do some mistakes.
> I use read.table to load the data frame, the way in the post, it was quickly
> that  i found to describe the problem.
>  The forum has been a great help for me.
>
> Thanks
>
>
>
>
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/copy-the-columns-based-on-the-code-tp4505253p4509340.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] 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.

--
Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.

______________________________________________
[hidden email] 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.