Data Manipulation with dplyr

## Learning Objectives - Master dplyr verbs for data manipulation - Use pipe operator effectively - Work with grouped data - Handle multiple tables ## Introduction to dplyr ### What is dplyr? dplyr is a grammar of data manipulation providing consistent verbs for: - Selecting columns - Filtering rows - Arranging rows - Creating new columns - Summarizing data ### Installation ```r install.packages("dplyr") library(dplyr) ``` ## The Pipe Operator ### What is %>% The pipe operator passes the left side as the first argument to the right side: ```r # Without pipe head(filter(df, age > 25), 3) # With pipe df %>% filter(age > 25) %>% head(3) ``` ### How it Works ```r # x %>% f(y) becomes f(x, y) # x %>% f(y) %>% g(z) becomes g(f(x, y), z) # This makes code readable left-to-right df %>% filter(age > 25) %>% select(name, age) %>% arrange(age) ``` ## select() - Choose Columns ### Basic Selection ```r df <- data.frame( name = c("Alice", "Bob", "Charlie"), age = c(25, 30, 35), score = c(95.5, 87.3, 92.1), group = c("A", "B", "A") ) # Select specific columns select(df, name, age) # Exclude columns select(df, -score) # Exclude multiple select(df, -score, -group) ``` ### Helper Functions ```r # Select columns by pattern select(df, starts_with("a")) # columns starting with "a" select(df, ends_with("e")) # columns ending with "e" select(df, contains("ou")) # columns containing "ou" # Select columns by type select_if(df, is.numeric) # Rename during select select(df, full_name = name, age) ``` ## filter() - Choose Rows ### Basic Filtering ```r # Single condition filter(df, age > 28) # Multiple conditions (AND) filter(df, age > 28 & score > 90) filter(df, age > 28, score > 90) # Same as above # OR condition filter(df, age < 25 | age > 32) # NOT condition filter(df, !age > 28) filter(df, age <= 28) ``` ### Comparison Operators ```r # == Equal filter(df, group == "A") # != Not equal filter(df, group != "A") # >, >=, <, <= filter(df, score >= 90) # %in% for multiple matches filter(df, name %in% c("Alice", "Bob")) # between for range filter(df, between(age, 25, 32)) # age >= 25 & age <= 32 ``` ### Filtering with NA ```r # Rows where age is NA filter(df, is.na(age)) # Rows where age is NOT NA filter(df, !is.na(age)) ``` ## arrange() - Sort Rows ### Basic Sorting ```r # Ascending order arrange(df, age) # Descending order arrange(df, desc(age)) # Multiple columns arrange(df, group, age) # Group first, then descending arrange(df, group, desc(age)) ``` ## mutate() - Add Columns ### Basic Mutation ```r # Add new column mutate(df, avg_score = score / 10) # Multiple mutations mutate(df, avg_score = score / 10, pass = score > 60 ) ``` ### Common Functions ```r # Cumulative sums mutate(df, cum_age = cumsum(age)) # Lag and lead mutate(df, prev_age = lag(age), next_age = lead(age) ) # Rank mutate(df, rank = rank(desc(score))) # Rolling averages mutate(df, rolling_avg = (age + lag(age) + lead(age)) / 3) ``` ### If-else in mutate ```r mutate(df, grade = case_when( score >= 90 ~ "A", score >= 80 ~ "B", score >= 70 ~ "C", TRUE ~ "F" ) ) ``` ## transmute() - Create and Replace ```r # transmute keeps only the new columns transmute(df, name, avg_score = score / 10 ) ``` ## summarize() - Aggregate ### Basic Summary ```r summarize(df, mean_age = mean(age), max_score = max(score), count = n() ) ``` ### Summary Functions ```r # n() - count rows summarize(df, n()) # n_distinct() - count unique summarize(df, n_group = n_distinct(group)) # sum(), mean(), median(), sd(), var() summarize(df, total = sum(score), average = mean(score) ) # min(), max(), first(), last() summarize(df, youngest = min(age), oldest = max(age) ) # quantile summarize(df, q25 = quantile(score, 0.25)) ``` ## group_by() - Group Data ### Basic Grouping ```r df <- data.frame( group = c("A", "A", "B", "B", "A"), name = c("Alice", "Bob", "Charlie", "Diana", "Eve"), score = c(95, 87, 92, 88, 90) ) # Group by single column df %>% group_by(group) %>% summarize(mean_score = mean(score)) # Group by multiple columns df %>% group_by(group, name) %>% summarize(mean_score = mean(score)) ``` ### Grouped Operations ```r # Add group statistics df %>% group_by(group) %>% mutate(group_mean = mean(score)) %>% select(name, score, group_mean) # Filter within groups df %>% group_by(group) %>% filter(score == max(score)) ``` ### Ungroup ```r df %>% group_by(group) %>% ungroup() %>% mutate(all_mean = mean(score)) ``` ## count() - Quick Counting ```r # Count by group count(df, group) # Count with sort count(df, group, sort = TRUE) # Count with multiple groups count(df, group, name) ``` ## distinct() - Unique Rows ```r # Remove duplicates distinct(df) # By specific columns distinct(df, group, .keep_all = TRUE) # Count unique distinct(df, group) %>% nrow() # or n_distinct(df$group) ``` ## joins - Combine Tables ### Types of Joins ```r # Sample dataframes df1 <- data.frame( id = c(1, 2, 3), name = c("Alice", "Bob", "Charlie") ) df2 <- data.frame( id = c(2, 3, 4), score = c(87, 92, 88) ) ``` ### Inner Join ```r # Keep only matching rows inner_join(df1, df2, by = "id") # id name score # 2 Bob 87 # 3 Charlie 92 ``` ### Left Join ```r # Keep all from df1 left_join(df1, df2, by = "id") # id name score # 1 Alice NA # 2 Bob 87 # 3 Charlie 92 ``` ### Right Join ```r # Keep all from df2 right_join(df1, df2, by = "id") # id name score # 2 Bob 87 # 3 Charlie 92 # 4 88 ``` ### Full Join ```r # Keep all from both full_join(df1, df2, by = "id") ``` ### Other Joins ```r # Semi join - keep df1 rows that match in df2 semi_join(df1, df2, by = "id") # Anti join - keep df1 rows that DON'T match anti_join(df1, df2, by = "id") ``` ## across() - Multiple Columns ### Apply to Multiple Columns ```r # Apply function to all numeric columns df %>% summarise(across(where(is.numeric), mean)) # Apply to specific columns df %>% summarise(across(c(age, score), mean)) # With naming df %>% summarise(across(c(age, score), list(mean = mean, sd = sd))) ``` ### in filter() and mutate() ```r # Filter where any numeric column > 50 df %>% filter(across(where(is.numeric), ~ .x > 50)) # Mutate multiple columns df %>% mutate(across(age:score, ~ .x * 2)) ``` ## rowwise() - Row Operations ```r # Operations by row df <- data.frame( a = c(1, 2, 3), b = c(4, 5, 6) ) df %>% rowwise() %>% mutate(sum = sum(a, b)) ``` ## Case When ```r # Multiple conditions df %>% mutate(grade = case_when( score >= 90 ~ "A", score >= 80 ~ "B", score >= 70 ~ "C", score >= 60 ~ "D", TRUE ~ "F" )) ``` ## Summary - Use `%>%` pipe for readable code flow - `select()` chooses columns, `filter()` chooses rows - `arrange()` sorts, `mutate()` creates/transforms columns - `summarize()` aggregates, `group_by()` enables group operations - `count()` quick counting, `distinct()` unique values - `join` functions combine tables: inner, left, right, full - `across()` applies functions to multiple columns - `rowwise()` for row-wise operations - `case_when()` for multiple if-else conditions