🔄 Lecture 5

Data Transformation with R

Byeong-Hak Choe
bchoe@geneseo.edu

SUNY Geneseo

September 29, 2026

📂 Reading a Data Table as a data.frame

📄 CSV Files

  • A CSV (comma-separated values) file is a plain text file where each value is separated by a comma.
    • CSV files are widely used for storing data from spreadsheets and databases.
  • Example

📍 Absolute Pathnames

  • An absolute pathname tells the computer the exact location of a file, starting from the very top folder of your computer.
    • It does not depend on R’s working directory, but it changes if the file is moved or renamed.
  • In R, you can see the working directory — the folder where R is currently “looking” for files — by running getwd() in the Console.
    • In a Posit Cloud, the working directory is /cloud/project/
  • Examples of an absolute pathname for custdata_rev.csv when entered in R:
    • On a Mac:
      /Users/user/documents/data/custdata_rev.csv
    • On Windows:
      C:\\Users\\user\\Documents\\data\\custdata_rev.csv
      • Note: In Windows, we use double backslashes (\\) because a single backslash (\) is treated as a special character in R.

🧭 Relative Pathnames

  • A relative pathname specifies the location of a file relative to the working directory.
  • Examples of a relative pathname for custdata_rev.csv:
    • Absolute pathname:
      /cloud/project/data/custdata_rev.csv
    • Working directory:
      /cloud/project/
    • Relative pathname:
      data/custdata_rev.csv
  • In Posit Cloud, we typically use relative pathnames to access files inside the project folder.

📥 Steps for Reading a CSV File as a data.frame

  1. Download custdata_rev.csv from the Class Files module in Brightspace.
    • The file will usually be saved in your computer’s Downloads folder.
    • Do not open this file in a spreadsheet app (e.g., Excel or Numbers).
  2. In Posit Cloud, create a subfolder named data (Files Pane → + Folder).
  3. Click the data folder, then upload the custdata_rev.csv file into it.
    • At the top of the Files Pane, click Upload to choose and add your file.
  4. Read the file using a relative pathname:
library(tidyverse)
# Read the CSV file from the data subfolder
custdata <- read_csv("data/custdata_rev.csv")
view(custdata)  # Opens a `data.frame` in a spreadsheet-like viewer
  • view(DF) (or View(DF)) opens a DF in a spreadsheet-like viewer.

🌐 Load a CSV File Directly from the Web into R

# Read the CSV file directly from the web (GitHub repo)
custdata_web <- read_csv(
        'https://bcdanl.github.io/data/custdata_rev.csv')
  • We can load a CSV file directly from the web into R.

🧪 Classwork 4: Absolute Pathnames

Practice on your computer

Open Classwork 4: Finding an Absolute Pathname

Download custdata_rev.csv from Brightspace, copy its absolute pathname, and paste it into any text editor.

🔎 Getting to Know a data.frame

class(custdata)
custdata$age
dim(custdata)
nrow(custdata)
ncol(custdata)
summary(custdata)
# Install once
install.packages("skimr")  
library(skimr)
skim(custdata)
  • The $ operator extracts a single column from a data.frame as a vector.
  • dim() shows both the number of rows and columns.
  • nrow() and ncol() give the row count and column count separately.
  • summary() gives a quick overview, while skimr::skim() provides a more detailed, user-friendly summary of variables across all data types.

👥 Observations in data.frame

  • Rows in a data.frame represent individual units or entities for which data is collected.
  • Examples:
    • Student Information: Each row = one student
    • Employee Information: Each row = one employee
    • Daily S&P 500 Index Data: Each row = one trading day
    • Household Survey Data: Each row = one household

🏷️ Variables in data.frame

  • Columns in a data.frame represent attributes or characteristics measured across multiple observations.
  • Examples:
    • Student Data: Name, Age, Grade, Major
    • Employee Data: EmployeeID, Name, Age, Department
    • Customer Data: CustomerID, Name, Age, Income, HousingType

Note

  • In a data.frame, a variable is a column of data.
  • In general programming, a variable is the name of an object.

🧹 Tidy data.frame

Variables, Observations, and Values

table1  # a sample data.frame from the tidyr package (part of the tidyverse)

  • A data.frame is tidy if it follows three rules:

    1. Each variable has its own column.
    2. Each observation has its own row.
    3. Each value has its own cell.

  • A tidy data.frame keeps your data organized, making it easier to understand, analyze, and share in any data analysis.

