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”)