---
title: "Untitled"
author: "JIASHU MIAO"
date: "2019/2/5"
output: html_document
---

```{r}
pkg <- c("readr","readxl","dplyr","stringr","ggplot2","data.table","tidyr")
lapply(pkg, require, character.only = TRUE)


```
- load data
```{r}
data_original <- read_excel("/Users/MichaelMiao/Documents/career/accument\ data\ excercise/Acumen_Data_Analysis_Exercise.xlsx", sheet = "Data")
attach(data_original)
#View(data_original)
names(data_original)
par(mfrow=c(2,5))
for (i in 1:length(data_original)) {
        boxplot(data_original[,i], main=names(data_original[i]), type="l")

}

# so there might be unreasonable variables in Age, Health Score
detach(data_original)
dataNEW1 <-  subset(data_original,`Health Score` >=0 & `Health Score` <=6)
# range(data_original$Age)
dataNEW2 <- subset(dataNEW1, dataNEW1$Age >= 18 & dataNEW1$Age <= 63 )
# summary(dataNEW2)
dataNEW2 <- na.omit(dataNEW2)
colSums(is.na(dataNEW2))
# filter out the observations that is not in correct score range 0:6, some of them 10!
# filter out the observations that the employ is beyond age 18 or over 62. 
# dataNEW2 becomes the new data


matx <- matrix(1:16, nrow = 4, byrow = T)
rowSums(matx)
apply(matx, 1, sum)

# Auto
# data(Auto)
# sapply(Auto,class)

# qplot(x=sqft,y= price,data = LArealstate,color=type,na.rm=T) + geom_point(na.rm = T)

# rop.table(table(data$Race[data$Quarter==1])) ## see the proportion of the race
# ## 
# ##         1         2         3 
# ## 0.5598007 0.2774086 0.1627907
# prop.table(table(data$`Sex (Male=1)`[data$Quarter==1]))  ## see the proportion of the sex

# agg = aggregate(data,
#                 by = list(data$Role),
#                 FUN = mean)

```



- some basic needs syntax: 
```{r}
par(mfrow=c(2,5))
quartermeanhealth <- aggregate(dataNEW2$`Health Score`,by = list(dataNEW2$Quarter), FUN = mean)
quartermeanhealth2 <- tapply(dataNEW2$`Health Score`,INDEX = dataNEW2$Quarter, FUN = mean) %>%  as.data.frame()
mydata <- dataNEW2
quartermeanhealth2 %>% dim() %>% as.double()
typeof(quartermeanhealth2)
typeof(quartermeanhealth)
quartermeanhealth <- quartermeanhealth[,2] %>% as.data.frame()
quartermeanhealth %>% dim()
identical(quartermeanhealth,quartermeanhealth2)
as.data.frame(matrix(unlist(mydata)))
s <- traning_data <- mydata[sample(mydata,1:nrow(mydata)), 50]
ss <- traning_data <- mydata[sample(1:nrow(mydata)), 50]
identical(ss,s)

# grep(pattern, x, ignore.case = FALSE, perl = FALSE, value = FALSE,
#      fixed = FALSE, useBytes = FALSE, invert = FALSE)
# 
# grepl(pattern, x, ignore.case = FALSE, perl = FALSE,
#       fixed = FALSE, useBytes = FALSE)
# 
# sub(pattern, replacement, x, ignore.case = FALSE, perl = FALSE,
#     fixed = FALSE, useBytes = FALSE)
# 
# gsub(pattern, replacement, x, ignore.case = FALSE, perl = FALSE,
#      fixed = FALSE, useBytes = FALSE)




# dunif(x, min = 0, max = 1, log = FALSE)
# punif(q, min = 0, max = 1, lower.tail = TRUE, log.p = FALSE)
# qunif(p, min = 0, max = 1, lower.tail = TRUE, log.p = FALSE)
# runif(n, min = 0, max = 1)

```

```{r}
# select()
# filter()
# group_by()
# summarise()
# arrange()
# join()
# mutate()
```

