3

I'm worried that my title does not exactly capture my question. Let me explain my issue as best as I can. This is part of my data.

pdpass begyr1 gvkey1 endyr1 begyr2 gvkey2 endyr2 begyr3 gvkey3 endyr3 begyr4 gvkey4 endyr4 begyr5 gvkey5 endyr5
-16348   1991  26005   1993   1994   8852   1996   1997   3708   2005     NA     NA     NA     NA     NA     NA
-16179   2006 174876   2006     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA
-16161   1993  30964   2005     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA
-15631   1996 111491   2006     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA
-14668   1988  15854   2003     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA     NA
-13980   1986  13218   1995   1996   9921   2005     NA     NA     NA     NA     NA     NA     NA     NA     NA

Since it is a wide data so I will type in the dput value as well.

#dput

structure(list(pdpass = c(-16348L, -16179L, -16161L, -15631L, -14668L, -13980L),
begyr1 = c(1991L, 2006L, 1993L, 1996L, 1988L,  1986L), gvkey1 = c(26005L, 174876L, 30964L, 111491L, 15854L, 13218L), endyr1 = c(1993L, 2006L, 2005L, 2006L, 2003L, 1995L), 
begyr2 = c(1994L, NA, NA, NA, NA, 1996L), gvkey2 = c(8852L, NA, NA, NA, NA, 9921L), endyr2 = c(1996L, NA, NA, NA, NA, 2005L), 
begyr3 = c(1997L, NA, NA, NA, NA, NA), gvkey3 = c(3708L, NA, NA, NA, NA, NA), endyr3 = c(2005L, NA, NA, NA, NA, NA),
begyr4 = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), gvkey4 = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), endyr4 = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_),
begyr5 = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), gvkey5 = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), endyr5 = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_)), row.names = c(NA, -6L), class = "data.frame", .Names = c("pdpass", "begyr1", "gvkey1", "endyr1", "begyr2", "gvkey2", "endyr2", "begyr3", "gvkey3", "endyr3", "begyr4", "gvkey4", "endyr4", "begyr5", "gvkey5", "endyr5"))

I would like to to tidy it into a long format.

The pdpass column is the ID variable. I would like to gather all begyrs, endyears, and gvkey values for each pdpass value. However, as you can see from the original data, some pdpass have only one gvkey, endyear, begyears values where as some have more than one. (They are indicated as gvkey2, endyear2, begyear2, gvkey3 ... and so on).

Basically, I would like to make a row only when the gvkey-endyear-begyear values end with the same number for each pdpass

Easily put, this is my desired output: 

   pdpass gvkeyN  gvkey begyearN begyear endyearN endyear
1  -16348 gvkey1  26005 begyear1    1991 endyear1    1993
2  -16348 gvkey2   8852 begyear2    1994 endyear2    1996
3  -16348 gvkey3   3708 begyear3    1997 endyear3    2005
4  -16179 gvkey1 174876 begyear1    2006 endyear1    2006
5  -16161 gvkey1  30964 begyear1    1993 endyear1    2005
6  -15631 gvkey1 111491 begyear1    1996 endyear1    2006
7  -14668 gvkey1  15854 begyear1    1988 endyear1    2003
8  -13980 gvkey1  13218 begyear1    1986 endyear1    1995
9  -13980 gvkey2  13218 begyear1    1996 endyear2    2005
10 -13956 gvkey1   8674 begyear1    1968 endyear2    1987    

# code
desired<-data.frame(pdpass=c(rep(-16348, 3), -16179, -16161, -15631, -14668, rep(-13980, 2), -13956),
gvkeyN=c("gvkey1", "gvkey2", "gvkey3", rep("gvkey1", 5), "gvkey2", "gvkey1"),
gvkey=c(26005, 8852, 3708, 174876, 30964, 111491, 15854, 13218, 13218, 8674),
begyearN=c("begyear1", "begyear2", "begyear3", rep("begyear1", 5), "begyear1", "begyear1"),
begyear=c(1991, 1994, 1997, 2006, 1993, 1996, 1988, 1986, 1996, 1968), 
endyearN=c("endyear1", "endyear2", endyear3", rep("endyear1", 5), "endyear2", "endyear2"),
endyear=c(1993, 1996, 2005, 2006, 2005, 2006, 2003, 1995, 2005, 1987))`

This is what I've tried so far

require(tidyr)
d1<-gather(NBER, gvkeyN, gvkey, -pdpass, -endyr1, -begyr1, -endyr2, -begyr2, -endyr3, -begyr3, -endyr4, -begyr4, -endyr5, -begyr5, na.rm=TRUE)
d2<-gather(d1, begyrN, begyear, -gvkeyN, -gvkey, -pdpass, -endyr1, -endyr2, -endyr3, -endyr4, -endyr5, na.rm=TRUE)
d3<-gather(d2, endyrN, endyear, -gvkeyN, -gvkey, -pdpass, -begyrN, -begyear, na.rm=TRUE)
d4<-arrange(d3, pdpass)
head(d4, 10)

pdpass gvkeyN gvkey begyrN begyear endyrN endyear
1  -16348 gvkey1 26005 begyr1    1991 endyr1    1993
2  -16348 gvkey2  8852 begyr1    1991 endyr1    1993
3  -16348 gvkey3  3708 begyr1    1991 endyr1    1993
4  -16348 gvkey1 26005 begyr2    1994 endyr1    1993
5  -16348 gvkey2  8852 begyr2    1994 endyr1    1993
6  -16348 gvkey3  3708 begyr2    1994 endyr1    1993
7  -16348 gvkey1 26005 begyr3    1997 endyr1    1993
8  -16348 gvkey2  8852 begyr3    1997 endyr1    1993
9  -16348 gvkey3  3708 begyr3    1997 endyr1    1993
10 -16348 gvkey1 26005 begyr1    1991 endyr2    1996

As you can see, I get unnecessary rows. All I need is rows where the numbers at the end of gvkey, endyear, and begyear exactly match. (e.g., gvkey 1 begyr 1 endyr 1 ; gvkey 2 begyr 2 endyr 2; gvkey3 begyr3 endyr3 and so on.); however, I get combinations of 1,2 and 3. Thus, row 2:9 above are all unnecessary.

Perhaps, I could somehow manually delete all the unnecessary rows after gathering. But, I thought that may be someway to make better use of the gather function.. I've looked at similar questions but couldn't find the solution for this problem. Could anyone please help me with any of these solutions?

Darren
  • 71
  • 6
  • The [documentation](http://stackoverflow.com/documentation/r/2904/reshaping-data-between-long-and-wide-forms#t=201607251536574365737) may also be helpful. – lmo Jul 25 '16 at 15:37

1 Answers1

6

We can use melt from data.table

library(data.table)
melt(setDT(df1), measure = patterns("^gvkey", "^begyr", "^endyr"), 
    na.rm = TRUE, value.name = c("gvkey", "begyear", "endyear"))
akrun
  • 674,427
  • 24
  • 381
  • 486