mp4.Rmd

---
title: <span style="color:gold">IMDB</span>, how *authoritative* is it? An Investigation of the Representation of Non-English Entertainment in IMDB’s Database 
author: "Danica Miguel, Lizette Carpenter, and Emily Kim"
date: "May 1, 2018"
output: 
  html_document:
    theme: journal
    code_folding: hide
---

```{r message = FALSE, include = FALSE, warning = FALSE}
library(mdsr)
library(RMySQL)
library(ggplot2)
library(scales)
db <- dbConnect_scidb(dbname = "imdb")
```

```{r message=FALSE, include = FALSE, warning = FALSE}
class(db)
```

###Problem Statement
When googling [imdb](https://www.google.com/search?q=imdb&rlz=1C5CHFA_enUS721US721&oq=IMDB&aqs=chrome.0.0j69i60j69i59j69i60l3.2432j1j7&sourceid=chrome&ie=UTF-8)[^1], the first result is the IMDb’s [website](https://www.imdb.com/pressroom/about/?pf_rd_m=A2FGELUUNOQJNL&pf_rd_p=c1695c53-c70f-429d-859f-647489ac83de&pf_rd_r=F9J4ZH0MYVAVCFF2GPJ9&pf_rd_s=right-1&pf_rd_t=60601&pf_rd_i=pressroom&ref_=fea_pr_pr_lk2)[^2] where it briefly explains, “IMDb, the world's most popular and authoritative source for movie, TV and celebrity content.” In our investigation, we want to look at the question "to what extent is IMDb truly worldwide?", by looking at different languages. Each member of our group is bilingual and shares the ability of enjoying movies and TV series produced in languages other than English. Thus, our interest is to see how the representation of entertainment produced in non-English languages has changed over the last 100 years.


```{r message=FALSE, warning = FALSE}
###total number of movies in imdb
total_entertainment <- db %>% 
  dbGetQuery("SELECT production_year, COUNT(*) as total_entertainment
FROM imdb.title
WHERE kind_id <= '2'
GROUP BY production_year
ORDER BY production_year DESC;")

###number of movies for each specified language in imdb
all_language_movies <- db%>%
  dbGetQuery("SELECT production_year, SUM(info LIKE '%English%') AS English, SUM(info LIKE '%Tagalog%') AS Tagalog, SUM(info LIKE '%Spanish%') AS Spanish, SUM(info LIKE '%Korean%') as Korean 
FROM imdb.movie_info
JOIN title on movie_info.movie_id = title.id
WHERE info_type_id = 4
AND kind_id <= '2'
GROUP BY production_year;")

###number of tv series for each specified language in imdb
all_language_tv <- db%>%
  dbGetQuery("SELECT production_year, SUM(info LIKE '%English%') AS English, SUM(info LIKE '%Tagalog%') AS Tagalog, SUM(info LIKE '%Spanish%') AS Spanish, SUM(info LIKE '%Korean%') as Korean 
FROM imdb.movie_info
JOIN title on movie_info.movie_id = title.id
WHERE info_type_id = 4
AND kind_id = '2'
GROUP BY production_year;")

###combining production years for each language for movies
all_movies_long <- all_language_movies %>%
  gather(key = "languages", value = "total", English:Korean) %>%
  arrange(production_year)

###number of tv series and movies for each specified country in imdb
all_countries_tvm <- db%>%
  dbGetQuery("SELECT production_year, 
SUM(info LIKE '%US%') AS US, 
SUM(info LIKE '%Philippines%') AS Philippines, 
SUM(info LIKE '%Mexico%') AS Mexico, 
SUM(info LIKE '%Korea%') AS Korea
FROM imdb.movie_info
JOIN title on movie_info.movie_id = title.id
WHERE info_type_id = 8
AND kind_id <= '2'
GROUP BY production_year;")

#combining production years for each country for movies and tv series
all_countries_long <- all_countries_tvm %>%
  gather(key = "country", value = "total", US:Korea) %>%
  arrange(production_year)

#Attempt to look at total budget of each production year in specific language 
# SELECT t.production_year, 
# substring_index(mii.info, '$', -1) AS s_budget,
# 1*(substring_index(mii.info, '$', -1) ) AS i_budget,
# SUM(mii2.info LIKE '%English%') AS english 
# FROM imdb.title t  
# JOIN movie_info mii ON mii.movie_id = t.id  
# JOIN movie_info mii2 ON mii2.movie_id = t.id  
# WHERE t.kind_id = 1 
# AND mii.info_type_id = 105
# AND mii.info LIKE '$'  
# AND mii2.info_type_id = 4 
# GROUP BY t.production_year;
```


###Movies Produced in the Last Century in Different Languages
```{r message=FALSE, warning = FALSE}
###plot for movies for each specified language
ggplot(all_movies_long, aes(x= production_year, y = total)) +
  geom_line(aes(color = factor(languages)), alpha = 1) +
  scale_x_continuous(name = "Production Year",
                   limit = c(1917, 2017)) +
  scale_y_continuous(name = "Number of Movies") +
  scale_color_discrete(name = "Languages")
```

As seen in this data graphic, the movies that are in English are the most represented in the IMDb database. We thought it would be especially interesting to look at the number of movies of English, Korean, Spanish, and Tagalog movies, especially since we are able to speak these languages. There are significantly less movies in the languages other than English. Although we acknowledge that IMDb was created by an English entrepreneur, [Col Needham](https://en.wikipedia.org/wiki/Col_Needham)[^3], and thus would have IMDb's database primarily from English movies, there is still a lack of representation of movies in different languages.


```{r message=FALSE, warning = FALSE}
###combining production years for each language for tv series
all_tv_long <- all_language_tv %>%
  gather(key = "languages", value = "total", English:Korean) %>%
  arrange(production_year)
```

###TV Series Produced in the Last Century in Different Languages
```{r message=FALSE, warning = FALSE}
###plot for tv series for each specified language
ggplot(all_tv_long, aes(x= production_year, y = total)) +
  geom_line(aes(color = factor(languages)), alpha = 1) +
  scale_x_continuous(name = "Production Year",
                   limit = c(1917, 2017)) +
  scale_y_continuous(name = "Number of TV Series") +
  scale_color_discrete(name = "Languages")
```

This idea is thus continued into the TV series. Can IMDb truly call itself "worldwide", despite the lack of representation of other languages? 

###Total Number of Movies and TV Series in Different Languages Over the Last Century 
```{r warning = FALSE}
###plot for total of movies and tv series for countries
ggplot(all_countries_long, aes(x= production_year, y = total)) +
  geom_line(aes(color = factor(country)), alpha = 1) +
    scale_x_continuous(name = "Production Year",
                   limit = c(1917, 2017)) +
  scale_y_continuous(name = "Number of Movies and TV Series") +
  scale_color_discrete(name = "Countries")
```


```{r message=FALSE, warning = FALSE}
#comparing the number of movies of the specified languages to all the movies in imdb
movies <- all_countries_tvm %>%
  left_join(total_entertainment, by="production_year")
```

#English Movies and TV Series
```{r message=FALSE, warning = FALSE}
years <- filter(movies, production_year == "2017" | production_year == "2007" | production_year == "1997")

ggplot(years, aes(x = production_year, y = US/total_entertainment*100)) +
  geom_bar(stat = "identity",
           fill = "red1") +
  ggtitle("English Speaking Movies and TV Series in IMDb")  +
  scale_x_continuous(name = "Production Year", 
                     breaks = c(1997, 2007, 2017, 10)) +
  scale_y_continuous(name = "% of English Movies in IMDb Database", 
                     limits = c(0, 100), 
                     breaks = c(0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100))
```

#Spanish Movies and TV Series
```{r message = FALSE, warning = FALSE}
years <- filter(movies, production_year == "2017" | production_year == "2007" | production_year == "1997")

ggplot(years, aes(x = production_year, y = Mexico/total_entertainment*100)) +
  geom_bar(stat = "identity",
           fill = "turquoise1") +
  ggtitle("Spanish Speaking Movies and TV Series in IMDb") +
  scale_x_continuous(name = "Production Year", 
                     breaks = c(1997, 2007, 2017, 10)) +
  scale_y_continuous(name = "% of Spanish Movies in IMDb Database", 
                     limits = c(0, 100), 
                     breaks = c(0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100))
```
Lizette: I immediatley think of the American Spanish language television network, Telemundo when I think of Latinx entertainment. The very first telenovela I remembered watching with my mother was 'Yo soy Betty, la fea.' I recall my mother explaining to me the various remakes of ['Yo soy Betty, la fea'](https://www.imdb.com/title/tt0233127/)[^4] or better known as 'Betty la fea' across Latin America. I will never forget her expressing that the Columbian version of 'Betty la fea' was her favorite and that the Mexican version "lacked charm." The fact that my mother is a proud Mexican women is probably why this has stayed with me. When the tv series was remade in [English](https://www.imdb.com/title/tt0805669/)[^5] starring America Ferrera, I remembered being dissapointed that many of the Spanish idioms of Betty's personality were not translated the same way in English. When analyzing the imdb database I had a flashback of all of this especially when I discovered that many of the remakes of the tv series was included in the database. Buen trabajo, Needham.

#Tagalog Movies and TV Series
```{r message = FALSE, warning = FALSE}
years <- filter(movies, production_year == "2017" | production_year == "2007" | production_year == "1997")

ggplot(years, aes(x = production_year, y = Philippines/total_entertainment*100)) +
  geom_bar(stat = "identity",
           fill = "darkorchid4") +
  ggtitle("Tagalog Speaking Movies and TV Series in IMDb") +
  scale_x_continuous(name = "Production Year", 
                     breaks = c(1997, 2007, 2017, 10)) +
  scale_y_continuous(name = "% of Tagalog Movies in IMDb Database", 
                     limits = c(0, 100), 
                     breaks = c(0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100))
```

Danica: Because Filipino pop culture is rarely seen outside of the Philippines. It was exciting to see that there are Filipino movies and tv series included in the IMDB database. I was even more surprised that there was more than one movie and tv series included in this database. It was also interesting that there are more entertainment included for 1997 than there are for 2007 and 2017 considering that there are differences in production qualities between the 1990s and now. However, growing up in a Filipino household, it made sense that older movies are more popular than newer movies. At home, on the Filipino channel, old movies, tv shows, and songs are always playing. To my parents and grandparents, the 1990s was the peak of Filipino pop culture. Famous Filipino singers were traveling abroad and there were many great tv series and movies made during this time. It was great that the Philippines is was not completely overshadowed in this database, but instead, IMDB included some of their best movies. In the future, it would be exciting for more newer and upcoming Filipino entertainment to be included in this database, especially for Filipino communities around the world.

#Korean Movies and TV Series
```{r message = FALSE, warning = FALSE}
years <- filter(movies, production_year == "2017" | production_year == "2007" | production_year == "1997")

ggplot(years, aes(x = production_year, y = Korea/total_entertainment*100)) +
  geom_bar(stat = "identity",
           fill = "olivedrab4") +
  ggtitle("Korean Speaking Movies and TV Series in IMDb") +
  scale_x_continuous(name = "Production Year", 
                     breaks = c(1997, 2007, 2017, 10)) +
  scale_y_continuous(name = "% of Korean Movies in IMDb Database", 
                     limits = c(0, 100), 
                     breaks = c(0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100))
```
Emily: In this data graphic, it is evident that there is almost no Korean speaking movies and TV series on the IMDb database. Specifically, in all three years, 1997, 2007, and 2017, all of the representation of Korean Movies and TV series is less than 1%. Again, acknowledging that IMDb, is a company created by a British man, it is still important to note the lack of diversity not just in Korean entertainment There is however, a slight increase between 1997 and 2007; I know that personally, during this time, a very popular Korean drama (which is considered as a Korean TV series) had come out. 꽃보다 남자, or [Boys Over Flowers](https://en.wikipedia.org/wiki/Boys_Over_Flowers_(TV_series))[^6], became very popular during this time, as I saw many of my non-Korean friends watch this. In a way, this Korean drama was possibly a catalyst of all Korean dramas in the following year. It is also interesting to note that the film [*Old Boy*](https://en.wikipedia.org/wiki/Oldboy_(2003_film))[^7] was internationally known, as it won the Grand Prix, and had been well received in the United States. Because of this exposure to a Korean movie, that may have contributed to the slight increase of Korean entertainment in IMDb after 1997.

###Conclusion
Through this mini project, it was interesting with working with the IMDb database. We initially thought there wouldn't be much representation of our own languages in this database compared to English speaking entertainment, and this project only reinforced that hypothesis. We know that personally, there are more movies and TV series that are simply not represented in this database. Thus, to bring back the question in focus, "how authoritative is IMDb" in terms of worldwide representation, we want to call attention to the diversity of people who work on this database. Do the employees of IMDb represent the worldwide audience that use the database? 

GitHub Repository[^8]

**FOOTNOTES**

[^1]:https://www.google.com/search?q=imdb&rlz=1C5CHFA_enUS721US721&oq=IMDB&aqs=chrome.0.0j69i60j69i59j69i60l3.2432j1j7&sourceid=chrome&ie=UTF-8

[^2]: https://www.imdb.com/pressroom/about/?pf_rd_m=A2FGELUUNOQJNL&pf_rd_p=c1695c53-c70f-429d-859f-647489ac83de&pf_rd_r=F9J4ZH0MYVAVCFF2GPJ9&pf_rd_s=right-1&pf_rd_t=60601&pf_rd_i=pressroom&ref_=fea_pr_pr_lk2

[^3]: https://en.wikipedia.org/wiki/Col_Needham

[^4]: https://www.imdb.com/title/tt0233127/

[^5]: https://www.imdb.com/title/tt0805669/

[^6]: https://en.wikipedia.org/wiki/Boys_Over_Flowers_(TV_series)

[^7]: https://en.wikipedia.org/wiki/Oldboy_(2003_film)

[^8]:https://github.com/eyk801/sds192-miniproject4.git