match and new columns

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

match and new columns

Val-17
Hi all,

I have a data frame
tdat <- read.table(textConnection("A B C Y
A12 B03 C04 0.70
A23 B05 C06 0.05
A14 B06 C07 1.20
A25 A23 A12 3.51
A16 A25 A14 2,16"),header = TRUE)

I want match tdat$B with tdat$A and populate the  column   values of tdat$A
( col A and Col B) in the newly created columns (col D and col  E).  please
find my attempt and the desired output below

Desired output
A B C Y  D E
A12 B03 C04 0.70  0  0
A23 B05 C06 0.05  0  0
A14 B06 C07 1.20  0  0
A25 A23 A12 3.51 B05 C06
A16 A25 A14 2,16 A23 A12

my attempt,

tdat$D <- 0
tdat$E <- 0

if(tdat$B %in% tdat$A)
  {
  tdat$D <- tdat$A[tdat$B]
  tdat$E <- tdat$A[tdat$C]
}
 but did not work.

Thank you in advance

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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
|

Re: match and new columns

Rui Barradas
Hello,

Here is one way.

tdat$D <- ifelse(tdat$B %in% tdat$A, tdat$A[tdat$B], 0)
tdat$E <- ifelse(tdat$B %in% tdat$A, tdat$A[tdat$C], 0)


Hope this helps,

Rui Barradas

On 12/13/2017 9:36 PM, Val wrote:

> Hi all,
>
> I have a data frame
> tdat <- read.table(textConnection("A B C Y
> A12 B03 C04 0.70
> A23 B05 C06 0.05
> A14 B06 C07 1.20
> A25 A23 A12 3.51
> A16 A25 A14 2,16"),header = TRUE)
>
> I want match tdat$B with tdat$A and populate the  column   values of tdat$A
> ( col A and Col B) in the newly created columns (col D and col  E).  please
> find my attempt and the desired output below
>
> Desired output
> A B C Y  D E
> A12 B03 C04 0.70  0  0
> A23 B05 C06 0.05  0  0
> A14 B06 C07 1.20  0  0
> A25 A23 A12 3.51 B05 C06
> A16 A25 A14 2,16 A23 A12
>
> my attempt,
>
> tdat$D <- 0
> tdat$E <- 0
>
> if(tdat$B %in% tdat$A)
>    {
>    tdat$D <- tdat$A[tdat$B]
>    tdat$E <- tdat$A[tdat$C]
> }
>   but did not work.
>
> Thank you in advance
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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 -- To UNSUBSCRIBE and more, see
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
|

Re: match and new columns

Val-17
Thank you Rui,
I did not get the desired result. Here is the output from your script

   A   B   C    Y D E
1 A12 B03 C04 0.70 0 0
2 A23 B05 C06 0.05 0 0
3 A14 B06 C07 1.20 0 0
4 A25 A23 A12 3.51 1 1
5 A16 A25 A14 2,16 4 4


On Wed, Dec 13, 2017 at 4:36 PM, Rui Barradas <[hidden email]> wrote:

> Hello,
>
> Here is one way.
>
> tdat$D <- ifelse(tdat$B %in% tdat$A, tdat$A[tdat$B], 0)
> tdat$E <- ifelse(tdat$B %in% tdat$A, tdat$A[tdat$C], 0)
>
>
> Hope this helps,
>
> Rui Barradas
>
>
> On 12/13/2017 9:36 PM, Val wrote:
>
>> Hi all,
>>
>> I have a data frame
>> tdat <- read.table(textConnection("A B C Y
>> A12 B03 C04 0.70
>> A23 B05 C06 0.05
>> A14 B06 C07 1.20
>> A25 A23 A12 3.51
>> A16 A25 A14 2,16"),header = TRUE)
>>
>> I want match tdat$B with tdat$A and populate the  column   values of
>> tdat$A
>> ( col A and Col B) in the newly created columns (col D and col  E).
>> please
>> find my attempt and the desired output below
>>
>> Desired output
>> A B C Y  D E
>> A12 B03 C04 0.70  0  0
>> A23 B05 C06 0.05  0  0
>> A14 B06 C07 1.20  0  0
>> A25 A23 A12 3.51 B05 C06
>> A16 A25 A14 2,16 A23 A12
>>
>> my attempt,
>>
>> tdat$D <- 0
>> tdat$E <- 0
>>
>> if(tdat$B %in% tdat$A)
>>    {
>>    tdat$D <- tdat$A[tdat$B]
>>    tdat$E <- tdat$A[tdat$C]
>> }
>>   but did not work.
>>
>> Thank you in advance
>>
>>         [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posti
>> ng-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>>
>>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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
|

Re: match and new columns

R help mailing list-2
Use the stringsAsFactors=FALSE argument to read.table when
making your data.frame - factors are getting in your way here.

Bill Dunlap
TIBCO Software
wdunlap tibco.com

On Wed, Dec 13, 2017 at 3:02 PM, Val <[hidden email]> wrote:

> Thank you Rui,
> I did not get the desired result. Here is the output from your script
>
>    A   B   C    Y D E
> 1 A12 B03 C04 0.70 0 0
> 2 A23 B05 C06 0.05 0 0
> 3 A14 B06 C07 1.20 0 0
> 4 A25 A23 A12 3.51 1 1
> 5 A16 A25 A14 2,16 4 4
>
>
> On Wed, Dec 13, 2017 at 4:36 PM, Rui Barradas <[hidden email]>
> wrote:
>
> > Hello,
> >
> > Here is one way.
> >
> > tdat$D <- ifelse(tdat$B %in% tdat$A, tdat$A[tdat$B], 0)
> > tdat$E <- ifelse(tdat$B %in% tdat$A, tdat$A[tdat$C], 0)
> >
> >
> > Hope this helps,
> >
> > Rui Barradas
> >
> >
> > On 12/13/2017 9:36 PM, Val wrote:
> >
> >> Hi all,
> >>
> >> I have a data frame
> >> tdat <- read.table(textConnection("A B C Y
> >> A12 B03 C04 0.70
> >> A23 B05 C06 0.05
> >> A14 B06 C07 1.20
> >> A25 A23 A12 3.51
> >> A16 A25 A14 2,16"),header = TRUE)
> >>
> >> I want match tdat$B with tdat$A and populate the  column   values of
> >> tdat$A
> >> ( col A and Col B) in the newly created columns (col D and col  E).
> >> please
> >> find my attempt and the desired output below
> >>
> >> Desired output
> >> A B C Y  D E
> >> A12 B03 C04 0.70  0  0
> >> A23 B05 C06 0.05  0  0
> >> A14 B06 C07 1.20  0  0
> >> A25 A23 A12 3.51 B05 C06
> >> A16 A25 A14 2,16 A23 A12
> >>
> >> my attempt,
> >>
> >> tdat$D <- 0
> >> tdat$E <- 0
> >>
> >> if(tdat$B %in% tdat$A)
> >>    {
> >>    tdat$D <- tdat$A[tdat$B]
> >>    tdat$E <- tdat$A[tdat$C]
> >> }
> >>   but did not work.
> >>
> >> Thank you in advance
> >>
> >>         [[alternative HTML version deleted]]
> >>
> >> ______________________________________________
> >> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> >> https://stat.ethz.ch/mailman/listinfo/r-help
> >> PLEASE do read the posting guide http://www.R-project.org/posti
> >> ng-guide.html
> >> and provide commented, minimal, self-contained, reproducible code.
> >>
> >>
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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.
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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
|

Re: match and new columns

Val-17
Hi Bill,

I put stringsAsFactors = FALSE
 still did not work.

tdat <- read.table(textConnection("A B C Y
A12 B03 C04 0.70
A23 B05 C06 0.05
A14 B06 C07 1.20
A25 A23 A12 3.51
A16 A25 A14 2,16"),header = TRUE ,stringsAsFactors = FALSE)
tdat$D <- 0
tdat$E <- 0

tdat$D <- (ifelse(tdat$B %in% tdat$A, tdat$A[tdat$B], 0))
tdat$E <- (ifelse(tdat$B %in% tdat$A, tdat$A[tdat$C], 0))
tdat

I got this,

 A  B  C   Y   D    E
1 A12 B03 C04 0.70    0    0
2 A23 B05 C06 0.05    0    0
3 A14 B06 C07 1.20    0    0
4 A25 A23 A12 3.51 <NA> <NA>
5 A16 A25 A14 2,16 <NA> <NA>





On Wed, Dec 13, 2017 at 7:23 PM, William Dunlap <[hidden email]> wrote:

> Use the stringsAsFactors=FALSE argument to read.table when
> making your data.frame - factors are getting in your way here.
>
> Bill Dunlap
> TIBCO Software
> wdunlap tibco.com
>
> On Wed, Dec 13, 2017 at 3:02 PM, Val <[hidden email]> wrote:
>
>> Thank you Rui,
>> I did not get the desired result. Here is the output from your script
>>
>>    A   B   C    Y D E
>> 1 A12 <https://maps.google.com/?q=1+A12&entry=gmail&source=g> B03 C04
>> 0.70 0 0
>> 2 A23 B05 C06 0.05 0 0
>> 3 A14 <https://maps.google.com/?q=3+A14&entry=gmail&source=g> B06 C07
>> 1.20 0 0
>> 4 A25 A23 A12 3.51 1 1
>> 5 A16 A25 A14 2,16 4
>> <https://maps.google.com/?q=A14+2,16+4&entry=gmail&source=g> 4
>>
>>
>> On Wed, Dec 13, 2017 at 4:36 PM, Rui Barradas <[hidden email]>
>> wrote:
>>
>> > Hello,
>> >
>> > Here is one way.
>> >
>> > tdat$D <- ifelse(tdat$B %in% tdat$A, tdat$A[tdat$B], 0)
>> > tdat$E <- ifelse(tdat$B %in% tdat$A, tdat$A[tdat$C], 0)
>> >
>> >
>> > Hope this helps,
>> >
>> > Rui Barradas
>> >
>> >
>> > On 12/13/2017 9:36 PM, Val wrote:
>> >
>> >> Hi all,
>> >>
>> >> I have a data frame
>> >> tdat <- read.table(textConnection("A B C Y
>> >> A12 B03 C04 0.70
>> >> A23 B05 C06 0.05
>> >> A14 B06 C07 1.20
>> >> A25 A23 A12 3.51
>> >> A16 A25 A14 2,16
>> <https://maps.google.com/?q=A14+2,16&entry=gmail&source=g>"),header =
>> TRUE)
>> >>
>> >> I want match tdat$B with tdat$A and populate the  column   values of
>> >> tdat$A
>> >> ( col A and Col B) in the newly created columns (col D and col  E).
>> >> please
>> >> find my attempt and the desired output below
>> >>
>> >> Desired output
>> >> A B C Y  D E
>> >> A12 B03 C04 0.70  0  0
>> >> A23 B05 C06 0.05  0  0
>> >> A14 B06 C07 1.20  0  0
>> >> A25 A23 A12 3.51 B05 C06
>> >> A16 A25 A14 2,16 A23 A12
>> <https://maps.google.com/?q=2,16+A23+A12&entry=gmail&source=g>
>> >>
>> >> my attempt,
>> >>
>> >> tdat$D <- 0
>> >> tdat$E <- 0
>> >>
>> >> if(tdat$B %in% tdat$A)
>> >>    {
>> >>    tdat$D <- tdat$A[tdat$B]
>> >>    tdat$E <- tdat$A[tdat$C]
>> >> }
>> >>   but did not work.
>> >>
>> >> Thank you in advance
>> >>
>> >>         [[alternative HTML version deleted]]
>> >>
>> >> ______________________________________________
>> >> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>> >> https://stat.ethz.ch/mailman/listinfo/r-help
>> >> PLEASE do read the posting guide http://www.R-project.org/posti
>> >> ng-guide.html
>> >> and provide commented, minimal, self-contained, reproducible code.
>> >>
>> >>
>>
>>         [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posti
>> ng-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>>
>
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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
|

Re: match and new columns

R help mailing list-2
Try the following (which won't work with factors):

> i <- match(tdat$B, tdat$A)
> newColumns <- tdat[i, c("B", "C")]
> newColumns[is.na(newColumns)] <- "0"
> transform(tdat, D=newColumns[["B"]], E=newColumns[["C"]])
    A   B   C    Y   D   E
1 A12 B03 C04 0.70   0   0
2 A23 B05 C06 0.05   0   0
3 A14 B06 C07 1.20   0   0
4 A25 A23 A12 3.51 B05 C06
5 A16 A25 A14 2,16 A23 A12


Bill Dunlap
TIBCO Software
wdunlap tibco.com

On Wed, Dec 13, 2017 at 5:37 PM, Val <[hidden email]> wrote:

> Hi Bill,
>
> I put stringsAsFactors = FALSE
>  still did not work.
>
> tdat <- read.table(textConnection("A B C Y
> A12 B03 C04 0.70
> A23 B05 C06 0.05
> A14 B06 C07 1.20
> A25 A23 A12 3.51
> A16 A25 A14 2,16
> <https://maps.google.com/?q=A14+2,16&entry=gmail&source=g>"),header =
> TRUE ,stringsAsFactors = FALSE)
> tdat$D <- 0
> tdat$E <- 0
>
> tdat$D <- (ifelse(tdat$B %in% tdat$A, tdat$A[tdat$B], 0))
> tdat$E <- (ifelse(tdat$B %in% tdat$A, tdat$A[tdat$C], 0))
> tdat
>
> I got this,
>
>  A  B  C   Y   D    E1 A12 <https://maps.google.com/?q=1+A12&entry=gmail&source=g> B03 C04 0.70    0    0
> 2 A23 B05 C06 0.05    0    03 A14 <https://maps.google.com/?q=3+A14&entry=gmail&source=g> B06 C07 1.20    0    0
> 4 A25 A23 A12 3.51 <NA> <NA>
> 5 A16 A25 A14 2,16 <NA> <NA>
>
>
>
>
>
> On Wed, Dec 13, 2017 at 7:23 PM, William Dunlap <[hidden email]> wrote:
>
>> Use the stringsAsFactors=FALSE argument to read.table when
>> making your data.frame - factors are getting in your way here.
>>
>> Bill Dunlap
>> TIBCO Software
>> wdunlap tibco.com
>>
>> On Wed, Dec 13, 2017 at 3:02 PM, Val <[hidden email]> wrote:
>>
>>> Thank you Rui,
>>> I did not get the desired result. Here is the output from your script
>>>
>>>    A   B   C    Y D E
>>> 1 A12 <https://maps.google.com/?q=1+A12&entry=gmail&source=g> B03 C04
>>> 0.70 0 0
>>> 2 A23 B05 C06 0.05 0 0
>>> 3 A14 <https://maps.google.com/?q=3+A14&entry=gmail&source=g> B06 C07
>>> 1.20 0 0
>>> 4 A25 A23 A12 3.51 1 1
>>> 5 A16 A25 A14 2,16 4
>>> <https://maps.google.com/?q=A14+2,16+4&entry=gmail&source=g> 4
>>>
>>>
>>> On Wed, Dec 13, 2017 at 4:36 PM, Rui Barradas <[hidden email]>
>>> wrote:
>>>
>>> > Hello,
>>> >
>>> > Here is one way.
>>> >
>>> > tdat$D <- ifelse(tdat$B %in% tdat$A, tdat$A[tdat$B], 0)
>>> > tdat$E <- ifelse(tdat$B %in% tdat$A, tdat$A[tdat$C], 0)
>>> >
>>> >
>>> > Hope this helps,
>>> >
>>> > Rui Barradas
>>> >
>>> >
>>> > On 12/13/2017 9:36 PM, Val wrote:
>>> >
>>> >> Hi all,
>>> >>
>>> >> I have a data frame
>>> >> tdat <- read.table(textConnection("A B C Y
>>> >> A12 B03 C04 0.70
>>> >> A23 B05 C06 0.05
>>> >> A14 B06 C07 1.20
>>> >> A25 A23 A12 3.51
>>> >> A16 A25 A14 2,16
>>> <https://maps.google.com/?q=A14+2,16&entry=gmail&source=g>"),header =
>>> TRUE)
>>> >>
>>> >> I want match tdat$B with tdat$A and populate the  column   values of
>>> >> tdat$A
>>> >> ( col A and Col B) in the newly created columns (col D and col  E).
>>> >> please
>>> >> find my attempt and the desired output below
>>> >>
>>> >> Desired output
>>> >> A B C Y  D E
>>> >> A12 B03 C04 0.70  0  0
>>> >> A23 B05 C06 0.05  0  0
>>> >> A14 B06 C07 1.20  0  0
>>> >> A25 A23 A12 3.51 B05 C06
>>> >> A16 A25 A14 2,16 A23 A12
>>> <https://maps.google.com/?q=2,16+A23+A12&entry=gmail&source=g>
>>> >>
>>> >> my attempt,
>>> >>
>>> >> tdat$D <- 0
>>> >> tdat$E <- 0
>>> >>
>>> >> if(tdat$B %in% tdat$A)
>>> >>    {
>>> >>    tdat$D <- tdat$A[tdat$B]
>>> >>    tdat$E <- tdat$A[tdat$C]
>>> >> }
>>> >>   but did not work.
>>> >>
>>> >> Thank you in advance
>>> >>
>>> >>         [[alternative HTML version deleted]]
>>> >>
>>> >> ______________________________________________
>>> >> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>>> >> https://stat.ethz.ch/mailman/listinfo/r-help
>>> >> PLEASE do read the posting guide http://www.R-project.org/posti
>>> >> ng-guide.html
>>> >> and provide commented, minimal, self-contained, reproducible code.
>>> >>
>>> >>
>>>
>>>         [[alternative HTML version deleted]]
>>>
>>> ______________________________________________
>>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>> PLEASE do read the posting guide http://www.R-project.org/posti
>>> ng-guide.html
>>> and provide commented, minimal, self-contained, reproducible code.
>>>
>>
>>
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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
|

Re: match and new columns

Ek Esawi
In reply to this post by Val-17
Hi Val—

Here is something similar to what Bill suggested.

wainb <- which(tdat$A %in% tdat$B)
tdat[c(length(tdat$D)-1,length(tdat$D)),c("D","E")] <-
tdat[wainb,c("B","C")]

HTH

EK

On Wed, Dec 13, 2017 at 4:36 PM, Val <[hidden email]> wrote:

> Hi all,
>
> I have a data frame
> tdat <- read.table(textConnection("A B C Y
> A12 B03 C04 0.70
> A23 B05 C06 0.05
> A14 B06 C07 1.20
> A25 A23 A12 3.51
> A16 A25 A14 2,16"),header = TRUE)
>
> I want match tdat$B with tdat$A and populate the  column   values of tdat$A
> ( col A and Col B) in the newly created columns (col D and col  E).  please
> find my attempt and the desired output below
>
> Desired output
> A B C Y  D E
> A12 B03 C04 0.70  0  0
> A23 B05 C06 0.05  0  0
> A14 B06 C07 1.20  0  0
> A25 A23 A12 3.51 B05 C06
> A16 A25 A14 2,16 A23 A12
>
> my attempt,
>
> tdat$D <- 0
> tdat$E <- 0
>
> if(tdat$B %in% tdat$A)
>   {
>   tdat$D <- tdat$A[tdat$B]
>   tdat$E <- tdat$A[tdat$C]
> }
>  but did not work.
>
> Thank you in advance
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> 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.
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
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.