CODEWITHSUNDEEP
rdplyr
Nellicopter
Nellicopter

Reputation: 83

Get the year of the latest available data in R

I would like to get the year of the latest available data for each combination of country(iso3), indicator(gho_id),gender(sex), type of residence(location). The data are stored in "gho_value". My dataset looks like that:

# A tibble: 16 x 6
   iso3  gho_id sex   location  year gho_value
   <chr> <chr>  <chr> <chr>    <dbl>     <dbl>
 1 AFG   BP_04  FMLE  <NA>      2000      28.6
 2 AFG   BP_04  FMLE  <NA>      2001      28.8
 3 AFG   BP_04  FMLE  <NA>      2002      29  
 4 AFG   BP_04  FMLE  <NA>      2003      29.2
 5 AFG   BP_04  FMLE  <NA>      2004      29.4
 6 AFG   BP_04  FMLE  <NA>      2005      29.5
 7 AFG   BP_04  FMLE  <NA>      2006      29.7
 8 AFG   BP_04  FMLE  <NA>      2007      29.8
 9 AFG   BP_04  FMLE  <NA>      2008      29.9
10 AFG   BP_04  FMLE  <NA>      2009      30.1
11 AFG   BP_04  FMLE  <NA>      2010      30.2
12 AFG   BP_04  FMLE  <NA>      2011      30.3
13 AFG   BP_04  FMLE  <NA>      2012      30.4
14 AFG   BP_04  FMLE  <NA>      2013      30.5
15 AFG   BP_04  FMLE  <NA>      2014      NA
16 AFG   BP_04  FMLE  <NA>      2015      NA

I would like to have this:

# A tibble: 16 x 7
   iso3  gho_id sex   location  year gho_value last_year
   <chr> <chr>  <chr> <chr>    <dbl>     <dbl>     <dbl>
 1 AFG   BP_04  FMLE  <NA>      2000      28.6      2013
 2 AFG   BP_04  FMLE  <NA>      2001      28.8      2013
 3 AFG   BP_04  FMLE  <NA>      2002      29        2013
 4 AFG   BP_04  FMLE  <NA>      2003      29.2      2013
 5 AFG   BP_04  FMLE  <NA>      2004      29.4      2013
 6 AFG   BP_04  FMLE  <NA>      2005      29.5      2013
 7 AFG   BP_04  FMLE  <NA>      2006      29.7      2013
 8 AFG   BP_04  FMLE  <NA>      2007      29.8      2013
 9 AFG   BP_04  FMLE  <NA>      2008      29.9      2013
10 AFG   BP_04  FMLE  <NA>      2009      30.1      2013
11 AFG   BP_04  FMLE  <NA>      2010      30.2      2013
12 AFG   BP_04  FMLE  <NA>      2011      30.3      2013
13 AFG   BP_04  FMLE  <NA>      2012      30.4      2013
14 AFG   BP_04  FMLE  <NA>      2013      30.5      2013
15 AFG   BP_04  FMLE  <NA>      2014      NA        2013
16 AFG   BP_04  FMLE  <NA>      2015      NA        2013

I have tried to use the tidyverse package (dpylr) and did this:

ungho2 <- left_join(ungho,
            ungho %>%
            arrange(iso3,gho_id,sex,location,year) %>%
            group_by(iso3,gho_id,sex,location) %>%
            filter(!(is.na(gho_value))) %>%
            mutate(latest_year_gho = last(year)) %>%
            ungroup(iso3,gho_id,sex,location))

My problem is that I get this (NA instead of 2013 when I use left_join): 

# A tibble: 16 x 7
   iso3  gho_id sex   location  year gho_value last_year
   <chr> <chr>  <chr> <chr>    <dbl>     <dbl>     <dbl>
 1 AFG   BP_04  FMLE  <NA>      2000      28.6      2013
 2 AFG   BP_04  FMLE  <NA>      2001      28.8      2013
 3 AFG   BP_04  FMLE  <NA>      2002      29        2013
 4 AFG   BP_04  FMLE  <NA>      2003      29.2      2013
 5 AFG   BP_04  FMLE  <NA>      2004      29.4      2013
 6 AFG   BP_04  FMLE  <NA>      2005      29.5      2013
 7 AFG   BP_04  FMLE  <NA>      2006      29.7      2013
 8 AFG   BP_04  FMLE  <NA>      2007      29.8      2013
 9 AFG   BP_04  FMLE  <NA>      2008      29.9      2013
