Rmarkdown: Data Processing

Data Processing

Data processing is a critical phase in any research process, particularly in the field of linguistics. It involves a series of steps aimed at transforming raw data into an informative, structured format that can be easily analyzed.

In this section, we’ll explore the essential data processing techniques in R, using functions designed to clean and shape the data. We’ll begin with a dataset containing linguistic information that is untidy and riddled with inconsistencies. Through the application of various R functions, we’ll demonstrate how to make this dataset clean, consistent, and ready for statistical analysis.

The functions we’ll cover include:

• read.csv(): Reading the dataset into R from a CSV file.
• select(): Selecting specific variables to keep or remove.
• filter(): Filtering rows based on conditions.
• mutate(): Creating or modifying variables.
• rename(): Renaming variables.
• arrange(): Sorting data based on variables.
• pivot_longer(): Transforming data from wide to long format.
• pivot_wider(): Transforming data from long to wide format.
• left_join(): Joining two data frames by matching rows in the left data frame to the right data frame, keeping all rows from the left data frame.
• full_join(): Joining two data frames by matching rows in both data frames, keeping all rows from both.
• inner_join(): Joining two data frames by matching rows in both data frames, keeping only rows that match in both.
• anti_join(): Joining two data frames by matching rows in the left data frame that do not have corresponding rows in the right data frame.

Our goal is to guide you through the entire data cleaning process, offering clear examples and explanations along the way. By the end of this lesson, you should have a strong understanding of how to prepare linguistic data for analysis in R, utilizing best practices that ensure data integrity and quality.

Now, let’s dive into the dataset.

Reading in the data

read.csv() is used to read a CSV file into R. The code below reads the csv file data_uncleaned.csv into R.

data_uncleaned <- read.csv("data_uncleaned.csv")
kable(data_uncleaned, format = "html", booktabs = TRUE, 
      digits = 2, escape = F, row.names = FALSE) %>%
  kable_styling() # 'kable' from the 'knitr' library and 'kable_styling' from 'kableExtra' library are used to format and display the dataset neatly. No need to worry about these; they're just for presentation.

Extraneous1	Name	Age	Gender	RegionName	Language	ProficiencyScore	Education	Occupation
1.54	Person_1	38	M	East	French	74	High School	Student
-0.13	Person_2	31	M	East	French	85	High School	Doctor
-0.03	Person_3	23	M	North	English	86	PhD	Student
0.65	Person_4	38	M	North	English	75	PhD	Artist
1.50	Person_5	20	M	West	English	78	Masters	Professor
0.74	Person_6	22	M	South	English	73	Masters	Student
0.22	Person_7	25	F	East	French	79	PhD	Artist
0.30	Person_8	18	F	South	English	88	High School	Doctor
0.01	Person_9	49	M	East	English	99	College	Engineer
-1.25	Person_10	24	M	South	English	96	Bachelors	Artist
-0.39	Person_11	48	F	West	English	73	Bachelors	Professor
0.32	Person_12	18	M	North	English	80	College	Artist
1.60	Person_13	25	F	South	English	79	Bachelors	Engineer
0.31	Person_14	27	F	North	Spanish	65	Bachelors	Professor
1.12	Person_15	25	F	East	Spanish	72	High School	Professor
1.39	Person_16	46	M	South	Spanish	97	PhD	Student
0.23	Person_17	20	F	West	Spanish	70	College	Artist
0.82	Person_18	24	F	North	English	72	High School	Doctor
1.37	Person_19	21	F	North	English	62	PhD	Engineer
-0.97	Person_20	34	M	West	English	78	College	Student
-1.23	Person_21	18	F	North	French	86	Masters	Doctor
-1.05	Person_22	45	F	South	Spanish	66	Masters	Professor
1.37	Person_23	23	F	South	French	77	Bachelors	Engineer
0.26	Person_24	45	F	South	English	61	Masters	Doctor
-1.04	Person_25	33	M	West	English	90	College	Engineer

str(data_uncleaned)

## 'data.frame':    25 obs. of  9 variables:
##  $ Extraneous1     : num  1.5368 -0.1298 -0.0328 0.6491 1.4991 ...
##  $ Name            : chr  "Person_1" "Person_2" "Person_3" "Person_4" ...
##  $ Age             : int  38 31 23 38 20 22 25 18 49 24 ...
##  $ Gender          : chr  "M" "M" "M" "M" ...
##  $ RegionName      : chr  "East" "East" "North" "North" ...
##  $ Language        : chr  "French" "French" "English" "English" ...
##  $ ProficiencyScore: int  74 85 86 75 78 73 79 88 99 96 ...
##  $ Education       : chr  "High School" "High School" "PhD" "PhD" ...
##  $ Occupation      : chr  "Student" "Doctor" "Student" "Artist" ...

select()

select() can be applied to data frames to select or unselect specific variables (columns).

1. Selecting Specific Columns by Name:

You can use select() to choose specific columns by providing their names.

data_select1 <- data_uncleaned %>%
  select(Name, Age, Gender, RegionName, Language, ProficiencyScore, Education, Occupation) # select all variables but Extraneous1

head(data_select1) # display the first six rows

##       Name Age Gender RegionName Language ProficiencyScore   Education
## 1 Person_1  38      M       East   French               74 High School
## 2 Person_2  31      M       East   French               85 High School
## 3 Person_3  23      M      North  English               86         PhD
## 4 Person_4  38      M      North  English               75         PhD
## 5 Person_5  20      M       West  English               78     Masters
## 6 Person_6  22      M      South  English               73     Masters
##   Occupation
## 1    Student
## 2     Doctor
## 3    Student
## 4     Artist
## 5  Professor
## 6    Student

