Module lessons (2/2)
Mutations and Aggregations
Beyond selecting and filtering, dplyr offers powerful capabilities to create new calculated columns and summarize data by aggregating it into groups.
Creating New Columns: mutate()
The mutate() verb allows you to calculate new columns or modify existing ones, while keeping all original columns intact.
# Adds a 'double_salary' column computed as 'salary' multiplied by 2
df %>%
mutate(double_salary = salary * 2)
# You can create multiple columns at once
df %>%
mutate(
total_cost = quantity * price,
tax = total_cost * 0.22
)
Grouping and Aggregating: group_by() and summarise()
Often, data analysis requires calculating summary statistics (e.g. mean, sum, count) for specific groups of rows (for instance, calculating the average salary for each department).
1. group_by()
Groups the data frame by the values of one or more columns. By itself, it does not change the visual appearance of the data, but it signals to dplyr that subsequent operations should be executed "group by group".
2. summarise() (or summarize())
Collapses many rows into a single summary row by applying statistical functions like mean(), sum(), min(), max(), or n() (which counts rows).
# Calculates the average salary per department
df %>%
group_by(department) %>%
summarise(mean_salary = mean(salary))
If you want to count how many records belong to each group:
# Counts how many employees are in each city
df %>%
group_by(city) %>%
summarise(count = n())
Try it yourself
Exercise 1: Create a calculated column
Given the data frame df, create a new column called total_price by multiplying the quantity column by the price column using mutate(). Save the result in df_new.
Show hint
Usa mutate(total_price = quantity * price) all'interno di una pipeline su df.
Solution available after 3 attempts
Exercise 2: Group and summarize with the mean
Calculate the mean of the salary column, grouping the records by the department column. Save the aggregated column with the name mean_salary and assign the final result to df_grouped.
Show hint
Usa df %>% group_by(department) %>% summarise(mean_salary = mean(salary))
Solution available after 3 attempts
Exercise 3: Count elements per group
Count the number of rows for each city (city column). Assign the count to the count variable inside summarise() using the n() function, and save the final result in df_counts.
Show hint
Combina group_by(city) con summarise(count = n()) in una pipeline.
Solution available after 3 attempts
Exercise 4: Multiple summaries
Calculate the minimum value (min_price) and maximum value (max_price) of the price column grouping by the category column. Save the result in df_summary.
Show hint
All'interno di summarise(), separa le metriche min_price = min(price) e max_price = max(price) con una virgola.
Solution available after 3 attempts
Exercise 5: Grouping and combined filters
Write a pipeline that groups df by the department column, calculates the mean of salary saving it in the new column avg_salary using mutate() (Note: use mutate, not summarise, to keep all rows), and finally filters to keep only records with salary strictly greater than avg_salary. Save in res.
Show hint
Usa df %>% group_by(department) %>% mutate(avg_salary = mean(salary)) %>% filter(salary > avg_salary)
Solution available after 3 attempts