Filter data at the date end of the year

[sirius1170]

Hello everybody,

I would like to ask for help. I have data like that.

data<-data.frame(stringsAsFactors=FALSE,
           code = c("AAA2010", "AAA2010", "AAA2010", "AAA2010", "AAC2011",
                    "AAC2011", "AAC2011", "AAC2011", "AAD2010", "AAD2010"),
           year = c("2010", "2010", "2010", "2010", "2011", "2011", "2011",
                    "2011", "2010", "2010"),
   closed_price = c(49900L, 46600L, 46900L, 45200L, 45100L, 45600L, 46500L,
                    46100L, 46100L, 45800L),
          stock = as.factor(c("AAA", "AAA", "AAA", "AAA", "AAC", "AAC", "AAC",
                              "AAC", "AAD", "AAD")),
          date3 = as.factor(c("2010-12-15", "2010-12-16", "2010-12-19",
                              "2010-12-20", "2011-12-21", "2011-12-22",
                              "2011-12-23", "2011-12-26", "2010-12-27", "2010-12-28"))
)

I would like to filter the last day's stock price of each stock. My output is something like that.

I would appreciate any help.
Best regards,

[FJCC]

data<-data.frame(stringsAsFactors=FALSE,
                 code = c("AAA2010", "AAA2010", "AAA2010", "AAA2010", "AAC2011",
                          "AAC2011", "AAC2011", "AAC2011", "AAD2010", "AAD2010"),
                 year = c("2010", "2010", "2010", "2010", "2011", "2011", "2011",
                          "2011", "2010", "2010"),
                 closed_price = c(49900L, 46600L, 46900L, 45200L, 45100L, 45600L, 46500L,
                                  46100L, 46100L, 45800L),
                 stock = as.factor(c("AAA", "AAA", "AAA", "AAA", "AAC", "AAC", "AAC",
                                     "AAC", "AAD", "AAD")),
                 date3 = as.factor(c("2010-12-15", "2010-12-16", "2010-12-19",
                                     "2010-12-20", "2011-12-21", "2011-12-22",
                                     "2011-12-23", "2011-12-26", "2010-12-27", "2010-12-28"))
)

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
data <- data %>% mutate(date3 = as.Date(date3)) 
MaxDates <- data %>%  group_by(stock) %>% summarize(MaxDate = max(date3))
data2 <- semi_join(data, MaxDates, by = c("stock", "date3" = "MaxDate"))
data2
#>      code year closed_price stock      date3
#> 1 AAA2010 2010        45200   AAA 2010-12-20
#> 2 AAC2011 2011        46100   AAC 2011-12-26
#> 3 AAD2010 2010        45800   AAD 2010-12-28

^{Created on 2019-11-27 by the reprex package (v0.2.1)}

[andresrcs]

Another way to do it

library(dplyr)

data <- data.frame(stringsAsFactors=FALSE,
                 code = c("AAA2010", "AAA2010", "AAA2010", "AAA2010", "AAC2011",
                          "AAC2011", "AAC2011", "AAC2011", "AAD2010", "AAD2010"),
                 year = c("2010", "2010", "2010", "2010", "2011", "2011", "2011",
                          "2011", "2010", "2010"),
                 closed_price = c(49900L, 46600L, 46900L, 45200L, 45100L, 45600L, 46500L,
                                  46100L, 46100L, 45800L),
                 stock = as.factor(c("AAA", "AAA", "AAA", "AAA", "AAC", "AAC", "AAC",
                                     "AAC", "AAD", "AAD")),
                 date3 = as.factor(c("2010-07-15", "2010-07-16", "2010-07-19",
                                     "2010-07-20", "2011-08-21", "2011-08-22",
                                     "2011-08-23", "2011-08-26", "2010-07-27", "2010-07-28"))
)

data %>% 
    mutate(date3 = as.Date(date3)) %>% 
    group_by(code) %>% 
    filter(date3 == max(date3))
#> # A tibble: 3 x 5
#> # Groups:   code [3]
#>   code    year  closed_price stock date3     
#>   <chr>   <chr>        <int> <fct> <date>    
#> 1 AAA2010 2010         45200 AAA   2010-07-20
#> 2 AAC2011 2011         46100 AAC   2011-08-26
#> 3 AAD2010 2010         45800 AAD   2010-07-28

Note: For future posts please use proper code formatting as explained here (I already edited your post to do so)

FAQ: How to format your code

Formatting Code: the Basics All code or console output you include in your posts should be formatted properly. Luckily, this is very easy to do! Just use the code formatting button at the top of the post editing box: [image] Select some code Click the </> button! The code formatting button automatically adds special Markdown formatting symbols to whatever you selected. If you check the preview pane to the right of your post, you'll see that the forum software now outputs nicely formatted code . [04] The code formatting button is pretty smart! It can tell the difference between snippets of code that are part of a sentence and blocks of code that stand alone. …

[sirius1170]

Many thanks for quick help! I really appreciate.