starts_with()	Starts with a prefix
ends_with()	Ends with a prefix
contains()	Contains a literal string
matches()	Matches a regular expression
num_range()	Numerical range like x01, x02, x03.
one_of()	Variables in character vector.
everything()	All variables.

```{r}
write.csv(df,file = "df_sample.csv",col.names = T)

par(mfrow=c(1,2))
library("dplyr")
# select func.
mydata <- airquality
class(mydata)
mydataozone <- subset(mydata, mydata$Month == 9 | mydata$Month == 5)
mydata33 <- select(mydata, contains("day")) # case insensitve # literal
mydata33
mydata34 <- select(mydata, matches("day")) # any regular expression
mydata34
mydata34 <- select(mydata, Day, everything()) # dplyr 里面 每一个 variable前面不能加data名字dollar sign
select(df, -c)#`: Exclude C from the dataset from df dataset
select(iris, one_of(c("Species", "Genus")))
# Select columns whose names are in a group of names.
 
#The select_if() function returns only those columns where logical condition is TRUE. The is.numeric refers to retain only numeric variables.
mydata35 = select_if(mydata[,2:3], is.numeric)

# select_all 
# select_all(.tbl, .funs = list(), ...)

select_all(mydata,funs(toupper(.)))
# summarise(mydata,Day,funs(mean()))
select_all(mydata,"toupper")
select_all(mydata,toupper)
select_all(mydata, funs())

select_at(mydata,vars(-c("Day","Month")))

# returns the result for logical is true

# select_if(mydata,vars(Ozone,quote(Solar.R)),is.numeric)
select_if(mydata,is.numeric)
# select(mydata,vars(Day,Ozone))    # vars is not for select 
s <- select_if(mydata[c("Day","Month")],is.factor)
floor(s)
is.whole <- function(x) all(floor(x) == x)
class(mydata)
haah <- function(x) all(floor(x) == x) 
#cp <- select_if(select_if(airquality,is.numeric), haah)

s <- select_if(mydata,is.numeric)
#select_if(mydata,is.whole)
#ss <- select_if(s,is.whole)
# select_vars(mydata,vars == "Day")  for charcter variables only

 is_whole <- function(x) all(floor(x) == x)
    select_if(mtcars, is.whole)
select(mydata,Day,Month,everything()) # reordering


```


```{r}
# rename() syntax : rename(data , new_name = old_name)
# data : Data Frame
# new_name : New variable name you want to keep
# old_name : Existing Variable Name

 mydata6 <- rename(mydata5, daysssss=Day)
head(mydata6,1)
select_at(mydata,c("Day","Month"))
rename_if(mtcars,is.whole,toupper)
mydata[order(mydata$Ozone),] # 正负号决定顺序




```
```{r}
s <- filter(mydata[,3:ncol(mydata)],Day == 1)
s <- s[1:3]
mydata37 <- filter(mydata,Temp %in% c(67,91)) %>%  print()
mydata37 <- filter(mydata) %>%  print()
# filter(mydata, funs(rowMeans(.)== 5))
# You can take the intersection of the replicated expressions:
filter_all(mtcars, any_vars(. > 150)) # filter 也可以用func logic!!!
filter(mtcars, mean(mpg) > 10)

filter_at(mtcars, vars(starts_with("d")), any_vars((. %% 2) == 0))

filter_at(mtcars, vars(starts_with("d")),any_vars(. >10))
filter_at(mtcars,vars(c("mpg","cyl")),all_vars(. >7)) #filter at 可以 vars
select_if(mtcars[,2:3],is.numeric)
filter_at(mtcars,vars(contains("mpg")),all_vars(. >23))
filter_if(mtcars, ~ all(floor(.) == .), all_vars(. != 0))  # ~ 很他妈关键
filter_if(mtcars, ~ all(is.numeric(.)== T), any_vars(. > 240))
mtcars %>% filter_at(vars(hp, vs), any_vars(. %% 2 == 0))
mydata10 = filter(mtcars, grepl("110", hp)) %>% print() # chracter 的时候贼tm好用
with(mtcars, hp == 110)
runif(4)
# filter 不用 contains
```

```{r}
# summarize 会给我一个新table这时候创建里的 和原数据table不一样  创建新的var


