create panel data in R (cross section dimension holds repeated entities)

Question

I have data that look like the following:

>loan data
           ID      loan_start_date    loan_maturity_date  feb13  march13 april13........
            1      2016-01-03         2017-01-03          46       45     44
            1      2011-01-08         2013-01-08          NA       NA     NA   
            1      2013-02-13         2015-02-13          23       22     21
            2      2012-02-03         2016-05-03          38       37     36
            2      2013-05-08         2014-01-09          10       09     08   
            2      2011-03-13         2013-02-18          0        NA     NA
            3      2015-07-03         2016-01-08          34       33     32
            3      2013-01-09         2015-07-08          28       27     26

Can I create panel data from this? If yes, how can I do that in R? In the panel data, the cross-section dimension is ID and time dimension is feb13,march13,april13 (time to loan maturity date from that specific month).....goes on for 48 months. I have looked at other examples of creating panel data but in those examples, each ID just took one row, now each ID is sitting in multiple rows. So I am confused how to create panel out of this. Would really appreciate your help.

Edit: The expected outcome should look like this if I am correct:

>loan data
           ID      months    time to maturity  
            1      feb13         46          
            1      march13       45            
            1      april13       44          
                      .
                      .
                      .
            1      jan17          0          
            1      feb13         NA          
            1      march13       NA            
            1      april13       NA          
                      .
                      .
                      .
            1      jan17         NA          
            1      feb13         23          
            1      march13       22             
            1      april13       21          
                      .
                      .
                      .
            1      jan17         NA        
            2      feb13         38          
            2      march13       37            
            2      april13       36         
                     .
                     .
                     .
            2      jan17         NA 
            2      feb13         10          
            2      march13       09            
            2      april13       08         
                     .
                     .
                     .
            2      jan17         NA 
            2      feb13         0          
            2      march13       NA            
            2      april13       NA         
                     .
                     .
                     .
            2      jan17         NA 
            2      feb13         0          
            2      march13       NA            
            2      april13       NA         
                     .
                     .
                     .
            2      jan17         NA 
            3      feb13         34          
            3      march13       33            
            3      april13       32         
                     .
                     .
                     .
            3      jan17         NA 
            3      feb13         28          
            3      march13       27            
            3      april13       26         
                     .
                     .
                     .
            3      jan17         NA

It looks as though you want to switch from wide to long format: this post could help https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format — niko, Dec 27 '18 at 09:20

g_t_m · Accepted Answer · 2018-12-27T10:58:18.600

Another option is to use the gather() function from the tidyr package.

This function turns multiple columns into a single key-value pair. You need to specify the data frame, the name of the new "key" column, the name of the new "value" column, and then specify which columns to gather. If (like in this case) there are more columns to include than exclude, you can simply specify which columns to exclude instead:

library(tidyr)

gather(data, key = "month_year", value = "months_to_maturity", -(ID:loan_maturity_date))

#>    ID start_date loan_maturity_date month_year months_to_maturity
#> 1   1 2016-01-03         2017-01-03      feb13                 46
#> 2   1 2011-01-08         2013-01-08      feb13                 NA
#> 3   1 2013-02-13         2015-02-13      feb13                 23
#> 4   2 2012-02-03         2016-05-03      feb13                 38
#> 5   2 2013-05-08         2014-01-09      feb13                 10
#> 6   2 2011-03-13         2013-02-18      feb13                  0
#> 7   3 2015-07-03         2016-01-08      feb13                 34
#> 8   3 2013-01-09         2015-07-08      feb13                 28
#> 9   1 2016-01-03         2017-01-03    march13                 45
#> 10  1 2011-01-08         2013-01-08    march13                 NA
#> 11  1 2013-02-13         2015-02-13    march13                 22
#> 12  2 2012-02-03         2016-05-03    march13                 37
#> 13  2 2013-05-08         2014-01-09    march13                  9
#> 14  2 2011-03-13         2013-02-18    march13                 NA
#> 15  3 2015-07-03         2016-01-08    march13                 33
#> 16  3 2013-01-09         2015-07-08    march13                 27
#> 17  1 2016-01-03         2017-01-03    april13                 44
#> 18  1 2011-01-08         2013-01-08    april13                 NA
#> 19  1 2013-02-13         2015-02-13    april13                 21
#> 20  2 2012-02-03         2016-05-03    april13                 36
#> 21  2 2013-05-08         2014-01-09    april13                  8
#> 22  2 2011-03-13         2013-02-18    april13                 NA
#> 23  3 2015-07-03         2016-01-08    april13                 32
#> 24  3 2013-01-09         2015-07-08    april13                 26

And the data used for this:

df <- 
  data.frame(ID                 = c(1,1,1,2,2,2,3,3),
             start_date         = c("2016-01-03",
                                    "2011-01-08",
                                    "2013-02-13",
                                    "2012-02-03",
                                    "2013-05-08",
                                    "2011-03-13",
                                    "2015-07-03",
                                    "2013-01-09"),
             loan_maturity_date = c("2017-01-03",
                                    "2013-01-08",
                                    "2015-02-13",
                                    "2016-05-03",
                                    "2014-01-09",
                                    "2013-02-18",
                                    "2016-01-08",
                                    "2015-07-08"),
             feb13              = c(46,
                                    NA,
                                    23,
                                    38,
                                    10,
                                    0 ,
                                    34,
                                    28),
             march13            = c(45,
                                    NA,
                                    22,
                                    37,
                                    09,
                                    NA,
                                    33,
                                    27),
             april13            = c(44,
                                    NA,
                                    21,
                                    36,
                                    08,
                                    NA,
                                    32,
                                    26))

score 0 · Answer 2 · answered Dec 27 '18 at 09:37

As mentioned in the comments, it seems what you want can be found here. Applying yields

dt <- reshape2::melt(df, id.vars = 'ID')
head(dt)
  ID variable value
1  2    feb17    40
2  4    feb17    33
3  3    feb17    35
4  5    feb17    34
5  5    feb17    NA
6  1    feb17    38

and here is the data used for this example

set.seed(123)
df <- data.frame(ID = sample(1:5, 10, replace = TRUE), 
                 feb17 = sample(c(NA,30:40), 10),
                 mar17 = sample(c(NA,30:40), 10),
                 apr17 = sample(c(NA,30:40), 10),
                 feb18 = sample(c(NA,30:40), 10),
                 mar18 = sample(c(NA,30:40), 10),
                 apr18 = sample(c(NA,30:40), 10)
                )
> head(df)
  ID feb17 mar17 apr17 feb18 mar18 apr18
1  2    40    39    40    30    NA    36
2  4    33    36    38    33    33    30
3  3    35    35    35    39    36    32
4  5    34    37    36    32    30    31
5  5    NA    34    NA    40    39    35
6  1    38    33    32    NA    37    38

create panel data in R (cross section dimension holds repeated entities)

2 Answers2