Meenu Sahi
2009-Aug-01 13:52 UTC
[R] Add columns in a dataframe and fill them from another table according to a criteria
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?
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]]
David Winsemius
2009-Aug-01 16:13 UTC
[R] Add columns in a dataframe and fill them from another table according to a criteria
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
David Winsemius
2009-Aug-02 00:36 UTC
[R] Add columns in a dataframe and fill them from another table according to a criteria
Apologies to list: Should have "replied to all". -- DW Begin forwarded message:> From: David Winsemius <dwinsemius@comcast.net> > Date: August 1, 2009 3:02:58 PM EDT > To: Meenu Sahi <meenusahi@gmail.com> > Subject: Re: [R] Add columns in a dataframe and fill them from > another table according to a criteria > > > On Aug 1, 2009, at 1:43 PM, Meenu Sahi wrote: > >> 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. > > You need to figure out how to send mail to the list with plain text. > But I suspect you did successfully get the attchment through to the > audience. > > I did not like the ordering of the PStates in your new target > dataframe so I changed it to fit my(and your) purposes. > > > Change<-c("b","a","b","c","d","a") > > State<-c("State1","State4","State2","State3","State1","State3") > > > > mydata4<-data.frame(Change,State) > > mydata4<-data.frame(mydata4, > + PState1=NA, > + PState2=NA, > + PState3=NA, > + PState4=NA > + ) > > mydata4 > Change State PState1 PState2 PState3 PState4 > 1 b State1 NA NA NA NA > 2 a State4 NA NA NA NA > 3 b State2 NA NA NA NA > 4 c State3 NA NA NA NA > 5 d State1 NA NA NA NA > 6 a State3 NA NA NA NA > > Note that str(pstate shows that State is a factor which becomes > important. > > This now effects the desired transformation: > > for (i in 1:length(mydata4) ) > { mydata4[i, as.numeric( mydata4[i, "State"])+2 ] <- > #assign to the i-th row, State + 2 column in > mydata4 ... > pstate[ mydata4[i, "Change"], as.numeric( mydata4[i, > "State"])+1 ] } > #... the value of i-th row, State+1 column of pstate > > > mydata4 > 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> > > The main non-obvious "trick" is the as.numeric( mydata4[i, > "State"]) bit. as.numeric() when applied to a factor results in a > numeric offset derived from the factor coding rather than using the > level names. I suppose I could have left the PState<n>'s in the > original order but then I would have been subtracting them from 7 to > get the proper column number. Seemed even less understandable > >> >> Regards >> Meenu >> >> On Sat, Aug 1, 2009 at 9:43 PM, David Winsemius <dwinsemius@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 >> >> >> <Input and Output.txt> > > David Winsemius, MD > Heritage Laboratories > West Hartford, CT >David Winsemius, MD Heritage Laboratories West Hartford, CT [[alternative HTML version deleted]]