저자 책 웹페이지: https://dataninja.me/ipds-kr/

1. (갭마인더 자료 추가분석)

dplyr 패키지를 이용하여 갭마인더 데이터에서 다음 요약 통계량을 계산하라.

일단은 필수패키지인 tidyverse를 로드합니다. 그리고 gapminder 데이터 패키지를 로드합니다. (로딩 메시지를 감추기 위해 suppressMessages() 명령을 사용했습니다.)

# install.packages("tidyverse")
# install.packages("gapminder")
suppressMessages(library(tidyverse))
suppressMessages(library(gapminder))

a. 2007년도 나라별 일인당 국민소득

gapminder %>% 
  filter(year==2007) %>%
  select(country, gdpPercap)
## # A tibble: 142 x 2
##        country  gdpPercap
##         <fctr>      <dbl>
##  1 Afghanistan   974.5803
##  2     Albania  5937.0295
##  3     Algeria  6223.3675
##  4      Angola  4797.2313
##  5   Argentina 12779.3796
##  6   Australia 34435.3674
##  7     Austria 36126.4927
##  8     Bahrain 29796.0483
##  9  Bangladesh  1391.2538
## 10     Belgium 33692.6051
## # ... with 132 more rows

b. 2007년도 대륙별 일인당 평균수명의 평균과 중앙값

gapminder %>%
  filter(year==2007) %>%
  group_by(continent) %>%
  summarize(n(), mean(lifeExp), median(lifeExp))
## # A tibble: 5 x 4
##   continent `n()` `mean(lifeExp)` `median(lifeExp)`
##      <fctr> <int>           <dbl>             <dbl>
## 1    Africa    52        54.80604           52.9265
## 2  Americas    25        73.60812           72.8990
## 3      Asia    33        70.72848           72.3960
## 4    Europe    30        77.64860           78.6085
## 5   Oceania     2        80.71950           80.7195

(고급문제: 위의 평균은 각 나라의 산술평균입니다. 만약 각 나라별 인구수를 가중치로 한 가중평균(weighted average)을 내고자 한다면 어떻게 해야 할까요?)

2 (온라인 예제 자료 읽어 들이기)

예제 데이터를 제공하는 다음 페이지들을 방문하여 각 페이지에서 흥미있는 데이터를 하나씩 선택하여 다운로드한 후, R에 읽어 들이는 코드를 작성하라.

a. UCI 머신러닝 리포(UCI Machine Learning Repository)

https://goo.gl/fstR7 or http://archive.ics.uci.edu/ml/index.php

c. 머신러닝/데이터 과학 공유/경연 사이트인 캐글:

https://www.kaggle.com/

4번문제에서 살펴볼 다음 자료를 다운로드하였습니다: https://www.kaggle.com/deepmatrix/imdb-5000-movie-dataset

df <- read_csv("imdb-5000-movie-dataset.zip")
## Parsed with column specification:
## cols(
##   .default = col_integer(),
##   color = col_character(),
##   director_name = col_character(),
##   actor_2_name = col_character(),
##   genres = col_character(),
##   actor_1_name = col_character(),
##   movie_title = col_character(),
##   actor_3_name = col_character(),
##   plot_keywords = col_character(),
##   movie_imdb_link = col_character(),
##   language = col_character(),
##   country = col_character(),
##   content_rating = col_character(),
##   imdb_score = col_double(),
##   aspect_ratio = col_double()
## )
## See spec(...) for full column specifications.
## Warning in rbind(names(probs), probs_f): number of columns of result is not
## a multiple of vector length (arg 1)
## Warning: 4 parsing failures.
## row # A tibble: 4 x 5 col     row    col   expected      actual                          file expected   <int>  <chr>      <chr>       <chr>                         <chr> actual 1  2324 budget an integer  2400000000 'imdb-5000-movie-dataset.zip' file 2  2989 budget an integer 12215500000 'imdb-5000-movie-dataset.zip' row 3  3006 budget an integer  2500000000 'imdb-5000-movie-dataset.zip' col 4  3860 budget an integer  4200000000 'imdb-5000-movie-dataset.zip'

