reshape data with non-unique id and varying time frames

Question

I have a dataset with the following format:

name1 year name2 profits2010 profits2009 count 
AA    2009  AA    10           15          20
AA    2010  AA    10           15          3
BB    2009  BB     4           NA          34
BB    2010  BB     4           NA          4

I need to reshape the data to this format.Any ideas on how this can be done?

name1 year name2 profits count
AA    2009 AA     15       20
AA    2010 AA     10       3
BB    2009 BB     NA       34
BB    2010 BB     4      4

If you are looking for to match the `year` column and the colnames `profits2009` and `profits2010`,then the input names would be reversed — akrun, May 26 '15 at 17:21
I meant for the first row, year is 2009, but you selected element from profits2010. and the same way for 2010, selected from profits2009. — akrun, May 26 '15 at 17:23
Great! corrected this. I will check your code . I used multiple if as suggested it worked. — user3570187, May 26 '15 at 17:44
I didn't know about this. I thought it is for people with high reputation or something. Ok I will accept the best solution ;) — user3570187, May 26 '15 at 18:33
It will give encouragement for people to answer your questions. BTW, have you tried my solution with multiple years. It should work. — akrun, May 26 '15 at 18:34

score 0 · Answer 1 · answered May 26 '15 at 17:11

0

This isn't really reshaping, just defining a new variable. Try this:

df$profits <- ifelse(df$year==2009,df$profits2009,df$profits2010)

answered May 26 '15 at 17:11

jrdnmdhl

1,935
18
26

I have like 8 years data. will the code be the same – user3570187 May 26 '15 at 17:17
@akrun Why would it be done that way? Second argument of ifelse is the true value and the third is the false value. If the year is 2009, it should grab the 2009 Value. – jrdnmdhl May 26 '15 at 17:42
@user3570187 if there are more years then you will need something a bit more complicated, but you can probably just nest several ifelse statements. – jrdnmdhl May 26 '15 at 17:44
@jrdnmdhl OP changed the expected output and still I think it is not the correct. We may need to wait to find what the true expected value – akrun May 26 '15 at 17:44
My code will assign 2009 values to 2009 year etc... as long as that is the intended functionality I believe my code works. If OP is trying to do something else then it won't though. – jrdnmdhl May 26 '15 at 17:46

score 0 · Accepted Answer · answered May 26 '15 at 17:34

Try

 indx <- grep('profits', names(df1))
 indx2 <- cbind(1:nrow(df1), match(df1$year, 
               as.numeric(sub('\\D+', '',   names(df1)[indx]))))
 df1$profits <- df1[indx][indx2]
 df1[-indx]
 #   name1 year name2 count profits
 #1    AA 2009    AA    20      15
 #2    AA 2010    AA     3      10
 #3    BB 2009    BB    34      NA
 #4    BB 2010    BB     4       4

reshape data with non-unique id and varying time frames

2 Answers2