summarise_all(mydata,funs(n(),sum(.,na.rm = T),mean))

summarise(mydata,Daymean = mean(Day))
# sample_n(mydata,size = 10, replace = F)
summarise_at(mydata, vars(Day, Month, Temp), funs(n(), mean, median, length))

summarize_if(mydata,is.numeric,funs(n(),min(.,na.rm = T)))
# summarize_if(mydata,~ )any_vars(.>10,funs(n(),min(.,na.rm = T)))

 # alternative way

numdata <- mydata[sapply(mydata, is.numeric)] %>% summarise_all(funs(n(),mean(.,na.rm = F))) # na.rm = T
# We are checking the number of levels/categories and count of missing observations in a categorical (factor) variable.

summarise_all(mydata["Day"], funs(nlevels(.), nmiss=sum(is.na(.))))
summarise_all(mydata["Day"], funs(nlevels(.), nmiss=mean(.)))

summarise_at(mydata, vars(Temp, Day),
funs(n(), missing = sum(is.na(.)), mean(., na.rm = TRUE), median(.,na.rm = TRUE))) #结果会按照顺序


# create summarise using your own function

# set.seed(222)
# mydata <- data.frame(X1=sample(1:100,100), X2=runif(100))
# summarise_at(mydata,vars(X1,X2), function(x) var(x - mean(x)))

mydata 

summarise_all(mtcars, function(x) var(x))

select_if(mtcars,function(x)max(x)>400)
max(mtcars$disp)
summarise_at(mtcars,vars(disp), funs(max(.,na.rm = T)) )

# summarize all num
summarise_if(mydata, is.numeric, funs(n(),mean,median))

# group by summarize 喜欢用

summarize_at(group_by(mydata,Temp),vars(Day,Month),funs(n(),mean(.,na.rm = T)))

summarise_at(group_by(iris,Species),vars(Sepal.Width),funs(n()))
dim(iris)
#group_by() for cate
attach(iris)
group_by_if(mtcars, is.factor)

# summarise_at(group_by_if(iris,Species %in% "setosa"),vars(Sepal.Width))

# NOT RUN {
# Group a data frame by all variables:
group_by_all(mtcars)

# Group by variables selected with a predicate:
group_by_if(iris, is.factor)

# Group by variables selected by name:
group_by_at(mtcars, vars(vs, am))

# Like group_by(), the scoped variants have optional mutate
# semantics. This provide a shortcut for group_by() + mutate():
group_by_all(mtcars, as.factor)
group_by_if(iris, is.factor, as.character)
```
```{r}
tapply(dataNEW2$Age, dataNEW2$Quarter, sum) 
dataNEW2

dataNEW2 %>%  group_by_at(.,vars(Quarter, `Sex (Male=1)` )) %>% 
  summarise_at(.,vars(`Health Score`, Age), funs(mean(.,na.rm = T))) #cool

s <- sapply(mtcars, function(x) x*1000) %>% as.data.frame() %>% print() #so cool

mydata2 <- iris
summarise_at(group_by(mydata2,Species),vars(Sepal.Length),funs(mean(.,na.rm=TRUE)))
```

```{r}
rename()
```


```{r}
# arrange 
# mydata <- airquality
# arrange(mydata, desc(Month), Temp, Solar.R) # 后面的条件弱了

# s <- mydata %>% group_by(.,Ozone) 
# s

mydata8 <- mydata %>% select(Day,Month,Temp) %>%
  filter(Day %in% (1:20) | Month >= 6) %>% 
  group_by(Month) %>% do(arrange(.,desc(Day))) %>% slice(.,1:2) %>% print()

# group by 和 slice关联