위에서 parsing failure 에러가 난 이유는 무엇일까? 에러 메시지를 자세히 살펴보면 몇 줄의 budget 변수 값이 너무 큰 것을 알 수 있다. read_csv() 함수는 데이터 파일의 처음 1000줄을 읽고 자동적으로 각 변수의 변수형을 결정하는데 크지 않은 숫자값에는 integer형을 배정하게 된다.

하지만 문제가 되는 4 행들은 budget 값이 integer 형태의 최대값보다 더 큰 값이다 보니 에러가 난 것이다. R의 최대 integer값은 다음과 같다:

.Machine$integer.max
## [1] 2147483647

문제가 되는 행들(예를 들어 2989 행; 봉준호 감독의 <괴물>이다!)은 예산이 120억원이다 보니 값이 너무 커진것이다. 문제가 되는 행에서 변수값은 결측치 (NA)가 된다:

12215500000 > .Machine$integer.max
## [1] TRUE
df[2989,] %>% glimpse()
## Observations: 1
## Variables: 28
## $ color                     <chr> "Color"
## $ director_name             <chr> "Joon-ho Bong"
## $ num_critic_for_reviews    <int> 363
## $ duration                  <int> 110
## $ director_facebook_likes   <int> 584
## $ actor_3_facebook_likes    <int> 74
## $ actor_2_name              <chr> "Kang-ho Song"
## $ actor_1_facebook_likes    <int> 629
## $ gross                     <int> 2201412
## $ genres                    <chr> "Comedy|Drama|Horror|Sci-Fi"
## $ actor_1_name              <chr> "Doona Bae"
## $ movie_title               <chr> "The Host "
## $ num_voted_users           <int> 68883
## $ cast_total_facebook_likes <int> 1173
## $ actor_3_name              <chr> "Ah-sung Ko"
## $ facenumber_in_poster      <int> 0
## $ plot_keywords             <chr> "daughter|han river|monster|river|se...
## $ movie_imdb_link           <chr> "http://www.imdb.com/title/tt0468492...
## $ num_user_for_reviews      <int> 279
## $ language                  <chr> "Korean"
## $ country                   <chr> "South Korea"
## $ content_rating            <chr> "R"
## $ budget                    <int> NA
## $ title_year                <int> 2006
## $ actor_2_facebook_likes    <int> 398
## $ imdb_score                <dbl> 7
## $ aspect_ratio              <dbl> 1.85
## $ movie_facebook_likes      <int> 7000

이것을 해결하는 방법은 몇가지가 있을 수 있다. read_csv(..., col_types=) 옵션에서 수동으로 변수형을 integer보다 최대값이 큰 double 등으로 지정해줄 수 있다. (.Machine$double.xmax를 실행해보라) 아니면 간편하게, guess_max= 옵션으로 좀 더 많은 행을 살펴보고 변수형을 결정하라고 할 수 있다:

df2 <- read_csv("imdb-5000-movie-dataset.zip", guess_max = 1e6)
## Parsed with column specification:
## cols(
##   .default = col_integer(),
##   color = col_character(),
##   director_name = col_character(),
##   actor_2_name = col_character(),
##   genres = col_character(),
##   actor_1_name = col_character(),
##   movie_title = col_character(),
##   actor_3_name = col_character(),
##   plot_keywords = col_character(),
##   movie_imdb_link = col_character(),
##   language = col_character(),
##   country = col_character(),
##   content_rating = col_character(),
##   budget = col_double(),
##   imdb_score = col_double(),
##   aspect_ratio = col_double()
## )
## See spec(...) for full column specifications.
df2[2989,] %>% glimpse()
## Observations: 1
## Variables: 28
## $ color                     <chr> "Color"
## $ director_name             <chr> "Joon-ho Bong"
## $ num_critic_for_reviews    <int> 363
## $ duration                  <int> 110
## $ director_facebook_likes   <int> 584
## $ actor_3_facebook_likes    <int> 74
## $ actor_2_name              <chr> "Kang-ho Song"
## $ actor_1_facebook_likes    <int> 629
## $ gross                     <int> 2201412
## $ genres                    <chr> "Comedy|Drama|Horror|Sci-Fi"
## $ actor_1_name              <chr> "Doona Bae"
## $ movie_title               <chr> "The Host "
## $ num_voted_users           <int> 68883
## $ cast_total_facebook_likes <int> 1173
## $ actor_3_name              <chr> "Ah-sung Ko"
## $ facenumber_in_poster      <int> 0
## $ plot_keywords             <chr> "daughter|han river|monster|river|se...
## $ movie_imdb_link           <chr> "http://www.imdb.com/title/tt0468492...
## $ num_user_for_reviews      <int> 279
## $ language                  <chr> "Korean"
## $ country                   <chr> "South Korea"
## $ content_rating            <chr> "R"
## $ budget                    <dbl> 12215500000
## $ title_year                <int> 2006
## $ actor_2_facebook_likes    <int> 398
## $ imdb_score                <dbl> 7
## $ aspect_ratio              <dbl> 1.85
## $ movie_facebook_likes      <int> 7000

