[R] Add columns in a dataframe and fill them from another table according to a criteria

[John Kane]

I'm still not sure that I understand what you are looking for.  However building on David Winsemius does this give you what you want? 

#======================================================================

pstate<-read.table(textConnection("Changes State1 State2 State3 State4
 a Pa1 Pa2 Pa3 Pa4
 b Pb1 Pb2 Pb3 Pb4
 c Pc1 Pc2 Pc3 Pc4
 d Pd1 Pd2 Pd3 Pd4"),header=TRUE,as.is=TRUE)

Change<-c("b","a","b","c","d","a")
state<-c("State1","State4","State2","State3","State1","State3")

mydata4<-data.frame(Change,state)
dd <- data.frame(Change=pstate[,1],
             prstate =stack(pstate[2:5])$values,
             state=stack(pstate[2:5])$ind )

# merge(mydata4, dd, by=c("Change", "state"))
=======================================================

--- On Sat, 8/1/09, Meenu Sahi <meenusahi at gmail.com> wrote:

> From: Meenu Sahi <meenusahi at gmail.com>
> Subject: Re: [R] Add columns in a dataframe and fill them from another table  according to a criteria
> To: "David Winsemius" <dwinsemius at comcast.net>
> Cc: "R" <r-help at r-project.org>, jrkrideau at yahoo.ca
> Received: Saturday, August 1, 2009, 1:43 PM
> Dear R users
> My apologizes for not writing in the correct format
> due to my ignorance. In the future I will write more
> clearly. I hope to contribute to the R community in the
> process of picking up the language professionally.
> 
> I have now written the R code which is attached in a
> notepad file. I've simplified my problem in an example
> of, table pstate which contains the probabilities of getting
> certain changes in the four different states and a dataframe
> mydata4 which contains all the changes connected to the four
> different states. I would like to add the probabilities into
> mydata4 after matching for the change and the state.
> 
> Everything before ##### output can be copy pasted in
> the R window. The desired output is written after
> ###### OUTPUT 
> Must I write an if else or can I do it in an easier
> way?
> Your help is greatly appreciated ! Many thanks for
> your patience.
>  
> Regards
> Meenu
> 
> 
> On Sat, Aug 1, 2009 at 9:43 PM,
> David Winsemius <dwinsemius at comcast.net>
> wrote:
> 
> 
> 
> 
> 
> On Aug 1, 2009, at 9:52 AM, Meenu Sahi wrote:
> 
> 
> Deare R
> users
> 
> I am new to R.
> What I want to do is explained below;-
> I have table called States.Prob which is given below:-
> 
> This table gives the probabilities of the changes in the
> swap curve
> depending on the state of the swap curve. I want to put
> these probabilities
> in my dataframe mydata(given after the prob table).
>                Prob of States
> 
> Changes  State1  State2 State3 State4
> a             Pa1      Pa2     Pa3     Pa4
> b             Pb1      Pb2     Pb3     Pb4
> c             Pc1      Pc2     Pc3     Pc4
> d             Pd1      Pd2     Pd3     Pd4
> 
> 
> and I have a dataframe(with 93 rows) called mydata part of
> which(6 rows) is
> given below where I want to fill in the last four columns
> with probabilities
> taken from States.Prob according to the change and state in
> mydata4:-
> 
> Change  State  PState1  PState2  PState3  PState4
> 1 b       State1  Pb1
> 2 a       State4                          
>                 Pa4
> 3 b       State2                Pb2
> 4 c       State3                          
>   Pc3
> 
> 5 d       State1  Pd1
> 6 a       State3                          
>   Pa3
> 
> What I want to do is highlighted in Red.
> How can I do this easily?
> 
> You may have seen it in red, but
> we don't, ....and I, at least, cannot figure out what
> you intend.   (Per the Posting Guide, which you have
> obviously not yet read, you need to compose your question in
> plain old monochromatic text and change your mail client so
> it posts in plain text.)
> 
> 
> If looking at the help pages for stack() and reshape() does
> not offer useful information and worked examples that meet
> your needs then:
> 
> An approach that would make you more populat in these parts
> would be to  make a simpler example, composed in
> syntactically correct R, that is complete in itself, and can
> pasted into an R session. Indicate what you intend as output
> from this simpler input.
> 
> 
> Perhaps....
> 
> > pstate <- read.table(textConnection("Changes
>  State1  State2 State3 State4 
> 
> + a             Pa1      Pa2     Pa3     Pa4
> + b             Pb1      Pb2     Pb3     Pb4
> + c             Pc1      Pc2     Pc3     Pc4
> + d             Pd1      Pd2     Pd3    
> Pd4"),  header=TRUE, as.is=TRUE)
> 
> 
> ?stack
> 
> > data.frame(Change=pstate[,1],
>              prstate =stack(pstate[2:5])$values,
>              state=stack(pstate[2:5])$ind )
> 
> #first column  is only 4 elements long, but will get
> recycled
> 
> # second  retreives the probabilities and may need to have
> as.numeric( ) wrapped around it if they really are numeric.
> # third returns what started out as column names.
> 
>   Change prstate  state
> 1       a     Pa1 State1
> 
> 2       b     Pb1 State1
> 3       c     Pc1 State1
> 4       d     Pd1 State1
> 5       a     Pa2 State2
> 6       b     Pb2 State2
> 7       c     Pc2 State2
> 8       d     Pd2 State2
> 9       a     Pa3 State3
> 
> 10      b     Pb3 State3
> 11      c     Pc3 State3
> 12      d     Pd3 State3
> 13      a     Pa4 State4
> 14      b     Pb4 State4
> 15      c     Pc4 State4
> 16      d     Pd4 State4 
> 
> 
> 
> Many
> thanks for your time.
> 
> kind regards
> Meenu
> P.S. Thanks for your reply John. I've tried to put only
> the relevant columns
> 
> of the dataframe. Hope its more clear now.
> 
>      
>  \\\\\\\\\\\\\\\\\\//////////////////
> 
>    
>    [[alternative HTML version deleted]]
> 
>       ^^^^^^^^^^^^^^Note: ^^^^^^^^^^^^^^^^^^^^^^
> 
> 
> David Winsemius, MD
> Heritage Laboratories
> West Hartford, CT
> 
> 
> 
> 


      __________________________________________________________________
Reclaim your name @ymail.com or @rocketmail.com. Get your new email address now! Go to http://ca.promos.yahoo.com/jacko/