# Utilities for Data Manipulation

#### David Gerbing

    library("lessR")

## Recode Data Values

Data transformations for continuous variables are straightforward, just enter the arithmetic expression for the transformation. For each variable identify the corresponding data frame that contains the variable if there is one. For example, the following creates a new variable xsq that is the square of the values of a variable x in the d data frame.

    d$xsq <- d$x^2

Or, use the base R `transform()` function to accomplish the same, plus other functions from other packages that accomplish the same result.

For variables that define discrete categories, however, the transformation may not be so straightforward with base R functions such as a nested string of `ifelse()` functions. An alternative is the lessR function `recode()`.

To use `recode()`, specify the variable to be recoded with the `old_vars` parameter, the first parameter in the function call. Specify values to be recoded with the required `old` parameter. Specify the corresponding recoded values with the required `new` parameter. There must be a 1-to-1 correspondence between the two sets of values, such as 0:5 recoded to 5:0, six items in the `old` set and six items in the `new` set.

### Examples

To illustrate, construct the following small data frame.

    d <- read.table(text="Severity Description
    1 Mild
    4 Moderate
    3 Moderate
    2 Mild
    1 Severe", header=TRUE, stringsAsFactors=FALSE)

    d

    ##   Severity Description
    ## 1        1        Mild
    ## 2        4    Moderate
    ## 3        3    Moderate
    ## 4        2        Mild
    ## 5        1      Severe

Now change the integer values of the variable Severity from 1 through 4 to 10 through 40. Because the parameter `old_vars` is the first parameter in the definition of `recode()`, and because it is listed first, the parameter name need not be specified. The default data frame is d, otherwise specify with the `data` parameter.

    d <- recode(Severity, old=1:4, new=c(10,20,30,40))
    d

    ##   Severity Description
    ## 1       10        Mild
    ## 2       40    Moderate
    ## 3       30    Moderate
    ## 4       20        Mild
    ## 5       10      Severe

In the previous example, the values of the variable were overwritten with the new values. In the following example, instead write the recoded values to a new variable with the `new_vars` parameter, here SevereNew.

    d <- recode(Severity, new_vars="SevereNew", old=1:4, new=c(10,20,30,40))

A convenient application of `recode()` is to Likert data, with responses scored to items on a survey such as from 0 for Strongly Disagree to 5 for Strongly Agree. To encourage responders to carefully read the items, some items are written in the opposite direction so that disagreement indicates agreement with the overall attitude being assessed.

As an example, reverse score Items m01, m02, m03, and m10 from survey responses to the 20-item Mach IV scale. That is, score a 0 as a 5 and so forth. The responses are included as part of lessR and so can be directly read.

    d <- Read("Mach4")

    d <- recode(c(m01:m03,m10), old=0:5, new=5:0)

### Missing Data

The function also addresses missing data. Existing data values can be converted to an R missing value. In this example, all values of 1 for the variable Plan are considered missing.

    d <- Read("Employee")

    newdata <- recode(Plan, old=1, new="missing")

Now values of 1 for Plan are missing, having the value of `NA` for not available, as shown by listing the first six rows of data with the base R function `head()`.

    head(d)

    ##                  Years Gender Dept    Salary JobSat Plan Pre Post
    ## Ritchie, Darnell     7      M ADMN  53788.26    med    1  82   92
    ## Wu, James           NA      M SALE  94494.58    low    1  62   74
    ## Downs, Deborah       7      W FINC  57139.90   high    2  90   86
    ## Hoang, Binh         15      M SALE 111074.86    low    3  96   97
    ## Jones, Alissa        5      W <NA>  53772.58   <NA>    1  65   62
    ## Afshari, Anbar       6      W ADMN  69441.93   high    2 100  100

The procedure can be reversed in which values that are missing according to the R code `NA` are converted to non-missing values. To illustrate with the Employee data set, examine the first six rows of data. The value of `Years` is missing in the second row of data.

    d <- Read("Employee")

    head(d)

    ##                  Years Gender Dept    Salary JobSat Plan Pre Post
    ## Ritchie, Darnell     7      M ADMN  53788.26    med    1  82   92
    ## Wu, James           NA      M SALE  94494.58    low    1  62   74
    ## Downs, Deborah       7      W FINC  57139.90   high    2  90   86
    ## Hoang, Binh         15      M SALE 111074.86    low    3  96   97
    ## Jones, Alissa        5      W <NA>  53772.58   <NA>    1  65   62
    ## Afshari, Anbar       6      W ADMN  69441.93   high    2 100  100

Here convert all missing data values for the variables Years and Salary to the value of 99.

    d <- recode(c(Years, Salary), old="missing", new=99)
    head(d)

    ##                  Years Gender Dept    Salary JobSat Plan Pre Post
    ## Ritchie, Darnell     7      M ADMN  53788.26    med    1  82   92
    ## Wu, James           99      M SALE  94494.58    low    1  62   74
    ## Downs, Deborah       7      W FINC  57139.90   high    2  90   86
    ## Hoang, Binh         15      M SALE 111074.86    low    3  96   97
    ## Jones, Alissa        5      W <NA>  53772.58   <NA>    1  65   62
    ## Afshari, Anbar       6      W ADMN  69441.93   high    2 100  100

Now the value of Years in the second row of data is 99.

## Sort Rows of Data

Sorts the values of a data frame according to the values of one or more variables contained in the data frame, or the row names. Variable types include numeric and factor variables. Factors are sorted by the ordering of their values, which, by default is alphabetical. Sorting by row names is also possible.

To illustrate, use the lessR Employee data set, here just the first 12 rows of data to save space.

    d <- Read("Employee")

    d <- d[1:12,]

    d <- Sort(d, Gender)

    d

    ##                  Years Gender Dept    Salary JobSat Plan Pre Post
    ## Ritchie, Darnell     7      M ADMN  53788.26    med    1  82   92
    ## Wu, James           NA      M SALE  94494.58    low    1  62   74
    ## Hoang, Binh         15      M SALE 111074.86    low    3  96   97
    ## Knox, Michael       18      M MKTG  99062.66    med    3  81   84
    ## Campagna, Justin     8      M SALE  72321.36    low    1  76   84
    ## Pham, Scott         13      M SALE  81871.05   high    2  90   94
    ## Downs, Deborah       7      W FINC  57139.90   high    2  90   86
    ## Jones, Alissa        5      W <NA>  53772.58   <NA>    1  65   62
    ## Afshari, Anbar       6      W ADMN  69441.93   high    2 100  100
    ## Kimball, Claire      8      W MKTG  61356.69   high    2  93   92
    ## Cooper, Lindsay      4      W MKTG  56772.95   high    1  78   91
    ## Saechao, Suzanne     8      W SALE  55545.25    med    1  98  100

    d <- Sort(d, c(Gender, Salary), direction=c("+", "-"))

    d

    ##                  Years Gender Dept    Salary JobSat Plan Pre Post
    ## Hoang, Binh         15      M SALE 111074.86    low    3  96   97
    ## Knox, Michael       18      M MKTG  99062.66    med    3  81   84
    ## Wu, James           NA      M SALE  94494.58    low    1  62   74
    ## Pham, Scott         13      M SALE  81871.05   high    2  90   94
    ## Campagna, Justin     8      M SALE  72321.36    low    1  76   84
    ## Ritchie, Darnell     7      M ADMN  53788.26    med    1  82   92
    ## Afshari, Anbar       6      W ADMN  69441.93   high    2 100  100
    ## Kimball, Claire      8      W MKTG  61356.69   high    2  93   92
    ## Downs, Deborah       7      W FINC  57139.90   high    2  90   86
    ## Cooper, Lindsay      4      W MKTG  56772.95   high    1  78   91
    ## Saechao, Suzanne     8      W SALE  55545.25    med    1  98  100
    ## Jones, Alissa        5      W <NA>  53772.58   <NA>    1  65   62

Sort by row names in ascending order.

    d <- Sort(d, row.names)

    ##   row.names -->  ascending

    d

    ##                  Years Gender Dept    Salary JobSat Plan Pre Post
    ## Afshari, Anbar       6      W ADMN  69441.93   high    2 100  100
    ## Campagna, Justin     8      M SALE  72321.36    low    1  76   84
    ## Cooper, Lindsay      4      W MKTG  56772.95   high    1  78   91
    ## Downs, Deborah       7      W FINC  57139.90   high    2  90   86
    ## Hoang, Binh         15      M SALE 111074.86    low    3  96   97
    ## Jones, Alissa        5      W <NA>  53772.58   <NA>    1  65   62
    ## Kimball, Claire      8      W MKTG  61356.69   high    2  93   92
    ## Knox, Michael       18      M MKTG  99062.66    med    3  81   84
    ## Pham, Scott         13      M SALE  81871.05   high    2  90   94
    ## Ritchie, Darnell     7      M ADMN  53788.26    med    1  82   92
    ## Saechao, Suzanne     8      W SALE  55545.25    med    1  98  100
    ## Wu, James           NA      M SALE  94494.58    low    1  62   74

Randomize the order of the data values.

    d <- Sort(d, random)

    ##   random

    d

    ##                  Years Gender Dept    Salary JobSat Plan Pre Post
    ## Jones, Alissa        5      W <NA>  53772.58   <NA>    1  65   62
    ## Saechao, Suzanne     8      W SALE  55545.25    med    1  98  100
    ## Ritchie, Darnell     7      M ADMN  53788.26    med    1  82   92
    ## Afshari, Anbar       6      W ADMN  69441.93   high    2 100  100
    ## Cooper, Lindsay      4      W MKTG  56772.95   high    1  78   91
    ## Pham, Scott         13      M SALE  81871.05   high    2  90   94
    ## Downs, Deborah       7      W FINC  57139.90   high    2  90   86
    ## Knox, Michael       18      M MKTG  99062.66    med    3  81   84
    ## Kimball, Claire      8      W MKTG  61356.69   high    2  93   92
    ## Wu, James           NA      M SALE  94494.58    low    1  62   74
    ## Campagna, Justin     8      M SALE  72321.36    low    1  76   84
    ## Hoang, Binh         15      M SALE 111074.86    low    3  96   97

## Rescale Data

    rescale(Salary)

    ##  [1] -0.926 -0.837 -0.925 -0.139 -0.775  0.484 -0.757  1.347 -0.545  1.118  0.005  1.950

## Rename a Variable in a Data Frame

List the name of the data frame, the existing variable name, and the new name, in that order.

    names(d)

    ## [1] "Years"  "Gender" "Dept"   "Salary" "JobSat" "Plan"   "Pre"    "Post"

    d <- rename(d, Salary, AnnualSalary)

    ## Change name of variable Salary to AnnualSalary in data frame d

    names(d)

    ## [1] "Years"        "Gender"       "Dept"         "AnnualSalary" "JobSat"       "Plan"         "Pre"
    ## [8] "Post"

## Create Factor Variables

    d <- rd("Mach4", quiet=TRUE)

    l <- rd("Mach4_lbl")

    LikertCats <- c("Strongly Disagree", "Disagree", "Slightly Disagree",
                    "Slightly Agree", "Agree", "Strongly Agree")

    d <- factors(m01:m20, levels=0:5, labels=LikertCats)

Convert the specified variables to factors according to the given vector of three variables only. Leave the original variables unmodified, create new variables.

    d <- factors(c(m06, m07, m20), levels=0:5, labels=LikertCats, new=TRUE)

Now copy the variable labels from the original integer variables to the newly created factor variables.

    l <- factors(c(m06, m07, m20), var_labels=TRUE)

## Reshape Data Wide to Long

A wide-form data table has multiple measurements from the same unit of analysis (e.g., person) across the row of data, usually repeated over time. The conversion to long-form forms three new columns from the input wide-form: the name of the grouping variable, the name of the values, and the name of the ID field.

Read the data.

    d <- Read("Anova_rb")

    d

    ##   Person sup1 sup2 sup3 sup4
    ## 1     p1    2    4    4    3
    ## 2     p2    2    5    4    6
    ## 3     p3    8    6    7    9
    ## 4     p4    4    3    5    7
    ## 5     p5    2    1    2    3
    ## 6     p6    5    5    6    8
    ## 7     p7    2    3    2    4

Go with the default variable names in the long-form.

    reshape_long(d, c("sup1", "sup2", "sup3", "sup4"))

    ##    Person Group Value
    ## 1      p1  sup1     2
    ## 2      p2  sup1     2
    ## 3      p3  sup1     8
    ## 4      p4  sup1     4
    ## 5      p5  sup1     2
    ## 6      p6  sup1     5
    ## 7      p7  sup1     2
    ## 8      p1  sup2     4
    ## 9      p2  sup2     5
    ## 10     p3  sup2     6
    ## 11     p4  sup2     3
    ## 12     p5  sup2     1
    ## 13     p6  sup2     5
    ## 14     p7  sup2     3
    ## 15     p1  sup3     4
    ## 16     p2  sup3     4
    ## 17     p3  sup3     7
    ## 18     p4  sup3     5
    ## 19     p5  sup3     2
    ## 20     p6  sup3     6
    ## 21     p7  sup3     2
    ## 22     p1  sup4     3
    ## 23     p2  sup4     6
    ## 24     p3  sup4     9
    ## 25     p4  sup4     7
    ## 26     p5  sup4     3
    ## 27     p6  sup4     8
    ## 28     p7  sup4     4

Specify custom variable names in the long-form.

    reshape_long(d, sup1:sup4, group="Supplement", response="Reps", ID="Person")

    ##    Person Supplement Reps
    ## 1      p1       sup1    2
    ## 2      p2       sup1    2
    ## 3      p3       sup1    8
    ## 4      p4       sup1    4
    ## 5      p5       sup1    2
    ## 6      p6       sup1    5
    ## 7      p7       sup1    2
    ## 8      p1       sup2    4
    ## 9      p2       sup2    5
    ## 10     p3       sup2    6
    ## 11     p4       sup2    3
    ## 12     p5       sup2    1
    ## 13     p6       sup2    5
    ## 14     p7       sup2    3
    ## 15     p1       sup3    4
    ## 16     p2       sup3    4
    ## 17     p3       sup3    7
    ## 18     p4       sup3    5
    ## 19     p5       sup3    2
    ## 20     p6       sup3    6
    ## 21     p7       sup3    2
    ## 22     p1       sup4    3
    ## 23     p2       sup4    6
    ## 24     p3       sup4    9
    ## 25     p4       sup4    7
    ## 26     p5       sup4    3
    ## 27     p6       sup4    8
    ## 28     p7       sup4    4