Quantcast

copy the columns based on the code

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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.

   
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the code

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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the code

MSousa
In reply to this post by 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


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the code

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the code

MSousa
This post was updated on .
CONTENTS DELETED
The author has deleted this message.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the code

MSousa
In reply to this post by Rui Barradas
thanks,

 is working
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the code

Michael Weylandt
In reply to this post by MSousa
"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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the code

Sarah Goslee
In reply to this post by MSousa
"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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the 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: 467-7374
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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the code

Sarah Goslee
In reply to this post by MSousa
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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the code

Igor Sosa Mayor
In reply to this post by Bert Gunter
:)

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: 467-7374
> 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.

attachment0 (237 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the code

MSousa
In reply to this post by Sarah Goslee


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





Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: copy the columns based on the code

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