이 에러에서 알 수 있는 또 하나의 중요한 사실은 budget 등이 영화 제작 국가의 통화로 표기되어 있다는 것이다 (미국은 달러, 한국은 원, 등등). 이러한 사실을 모르고 분석하면 잘못된 결론을 얻을 수 있으니 주의하도록 하자.

이러한 문제는 각 budget 변수의 분포를 나라별로 그려보면 간단히 알 수 있다:

df2 %>% ggplot(aes(budget)) + geom_histogram()
## `stat_bin()` using `bins = 30`. Pick better value with `binwidth`.
## Warning: Removed 492 rows containing non-finite values (stat_bin).

즉, 달러 이외의 통화를 사용하는 나라 때문에 분포에 많은 이상치가 존재하게 된다.

먼저, 가장 많은 영화를 만들어내는 나라를 살펴보고, 미국의 country 코드를 알아내도록 하자:

df2 %>% count(country) %>% arrange(-n)
## # A tibble: 66 x 2
##      country     n
##        <chr> <int>
##  1       USA  3807
##  2        UK   448
##  3    France   154
##  4    Canada   126
##  5   Germany    97
##  6 Australia    55
##  7     India    34
##  8     Spain    33
##  9     China    30
## 10     Italy    23
## # ... with 56 more rows

미국은 country=="USA" 임을 알 수 있다. 미국 영화들의 예산의 분포를 그려보면 아주 심각한 이상치들이 제거된 다음과 같은 분포를 얻게 된다.

df2 %>% 
  filter(country=="USA") %>%
  ggplot(aes(budget)) + geom_histogram()
## `stat_bin()` using `bins = 30`. Pick better value with `binwidth`.
## Warning: Removed 298 rows containing non-finite values (stat_bin).

d. 위키피디아의 머신러닝 연구를 위한 데이터세트 리스트:

https://goo.gl/SpCOlK or https://en.wikipedia.org/wiki/List_of_datasets_for_machine_learning_research

(생략)

3 (범주별 요약 통계량)

위에서 읽어 들인 데이터의 범주별 요약 통계량을 작성하라. dplyr 패키지의 %>% 연산자, group_by(), summarize() 함수를 사용하여야 한다.

위에서 읽어들인 IMDB 자료를 살펴보도록 하자. 미국에서 각 10년간 만들어진 영화의 개수, 영화 편당 제작비의 총액과 중간값, 영화 편당 수익의 총액과 중간값, 그리고 ROI (총수익 / 총제작비)을 계산해 보도록 하자.

df2 %>%
  filter(country=="USA") %>%
  group_by(decade=floor(title_year/10)*10) %>%
  summarize(n_movies=n(),
            tot_budet = sum(budget, na.rm=TRUE),
            median_budget = median(budget, na.rm=TRUE),
            tot_gross = sum(as.numeric(gross), na.rm=TRUE),
            median_gross = median(gross, na.rm=TRUE),
            roi = tot_gross/tot_budet)
