Hello,
I am new to R and am trying to figure out the following problem.
I have a data frame with a several time series variables and a date variable. For some of these variables, they start zero then become a number at some point. This is similar to sales data for a product launch. For some of the variables, the elements are never zero.
Here is my data frame:
test_df <- structure(list(variable = c("a", "a", "a", "a", "a",
"a", "b", "b", "b", "b", "b", "b", "c", "c", "c", "c", "c",
"c"), Date = structure(c(17167, 17198, 17226, 17257, 17287,
17318, 17167, 17198, 17226, 17257, 17287, 17318, 17167, 17198,
17226, 17257, 17287, 17318), class = "Date"), values = c(0,
0, 0, 3, 4, 5, 10, 11, 12, 13, 14, 15, 0, 0, 0, 0, 45, 50)),
row.names = c(NA, -18L), class = "data.frame", .Names = c("variable",
"Date", "values"))
library(tidyverse)
variable Date values
1 a 2017-01-01 0
2 a 2017-02-01 0
3 a 2017-03-01 0
4 a 2017-04-01 3
5 a 2017-05-01 4
6 a 2017-06-01 5
7 b 2017-01-01 10
8 b 2017-02-01 11
9 b 2017-03-01 12
10 b 2017-04-01 13
11 b 2017-05-01 14
12 b 2017-06-01 15
13 c 2017-01-01 0
14 c 2017-02-01 0
15 c 2017-03-01 0
16 c 2017-04-01 0
17 c 2017-05-01 45
18 c 2017-06-01 50
I would like to create a new data frame that shows me:
1) The first non-zero value in each column
2) The name of the variable that is associated with the first non-zero value
3) The date value associated with the first non-zero value
4) If the variable has never gone from zero to a number, it should be marked as NA.
The desired output would look something like this:
desired_output <- structure(list(variable = structure(c(1L, NA,
2L), .Label = c("a", "c"), class = "factor"), Date = structure(c(1L,
NA, 2L), .Label = c("2017-04-01", "2017-05-01"), class = "factor"),
values = c(3, NA, 45)), .Names = c("variable", "Date", "values"),
row.names = c(NA, -3L), class = "data.frame")
variable Date values
1 a 2017-04-01 3
2 <NA> <NA> NA
3 c 2017-05-01 45
I tried messing around with dplyr::filter and some basic for loops, but this seems way beyond my current capabilities. Any help would be much appreciated!