t = mydata %>% select(Day, Month) %>%
   filter(Day %in% c("1", "2","3","4")) %>%
   group_by(Month) %>%
   filter(min_rank(desc(Day)) == 2 | Day == 4 ) %>% print()

#(min_rank(desc(Y2015)) == 3) 这时候 filter function要用

```


```{r}
mydata39 <- mutate(mydata,ratio= Temp/Day) %>% 
  select(.,ratio,everything()) %>% print() 
mydata40 <- mutate_all(mydata,funs("new"=.*1000))
names(mydata40) # 这个思路好流弊
 
 mydata12 = mutate_at(mydata, vars(Month,Temp), funs(Rank=min_rank(.))) %>% print()
 mydata42 <- mutate(mydata, ratio = Month/3, funs(Rank=min_rank(.))) %>% print()
 # arrange(mydata42,desc(ratio))
 # range(mydata42$ratio)
 # table(mydata42$Month)
 
 dff <- data.frame(x = c(1,5,6,NA))
 dff %>%  mutate(.,newvar = if_else(x<5,x+1,x+2,0))  #!!!cool
 # if_else(condition = ,true = ,false = ,missing = )

 mydf =data.frame(x = c(1:5,NA))
mydf %>% mutate(newvar= if_else(is.na(x),"I am missing",
if_else(x==1,"I am one",
if_else(x==2,"I am two",
if_else(x==3,"I am three","Others"))))) # cool太烧脑

# The rowwise() function allows you to apply functions to rows.
# df = mydata %>%
  # rowwise() %>% mutate(Max= max(Y2012,Y2013,Y2014,Y2015)) %>%
  # select(Y2012:Y2015,Max)
```
```{r}
mydata %>% rowwise() %>%  group_by(Month)%>% mutate(.,Max = max(Day,Month)) %>% select(Day,Month,Max) %>% arrange(desc(Max)) %>%  print()

# the order of pipe operator really matters

# mydata %>% rowwise() %>% mutate(.,Max=max(mydata$Day,mydata$Month)) %>% select(.,Day,Month,Max) %>% print()

# max(1,2,3)
```
```{r}

# 必须logical funct
is.biggerten <- function(x)all(x > 50)
is.biggerten(1)



summarise_if(mtcars,is.numeric,funs(nlevels(.)))
# summarise_all(mtcars,any_vars(.>10),funs(nlevels(.)))


class(mtcars$hp)
#summarise_at(mtcars, vars(mpg,hp), any_vars(.>10),funs(n())) # only for filter
# summarise_if(mtcars, is.whole,funs(n())) %>% print()
select_if(mtcars,is.whole) %>% print()


select_if(mtcars,is.biggerten)

#!!!!!!
# ifelse(Rentention,1,0)
# ifelse(test, yes, no)

# !!!!
```
```{r}
# index cumulative sum
#cumsum(mydata$Temp) 


# join 
#inner_join(x,y,by="ID")
# left, right, full, semi, anti

# Combine data vertically 
# rows in both x and y 
# mydata11 <- intersect(mydata10,mydata2)

# rows in either or both x, y
# mydata12 <- union(mydata10,mydata2) %>%  as.data.frame()

# rows in x but NOT in y
# mydata13 <- setdiff(mydata11,mydata)
```

```{r}
names(mydata)
ggplot(data = dataNEW2,mapping = aes(floor(Age), `Health Score`)) + geom_point(aes(color = "red" )) + geom_smooth(method = "glm")+coord_cartesian()+theme_classic()+xlab("shit")

ggplot(data = dataNEW2,mapping = aes( factor(`Hospital Visit This Quarter (1=Yes)`),`Health Score`,colour=factor(Quarter)))+geom_boxplot()+ facet_wrap(~Quarter)
# t + ggtitle("New Plot Title") Add a main title above the plot
# t + xlab("New X label") Change the label on the X axis
# t + ylab("New Y label") Change the label on the Y axis


#！！！！！！ factor
```


```{r}
# 想把fac换成 num的话 需要    


as.numeric(as.character(x))

```
```{r}

```
