## # A tibble: 12 x 7
##    decade n_movies   tot_budet median_budget   tot_gross median_gross
##     <dbl>    <int>       <dbl>         <dbl>       <dbl>        <dbl>
##  1   1910        1      385907        385907           0           NA
##  2   1920        3      724000        245000     5808000      2904000
##  3   1930       15    22944736       1500000   411246620     12601306
##  4   1940       25    50165377       2160000   310511077     23650000
##  5   1950       25    72231862       2941924   119600000     17300000
##  6   1960       46   240815419       3200000   681522497     46300000
##  7   1970       89   639148038       4000000  3222752169     48500000
##  8   1980      229  3145085000      11000000  9967933433     34700000
##  9   1990      640 20679721000      25000000 32125546923     30628981
## 10   2000     1550 54696419779      25000000 72883213540     28532415
## 11   2010     1110 45903912917      24000000 58891120126     37652565
## 12     NA       74    16200000       2650000           0           NA
## # ... with 1 more variables: roi <dbl>

(여기서 as.numeric()을 사용한 이유는 integer overflow 에러 때문이다.) 물론 이 분석에는 물가 상승이 반영되어 있지 않지만, 미국 영화산업의 성장과 특이한 사항들을 알 수 있다. 예를 들어 1970년대에는 몇십편의 영화밖에 제작되지 않았지만, 편당 수익의 중간값, 그리고 ROI 값은 현재보다 크다!

4 (IMDB 자료 분석)