2. Selecting Columns by Range:

If you want to select a range of consecutive columns, you can use the : operator.

data_select2 <- data_uncleaned %>%
  select(Name:Occupation) # select variables from Name to Occupation

head(data_select2) # display the first six rows

##       Name Age Gender RegionName Language ProficiencyScore   Education
## 1 Person_1  38      M       East   French               74 High School
## 2 Person_2  31      M       East   French               85 High School
## 3 Person_3  23      M      North  English               86         PhD
## 4 Person_4  38      M      North  English               75         PhD
## 5 Person_5  20      M       West  English               78     Masters
## 6 Person_6  22      M      South  English               73     Masters
##   Occupation
## 1    Student
## 2     Doctor
## 3    Student
## 4     Artist
## 5  Professor
## 6    Student

3. Unselecting Columns:

To exclude specific columns, you can use the - operator.

data_select3 <- data_uncleaned %>%
  select(-Extraneous1) # only exclude the Extraneous1 variable

head(data_select3) # display the first six rows

##       Name Age Gender RegionName Language ProficiencyScore   Education
## 1 Person_1  38      M       East   French               74 High School
## 2 Person_2  31      M       East   French               85 High School
## 3 Person_3  23      M      North  English               86         PhD
## 4 Person_4  38      M      North  English               75         PhD
## 5 Person_5  20      M       West  English               78     Masters
## 6 Person_6  22      M      South  English               73     Masters
##   Occupation
## 1    Student
## 2     Doctor
## 3    Student
## 4     Artist
## 5  Professor
## 6    Student

4. Using Helper Functions:

Various helper functions can be used with select(), such as starts_with(), ends_with(), contains(), etc., to select columns that match certain patterns.

data_select4 <- data_uncleaned %>%
  select(starts_with("Pro"), ends_with("Name"), contains("end")) # make sure the strings are enclosed with single or double quote marks

head(data_select4)

##   ProficiencyScore     Name RegionName Gender
## 1               74 Person_1       East      M
## 2               85 Person_2       East      M
## 3               86 Person_3      North      M
## 4               75 Person_4      North      M
## 5               78 Person_5       West      M
## 6               73 Person_6      South      M

5. Rearranging Columns:

You can rearrange the columns by specifying the order within the select() function.

data_select5<- data_uncleaned %>%
  select(Name, Gender, Age, Language) # the columns have been rearranged according to the order you specified

head(data_select5)

##       Name Gender Age Language
## 1 Person_1      M  38   French
## 2 Person_2      M  31   French
## 3 Person_3      M  23  English
## 4 Person_4      M  38  English
## 5 Person_5      M  20  English
## 6 Person_6      M  22  English

6. Selecting Everything Except Certain Columns:

var_exclude <- c("Extraneous1", "Name", "Age")
data_select6 <- data_uncleaned %>%
  select(-all_of(var_exclude)) # removes the columns specified in the 'var_exclude'

head(data_select6)

##   Gender RegionName Language ProficiencyScore   Education Occupation
## 1      M       East   French               74 High School    Student
## 2      M       East   French               85 High School     Doctor
## 3      M      North  English               86         PhD    Student
## 4      M      North  English               75         PhD     Artist
## 5      M       West  English               78     Masters  Professor
## 6      M      South  English               73     Masters    Student

filter()

filter() can be used to filter rows in a dataframe

Logical Operators in R

In R, logical operators are essential for comparison and making logical evaluations. They are particularly important in functions like filter(), where they can be used to select specific subsets of data based on certain criteria. Here are some of the main logical operators you’ll be using:

• ==: Equal to
• !=: Not equal to
• <: Less than
• <=: Less than or equal to
• >: Greater than
• >=: Greater than or equal to
• &: And
• |: Or
• %in%: if a value or values are present in a set of values

1. Filtering Rows Based on Conditions:

Filter rows based on a specific condition.

data_filter1 <- data_uncleaned %>%
  filter(Age <= 20) # include only the rows where the 'Age' is less than or equal to 20
kable(data_filter1, format = "html", booktabs = TRUE, 
      digits = 2, escape = F, row.names = FALSE) %>%
  kable_styling()

Extraneous1	Name	Age	Gender	RegionName	Language	ProficiencyScore	Education	Occupation
1.50	Person_5	20	M	West	English	78	Masters	Professor
0.30	Person_8	18	F	South	English	88	High School	Doctor
0.32	Person_12	18	M	North	English	80	College	Artist
0.23	Person_17	20	F	West	Spanish	70	College	Artist
-1.23	Person_21	18	F	North	French	86	Masters	Doctor

2. Filtering Rows with Multiple Conditions:

You can combine multiple conditions using logical operators like & and |.

data_filter2 <- data_uncleaned %>%
  filter(Age <= 20 | Age >= 40) # include only the rows where the 'Age' is less than or equal to 20, OR greater than or equal to 40
kable(data_filter2, format = "html", booktabs = TRUE, 
      digits = 2, escape = F, row.names = FALSE) %>%
  kable_styling()