🔄 Data Transformation with dplyr

🛠️ Data Transformation with dplyr

  • dplyr is a core tidyverse package for data manipulation — tasks like filtering, sorting, selecting, and renaming.
  • Common dplyr functions with data.frame DF:
    • filter(DF, LOGICAL_CONDITIONS)
    • arrange(DF, VARIABLES)
    • distinct(DF, VARIABLES)
    • select(DF, VARIABLES)
    • rename(DF, NEW_NAME = CURRENT_NAME)
  • dplyr functions take a data.frame as the first argument.
    • They return a data.frame as output.

🔗 Making dplyr Code Flow with the Pipe Operator

  • The pipe operator (|> or %>%) makes code easier to read by connecting steps in order.
  • How it works:
    • f(x, y) is the same as x |> f(y).
    • Example with a data frame DF:
      • filter(DF, logical_condition)
      • is the same as
        DF |> filter(logical_condition)
  • You can read the pipe as “then”:
    • Example: Take the data, then filter it

🧩 Data Transformation Functions with the Pipe

  • Common dplyr functions with the pipe operator:
    • DF |> filter(LOGICAL_CONDITIONS)
    • DF |> arrange(VARIABLES)
    • DF |> distinct(VARIABLES)
    • DF |> select(VARIABLES)
    • DF |> rename(NEW_NAME = CURRENT_NAME)
  • Why this works so well:
    • dplyr functions usually take a data.frame as the first argument.
    • They return a data.frame as output.
  • This allows you to chain together multiple steps naturally:
    • Example: Start with data, then filter, then arrange
      \(\qquad\quad\;\;\) DF |> filter(...) |> arrange(...)

☁️ Using the Pipe in Posit Cloud

  • To enable the native pipe operator (|>) in RStudio:
    • Go to Tools > Global Options > Code (side menu).
    • Under Pipe operator, choose Use native pipe operator (|>).
  • Keyboard shortcut for inserting a pipe:
    • Windows: Ctrl + Shift + M
    • Mac: Cmd + Shift + M

🎯 Filter observations with filter()

🔍 Filter Observations with filter()

install.packages("nycflights13")  # Install once
library(nycflights13)
library(tidyverse)

flights <- nycflights13::flights

flights$month == 1  # A logical test returns TRUE or FALSE
class(flights$month == 12)  
# Flights in January or December
jan <- flights |> 
  filter(month == 1)

dec <- flights |> 
  filter(month == 12)
# Flights on January 1st or December 25th
jan1 <- flights |> 
  filter(month == 1, day == 1)

dec25 <- flights |> 
  filter(month == 12, day == 25)
  • filter() keeps only the observations that meet one or more logical conditions.
    • A logical condition evaluates to TRUE (keep the observation) or FALSE (drop the observation).

⚖️ Logical Conditions with Equality and Inequality

  • Here, both V1 and V2 are variables, and the comparisons are applied element-wise (vectorized).
  • For logical conditions using inequalities, we focus on cases where V1 and V2 are integer or numeric.

1️⃣ Logical Conditions - Example 1

df <- data.frame(
  num = c(8, 9, 10, 11),
  chr = c("A", "C", "B", "A")
  )
df |> 
  filter(num > 8)
df |> 
  filter(chr == "A")

🔣 Logical Operators — AND, OR, NOT

  • Here, both x and y are logical conditions/variables.
  • What logical operations (&, |, !) do is combining logical variables/conditions, which returns a logical variable when executed.

🧠 Logical Operations

  • x and y are logical conditions.
    • If x is TRUE, it highlights the left circle.
    • If y is TRUE, it highlights the right circle.

  • The shaded regions show which parts each logical operator selects.

2️⃣ Logical Conditions - Example 2

df <- data.frame(
  num = c(8, 9, 10, 11),
  chr = c("A", "C", "B", "A")
  )
df |> 
  filter(num > 8 & 
         num < 11)
                
df |> 
  filter(num > 8,
         num < 11)
  • In filter(), separating conditions with a comma is equivalent to combining them with the & operator.

3️⃣ Logical Conditions - Example 3

df <- data.frame(
  num = c(8, 9, 10, 11),
  chr = c("A", "C", "B", "A")
  )
