Grouping stock data by identifier and creating new columns with data.table

[Pia]

Hi all,

I want to clean my stock data which is in the following format (the PERMNO is the identifier for the stock/company):

To work better with the data, I want to create a new column for each stock/PERMNO with all prices underneath (of course matching the respective date), so that i have the price data in long format in individual columns for each company respectively.

If have managed to group the data by PERMNO with the following line, but I have no idea how to put it in new columns now.
DT[,print(.SD),by=PERMNO])

Would really appreciate your help!

[FJCC]

Is this what you are trying to do?

library(data.table)
DT <- data.table(PERMNO = rep(c(10026, 10027), each = 3), 
                 Date = rep(c(20180101, 20180102, 20180103), 2),
                 PRC = c(1,2,3,4,5,6))
DT
#>    PERMNO     Date PRC
#> 1:  10026 20180101   1
#> 2:  10026 20180102   2
#> 3:  10026 20180103   3
#> 4:  10027 20180101   4
#> 5:  10027 20180102   5
#> 6:  10027 20180103   6
DTwide <- dcast(DT, Date ~ PERMNO, value.var = "PRC")
DTwide
#>        Date 10026 10027
#> 1: 20180101     1     4
#> 2: 20180102     2     5
#> 3: 20180103     3     6

^{Created on 2019-09-12 by the reprex package (v0.2.1)}

[Pia]

YESSS!!! Wow I am so thankful! I am new with R and just started my master thesis, and I literally sat on this stupid problem for an entire day now. Thank you so much!!!

[FJCC]

If you will be working with data.tables, the vignettes available at the CRAN site are very helpful.

https://cran.r-project.org/web/packages/data.table/index.html

[Pia]

Great, thank you! Will have a look into that.

[jcblum]

Hi @Pia! Welcome! I’m glad to see someone here was able to get you unstuck! I hope you’ll keep posting questions as you keep learning .

You might also want to take a look at our Tips for Writing R-related Questions to learn about how to prepare your questions to get the best help here. For example, while it’s pretty natural for people to start out posting screenshots of their data, that’s actually a fairly heavy lift for your helpers (they have to re-type all your data by hand just to get started poking at your problem). The tips page links to several resources for learning more helper-friendly ways of composing your questions.

[Pia]

Hi @jcblum. Thanks for the welcome and the helpful tips. I will definitely look into it. Great seeing that everyone is so helpful!