1

I have following panel data:

firmid date return
1        1     1
1        2     1
1        3     1
2        2     2
2        3     2
3        1     2
3        3     2

I want to transform this long format to wide but only for date 1 to look like this

firmid return in date=1
 1        1
 3        2

I appreciate any advice!

cmaher
  • 5,100
  • 1
  • 22
  • 34
user9259005
  • 465
  • 1
  • 4
  • 12
  • 1
    Isn't the result you posted still in long format? I believe you can get something that looks like your desired result if you do: `mat[mat$date == 1,]` – Mike H. Feb 06 '18 at 21:31
  • 1
    Possible duplicate of [Filtering a data frame by values in a column](https://stackoverflow.com/questions/7381455/filtering-a-data-frame-by-values-in-a-column) – Mike H. Feb 06 '18 at 22:42
  • Similar to @Mike H, you could also use `subset(mat, date == 1)` – David C. Feb 07 '18 at 00:14

3 Answers3

2
df <- read.table(header = T, text = "firmid date return
1        1     1
1        2     1
1        3     1
2        2     2
2        3     2
3        1     2
3        3     2")

Base R solution:

df <- df[df$date == 1, ]
df$date <- NULL
df
     firmid return
1      1      1
6      3      2

data.table solution:

library(data.table)
setDT(df)
df <- df[date == 1, ]
df[, date := NULL]

     firmid return
1:      1      1
2:      3      2
sm925
  • 2,648
  • 1
  • 16
  • 28
1

You can use dplyr to achieve it too:

library(dplyr)

df2 <- df %>% 
    filter(date == 1) %>%
    select(-date)

#  firmid return
#1      1      1
#2      3      2
patL
  • 2,259
  • 1
  • 17
  • 38
0

A different dplyr solution that allows you to have multiple values of return within firmid:

df %>%
  filter(date == 1) %>%
  group_by(firmid, return) %>%
  summarise()
JereB
  • 137
  • 8