
# ME314 Introduction to Data Science and Machine Learning

## Day 3 Assignment, LSE ME314 2021
---

### 1.  Normalizing data

This question uses this table:

   | EmployeeID | SalesPerson | SalesOffice | OfficeNumber | Customer1 | Customer2 | Customer3 |
   | ------:|:-------------:|:----------:|:------------ |:-------:|:------:|:------:|
   | 1003   | Mary Smith    |  Chicago   | 312-555-1212 | Ford   | GM |       |
   | 1004   | John Hunt     |  New York  | 212-555-1212 | Dell   | HP | Apple |
   | 1005   | Martin Hap    |  Chicago   | 312-555-1212 | Boeing |    |       |

from the lecture notes.  For each answer, please explain as fully as possible, and feel free to use tables or diagrams if you prefer.

a)  Why does this table violate the first normal form, and what would be required to make it 1NF?

[your answer]

b)  What additional steps would be needed to make the table 2NF? What about 3NF?

[your answer]

c)  Why might we not want to normalize data to the fullest extent possible?

[your answer]

d)  In the table below, which of the three normalization rules does this violate, if any, and why?

   |  countryID  |  countryName    |   EUmember   |  EUjoindate  |
   | -----------:|:----------------|:------------:|:------------:|
   | 00001       | France          |  `true`      |  1958-01-01  |
   | 00004       | Hungary         |  `true`      |  2004-05-01  |
   | 00003       | Serbia          |  `false`     |       `NULL` |
   | 00004       | Finland         |  `true`      |  1995-01-01  |
   | 00005       | Russia          |  `false`     |       `NULL` |
   | 00006       | Ireland, UK     |  `true`      |  1973-01-01  |

   Are there any other problems with the table, besides normalization?

[your answer]

e)  What would it take to fully (1NF-3NF) normalize this dataset?

   Write out these tables, and describe why this meets each of the normal forms.  This is a database of movies watched on NetBricks, a streaming movie service.

   | Name           | Address    |   Movies Rented   |  Salutation  | Category | Fiction? |
   |:---------------|:-----------|:------------------|:------------:|----------|----------|
   | Bob Smith      | 1 Houghton Street  | _Star Wars_, _Fast and Furious 7_ |  Dr. |  Scifi, Action | Yes, Yes|
   | Pry Ministair  | 10 Downing St  |  _Brexit: Three Years On_   |  Rt Hon.  | Documentary | No |
   | Joe Bloggs     | 8 Myhatt St.  |  _The Big Short_, _Star Wars_  | Mr. | Documentary, Scifi | No, Yes |


[your answer]

### 2.  Reshaping data

For this exercise, we will use the **nycflights13** R package, whose tables have been output in `.csv` form [here](nycflights13/).  You may do the following in either R or Python.  Note that this example is developed extensively in [_R for Data Science_](http://r4ds.had.co.nz/relational-data.html).

a)  Create a subtable of the `flights` data, that departed before 05:53 on 2013-02-28.  How many rows and columns does this subtable have?  

```{r}
library("dplyr")
library("lubridate")

flights <- read.csv("nycflights13/flights.csv")

flights$dep_datetime <- ymd_hm(sprintf("%s-%02d-%02d %02d:%02d",
                            flights$year,
                            flights$month,
                            flights$day,
                            flights$hour,
                            flights$minute))
flights_sub <- subset(flights, dep_datetime < ymd_hm("2013-02-28 05:53"))

nrow(flights_sub)
```

b)  Merge or join the subtable from a. `flights` data, to produce a result that includes:  
   *  Departure time
   *  Carrier (two digit code, from `flights`)
   *  Flight number
   *  Destination airport name (hint: you will need to get this from the `airports` table)  

```{r}
## your code
```

c) For every airline that had flights in the `flights` data compute the average age of the planes it flew from the entire dataset.  Age here will be defined as 2013 minus the `year` variable from the `planes` data.  Hint: This involves a join operation on `tailnum`, but also a grouped mean to compute the age (and subtracting 2013, which you can do before or after the computation of the mean).


```{r}
## your code
```

### 3.  Groupwork
Get into pairs or groups and think of a research question you might have using the flights data and produce some output to discuss (no more than 2-3 minutes) at the start of the next class. (You can send the code via slack to be run by Sarah or Yuanmo or connect your own laptop.)

Consult the dplyr reference of the many functions that there are: 
[dplyr reference](https://dplyr.tidyverse.org/reference/index.html)


```{r}
## your code
```
