---
title: "Data Manipulation Functions"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Data Manipulation Functions}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r setup, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
```

## Transpose

There are several functions in base R to pivot data.  These include the `t()`
and `reshape()` functions.  There are also well-designed functions in the
**tidyverse** for transposing data.  Nevertheless, some users will prefer
the syntax of `proc_transpose()`.  This function provides control over output
column naming, and an intuitive set of parameters.

To explore `proc_transpose`, let's first create some sample data:
```{r eval=FALSE, echo=TRUE} 
# Create input data
dat <- read.table(header = TRUE, text = '
                  Name    Subject   Semester1  Semester2
                  Samma	  Maths	    96             94
                  Sandy	  English	  76             51
                  Devesh	German	  76             95
                  Rakesh	Maths	    50             63
                  Priya	  English	  62             80
                  Kranti	Maths	    92             92
                  William	German	  87             75')

# View data
dat
#      Name Subject Semester1 Semester2
# 1   Samma   Maths        96        94
# 2   Sandy English        76        51
# 3  Devesh  German        76        95
# 4  Rakesh   Maths        50        63
# 5   Priya English        62        80
# 6  Kranti   Maths        92        92
# 7 William  German        87        75
```
### Default Transpose

The `proc_tranpose()` function may be executed without any parameters.  The 
default usage will tranpose all numeric variables and construct generic 
column names for the new columns:
```{r eval=FALSE, echo=TRUE} 
# No parameters
res <- proc_transpose(dat)

# View result
res
#        NAME COL1 COL2 COL3 COL4 COL5 COL6 COL7
# 1 Semester1   96   76   76   50   62   92   87
# 2 Semester2   94   51   95   63   80   92   75

```
If you didn't want all numeric variables transposed, you could specify which
variable(s) to transpose using the `var` parameter. Multiple variables can be
passed as a vector:
```{r eval=FALSE, echo=TRUE} 
# Var parameter
res <- proc_transpose(dat, var = "Semester1")

# View result
res
#        NAME COL1 COL2 COL3 COL4 COL5 COL6 COL7
# 1 Semester1   96   76   76   50   62   92   87

```

### Prefix and Suffix

You may control the output column names using the `prefix`, 
`suffix`, and `name` parameters.  The `prefix` and `suffix` will be used to
construct the new transposed column names.  The `name` parameter value will
be used as the name of the generic "NAME" column, which identify 
the `var` parameter values:
```{r eval=FALSE, echo=TRUE} 
# With prefix
res <- proc_transpose(dat, name = VarName, prefix = Student)

# View result
res
    VarName Student1 Student2 Student3 Student4 Student5 Student6 Student7
1 Semester1       96       76       76       50       62       92       87
2 Semester2       94       51       95       63       80       92       75
```
Here is the same function call with a `suffix`:
```{r eval=FALSE, echo=TRUE} 
# With suffix
res <- proc_transpose(dat, name = VarName, prefix = S, suffix = Score)

# View result
res
    VarName S1Score S2Score S3Score S4Score S5Score S6Score S7Score
1 Semester1      96      76      76      50      62      92      87
2 Semester2      94      51      95      63      80      92      75
```
Note that since a variable name in R cannot start with a number, some sort
of prefix is required.

### Name Columns From Data

If your data contains a column with appropriate labels for the transposed columns,
you can assign it to the `id` parameter.  The `id` values will then be used for
the new column names.
```{r eval=FALSE, echo=TRUE} 
# Assign column names from data
res <- proc_transpose(dat, name = VarName, id = Name)

# View result
res
    VarName Samma Sandy Devesh Rakesh Priya Kranti William
1 Semester1    96    76     76     50    62     92      87
2 Semester2    94    51     95     63    80     92      75
```

Using two `id` parameters tells the function that you want columns that are
combinations of the two variables:
```{r eval=FALSE, echo=TRUE}
# Two id variables
res <- proc_transpose(dat, id = v(Name, Subject))

res
#        NAME Samma.Maths Sandy.English Devesh.German Rakesh.Maths Priya.English Kranti.Maths William.German
# 1 Semester1          96            76            76           50            62           92             87
# 2 Semester2          94            51            95           63            80           92             75
```
The default delimiter shown above is a dot (".").  The delimiter may be changed
with the `delimiter` parameter:

```{r eval=FALSE, echo=TRUE}
# Underscore delimiter
res <- proc_transpose(dat, id = v(Name, Subject), delimiter = "_")

res
#        NAME Samma_Maths Sandy_English Devesh_German Rakesh_Maths Priya_English Kranti_Maths William_German
# 1 Semester1          96            76            76           50            62           92             87
# 2 Semester2          94            51            95           63            80           92             75
```

### By Groups

The `by` parameter tells the function to group by the `by` variable 
before transposing.  As you can see below, the `by` varible is then retained
on the output dataset so you can identify which rows belong to which group.
```{r eval=FALSE, echo=TRUE} 
# By variable
res <- proc_transpose(dat, by = Name, id = Subject, name = Semester)

# View result
res
#       Name  Semester German English Maths
# 1   Devesh Semester1     76      NA    NA
# 2   Devesh Semester2     95      NA    NA
# 3   Kranti Semester1     NA      NA    92
# 4   Kranti Semester2     NA      NA    92
# 5    Priya Semester1     NA      62    NA
# 6    Priya Semester2     NA      80    NA
# 7   Rakesh Semester1     NA      NA    50
# 8   Rakesh Semester2     NA      NA    63
# 9    Samma Semester1     NA      NA    96
# 10   Samma Semester2     NA      NA    94
# 11   Sandy Semester1     NA      76    NA
# 12   Sandy Semester2     NA      51    NA
# 13 William Semester1     87      NA    NA
# 14 William Semester2     75      NA    NA 
```
The `by` parameter is a valuable feature of `proc_transpose()`.  The by variable
can have a significant effect on the shape of the output data. Let's see
what happens when we use a different by variable.
```{r eval=FALSE, echo=TRUE} 
# By variable
res <- proc_transpose(dat, by = Subject, id = Name)

#   Subject      NAME Sandy Priya Devesh William Samma Rakesh Kranti
# 1 English Semester1    76    62     NA      NA    NA     NA     NA
# 2 English Semester2    51    80     NA      NA    NA     NA     NA
# 3  German Semester1    NA    NA     76      87    NA     NA     NA
# 4  German Semester2    NA    NA     95      75    NA     NA     NA
# 5   Maths Semester1    NA    NA     NA      NA    96     50     92
# 6   Maths Semester2    NA    NA     NA      NA    94     63     92

```

Now let's use two by variables:
```{r eval=FALSE, echo=TRUE} 
# Two by variables
res <- proc_transpose(dat, by = v(Name, Subject))

# View results
res
#       Name Subject      NAME COL1
# 1    Priya English Semester1   62
# 2    Priya English Semester2   80
# 3    Sandy English Semester1   76
# 4    Sandy English Semester2   51
# 5   Devesh  German Semester1   76
# 6   Devesh  German Semester2   95
# 7  William  German Semester1   87
# 8  William  German Semester2   75
# 9   Kranti   Maths Semester1   92
# 10  Kranti   Maths Semester2   92
# 11  Rakesh   Maths Semester1   50
# 12  Rakesh   Maths Semester2   63
# 13   Samma   Maths Semester1   96
# 14   Samma   Maths Semester2   94

```
By transposing one more time on the results of the previous example,
you can restore the original data frame.
```{r eval=FALSE, echo=TRUE} 
# Restore original data shape
res2 <- proc_transpose(res, by = v(Name, Subject), 
                       id = NAME, options = noname)

# View results
res2[ , c("Name", "Subject", "Semester1", "Semester2")]
#      Name Subject Semester1 Semester2
# 1   Priya English        62        80
# 2   Sandy English        76        51
# 3  Devesh  German        76        95
# 4 William  German        87        75
# 5  Kranti   Maths        92        92
# 6  Rakesh   Maths        50        63
# 7   Samma   Maths        96        94

```
## Sort

The `proc_sort()` function in the **procs** package attempts to recreate the most
basic functionality of PROC SORT.  The function accepts an input data frame
and returns the sorted result. Before examining the function, first let's create
some sample data:
```{r eval=FALSE, echo=TRUE} 
# Create sample data
dat <- read.table(header = TRUE, text = '
                    ID	Name	Score
                    1	David	 74
                    2	Sam	   45
                    3	Bane	 87
                    3	Mary	 92
                    4	Dane	 23
                    5	Jenny	 87
                    6	Simran 63
                    8	Priya	 72 
                    1	David	 45
                    2	Ram	   54
                    3	Bane	 87
                    5	Ken	   87')

# View data
dat
#    ID   Name Score
# 1   1  David    74
# 2   2    Sam    45
# 3   3   Bane    87
# 4   3   Mary    92
# 5   4   Dane    23
# 6   5  Jenny    87
# 7   6 Simran    63
# 8   8  Priya    72
# 9   1  David    45
# 10  2    Ram    54
# 11  3   Bane    87
# 12  5    Ken    87
```

Like `proc_transpose()`, the function has a default
usage, which is to sort by all variables:
```{r eval=FALSE, echo=TRUE} 
# Default sort
res <- proc_sort(dat)

# View results
res
#    ID   Name Score
# 9   1  David    45
# 1   1  David    74
# 10  2    Ram    54
# 2   2    Sam    45
# 3   3   Bane    87
# 11  3   Bane    87
# 4   3   Mary    92
# 5   4   Dane    23
# 6   5  Jenny    87
# 12  5    Ken    87
# 7   6 Simran    63
# 8   8  Priya    72

```
### Sort By
To sort by a specific variable, you can use the `by` parameter:
```{r eval=FALSE, echo=TRUE} 
# Sort By
res <- proc_sort(dat, by = Score)

# View results
res
#    ID   Name Score
# 5   4   Dane    23
# 2   2    Sam    45
# 9   1  David    45
# 10  2    Ram    54
# 7   6 Simran    63
# 8   8  Priya    72
# 1   1  David    74
# 3   3   Bane    87
# 6   5  Jenny    87
# 11  3   Bane    87
# 12  5    Ken    87
# 4   3   Mary    92
```
You can also sort by two variables:
```{r eval=FALSE, echo=TRUE} 
# Sort By
res <- proc_sort(dat, by = v(Score, Name))

# View results
res
#    ID   Name Score
# 5   4   Dane    23
# 9   1  David    45
# 2   2    Sam    45
# 10  2    Ram    54
# 7   6 Simran    63
# 8   8  Priya    72
# 1   1  David    74
# 3   3   Bane    87
# 11  3   Bane    87
# 6   5  Jenny    87
# 12  5    Ken    87
# 4   3   Mary    92
```
Notice that when the scores are tied, the data is now sorted alphabetically 
by name.

### Sort Order

To put the highest scores on top, you can sort descending using the `order`
parameter.
```{r eval=FALSE, echo=TRUE} 
# Sort By
res <- proc_sort(dat, by = Score, order = descending)

# View results
res
#    ID   Name Score
# 4   3   Mary    92
# 3   3   Bane    87
# 6   5  Jenny    87
# 11  3   Bane    87
# 12  5    Ken    87
# 1   1  David    74
# 8   8  Priya    72
# 7   6 Simran    63
# 10  2    Ram    54
# 2   2    Sam    45
# 9   1  David    45
# 5   4   Dane    23
```

The `order` instructions can be abbreviated "a" for ascending and "d" for 
descending.  These abbreviations are convenient when there is more than 
one `by` variable.
```{r eval=FALSE, echo=TRUE} 
# Sort By
res <- proc_sort(dat, by = v(Score, Name), order = v(d, a))

# View results
res
#    ID   Name Score
# 4   3   Mary    92
# 3   3   Bane    87
# 11  3   Bane    87
# 6   5  Jenny    87
# 12  5    Ken    87
# 1   1  David    74
# 8   8  Priya    72
# 7   6 Simran    63
# 10  2    Ram    54
# 9   1  David    45
# 2   2    Sam    45
# 5   4   Dane    23
```
### Keep 
The `keep` parameter allows you to select only some of the variables for the
output dataset:
```{r eval=FALSE, echo=TRUE} 
# Keep Name and Score only
res <- proc_sort(dat, by = Name, keep = v(Name, Score))

# View results
res
#      Name Score
# 3    Bane    87
# 11   Bane    87
# 5    Dane    23
# 1   David    74
# 9   David    45
# 6   Jenny    87
# 12    Ken    87
# 4    Mary    92
# 8   Priya    72
# 10    Ram    54
# 2     Sam    45
# 7  Simran    63

```

### Options
Another convenient feature of `proc_sort()` is the `nodupkey` option.  This 
option will eliminate duplicates based on the unique combination of values
of the `by` variables. For instance, to get a unique list of students, you
could use `keep` and `options = nodupkey`:
```{r eval=FALSE, echo=TRUE} 
# Keep and Nodupkey
res <- proc_sort(dat, by = Name, keep = Name, options = nodupkey)

# View results
res
#      Name
# 3    Bane
# 5    Dane
# 1   David
# 6   Jenny
# 12    Ken
# 4    Mary
# 8   Priya
# 10    Ram
# 2     Sam
# 7  Simran
```
There is also a `dupkey` option to retain only records with duplicate
key values. This feature
would allow you to easily find students who took the exam twice, and show
the scores for each attempt.
```{r eval=FALSE, echo=TRUE} 
# Keep and dupkey
res <- proc_sort(dat, by = Name, options = dupkey)

# View results
res
#   ID  Name Score
# 1  3  Bane    87
# 2  3  Bane    87
# 3  1 David    74
# 4  1 David    45
```

### Sorting Non-alphabetically
Most of the time, you will sort alphabetically or numerically.  But sometimes
you may have character data that you want to sort in a specific order that is
not alphabetic.  Let's return to the class data we used above:
```{r eval=FALSE, echo=TRUE} 
# Create input data
dat <- read.table(header = TRUE, text = '
                  Name    Subject   Semester1  Semester2
                  Samma	  Maths	    96             94
                  Sandy	  English	  76             51
                  Devesh	German	  76             95
                  Rakesh	Maths	    50             63
                  Priya	  English	  62             80
                  Kranti	Maths	    92             92
                  William	German	  87             75')

# View data
dat
#      Name Subject Semester1 Semester2
# 1   Samma   Maths        96        94
# 2   Sandy English        76        51
# 3  Devesh  German        76        95
# 4  Rakesh   Maths        50        63
# 5   Priya English        62        80
# 6  Kranti   Maths        92        92
# 7 William  German        87        75
```
What if we wanted to sort "Maths" to the top, followed by "English" and "German"?
How could that be accomplished?

To perform a sort in a specific, non-alphabetic order, first create a factor
on your desired sort column and order the levels by the intended sort.
For instance:
```{r eval=FALSE, echo=TRUE} 
# Create factor with ordered levels
dat$Subject <- factor(dat$Subject, levels = c("Maths", "English", "German"))

# View attributes
attributes(dat$Subject)
# $levels
# [1] "Maths"   "English" "German" 
# 
# $class
# [1] "factor"

```
Then use `proc_sort()` to sort by the factor variable:

```{r eval=FALSE, echo=TRUE} 
# Sort by factor variable
dat2 <- proc_sort(dat, by = Subject)

# View result
dat2
#      Name Subject Semester1 Semester2
# 1   Samma   Maths        96        94
# 4  Rakesh   Maths        50        63
# 6  Kranti   Maths        92        92
# 2   Sandy English        76        51
# 5   Priya English        62        80
# 3  Devesh  German        76        95
# 7 William  German        87        75

```

Note that after the sort operation, the "Subject" variable is still a factor:
```{r eval=FALSE, echo=TRUE} 
class(dat2$Subject)
# [1] "factor"

```

If desired, we can turn the variable back into a character using 
the "as.character" parameter.  Following the sort, this parameter will 
automatically cast any factors on the by parameter to character variables:
```{r eval=FALSE, echo=TRUE} 
# Sort by factor variable
dat2 <- proc_sort(dat, by = Subject, as.character = TRUE)

# Same results
dat2
#      Name Subject Semester1 Semester2
# 1   Samma   Maths        96        94
# 4  Rakesh   Maths        50        63
# 6  Kranti   Maths        92        92
# 2   Sandy English        76        51
# 5   Priya English        62        80
# 3  Devesh  German        76        95
# 7 William  German        87        75

# But now Subject is a character
class(dat2$Subject)
# [1] "character"
```

### Sorting Missing Values

Normally, R sorts missing values (NA) to the bottom or last. This behavior is
also the default with `proc_sort()`.  To illustrate, let's add an NA
value to the dataset created above, so that we can experiment.  Here is 
the modification:
```{r eval=FALSE, echo=TRUE} 
# Create input data with missing values
dat2 <- read.table(header = TRUE, text = '
                  Name    Subject   Semester1  Semester2
                  Samma	  Maths	    96             94
                  Sandy	  English	  76             NA
                  Devesh	German	  76             95
                  Rakesh	Maths	    50             63
                  Priya	  English	  62             80
                  Kranti	Maths	    92             92
                  William	German	  87             75')

# View dataset
dat2
#      Name Subject Semester1 Semester2
# 1   Samma   Maths        96        94
# 2   Sandy English        76        NA
# 3  Devesh  German        76        95
# 4  Rakesh   Maths        50        63
# 5   Priya English        NA        80
# 6  Kranti   Maths        92        92
# 7 William  German        87        75

```
Now let's try some different sorts. Here is the default sort:
```{r eval=FALSE, echo=TRUE} 
# Default sort on column with NA
proc_sort(dat2, by = Semester2)
#      Name Subject Semester1 Semester2
# 4  Rakesh   Maths        50        63
# 7 William  German        87        75
# 5   Priya English        NA        80
# 6  Kranti   Maths        92        92
# 1   Samma   Maths        96        94
# 3  Devesh  German        76        95
# 2   Sandy English        76        NA

```
As you can see, the function sorts the NA value last.  But what if you want to
sort it first?  

Fortunately, there is a parameter to control the NA sort behavior: `na.sort`.
This parameter allows you to sort the NAs first, last, or in a manner consistent
with SAS.  Here is how to sort the NAs first:
```{r eval=FALSE, echo=TRUE} 
# Force NA to top 
proc_sort(dat2, by = Semester2, na.sort = "first")
#      Name Subject Semester1 Semester2
# 2   Sandy English        76        NA
# 4  Rakesh   Maths        50        63
# 7 William  German        87        75
# 5   Priya English        NA        80
# 6  Kranti   Maths        92        92
# 1   Samma   Maths        96        94
# 3  Devesh  German        76        95

```
Note that "first" and "last" will always sort NAs in the requested direction,
no matter the direction of the sort overall.  Here is the same sort on 
"Semester2", but this time descending instead of ascending:
```{r eval=FALSE, echo=TRUE} 
# NA still forced to top 
proc_sort(dat2, by = Semester2, order = "d", na.sort = "first")
#      Name Subject Semester1 Semester2
# 2   Sandy English        76        NA
# 3  Devesh  German        76        95
# 1   Samma   Maths        96        94
# 6  Kranti   Maths        92        92
# 5   Priya English        NA        80
# 7 William  German        87        75
# 4  Rakesh   Maths        50        63

```
In SAS, however, the NAs can go to the top or bottom, depending on the direction 
of the sort. The `na.sort` parameter also has a "sas" option to mimic this 
behavior. Observe:
```{r eval=FALSE, echo=TRUE} 
# Ascending sort  
proc_sort(dat2, by = Semester2, order = "a", na.sort = "sas")
#      Name Subject Semester1 Semester2
# 2   Sandy English        76        NA
# 4  Rakesh   Maths        50        63
# 7 William  German        87        75
# 5   Priya English        NA        80
# 6  Kranti   Maths        92        92
# 1   Samma   Maths        96        94
# 3  Devesh  German        76        95

# Descending sort  
proc_sort(dat2, by = Semester2, order = "d", na.sort = "sas")
#      Name Subject Semester1 Semester2
# 3  Devesh  German        76        95
# 1   Samma   Maths        96        94
# 6  Kranti   Maths        92        92
# 5   Priya English        NA        80
# 7 William  German        87        75
# 4  Rakesh   Maths        50        63
# 2   Sandy English        76        NA

```
The "sas" option allows you to more easily match the sort produced by 
SAS PROC SORT.  You may set this option globally with 
`options("procs.na.sort" = "sas")`. See the `proc_sort()` documentation 
and `vignette("procs-globals")` for 
additional information.
