| Read in CSV file |
Import |
mydt <- fread(“myfile.csv”) |
myt <- read_csv(“myfile.csv”) #OR
myt <- vroom::vroom(“myfile.csv”) |
| Import the first x number of rows of a CSV file |
Import |
mydt_x <- fread(“myfile.csv”, nrows = x) |
myt_x <- read_csv(“myfile.csv”, n_max = x) |
| Import only those rows from a CSV file that match a certain pattern |
Import |
mydt_pattern <- fread(“grep ‘mypattern’ myfile.csv”) |
myt_pattern <- vroom::vroom(pipe(“grep ‘mypattern’ myfile.csv”)) |
| Import a .gz compressed file |
Import |
mydt <- fread(“myfile.gz”) |
myt <- vroom::vroom(“myfile.gz”) |
| Import a.zip compressed file |
import |
mydt <- fread(cmd = ‘unzip -cq myfile.zip’) |
myt <- read_csv(“myfile.zip”) |
| Create data table from existing data frame (tibble for tidyverse) |
Import |
mydt <- as.data.table(mydf) #OR
setDT(mydf) |
myt <- as_tibble(mydf) |
| Alter data.table in place without making a copy |
Wrangle |
any function that starts with set such as setkey(mydt, mycol) or using the := operator within brackets |
not applicable |
| Order rows based on multiple column values |
Wrangle |
mydt2 <- mydt[order(colA, -colB)] #OR
setorder(mydt, colA, -colB) |
myt <- arrange(myt, colA, -colB) |
| Rename columns |
Wrangle |
setnames(mydt, old = c(‘colA’,’colB’), new = c(‘NewColA’, ‘NewColB’)) |
myt <- rename(myt, NewColA = colA, NewColB = colB) |
| Reordering columns: Move some columns to the front (left-most) position |
Wrangle |
setcolorder(mydt, c(“colB”, “colC”)) # colB now in position 1 and colC in position 2 |
myt <- relocate(myt, colB, colC) |
| Filter rows for row number n |
Subset |
mydt2 <- mydt[n] |
myt2 <- slice(myt, n) |
| Filter for the last row |
Subset |
mydt2 <- mydt[.N] |
myt2 <- slice(myt, n()) |
| Filter rows by condition |
Subset |
# In some cases setkey(mydt, colA, colB) will speed performance
# for logical tests on colA and colB; same with other columns
mydt2 <- mydt[logical expression] |
myt2 <- filter(myt, logical expression) |
| Filter rows where colA equals string1 or string2 |
Subset |
mydt2 <- mydt[colA %chin% c(“string1”, “string2”)] |
myt2 <- filter(myt, colA %in% c(“string1”, “string2”)) |
| Filter rows where colA matches a regular expression |
Subset |
mydt2 <- mydt[colA %like% “mypattern”] |
myt2 <- filter(myt, stringr::str_detect(colA, “mypattern”)) |
| Filter rows where colA values are between 2 numbers |
Subset |
mydt2 <- mydt[colA %between% c(n1, n2)] |
myt2 <- filter(myt, between(colA, n1, n2)) |
| Filter for first n rows by group |
Subset |
mydt2 <- mydt[, .SD[1:n], by = groupcol] |
myt2 <- myt %>%
group_by(groupcol) %>%
slice(1:n) |
| Filter rows for maximum value by group |
Subset |
mydt2 <- mydt[, .SD[which.max(valcol)], by = groupcol] |
myt2 <- myt %>%
group_by(groupcol) %>%
filter(valcol == max(valcol)) |
| Select column and return results as a vector |
Subset |
myvec <- mydt[, colname] |
myvec <- pull(myt, colname) |
| Select multiple columns to create a new data.table (data frame or tibble for tidyverse) |
Subset |
mydt2 <- mydt[, list(colA, colB)] #OR
mydt2 <- mydt[, .(colA, colB)] #OR
mydt2 <- mydt[, c(“colA”, “colB”)] |
myt2 <- select(myt, colA, colB) |
| Select multiple columns using a variable containing the column names |
Subset |
my_col_names <- c(“colA”, “colB”)
mydt2 <- mydt[, ..my_col_names] #OR
mydt2 <- mydt[, my_col_names, with = FALSE] |
my_col_names <- c(“colA”, “colB”)
myt2 <- select(myt, all_of(my_col_names)) |
| Select multiple columns and rename some |
Subset |
mydt2 <- mydt[, .(newname1 = col1, newname2 = col2, col3)] |
myt2 <- select(myt, newname1 = col1, newname2 = col2, col3) |
| Exclude multiple columns |
Subset |
mydt2 <- mydt[, -c(“colA”, “colB”)] #OR
mydt2 <- mydt[, !c(“colA”, “colB”)] #OR
my_col_names <- c(“colA”, “colB”)
mydt2 <- mydt[, !..my_col_names] |
myt2 <- select(myt, -c(colA, colB)) #OR
my_col_names <- c(“colA”, “colB”)
myt2 <- select(myt, -{{my_col_names}}) |
| Remove duplicate rows based on values in multiple columns |
Subset |
mydt2 <- unique(mydt, by = c(“colA”, “colB”)) |
myt2 <- distinct(myt, colA, colB, .keep_all = TRUE) |
| Count unique rows based on multiple columns |
Summarize |
uniqueN(mydt, by = c(“colA”, “colB”)) |
nrow(distinct(myt, colA, colB)) |
| Run summary calculations on data |
Summarize |
mydt2 <- mydt[, myfun(colA …)] |
myt2 <- myt %>%
summarise(
ColName = myfun(colA …)
) |
| Run summary calculations on data by one group |
Summarize |
mydt2 <- mydt[, myfun(colA …), by = groupcol] |
myt2 <- myt %>%
group_by(groupcol) %>%
summarise(
NewCol = myfun(colA…)
) |
| Run summary calculations on data by one group and name new column |
Summarize |
mydt2 <- mydt[, .(MyNewCol = myfun(colA…)), by = groupcol] |
myt2 <- myt %>%
group_by(groupcol) %>%
summarise(
NewCol = myfun(colA…)
) |
| Run summary calculations on data by multiple groups |
Summarize |
mydt2 <- mydt[, myfun(colA …), by = .(groupcol1, groupcol2)] |
myt2 <- myt %>%
group_by(groupcol1, groupcol2) %>%
summarise(
NewCol = myfun(colA…)
) |
| Run summary calculation on filtered data by multiple groups |
Summarize |
mydt2 <- mydt[filter expression, myfun(colA), by = .(groupcol1, groupcol2)] |
myt2 <- myt %>%
filter(filter expression) %>%
group_by(groupcol1, groupcol2) %>%
summarise(
NewCol = myfun(colA), .groups = “keep”
) |
| Count number of rows by groups |
Summarize |
mydt2 <- mydt[,.N, by = groupcol] #for one group #OR
mydt2 <- mydt[, .N, by = .(groupcol1, groupcol2)] |
myt2 <- count(myt, groupcol) #for one group #OR
myt2 <- count(myt, groupcol1, groupcol2) |
| Summarize multiple columns and return results in multiple columns |
Summarize |
mydt2 <- mydt[, lapply(.SD, myfun),
.SDcols = c(“colA”, “colB”)] |
myt2 <- myt %>%
summarise(
across(c(colA, colB), myfun)
) |
| Summarize multiple columns by group and return results in multiple columns |
Summarize |
mydt2 <- mydt[, lapply(.SD, myfun),
.SDcols = c(“colA”, “colB”), by = groupcol] |
myt2 <- myt %>%
group_by(groupcol) %>%
summarise( across(c(colA, colB), myfun) ) |
| Add a column |
Calculate |
mydt[, MyNewCol := myfun(colA)] |
myt <- myt %>%
mutate(
MyNewCol = myfun(colA)
) |
| Add multiple columns at once |
Calculate |
# use any function or expression
mydt[, `:=`(NewCol1 = myfun(colA),
NewCol2 = colB + colC )] #OR
mydt[, c(“NewCol1”, “newCol2”) := list(myfun(colA), colB + colC)] |
myt <- myt %>%
mutate(
MyNewCol1 = myfun(colA),
MyNewCol2 = colB + colC
) |
| Add column using current and previous values from another column, such as finding the difference between value on a date vs. the prior date |
Calculate |
mydt[, Diff := colA – shift(colA)] |
myt <- mutate(myt, Diff = colA – lag(colA)) |
| Add column referencing previous value of a column by a group |
Calculate |
mydt2 <- mydt[, Diff := colA – shift(colA), by = groupcol] |
myt2 <- myt %>%
group_by(groupcol) %>%
mutate(
Diff = colA – lag(colA)
) |
| Add column with row ID numbers by group |
Calculate |
mydt[, myid := 1:.N, by = groupcol] |
myt <- myt %>%
group_by(groupcol) %>%
mutate(
myid = row_number()
) |
| Add column based on several conditions without using multiple if else statements (like SQL’s CASE) |
Calculate |
# Needs data.table version 1.13 or later
# I like each condition on a new line but that’s not required
mydt2 <- mydt[, NewCol := fcase(
condition1, “Value1”,
condition2, “Value2”,
condition3, “Value3”,
default = “Other” # value for all else
)] |
myt2 <- myt %>%
mutate(
NewCol = case_when(
condition1 ~ “Value1”,
condition2 ~ “Value2”,
condition3 ~ “Value3”,
TRUE ~ “Other”
)
) |
| Add column via operating by row |
Calculate |
mydt[, newcol := myfun(colB, colC, colD), by = 1:nrow(mydt)]
# or if colA has all unique values
mydt[, newcol := myfun(colB, colC, colD), by = colA] |
myt <- myt %>%
rowwise() %>%
mutate(
newcol = myfun(colB, colC, colD)
)
# or
myt <- myt %>%
rowwise() %>%
mutate(
#use dplyr select syntax:
newcol = myfun(c_across(colB:colD))
) |
| Join two data sets by more than one column; keep all in set1 but only matches in set2 |
Join |
mydt <- dt2[dt1, on = c(“dt2col” = “dt1col”)] #OR
mydt <- merge(dt1, dt2, by.x = “dt1col”, by.y = “dt2col”, all.x = TRUE) #OR
setkey(dt1, “dt1col”)
setkey(dt2, “dt2col”)
mydt <- dt2[dt1] |
myt <- left_join(df1, df2, by = c(“df1col” = “df2col”)) |
| Join 2 data sets by more than one column – keep all in set1 but only matches in set2 |
Join |
mydt <- merge(dt1, dt2, by.x = c(“dt1colA”, “dt1colB”), by.y = c(“dt2colA”, “dt2colB”), all.x = TRUE, all.y = FALSE) #OR
setkey(dt1, dt1colA, dt1colB)
setkey(dt2, dt2colA, dt2colB)
mydt <- dt2[dt1] |
myt <- left_join(df1, df2, by = c(“df1colA” = “df2colA”, “df1colB” = “df2colB”)) |
| Join two data sets by one common column; only keep matches |
Join |
mydt <- merge(dt1, dt2, by.x = “dtcol1”, by.y = “dtcol2”) |
myt <- inner_join(df1, df2, by = c(“df1col” = “df2col”)) |
| Join two data sets by one common column and keep all data in both sets, whether or not there are matches |
Join |
mydt <- merge(dt1, dt2, by.x = “dtcol1”, by.y = “dtcol2”, all = TRUE) |
myt <- full_join(df1, df2, by = c(“df1col” = “df2col”)) |
| Combine two data sets by adding rows from one to the bottom of another |
Join |
mydt_joined <- rbindlist(list(mydt, mydt2)) |
myt_joined <- bind_rows(myt, myt2) |
| Reshape data wide to long |
Reshape |
mydt_long <- melt(mydt, measure.vars = c(“col1”, “col2”, “col3”), variable.name = “NewCategoryColName”, value.name = “NewValueColName”) |
myt_long <- pivot_longer(myt, cols = starts_with(“col”), names_to = “NewCategoryColName”, values_to = “NewValueColName”) |
| Reshape data long to wide |
Reshape |
mydt_wide <- dcast(mydt, id_col1 ~ col1 , value.var = “ValueColName”) |
myt_wide <- pivot_wider(myt, names_from = col1, values_from =ValueColName) |
| Chain multiple expressions |
Wrangle |
mydt[expr1][expr2] |
myt <- myt %>%
expr1 %>%
expr2 |
| Export data to a CSV file |
Export |
fwrite(mydt, “myfile.csv”) |
write_csv(myt, “myfile.csv”) |
| Append rows to an existing CSV file |
Export |
fwrite(mydt2, “myfile.csv”, append = TRUE) |
vroom::vroom_write(myt2, “myfile.csv”, delim = “,”, append = TRUE) |
| Export data to a compressed CSV file |
Export |
fwrite(mydt, “myfile.csv.gz”, compress = “gzip”) |
vroom::vroom_write(myt, “myfile2.csv.gz”) |
![Do More With R [video teaser/video series] - R Programming Guide - Tips & Tricks](https://www.infoworld.com/wp-content/uploads/2024/06/do_more_with_r_teaser-100758232-orig.jpg?quality=50&strip=all&w=1024)