Extraneous1	Name	Age	Gender	RegionName	Language	ProficiencyScore	Education	Occupation
1.50	Person_5	20	M	West	English	78	Masters	Professor
0.30	Person_8	18	F	South	English	88	High School	Doctor
0.01	Person_9	49	M	East	English	99	College	Engineer
-0.39	Person_11	48	F	West	English	73	Bachelors	Professor
0.32	Person_12	18	M	North	English	80	College	Artist
1.39	Person_16	46	M	South	Spanish	97	PhD	Student
0.23	Person_17	20	F	West	Spanish	70	College	Artist
-1.23	Person_21	18	F	North	French	86	Masters	Doctor
-1.05	Person_22	45	F	South	Spanish	66	Masters	Professor
0.26	Person_24	45	F	South	English	61	Masters	Doctor

data_filter3 <- data_uncleaned %>%
  filter(Age >= 20 & Age <= 40) # include only the rows where the 'Age' is greater than or equal to 20 AND less than or equal to 40
kable(data_filter3, format = "html", booktabs = TRUE, 
      digits = 2, escape = F, row.names = FALSE) %>%
  kable_styling()

Extraneous1	Name	Age	Gender	RegionName	Language	ProficiencyScore	Education	Occupation
1.54	Person_1	38	M	East	French	74	High School	Student
-0.13	Person_2	31	M	East	French	85	High School	Doctor
-0.03	Person_3	23	M	North	English	86	PhD	Student
0.65	Person_4	38	M	North	English	75	PhD	Artist
1.50	Person_5	20	M	West	English	78	Masters	Professor
0.74	Person_6	22	M	South	English	73	Masters	Student
0.22	Person_7	25	F	East	French	79	PhD	Artist
-1.25	Person_10	24	M	South	English	96	Bachelors	Artist
1.60	Person_13	25	F	South	English	79	Bachelors	Engineer
0.31	Person_14	27	F	North	Spanish	65	Bachelors	Professor
1.12	Person_15	25	F	East	Spanish	72	High School	Professor
0.23	Person_17	20	F	West	Spanish	70	College	Artist
0.82	Person_18	24	F	North	English	72	High School	Doctor
1.37	Person_19	21	F	North	English	62	PhD	Engineer
-0.97	Person_20	34	M	West	English	78	College	Student
1.37	Person_23	23	F	South	French	77	Bachelors	Engineer
-1.04	Person_25	33	M	West	English	90	College	Engineer

3. Filtering Rows Using %in% Operator:

Use the %in% operator to filter rows based on whether a value is in a set of values.

Occupation_interest <- c("Doctor", "Professor", "Student")
data_filter4 <- data_uncleaned %>%
  filter(Occupation %in% Occupation_interest) # include only the rows where the Occupation's value is present in the 'Occupation_interest' vector
kable(data_filter4, format = "html", booktabs = TRUE, 
      digits = 2, escape = F, row.names = FALSE) %>%
  kable_styling()

Extraneous1	Name	Age	Gender	RegionName	Language	ProficiencyScore	Education	Occupation
1.54	Person_1	38	M	East	French	74	High School	Student
-0.13	Person_2	31	M	East	French	85	High School	Doctor
-0.03	Person_3	23	M	North	English	86	PhD	Student
1.50	Person_5	20	M	West	English	78	Masters	Professor
0.74	Person_6	22	M	South	English	73	Masters	Student
0.30	Person_8	18	F	South	English	88	High School	Doctor
-0.39	Person_11	48	F	West	English	73	Bachelors	Professor
0.31	Person_14	27	F	North	Spanish	65	Bachelors	Professor
1.12	Person_15	25	F	East	Spanish	72	High School	Professor
1.39	Person_16	46	M	South	Spanish	97	PhD	Student
0.82	Person_18	24	F	North	English	72	High School	Doctor
-0.97	Person_20	34	M	West	English	78	College	Student
-1.23	Person_21	18	F	North	French	86	Masters	Doctor
-1.05	Person_22	45	F	South	Spanish	66	Masters	Professor
0.26	Person_24	45	F	South	English	61	Masters	Doctor

4. Inverting a Filter Condition:

You can invert a filter condition using the ! operator.

# the codes below illustrate two ways to include only the rows where the 'Gender' column's value is not "Male"
data_filter5 <- data_uncleaned %>%
  filter(Gender != "Male") # uses the '!=' to exclude rows where 'Gender' is "Male."

data_filter6 <- data_uncleaned %>%
  filter(!(Gender == "Male")) # uses '==' to check for "Male" and then negates the result with the logical NOT operator '!'. 

mutate()

mutate() used to create or transform variables within a data frame. It returns a new data frame with the added or modified columns.

1. Creating a New Column:

You can use mutate() to create a new column based on existing columns or values.

data_mutate1 <- data_uncleaned %>%
  mutate(Age_nextYear = Age + 1) %>% # creates a new column 'Age_nextYear' by adding 1 to the existing 'Age' column 
    select(Age, Age_nextYear) # selects only the 'Age' and 'Age_nextYear' columns

head(data_mutate1)

##   Age Age_nextYear
## 1  38           39
## 2  31           32
## 3  23           24
## 4  38           39
## 5  20           21
## 6  22           23

2. Creating Multiple Columns:

You can create or modify multiple columns in one call to mutate().

data_mutate2 <- data_uncleaned %>%
  mutate(Age_nextYear = Age + 1,
         Age_Year3 = Age + 2) %>% 
    select(Age, Age_nextYear, Age_Year3)

head(data_mutate2)

##   Age Age_nextYear Age_Year3
## 1  38           39        40
## 2  31           32        33
## 3  23           24        25
## 4  38           39        40
## 5  20           21        22
## 6  22           23        24

3. Modifying an Existing Column:

