install.packages("devtools")
library("devtools")
install_github("ropensci/plotly")

library(plotly)

set_credentials_file("DemoAccount", "lr1c37zw81") ## Replace contents with your API Key

$ sqlite3 data.db # Create your database
$.databases       # Show databases to make sure it works
$.mode csv        
$.import <filename> <tablename>
# Where filename is the name of the csv & tablename is the name of the new database table
$.quit 

library(readr)
# data.table, selecting a subset of columns
time_data.table <- system.time(fread(\'/users/ryankelly/NYC_data.csv\', 
                   select = c(\'Agency\', \'Created Date\',\'Closed Date\', \'Complaint Type\', \'Descriptor\', \'City\'), 
                   showProgress = T))

kable(data.frame(rbind(time_data.table, time_data.table_full, time_readr)))

library(dplyr)      ## Will be used for pandas replacement

# Connect to the database
db <- src_sqlite(\'/users/ryankelly/data.db\')
db

# Wrapped in a function for display purposes
head_ <- function(x, n = 5) kable(head(x, n))

head_(data)

##       City
## 1 BROOKLYN
## 2 ELMHURST
## 3  JAMAICA
## 4 NEW YORK
## 5         
## 6  BAYSIDE

# dt[, .(No.Complaints = .N), Agency]
#setkey(dt, No.Complaints) # setkey index\'s the data

q <- data %>% select(Agency) %>% group_by(Agency) %>% summarise(No.Complaints = n())
head_(q)

# dt[, unique(City)]

q <- data %>% select(City) %>% distinct() %>% summarise(Number.of.Cities = n())
head(q)

##   Number.of.Cities
## 1             1818

# Plot result
plt <- ggplot(q_f, aes(ComplaintType, No.Complaints, fill = CITY)) + 
            geom_bar(stat = \'identity\') + 
            theme_minimal() + theme(axis.text.x = element_text(angle = 45, hjust = 1))

plt

# dt[CreatedDate < \'2014-11-26 23:47:00\' & CreatedDate > \'2014-09-16 23:45:00\', 
#      .(ComplaintType, CreatedDate, City)]

q <- data %>% filter(CreatedDate < "2014-11-26 23:47:00",   CreatedDate > "2014-09-16 23:45:00") %>%
    select(ComplaintType, CreatedDate, City)

head_(q)

# dt[, hour := strftime(\'%H\', CreatedDate), .(ComplaintType, CreatedDate, City)]

q <- data %>% mutate(hour = strftime(\'%H\', CreatedDate)) %>% 
            select(ComplaintType, CreatedDate, City, hour)

head_(q)

# Using lubridate::new_period()
# dt[, interval := CreatedDate - new_period(900, \'seconds\')][, .(CreatedDate, interval)]

q <- data %>% 
     mutate(interval = sql("datetime((strftime(\'%s\', CreatedDate) / 900) * 900, \'unixepoch\')")) %>%                     
     select(CreatedDate, interval)

head_(q, 10)