Week 1 Notebook

Explore The Data

# Load text file into local variable called 'data'
data = read.delim(file = 'purchases.txt', header = FALSE, sep = '\t', dec = '.')
# Display what has been loaded
head(data)

# FOR EACH COLUMN prints min, quadrants, median, mean, max
summary(data)

       V1               V2                   V3       
 Min.   :    10   Min.   :   5.00   2013-12-31:  864  
 1st Qu.: 57720   1st Qu.:  25.00   2006-12-31:  584  
 Median :102440   Median :  30.00   2012-12-31:  583  
 Mean   :108935   Mean   :  62.34   2011-12-31:  510  
 3rd Qu.:160525   3rd Qu.:  60.00   2008-12-31:  503  
 Max.   :264200   Max.   :4500.00   2014-12-31:  485  
                                    (Other)   :47714  

Transform The Data

# Add headers and interpret the last column as a date, extract year of purchase
# rename columns to human understandable representations
colnames(data) = c('customer_id', 'purchase_amount', 'date_of_purchase')
# tell R this column is a date variable
data$date_of_purchase = as.Date(data$date_of_purchase, "%Y-%m-%d")
# create a new column that contains the year of purchase
data$year_of_purchase = as.numeric(format(data$date_of_purchase, "%Y"))

# Display the data set after transformation
head(data)

summary(data)

  customer_id     purchase_amount   date_of_purchase    
 Min.   :    10   Min.   :   5.00   Min.   :2005-01-02  
 1st Qu.: 57720   1st Qu.:  25.00   1st Qu.:2009-01-17  
 Median :102440   Median :  30.00   Median :2011-11-23  
 Mean   :108935   Mean   :  62.34   Mean   :2011-07-14  
 3rd Qu.:160525   3rd Qu.:  60.00   3rd Qu.:2013-12-29  
 Max.   :264200   Max.   :4500.00   Max.   :2015-12-31  
 year_of_purchase
 Min.   :2005    
 1st Qu.:2009    
 Median :2011    
 Mean   :2011    
 3rd Qu.:2013    
 Max.   :2015    

Use SQL and visualize the Data

# To explore the data using simple SQL statements
# we'll use sqldf package which allows us to use a
# SQL database like an R dataframe & a dataframe like a SQL DB
library(sqldf)
# Number of purchases per year
## SELECT sql statement which is used to fetch the data from a database
## and returns results in the form of a data table
# see how many purchases were made each year from data set 'data':
# 'SELECT each year_of_purchase and COUNT each instance of year_of_purchase
x = sqldf("SELECT year_of_purchase, COUNT(year_of_purchase) AS 'counter' FROM data GROUP BY 1 ORDER BY 1")
# now plot the counted number of purchases by year as a bar plot
barplot(x$counter, names.arg = x$year_of_purchase)

# Average purchase amount per year
x = sqldf("SELECT year_of_purchase, AVG(purchase_amount) AS 'avg_amount' FROM data GROUP BY 1 ORDER BY 1")
barplot(x$avg_amount, names.arg = x$year_of_purchase)

# Total purchase amounts per year
x = sqldf("SELECT year_of_purchase, SUM(purchase_amount) AS 'sum_amount' FROM data GROUP BY 1 ORDER BY 1")
barplot(x$sum_amount, names.arg = x$year_of_purchase)

# All in one
x = sqldf("SELECT year_of_purchase,
                  COUNT(year_of_purchase) AS 'counter',
                  AVG(purchase_amount) AS 'avg_amount',
                  SUM(purchase_amount) AS 'sum_amount'
           FROM data GROUP BY 1 ORDER BY 1")
print(x)