Repository navigation
Expand file tree
/
Copy pathModule_4.Rmd
More file actions
1689 lines (1107 loc) · 70.5 KB
/
Copy pathModule_4.Rmd
File metadata and controls
1689 lines (1107 loc) · 70.5 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: "Module 4"
description: |
This module is an introduction to data wrangling and the tidyverse.
output:
distill::distill_article:
#code_folding: true
toc: true
toc_depth: 3
---
```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = FALSE)
```
This module is based in part on a [PWC case study](https://www.pwc.com/us/en/careers/university-relations/data-and-analytics-case-studies.html) (Graduate level Data Management and Analysis Course), [R for Data Science](https://r4ds.had.co.nz/index.html), and [ModernDive](https://moderndive.com/index.html). I am changing a few things from the PWC case study, most notably is the use of the Tidyverse R packages. I will introduce dplyr for data manipulation and ggplot2 for visualizations. I am also dropping the AI/Predictive modeling portion for this class. My masters level course will focus on AI/ML.
# Module Objectives
- Explain fundamentals of statistical programming with R
- Demonstrate basic approaches to acquire, transform, analyze, and visualize data in R
# Statistical programming tools
{width="100%"}
# R programming basics
Lets create a new script to store our code. If we want to keep our code, then we must use a script. Also remember that `#` allows you to make comments that are not run as code. The key shortcut is:
ctrl (cmd)+shift+N
{width="100%"}
You can use R as a calculator:
```{r, echo=TRUE}
1 / 200 * 30
(59 + 73 + 2) / 3
sin(pi / 2)
```
You can create new objects with `<-`:
```{r, echo=TRUE}
x <- 3 * 4
x
```
All R statements where you create objects, **assignment** statements, have the same form:
```{r , echo=TRUE, eval = FALSE}
object_name <- value
```
When reading that code say "object name gets value" in your head.
You will make lots of assignments and `<-` is a pain to type. Don't be lazy and use `=`: it will work, but it will cause confusion later.
## What's in a name?
R is case-sensitive, so objects and functions must be used with correct capitalization. Object names must start with a letter, and can only contain letters, numbers, `_` and `.`. You want your object names to be descriptive, so you'll need a convention for multiple words. I recommend **snake_case** where you separate lowercase words with `_`.
```{r, eval = FALSE, echo=TRUE}
i_use_snake_case
otherPeopleUseCamelCase
some.people.use.periods
And_aFew.People_RENOUNCEconvention
```
```{r, echo=TRUE,error=TRUE}
x_value <- 3 * 4
X_value
```
```{r, echo=TRUE,error=TRUE}
x_value <- 3 * 4
x_Value
```
## Calling functions
R has a large collection of built-in functions that are called like this:
```{r , eval = FALSE}
function_name(arg1 = val1, arg2 = val2, ...)
```
Let's try using `seq()` which makes regular **seq**uences of numbers and, while we're at it, learn more helpful features of RStudio.
```{r, echo=TRUE}
seq(1, 10)
```
Type this code and notice you get similar assistance with the paired quotation marks:
```{r, echo=TRUE}
x <- "hello world"
```
Quotation marks and parentheses must always come in a pair. RStudio does its best to help you, but it's still possible to mess up and end up with a mismatch. If this happens, R will show you the continuation character "+":
> x <- "hello
+
The `+` tells you that R is waiting for more input; it doesn't think you're done yet. Usually that means you've forgotten either a `"` or a `)`. Either add the missing pair, or press ESCAPE to abort the expression and try again.
If you make an assignment, you don't get to see the value. You're then tempted to immediately double-check the result:
```{r, echo=TRUE}
y <- seq(1, 10, length.out = 5)
y
```
This common action can be shortened by surrounding the assignment with parentheses, which causes assignment and "print to screen" to happen.
```{r, echo=TRUE}
(y <- seq(1, 10, length.out = 5))
```
Now look at your environment in the upper right pane. What is there?
The objects we created.
Interested in keyboard shortcuts... try: Alt+Shift+K or use the menus 😄
## Prerequisites
In this module we are going to focus on how to use the dplyr package. If you have not already you need to install the tidyverse of packages. Installing packages is like downloading an app for your phone that adds functionality. After you download an app you have to tap it to use..in R to *tap* the package you use `library(package_name)`.
```{r setup2, echo=TRUE,eval=FALSE}
install.packages("tidyverse")
```
```{r load libraries, echo=TRUE,warning=FALSE}
library(tidyverse)
```
We can use the data from Module 1.
Remember dbname="**LOCATION OF YOUR DATABASE**"
```{r, echo=TRUE}
library(DBI)
db = dbConnect(RSQLite::SQLite(), dbname = "data/03-Data-file-classroom-exercise-Chinook_Sqlite.sqlite")
```
Lets move the tables into R
```{r, echo=TRUE}
Customer<-dbReadTable(db,"Customer")
```
```{r, echo=TRUE}
Invoice<-dbReadTable(db,"Invoice")
```
You can also accomplish the same task in a RMarkdown document using SQL with the following code:
```` r
```{sql, connection=db, echo=TRUE, output.var="Customer"}`r ''`
SELECT *
FROM Customer
```
```{sql, connection=db, echo=TRUE, output.var="Invoice"}`r ''`
SELECT *
FROM Invoice
```
````
{width="100%"}
[Resource for working with databases in R.](https://www.r-bloggers.com/2019/08/a-comprehensive-introduction-to-working-with-databases-using-r/)
Lets also import our data used in Module 2. I almost always use the import wizard.
{width="100%"}
This is the code that the wizard produces for us:
```{r import1, echo=TRUE, warning=TRUE}
#don't forget libraries
library(readr)
Session_2_data_file_1_payroll_data <- read_delim("data/Session-2-data-file-1-payroll_data.txt", "|", escape_double = FALSE, trim_ws = TRUE)
library(readxl)
Session_2_data_file_2_reference_data <- read_excel("data/Session-2-data-file-2-reference-data.xlsx")
```
**Note: Whenever we run tidyverse library it loads several packages**
## Basic Syntax
Lets start with storing our imported data from Module 2 in names we like. This could have been done in the import wizard, but lets do it now.
```{r, echo=TRUE}
Payroll<-Session_2_data_file_1_payroll_data
Reference<-Session_2_data_file_2_reference_data
```
We can remove the old datasets from our environment by using `rm()`
```{r, echo=TRUE}
rm(Session_2_data_file_1_payroll_data)
rm(Session_2_data_file_2_reference_data)
```
At this point you should have a database connection "db" in your environment and 4 datasets: Customer, Invoice, Payroll, and Reference. These are the datasets used in Module 1 and Module 2. Be sure and import the original data because we are going to learn how to fix the same errors that we fixed in Module 2.
{width="50%"}
We are going to be using the dplyr package for data manipulation or data wrangling.
<aside>
dplyr package is loaded whenever you run library(tidyverse)
</aside>
## Operators
### Arithmetic Operators
| **Operator** | **Description** |
|:-------------|:----------------------------|
| `+` | addition |
| `-` | subtraction |
| `*` | multiplication |
| `/` | division |
| \^ or \*\* | exponentiation |
| x%%y | modulus (x mod y) 5%%2 is 1 |
| x%/%y | integer division 5%/%2 is 2 |
### Logical Operators {#logical-operators}
| **Operator** | **Description** |
|:-------------|:-------------------------|
| \< | less than |
| \<= | less than or equal to |
| \> | greater than |
| \>= | greater than or equal to |
| == | exactly equal to |
| != | not equal to |
| !x | Not x |
| x\|y | x OR y |
| x&y | x AND y |
| isTRUE(x) | test if x is TRUE |
## Built-in Functions
- R has many built in functions that compute different statistical procedures.
- Functions in R are followed by ( ).
- Inside the parenthesis we write the object (vector, array, matrix, dataframe) to which we want to apply the function.
### Numeric Functions
| **Function** | **Description** |
|:-----------------------|:--------------------------------------|
| abs(x) | absolute value |
| sqrt(x) | square root |
| ceiling(x) | ceiling(3.475) is 4 |
| floor(x) | floor(3.475) is 3 |
| trunc(x) | trunc(5.99) is 5 |
| 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), tan(x) | also acos(x), cosh(x), acosh(x), etc. |
| log(x) | natural logarithm |
| log10(x) | common logarithm |
| exp(x) | e\^x |
### Character Functions
+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| **Function** | **Description** |
+:=============================================================+:======================================================================================================================================================================================================+
| substr(x, start=n1, stop=n2) | Extract or replace substrings in a character vector. x \<- "abcdef", substr(x, 2, 4) is "bcd" |
+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| grep(pattern, x, ignore.case=FALSE, fixed=FALSE) | Search for pattern in x. If fixed =FALSE then pattern is a regular expression. If fixed=TRUE then pattern is a text string. Returns matching indices. grep("A", c("b","A","c"), fixed=TRUE) returns 2 |
+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sub(pattern, replacement, x, ignore.case=FALSE, fixed=FALSE) | Find pattern in x and replace with replacement text. If fixed=FALSE then pattern is a regular expression. If fixed = T then pattern is a text string. sub("",".","Hello There") returns "Hello.There" |
+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| strsplit(x, split) | Split the elements of character vector x at split. strsplit("abc", "") returns 3 element vector "a","b","c" |
+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| paste(..., sep="") | Concatenate strings after using sep string to seperate them. paste("x",1:3,sep="") returns c("x1","x2" "x3") paste("x",1:3,sep="M") returns c("xM1","xM2" "xM3") paste("Today is", date()) |
+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| toupper(x) | Uppercase |
+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tolower(x) | Lowercase |
+--------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
### Statistical Functions
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| **Function** | **Description** |
+:======================================================================================+:===================================================================================================================================================================================================================================================+
| dnorm(x) | normal density function (by default m=0 sd=1) \# plot standard normal curve x \<- pretty(c(-3,3), 30) y \<- dnorm(x) plot(x, y, type="l", xlab="Normal Deviate", ylab="Density", yaxs="i") |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pnorm(q) | cumulative normal probability for q (area under the normal curve to the right of q) pnorm(1.96) is 0.975 |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| qnorm(p) | normal quantile. value at the p percentile of normal distribution qnorm(.9) is 1.28 \# 90th percentile |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rnorm(n, m=0, sd=1) | n random normal deviates with mean m and standard deviation sd. #50 random normal variates with mean=50, sd=10x \<- rnorm(50, m=50, sd=10) |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dbinom(x, size, prob), pbinom(p, size,prob), qbinom(q,size,prob), rbinom(n,size,prob) | binomial distribution where size is the sample size and prob is the probability of a heads (pi) \# prob of 0 to 5 heads of fair coin out of 10 flips dbinom(0:5, 10, .5) \# prob of 5 or less heads of fair coin out of 10 flips pbinom(5, 10, .5) |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dpois(x, lamda), ppois(q,lamda), qpois(p,lamda), rpois(n,lamda) | poisson distribution with m=std=lamda #probability of 0,1, or 2 events with lamda=4 dpois(0:2, 4) \# probability of at least 3 events with lamda=4 1- ppois(2,4) |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dunif(x,min,max=1) | uniform distribution, follows the same pattern |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| punif(q,min=0,max=1) | as the normal distribution above. |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| qunif(p,min=0,max=1) | #10 uniform random variates |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| runif(n,min=0,max=1) | x \<- runif(10) |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mean(x,trim=0, na.rm=FALSE) | mean of object x, \# trimmed mean, removing any missing values and \# 5 percent of highest and lowest scores mx \<- mean(x,trim=.05,na.rm=TRUE) |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sd(x) | standard deviation of object(x). also look at var(x) for variance and mad(x) for median absolute deviation. |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| median(x) | median |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| quantile(x) | quantiles where x is the numeric vector whose quantiles are desired and probs is a numeric vector with probabilities in \[0,1\]. \# 30th and 84th percentiles of x, y \<- quantile(x, c(.3,.84)) |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| range(x) | range |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sum(x) | sum |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| diff(x,lag=1) | lagged differences, with lag indicating which lag to use |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| min(x) | minimum |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| max(x) | maximum |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| scale(x, center=TRUE, scale=TRUE) | column center or standardize a matrix |
+---------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
### Other Functions {#other-functions}
+----------------------------+--------------------------------------------------------------------------+
| **Function** | **Description** |
+:===========================+:=========================================================================+
| seq(from, to, by) | generate a sequence indices \<- seq(1,10,2) #indices is c(1, 3, 5, 7, 9) |
+----------------------------+--------------------------------------------------------------------------+
| rep(x,ntimes) | repeat x n times y \<- rep(1:3, 2) \# y is c(1, 2, 3, 1, 2, 3) |
+----------------------------+--------------------------------------------------------------------------+
| cut(x,n) | divide continuous variable in factor with n levels y \<- cut(x, 5) |
+----------------------------+--------------------------------------------------------------------------+
| length(object) | number of elements or components |
+----------------------------+--------------------------------------------------------------------------+
| str(object) | structure of an object |
+----------------------------+--------------------------------------------------------------------------+
| class(object) | class or type of an object |
+----------------------------+--------------------------------------------------------------------------+
| names(object) | names |
+----------------------------+--------------------------------------------------------------------------+
| c(object, object,...) | combine objects into a vector |
+----------------------------+--------------------------------------------------------------------------+
| cbind(object, object,...) | combine objects as columns |
+----------------------------+--------------------------------------------------------------------------+
| rbind(object, object,...) | combine objects as rows |
+----------------------------+--------------------------------------------------------------------------+
| ls() | list current objects |
+----------------------------+--------------------------------------------------------------------------+
| rm(object) | delete an object |
+----------------------------+--------------------------------------------------------------------------+
| newobject \<- edit(object) | create a new object |
+----------------------------+--------------------------------------------------------------------------+
| fix(object) | edit an object in place |
+----------------------------+--------------------------------------------------------------------------+
# [Dplyr](https://dplyr.tidyverse.org/)
### Pipes %>%
Before we start data wrangling, let's first introduce a tool that gets loaded with the `dplyr` package: the pipe operator `%>%`. The pipe operator allows us to combine multiple operations in R into a single sequential *chain* of actions.
Let's start with a hypothetical example. Say you would like to perform a hypothetical sequence of operations on a hypothetical data frame `x` using hypothetical functions `f()`, `g()`, and `h()`:
1. Take `x` *then*
2. Use `x` as an input to a function `f()` *then*
3. Use the output of `f(x)` as an input to a function `g()` *then*
4. Use the output of `g(f(x))` as an input to a function `h()`
One way to achieve this sequence of operations is by using nesting parentheses as follows...This is how **Excel** works. We saw this multiple times in Module 2.
h(g(f(x)))
This isn't terrible, but as we saw...as you nest more things it gets more complicated. This is where the pipe operator `%>%` comes in handy. `%>%` takes the output of one function and then "pipes" it to be the input of the next function. Furthermore, a helpful trick is to read `%>%` as "then" or "and then." For example, you can obtain the same output as the hypothetical sequence of functions as follows:
x %>%
f() %>%
g() %>%
h()
You would read this sequence as:
1. Take `x` *then*
2. Use this output as the input to the next function `f()` *then*
3. Use this output as the input to the next function `g()` *then*
4. Use this output as the input to the next function `h()`
So while both approaches achieve the same goal, the latter is much more human-readable because you can clearly read the sequence of operations line-by-line.
```{r filter1, echo=TRUE}
#lets look at some pipes
USA_Customer <- Customer %>%
filter(Country=="USA")
```
<details>
<summary>
Take a Peek
</summary>
<p>
```{r}
knitr::kable(USA_Customer,caption = "The resulting data")%>%
kableExtra::kable_styling("striped")%>%
kableExtra::scroll_box(width = "100%",height="300px")
```
</p>
</details>
## Functions
functions include:
1. Pick variables by their names (`select()`).
2. `filter()` a data frame's existing rows to only pick out a subset of them.
3. `summarize()` one or more of its columns/variables with a *summary statistic*.
4. `group_by()` its rows. In other words, assign different rows to be part of the same *group*. We can then combine `group_by()` with `summarize()` to report summary statistics for each group *separately*. For example, say you don't want a single overall average sales for a company, but want it by department.
5. `mutate()` its existing columns/variables to create new ones. For example, you can create ROA from income divided by assets.
6. `arrange()` its rows. Sorting rows.
7. `join()` it with another data frame by matching along a "key" variable. In other words, merge these two data frames together. This is similar to what we saw in Module 1.
### filter
The `filter()` function here works much like the "Filter" option in Microsoft Excel; it allows you to specify criteria about the values of a variable in your dataset and then filters out only the rows that match that criteria.
*Note: not including the select function is similar to including SELECT \* in SQL. We select everything.*
```{r filter2, echo=TRUE, eval=FALSE}
#lets look at some pipes
USA_Customer <- Customer %>%
filter(Country=="USA")
View(USA_Customer)
```
We can use any of the [logical operators](#logical-operators) in the above table.
```{r filter3, echo=TRUE,eval=FALSE}
Customer_USA_2_Cities <- Customer %>%
filter(Country == "USA" &
(City == "Reno" | City == "Mountain View" | City== "New York") &
SupportRepId >= 4)
View(Customer_USA_2_Cities)
```
Why do we only end up with two cities?
For any row in the data `City` can be `"Reno"` OR `"Mountain View"` OR `"New York"`, but not all three simultaneously...our data does not stack more than one city on a row. The `Country` must be `"USA"` and the `SupportRepid` must be greater than or equal to 4.
You can use the "not" operator `!`. Below we can `filter` the customer data dropping USA and Italy.
```{r filter4, echo=TRUE,eval=FALSE}
Customer_NOT_USA_Italy <- Customer %>%
filter(!(Country == "USA" | Country == "Italy"))
View(Customer_NOT_USA_Italy)
```
Again, note the careful use of parentheses around the `(Country == "USA" | Country == "Italy")`.
What would happen if the parentheses were not present?
```{r filter5, echo=TRUE,eval=FALSE}
Customer %>%
filter(!Country == "USA" | Country == "Italy")
```
Can anyone explain the results?
<details>
<summary>
Take a Peek
</summary>
<p>
```{r}
knitr::kable(Customer %>%
filter(!Country == "USA" | Country == "Italy"))%>%
kableExtra::kable_styling("striped")%>%
kableExtra::scroll_box(width = "100%",height="300px")
```
</p>
</details>
As we progressively include more countries, this will get unwieldy to write. A slightly shorter approach uses the `%in%` operator along with the [`c()` function](#other-functions). The `c()` function "combines" or "concatenates" values into a single *vector* of values.
```{r filter6, echo=TRUE,eval=FALSE}
many_countries<-Customer%>%
filter(Country %in% c("USA","Italy","Brazil","Canada"))
View(many_countries)
```
<details>
<summary>
Did it work?
</summary>
<p>
```{r}
knitr::kable(Customer %>%
filter(Country %in% c("USA","Italy","Brazil","Canada")))%>%
kableExtra::kable_styling("striped")%>%
kableExtra::scroll_box(width = "100%",height="300px")
```
</p>
</details>
# Exercise #1 {#exercise-1}
In an Rmd file answer the following questions. Include the **question**, **code**, and the **results**. I provide the results, but you have to come up with the code. Turn in the Rmd and a knit html of your work. *note: in order to keep the output of tables to a minimum use the function* `head(table_name, 5)` *to include the first 5 results in the table.*
<aside>
I do not have the same limitation with only using the top 5 results for my answers because my answers are in html
</aside>
1. Using the customer table, if you select the first 5 rows what is the country in the last row?
<details>
<summary>
**Results**
</summary>
<p>
```{r Exercise.4.1.1}
knitr::kable(head(Customer,5))%>%
kableExtra::kable_styling("striped")%>%
kableExtra::scroll_box(width = "100%",height="300px")
```
</p>
</details>
2. Filter the Invoice data set using the `BillingCountry` column. Your results should only have 3 countries of your choice.
<details>
<summary>
**Results using USA, Canada, and Brazil...you can choose your own**
</summary>
<p>
```{r Exercise.4.1.2}
knitr::kable(Invoice%>%
filter(BillingCountry %in% c("USA","Brazil","Canada")))%>%
kableExtra::kable_styling("striped")%>%
kableExtra::scroll_box(width = "100%",height="300px")
```
</p>
</details>
3. Filter the Invoice data set such that the results do NOT contain 2 countries of your choice.
<details>
<summary>
**Results not choosing USA and Canada...you can not choose your own 😄**
</summary>
<p>
```{r Exercise.4.1.3}
knitr::kable(Invoice%>%
filter(BillingCountry!="USA" & BillingCountry!="Canada"))%>%
kableExtra::kable_styling("striped")%>%
kableExtra::scroll_box(width = "100%",height="300px")
```
</p>
</details>
4. Filter the Invoice data set such that the results only have invoices with totals over 10.00.
<details>
<summary>
**Results**
</summary>
<p>
```{r Exercise.4.1.4}
knitr::kable(Invoice%>%
filter(Total>=10))%>%
kableExtra::kable_styling("striped")%>%
kableExtra::scroll_box(width = "100%",height="300px")
```
</p>
</details>
### summarize
Summary statistics are single numerical values that summarize a large number of values. Commonly known examples of summary statistics include the mean (also called the average) and the median (the middle value). Other examples of summary statistics that might not immediately come to mind include the *sum*, the smallest value also called the *minimum*, the largest value also called the *maximum*, and the *standard deviation*.
Within summarize here are a few examples of functions that can be used:
- `mean()`: the average
- `sd()`: the standard deviation, which is a measure of spread
- `min()` and `max()`: the minimum and maximum values, respectively
- `IQR()`: interquartile range
- `sum()`: the total amount when adding multiple numbers
- `n()`: a count of the number of rows
Lets look at a few examples
```{r summarize1,echo=TRUE}
summary_invoice<-Invoice%>%
summarize(mean_total=mean(Total,na.rm=TRUE))
summary_invoice
```
What does the `na.rm=TRUE` do?
Before we investigate that we need to clean up one of our variables in the Payroll data set.
```{r, echo=TRUE}
class(Payroll$`Overtime Pay`)
```
We have a couple issues here:
1. Overtime Pay does not conform to a good naming convention. Clearly R can deal with the space, but it is likely to make things more difficult later.
2. Overtime Pay is a character, but we know it should be numeric
- Another issue is the comma and if we were using the salary column the \$ would be a problem.
Let's change the name with the rename function:
```{r, echo=TRUE}
Payroll<-Payroll%>%
#syntax: new_name=old_name
rename(Overtime_Pay=`Overtime Pay`)
summary(Payroll$Overtime_Pay)
```
Before we can convert the character to a number we need to remove the commas... 😢 and then use `as.numeric` . When we convert to 'numeric' with `as.numeric`, all the non-numeric elements are converted to `NA`. So, we need to remove `,` to make it work.
More on mutate later
```{r, echo=TRUE}
#?gsub
#?as.numeric
Payroll2<-Payroll%>%
#nested formula: gsub in as.numeric
mutate(Overtime_Pay=as.numeric(gsub('[,]', '',Overtime_Pay)))
summary(Payroll2$Overtime_Pay)
```
Another question arises: Are the `-` dashes zero and the observations that have nothing actually missing? If the `-` dash represents zero we need to address that:
```{r, echo=TRUE}
Payroll3<-Payroll%>%
mutate(Overtime_Pay=gsub('[,]', '',Overtime_Pay),
Overtime_Pay=gsub('[-]', '0',Overtime_Pay),
Overtime_Pay=as.numeric(Overtime_Pay))
summary(Payroll3$Overtime_Pay)
Payroll4<-Payroll%>%
mutate(Overtime_Pay=gsub('[,]', '',Overtime_Pay))%>%
mutate(Overtime_Pay=gsub('[-]', '0',Overtime_Pay))%>%
mutate(Overtime_Pay=as.numeric(Overtime_Pay))
summary(Payroll4$Overtime_Pay)
```
Now can we find the mean Overtime_Pay?
```{r summarize2, echo=TRUE,warning=TRUE}
summary_payroll<-Payroll4%>%
summarise(mean_overtimepay=mean(Overtime_Pay,na.rm=FALSE))
summary_payroll
```
<details>
<summary>
The fix 🔧
</summary>
<p>
```{r summarize3, echo=TRUE,warning=TRUE}
summary_payroll<-Payroll4%>%
summarise(mean_overtimepay=mean(Overtime_Pay,na.rm=TRUE))
summary_payroll
```
</p>
</details>
Does the following code work?
```{r, echo=TRUE,eval=FALSE}
summary_payroll<-Payroll4%>%
summarise(mean_overtimepay=mean(Overtime_Pay,na.rm=TRUE))%>%
summarise(std_overtimepay=sd(Overtime_Pay,na.rm=TRUE))
summary_payroll
```
Why?
### group_by
Say instead of a single mean of Overtime_Pay for the company, you would like mean overtime pay for each department. In other words, we would like to compute the mean overtime pay split by department. We can do this by "grouping" Overtime_Pay observations by the values of another variable, in this case by the values of the variable `Dept`. Run the following code:
```{r summarize4, echo=TRUE}
summary_dept_payroll<-Payroll4%>%
group_by(Dept)%>%
summarise(mean_overtimepay=mean(Overtime_Pay,na.rm=TRUE))
#the code below makes the table look nice in rmarkdown
#it uses the knitr package and the kableExtra package
#If you are just copying code to your RStudio then Just use:
# summary_dept_payroll
knitr::kable(summary_dept_payroll)%>%
kableExtra::kable_styling("striped")%>%
kableExtra::scroll_box(width = "100%",height="300px")
```
<aside>
I include the kable/kableExtra code here to show you how flexible RMarkdown can be, but it is not necessary. If it confuses you, then ignore it.
</aside>
It is important to note that the `group_by()` function doesn't change data frames by itself. Rather it changes the *meta-data*, or data about the data, specifically the grouping structure. It is only after we apply the `summarize()` function that the data frame changes.
#### Using summarize to make more than one variable
```{r summarize5,echo=TRUE}
summary_dept_payroll<-Payroll4%>%
group_by(Dept)%>%
summarise(mean_overtimepay=mean(Overtime_Pay,na.rm=TRUE),
numberofemployees=n())
summary_dept_payroll
library(knitr)
kable(summary_dept_payroll)
```
#### Group by more than one variable
```{r summarize6,echo=TRUE}
summary_dept_st_payroll<-Payroll4%>%
group_by(Dept,State)%>%
summarise(mean_overtimepay=mean(Overtime_Pay,na.rm=TRUE),
numberofemployees=n())
```
```{r}
knitr::kable(summary_dept_st_payroll)%>%
kableExtra::kable_styling("striped")%>%
kableExtra::scroll_box(width = "100%",height="300px")
```
Will the following work?
```{r summarize7,echo=TRUE,eval=FALSE}
summary_dept_st_payroll<-Payroll4%>%
group_by(Dept)%>%
group_by(State)%>%
summarise(mean_overtimepay=mean(Overtime_Pay,na.rm=TRUE),
numberofemployees=n())
```
Why?
# Exercise #2
Include the **question**, **code**, and the **results**. I provide the results, but you have to come up with the code. Turn in the Rmd and a knit html of your work.
1. What is the mean invoice total for the USA?
<details>
<summary>
Result 🥴
</summary>
<p>
```{r}
knitr::kable(Invoice%>%
filter(BillingCountry=="USA")%>%
summarise(mean_total=mean(Total,na.rm=TRUE)))
```
</p>
</details>
2. What is the mean invoice total for the USA by State?
<details>
<summary>
Result 😉
</summary>
<p>
```{r}
knitr::kable(Invoice%>%
filter(BillingCountry=="USA")%>%
group_by(BillingState)%>%
summarise(mean_total=mean(Total,na.rm=TRUE)))
```
</p>
</details>
3. What is the mean invoice total for the USA by State and City?
<details>
<summary>
Result 😜
</summary>
<p>
```{r}
knitr::kable(Invoice%>%
filter(BillingCountry=="USA")%>%
group_by(BillingState,BillingCity)%>%
summarise(mean_total=mean(Total,na.rm=TRUE)))
```
</p>
</details>
4. What is the mean invoice total and number of invoices for the USA by State and City?
<details>
<summary>
Result 😨
</summary>
<p>
```{r}
knitr::kable(Invoice%>%
filter(BillingCountry=="USA")%>%
group_by(BillingState,BillingCity)%>%
summarise(mean_total=mean(Total,na.rm=TRUE),
num_invoices=n()))
```
</p>
</details>
### Mutate
Besides selecting sets of existing columns, it's often useful to add new columns that are functions of existing columns. That's the job of `mutate()`.
`mutate()` always adds new columns at the end of your dataset.
First lets download a new dataset. This code allows you to download data directly from a website.
```{r mutate1,echo=TRUE}
#load data.
#curl package lets us download data from a website with the proper location
#check the packages tab and see if you have curl
#try following
#?curl
library(curl)
load(curl("https://raw.githubusercontent.com/Professor-Hunt/ACC3053/main/data/AnnualCompanyData.Rda"))
```
Lets talk about some common financial ratios:
[Current Ratio]{style="color:red;"} measures a company's ability to pay off short-term liabilities with current assets:
```{=tex}
\begin{equation}
\mbox{Current ratio} = \frac{\mbox{Current assets}}{\mbox{Current liabilities}}
\end{equation}
```
```{r CurrentRatio,echo=TRUE}
Current_Ratio<-AnnualCompanyData%>%
select(CompanyName,FiscalYear,CurrentAssets,CurrentLiab)%>%
mutate(Current_Ratio=CurrentAssets/CurrentLiab)
```
```{r}
knitr::kable(Current_Ratio)%>%
kableExtra::kable_styling("striped")%>%
kableExtra::scroll_box(width = "100%",height="300px")
#$\color{red}{\text{Current ratio}}$
```
You give it a shot.
[Acid-test Ratio]{style="color:red;"} measures a company's ability to pay off short-term liabilities with quick assets: