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

[Meenu Sahi]

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
>
>
-------------- next part --------------
pstate<-read.table(textConnection("Changes PState1 PState2 PState3 PState4
+ 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)


mydata4<-within(mydata4, {
PState1<-NA
PState2<-NA
PState3<-NA
PState4<-NA
})
#################OUTPUT
#I would like to see my output of mydata4 with NA in the last 4 columns replaced by matching probabilities
# from table pstate in whichever of the 4 columns are applicable depending on the State and Change. e.g. Row1
# of mydata4 has Change b and State1 and therefore PState1 should be filled by the probability pb1 from table pstate
# Output should be as follows:
 Change  State  PState1  PState2  PState3  PState4
1 b      State1  Pb1      NA      NA       NA
2 a      State4  NA       NA      NA       Pa4
3 b      State2  NA       Pb2     NA       NA
4 c      State3  NA       NA      Pc3      NA
5 d      State1  Pd1      NA      NA       NA
6 a      State3  NA       NA      Pa3      NA