mutate() can be used to change the values of an existing column.

data_mutate3 <- data_uncleaned %>%
  mutate(Age = Age * 100) # modifies the 'Age' column by multiplying each value by 100

head(data_mutate3)

##   Extraneous1     Name  Age Gender RegionName Language ProficiencyScore
## 1  1.53683687 Person_1 3800      M       East   French               74
## 2 -0.12979440 Person_2 3100      M       East   French               85
## 3 -0.03282877 Person_3 2300      M      North  English               86
## 4  0.64913683 Person_4 3800      M      North  English               75
## 5  1.49905319 Person_5 2000      M       West  English               78
## 6  0.73987894 Person_6 2200      M      South  English               73
##     Education Occupation
## 1 High School    Student
## 2 High School     Doctor
## 3         PhD    Student
## 4         PhD     Artist
## 5     Masters  Professor
## 6     Masters    Student

4. Using Conditional Logic:

You can incorporate conditional logic using functions like if_else().

data_mutate4 <- data_uncleaned %>%
  mutate(Age_group = ifelse(Age > 25, "group 1", "group 2")) %>% # creates a new column 'Age_group': it assigns the value "group 1" if the 'Age' is greater than 25, and "group 2" otherwise
  select(Name, Age, Age_group) # selects only 'Name', 'Age', and 'Age_group' columns

head(data_mutate4)

##       Name Age Age_group
## 1 Person_1  38   group 1
## 2 Person_2  31   group 1
## 3 Person_3  23   group 2
## 4 Person_4  38   group 1
## 5 Person_5  20   group 2
## 6 Person_6  22   group 2

rename()

rename() renames columns in a data frame. It returns a new data frame with the specified columns renamed.

1. Renaming a Single Column:

You can rename a single column by specifyingThe fundamental syntax for utilizing the rename() function is: rename(new_name = old_name)

data_rename1 <- data_uncleaned %>%
  rename(Region = RegionName) # renames the 'RegionName' column to 'Region'

head(data_rename1)

##   Extraneous1     Name Age Gender Region Language ProficiencyScore   Education
## 1  1.53683687 Person_1  38      M   East   French               74 High School
## 2 -0.12979440 Person_2  31      M   East   French               85 High School
## 3 -0.03282877 Person_3  23      M  North  English               86         PhD
## 4  0.64913683 Person_4  38      M  North  English               75         PhD
## 5  1.49905319 Person_5  20      M   West  English               78     Masters
## 6  0.73987894 Person_6  22      M  South  English               73     Masters
##   Occupation
## 1    Student
## 2     Doctor
## 3    Student
## 4     Artist
## 5  Professor
## 6    Student

2. Renaming Multiple Columns:

Multiple columns can be renamed simultaneously by providing additional arguments.

data_rename2 <- data_uncleaned %>%
  rename(Region = RegionName,
         NativeLanguage = Language) # # renames the 'RegionName' column to 'Region' and the 'Language' column to 'NativeLanguage' 

head(data_rename2)

##   Extraneous1     Name Age Gender Region NativeLanguage ProficiencyScore
## 1  1.53683687 Person_1  38      M   East         French               74
## 2 -0.12979440 Person_2  31      M   East         French               85
## 3 -0.03282877 Person_3  23      M  North        English               86
## 4  0.64913683 Person_4  38      M  North        English               75
## 5  1.49905319 Person_5  20      M   West        English               78
## 6  0.73987894 Person_6  22      M  South        English               73
##     Education Occupation
## 1 High School    Student
## 2 High School     Doctor
## 3         PhD    Student
## 4         PhD     Artist
## 5     Masters  Professor
## 6     Masters    Student

arrange()

arrange() is used to sort rows within a data frame based on specific columns (default: by ascending order)

1. Arranging by a Single Column:

You can sort the rows by a single column in ascending order.

data_arrange1 <- data_uncleaned %>%
  arrange(Age) # sort the rows of the dataframe based on the values in the 'Age' column, arranging them in ascending orde

head(data_arrange1)

##   Extraneous1      Name Age Gender RegionName Language ProficiencyScore
## 1   0.3001195  Person_8  18      F      South  English               88
## 2   0.3212447 Person_12  18      M      North  English               80
## 3  -1.2276742 Person_21  18      F      North   French               86
## 4   1.4990532  Person_5  20      M       West  English               78
## 5   0.2316709 Person_17  20      F       West  Spanish               70
## 6   1.3700492 Person_19  21      F      North  English               62
##     Education Occupation
## 1 High School     Doctor
## 2     College     Artist
## 3     Masters     Doctor
## 4     Masters  Professor
## 5     College     Artist
## 6         PhD   Engineer

data_arrange2 <- data_uncleaned %>%
  arrange(Name) # sort the rows of the dataframe based on the values in the 'Name' column, arranging them in alphabetical order.

head(data_arrange2)

##   Extraneous1      Name Age Gender RegionName Language ProficiencyScore
## 1   1.5368369  Person_1  38      M       East   French               74
## 2  -1.2482735 Person_10  24      M      South  English               96
## 3  -0.3902175 Person_11  48      F       West  English               73
## 4   0.3212447 Person_12  18      M      North  English               80
## 5   1.6008964 Person_13  25      F      South  English               79
## 6   0.3084867 Person_14  27      F      North  Spanish               65
##     Education Occupation
## 1 High School    Student
## 2   Bachelors     Artist
## 3   Bachelors  Professor
## 4     College     Artist
## 5   Bachelors   Engineer
## 6   Bachelors  Professor

