library(RMySQL)
## Warning: package 'RMySQL' was built under R version 3.4.4
## Loading required package: DBI
## Warning: package 'DBI' was built under R version 3.4.4
library(tidyverse)
## Warning: package 'tidyverse' was built under R version 3.4.4
## -- Attaching packages ------------------------------------------------------------- tidyverse 1.2.1 --
## <U+221A> ggplot2 3.0.0 <U+221A> purrr 0.2.5
## <U+221A> tibble 1.4.2 <U+221A> dplyr 0.7.5
## <U+221A> tidyr 0.8.1 <U+221A> stringr 1.3.1
## <U+221A> readr 1.1.1 <U+221A> forcats 0.3.0
## Warning: package 'ggplot2' was built under R version 3.4.4
## Warning: package 'tibble' was built under R version 3.4.4
## Warning: package 'tidyr' was built under R version 3.4.4
## Warning: package 'readr' was built under R version 3.4.4
## Warning: package 'purrr' was built under R version 3.4.4
## Warning: package 'dplyr' was built under R version 3.4.4
## Warning: package 'stringr' was built under R version 3.4.4
## Warning: package 'forcats' was built under R version 3.4.4
## -- Conflicts ---------------------------------------------------------------- tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()
yhendus <- dbConnect(dbDriver("MySQL"), user="root",
password = "root", host = "localhost",
dbname = "film")
paring <- dbSendQuery(yhendus,
"SELECT film_id, film_type_id FROM efis_film_type_rel WHERE film_id<1001")
## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 0 imported
## as numeric
## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 1 imported
## as numeric
filmiandmed_3 = fetch(paring, n = -1)
head(filmiandmed_3)
## film_id film_type_id
## 1 1 1
## 2 1 34
## 3 2 9
## 4 2 33
## 5 2 89
## 6 5 2
filmituup <- filmiandmed_3
paring<-dbSendQuery(yhendus, "SELECT film_type_id, title FROM efis_film_type_translation WHERE language = 'et'")
## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 0 imported
## as numeric
tolked <- fetch(paring, n = -1)
head(tolked)
## film_type_id title
## 1 1 Mängufilm
## 2 2 Komöödiafilm
## 3 3 Detektiivfilm
## 4 4 Melodraama
## 5 5 Põnevusfilm
## 6 6 Dokumentaalfilm
nrow(tolked)
## [1] 91
filmituup %>% inner_join(tolked, by = c("film_type_id" = "film_type_id")) %>% group_by(film_id)
## # A tibble: 2,012 x 3
## # Groups: film_id [793]
## film_id film_type_id title
## <dbl> <dbl> <chr>
## 1 1 1 Mängufilm
## 2 1 34 Lühimängufilm
## 3 2 9 Draama
## 4 2 33 Tudengifilm
## 5 2 89 Lühimängufilm
## 6 5 2 Komöödiafilm
## 7 5 33 Tudengifilm
## 8 5 89 Lühimängufilm
## 9 7 1 Mängufilm
## 10 7 34 Lühimängufilm
## # ... with 2,002 more rows
paring<-dbSendQuery(yhendus, "SELECT id, production_year FROM efis_film WHERE id<1001")
## Warning in .local(conn, statement, ...): Unsigned INTEGER in col 0 imported
## as numeric
aastad = fetch(paring, n = -1)
head(aastad)
## id production_year
## 1 1 2001
## 2 2 2006
## 3 5 1976
## 4 7 2004
## 5 8 1979
## 6 9 1980
tabel <- filmituup %>% inner_join(tolked, by = c("film_type_id" = "film_type_id"))
tabel_2 <- tabel %>% inner_join(aastad, by= c("film_id" = "id"))
head(tabel_2)
## film_id film_type_id title production_year
## 1 1 1 Mängufilm 2001
## 2 1 34 Lühimängufilm 2001
## 3 2 9 Draama 2006
## 4 2 33 Tudengifilm 2006
## 5 2 89 Lühimängufilm 2006
## 6 5 2 Komöödiafilm 1976
#####mingi plott tehtud filmidest aasta jooksul ....
test <- tabel_2 %>% group_by(production_year) %>% filter(production_year > "1985") %>% summarise(kogus = n())
## Warning: package 'bindrcpp' was built under R version 3.4.4
ggplot(test, aes(x = production_year, y = kogus)) +
xlab("Aasta") +
ylab("Filmide arv") +
theme_minimal(base_size = 12, base_family = "Georgia") +
geom_point(aes(size = kogus, color = kogus), alpha = 0.7) +
scale_size_area(guide = FALSE, max_size = 15)
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database
## Warning in grid.Call.graphics(C_text, as.graphicsAnnot(x$label), x$x, x
## $y, : font family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database
## Warning in grid.Call(C_textBounds, as.graphicsAnnot(x$label), x$x, x$y, :
## font family not found in Windows font database
