0
$\begingroup$

I've been working in SAS for a few years but as my time as a student with a no-cost-to-me license comes to an end, I want to learn R.

Is it possible to transpose a data set so that all the observations for a single ID are on the same line? (I have 2-8 observations per unique individual but they are currently arranged vertically rather than horizontally.) In SAS, I had been using PROC SQL and PROC TRANSPOSE depending on my analysis aims.

Example:

ID    date        timeframe  fruit_amt   veg_amt <br/> 
4352  05/23/2013  before     0.25        0.75 <br/> 
5002  05/24/2014  after      0.06        0.25 <br/> 
4352  04/16/2014  after      0           0 <br/> 
4352  05/23/2013  after      0.06        0.25 <br/> 
5002  05/24/2014  before     0.75        0.25 <br/>

Desired:

ID    B_fr05/23/2013   B_veg05/23/2013  A_fr05/23/2013  A_veg05/23/2013   B_fr05/24/2014   B_veg05/24/2014   (etc)  <br/>
4352  0.25             0.75             0.06            0.25              .                .  <br/>
5002  .                .                .               .                 0.75             0.25 <br/>
Improve this question
$\endgroup$
4
  • $\begingroup$ R is a good way to go! Can you please add a data sample and desired output you want to get? $\endgroup$
    – IgorS
    Commented Dec 22, 2014 at 14:52
  • $\begingroup$ Columns are separated by commas and lines by semicolon: ID, timeframe, fruit_amt, veg_amt; 4352, before, 0.25, 0.75; $\endgroup$
    – Andrea Bontrager Yoder
    Commented Dec 22, 2014 at 15:17
  • $\begingroup$ Trying again. Columns are separated by commas and lines by semicolon: ID, date, timeframe, fruit_amt, veg_amt; 4352, 05/23/2013, before, 0.25, 0.75; 5002, 05/24/2014, after, 0.06, 0.25; 4352, 04/16/2014, after, 0, 0; 4352, 05/23//2013, after, 0.06, 0.25; 5002, 05/24/2014, before, 0.75, 0.25; I want to have all the observations for a single ID (ex, 4352 or 5002) on one row (making the data set much wider). My data set is much wider than this to begin with (I've got about 50 different columns per current observation) but this is the basic idea. $\endgroup$
    – Andrea Bontrager Yoder
    Commented Dec 22, 2014 at 15:23
  • $\begingroup$ You could better include it into your post and format in proper way... $\endgroup$
    – IgorS
    Commented Dec 22, 2014 at 15:40

2 Answers 2

Reset to default
0
$\begingroup$

You can use the reshape2 package for this task.

First, transform the data to the long format with melt:

library(reshape2)
dat_m <- melt(dat, measure.vars = c("fruit_amt", "veg_amt"))

where dat is the name of your data frame.

Second, cast to the wide format:

dcast(dat_m, ID ~ timeframe + variable + date)

The result:

    ID after_fruit_amt_04/16/2014 after_fruit_amt_05/23/2013 after_fruit_amt_05/24/2014 after_veg_amt_04/16/2014
1 4352                          0                       0.06                         NA                        0
2 5002                         NA                         NA                       0.06                       NA
  after_veg_amt_05/23/2013 after_veg_amt_05/24/2014 before_fruit_amt_05/23/2013 before_fruit_amt_05/24/2014
1                     0.25                       NA                        0.25                          NA
2                       NA                     0.25                          NA                        0.75
  before_veg_amt_05/23/2013 before_veg_amt_05/24/2014
1                      0.75                        NA
2                        NA                      0.25
>
Improve this answer
$\endgroup$
0
$\begingroup$

Try 'arrange(Data.frame.name, ID)' function from package 'dplyr'

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.