What you’re observing is known as lexicographic (or alphabetical) sorting, where the sorting algorithm compares characters one at a time from left to right. So ‘person_10’ comes before ‘person_2’ because ‘1’ comes before ‘2’ lexicographically, even though numerically 10 comes after 2.

• Adding Leading Zeros: You can standardize the ‘Name’ column by adding leading zeros to the numbers. This will make them sort correctly in lexicographic order. For example, ‘Person_1’ could become ‘Person_01’, ‘Person_2’ would become ‘Person_02’, etc., so they sort as expected.

library(stringr)
data_arrange2_with_zeros <- data_uncleaned %>%
  mutate(Name = sprintf("Person_%02d", as.numeric(str_extract(Name, "\\d+")))) %>%
  arrange(Name)

head(data_arrange2_with_zeros)

##   Extraneous1      Name Age Gender RegionName Language ProficiencyScore
## 1  1.53683687 Person_01  38      M       East   French               74
## 2 -0.12979440 Person_02  31      M       East   French               85
## 3 -0.03282877 Person_03  23      M      North  English               86
## 4  0.64913683 Person_04  38      M      North  English               75
## 5  1.49905319 Person_05  20      M       West  English               78
## 6  0.73987894 Person_06  22      M      South  English               73
##     Education Occupation
## 1 High School    Student
## 2 High School     Doctor
## 3         PhD    Student
## 4         PhD     Artist
## 5     Masters  Professor
## 6     Masters    Student

• Adding a Numeric ID Column: Another approach is to add a separate numeric ‘ID’ column. This ‘ID’ can be extracted from the ‘Name’ column and then used for sorting.

library(stringr)
data_arrange2_with_ID <- data_uncleaned %>%
  mutate(ID = as.numeric(str_extract(Name, "\\d+"))) %>%
  arrange(ID)

head(data_arrange2_with_ID)

##   Extraneous1     Name Age Gender RegionName Language ProficiencyScore
## 1  1.53683687 Person_1  38      M       East   French               74
## 2 -0.12979440 Person_2  31      M       East   French               85
## 3 -0.03282877 Person_3  23      M      North  English               86
## 4  0.64913683 Person_4  38      M      North  English               75
## 5  1.49905319 Person_5  20      M       West  English               78
## 6  0.73987894 Person_6  22      M      South  English               73
##     Education Occupation ID
## 1 High School    Student  1
## 2 High School     Doctor  2
## 3         PhD    Student  3
## 4         PhD     Artist  4
## 5     Masters  Professor  5
## 6     Masters    Student  6

2.Arranging by Multiple Columns:

For sorting by multiple columns, you can provide additional columns as arguments.

data_arrange3 <- data_uncleaned %>%
  arrange(Age, ProficiencyScore) # sort the rows of the dataframe based on two columns: 'Age' and 'ProficiencyScore'. The rows are first sorted in ascending order by 'Age', and if there are rows with the same 'Age', they will be further sorted by 'ProficiencyScore' in ascending order. 

head(data_arrange3)

##   Extraneous1      Name Age Gender RegionName Language ProficiencyScore
## 1   0.3212447 Person_12  18      M      North  English               80
## 2  -1.2276742 Person_21  18      F      North   French               86
## 3   0.3001195  Person_8  18      F      South  English               88
## 4   0.2316709 Person_17  20      F       West  Spanish               70
## 5   1.4990532  Person_5  20      M       West  English               78
## 6   1.3700492 Person_19  21      F      North  English               62
##     Education Occupation
## 1     College     Artist
## 2     Masters     Doctor
## 3 High School     Doctor
## 4     College     Artist
## 5     Masters  Professor
## 6         PhD   Engineer

3.Arranging in Descending Order:

Use the desc() function to sort a column in descending order.

data_arrange4 <- data_uncleaned %>%
  arrange(desc(Age)) # sort the rows of the dataframe based on the values in the 'Age' column, but in descending order this time

head(data_arrange4)

##    Extraneous1      Name Age Gender RegionName Language ProficiencyScore
## 1  0.009833386  Person_9  49      M       East  English               99
## 2 -0.390217498 Person_11  48      F       West  English               73
## 3  1.393376002 Person_16  46      M      South  Spanish               97
## 4 -1.054260821 Person_22  45      F      South  Spanish               66
## 5  0.263767897 Person_24  45      F      South  English               61
## 6  1.536836872  Person_1  38      M       East   French               74
##     Education Occupation
## 1     College   Engineer
## 2   Bachelors  Professor
## 3         PhD    Student
## 4     Masters  Professor
## 5     Masters     Doctor
## 6 High School    Student

4.Using Functions Within arrange():

You can use functions within arrange() to create more complex sorting rules.

data_arrange5 <- data_uncleaned %>%
  arrange(abs(Extraneous1)) # sort the rows of the dataframe based on the absolute values in the 'Extraneous1' column. Whether the values are negative or positive, they will be sorted by their absolute value in ascending order

head(data_arrange5)

##    Extraneous1      Name Age Gender RegionName Language ProficiencyScore
## 1  0.009833386  Person_9  49      M       East  English               99
## 2 -0.032828767  Person_3  23      M      North  English               86
## 3 -0.129794396  Person_2  31      M       East   French               85
## 4  0.216235485  Person_7  25      F       East   French               79
## 5  0.231670865 Person_17  20      F       West  Spanish               70
## 6  0.263767897 Person_24  45      F      South  English               61
##     Education Occupation
## 1     College   Engineer
## 2         PhD    Student
## 3 High School     Doctor
## 4         PhD     Artist
## 5     College     Artist
## 6     Masters     Doctor

