Repository navigation
Expand file tree
/
Copy pathData Analysis Workflow.Rmd
More file actions
4299 lines (4121 loc) · 157 KB
/
Copy pathData Analysis Workflow.Rmd
File metadata and controls
4299 lines (4121 loc) · 157 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
---
title: "Data Analysis Workflow"
---
# 0. Step 0 Get ready
## 0.1 Set working directory
```{r}
getwd()
setwd()
```
**Relative** - `setwd("./data")`, `setwd("../")` (Move up one directory)
**Absolute** - `setwd("/Users/jtleek/data/")` "/" for path in windows
## 0.2 Check for and create directories
```{r}
# check to see if the directory exists
file.exists("directoryName")
# create a directory if it doesn't exist
dir.create("directoryName")
# an example checking for a "data" directory and creating it if it doesn't exist
if(!file.exists("data")){
dir.create("data")
}
```
# Packages and Tips
## {base}
rewrite the names of the columns to remove any spaces
```{r}
names(ozone) <- make.names(names(ozone))
```
## {ggplot2}
I've coloured the models by `-dist`: this is an easy way to make sure that the best models (i.e. the ones with the smallest distance) get the brighest colours.
```{r}
ggplot(sim1, aes(x, y)) +
geom_point(size = 2, colour = "grey30") +
geom_abline(
aes(intercept = a1, slope = a2, colour = -dist),
data = filter(models, rank(dist) <= 10)
)
```
## {data.table}
Much, much faster at subsetting, group, and updating
### Create data tables just like data frames
```{r}
library(data.table)
DF = data.frame(x=rnorm(9),y=rep(c("a","b","c"),each=3),z=rnorm(9))
head(DF,3)
DT = data.table(x=rnorm(9),y=rep(c("a","b","c"),each=3),z=rnorm(9))
head(DT,3)
```
### See all the data tables in memory
```{r}
tables()
```
### Subset rows
```{r}
DT[2,]
DT[DT$y=="a",]
```
### Subsetting is based on rows (not columns like DF[c(2,3)])
```{r}
DT[c(2,3)]
```
**Data table use expression after comma**
### Calculate values for variables with expressions
```{r}
DT[,list(mean(x),sum(z))]
DT[,table(y)]
```
### Add new columns
```{r}
DT[,w:=z^2]
```
**When add a new variable to a data table, a new copy isn't being created. If you're trying to create a copy, you have to explicitly do that with the copy function.**
### Multiple operations
```{r}
DT[,m:= {tmp <- (x+z); log2(tmp+5)}]
```
### plyr like operations
```{r}
DT[,a:=x>0]
```
```{r}
DT[,b:= mean(x+w),by=a]
```
`by=a` aggregated/grouped by a. Then add aggregated values (mean in this case) of different groups to each row of b.
### Special variables
`.N` An integer, length 1, containing the number of elements (counts) of a factor level
```{r}
set.seed(123);
DT <- data.table(x=sample(letters[1:3], 1E5, TRUE))
DT[, .N, by=x]
```
### Keys
#### To subset
```{r}
DT <- data.table(x=rep(c("a","b","c"),each=100), y=rnorm(300))
setkey(DT, x)
DT['a']
```
#### To Join(merge)
```{r}
DT1 <- data.table(x=c('a', 'a', 'b', 'dt1'), y=1:4)
DT2 <- data.table(x=c('a', 'b', 'dt2'), z=5:7)
setkey(DT1, x); setkey(DT2, x)
merge(DT1, DT2)
```
### Fast reading from disk
```{r}
big_df <- data.frame(x=rnorm(1E6), y=rnorm(1E6))
file <- tempfile()
write.table(big_df, file=file, row.names=FALSE, col.names=TRUE, sep="\t", quote=FALSE)
system.time(fread(file))
system.time(read.table(file, header=TRUE, sep="\t"))
```
`fread()` could be applied to reading data tables, a substitute for `read.table()` with tab separated files
### Further resources
- [Latest development of data.table](https://github.com/Rdatatable/data.table/wiki)
- [A list of differences between data.table and data.frame](https://stackoverflow.com/questions/13618488/what-you-can-do-with-data-frame-that-you-cant-in-data-table)
## {sqldf}
sqldf package allows for execution of SQL commands on R data frames
## {doParallel}
```{r}
library(doParallel) # doParallel lets use a bunch cores, which speeds things up
cl <- makePSOCKcluster(7)
registerDoParallel(cl) # this starts things off
stopCluster(cl)
```
# 1. Step 1 Get the data
## 1.1 From the internet
```{r}
# Download the file
fileUrl <- "https://data.baltimorecity.gov/api/views/dz54-2aru/rows.csv?accessType=DOWNLOAD"
download.file(fileUrl,destfile="./data/cameras.csv",mode = 'wb')
# Check the downloaded file
list.files("./data")
# Record the date downloaded
dateDownloaded <- date()
dateDownloaded
```
## 1.2 From local flat files
### 1.2.1 {base}
#### read.table()
```{r}
cameraData <- read.table("./data/cameras.csv",sep=",",header=TRUE)
```
Important parameters
- `file`
- `header`
- `sep` - delimiter
- `row.names`
- `nrows` - how many rows to read of the file (e.g. nrows=10 reads 10 lines)
- `quote` - whether there are any quoted values, quote="" means no quotes
- `na.strings` - set the character that represents a missing value
- `skip` - number of lines to skip before starting to read
- `colClasses`
#### read.csv()
```{r}
cameraData <- read.csv("./data/cameras.csv")
```
`read.csv` sets `sep=","` and `header=TRUE`
### 1.2.2 {readr}
#### read_csv()
```{r}
library(readr)
ozone <- read_csv("data/hourly_44201_2014.csv", col_types = "ccccinnccccccncnncccccc")
```
`col_types=` c = character, i = integer, n = number, d = double, l = logical, D = date, T = date time, t = time, ? = guess, or _/- to skip the column
## 1.3 From Excel files
### 1.3.1 {readxl}
#### read_excel()
```{r}
download.file(fileUrl,destfile="./data/cameras.xlsx",mode="wb")
library(readxl)
cameraData <- read_excel("./data/cameras.xlsx",sheet=1,col_names = TRUE,range=cell_limits(ul=c(18,7),lr=c(23,15)))
```
## 1.3 From XML or Html
### 1.3.1 {XML}
```{r}
library(XML)
fileUrl <- "http://www.w3schools.com/xml/simple.xml"
doc <- xmlTreeParse(fileUrl,useInternal=TRUE) # or htmlTreeParse() if from Html
```
`useInternal=TRUE` get all the different nodes inside of the file
1. Inspect the data
```{r}
# get rootNode, the wrapper for the entire document
rootNode <- xmlRoot(doc)
# get the name of the document
xmlName(rootNode)
# get all the nested elements within the rootNode
names(rootNode)
```
```{r}
# Directly access parts of the XML document
rootNode[[1]]
# access subcomponent
rootNode[[1]][[1]]
```
2. Programatically extract parts of the file
```{r}
# get all values
xmlSApply(rootNode,xmlValue)
```
To be more specific, use [XPath](http://www.stat.berkeley.edu/~statcur/Workshop2/Presentations/XML.pdf)
- `/node` Top level node
- `//node` Node at any level
- `node[@attr-name]` Node with an attribute name
- `node[@attr-name='bob']` Node with attribute name attr-name='bob'
```{r}
# examples
xpathSApply(rootNode,"//name",xmlValue)
xpathSApply(rootNode,"//price",xmlValue)
```
2.1 Extract content by attributes
```{r}
# example
fileUrl <- "http://espn.go.com/nfl/team/_/name/bal/baltimore-ravens"
doc <- htmlTreeParse(fileUrl,useInternal=TRUE)
scores <- xpathSApply(doc,"//li[@class='score']",xmlValue)
teams <- xpathSApply(doc,"//li[@class='team-name']",xmlValue)
```
### 1.3.2 Notes and further resources
- Official XML tutorials [short](http://www.omegahat.org/RSXML/shortIntro.pdf), [long](http://www.omegahat.org/RSXML/shortIntro.pdf)
- [An outstanding guide to the XML package](https://www.stat.berkeley.edu/~statcur/Workshop2/Presentations/XML.pdf)
## 1.4 From JSON
### 1.4.1 {jsonlite}
```{r}
library(jsonlite)
jsonData <- fromJSON("https://api.github.com/users/jtleek/repos")
```
1. Inspect the data (nested objects)
```{r}
names(jsonData)
jsonData$name
names(jsonData$owner)
jsonData$owner$login
```
2. Writing data frames to JSON
```{r}
myjson <- toJSON(iris, pretty=TRUE)
# print the data
cat(myjson)
```
`pretty=TRUE` give nice indentations so it's easy to read
### 1.4.2 Further resources
- [http://www.json.org/](http://www.json.org/)
- [A good tutorial on jsonlite](https://www.r-bloggers.com/new-package-jsonlite-a-smarter-json-encoderdecoder/)
- [jsonlite vignette](https://cran.r-project.org/web/packages/jsonlite/vignettes/json-mapping.pdf)
## 1.5 From MySQL
### 1.5.1 {RMySQL}
1. Connect and list databases
```{r}
library(RMySQL)
ucscDb <- dbConnect(MySQL(),user="genome",
host="genome-mysql.cse.ucsc.edu")
result <- dbGetQuery(ucscDb,"show databases;"); dbDisconnect(ucscDb)
```
2. Connect to specific database and list tables
```{r}
hg19 <- dbConnect(MySQL(),user="genome", db="hg19",
host="genome-mysql.cse.ucsc.edu")
allTables <- dbListTables(hg19)
length(allTables)
allTables[1:5]
```
3. Get dimensions of a specific table
```{r}
# get column names
dbListFields(hg19,"affyU133Plus2")
# get number of rows
dbGetQuery(hg19, "select count(*) from affyU133Plus2")
```
4. Read from the table
```{r}
affyData <- dbReadTable(hg19, "affyU133Plus2")
head(affyData)
```
4.1 Select a specific subset
```{r}
query <- dbSendQuery(hg19, "select * from affyU133Plus2 where misMatches between 1 and 3")
affyMis <- fetch(query)
affyMisSmall <- fetch(query,n=10); dbClearResult(query)
dim(affyMisSmall)
```
5. Close the connection
```{r}
dbDisconnect(hg19)
```
### 1.5.2 Further resources
- [RMySQL vignette](https://cran.r-project.org/web/packages/RMySQL/RMySQL.pdf)
- [List of MySQL commands](http://www.pantz.org/software/mysql/mysqlcommands.html)
- A nice blog post summarizing [some other commands](https://www.r-bloggers.com/mysql-and-r/)
## 1.6 From HDF5
### 1.6.1 {rhdf5}
```{r}
source("http://bioconductor.org/biocLite.R")
biocLite("rhdf5")
library(rhdf5)
```
1. Create HDF5 file
```{r}
created = h5createFile("example.h5")
```
1.1 Create groups
```{r}
created = h5createGroup("example.h5","foo")
created = h5createGroup("example.h5","baa")
created = h5createGroup("example.h5","foo/foobaa")
h5ls("example.h5")
```
1.2 Write to groups
```{r}
A = matrix(1:10,nr=5,nc=2)
h5write(A, "example.h5","foo/A")
B = array(seq(0.1,2.0,by=0.1),dim=c(5,2,2))
attr(B, "scale") <- "liter"
h5write(B, "example.h5","foo/foobaa/B")
h5ls("example.h5")
```
1.3 Write a data set
```{r}
df = data.frame(1L:5L,seq(0,1,length.out=5),
c("ab","cde","fghi","a","s"), stringsAsFactors=FALSE)
h5write(df, "example.h5","df")
h5ls("example.h5")
```
2. Read data
```{r}
readA = h5read("example.h5","foo/A")
readB = h5read("example.h5","foo/foobaa/B")
readdf= h5read("example.h5","df")
readA
```
3. Write and read chunks(subsets)
```{r}
h5write(c(12,13,14),"example.h5","foo/A",index=list(1:3,1))
h5read("example.h5","foo/A")
```
`index=list(1:3,1)` give you indices for the dimensions to read/write
#### 1.6.2 Further resources
- [The rhdf5 tutorial](http://www.bioconductor.org/packages/release/bioc/html/rhdf5.html)
- The HDF group has [informaton on HDF5 in general](https://portal.hdfgroup.org/display/HDF5/HDF5)
## 1.7 From Web
### 1.7.1 {base}
#### readLines()
```{r}
# open a connection
con = url("http://scholar.google.com/citations?user=HI-I6C0AAAAJ&hl=en")
htmlCode = readLines(con)
# close the connection
close(con)
```
### 1.7.2 {XML}
```{r}
library(XML)
url <- "http://scholar.google.com/citations?user=HI-I6C0AAAAJ&hl=en"
html <- htmlTreeParse(url, useInternalNodes=T)
xpathSApply(html, "//title", xmlValue)
xpathSApply(html, "//td[@id='col-citedby']", xmlValue)
```
`useInternalNodes=T` gets the complete structure out
### 1.7.3 {httr}
#### GET()
```{r}
library(httr)
# GET the URL
html2 = GET(url)
# extract the content from that HTML page as a text
content2 = content(html2,as="text")
# parse the content
parsedHtml = htmlParse(content2,asText=TRUE)
xpathSApply(parsedHtml, "//title", xmlValue)
```
1. Access websites with passwords
```{r}
pg2 = GET("http://httpbin.org/basic-auth/user/passwd",
authenticate("user","passwd"))
```
1.1 Use handles (to store authentication)
```{r}
google = handle("http://google.com")
pg1 = GET(handle=google,path="/")
pg2 = GET(handle=google,path="search")
```
### 1.7.4 Further resources
- [R Bloggers](https://www.r-bloggers.com/) has a number of examples of web scraping
- The [httr help file](https://cran.r-project.org/web/packages/httr/httr.pdf) has useful examples
## 1.8 From APIs
### 1.8.1 {httr}
1. Access Website from R
```{r}
# example 1: Twitter
library(httr)
myapp = oauth_app("twitter",
key="yourConsumerKeyHere",secret="yourConsumerSecretHere")
sig = sign_oauth1.0(myapp,
token = "yourTokenHere",
token_secret = "yourTokenSecretHere")
homeTL = GET("https://api.twitter.com/1.1/statuses/home_timeline.json", sig)
```
```{r}
# example 2: GitHub
# 1. Find OAuth settings for github:
# http://developer.github.com/v3/oauth/
oauth_endpoints("github")
# 2. To make your own application, register at
# https://github.com/settings/developers. Use any URL for the homepage URL
# (http://github.com is fine) and http://localhost:1410 as the callback url
# Replace your key and secret below.
myapp <- oauth_app("datasciencespecialization",
key = "key",
secret = "secret")
# 3. Get OAuth credentials
github_token <- oauth2.0_token(oauth_endpoints("github"), myapp)
# 4. Use API
gtoken <- config(token = github_token)
req <- GET("https://api.github.com/users/jtleek/repos", gtoken)
stop_for_status(req)
content(req)
# OR:
req <- with_config(gtoken, GET("https://api.github.com/users/jtleek/repos"))
stop_for_status(req)
content(req)
```
2. Convert the json object
```{r}
# example 1
# return a structured R object, which is a little bit hard to read
json1 = content(homeTL)
```
### 1.8.2 {jsonlite}
```{r}
# example 1
# reformat it as a data frame where each row corresponds to a tweet in the user's timeline
json2 = jsonlite::fromJSON(toJSON(json1))
```
```{r}
# example 2
library(jsonlite)
repo <- fromJSON(toJSON(content(req)))
```
## 1.9 From image
- [jpeg](https://cran.r-project.org/web/packages/jpeg/index.html)
- [readbitmap](https://cran.r-project.org/web/packages/readbitmap/index.html)
- [png](https://cran.r-project.org/web/packages/png/index.html)
- [EBImage (Bioconductor)](http://www.bioconductor.org/packages/2.13/bioc/html/EBImage.html)
## 1.10 From GIS data
- [rgdal](https://cran.r-project.org/web/packages/rgdal/index.html)
- [rgeos](https://cran.r-project.org/web/packages/rgeos/index.html)
- [raster](https://cran.r-project.org/web/packages/raster/index.html)
## 1.11 From music data
- [tuneR](https://cran.r-project.org/web/packages/tuneR/)
- [seewave](http://rug.mnhn.fr/seewave/)
# 2. Step 2 Clean the data
## 2.1 Data transformation
### 2.1.1 Subset
#### 2.1.1.1 {base}
1. Position or name
```{r}
# subset by colunmns, x is a data frame
X[,1]
# or
X[1]
# or
X[,'var1']
# subset by rows and colunmns
X[1:2,'var2']
```
2. Logicals (and, or, %in%)
```{r}
X[(X$var1 <= 3 & X$var3 > 11),]
X[(X$var1 <= 3 | X$var3 > 15),]
restData[restData$zipCode %in% c("21212","21213"),]
```
3. Deal with missing values
```{r}
X[which(X$var2 > 8),]
```
`which()` returns the indices where conditions are met
#### 2.1.1.2 {dplyr} or {tidyverse}
##### filter() (rows)
```{r}
donor %>% filter(amount > 100)
donor %>% filter(! type %in% 'Candidate')
```
Operators for conditions: `!, %in%, >, >=, <, <=, ==, &, |`
##### select() (columns)
```{r}
select(chicago, 1:5)
select(chicago, city:dptp)
select(chicago, -(city:dptp))
```
### 2.1.2 Reorder
#### 2.1.2.1 {dplyr} or {tidyverse}
##### arrange()
```{r}
arrange(chicago, desc(date))
```
#### 2.1.2.2 {base}
##### sort()
```{r}
sort(X$var1)
sort(X$var1,decreasing=TRUE)
sort(X$var2,na.last=TRUE)
```
##### order()
```{r}
X[order(X$var1),]
X[order(X$var1,X$var3),]
```
`order()` returns a permutation which rearranges its first argument into ascending or descending order, breaking ties by further arguments.
### 2.1.3 Add rows and columns
#### 2.1.3.1 {base}
```{r}
X$var4 <- rnorm(5)
```
##### cbind() and rbind()
```{r}
Y <- cbind(X,rnorm(5))
```
#### 2.1.3.2 {dplyr} or {tidyverse}
##### bind_rows() and bind_cols()
Append z to y as new rows.
```{r}
bind_rows(y, z)
```
Append z to y as new columns.
```{r}
bind_cols(y, z)
```
Caution: matches rows by position.
### 2.1.4 Rename
#### 2.1.4.1 {dplyr} or {tidyverse}
##### rename()
```{r}
rename(chicago, dewpoint = dptp, pm25 = pm25tmean2)
```
### 2.1.5 Create new variables
#### 2.1.5.1 {dplyr} or {tidyverse}
##### mutate()
```{r}
library(tidyverse)
restData2 = mutate(restData,zipGroups=cut2(zipCode,g=4))
```
#### 2.1.5.2 {base}
- Create sequences
```{r}
s1 <- seq(1,10,by=2)
s2 <- seq(1,10,length=3)
x <- c(1,3,8,25,100); seq(along = x)
```
- Create binary variables
```{r}
restData$nearMe = restData$neighborhood %in% c("Roland Park", "Homeland")
restData$zipWrong = ifelse(restData$zipCode < 0, TRUE, FALSE)
```
#### Common transforms
- `abs(x)` absolute value
- `sqrt(x)` square root
- `ceiling(x)` ceiling(3.475) is 4
- `floor(x)` floor(3.475) is 3
- `round(x,digits=n)` round(3.475,digits=2) is 3.48
- `signif(x,digits=n)` signif(3.475,digits=2) is 3.5
- `cos(x)`, `sin(x)` etc.
- `log(x)` natural logarithm
- `log2(x)`, `log10(x)` other common logs
- `exp(x)` exponentiating x
- [http://statmethods.net/management/functions.html](http://statmethods.net/management/functions.html)
### 2.1.6 Reshape the data
#### 2.1.6.1 {tidyr} or {tidyverse}
##### Convert columns into rows (with `gather()`)
```{r}
donor %>% gather(column_name, year, c(receipt_year, election_year))
```
`key=column_name` (new) name of column that store (old) column names
`value=year` (new) name of column that store (old) values in (old) columns
`c(receipt_year, election_year)` A selection of columns. If empty, all variables are selected. You can supply bare variable names, select all variables between x and z with `x:z`, exclude y with `-y`.
##### Convert rows into columns (with `spread()`)
```{r}
donor %>% spread(receipt_year, amount)
```
`key=receipt_year` (old) name or position of column that contains the names of the (new) columns
`value=amount` (old) name of column that contains the values of the (new) columns
#### 2.1.6.2 {reshape2}
```{r}
library(reshape2)
```
##### melt() data frames
```{r}
mtcars$carname <- rownames(mtcars)
carMelt <- melt(mtcars,id=c("carname","gear","cyl"),measure.vars=c("mpg","hp"))
```
`id=` creates an id value for car name, for gear and for cylinders. And then melt all the rest of the values.
##### Cast (dcast()) data frames
```{r}
cylData <- dcast(carMelt, cyl ~ variable)
cylData <- dcast(carMelt, cyl ~ variable,mean)
```
Default aggregation function is length()
#### 2.1.6.3 Further resources
- [A nice reshape tutorial](https://www.slideshare.net/jeffreybreen/reshaping-data-in-r)
### 2.1.7 Aggregate values / Create a new variable
#### 2.1.7.1 {dplyr} or {tidyverse}
##### group_by(), summarise()
###### Example 1
In `donor`, which `type` received the more money in donations from `party` 'REPUBLICAN'?
```{r}
donor %>% group_by(type, party) %>% summarise(dollars = sum(amount, na.rm = TRUE))
```
- `group_by()` lists the variables by which to aggregate data
- `summarise()` creates a variable (dollars) and define the variable with an aggregation function
- Use `%>%` to 'link' `group_by()` and `summarise()`
- Aggregation functions: `n(), n_distinct(), sum(), mean(), max(), min()`, etc.
- `options(pillar.sigfig = 10)` to see more digits for numeric results in tibble
###### Example 2
In `donor`, what is the largest average donation `amount` for `contributor_state`?
```{r}
donor %>%
group_by(contributor_state) %>%
summarise(avg_donation = mean(amount, na.rm = TRUE)) %>%
arrange(desc(avg_donation))
```
###### Example 3
```{r}
police %>%
transmute(
initial_type_group = initial_type_group %>% as.character()
, district_sector = district_sector %>% as.character()
, event_clearance_group = event_clearance_group %>% as.character()
) %>%
mutate(
initial_type_group = ifelse(initial_type_group %in% NA, 'Unknown', initial_type_group)
) %>%
filter(event_clearance_group %in% 'DISTURBANCES') %>%
group_by(initial_type_group, district_sector) %>%
summarise(n = n()) %>%
write_csv('tidyverse_exercise_output.csv')
```
###### Example 4
```{r}
police %>%
transmute(
initial_type_group = initial_type_group %>% as.character()
, district_sector = district_sector %>% as.character()
, event_clearance_group = event_clearance_group %>% as.character()
) %>%
mutate(
initial_type_group = ifelse(initial_type_group %in% NA, 'Unknown', initial_type_group)
) %>%
filter(event_clearance_group %in% 'DISTURBANCES') %>%
group_by(initial_type_group, district_sector) %>%
summarise(n = n()) %>%
filter(
(initial_type_group %in% 'Unknown' & district_sector %in% 'W') |
(initial_type_group %in% 'ROAD RAGE' & district_sector %in% 'L')
)
```
#### 2.1.7.2 {base}
##### tapply()
```{r}
tapply(InsectSprays$count,InsectSprays$spray,sum)
```
sum up the counts, break down by spray
##### Another way - split()+apply()+unlist()
```{r}
spIns = split(InsectSprays$count,InsectSprays$spray)
```
`split()` returns a list
```{r}
sprCount = lapply(spIns,sum)
unlist(sprCount)
```
##### Another way - split()+sapply()
```{r}
sapply(spIns,sum)
```
#### 2.1.7.3 {plyr}
##### ddply()
```{r}
library(plyr)
ddply(InsectSprays,.(spray),summarize,sum=sum(count))
```
`.()` are the variables to summarize
```{r}
spraySums <- ddply(InsectSprays,.(spray),summarize,sum=ave(count,FUN=sum))
```
##### Further resources
- A [tutorial](http://plyr.had.co.nz/09-user/) from the developer of plyr
- [A good plyr primer](https://www.r-bloggers.com/a-quick-primer-on-split-apply-combine-problems/)
### 2.1.8 Merge the data
#### 2.1.8.1 {dplyr} or {tidyverse}
##### Mutating Joins
Join matching rows from b to a.
```{r}
left_join(a, b, by = "x1")
```
Join matching rows from a to b.
```{r}
right_join(a, b, by = "x1")
```
Join data. Retain only rows in both sets.
```{r}
inner_join(a, b, by = "x1")
```
Join data. Retain all values, all rows.
```{r}
full_join(a, b, by = "x1")
```
##### Filtering Joins
All rows in a that have a match in b.
```{r}
semi_join(a, b, by = "x1")
```
All rows in a that do not have a match in b
```{r}
anti_join(a, b, by = "x1")
```
##### Set Operations
Rows that appear in both y and z.
```{r}
intersect(y, z)
```
Rows that appear in either or both y and z.
```{r}
union(y, z)
```
Rows that appear in y but not z.
```{r}
setdiff(y, z)
```
#### 2.1.8.2 {base}
##### merge()
```{r}
mergedData = merge(reviews,solutions,by.x="solution_id",by.y="id",all=TRUE)
```
`all=TRUE` full join
#### 2.1.8.3 Further resourece
- [The quick R data merging page](https://www.statmethods.net/management/merging.html)
## 2.2 Data types
### 2.2.1 Strings
#### 2.2.1.1 Transformation of upper/lower case
##### {base}
```{r}
tolower(names(cameraData))
toupper()
```
##### {stringr}
```{r}
str_to_lower()
str_to_upper()
str_to_upper(c("i", "ı"), locale = "tr")
str_to_title()
```
pick the set of rules to use by specifying a `locale`
#### 2.2.1.2 Sort and order
##### {stringr}
```{r}
x <- c("apple", "eggplant", "banana")
str_sort(x, locale = "en") # English
#> [1] "apple" "banana" "eggplant"
str_sort(x, locale = "haw") # Hawaiian
#> [1] "apple" "eggplant" "banana"
```
#### 2.2.1.3 Split
##### {base}
###### strsplit()+sapply()
```{r}
splitNames = strsplit(names(cameraData),"\\.")
```
`split="\\."` character vector containing regular expression(s) to use for splitting.
- strsplit() returns a list
- use escape character '\' because the period is a reserved character
Then extract the first element of splited names:
```{r}
splitNames[[6]][1]
firstElement <- function(x){x[1]}
sapply(splitNames,firstElement)
```
##### {stringr}
###### str_split()
```{r}
# split sentences into words
sentences %>%
head(5) %>%
str_split(" ")
```
Because each component might contain a different number of pieces, this returns a list. If you're working with a length-1 vector, the easiest thing is to just extract the first element of the list:
```{r}
"a|b|c|d" %>%
str_split("\\|") %>%
.[[1]]
```
use `simplify = TRUE` to return a matrix
```{r}
sentences %>%
head(5) %>%
str_split(" ", simplify = TRUE)
```
request a maximum number of pieces
```{r}
fields <- c("Name: Hadley", "Country: NZ", "Age: 35")
fields %>% str_split(": ", n = 2, simplify = TRUE)
```
Instead of splitting up strings by patterns, you can also split up by character, line, sentence and word `boundary()`s:
```{r}
x <- "This is a sentence. This is another sentence."
str_view_all(x, boundary("word"))
str_split(x, " ")[[1]]
str_split(x, boundary("word"))[[1]]
```
#### 2.2.1.4 Substitute
##### {base}
###### sub() and gsub()
```{r}
testName <- "this_is_a_test"
# sub only the first one
sub("_","",testName)
# sub all
gsub("_","",testName)
```
#### 2.2.1.5 Number of characters
##### {base}
```{r}
nchar("Jeffrey Leek")
```
##### {stringr}
```{r}
str_length(c("a", "R for data science", NA))
```
#### 2.2.1.6 Extract parts of the string / subset strings
##### {base}
```{r}
substr("Jeffrey Leek",1,7) # 1st to 7th characters
```
##### {stringr}
###### str_sub()
```{r}
x <- c("Apple", "Banana", "Pear")
str_sub(x, 1, 3)
# negative numbers count backwards from end
str_sub(x, -3, -1)
```
takes `start` and `end` arguments which give the (inclusive) position of the substring
```{r}
str_sub("a", 1, 5)
```
won't fail if the string is too short: it will just return as much as possible
```{r}
str_sub(x, 1, 1) <- str_to_lower(str_sub(x, 1, 1))
x
```
can also use the assignment form to modify strings
#### 2.2.1.7 Combine strings
##### {base}
```{r}
# paste to get a string spereated with a space
paste("Jeffrey","Leek")
# paste to get a string without space
paste0("Jeffrey","Leek")
```
##### {stringr}
###### str_c()
```{r}
str_c("x", "y")
str_c("x", "y", sep = ", ")
str_c("prefix-", c("a", "b", "c"), "-suffix")
#> [1] "prefix-a-suffix" "prefix-b-suffix" "prefix-c-suffix"
```
Use the `sep` argument to control how they're separated
```{r}
str_c(c("x", "y", "z"), collapse = ", ")
#> [1] "x, y, z"
```
To collapse a vector of strings into a single string, use `collapse`
```{r}
name <- "Hadley"
time_of_day <- "morning"
birthday <- FALSE
str_c(
"Good ", time_of_day, " ", name,
if (birthday) " and HAPPY BIRTHDAY",
"."
)
```
Objects of length 0 are silently dropped. This is particularly useful in conjunction with `if`
#### 2.2.1.8 Trim whitespace
##### {stringr}
```{r}
# removes whitespace from start and end of string
str_trim("Jeff ")
```
#### 2.2.1.9 Regular expression
##### 2.2.1.9.1 Basic matches
`.` is used to refer to any character
```{r}
9.11
```
##### 2.2.1.9.2 Anchors
`^` represents the start of a line
```{r}
^i think
```
`$` represents the end of a line
```{r}
morning$
```
You can also match the boundary between words with `\b`. You can search for `\bsum\b` to avoid matching `summarise`, `summary`, `rowsum` and so on.
##### 2.2.1.9.3 Character Classes with [] and alternatives
- `\d`: matches any digit.
- `\s`: matches any whitespace (e.g. space, tab, newline).
- `[abc]`: matches a, b, or c.
- `[^abc]`: matches anything except a, b, or c.
A character class containing a single character is a nice alternative to backslash escapes when you want to include a single metacharacter in a regex. Many people find this more readable.
```{r}
# Look for a literal character that normally has special meaning in a regex
str_view(c("abc", "a.c", "a*c", "a c"), "a[.]c")
str_view(c("abc", "a.c", "a*c", "a c"), ".[*]c")
str_view(c("abc", "a.c", "a*c", "a c"), "a[ ]")
```
This works for most (but not all) regex metacharacters: `$` `.` `|` `?` `*` `+` `(` `)` `[` `{`. Unfortunately, a few characters have special meaning even inside a character class and must be handled with backslash escapes: `]` `\` `^` and `-`.
`|` "or"
```{r}
flood|fire
flood|earthquake|hurricane|coldfire
str_view(c("grey", "gray"), "gr(e|a)y")
```
list a set of characters we will accept at a given point in the match
```{r}
[Bb][Uu][Ss][Hh]
^[Ii] am
^[0-9][a-zA-Z]
^[Gg]ood|[Bb]ad
^([Gg]ood|[Bb]ad)
```
When used at the beginning of a character class, the "^" is also a metacharacter and indicates matching characters NOT in the indicated class
```{r}
[^?.]$
```
##### 2.2.1.9.4 Repetition
- `?`: 0 or 1
- `+`: 1 or more
- `*`: 0 or more
```{r}
x <- "1888 is the longest year in Roman numerals: MDCCCLXXXVIII"
str_view(x, "CC?")
str_view(x, "CC+")
str_view(x, 'C[LX]+')
```
```{r}
[Gg]eorge( [Ww]\.)? [Bb]ush
```
```{r}
(.*)
[0-9]+ (.*)[0-9]+
```
`{}` are referred to as interval quantifiers which specify the minimum and maximum number of matches of an expression
```{r}
[Bb]ush ([^ ]+ +){1,5}debate
```
specify the number of matches precisely:
- `{n}`: exactly n
- `{n,}`: n or more
- `{,m}`: at most m
- `{n,m}`: between n and m
```{r}
str_view(x, "C{2}")
str_view(x, "C{2,}")
str_view(x, "C{2,3}")
```
The greediness of `*` (longest possible match) can be turned off with the `?`
```{r}
^s(.*?)s$
str_view(x, 'C{2,3}?')
str_view(x, 'C[LX]+?')
```
##### 2.2.1.9.5 Grouping and backreferences
`()` not only limits the scope of alternatives divided by a `|`, but also can be used to "remember" text matched by the subexpression enclosed; We refer to the matched text with `\1`, `\2`, etc.
```{r}