i am having trouble extracting some information from some data i'm working with. i want to extract the last non-NA values for each id in each year and collect these results in a table which links those values with their original id and year values.
here is what my data looks like.
dat <- data.frame(id = c(rep('a', 5), rep('b', 5), rep('c', 5)),
year = c(seq(2011, 2015), seq(2011, 2015), seq(2011, 2015)),
x = c(c(1:3, NA, NA), c(1:5), rep(NA, 5)),
y = c(c(NA, NA, 3, 4, NA), c(NA, 2, 3, 4, NA), rep(NA, 5)),
z = c(c(1:5), c(1:4, NA), rep(NA, 5)))
>
> dat
id year x y z
1 a 2011 1 NA 1
2 a 2012 2 NA 2
3 a 2013 3 3 3
4 a 2014 NA 4 4
5 a 2015 NA NA 5
6 b 2011 1 NA 1
7 b 2012 2 2 2
8 b 2013 3 3 3
9 b 2014 4 4 4
10 b 2015 5 NA NA
11 c 2011 NA NA NA
12 c 2012 NA NA NA
13 c 2013 NA NA NA
14 c 2014 NA NA NA
15 c 2015 NA NA NA
here is what my ideal result looks like. i did this by hand. if an id/year pair has a non-NA value for x, y or z but that is not the last value by year, then i want it to be NA.
for example, a has non-NA values for z in all years, so its last z value is 5 and in column z of the result table, i want NA for all years except that year for a.
> target_result <- data.frame(id = c(rep('a', 3), rep('b', 3)),
year = c(2013, 2014, 2015, 2015, 2014, 2014),
last_x = c(3, NA, NA, 5, NA, NA),
last_y = c(NA, 4, NA, NA, 4, NA),
last_z = c(NA, NA, 5, NA, NA, 4))
>
> target_result
id year last_x last_y last_z
1 a 2013 3 NA NA
2 a 2014 NA 4 NA
3 a 2015 NA NA 5
4 b 2015 5 NA NA
5 b 2014 NA 4 NA
6 b 2014 NA NA 4
this is my analytical process so far. i think my first step seems okay. i should clarify that the real data i'm working with has daily frequency. i kept my example data small for the sake of simplicity.
> step_1 <- dat %>%
group_by(id, year) %>%
summarize(last_x = last(x),
last_y = last(y),
last_z = last(z))
`summarise()` has grouped output by 'id'. You can override using the `.groups` argument.
>
> step_1
# A tibble: 15 x 5
# Groups: id [3]
id year last_x last_y last_z
<chr> <int> <int> <dbl> <int>
1 a 2011 1 NA 1
2 a 2012 2 NA 2
3 a 2013 3 3 3
4 a 2014 NA 4 4
5 a 2015 NA NA 5
6 b 2011 1 NA 1
7 b 2012 2 2 2
8 b 2013 3 3 3
9 b 2014 4 4 4
10 b 2015 5 NA NA
11 c 2011 NA NA NA
12 c 2012 NA NA NA
13 c 2013 NA NA NA
14 c 2014 NA NA NA
15 c 2015 NA NA NA
second step also seems okay. some of the rows from my ideal result are in here.
> step_2 <- step_1[rowSums(is.na(step_1[,3:5])) < 3,]
> step_2
# A tibble: 10 x 5
# Groups: id [2]
id year last_x last_y last_z
<chr> <int> <int> <dbl> <int>
1 a 2011 1 NA 1
2 a 2012 2 NA 2
3 a 2013 3 3 3
4 a 2014 NA 4 4
5 a 2015 NA NA 5
6 b 2011 1 NA 1
7 b 2012 2 2 2
8 b 2013 3 3 3
9 b 2014 4 4 4
10 b 2015 5 NA NA
but i know that my third step is wrong because it destroys the relationship between the last_ columns and year and just collapses everything into a table that doesn't contain any NA values.
> small_and_wrong <- as.data.frame(as.data.table(step_2)[, lapply(.SD, function(x) last(na.omit(x))), id])
> small_and_wrong
id year last_x last_y last_z
1 a 2015 3 4 5
2 b 2015 5 4 4
any advice for how to get back on track? thank you in advance.
CodePudding user response：
maybe such a solution will be useful
tidyverse
df <- data.frame(
id = c(rep('a', 5), rep('b', 5), rep('c', 5)),
year = c(seq(2011, 2015), seq(2011, 2015), seq(2011, 2015)),
x = c(c(1:3, NA, NA), c(1:5), rep(NA, 5)),
y = c(c(NA, NA, 3, 4, NA), c(NA, 2, 3, 4, NA), rep(NA, 5)),
z = c(c(1:5), c(1:4, NA), rep(NA, 5))
)
library(tidyverse)
df %>%
pivot_longer(-c(id, year), values_drop_na = TRUE) %>%
group_by(id, name) %>%
slice_tail(n = 1) %>%
ungroup() %>%
pivot_wider(id_cols = c(id, year), names_from = name, values_from = value)
#> # A tibble: 5 x 5
#> id year x y z
#> <chr> <int> <dbl> <dbl> <dbl>
#> 1 a 2013 3 NA NA
#> 2 a 2014 NA 4 NA
#> 3 a 2015 NA NA 5
#> 4 b 2015 5 NA NA
#> 5 b 2014 NA 4 4
Created on 2021-11-24 by the reprex package (v2.0.1)
data.table
library(data.table)
library(magrittr)
melt(data = setDT(df), id.vars = c("id", "year"), na.rm = TRUE) %>%
.[order(id, year), last(.SD), by = list(id, variable)] %>%
dcast(formula = id year ~ variable)
#> id year x y z
#> 1: a 2013 3 NA NA
#> 2: a 2014 NA 4 NA
#> 3: a 2015 NA NA 5
#> 4: b 2014 NA 4 4
#> 5: b 2015 5 NA NA
Created on 2021-11-24 by the reprex package (v2.0.1)
CodePudding user response：
Perhaps this helps
library(dplyr)
dat %>%
group_by(id) %>%
mutate(across(x:z, ~ replace(.x,
row_number() < if(any(!is.na(.x))) max(which(!is.na(.x)))
else n(), NA))) %>%
ungroup %>%
filter(if_any(x:z, ~ !is.na(.x)))
CodePudding user response：
By considering max values for non NA values for each column and then applying a condition between year and newly created max column values.
dat <- data.frame(id = c(rep('a', 5), rep('b', 5), rep('c', 5)),
year = c(seq(2011, 2015), seq(2011, 2015), seq(2011, 2015)),
x = c(c(1:3, NA, NA), c(1:5), rep(NA, 5)),
y = c(c(NA, NA, 3, 4, NA), c(NA, 2, 3, 4, NA), rep(NA, 5)),
z = c(c(1:5), c(1:4, NA), rep(NA, 5)))
library(data.table)
setDT(dat)
Creating columns which contains the max year for non NA values based on each column x, y and z grouped by id
dat[!is.na(x),':='(Max_YearX = max(year)), by = .(id)]
dat[!is.na(y),':='(Max_YearY = max(year)), by = .(id)]
dat[!is.na(z),':='(Max_YearZ = max(year)), by = .(id)]
Changing all the values to NA wherever year is not same.
dat[,':='(x = ifelse(Max_YearX != year, NA, x),
y = ifelse(Max_YearY != year, NA, y),
z = ifelse(Max_YearZ != year, NA, z))]
Keeping only the required columns
dat <- dat[,.(id, year, x, y, z)]
dat
id year x y z
1: a 2011 NA NA NA
2: a 2012 NA NA NA
3: a 2013 3 NA NA
4: a 2014 NA 4 NA
5: a 2015 NA NA 5
6: b 2011 NA NA NA
7: b 2012 NA NA NA
8: b 2013 NA NA NA
9: b 2014 NA 4 4
10: b 2015 5 NA NA
11: c 2011 NA NA NA
12: c 2012 NA NA NA
13: c 2013 NA NA NA
14: c 2014 NA NA NA
15: c 2015 NA NA NA