5.Arranging Based on Calculations:

You can arrange rows based on the result of a calculation involving one or more columns.

# Set the seed for random number generation to ensure reproducibility
set.seed(123) 

data_arrange6 <- data_uncleaned %>%
  # Add random noise between -10 and 10 to 'ProficiencyScore' to create three new columns
  mutate(
    ProficiencyScore1 = ProficiencyScore + sample(-10:10, size = n(), replace = TRUE),
    ProficiencyScore2 = ProficiencyScore + sample(-10:10, size = n(), replace = TRUE),
    ProficiencyScore3 = ProficiencyScore + sample(-10:10, size = n(), replace = TRUE)
  ) %>%
  # Calculate the total proficiency score across the three new columns and store it in 'ProficiencyScoreTotal'
  mutate(ProficiencyScoreTotal = ProficiencyScore1 + ProficiencyScore2 + ProficiencyScore3) %>%
  # Sort the dataframe based on the sum of the three new proficiency score columns
  arrange(ProficiencyScore1 + ProficiencyScore2 + ProficiencyScore3)

head(data_arrange6)

##   Extraneous1      Name Age Gender RegionName Language ProficiencyScore
## 1   0.2637679 Person_24  45      F      South  English               61
## 2   0.3084867 Person_14  27      F      North  Spanish               65
## 3   1.3700492 Person_19  21      F      North  English               62
## 4  -1.0542608 Person_22  45      F      South  Spanish               66
## 5   0.2316709 Person_17  20      F       West  Spanish               70
## 6   0.6491368  Person_4  38      M      North  English               75
##   Education Occupation ProficiencyScore1 ProficiencyScore2 ProficiencyScore3
## 1   Masters     Doctor                57                65                57
## 2 Bachelors  Professor                57                59                73
## 3       PhD   Engineer                70                52                67
## 4   Masters  Professor                72                70                58
## 5   College     Artist                69                72                62
## 6       PhD     Artist                67                77                72
##   ProficiencyScoreTotal
## 1                   179
## 2                   189
## 3                   189
## 4                   200
## 5                   203
## 6                   216

pivot_longer()

pivot_longer() is used to convert wide-format data to long-format data, often needed for various types of analyses or data visualizations.

1. Basic Usage: Here’s a simple example where we turn columns ProficiencyScore1, ProficiencyScore2, and ProficiencyScore3 into a pair of new variables, Test and Score

# Read in the new dataset
data_wide <- read.csv("data_wide.csv", header = T)

# Look at what the dataset looks like
kable(data_wide, format = "html", booktabs = TRUE, 
      digits = 2, escape = F, row.names = FALSE) %>%
  kable_styling()

ID	Age	Gender	Region	Language	Education	Occupation	ProficiencyScore1	ProficiencyScore2	ProficiencyScore3
1	21	F	North	English	College	Professor	99	107	88
2	41	F	South	French	PhD	Engineer	56	59	54
3	46	F	North	English	PhD	Student	62	69	53
4	39	F	West	English	Bachelors	Doctor	102	97	98
5	48	F	South	English	Masters	Artist	99	87	79
6	43	M	North	English	College	Professor	94	83	83
7	19	M	South	English	High School	Artist	74	87	94
8	22	F	South	French	Masters	Artist	72	78	72
9	26	M	East	Spanish	High School	Doctor	80	78	93
10	27	M	South	English	High School	Doctor	69	60	72
11	32	F	North	Spanish	Masters	Student	76	78	86
12	47	M	East	French	College	Artist	106	105	98
13	41	M	North	English	Bachelors	Engineer	72	68	57
14	39	F	East	English	College	Doctor	86	98	84
15	35	F	North	English	Bachelors	Student	91	91	93
16	29	F	North	English	High School	Student	77	91	86
17	40	M	South	French	Masters	Professor	78	77	85
18	45	M	West	Spanish	Bachelors	Engineer	70	79	80
19	30	F	South	English	PhD	Professor	57	62	69
20	44	M	East	English	Bachelors	Doctor	88	87	90
21	20	M	West	Spanish	PhD	Engineer	60	67	58
22	22	M	West	English	High School	Artist	82	66	82
23	36	M	South	Spanish	College	Engineer	67	74	61
24	36	F	East	English	PhD	Professor	67	59	76
25	45	F	West	French	Masters	Student	91	94	98

data_long <- data_wide %>%
  # Use the 'pivot_longer' function to reshape the dataframe, selecting specific columns to pivot
  pivot_longer(cols = c(ProficiencyScore1, ProficiencyScore2, ProficiencyScore3), # Specify the columns to be pivoted into long format
               names_to = "Test", # Rename the new column holding the original column names as "Test"
               values_to = "Score") %>% # Rename the new column holding the values from the original columns as "Score" 
  select(ID, Test, Score) # Only select three columns to display
  
head(data_long, n = 10)

## # A tibble: 10 × 3
##       ID Test              Score
##    <int> <chr>             <int>
##  1     1 ProficiencyScore1    99
##  2     1 ProficiencyScore2   107
##  3     1 ProficiencyScore3    88
##  4     2 ProficiencyScore1    56
##  5     2 ProficiencyScore2    59
##  6     2 ProficiencyScore3    54
##  7     3 ProficiencyScore1    62
##  8     3 ProficiencyScore2    69
##  9     3 ProficiencyScore3    53
## 10     4 ProficiencyScore1   102

