---
title: "Exercise: Merging and Reshaping Data"
author: "Rian DeFaccio, for BH Analytics LLC"
date: "`r Sys.Date()`"
output: html_document
---

To begin, load any libraries you would like to use.

```{r}
library(tidyverse)
```

### 1. Import Data

------------------------------------------------------------------------

In this exercise we are going to work with some data from a simulated company. The data is split across three tables as described below:

-   ***data/retail_data/customers.csv*****:** The customers data set contains information about the country of origin for each `CustomerID`.
-   ***data/retail_data/stock_codes.csv*****:** The stock codes data set contains information about the description for each item sold.
-   ***data/retail_data/transactions2.csv*****:** The transactions data contains information about each store transaction, and can be linked to both the customers and stock codes data sets.

Begin by importing all three data sets. Do an initial inspection and adjust data types for columns as needed.

```{r message=FALSE}
transactions <- read_csv('../data/retail_sales/transactions2.csv') %>%
  mutate(InvoiceDateDt = mdy_hm(InvoiceDate),
         CustomerID = as.character(CustomerID))
head(transactions)
```

```{r message=FALSE}
stock_codes <- read.csv('../data/retail_sales/stock_codes.csv')
head(stock_codes)
```

```{r message=FALSE}
customers <- read_csv('../data/retail_sales/customers.csv') %>%
  mutate(CustomerID = as.character(CustomerID))
head(customers)
```

### 2. Initial Inspection: Customers

------------------------------------------------------------------------

Let's begin by inspecting the customer data set.

Verify that `CustomerID` is unique in this table. In other words, check that there are as many rows in the Data Frame as there are unique `CustomerID`s.

```{r}
# as many rows in DF as CustomerIDs?
nrow(customers)
n_distinct(customers$CustomerID)
# yup! that looks good
```

Which country has the highest number of customers?

Which country has the lowest number of customers?

```{r}
# which country has the most/least customers
table(customers$Country, useNA = 'ifany') %>% 
  sort(decreasing = TRUE) %>% 
  as.data.frame()
# United Kingdom has 3,950 customers
# 9 countries only have one customer
```

### 3. Initial Inspection: Stock Codes

------------------------------------------------------------------------

Let's do basically the same thing for the stock codes data set. Check that the overall number of rows and the number of unique stock codes matches.

```{r}
# as many rows in DF as StockCodes?
nrow(stock_codes)
n_distinct(stock_codes$StockCode)
# yup! that looks good
```

Next, examine the descriptions in this data set. How many unique descriptions are there?

```{r}
n_distinct(stock_codes$Description)
# lots of descriptions
```

Take a look at some of the most common descriptions

```{r}
table(stock_codes$Description) %>%
  sort(decreasing = TRUE) %>%
  as.data.frame() %>%
  head(10)
```

That's all we'll do with the stock codes for now.

### 4. Initial Inspection: Transactions

------------------------------------------------------------------------

What are the columns you would use to join this table to the prior two tables? Are these columns unique in the transactions table? How can you check?

```{r}
# considering CustomerID first
table(transactions$CustomerID) %>% 
  sort(decreasing = TRUE) %>%
  head()
# many [CustomerID]s are repeated many times
# this makes sense, each customer can have many, many transactions
```

```{r}
# considering StockCodes next
table(transactions$StockCode) %>% 
  sort(decreasing = TRUE) %>%
  head()
# many [StockCode]s are also repeated many times
# this also makes sense, many items are sold many times
```

Is `InvoiceNo` unique in this table? Is it ever repeated? Is there a unique identifier in this table?

```{r}
head(transactions)
# already we can see [InvoiceNo] is repeated across many rows.
# there would not appear to be a unique identifier (aka primary key)
```

Look at the distribution of the unit prices. Do this first by getting a few summary statistics.

```{r}
summary(transactions$UnitPrice)
```

Examine the transactions that had negative unit prices. How many transactions were there?

