#install.packages('readxl')
library(readxl)
rawdata = readxl::read_excel("MGR_patent control.xlsx", sheet = 5)

#install.packages('tidyverse')
library(tidyverse)
applications = rawdata %>%
  separate(., Journal, into = c("data"), sep = ";", extra = "drop", remove = FALSE) %>% 
  separate(., data, into = c("front", "number"), sep = ":") %>% 
  separate(., number, into = c("extra", "code", "id", "junk", "date"), sep = " ") %>% 
  select(-extra, -junk) %>% 
  mutate(id = str_trim(id, side = "both")) %>% 
  unite(application_number, c("code", "id"), sep = "") %>% 
  mutate(application_number = str_trim(application_number, side = "both")) %>% 
  mutate(duplicated = duplicated(application_number))

head(applications)

#> # A tibble: 6 x 7
#>   Organism    Journal         front   Company  application_num… date  duplicated
#>   <chr>       <chr>           <chr>   <chr>    <chr>            <chr> <lgl>     
#> 1 methanocal… "JOURNAL   Pat… JOURNA… Abbott … WO2011053699-A1  05-M… FALSE     
#> 2 pyrococcus… "JOURNAL   Pat… JOURNA… Abbott … WO2011053699-A1  05-M… TRUE      
#> 3 pyrococcus… "JOURNAL   Pat… JOURNA… Abbott … WO2011053699-A1  05-M… TRUE      
#> 4 pyrococcus… "JOURNAL   Pat… JOURNA… Abbott … WO2011053699-A1  05-M… TRUE      
#> 5 pyrococcus… "JOURNAL   Pat… JOURNA… Abbott … WO2011053699-A1  05-M… TRUE      
#> 6 pyrococcus… "JOURNAL   Pat… JOURNA… Abbott … WO2011053699-A1  05-M… TRUE

applications %>%
  count(duplicated)

#> # A tibble: 2 x 2
#>   duplicated     n
#>   <lgl>      <int>
#> 1 FALSE        999
#> 2 TRUE       11999

unique = applications %>% 
  filter(duplicated == FALSE) %>% 
  mutate(date = lubridate::dmy(date)) %>% 
  mutate(year = lubridate::year(date))

unique_year = unique %>% 
  count(year)

mean(unique_year$n)

#> [1] 41.625

median(unique_year$n)

#> [1] 47.5

unique_year %>% knitr::kable(align = "c")

unique_year %>%
  ggplot(., aes(x=year, y = n)) +
  ylim(0,100) +
  geom_point() +
  geom_line() +
  scale_x_continuous(breaks = scales::pretty_breaks(n = 20)) +
  labs(title = "Trends in patent applications for Genetic Sequences", x = "application year", y = "applications")