캐글 웹사이트에서 다음 IMDB(Internet Movie Database) 영화 정보 데이터를 다운로드하도록 하자(https://goo.gl/R08lpm or https://www.kaggle.com/deepmatrix/imdb-5000-movie-dataset 무료 캐글 계정이 필요하다). dplyr 패키지를 이용하여 다음 질문에 답하라.

a. 이 데이터는 어떤 변수로 이루어져 있는가?

https://www.kaggle.com/deepmatrix/imdb-5000-movie-dataset 페이지, 그리고 https://www.kaggle.com/deepmatrix/imdb-5000-movie-dataset/data의 변수설명을 살펴보자. 위에서 R로 읽어들인 데이터프레임은 다음과 같다.

glimpse(df2)
## Observations: 5,043
## Variables: 28
## $ color                     <chr> "Color", "Color", "Color", "Color", ...
## $ director_name             <chr> "James Cameron", "Gore Verbinski", "...
## $ num_critic_for_reviews    <int> 723, 302, 602, 813, NA, 462, 392, 32...
## $ duration                  <int> 178, 169, 148, 164, NA, 132, 156, 10...
## $ director_facebook_likes   <int> 0, 563, 0, 22000, 131, 475, 0, 15, 0...
## $ actor_3_facebook_likes    <int> 855, 1000, 161, 23000, NA, 530, 4000...
## $ actor_2_name              <chr> "Joel David Moore", "Orlando Bloom",...
## $ actor_1_facebook_likes    <int> 1000, 40000, 11000, 27000, 131, 640,...
## $ gross                     <int> 760505847, 309404152, 200074175, 448...
## $ genres                    <chr> "Action|Adventure|Fantasy|Sci-Fi", "...
## $ actor_1_name              <chr> "CCH Pounder", "Johnny Depp", "Chris...
## $ movie_title               <chr> "Avatar ", "Pirates of the Caribbean...
## $ num_voted_users           <int> 886204, 471220, 275868, 1144337, 8, ...
## $ cast_total_facebook_likes <int> 4834, 48350, 11700, 106759, 143, 187...
## $ actor_3_name              <chr> "Wes Studi", "Jack Davenport", "Step...
## $ facenumber_in_poster      <int> 0, 0, 1, 0, 0, 1, 0, 1, 4, 3, 0, 0, ...
## $ plot_keywords             <chr> "avatar|future|marine|native|paraple...
## $ movie_imdb_link           <chr> "http://www.imdb.com/title/tt0499549...
## $ num_user_for_reviews      <int> 3054, 1238, 994, 2701, NA, 738, 1902...
## $ language                  <chr> "English", "English", "English", "En...
## $ country                   <chr> "USA", "USA", "UK", "USA", NA, "USA"...
## $ content_rating            <chr> "PG-13", "PG-13", "PG-13", "PG-13", ...
## $ budget                    <dbl> 237000000, 300000000, 245000000, 250...
## $ title_year                <int> 2009, 2007, 2015, 2012, NA, 2012, 20...
## $ actor_2_facebook_likes    <int> 936, 5000, 393, 23000, 12, 632, 1100...
## $ imdb_score                <dbl> 7.9, 7.1, 6.8, 8.5, 7.1, 6.6, 6.2, 7...
## $ aspect_ratio              <dbl> 1.78, 2.35, 2.35, 2.35, NA, 2.35, 2....
## $ movie_facebook_likes      <int> 33000, 0, 85000, 164000, 0, 24000, 0...

b. 연도별 리뷰받은 영화의 개수는?

df2 %>%
  group_by(title_year) %>%
  count()
## # A tibble: 92 x 2
## # Groups:   title_year [92]
##    title_year     n
##         <int> <int>
##  1       1916     1
##  2       1920     1
##  3       1925     1
##  4       1927     1
##  5       1929     2
##  6       1930     1
##  7       1932     1
##  8       1933     2
##  9       1934     1
## 10       1935     1
## # ... with 82 more rows
df2 %>%
  group_by(title_year) %>%
  summarize(n_movies=n()) %>% 
  ggplot(aes(title_year, n_movies)) + geom_point() + geom_line()
## Warning: Removed 1 rows containing missing values (geom_point).
## Warning: Removed 1 rows containing missing values (geom_path).

c. 연도별 리뷰평점의 개수는?

df2 %>%
  group_by(title_year) %>%
  summarize(avg_imdb_score = mean(imdb_score)) %>%
  ggplot(aes(title_year, avg_imdb_score)) + geom_point() + geom_line()
## Warning: Removed 1 rows containing missing values (geom_point).
## Warning: Removed 1 rows containing missing values (geom_path).

(고급: 위의 시각화에 오차구간을 추가하려면? )

5 (SQL 연습)

‘Online SQL Tryit Editor(https://goo.gl/NJDGdw 혹은 https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all )’에 방문해보자. 이 페이지에서는 가상의 레스토랑의 재료 주문정보를 기록한 데이터베이스를 예제로 제공하고 있다. 이 페이지를 이용해 다음 질문에 답하라.

a. 다음 질문에 대답하는 SQL 문을 작성하고 실행하라.

  1. Orders 테이블에서 employeeID별 주문 수는? 가장 주문 수가 많은 employeeID부터 내림차순 으로 출력하라.
SELECT EmployeeID, count(*) AS n_orders
FROM Orders
GROUP BY EmployeeID
ORDER BY n_orders DESC;
  1. 위의 결과를 Employees 테이블과 결합하여 같은 결과에 FirstName과 LastName을 추가하여 출력하라.
SELECT a.EmployeeID, b.FirstName, b.LastName, count(*) AS n_orders
FROM Orders a INNER JOIN Employees b
  ON a.EmployeeID = b.EmployeeID
GROUP BY b.EmployeeID, b.FirstName, b.LastName
ORDER BY n_orders DESC;
  1. Orders, OrderDetails, Products 테이블을 결합하여 각 OrderID별로 주문 날짜, 주문품목 양(새 열 이름은 n_items으로), 주문 총액(열 이름은 total_price으로)을 출력하라.
SELECT a.OrderID,
  a.OrderDate,
  SUM(b.Quantity) as n_items,
  SUM(b.Quantity * c.Price) as total_price
FROM Orders a
  INNER JOIN OrderDetails b
    ON a.OrderID = b.OrderID
  INNER JOIN Products c
    ON b.ProductID = c.ProductID
GROUP BY a.OrderID, a.OrderDate
ORDER BY a.OrderID;

b. 웹페이지에는 총 8개의 테이블이 있다. 각 테이블은 각각 어떤 열로 구성되어 있는가?

각 테이블을 클릭하면 됩니다. 각 테이블에 다음 명령을 실행해 줍니다:

SELECT * FROM Orders;

c. [고급]

각 테이블들 간에 공통되는 열들은 어떤 것들인가 (예를 들어, Orders 테이블과 Customers 테이블 모두 CustomerID 열을 가지고 있다)?

테이블들 간의 관계를 어떻게 나타낼 수 있을까?

개체-관계 모델 (entity-relationship model, ER model)은 테이블 간의 관계를 나타내는 데 많이 사용된다.