data_long1 <- data_long %>%
  mutate(Test_rename = str_extract(Test, "\\d+")) # Use 'mutate' to create a new column 'Test_rename' by extracting the numeric part from the 'Test' column using regular expressions

head(data_long1, n = 10)  

## # A tibble: 10 × 4
##       ID Test              Score Test_rename
##    <int> <chr>             <int> <chr>      
##  1     1 ProficiencyScore1    99 1          
##  2     1 ProficiencyScore2   107 2          
##  3     1 ProficiencyScore3    88 3          
##  4     2 ProficiencyScore1    56 1          
##  5     2 ProficiencyScore2    59 2          
##  6     2 ProficiencyScore3    54 3          
##  7     3 ProficiencyScore1    62 1          
##  8     3 ProficiencyScore2    69 2          
##  9     3 ProficiencyScore3    53 3          
## 10     4 ProficiencyScore1   102 1

2. Pivoting Columns with Multiple Variables:

We can use pivot_longer() to separate these mixed variables into individual ones: Metric and Year.

# Read in the dataset
data_weather <- read.csv("data_weather.csv", header = T)

# Look at what the dataset looks like
kable(data_weather, format = "html", booktabs = TRUE, 
      digits = 2, escape = F, row.names = FALSE) %>%
  kable_styling()

City	Season	Temperature_2020	Rainfall_2020	Temperature_2021	Rainfall_2021	Temperature_2022	Rainfall_2022
New York	Summer	77	47	79	50	78	45
New York	Winter	32	30	31	29	33	28
Los Angeles	Summer	75	10	76	12	74	11
Los Angeles	Winter	48	20	46	18	47	21
Chicago	Summer	81	45	82	48	80	46
Chicago	Winter	20	22	22	24	21	23
Miami	Summer	89	30	88	28	90	32
Miami	Winter	66	12	68	10	67	11
Dallas	Summer	95	20	93	18	94	19
Dallas	Winter	38	28	36	25	37	27

# Pivot the dataset
weather_data_long <- data_weather %>% 
  pivot_longer(
    cols = -c(City, Season),  # Specify the columns to be pivoted, excluding 'City' and 'Season'
    names_to = c("Metric", "Year"), # Split original column names into two new columns: 'Metric' and 'Year' 
    names_pattern = "(.*)_(\\d+)", # Use regex to specify the pattern for splitting original column names
    values_to = "Value" # Rename the new column holding the values from the original columns as 'Value'
  )

head(weather_data_long)

## # A tibble: 6 × 5
##   City     Season Metric      Year  Value
##   <chr>    <chr>  <chr>       <chr> <int>
## 1 New York Summer Temperature 2020     77
## 2 New York Summer Rainfall    2020     47
## 3 New York Summer Temperature 2021     79
## 4 New York Summer Rainfall    2021     50
## 5 New York Summer Temperature 2022     78
## 6 New York Summer Rainfall    2022     45

pivot_wider()

pivot_wider() is used to convert long-format data to wide-format data, often needed for data reporting or specific types of analysis

1. Basic Usage Here’s a simple example where we turn columns ProficiencyScore1, ProficiencyScore2, and ProficiencyScore3 into a pair of new variables, Test and Score

# Take a look at the long format data
head(data_long, n = 15)

## # A tibble: 15 × 3
##       ID Test              Score
##    <int> <chr>             <int>
##  1     1 ProficiencyScore1    99
##  2     1 ProficiencyScore2   107
##  3     1 ProficiencyScore3    88
##  4     2 ProficiencyScore1    56
##  5     2 ProficiencyScore2    59
##  6     2 ProficiencyScore3    54
##  7     3 ProficiencyScore1    62
##  8     3 ProficiencyScore2    69
##  9     3 ProficiencyScore3    53
## 10     4 ProficiencyScore1   102
## 11     4 ProficiencyScore2    97
## 12     4 ProficiencyScore3    98
## 13     5 ProficiencyScore1    99
## 14     5 ProficiencyScore2    87
## 15     5 ProficiencyScore3    79

# Using pivot_wider to convert from long to wide format
data_wide <- data_long %>%
  pivot_wider(
    names_from = Test, # Use the values in the 'Test' column to create new column names in the wider format
    values_from = Score # Use the values in the 'Score' column to populate the new columns
  )

# Display the wide format
head(data_wide, n = 10)

## # A tibble: 10 × 4
##       ID ProficiencyScore1 ProficiencyScore2 ProficiencyScore3
##    <int>             <int>             <int>             <int>
##  1     1                99               107                88
##  2     2                56                59                54
##  3     3                62                69                53
##  4     4               102                97                98
##  5     5                99                87                79
##  6     6                94                83                83
##  7     7                74                87                94
##  8     8                72                78                72
##  9     9                80                78                93
## 10    10                69                60                72

2. Using pivot_wider() with multiple variables:

From our previous example using weather data, we learned that our long-format data set contains rainfall and temperature across multiple years. Suppose we are interested in calculating the annual average for each of these metrics, we would need to transform the data into a wide-format structure.

# Look at what the data looks like:
head(weather_data_long) 

## # A tibble: 6 × 5
##   City     Season Metric      Year  Value
##   <chr>    <chr>  <chr>       <chr> <int>
## 1 New York Summer Temperature 2020     77
## 2 New York Summer Rainfall    2020     47
## 3 New York Summer Temperature 2021     79
## 4 New York Summer Rainfall    2021     50
## 5 New York Summer Temperature 2022     78
## 6 New York Summer Rainfall    2022     45