df |> 
  filter(num < 10 & 
         chr == "A")

df |> 
  filter(num < 10, 
         chr == "A")

4️⃣ Logical Conditions - Example 4

df <- data.frame(
  num = c(8, 9, 10, 11),
  chr = c("A", "C", "B", "A")
  )
df |> 
  filter(num < 10 | 
         chr == "A")

❓ Missing Values (NA)

NA > 5
10 == NA
NA + 10
NA / 2
(1 + NA + 3) / 3

mean( c(1, NA, 3) )
sd( c(1, NA, 3) )
  • NA (not available) represents a missing or unknown value in R.
  • In most calculations, if one value is unknown, the result will also be unknown (NA).

🆚 Comparing Missing Values

v1 <- NA
v2 <- NA
v1 == v2
  • Suppose v1 is Mary’s age (unknown) and v2 is John’s age (unknown).
  • Can we say they are the same age?
  • Since both values are missing, R cannot know — so the result is also NA.

🔎 Checking for Missing Values with is.na()

num_missing <- NA
is.na(num_missing)  # is num_missing NA?

text_missing <- "missing"
is.na(text_missing) # is text_missing NA?

V1 <- c(1, NA, 3)
is.na(V1)  # is V1 NA?
!is.na(V1)  # is V1 not NA?
df <- data.frame(
  v1 = c(1, NA, 3),
  v2 = c(1, 2, 3)
  )

df |> 
  filter( is.na(v1) )

df |> 
  filter( !is.na(v1) )
  • Use is.na() to test whether a value is missing (NA).
  • In filter(), you can:
    • Use is.na() to keep observations with missing values.
    • Use !is.na() to remove observations with missing values.

↕️ Arrange Observations with arrange()

🔢 Arrange Observations with arrange()

# Sort observations 
  # by dep_delay (ascending)
flights |> 
  arrange(dep_delay)
# Sort by dep_delay, 
  # then by sched_dep_time
flights |> 
  arrange(dep_delay, sched_dep_time)
  • arrange() sorts out observations.
  • When you provide multiple variables, arrange() sorts by the first variable, and then uses the next variable(s) to break ties.

⬇️ Descending Order with desc()

# Sort by departure delay 
  # in descending order
flights |> 
  arrange(desc(dep_delay))
# Equivalent shortcut 
  # for numeric variables
flights |> 
  arrange(-dep_delay)
  • Use desc(VARIABLE) to sort in descending order.
    • For numeric variables, you can also use a leading minus sign.

📅 Arrange Observations with arrange() - Example

df <- data.frame(
  year = c(2024, 2021, 2024, 2024),
  month = c(7, 10, 7, 4),
  day = c(20, 19, 15, 9)
)
df |> 
  arrange(year, month, day)
  • If we provide more than one variable name, each additional variable will be used to break ties in the values of preceding variables.

🆔 Find All Unique Observations with distinct()

🧹 Find All Unique Observations with distinct()

df <- data.frame(
  country = c("USA", "Korea", "USA"),
  city = c("D.C.", "Seoul", "D.C."),
  subregion = c("Georgetown", 
                "Gangnam", 
                "Georgetown") 
  )
# Remove duplicate observations
df |> 
  distinct()
  • distinct() removes duplicate observations.
    • By default, it checks all variables to find unique observations.

🧩 Find All Unique Combinations of Selected Variables

# Remove duplicate observations 
  # from the flights data.frame
flights |> 
  distinct()
# Find all unique 
  #  origin-destination pairs
flights |> 
  distinct(origin, dest)
  • You can pass one or more variables to distinct().
    • This returns unique combinations of just those variables (while ignoring the rest).

🎯 Select Variables with select()

📌 Select Variables with select()

  • It’s not uncommon to get datasets with hundreds or thousands of variables.

  • select() allows us to narrow in on the variables we’re actually interested in.

  • We can select variables by their names.

flights |> 
  select(year, month, day)

➖ Remove Variables with select()

flights |> 
  select(-year)
  • With select(-VARIABLES), we can remove variables.

✏️ Rename Variables with rename()

🏷️ Rename Variables with rename()

flights |> 
  rename( tail_num = tailnum )

  • rename() can be used to rename variables:

    • DF |> rename(NEW_NAME = CURRENT_NAME)