[R] reshaping column items into rows per unique ID
Jim Lemon
drjimlemon at gmail.com
Sun Feb 25 22:48:51 CET 2018
Hi Allaisone,
If you want a data frame as the output you will have to put up with a
few NA values unless each Customer has the same number of diet types:
a1df<-read.table(text="CustomerID DietType
1 a
1 c
1 b
2 f
2 a
3 j
4 c
4 c
4 f",
header=TRUE,stringsAsFactors=FALSE)
library(prettyR)
stretch_df(a1df,"CustomerID","DietType")
CustomerID DietType_1 DietType_2 DietType_3
1 1 a c b
2 2 f a <NA>
3 3 j <NA> <NA>
4 4 c c f
Jim
On Sun, Feb 25, 2018 at 11:59 PM, Allaisone 1 <allaisone1 at hotmail.com> wrote:
> Hi All
>
> I have a datafram which looks like this :
>
> CustomerID DietType
> 1 a
> 1 c
> 1 b
> 2 f
> 2 a
> 3 j
> 4 c
> 4 c
> 4 f
>
> And I would like to reshape this so I can see the list of DietTypes per customer in rows instead of columns like this :
>
>> MyDf
> CustomerID DietType DietType DietType
> 1 a c b
> 2 f a
> 3 j
> 4 c c f
>
> I tried many times using melt(),spread (),and dcast () functions but was not able to produce the desired table. The best attempt was by typing :
>
> # 1) Adding new column with unique values:
> MyDf $newcol <- c (1:9)
> #2) then :
> NewDf <- dcast (MyDf,CustomerID~newcol,value.var=DietType)
>
> This produces the desired table but with many NA values like this :
>
> CustomerID 1 2 3 4 5 6 7 8 9
> 1 a c b NA NA NA NA NA NA
> 2 NA NA NA f a NA NA NA NA
> 3 NA NA NA NA NA j NA NA NA
> 4 NA NA NA NA NA NA c c f
>
> As you see, the lette/s indicating DietType move to the right side each time we move down leaving many NA values and as my original files is very large, I expect that the final output would contain around 800,000 columns and 70,000 rows. This is why my code works with small data but does not work with my large file because of memory issue even though I'm using large PC.
>
> What changes I need to do with my code to produce the desired table where the list of DietTypes are grouped in rows exactly like the second table shown abover?
>
> Regards
> Allaisnoe
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
More information about the R-help
mailing list