weather_data_wide <- weather_data_long %>%
  # Use the 'pivot_wider' function to reshape the dataframe
  pivot_wider(
    names_from = c(Metric, Year),  # Combine values from the 'Metric' and 'Year' columns to create new column names
    names_glue = "{Metric}_{Year}",  # Specify the format of the new column names using a glue string
    values_from = Value  # Use the values in the 'Value' column to populate the new columns
  )

head(weather_data_wide)

## # A tibble: 6 × 8
##   City      Season Temperature_2020 Rainfall_2020 Temperature_2021 Rainfall_2021
##   <chr>     <chr>             <int>         <int>            <int>         <int>
## 1 New York  Summer               77            47               79            50
## 2 New York  Winter               32            30               31            29
## 3 Los Ange… Summer               75            10               76            12
## 4 Los Ange… Winter               48            20               46            18
## 5 Chicago   Summer               81            45               82            48
## 6 Chicago   Winter               20            22               22            24
## # ℹ 2 more variables: Temperature_2022 <int>, Rainfall_2022 <int>

weather_data_wide_avg <- weather_data_wide %>%
  # Use 'rowwise()' to indicate that the following operations will be applied to each row
  rowwise() %>%
  # Calculate the average temperature for each row based on columns starting with "Temperature"
  mutate(temperature_avg = mean(c_across(starts_with("Temperature"))),
  # Calculate the average rainfall for each row based on columns starting with "Rainfall"
         rainfall_avg = mean(c_across(starts_with("Rainfall"))))

kable(weather_data_wide_avg, format = "html", booktabs = TRUE, 
      digits = 2, escape = F, row.names = FALSE) %>%
  kable_styling() 

City	Season	Temperature_2020	Rainfall_2020	Temperature_2021	Rainfall_2021	Temperature_2022	Rainfall_2022	temperature_avg	rainfall_avg
New York	Summer	77	47	79	50	78	45	78	47.33
New York	Winter	32	30	31	29	33	28	32	29.00
Los Angeles	Summer	75	10	76	12	74	11	75	11.00
Los Angeles	Winter	48	20	46	18	47	21	47	19.67
Chicago	Summer	81	45	82	48	80	46	81	46.33
Chicago	Winter	20	22	22	24	21	23	21	23.00
Miami	Summer	89	30	88	28	90	32	89	30.00
Miami	Winter	66	12	68	10	67	11	67	11.00
Dallas	Summer	95	20	93	18	94	19	94	19.00
Dallas	Winter	38	28	36	25	37	27	37	26.67

Join functions

The join functions in dplyr allow for the combination of two datasets based on shared variables. Different types of joins achieve different objectives, and today we’ll focus on:

• left_join()
• full_join()
• inner_join()
• anti_join()

1. Creating Sample Datasets Let’s consider two simple datasets: one for student grades and another for student contact information.

# Creating dataset for student grades
grades_data <- data.frame(
  StudentID = c(1, 2, 3, 4),
  Grade = c("A", "B", "C", "A")
)

# Creating dataset for student contact information
contact_data <- data.frame(
  StudentID = c(3, 4, 5, 6),
  Email = c("student3@email.com", 
            "student4@email.com", 
            "student5@email.com", 
            "student6@email.com"))

# Display datasets
grades_data

##   StudentID Grade
## 1         1     A
## 2         2     B
## 3         3     C
## 4         4     A

contact_data

##   StudentID              Email
## 1         3 student3@email.com
## 2         4 student4@email.com
## 3         5 student5@email.com
## 4         6 student6@email.com

2. left_join()

The left_join() function keeps all records from the left dataset and adds corresponding records from the right dataset.

# Perform a left join on 'grades_data' and 'contact_data' using the 'StudentID' column as the key
left_joined_data <- left_join(x = grades_data, 
                              y = contact_data, 
                              by = "StudentID")

left_joined_data

##   StudentID Grade              Email
## 1         1     A               <NA>
## 2         2     B               <NA>
## 3         3     C student3@email.com
## 4         4     A student4@email.com

Key Point: Notice that StudentID 1 and StudentID 2 from grades_data are still in the result, even though they do not appear in contact_data.

3. full_join()

The full_join() function retains all unique keys from both datasets.

full_joined_data <- full_join(grades_data, contact_data, by = "StudentID")
full_joined_data

##   StudentID Grade              Email
## 1         1     A               <NA>
## 2         2     B               <NA>
## 3         3     C student3@email.com
## 4         4     A student4@email.com
## 5         5  <NA> student5@email.com
## 6         6  <NA> student6@email.com

Key Point: Both datasets contribute to the unique keys in the output. Records with no matches in the other dataset will have NA values in the new columns.

4. inner_join()

The inner_join() function keeps only the records that have matching keys in both datasets.

inner_joined_data <- inner_join(grades_data, contact_data, by = "StudentID")
inner_joined_data

##   StudentID Grade              Email
## 1         3     C student3@email.com
## 2         4     A student4@email.com

Key Point: Only StudentIDs 3 and 4, which appear in both datasets, are included in the output.

5. anti_join() The anti_join() function keeps only the records from the left dataset that do not have a match in the right dataset.

anti_joined_data <- anti_join(grades_data, contact_data, by = "StudentID")
anti_joined_data

##   StudentID Grade
## 1         1     A
## 2         2     B

Key Point: This function is useful for identifying records in one dataset that do not have corresponding records in another.