```{r}
transactions %>%
  filter(UnitPrice < 0)
# just one
```

Examine the transactions that are missing `InvoiceNo`s. How many were there?

```{r}
transactions %>% filter(is.na(InvoiceNo))
# also just one
```

How many extremely large transactions were there? We will let you determine for yourself what qualifies as an "extremely large" transaction.

```{r}
transactions %>%
  filter(UnitPrice > 1000) %>%
  arrange(desc(UnitPrice))
# if we consider $1,000 as extremely large, then there were 8 
```

Finally, let's do a little data cleaning by removing rows that had negative unit prices or where the `InvoiceNo` is missing.

Save this as a Data Frame called `trans_clean`.

```{r}
trans_clean <- transactions %>%
  filter(UnitPrice >= 0 & !is.na(InvoiceNo))
```

### 5. Merge Data

------------------------------------------------------------------------

Now let's merge all of the data sets together. We will have to do two merges, and in both cases we want to keep all of our transactions while gathering as much additional data from the other tables as possible. What kind of merge should you use to accomplish this?

Call your final Data Frame `df_final`.

```{r}
df_final <- transactions %>%
  left_join(customers, by = 'CustomerID') %>%
  left_join(stock_codes, by = 'StockCode')
head(df_final)
```

### 

### 6. Analyze Units Sold

------------------------------------------------------------------------

Let's look at how many units were sold. First calculate how many units were sold in total. The `Quantity` variable tells us how many units were sold in each transaction.

```{r}
df_final %>%
  summarise(units_sold = sum(Quantity)) %>%
  pull(units_sold) %>%
  format(big.mark = ",") %>%
  paste0('There were ', ., ' units sold in total.')
```

Were there more units sold in the year 2010 or 2011?

```{r}
# need to generate a year variable first
df_final$Year <- lubridate::year(df_final$InvoiceDateDt)

df_final %>%
  filter(Year == 2010) %>%
  summarise(units_sold = sum(Quantity)) %>%
  pull(units_sold) %>%
  format(big.mark = ",") %>%
  paste0('There were ', ., ' units sold in 2010.')

df_final %>%
  filter(Year == 2011) %>%
  summarise(units_sold = sum(Quantity)) %>%
  pull(units_sold) %>%
  format(big.mark = ",") %>%
  paste0('There were ', ., ' units sold in 2011.')

# there were more units sold in 2011
```

### 7. Analyze Revenue

------------------------------------------------------------------------

Now let's consider total revenue. What was the total amount of revenue in the entire data set? To figure out the revenue you will need to create a new column first called `transaction_revenue` which is equal to the quantity of units multiplied by the unit price.

```{r}
df_final$transaction_revenue <- df_final$Quantity * df_final$UnitPrice

df_final %>%
  summarise(total_sales = sum(transaction_revenue)) %>%
  pull(total_sales) %>%
  format(big.mark = ",") %>%
  paste0('There was $', ., ' in total sales.')
```

Was there more revenue in 2010 or 2011?

```{r}
df_final %>%
  filter(Year == 2010) %>%
  summarise(total_sales = sum(transaction_revenue)) %>%
  pull(total_sales) %>%
  format(big.mark = ",") %>%
  paste0('There was $', ., ' in total sales in 2010.')

df_final %>%
  filter(Year == 2011) %>%
  summarise(total_sales = sum(transaction_revenue)) %>%
  pull(total_sales) %>%
  format(big.mark = ",") %>%
  paste0('There was $', ., ' in total sales in 2011.')

# again, more in 2011
```

### 8. Save Your Work

------------------------------------------------------------------------

Let's save `df_final` so we can use it later for further analysis. Save it as a *.rds* file in the *data/retail_sales* folder with a name like *retail_sales_analytic_rjd.rds*.

```{r}
saveRDS(df_final, '../data/retail_sales/retail_sales_analytic.rds')
```
