Report first and last non-missing observation for variables

Question

I want to find out when I have the first and last non-missing observations for all my variables in may dataframe.

library(tidyverse)

df <- tribble(
  ~year, ~country, ~series1, ~series2, 
  #--|--|--|----
  2003, "USA", NA, 5,
  2004, "USA", NA, 6, 
  2005, "USA", NA, 7, 
  2006, "USA", 10, 8,
  2007, "USA", NA, 4,
  2008, "USA", NA, 10,
  2009, "USA", 16, 12,
  2010, "USA", 12, 8,
  2011, "USA", 12, 13,
  2012, "USA", 13, 10,
  2013, "USA", 11, 5,
  2005, "FRA", 5, NA, 
  2006, "FRA", 6, NA, 
  2007, "FRA", 5, NA, 
  2008, "FRA", 4, NA,
  2009, "FRA", 9, NA,
  2010, "FRA", 7, NA, 
  2011, "FRA", 14, NA,
  2012, "FRA", 7, 11, 
  2013, "FRA", 6, 6,
  2014, "FRA", 5, 7,
  2015, "FRA", 4, 5
)

df_stats <- df %>%
  group_by(country) %>%
  summarize(First = min(year, na.rm = TRUE),
            Last  = max(year, na.rm = TRUE))

Which returns df_stats:

--------------------------
| country | First | Last |
--------------------------
| FRA     | 2005  | 2015 |
--------------------------
| USA     | 2003  | 2013 |
--------------------------

But I would like to have this separately for my series1 and series2.

(In my real life problem I have many countries and many variables.)

So I would like to get:

-------------------------------------------------------------------------
| country | First.series1 | Last.series1 | First.series2 | Last.series2 |
-------------------------------------------------------------------------
| FRA     | 2005          | 2015         |  2012          | 2015        |
-------------------------------------------------------------------------
| USA     | 2006          | 2013         |  2003          | 2013        |
-------------------------------------------------------------------------

Answer 1

Here's how I'd approach it:

df %>% 
  gather(series, value, starts_with("series")) %>% 
  filter(!is.na(value)) %>% 
  group_by(country, series) %>%
  summarize(First = min(year, na.rm = TRUE),
            Last  = max(year, na.rm = TRUE))

# A tibble: 4 x 4
# Groups:   country [?]
  country  series First  Last
    <chr>   <chr> <dbl> <dbl>
1     FRA series1  2005  2015
2     FRA series2  2012  2015
3     USA series1  2006  2013
4     USA series2  2003  2013

So basically we convert the data to long-format, filter out the NA values and then compute the min/max per country and series.

Answer 2

Here is an option using data.table

library(data.table)
dcast(melt(setDT(df), id.var = c('year', 'country'), na.rm = TRUE)[, 
    .(First = min(year, na.rm = TRUE), Last = max(year, na.rm = TRUE)),
   .(country, variable)], country ~variable, value.var = c("First", "Last"), sep=".")
#   country First.series1 First.series2 Last.series1 Last.series2
#1:     FRA          2005          2012         2015         2015
#2:     USA          2006          2003         2013         2013