10 AFG   BP_04  FMLE  <NA>      2009      30.1      2013
11 AFG   BP_04  FMLE  <NA>      2010      30.2      2013
12 AFG   BP_04  FMLE  <NA>      2011      30.3      2013
13 AFG   BP_04  FMLE  <NA>      2012      30.4      2013
14 AFG   BP_04  FMLE  <NA>      2013      30.5      2013
15 AFG   BP_04  FMLE  <NA>      2014      NA        NA
16 AFG   BP_04  FMLE  <NA>      2015      NA        NA

What could I do?

Thank you very much for your help!

Upvotes: 0

Views: 67

Answers (3)

hello_friend
hello_friend

Reputation: 5798

Base R solution:

df <- data.frame(do.call("rbind", lapply(split(df, paste0(df$iso3, df$gho_id, df$sex, df$location)),

       function(x){x$last_year <- max(x$year[!(is.na(x$gho_value))]); return(x)})), row.names = NULL)

Tidyverse solution (handling case when the entire series is NA): 

require(dplyr)

df %>%

  group_by(iso3, gho_id, sex, location) %>%

  mutate(last_year = ifelse(all(is.na(gho_value)), NA, max(year[!is.na(gho_value)]))) %>% 

  ungroup()

Data:

df <- data.frame(iso3 = rep("AFG", 16),

          gho_id = rep("BP04", 16),

          sex = rep("FMLE", 16),

          location = rep(NA, 16),

          year = seq.int(2000, 2015),

          gho_value = as.numeric(c(28.6, 28.8, 29, 29.2, 29.4, 29.5, 29.7, 29.8,
                         29.9, 30.1, 30.2, 30.3, 30.4, 30.5, NA, NA)))

Upvotes: 0

cimentadaj
cimentadaj

Reputation: 1488

Since you're already using dplyr, here's a simple solution:

df <- data.frame(iso3 = rep("AFG", 16),
          gho_id = rep("BP04", 16),
          sex = rep("FMLE", 16),
          location = rep(NA, 16),
          year = seq.int(2000, 2015),
          gho_value = as.numeric(c(28.6, 28.8, 29, 29.2, 29.4, 29.5, 29.7, 29.8,
                                   29.9, 30.1, 30.2, 30.3, 30.4, 30.5, NA, NA)))


library(dplyr)
df %>%
  # Group by the variables you wanted to
  group_by(iso3, gho_id, sex, location) %>%
  # Get the max year when `gho_value` is not NA for each group defined above
  mutate(last_year = max(year[!is.na(gho_value)]))

Upvotes: 1

ThomasIsCoding
ThomasIsCoding

Reputation: 102920

With base R, I guess the following code might help (assuming df is data.frame for your data)

df$last_year <- df$year[tail(which(rowSums(!is.na(df[-4]))==ncol(df[-1])),1)]

which gives

> df
   iso3 gho_id  sex location year gho_value last_year
1   AFG  BP_04 FMLE     <NA> 2000      28.6      2013
2   AFG  BP_04 FMLE     <NA> 2001      28.8      2013
3   AFG  BP_04 FMLE     <NA> 2002      29.0      2013
4   AFG  BP_04 FMLE     <NA> 2003      29.2      2013
5   AFG  BP_04 FMLE     <NA> 2004      29.4      2013
6   AFG  BP_04 FMLE     <NA> 2005      29.5      2013
7   AFG  BP_04 FMLE     <NA> 2006      29.7      2013
8   AFG  BP_04 FMLE     <NA> 2007      29.8      2013
9   AFG  BP_04 FMLE     <NA> 2008      29.9      2013
10  AFG  BP_04 FMLE     <NA> 2009      30.1      2013
11  AFG  BP_04 FMLE     <NA> 2010      30.2      2013
12  AFG  BP_04 FMLE     <NA> 2011      30.3      2013
13  AFG  BP_04 FMLE     <NA> 2012      30.4      2013
14  AFG  BP_04 FMLE     <NA> 2013      30.5      2013
15  AFG  BP_04 FMLE     <NA> 2014        NA      2013
16  AFG  BP_04 FMLE     <NA> 2015        NA      2013

DATA

df <- structure(list(iso3 = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
                                        1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "AFG", class = "factor"), 
                     gho_id = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
                                          1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "BP_04", class = "factor"), 
                     sex = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
                                       1L, 1L, 1L, 1L, 1L, 1L), .Label = "FMLE", class = "factor"), 
                     location = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
                                            1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "<NA>", class = "factor"), 
                     year = 2000:2015, gho_value = c(28.6, 28.8, 29, 29.2, 29.4, 
                                                     29.5, 29.7, 29.8, 29.9, 30.1, 30.2, 30.3, 30.4, 30.5, NA, 
                                                     NA)), row.names = c(NA, -16L), class = "data.frame")

Upvotes: 0

Related Questions