CODEWITHSUNDEEP
rdplyr
ulima2_
ulima2_

Reputation: 1336

Report first and last non-missing observation for variables

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        |
-------------------------------------------------------------------------

Upvotes: 2

Views: 399

Answers (3)

Preston
Preston

Reputation: 8187

I'm a bit late to the party, the other answers are great, but if you want to stick with tidyverse and the data in the format you specified, try the below:

library(tidyverse)

# Put the data in a tidy format
gathered_df <- df %>%
  gather(key = series_no, value = val, series1:series2, na.rm = TRUE)

# find the first and last by country and series
sum_df <- gathered_df %>%
  group_by(series_no, country) %>%
  summarise(Last = max(year),
            First = min(year))

# make min and max into a column, then add a label
# Eg First:series2
reduced_df <- sum_df %>%
  gather(key = measurement, value = year, First:Last) %>%
  mutate(label = factor(paste(series_no, ":", measurement))) %>%
  group_by(label) %>%
  select(label, year, country)

# Put the output in a table format as you wanted
output <- reduced_df %>%
  spread(key = label, value = year)

Upvotes: 3

akrun
akrun

Reputation: 887223

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

Upvotes: 4

talat
talat

Reputation: 70286

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.

Upvotes: 4

Related Questions