R - from long to wide and back to the same long format

Question

I have Compustat data in a long format that I convert to a wide format spread(from a package tidyr).

Then I do some calculations, and after that I want to return the data file to a long format again. Is there any “memory” function, so my new long data framework is exactly the same as the old one (same order).

The problem is that there is a lot NA, and the data of each stock starts when stocks are first listed and end after they are divisible or at the end of the sample. My sample runs from 1960 to 2015 (quarterly). Of course, not all stocks have data for all these dates, but when I return from a wide format to a long one, each stock receives all dates from 1960 to 2015 .4. This long data format is part of the evaluation I create, and I have to compare it with other long data formats (they all have the same order in kypermno and date), because of this I need to convert the wide data frame back to exact original form with new values ​​only.

Edit: Here is an example of my problem:

long format "original" (called "test"):

    `kypermno fyyyyq ROE_Q
      <int>  <int> <dbl>
1      1001   1985  0.56
2      1001   1986  0.43
3      1001   1987  0.78
4      1001   1988    NA
5      1001   1989  0.34
6      1001   1990  0.76
7      1002   1980  0.12
8      1002   1981  0.67
9      1002   1982  0.12
10     1002   1983  0.56
11     1002   1984    NA
12     1002   1985  0.91
13     1002   1986  0.45
14     1002   1987  0.23
15     1002   1988  0.54
16     1002   1989  0.14
17     1002   1990  0.19
18     1002   1991  0.27`

:

dat_wide <- spread(test, kypermno, ROE_Q)

:

 fyyyyq `1001` `1002`
*   <int>  <dbl>  <dbl>
1    1980     NA   0.12
2    1981     NA   0.67
3    1982     NA   0.12
4    1983     NA   0.56
5    1984     NA     NA
6    1985   0.56   0.91
7    1986   0.43   0.45
8    1987   0.78   0.23
9    1988     NA   0.54
10   1989   0.34   0.14
11   1990   0.76   0.19
12   1991     NA   0.27

, :

dat_long <- gather(dat_wide, key = 'fyyyyq', value = 'ROE_Q', -kypermno)

fyyyyq kypermno ROE_Q
    <int>    <chr> <dbl>
1    1980     1001    NA
2    1981     1001    NA
3    1982     1001    NA
4    1983     1001    NA
5    1984     1001    NA
6    1985     1001  0.56
7    1986     1001  0.43
8    1987     1001  0.78
9    1988     1001    NA
10   1989     1001  0.34
11   1990     1001  0.76
12   1991     1001    NA
13   1980     1002  0.12
14   1981     1002  0.67
15   1982     1002  0.12
16   1983     1002  0.56
17   1984     1002    NA
18   1985     1002  0.91
19   1986     1002  0.45
20   1987     1002  0.23
21   1988     1002  0.54
22   1989     1002  0.14
23   1990     1002  0.19
24   1991     1002  0.27

, NA ( , long to wide), NA omit , NA ( ). , , (18 ) , , ( 24 "" NA).

, .

PS: , kypermno fyyyyq ( ), , .

Answer 1

fill=, , "" . , NA, .

, , .

NB: , .

test <- read.table(text = 
'ID kypermno fyyyyq ROE_Q
1      1001   1985  0.56
2      1001   1986  0.43
3      1001   1987  0.78
4      1001   1988    NA
5      1001   1989  0.34
6      1001   1990  0.76
7      1002   1980  0.12
8      1002   1981  0.67
9      1002   1982  0.12
10     1002   1983  0.56
11     1002   1984    NA
12     1002   1985  0.91
13     1002   1986  0.45
14     1002   1987  0.23
15     1002   1988  0.54
16     1002   1989  0.14
17     1002   1990  0.19
18     1002   1991  0.27',
header = TRUE)
test <- test[,-1]

library(tidyr)
dat_wide <- spread(test, kypermno, ROE_Q)
dat_wide

dat_long <- gather(dat_wide, key = 'kypermno', value = ROE_Q, -fyyyyq)
dat_long


# Keep only the original data
dat_long[ paste(dat_long[,2], dat_long[,1]) %in% paste(test[,1], test[,2]),]

# Alternative (shorter and probably better)
merge(test[,1:2], dat_long, all.x=TRUE)

, , , ...