data.table, base, dplyr, pandas, and polars
This page presents a side-by-side comparison of common data manipulation operations in five idioms: data.table, base, dplyr, polars, and pandas. This allows you to compare syntax and understand how to accomplish tasks across these popular frameworks.
This reference guide covers everything from basic filtering and sorting to advanced operations like joins and reshaping data. Many of these examples were originally crafted by Atrebas. They were then reorganized and augmented with base examples by a team of contributors.
To begin, we create example data. The base R data frame is called DF, the data.table table is called DT, and the dplyr tibble is called TB. Data creation is wrapped in a refresh_data() function, which is called periodically throughout the document to ensure that the data is reset after modifications.
refresh_data = function() {
DT <<- data.table(
V1 = rep(1:2, 5)[-10],
V2 = 1:9,
V3 = c(0.5, 1.0, 1.5),
V4 = rep(LETTERS[1:3], 3)
)
DF <<- data.frame(
V1 = rep(1:2, 5)[-10],
V2 = 1:9,
V3 = c(0.5, 1.0, 1.5),
V4 = rep(LETTERS[1:3], 3)
)
TB <<- tibble(
V1 = rep(1:2, 5)[-10],
V2 = 1:9,
V3 = rep(c(0.5, 1.0, 1.5), 3),
V4 = rep(LETTERS[1:3], 3)
)
}
refresh_data()
The Python tabs use PL and PD for Polars and pandas data frames with the same columns and values:
import numpy as np
import pandas as pd
import polars as pl
def refresh_python():
global PL, PD
data = {
"V1": [1, 2, 1, 2, 1, 2, 1, 2, 1],
"V2": range(1, 10),
"V3": [0.5, 1.0, 1.5] * 3,
"V4": list("ABC") * 3,
}
PL = pl.DataFrame(data)
PD = pd.DataFrame(data)
refresh_python()
When using the let() and set*() functions or := operator modifies a data.table “in place,” which means that it does not copy the object at all. This is more efficient than re-assigning the entire data set. However, when modified in place, the data table will not be printed immediately to the console after modification. You must call the object again to see the changes.
Filter
Integer index
Positive indices keep the specified rows.
DT[3:4,] V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 3 1.5 C
2: 2 4 0.5 ADF[3:4,] V1 V2 V3 V4
3 1 3 1.5 C
4 2 4 0.5 ATB |> slice(3:4)# A tibble: 2 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 3 1.5 C
2 2 4 0.5 APL.slice(2, 2)shape: (2, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
└─────┴─────┴─────┴─────┘PD.iloc[2:4] V1 V2 V3 V4
2 1 3 1.5 C
3 2 4 0.5 ANegative indices exclude the specified rows.
DT[-(3:7),] V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 A
2: 2 2 1.0 B
3: 2 8 1.0 B
4: 1 9 1.5 CDF[-(3:7),] V1 V2 V3 V4
1 1 1 0.5 A
2 2 2 1.0 B
8 2 8 1.0 B
9 1 9 1.5 CTB |> slice(-(3:7))# A tibble: 4 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 1 0.5 A
2 2 2 1 B
3 2 8 1 B
4 1 9 1.5 CPL.filter(~pl.int_range(pl.len()).is_between(2, 6))shape: (4, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘PD.drop(PD.index[2:7]) V1 V2 V3 V4
0 1 1 0.5 A
1 2 2 1.0 B
7 2 8 1.0 B
8 1 9 1.5 CLogical index
Filter rows using a logical vector. Keep the rows where the condition is TRUE. %chin% is a fast version of %in%, optimized for strings. %like% is a convenient operator for regular expression matches.
DT[V2 > 5]
DT[V4 %chin% c("A", "C")] # faster than %in% for strings
DT[V4 %like% c("A|C")] # regular expressions V1 V2 V3 V4
<int> <int> <num> <char>
1: 2 6 1.5 C
2: 1 7 0.5 A
3: 2 8 1.0 B
4: 1 9 1.5 C V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 A
2: 1 3 1.5 C
3: 2 4 0.5 A
4: 2 6 1.5 C
5: 1 7 0.5 A
6: 1 9 1.5 C V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 A
2: 1 3 1.5 C
3: 2 4 0.5 A
4: 2 6 1.5 C
5: 1 7 0.5 A
6: 1 9 1.5 Csubset(DF, V2 > 5)
subset(DF, V4 %in% c("A", "C"))
subset(DF, grepl("A|C", V4)) V1 V2 V3 V4
6 2 6 1.5 C
7 1 7 0.5 A
8 2 8 1.0 B
9 1 9 1.5 C V1 V2 V3 V4
1 1 1 0.5 A
3 1 3 1.5 C
4 2 4 0.5 A
6 2 6 1.5 C
7 1 7 0.5 A
9 1 9 1.5 C V1 V2 V3 V4
1 1 1 0.5 A
3 1 3 1.5 C
4 2 4 0.5 A
6 2 6 1.5 C
7 1 7 0.5 A
9 1 9 1.5 CTB |> filter(V2 > 5)
TB |> filter(V4 %in% c("A", "C"))
TB |> filter(grepl("A|C", V4))# A tibble: 4 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 2 6 1.5 C
2 1 7 0.5 A
3 2 8 1 B
4 1 9 1.5 C# A tibble: 6 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 1 0.5 A
2 1 3 1.5 C
3 2 4 0.5 A
4 2 6 1.5 C
5 1 7 0.5 A
6 1 9 1.5 C# A tibble: 6 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 1 0.5 A
2 1 3 1.5 C
3 2 4 0.5 A
4 2 6 1.5 C
5 1 7 0.5 A
6 1 9 1.5 CPL.filter(pl.col("V2") > 5)
PL.filter(pl.col("V4").is_in(["A", "C"]))
PL.filter(pl.col("V4").str.contains("A|C"))shape: (4, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘shape: (6, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘shape: (6, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘PD.loc[PD["V2"] > 5]
PD.loc[PD["V4"].isin(["A", "C"])]
PD.loc[PD["V4"].str.contains("A|C")] V1 V2 V3 V4
5 2 6 1.5 C
6 1 7 0.5 A
7 2 8 1.0 B
8 1 9 1.5 C V1 V2 V3 V4
0 1 1 0.5 A
2 1 3 1.5 C
3 2 4 0.5 A
5 2 6 1.5 C
6 1 7 0.5 A
8 1 9 1.5 C V1 V2 V3 V4
0 1 1 0.5 A
2 1 3 1.5 C
3 2 4 0.5 A
5 2 6 1.5 C
6 1 7 0.5 A
8 1 9 1.5 CFilter rows based on multiple conditions.
DT[V1 == 1 & V4 == "A"] V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 A
2: 1 7 0.5 Asubset(DF, V1 == 1 & V4 == "A") V1 V2 V3 V4
1 1 1 0.5 A
7 1 7 0.5 ATB |> filter(V1 == 1, V4 == "A")# A tibble: 2 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 1 0.5 A
2 1 7 0.5 APL.filter(pl.col("V1") == 1, pl.col("V4") == "A")shape: (2, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
└─────┴─────┴─────┴─────┘PD.loc[(PD["V1"] == 1) & (PD["V4"] == "A")] V1 V2 V3 V4
0 1 1 0.5 A
6 1 7 0.5 AUnique
unique(DT)
unique(DT, by = c("V1", "V4"))Indices: <V4>, <V4__V1>
V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 A
2: 2 2 1.0 B
3: 1 3 1.5 C
4: 2 4 0.5 A
5: 1 5 1.0 B
6: 2 6 1.5 C
7: 1 7 0.5 A
8: 2 8 1.0 B
9: 1 9 1.5 C V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 A
2: 2 2 1.0 B
3: 1 3 1.5 C
4: 2 4 0.5 A
5: 1 5 1.0 B
6: 2 6 1.5 CDF[!duplicated(DF), ]
DF[!duplicated(DF[c("V1", "V4")]), ] V1 V2 V3 V4
1 1 1 0.5 A
2 2 2 1.0 B
3 1 3 1.5 C
4 2 4 0.5 A
5 1 5 1.0 B
6 2 6 1.5 C
7 1 7 0.5 A
8 2 8 1.0 B
9 1 9 1.5 C V1 V2 V3 V4
1 1 1 0.5 A
2 2 2 1.0 B
3 1 3 1.5 C
4 2 4 0.5 A
5 1 5 1.0 B
6 2 6 1.5 CTB |> distinct()
TB |> distinct(V1, V4, .keep_all = TRUE)# A tibble: 9 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 1 0.5 A
2 2 2 1 B
3 1 3 1.5 C
4 2 4 0.5 A
5 1 5 1 B
6 2 6 1.5 C
7 1 7 0.5 A
8 2 8 1 B
9 1 9 1.5 C# A tibble: 6 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 1 0.5 A
2 2 2 1 B
3 1 3 1.5 C
4 2 4 0.5 A
5 1 5 1 B
6 2 6 1.5 CPL.unique(maintain_order=True)
PL.unique(subset=["V1", "V4"], maintain_order=True)shape: (9, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘shape: (6, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘PD.drop_duplicates()
PD.drop_duplicates(subset=["V1", "V4"]) V1 V2 V3 V4
0 1 1 0.5 A
1 2 2 1.0 B
2 1 3 1.5 C
3 2 4 0.5 A
4 1 5 1.0 B
5 2 6 1.5 C
6 1 7 0.5 A
7 2 8 1.0 B
8 1 9 1.5 C V1 V2 V3 V4
0 1 1 0.5 A
1 2 2 1.0 B
2 1 3 1.5 C
3 2 4 0.5 A
4 1 5 1.0 B
5 2 6 1.5 CMissing values
Drop rows with missing values in specified columns.
na.omit(DT, cols = 1:4)Indices: <V4>, <V4__V1>
V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 A
2: 2 2 1.0 B
3: 1 3 1.5 C
4: 2 4 0.5 A
5: 1 5 1.0 B
6: 2 6 1.5 C
7: 1 7 0.5 A
8: 2 8 1.0 B
9: 1 9 1.5 CDF[complete.cases(DF[, 1:4]), ] V1 V2 V3 V4
1 1 1 0.5 A
2 2 2 1.0 B
3 1 3 1.5 C
4 2 4 0.5 A
5 1 5 1.0 B
6 2 6 1.5 C
7 1 7 0.5 A
8 2 8 1.0 B
9 1 9 1.5 CTB |> tidyr::drop_na(1:4)# A tibble: 9 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 1 0.5 A
2 2 2 1 B
3 1 3 1.5 C
4 2 4 0.5 A
5 1 5 1 B
6 2 6 1.5 C
7 1 7 0.5 A
8 2 8 1 B
9 1 9 1.5 CPL.drop_nulls(subset=["V1", "V2", "V3", "V4"])shape: (9, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘PD.dropna(subset=["V1", "V2", "V3", "V4"]) V1 V2 V3 V4
0 1 1 0.5 A
1 2 2 1.0 B
2 1 3 1.5 C
3 2 4 0.5 A
4 1 5 1.0 B
5 2 6 1.5 C
6 1 7 0.5 A
7 2 8 1.0 B
8 1 9 1.5 CRandom sample
Draw a random sample of rows.
DT[sample(.N, 3)]
DT[sample(.N, .N / 2)] V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 5 1.0 B
2: 2 2 1.0 B
3: 1 7 0.5 A V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 3 1.5 C
2: 2 2 1.0 B
3: 1 5 1.0 B
4: 2 6 1.5 CDF[sample(nrow(DF), 3), ]
DF[sample(nrow(DF), nrow(DF) / 2), ] V1 V2 V3 V4
9 1 9 1.5 C
4 2 4 0.5 A
5 1 5 1.0 B V1 V2 V3 V4
5 1 5 1.0 B
1 1 1 0.5 A
7 1 7 0.5 A
9 1 9 1.5 CTB |> slice_sample(n = 3)
TB |> slice_sample(prop = 0.5)# A tibble: 3 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 2 6 1.5 C
2 2 8 1 B
3 1 1 0.5 A# A tibble: 4 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 3 1.5 C
2 1 1 0.5 A
3 1 7 0.5 A
4 1 9 1.5 CPL.sample(n=3)
PL.sample(fraction=0.5)shape: (3, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
└─────┴─────┴─────┴─────┘shape: (4, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
│ 1 ┆ 1 ┆ 0.5 ┆ A │
└─────┴─────┴─────┴─────┘PD.sample(n=3)
PD.sample(frac=0.5) V1 V2 V3 V4
7 2 8 1.0 B
1 2 2 1.0 B
8 1 9 1.5 C V1 V2 V3 V4
5 2 6 1.5 C
2 1 3 1.5 C
7 2 8 1.0 B
4 1 5 1.0 BOther
DT[V2 %between% c(3, 5)]
DT[data.table::between(V2, 3, 5, incbounds = FALSE)]
DT[V2 %inrange% list(-1:1, 1:3)]subset(DF, grepl("^B", V4))
subset(DF, V2 >= 3 & V2 <= 5)
subset(DF, V2 > 3 & V2 < 5)
subset(DF, V2 %in% c(-1:1, 1:3))TB |> filter(grepl("^B", V4))
TB |> filter(dplyr::between(V2, 3, 5))
TB |> filter(V2 > 3 & V2 < 5)
TB |> filter(V2 >= -1:1 & V2 <= 1:3)PL.filter(pl.col("V4").str.starts_with("B"))
PL.filter(pl.col("V2").is_between(3, 5))
PL.filter(pl.col("V2").is_between(3, 5, closed="none"))shape: (3, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
└─────┴─────┴─────┴─────┘shape: (3, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
└─────┴─────┴─────┴─────┘shape: (1, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 2 ┆ 4 ┆ 0.5 ┆ A │
└─────┴─────┴─────┴─────┘PD.loc[PD["V4"].str.startswith("B")]
PD.loc[PD["V2"].between(3, 5)]
PD.loc[PD["V2"].between(3, 5, inclusive="neither")] V1 V2 V3 V4
1 2 2 1.0 B
4 1 5 1.0 B
7 2 8 1.0 B V1 V2 V3 V4
2 1 3 1.5 C
3 2 4 0.5 A
4 1 5 1.0 B V1 V2 V3 V4
3 2 4 0.5 ASort
Rows
Sort rows in ascending order.
DT[order(V3)] V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 A
2: 2 4 0.5 A
3: 1 7 0.5 A
4: 2 2 1.0 B
5: 1 5 1.0 B
6: 2 8 1.0 B
7: 1 3 1.5 C
8: 2 6 1.5 C
9: 1 9 1.5 Csort_by(DF, ~V3) V1 V2 V3 V4
1 1 1 0.5 A
4 2 4 0.5 A
7 1 7 0.5 A
2 2 2 1.0 B
5 1 5 1.0 B
8 2 8 1.0 B
3 1 3 1.5 C
6 2 6 1.5 C
9 1 9 1.5 CTB |> arrange(V3)# A tibble: 9 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 1 0.5 A
2 2 4 0.5 A
3 1 7 0.5 A
4 2 2 1 B
5 1 5 1 B
6 2 8 1 B
7 1 3 1.5 C
8 2 6 1.5 C
9 1 9 1.5 CPL.sort("V3")shape: (9, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘PD.sort_values("V3") V1 V2 V3 V4
0 1 1 0.5 A
3 2 4 0.5 A
6 1 7 0.5 A
1 2 2 1.0 B
7 2 8 1.0 B
4 1 5 1.0 B
2 1 3 1.5 C
5 2 6 1.5 C
8 1 9 1.5 CSort rows in decreasing order.
DT[order(-V3)] V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 3 1.5 C
2: 2 6 1.5 C
3: 1 9 1.5 C
4: 2 2 1.0 B
5: 1 5 1.0 B
6: 2 8 1.0 B
7: 1 1 0.5 A
8: 2 4 0.5 A
9: 1 7 0.5 Asort_by(DF, ~list(-V3)) V1 V2 V3 V4
3 1 3 1.5 C
6 2 6 1.5 C
9 1 9 1.5 C
2 2 2 1.0 B
5 1 5 1.0 B
8 2 8 1.0 B
1 1 1 0.5 A
4 2 4 0.5 A
7 1 7 0.5 ATB |> arrange(desc(V3))# A tibble: 9 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 3 1.5 C
2 2 6 1.5 C
3 1 9 1.5 C
4 2 2 1 B
5 1 5 1 B
6 2 8 1 B
7 1 1 0.5 A
8 2 4 0.5 A
9 1 7 0.5 APL.sort("V3", descending=True)shape: (9, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
└─────┴─────┴─────┴─────┘PD.sort_values("V3", ascending=False) V1 V2 V3 V4
2 1 3 1.5 C
8 1 9 1.5 C
5 2 6 1.5 C
7 2 8 1.0 B
1 2 2 1.0 B
4 1 5 1.0 B
0 1 1 0.5 A
3 2 4 0.5 A
6 1 7 0.5 ASort rows by multiple columns.
DT[order(V1, -V2)] V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 9 1.5 C
2: 1 7 0.5 A
3: 1 5 1.0 B
4: 1 3 1.5 C
5: 1 1 0.5 A
6: 2 8 1.0 B
7: 2 6 1.5 C
8: 2 4 0.5 A
9: 2 2 1.0 Bsort_by(DF, ~list(V1, -V2)) V1 V2 V3 V4
9 1 9 1.5 C
7 1 7 0.5 A
5 1 5 1.0 B
3 1 3 1.5 C
1 1 1 0.5 A
8 2 8 1.0 B
6 2 6 1.5 C
4 2 4 0.5 A
2 2 2 1.0 BTB |> arrange(V1, desc(V2))# A tibble: 9 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 9 1.5 C
2 1 7 0.5 A
3 1 5 1 B
4 1 3 1.5 C
5 1 1 0.5 A
6 2 8 1 B
7 2 6 1.5 C
8 2 4 0.5 A
9 2 2 1 BPL.sort(["V1", "V2"], descending=[False, True])shape: (9, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 9 ┆ 1.5 ┆ C │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 2 ┆ 2 ┆ 1.0 ┆ B │
└─────┴─────┴─────┴─────┘PD.sort_values(["V1", "V2"], ascending=[True, False]) V1 V2 V3 V4
8 1 9 1.5 C
6 1 7 0.5 A
4 1 5 1.0 B
2 1 3 1.5 C
0 1 1 0.5 A
7 2 8 1.0 B
5 2 6 1.5 C
3 2 4 0.5 A
1 2 2 1.0 BSort dataset and the column passed in argument becomes key. Output is an object the same type as the object indexed. This code is not executed to avoid sorting the original data set in place.
setorder(DT, V4, -V1)
setorderv(DT, c("V4", "V1"), c(1, -1))DF = DF[order(DF$V4, -DF$V1), ]TB = TB |> arrange(V4, desc(V1))PL = PL.sort(["V4", "V1"], descending=[False, True])PD = PD.sort_values(["V4", "V1"], ascending=[True, False])refresh_data()refresh_python()Columns
Reorders the columns of a dataset. This code is not executed to avoid reordering columns in the original data set.
setcolorder(DT, c("V4", "V1", "V2"))
DT V4 V1 V2 V3
<char> <int> <int> <num>
1: A 1 1 0.5
2: B 2 2 1.0
3: C 1 3 1.5
4: A 2 4 0.5
5: B 1 5 1.0
6: C 2 6 1.5
7: A 1 7 0.5
8: B 2 8 1.0
9: C 1 9 1.5DF = DF[, c("V4", "V1", "V2")]
DF V4 V1 V2
1 A 1 1
2 B 2 2
3 C 1 3
4 A 2 4
5 B 1 5
6 C 2 6
7 A 1 7
8 B 2 8
9 C 1 9TB = TB |> select(V4, V1, V2)
TB# A tibble: 9 × 3
V4 V1 V2
<chr> <int> <int>
1 A 1 1
2 B 2 2
3 C 1 3
4 A 2 4
5 B 1 5
6 C 2 6
7 A 1 7
8 B 2 8
9 C 1 9PL = PL.select("V4", "V1", "V2")PD = PD[["V4", "V1", "V2"]]refresh_data()refresh_python()Select
Keep
Extract one column as a vector.
# DT[["V3"]]
# DT[, V3]
DT[[3]][1] 0.5 1.0 1.5 0.5 1.0 1.5 0.5 1.0 1.5# DF[["V3"]]
# DF[, 3, drop = TRUE]
DF[[3]][1] 0.5 1.0 1.5 0.5 1.0 1.5 0.5 1.0 1.5# TB[["V3"]]
# TB |> pull(V3)
TB[[3]][1] 0.5 1.0 1.5 0.5 1.0 1.5 0.5 1.0 1.5PL.get_column("V3")shape: (9,)
Series: 'V3' [f64]
[
0.5
1.0
1.5
0.5
1.0
1.5
0.5
1.0
1.5
]PD["V3"]0 0.5
1 1.0
2 1.5
3 0.5
4 1.0
5 1.5
6 0.5
7 1.0
8 1.5
Name: V3, dtype: float64Extract one column as a data frame.
# DT[, .SD, .SDcols = "V3"]
DT[, "V3"] V3
<num>
1: 0.5
2: 1.0
3: 1.5
4: 0.5
5: 1.0
6: 1.5
7: 0.5
8: 1.0
9: 1.5# DF[, "V3", drop = FALSE]
DF[, 3, drop = FALSE] V3
1 0.5
2 1.0
3 1.5
4 0.5
5 1.0
6 1.5
7 0.5
8 1.0
9 1.5# TB |> select(V3)
TB[, "V3"]# A tibble: 9 × 1
V3
<dbl>
1 0.5
2 1
3 1.5
4 0.5
5 1
6 1.5
7 0.5
8 1
9 1.5PL.select("V3")shape: (9, 1)
┌─────┐
│ V3 │
│ --- │
│ f64 │
╞═════╡
│ 0.5 │
│ 1.0 │
│ 1.5 │
│ 0.5 │
│ 1.0 │
│ 1.5 │
│ 0.5 │
│ 1.0 │
│ 1.5 │
└─────┘PD[["V3"]] V3
0 0.5
1 1.0
2 1.5
3 0.5
4 1.0
5 1.5
6 0.5
7 1.0
8 1.5Select several columns by column names.
DT[, .(V2, V3, V4)]
DT[, V2:V4]
DT[, .SD, .SDcols = V2:V4]
DT[, .SD, .SDcols = c("V2", "V3", "V4")]
cols = c("V2", "V3")
DT[, ..cols]DF[, c("V2", "V3", "V4")]
subset(DF, select = c("V2", "V3", "V4"))
cols = c("V2", "V3")
DF[, cols]
DF[ , names(DF) %in% cols]TB |> select(V2, V3, V4)
TB |> select(V2:V4)
TB |> select(any_of(c("V2", "V3", "V4")))
cols = c("V2", "V3")
DF |> select(!!cols)PL.select("V2", "V3", "V4")
cols = ["V2", "V3"]
PL.select(cols)shape: (9, 3)
┌─────┬─────┬─────┐
│ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- │
│ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╡
│ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 1.0 ┆ B │
│ 3 ┆ 1.5 ┆ C │
│ 4 ┆ 0.5 ┆ A │
│ 5 ┆ 1.0 ┆ B │
│ 6 ┆ 1.5 ┆ C │
│ 7 ┆ 0.5 ┆ A │
│ 8 ┆ 1.0 ┆ B │
│ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┘shape: (9, 2)
┌─────┬─────┐
│ V2 ┆ V3 │
│ --- ┆ --- │
│ i64 ┆ f64 │
╞═════╪═════╡
│ 1 ┆ 0.5 │
│ 2 ┆ 1.0 │
│ 3 ┆ 1.5 │
│ 4 ┆ 0.5 │
│ 5 ┆ 1.0 │
│ 6 ┆ 1.5 │
│ 7 ┆ 0.5 │
│ 8 ┆ 1.0 │
│ 9 ┆ 1.5 │
└─────┴─────┘PD[["V2", "V3", "V4"]]
cols = ["V2", "V3"]
PD[cols] V2 V3 V4
0 1 0.5 A
1 2 1.0 B
2 3 1.5 C
3 4 0.5 A
4 5 1.0 B
5 6 1.5 C
6 7 0.5 A
7 8 1.0 B
8 9 1.5 C V2 V3
0 1 0.5
1 2 1.0
2 3 1.5
3 4 0.5
4 5 1.0
5 6 1.5
6 7 0.5
7 8 1.0
8 9 1.5Drop
Exclude several columns by column name.
# DT[, .SD, .SDcols = !c("V2", "V3")]
DT[, !c("V2", "V3")] V1 V4
<int> <char>
1: 1 A
2: 2 B
3: 1 C
4: 2 A
5: 1 B
6: 2 C
7: 1 A
8: 2 B
9: 1 CDF[ , !(names(DF) %in% c("V2", "V3"))] V1 V4
1 1 A
2 2 B
3 1 C
4 2 A
5 1 B
6 2 C
7 1 A
8 2 B
9 1 CTB |> select(-V2, -V3)# A tibble: 9 × 2
V1 V4
<int> <chr>
1 1 A
2 2 B
3 1 C
4 2 A
5 1 B
6 2 C
7 1 A
8 2 B
9 1 CPL.drop("V2", "V3")shape: (9, 2)
┌─────┬─────┐
│ V1 ┆ V4 │
│ --- ┆ --- │
│ i64 ┆ str │
╞═════╪═════╡
│ 1 ┆ A │
│ 2 ┆ B │
│ 1 ┆ C │
│ 2 ┆ A │
│ 1 ┆ B │
│ 2 ┆ C │
│ 1 ┆ A │
│ 2 ┆ B │
│ 1 ┆ C │
└─────┴─────┘PD.drop(columns=["V2", "V3"]) V1 V4
0 1 A
1 2 B
2 1 C
3 2 A
4 1 B
5 2 C
6 1 A
7 2 B
8 1 CRemove a column from the data set. Using let() is efficient because it modifies the data set in place. This code is not executed because the V5 column is not present in the data set.
DT[, let(V1 = NULL)]
DT V2 V3 V4
<int> <num> <char>
1: 1 0.5 A
2: 2 1.0 B
3: 3 1.5 C
4: 4 0.5 A
5: 5 1.0 B
6: 6 1.5 C
7: 7 0.5 A
8: 8 1.0 B
9: 9 1.5 C V2 V3 V4
<int> <num> <char>
1: 1 0.5 A
2: 2 1.0 B
3: 3 1.5 C
4: 4 0.5 A
5: 5 1.0 B
6: 6 1.5 C
7: 7 0.5 A
8: 8 1.0 B
9: 9 1.5 CDF = DF[, !names(DF) %in% "V1"]
DF V2 V3 V4
1 1 0.5 A
2 2 1.0 B
3 3 1.5 C
4 4 0.5 A
5 5 1.0 B
6 6 1.5 C
7 7 0.5 A
8 8 1.0 B
9 9 1.5 CTB = TB |> select(-V1)
TB# A tibble: 9 × 3
V2 V3 V4
<int> <dbl> <chr>
1 1 0.5 A
2 2 1 B
3 3 1.5 C
4 4 0.5 A
5 5 1 B
6 6 1.5 C
7 7 0.5 A
8 8 1 B
9 9 1.5 CPL = PL.drop("V1")PD = PD.drop(columns="V1")Remove several columns from the data set. Using := is efficient because it modifies the data set in place. This code is not executed because the V6 and V7 columns are not present in the data set.
cols = c("V2", "V3")
DT[, (cols) := NULL]
DT V4
<char>
1: A
2: B
3: C
4: A
5: B
6: C
7: A
8: B
9: C V4
<char>
1: A
2: B
3: C
4: A
5: B
6: C
7: A
8: B
9: CDF = DF[, !(names(DF) %in% c("V2", "V3"))]
DF[1] "A" "B" "C" "A" "B" "C" "A" "B" "C"TB = TB |> select(-V2, -V3)
TB# A tibble: 9 × 1
V4
<chr>
1 A
2 B
3 C
4 A
5 B
6 C
7 A
8 B
9 Ccols = ["V2", "V3"]
PL = PL.drop(cols)cols = ["V2", "V3"]
PD = PD.drop(columns=cols)refresh_data()refresh_python()Rename
Select and rename.
DT[, .(X1 = V1, X2 = V2)] X1 X2
<int> <int>
1: 1 1
2: 2 2
3: 1 3
4: 2 4
5: 1 5
6: 2 6
7: 1 7
8: 2 8
9: 1 9setNames(
DF[, c("V1", "V2")],
c("X1", "X2")) X1 X2
1 1 1
2 2 2
3 1 3
4 2 4
5 1 5
6 2 6
7 1 7
8 2 8
9 1 9TB |> select(X1 = V1, X2 = V2)# A tibble: 9 × 2
X1 X2
<int> <int>
1 1 1
2 2 2
3 1 3
4 2 4
5 1 5
6 2 6
7 1 7
8 2 8
9 1 9PL.select(pl.col("V1").alias("X1"), pl.col("V2").alias("X2"))shape: (9, 2)
┌─────┬─────┐
│ X1 ┆ X2 │
│ --- ┆ --- │
│ i64 ┆ i64 │
╞═════╪═════╡
│ 1 ┆ 1 │
│ 2 ┆ 2 │
│ 1 ┆ 3 │
│ 2 ┆ 4 │
│ 1 ┆ 5 │
│ 2 ┆ 6 │
│ 1 ┆ 7 │
│ 2 ┆ 8 │
│ 1 ┆ 9 │
└─────┴─────┘PD[["V1", "V2"]].rename(columns={"V1": "X1", "V2": "X2"}) X1 X2
0 1 1
1 2 2
2 1 3
3 2 4
4 1 5
5 2 6
6 1 7
7 2 8
8 1 9Using the data.table::setnames() to rename columns is efficient because it renames column in place. This code is not executed to avoid renaming columns in the original data set.
setnames(DT, old = c("V1", "V2"), new = c("X1", "X2"))
DT X1 X2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 A
2: 2 2 1.0 B
3: 1 3 1.5 C
4: 2 4 0.5 A
5: 1 5 1.0 B
6: 2 6 1.5 C
7: 1 7 0.5 A
8: 2 8 1.0 B
9: 1 9 1.5 Ccolnames(DF)[match(c("V1", "V2"), colnames(DF))] = c("X1", "X2")
DF X1 X2 V3 V4
1 1 1 0.5 A
2 2 2 1.0 B
3 1 3 1.5 C
4 2 4 0.5 A
5 1 5 1.0 B
6 2 6 1.5 C
7 1 7 0.5 A
8 2 8 1.0 B
9 1 9 1.5 CTB = TB |> rename(X1 = V1, X2 = V2)
TB# A tibble: 9 × 4
X1 X2 V3 V4
<int> <int> <dbl> <chr>
1 1 1 0.5 A
2 2 2 1 B
3 1 3 1.5 C
4 2 4 0.5 A
5 1 5 1 B
6 2 6 1.5 C
7 1 7 0.5 A
8 2 8 1 B
9 1 9 1.5 CPL = PL.rename({"V1": "X1", "V2": "X2"})PD = PD.rename(columns={"V1": "X1", "V2": "X2"})refresh_data()refresh_python()Advanced selections
Complex selections using regular expressions or dedicated functions.
DT[, .SD, .SDcols = c("V1", "V2")]
DT[, .SD, .SDcols = patterns("^V[1-2]$")]
DT[, .SD, .SDcols = patterns("V")]
DT[, .SD, .SDcols = patterns("3$")]
DT[, .SD, .SDcols = patterns(".2")]
DT[, .SD, .SDcols = patterns("^V1$|^X$")]
DT[, .SD, .SDcols = patterns("^(?!V2)", perl = TRUE)]DF[, c("V1", "V2")]
DF[ , grep("^V[1-2]$", names(DF))]
DF[ , c("V4", setdiff(names(DF), "V4"))]
DF[ , grep("V", names(DF))]
DF[ , grep("3$", names(DF))]
DF[ , grep(".2", names(DF))]
DF[ , c("V1", "X")]
DF[ , !grepl("^V2", names(DF))]TB |> select(V1, V2)
TB |> select(num_range("V", 1:2))
TB |> select(contains("V"))
TB |> select(ends_with("3"))
TB |> select(matches(".2"))
TB |> select(one_of(c("V1", "X")))
TB |> select(-starts_with("V2"))PL.select(pl.col("^V[1-2]$"))
PL.select(pl.col("^.*3$"))
PL.select(pl.exclude("V2"))shape: (9, 2)
┌─────┬─────┐
│ V1 ┆ V2 │
│ --- ┆ --- │
│ i64 ┆ i64 │
╞═════╪═════╡
│ 1 ┆ 1 │
│ 2 ┆ 2 │
│ 1 ┆ 3 │
│ 2 ┆ 4 │
│ 1 ┆ 5 │
│ 2 ┆ 6 │
│ 1 ┆ 7 │
│ 2 ┆ 8 │
│ 1 ┆ 9 │
└─────┴─────┘shape: (9, 1)
┌─────┐
│ V3 │
│ --- │
│ f64 │
╞═════╡
│ 0.5 │
│ 1.0 │
│ 1.5 │
│ 0.5 │
│ 1.0 │
│ 1.5 │
│ 0.5 │
│ 1.0 │
│ 1.5 │
└─────┘shape: (9, 3)
┌─────┬─────┬─────┐
│ V1 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- │
│ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╡
│ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 1.5 ┆ C │
│ 2 ┆ 0.5 ┆ A │
│ 1 ┆ 1.0 ┆ B │
│ 2 ┆ 1.5 ┆ C │
│ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 1.5 ┆ C │
└─────┴─────┴─────┘PD.filter(regex=r"^V[1-2]$")
PD.filter(regex=r"3$")
PD.drop(columns="V2") V1 V2
0 1 1
1 2 2
2 1 3
3 2 4
4 1 5
5 2 6
6 1 7
7 2 8
8 1 9 V3
0 0.5
1 1.0
2 1.5
3 0.5
4 1.0
5 1.5
6 0.5
7 1.0
8 1.5 V1 V3 V4
0 1 0.5 A
1 2 1.0 B
2 1 1.5 C
3 2 0.5 A
4 1 1.0 B
5 2 1.5 C
6 1 0.5 A
7 2 1.0 B
8 1 1.5 CSummarize
Single columns
Create a new data frame with a single row and a single column, summarizing the information of one column. Named or unnamed results.
DT[, sum(V1)]
DT[, .(sumV1 = sum(V1))][1] 13 sumV1
<int>
1: 13sum(DF$V1)
data.frame(sumV1 = sum(DF$V1))[1] 13 sumV1
1 13TB |> pull(V1) |> sum()
TB |> summarise(sumV1 = sum(V1))[1] 13# A tibble: 1 × 1
sumV1
<int>
1 13PL.select(pl.col("V1").sum())
PL.select(sumV1=pl.col("V1").sum())shape: (1, 1)
┌─────┐
│ V1 │
│ --- │
│ i64 │
╞═════╡
│ 13 │
└─────┘shape: (1, 1)
┌───────┐
│ sumV1 │
│ --- │
│ i64 │
╞═══════╡
│ 13 │
└───────┘PD["V1"].sum()
pd.DataFrame({"sumV1": [PD["V1"].sum()]})np.int64(13) sumV1
0 13Create a new data frame with a single row and two columns, summarizing the information of two manually specified columns.
DT[, .(sumV1 = sum(V1), sdV3 = sd(V3))] sumV1 sdV3
<int> <num>
1: 13 0.4330127data.frame(sumV1 = sum(DF$V1), sdV3 = sd(DF$V3)) sumV1 sdV3
1 13 0.4330127TB |> summarise(sumV1 = sum(V1), sdV3 = sd(V3))# A tibble: 1 × 2
sumV1 sdV3
<int> <dbl>
1 13 0.433PL.select(sumV1=pl.col("V1").sum(), sdV3=pl.col("V3").std())shape: (1, 2)
┌───────┬──────────┐
│ sumV1 ┆ sdV3 │
│ --- ┆ --- │
│ i64 ┆ f64 │
╞═══════╪══════════╡
│ 13 ┆ 0.433013 │
└───────┴──────────┘PD.agg(sumV1=("V1", "sum"), sdV3=("V3", "std")) V1 V3
sumV1 13.0 NaN
sdV3 NaN 0.433013Multiple columns
Apply a function to each column.
DT[, lapply(.SD, head, 1)] V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 Adata.frame(lapply(DF, head, 1)) V1 V2 V3 V4
1 1 1 0.5 ATB |> summarize(across(everything(), \(x) head(x, 1)))# A tibble: 1 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 1 0.5 APL.head(1)shape: (1, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
└─────┴─────┴─────┴─────┘PD.head(1) V1 V2 V3 V4
0 1 1 0.5 AApply a function to each column that matches a specific type.
DT[, lapply(.SD, mean), .SDcols = is.numeric] V1 V2 V3
<num> <num> <num>
1: 1.444444 5 1data.frame(lapply(DF[sapply(DF, is.numeric)], mean)) V1 V2 V3
1 1.444444 5 1TB |> summarize(across(where(is.numeric), mean))# A tibble: 1 × 3
V1 V2 V3
<dbl> <dbl> <dbl>
1 1.44 5 1PL.select(pl.col(pl.NUMERIC_DTYPES).mean())shape: (1, 3)
┌──────────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 │
│ --- ┆ --- ┆ --- │
│ f64 ┆ f64 ┆ f64 │
╞══════════╪═════╪═════╡
│ 1.444444 ┆ 5.0 ┆ 1.0 │
└──────────┴─────┴─────┘`NUMERIC_DTYPES` was deprecated in version 1.0.0. Define your own data type groups or use the `polars.selectors` module for selecting columns of a certain data type.PD.select_dtypes("number").mean().to_frame().T V1 V2 V3
0 1.444444 5.0 1.0By group
Count the number of observation by group.
DT[, .N, by = V4] V4 N
<char> <int>
1: A 3
2: B 3
3: C 3as.data.frame(table(DF$V4)) Var1 Freq
1 A 3
2 B 3
3 C 3TB |>
group_by(V4) |>
tally()# A tibble: 3 × 2
V4 n
<chr> <int>
1 A 3
2 B 3
3 C 3PL.group_by("V4").len()shape: (3, 2)
┌─────┬─────┐
│ V4 ┆ len │
│ --- ┆ --- │
│ str ┆ u32 │
╞═════╪═════╡
│ A ┆ 3 │
│ B ┆ 3 │
│ C ┆ 3 │
└─────┴─────┘PD.groupby("V4", as_index=False).size() V4 size
0 A 3
1 B 3
2 C 3Multiple named summaries
DT[, .(nobs = .N, meanV1 = mean(V1)), by = V4] V4 nobs meanV1
<char> <int> <num>
1: A 3 1.333333
2: B 3 1.666667
3: C 3 1.333333do.call(rbind, by(DF, ~V4, \(x) {
data.frame(nobs = nrow(x), meanV1 = mean(x$V1))
}
)) nobs meanV1
A 3 1.333333
B 3 1.666667
C 3 1.333333TB |>
group_by(V4) |>
summarize(nobs = n(), meanV1 = mean(V1)) |>
ungroup()# A tibble: 3 × 3
V4 nobs meanV1
<chr> <int> <dbl>
1 A 3 1.33
2 B 3 1.67
3 C 3 1.33PL.group_by("V4").agg(nobs=pl.len(), meanV1=pl.col("V1").mean())shape: (3, 3)
┌─────┬──────┬──────────┐
│ V4 ┆ nobs ┆ meanV1 │
│ --- ┆ --- ┆ --- │
│ str ┆ u32 ┆ f64 │
╞═════╪══════╪══════════╡
│ C ┆ 3 ┆ 1.333333 │
│ A ┆ 3 ┆ 1.333333 │
│ B ┆ 3 ┆ 1.666667 │
└─────┴──────┴──────────┘PD.groupby("V4", as_index=False).agg(nobs=("V1", "size"), meanV1=("V1", "mean")) V4 nobs meanV1
0 A 3 1.333333
1 B 3 1.666667
2 C 3 1.333333Apply a function to the full data frame in each group. Here, we return the first row in each group using the head() function.
DT[, head(.SD, 1), by = V4] V4 V1 V2 V3
<char> <int> <int> <num>
1: A 1 1 0.5
2: B 2 2 1.0
3: C 1 3 1.5do.call(rbind, by(DF, DF$V4, \(x) head(x, 1))) V1 V2 V3 V4
A 1 1 0.5 A
B 2 2 1.0 B
C 1 3 1.5 CTB |>
group_by(V4) |>
summarize(across(everything(), head, 1))# A tibble: 3 × 4
V4 V1 V2 V3
<chr> <int> <int> <dbl>
1 A 1 1 0.5
2 B 2 2 1
3 C 1 3 1.5There was 1 warning in `summarize()`.
ℹ In argument: `across(everything(), head, 1)`.
ℹ In group 1: `V4 = "A"`.
Caused by warning:
! The `...` argument of `across()` is deprecated as of dplyr 1.1.0.
Supply arguments directly to `.fns` through an anonymous function instead.
# Previously
across(a:b, mean, na.rm = TRUE)
# Now
across(a:b, \(x) mean(x, na.rm = TRUE))PL.group_by("V4", maintain_order=True).first()shape: (3, 4)
┌─────┬─────┬─────┬─────┐
│ V4 ┆ V1 ┆ V2 ┆ V3 │
│ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ i64 ┆ i64 ┆ f64 │
╞═════╪═════╪═════╪═════╡
│ A ┆ 1 ┆ 1 ┆ 0.5 │
│ B ┆ 2 ┆ 2 ┆ 1.0 │
│ C ┆ 1 ┆ 3 ┆ 1.5 │
└─────┴─────┴─────┴─────┘PD.groupby("V4", as_index=False).first() V4 V1 V2 V3
0 A 1 1 0.5
1 B 2 2 1.0
2 C 1 3 1.5Get the row number of first (and last) observation by group. In data.table, we use the .I operator, which reports the row number.
DT[, .I[1], by = V4]
DT[, range(.I), by = V4] V4 V1
<char> <int>
1: A 1
2: B 2
3: C 3 V4 V1
<char> <int>
1: A 1
2: A 7
3: B 2
4: B 8
5: C 3
6: C 9do.call(rbind, by(DF, ~V4, \(x) x[1,]))
do.call(rbind, by(DF, ~V4, \(x) x[c(1, nrow(x)),])) V1 V2 V3 V4
A 1 1 0.5 A
B 2 2 1.0 B
C 1 3 1.5 C V1 V2 V3 V4
A.1 1 1 0.5 A
A.7 1 7 0.5 A
B.2 2 2 1.0 B
B.8 2 8 1.0 B
C.3 1 3 1.5 C
C.9 1 9 1.5 CTB |>
group_by(V4) |>
summarize(cur_group_rows()[1]) |>
ungroup()
TB |>
group_by(V4) |>
reframe(cur_group_rows()[c(1, n())]) |>
ungroup()# A tibble: 3 × 2
V4 `cur_group_rows()[1]`
<chr> <int>
1 A 1
2 B 2
3 C 3# A tibble: 6 × 2
V4 `cur_group_rows()[c(1, n())]`
<chr> <int>
1 A 1
2 A 7
3 B 2
4 B 8
5 C 3
6 C 9PL.with_row_index().group_by("V4").agg(
first_index=pl.col("index").first(),
last_index=pl.col("index").last(),
)shape: (3, 3)
┌─────┬─────────────┬────────────┐
│ V4 ┆ first_index ┆ last_index │
│ --- ┆ --- ┆ --- │
│ str ┆ u32 ┆ u32 │
╞═════╪═════════════╪════════════╡
│ B ┆ 1 ┆ 7 │
│ C ┆ 2 ┆ 8 │
│ A ┆ 0 ┆ 6 │
└─────┴─────────────┴────────────┘PD.reset_index().groupby("V4")["index"].agg(["first", "last"]) first last
V4
A 0 6
B 1 7
C 2 8List-columns are columns where each element is a vector, data frame, or other object.
DT[, .(.(V1)), by = V4]
DT[, .(.(.SD)), by = V4] V4 V1
<char> <list>
1: A 1,2,1
2: B 2,1,2
3: C 1,2,1 V4 V1
<char> <list>
1: A <data.table[3x3]>
2: B <data.table[3x3]>
3: C <data.table[3x3]>tapply(DF$V1, DF$V4,
function(x) list(x))
split(DF, DF$V4)$A
[1] 1 2 1
$B
[1] 2 1 2
$C
[1] 1 2 1$A
V1 V2 V3 V4
1 1 1 0.5 A
4 2 4 0.5 A
7 1 7 0.5 A
$B
V1 V2 V3 V4
2 2 2 1 B
5 1 5 1 B
8 2 8 1 B
$C
V1 V2 V3 V4
3 1 3 1.5 C
6 2 6 1.5 C
9 1 9 1.5 CTB |>
group_by(V4) |>
summarise(list(V1))
TB |>
group_by(V4) |>
group_nest()# A tibble: 3 × 2
V4 `list(V1)`
<chr> <list>
1 A <int [3]>
2 B <int [3]>
3 C <int [3]># A tibble: 3 × 2
V4 data
<chr> <list<tibble[,3]>>
1 A [3 × 3]
2 B [3 × 3]
3 C [3 × 3]PL.group_by("V4").agg(pl.col("V1"))
PL.partition_by("V4", as_dict=True)shape: (3, 2)
┌─────┬───────────┐
│ V4 ┆ V1 │
│ --- ┆ --- │
│ str ┆ list[i64] │
╞═════╪═══════════╡
│ A ┆ [1, 2, 1] │
│ C ┆ [1, 2, 1] │
│ B ┆ [2, 1, 2] │
└─────┴───────────┘{('A',): shape: (3, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
└─────┴─────┴─────┴─────┘, ('B',): shape: (3, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
└─────┴─────┴─────┴─────┘, ('C',): shape: (3, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘}PD.groupby("V4")["V1"].agg(list)
{key: value for key, value in PD.groupby("V4")}V4
A [1, 2, 1]
B [2, 1, 2]
C [1, 2, 1]
Name: V1, dtype: object{'A': V1 V2 V3 V4
0 1 1 0.5 A
3 2 4 0.5 A
6 1 7 0.5 A, 'B': V1 V2 V3 V4
1 2 2 1.0 B
4 1 5 1.0 B
7 2 8 1.0 B, 'C': V1 V2 V3 V4
2 1 3 1.5 C
5 2 6 1.5 C
8 1 9 1.5 C}Modify
Single columns
Create or modify a column.
DT[, let(V5 = V1^2)]
DT V1 V2 V3 V4 V5
<int> <int> <num> <char> <num>
1: 1 1 0.5 A 1
2: 2 2 1.0 B 4
3: 1 3 1.5 C 1
4: 2 4 0.5 A 4
5: 1 5 1.0 B 1
6: 2 6 1.5 C 4
7: 1 7 0.5 A 1
8: 2 8 1.0 B 4
9: 1 9 1.5 C 1 V1 V2 V3 V4 V5
<int> <int> <num> <char> <num>
1: 1 1 0.5 A 1
2: 2 2 1.0 B 4
3: 1 3 1.5 C 1
4: 2 4 0.5 A 4
5: 1 5 1.0 B 1
6: 2 6 1.5 C 4
7: 1 7 0.5 A 1
8: 2 8 1.0 B 4
9: 1 9 1.5 C 1DF$V5 = DF$V1^2
DF V1 V2 V3 V4 V5
1 1 1 0.5 A 1
2 2 2 1.0 B 4
3 1 3 1.5 C 1
4 2 4 0.5 A 4
5 1 5 1.0 B 1
6 2 6 1.5 C 4
7 1 7 0.5 A 1
8 2 8 1.0 B 4
9 1 9 1.5 C 1TB = TB |> mutate(V5 = V1^2)
TB# A tibble: 9 × 5
V1 V2 V3 V4 V5
<int> <int> <dbl> <chr> <dbl>
1 1 1 0.5 A 1
2 2 2 1 B 4
3 1 3 1.5 C 1
4 2 4 0.5 A 4
5 1 5 1 B 1
6 2 6 1.5 C 4
7 1 7 0.5 A 1
8 2 8 1 B 4
9 1 9 1.5 C 1PL = PL.with_columns(V5=pl.col("V1") ** 2)PD = PD.assign(V5=PD["V1"] ** 2)Create several new columns, each named explicitly.
DT[, let(
V5 = sqrt(V1),
V6 = "X")]
DT V1 V2 V3 V4 V5 V6
<int> <int> <num> <char> <num> <char>
1: 1 1 0.5 A 1.000000 X
2: 2 2 1.0 B 1.414214 X
3: 1 3 1.5 C 1.000000 X
4: 2 4 0.5 A 1.414214 X
5: 1 5 1.0 B 1.000000 X
6: 2 6 1.5 C 1.414214 X
7: 1 7 0.5 A 1.000000 X
8: 2 8 1.0 B 1.414214 X
9: 1 9 1.5 C 1.000000 X V1 V2 V3 V4 V5 V6
<int> <int> <num> <char> <num> <char>
1: 1 1 0.5 A 1.000000 X
2: 2 2 1.0 B 1.414214 X
3: 1 3 1.5 C 1.000000 X
4: 2 4 0.5 A 1.414214 X
5: 1 5 1.0 B 1.000000 X
6: 2 6 1.5 C 1.414214 X
7: 1 7 0.5 A 1.000000 X
8: 2 8 1.0 B 1.414214 X
9: 1 9 1.5 C 1.000000 XDF$V5 = sqrt(DF$V1)
DF$V6 = "X"
DF V1 V2 V3 V4 V5 V6
1 1 1 0.5 A 1.000000 X
2 2 2 1.0 B 1.414214 X
3 1 3 1.5 C 1.000000 X
4 2 4 0.5 A 1.414214 X
5 1 5 1.0 B 1.000000 X
6 2 6 1.5 C 1.414214 X
7 1 7 0.5 A 1.000000 X
8 2 8 1.0 B 1.414214 X
9 1 9 1.5 C 1.000000 XTB = TB |> mutate(
V5 = sqrt(V1),
V6 = "X")
TB# A tibble: 9 × 6
V1 V2 V3 V4 V5 V6
<int> <int> <dbl> <chr> <dbl> <chr>
1 1 1 0.5 A 1 X
2 2 2 1 B 1.41 X
3 1 3 1.5 C 1 X
4 2 4 0.5 A 1.41 X
5 1 5 1 B 1 X
6 2 6 1.5 C 1.41 X
7 1 7 0.5 A 1 X
8 2 8 1 B 1.41 X
9 1 9 1.5 C 1 XPL = PL.with_columns(V5=pl.col("V1").sqrt(), V6=pl.lit("X"))PD = PD.assign(V5=PD["V1"] ** 0.5, V6="X")Multiple columns
Take the square of every numeric column.
DT[, names(.SD) := lapply(.SD, \(x) x^2), .SDcols = is.numeric]
DT V1 V2 V3 V4 V5 V6
<num> <num> <num> <char> <num> <char>
1: 1 1 0.25 A 1 X
2: 4 4 1.00 B 2 X
3: 1 9 2.25 C 1 X
4: 4 16 0.25 A 2 X
5: 1 25 1.00 B 1 X
6: 4 36 2.25 C 2 X
7: 1 49 0.25 A 1 X
8: 4 64 1.00 B 2 X
9: 1 81 2.25 C 1 X V1 V2 V3 V4 V5 V6
<num> <num> <num> <char> <num> <char>
1: 1 1 0.25 A 1 X
2: 4 4 1.00 B 2 X
3: 1 9 2.25 C 1 X
4: 4 16 0.25 A 2 X
5: 1 25 1.00 B 1 X
6: 4 36 2.25 C 2 X
7: 1 49 0.25 A 1 X
8: 4 64 1.00 B 2 X
9: 1 81 2.25 C 1 XTB = TB |>
mutate(across(where(is.numeric), \(x) x^2))
TB# A tibble: 9 × 6
V1 V2 V3 V4 V5 V6
<dbl> <dbl> <dbl> <chr> <dbl> <chr>
1 1 1 0.25 A 1 X
2 4 4 1 B 2 X
3 1 9 2.25 C 1 X
4 4 16 0.25 A 2 X
5 1 25 1 B 1 X
6 4 36 2.25 C 2 X
7 1 49 0.25 A 1 X
8 4 64 1 B 2 X
9 1 81 2.25 C 1 XPL = PL.with_columns(pl.col(pl.NUMERIC_DTYPES) ** 2)`NUMERIC_DTYPES` was deprecated in version 1.0.0. Define your own data type groups or use the `polars.selectors` module for selecting columns of a certain data type.PD[PD.select_dtypes("number").columns] **= 2refresh_data()refresh_python()Replace
Replace values in rows that match a condition applied to a column.
DT[V2 <= 4, let(V2 = 0)]
DT V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 0 0.5 A
2: 2 0 1.0 B
3: 1 0 1.5 C
4: 2 0 0.5 A
5: 1 5 1.0 B
6: 2 6 1.5 C
7: 1 7 0.5 A
8: 2 8 1.0 B
9: 1 9 1.5 C V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 0 0.5 A
2: 2 0 1.0 B
3: 1 0 1.5 C
4: 2 0 0.5 A
5: 1 5 1.0 B
6: 2 6 1.5 C
7: 1 7 0.5 A
8: 2 8 1.0 B
9: 1 9 1.5 CDF$V2 = replace(DF$V2, DF$V2 < 4, 0)
DF V1 V2 V3 V4
1 1 0 0.5 A
2 2 0 1.0 B
3 1 0 1.5 C
4 2 4 0.5 A
5 1 5 1.0 B
6 2 6 1.5 C
7 1 7 0.5 A
8 2 8 1.0 B
9 1 9 1.5 CTB = DF |>
mutate(V2 = base::replace(V2, V2 < 4, 0))
TB V1 V2 V3 V4
1 1 0 0.5 A
2 2 0 1.0 B
3 1 0 1.5 C
4 2 4 0.5 A
5 1 5 1.0 B
6 2 6 1.5 C
7 1 7 0.5 A
8 2 8 1.0 B
9 1 9 1.5 CPL = PL.with_columns(pl.when(pl.col("V2") <= 4).then(0).otherwise(pl.col("V2")))PD["V2"] = PD["V2"].mask(PD["V2"] <= 4, 0)refresh_data()refresh_python()If, else, case
Fast version of ifelse(). Return a vector the same length as the vector tested, with specific value for in case where the condition is TRUE, where it is FALSE, and where it is NA.
x = c(-3:3, NA)
fifelse(test = x < 0,
yes = "neg",
no = "pos",
na = "NA")[1] "neg" "neg" "neg" "pos" "pos" "pos" "pos" "NA"x = c(-3:3, NA)
result = ifelse(is.na(x),
"NA",
ifelse(x < 0, "neg", "pos"))
result[1] "neg" "neg" "neg" "pos" "pos" "pos" "pos" "NA"x = c(-3:3, NA)
if_else(condition = x < 0,
true = "neg",
false = "pos",
missing = "NA")[1] "neg" "neg" "neg" "pos" "pos" "pos" "pos" "NA"pl.Series("x", range(-3, 4)).replace_strict({-3:"neg", -2:"neg", -1:"neg"}, default="pos")shape: (7,)
Series: 'x' [str]
[
"neg"
"neg"
"neg"
"pos"
"pos"
"pos"
"pos"
]np.where(pd.Series(range(-3, 4)) < 0, "neg", "pos")array(['neg', 'neg', 'neg', 'pos', 'pos', 'pos', 'pos'], dtype='<U3')Recode several cases at once, based on a vector of conditions.
x = 1:10
fcase(
x %% 6 == 0, "fizz buzz",
x %% 2 == 0, "fizz",
x %% 3 == 0, "buzz",
default = as.character(x)
) [1] "1" "fizz" "buzz" "fizz" "5" "fizz buzz"
[7] "7" "fizz" "buzz" "fizz"x = 1:10
result = ifelse(x %% 6 == 0, "fizz buzz",
ifelse(x %% 2 == 0, "fizz",
ifelse(x %% 3 == 0, "buzz", as.character(x))))
result [1] "1" "fizz" "buzz" "fizz" "5" "fizz buzz"
[7] "7" "fizz" "buzz" "fizz"x = 1:10
case_when(
x %% 6 == 0 ~ "fizz buzz",
x %% 2 == 0 ~ "fizz",
x %% 3 == 0 ~ "buzz",
TRUE ~ as.character(x)
) [1] "1" "fizz" "buzz" "fizz" "5" "fizz buzz"
[7] "7" "fizz" "buzz" "fizz"x = pl.Series(range(1, 11))
pl.when(x % 6 == 0).then(pl.lit("fizz buzz")).when(x % 2 == 0).then(pl.lit("fizz")).otherwise(pl.lit("buzz"))<Expr ['.when(Series).then("fizz buzz"…'] at 0x7D84E0EFC250>x = pd.Series(range(1, 11))
np.select([x % 6 == 0, x % 2 == 0, x % 3 == 0], ["fizz buzz", "fizz", "buzz"], default=x.astype(str))array(['1', 'fizz', 'buzz', 'fizz', '5', 'fizz buzz', '7', 'fizz', 'buzz',
'fizz'], dtype=object)Modify by group
Group summaries of a column in categories.
DT[, by = "V4", .(sumV2 = sum(V2))] V4 sumV2
<char> <int>
1: A 12
2: B 15
3: C 18aggregate(V2 ~ V4, data = DF, FUN = sum) V4 V2
1 A 12
2 B 15
3 C 18TB |>
group_by(V4) |>
summarise(sumV2 = sum(V2)) |>
ungroup()# A tibble: 3 × 2
V4 sumV2
<chr> <int>
1 A 12
2 B 15
3 C 18PL.group_by("V4").agg(sumV2=pl.col("V2").sum())shape: (3, 2)
┌─────┬───────┐
│ V4 ┆ sumV2 │
│ --- ┆ --- │
│ str ┆ i64 │
╞═════╪═══════╡
│ A ┆ 12 │
│ C ┆ 18 │
│ B ┆ 15 │
└─────┴───────┘PD.groupby("V4", as_index=False).agg(sumV2=("V2", "sum")) V4 sumV2
0 A 12
1 B 15
2 C 18Group values of a column in groups while applying a function to each category.
DT[,
by = tolower(V4),
.(sumV1 = sum(V1))] tolower sumV1
<char> <int>
1: a 4
2: b 5
3: c 4aggregate(
V1 ~ tolower(V4),
data = DF,
FUN = sum) tolower(V4) V1
1 a 4
2 b 5
3 c 4TB |>
group_by(tolower(V4)) |>
summarise(sumV1 = sum(V1))# A tibble: 3 × 2
`tolower(V4)` sumV1
<chr> <int>
1 a 4
2 b 5
3 c 4PL.group_by(pl.col("V4").str.to_lowercase()).agg(sumV1=pl.col("V1").sum())shape: (3, 2)
┌─────┬───────┐
│ V4 ┆ sumV1 │
│ --- ┆ --- │
│ str ┆ i64 │
╞═════╪═══════╡
│ b ┆ 5 │
│ c ┆ 4 │
│ a ┆ 4 │
└─────┴───────┘PD.groupby(PD["V4"].str.lower())["V1"].sum()V4
a 4
b 5
c 4
Name: V1, dtype: int64Group values of a column in two categories, TRUE (for rows matching the condition) and FALSE (For rows not matching the condition).
DT[,
keyby = V4 == "A",
sum(V1)]Key: <V4>
V4 V1
<lgcl> <int>
1: FALSE 9
2: TRUE 4aggregate(V1 ~ groupA,
data = transform(DF, groupA = V4 == "A"),
FUN = sum) groupA V1
1 FALSE 9
2 TRUE 4TB |>
group_by(V4 == "A") |>
summarise(sum(V1))# A tibble: 2 × 2
`V4 == "A"` `sum(V1)`
<lgl> <int>
1 FALSE 9
2 TRUE 4PL.group_by(pl.col("V4") == "A").agg(pl.col("V1").sum())shape: (2, 2)
┌───────┬─────┐
│ V4 ┆ V1 │
│ --- ┆ --- │
│ bool ┆ i64 │
╞═══════╪═════╡
│ true ┆ 4 │
│ false ┆ 9 │
└───────┴─────┘PD.groupby(PD["V4"] == "A")["V1"].sum()V4
False 9
True 4
Name: V1, dtype: int64Group values of a column in several categories with some of the rows of the initial dataset removed.
DT[1:5,
by = V4,
.(sumV1 = sum(V1))] V4 sumV1
<char> <int>
1: A 3
2: B 3
3: C 1aggregate(V1 ~ V4,
data = DF[1:5,],
FUN = sum) V4 V1
1 A 3
2 B 3
3 C 1TB |>
slice(1:5) |>
group_by(V4) |>
summarise(sumV1 = sum(V1))# A tibble: 3 × 2
V4 sumV1
<chr> <int>
1 A 3
2 B 3
3 C 1PL.filter(pl.col("V2") > 2).group_by("V4").agg(pl.col("V1").sum())shape: (3, 2)
┌─────┬─────┐
│ V4 ┆ V1 │
│ --- ┆ --- │
│ str ┆ i64 │
╞═════╪═════╡
│ B ┆ 3 │
│ C ┆ 4 │
│ A ┆ 3 │
└─────┴─────┘PD.query("V2 > 2").groupby("V4")["V1"].sum()V4
A 3
B 3
C 4
Name: V1, dtype: int64Add a new column with the number of observations per group.
DT[, let(n = .N), by = V1]
DT V1 V2 V3 V4 n
<int> <int> <num> <char> <int>
1: 1 1 0.5 A 5
2: 2 2 1.0 B 4
3: 1 3 1.5 C 5
4: 2 4 0.5 A 4
5: 1 5 1.0 B 5
6: 2 6 1.5 C 4
7: 1 7 0.5 A 5
8: 2 8 1.0 B 4
9: 1 9 1.5 C 5 V1 V2 V3 V4 n
<int> <int> <num> <char> <int>
1: 1 1 0.5 A 5
2: 2 2 1.0 B 4
3: 1 3 1.5 C 5
4: 2 4 0.5 A 4
5: 1 5 1.0 B 5
6: 2 6 1.5 C 4
7: 1 7 0.5 A 5
8: 2 8 1.0 B 4
9: 1 9 1.5 C 5DF$n <-
ave(DF$V1,
DF$V1,
FUN = length)
DF V1 V2 V3 V4 n
1 1 1 0.5 A 5
2 2 2 1.0 B 4
3 1 3 1.5 C 5
4 2 4 0.5 A 4
5 1 5 1.0 B 5
6 2 6 1.5 C 4
7 1 7 0.5 A 5
8 2 8 1.0 B 4
9 1 9 1.5 C 5TB = TB |>
group_by(V1) |>
add_tally()
TB# A tibble: 9 × 5
# Groups: V1 [2]
V1 V2 V3 V4 n
<int> <int> <dbl> <chr> <int>
1 1 1 0.5 A 5
2 2 2 1 B 4
3 1 3 1.5 C 5
4 2 4 0.5 A 4
5 1 5 1 B 5
6 2 6 1.5 C 4
7 1 7 0.5 A 5
8 2 8 1 B 4
9 1 9 1.5 C 5PL.with_columns(n=pl.len().over("V4"))shape: (9, 5)
┌─────┬─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 ┆ n │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str ┆ u32 │
╞═════╪═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A ┆ 3 │
│ 2 ┆ 2 ┆ 1.0 ┆ B ┆ 3 │
│ 1 ┆ 3 ┆ 1.5 ┆ C ┆ 3 │
│ 2 ┆ 4 ┆ 0.5 ┆ A ┆ 3 │
│ 1 ┆ 5 ┆ 1.0 ┆ B ┆ 3 │
│ 2 ┆ 6 ┆ 1.5 ┆ C ┆ 3 │
│ 1 ┆ 7 ┆ 0.5 ┆ A ┆ 3 │
│ 2 ┆ 8 ┆ 1.0 ┆ B ┆ 3 │
│ 1 ┆ 9 ┆ 1.5 ┆ C ┆ 3 │
└─────┴─────┴─────┴─────┴─────┘PD.assign(n=PD.groupby("V4")["V4"].transform("size")) V1 V2 V3 V4 n
0 1 1 0.5 A 3
1 2 2 1.0 B 3
2 1 3 1.5 C 3
3 2 4 0.5 A 3
4 1 5 1.0 B 3
5 2 6 1.5 C 3
6 1 7 0.5 A 3
7 2 8 1.0 B 3
8 1 9 1.5 C 3refresh_data()refresh_python()Advanced
Summarise all the columns, typically using an aggregation function.
DT[, lapply(.SD, max)] V1 V2 V3 V4
<int> <int> <num> <char>
1: 2 9 1.5 Capply(DF, 2, max) V1 V2 V3 V4
"2" "9" "1.5" "C"TB |> summarise(across(everything(), max))# A tibble: 1 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 2 9 1.5 CPL.select(pl.all().first())shape: (1, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
└─────┴─────┴─────┴─────┘PD.head(1) V1 V2 V3 V4
0 1 1 0.5 ASummarise several columns, typically using an aggregation function.
DT[, lapply(.SD, mean),
.SDcols = c("V1", "V2")] V1 V2
<num> <num>
1: 1.444444 5data.frame(
mean_V1 = mean(DF$V1),
mean_V2 = mean(DF$V2)) mean_V1 mean_V2
1 1.444444 5PL.select(pl.col("V1", "V2").sum())shape: (1, 2)
┌─────┬─────┐
│ V1 ┆ V2 │
│ --- ┆ --- │
│ i64 ┆ i64 │
╞═════╪═════╡
│ 13 ┆ 45 │
└─────┴─────┘PD[["V1", "V2"]].sum().to_frame().T V1 V2
0 13 45Summarise several columns by groups, typically using an aggregation function.
DT[, by = V4,
lapply(.SD, mean),
.SDcols = c("V1", "V2")]
DT[, by = V4,
lapply(.SD, mean),
.SDcols = patterns("V1|V2|Z0")] V4 V1 V2
<char> <num> <num>
1: A 1.333333 4
2: B 1.666667 5
3: C 1.333333 6 V4 V1 V2
<char> <num> <num>
1: A 1.333333 4
2: B 1.666667 5
3: C 1.333333 6cols = intersect(c("V1", "V2", "Z0"), names(DF))
aggregate(DF[cols], by = list(DF$V4), FUN = mean, na.rm = TRUE) Group.1 V1 V2
1 A 1.333333 4
2 B 1.666667 5
3 C 1.333333 6TB |>
group_by(V4) |>
summarise(across(c(V1, V2), mean)) |>
ungroup()# A tibble: 3 × 3
V4 V1 V2
<chr> <dbl> <dbl>
1 A 1.33 4
2 B 1.67 5
3 C 1.33 6PL.group_by("V4").agg(pl.col("V1", "V2").sum())shape: (3, 3)
┌─────┬─────┬─────┐
│ V4 ┆ V1 ┆ V2 │
│ --- ┆ --- ┆ --- │
│ str ┆ i64 ┆ i64 │
╞═════╪═════╪═════╡
│ C ┆ 4 ┆ 18 │
│ B ┆ 5 ┆ 15 │
│ A ┆ 4 ┆ 12 │
└─────┴─────┴─────┘PD.groupby("V4")[["V1", "V2"]].sum() V1 V2
V4
A 4 12
B 5 15
C 4 18Summarise several columns by group using multiple aggregation functions, grouping by one or more variables.
DT[, by = V4,
c(lapply(.SD, sum),
lapply(.SD, mean))] V4 V1 V2 V3 V1 V2 V3
<char> <int> <int> <num> <num> <num> <num>
1: A 4 12 1.5 1.333333 4 0.5
2: B 5 15 3.0 1.666667 5 1.0
3: C 4 18 4.5 1.333333 6 1.5aggregate(cbind(DF$V1, DF$V2, DF$V3) ~ V4,
data = DF,
FUN = function(x) c(sum = sum(x), mean = mean(x))) V4 V1.sum V1.mean V2.sum V2.mean V3.sum V3.mean
1 A 4.000000 1.333333 12 4 1.5 0.5
2 B 5.000000 1.666667 15 5 3.0 1.0
3 C 4.000000 1.333333 18 6 4.5 1.5TB |>
group_by(V4) |>
summarise(across(everything(),
list(sum = sum, mean = mean)))# A tibble: 3 × 7
V4 V1_sum V1_mean V2_sum V2_mean V3_sum V3_mean
<chr> <int> <dbl> <int> <dbl> <dbl> <dbl>
1 A 4 1.33 12 4 1.5 0.5
2 B 5 1.67 15 5 3 1
3 C 4 1.33 18 6 4.5 1.5PL.group_by("V4").agg(pl.col("V1", "V2").agg_groups())shape: (3, 3)
┌─────┬───────────┬───────────┐
│ V4 ┆ V1 ┆ V2 │
│ --- ┆ --- ┆ --- │
│ str ┆ list[u32] ┆ list[u32] │
╞═════╪═══════════╪═══════════╡
│ C ┆ [2, 5, 8] ┆ [2, 5, 8] │
│ B ┆ [1, 4, 7] ┆ [1, 4, 7] │
│ A ┆ [0, 3, 6] ┆ [0, 3, 6] │
└─────┴───────────┴───────────┘agg_groups() is deprecated and will be removed in Polars 2.0. Use df.with_row_index().group_by(...).agg(pl.col('index')) instead.PD.groupby("V4")[["V1", "V2"]].agg(["mean", "sum"]) V1 V2
mean sum mean sum
V4
A 1.333333 4 4.0 12
B 1.666667 5 5.0 15
C 1.333333 4 6.0 18Summarise a subset of columns by column type or condition.
DT[, lapply(.SD, mean), .SDcols = is.numeric]
foo = function(x) {is.numeric(x) && mean(x) > 3}
DT[, lapply(.SD, mean), .SDcols = foo] V1 V2 V3
<num> <num> <num>
1: 1.444444 5 1 V2
<num>
1: 5sapply(DF[sapply(DF, is.numeric)],
mean)
sapply(DF[sapply(DF, \(x) {
is.numeric(x) && mean(x) > 3
})], mean) V1 V2 V3
1.444444 5.000000 1.000000V2
5TB |>
summarise(across(where(is.numeric),
mean))
TB |> summarise(across(
where(~ is.numeric(.x) && mean(.x) > 3), mean))# A tibble: 1 × 3
V1 V2 V3
<dbl> <dbl> <dbl>
1 1.44 5 1# A tibble: 1 × 1
V2
<dbl>
1 5PL.select(pl.col(pl.NUMERIC_DTYPES).mean())shape: (1, 3)
┌──────────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 │
│ --- ┆ --- ┆ --- │
│ f64 ┆ f64 ┆ f64 │
╞══════════╪═════╪═════╡
│ 1.444444 ┆ 5.0 ┆ 1.0 │
└──────────┴─────┴─────┘`NUMERIC_DTYPES` was deprecated in version 1.0.0. Define your own data type groups or use the `polars.selectors` module for selecting columns of a certain data type.PD.select_dtypes("number").mean()V1 1.444444
V2 5.000000
V3 1.000000
dtype: float64Modify all the columns using the same function.
DT[, lapply(.SD, rev)] V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 9 1.5 C
2: 2 8 1.0 B
3: 1 7 0.5 A
4: 2 6 1.5 C
5: 1 5 1.0 B
6: 2 4 0.5 A
7: 1 3 1.5 C
8: 2 2 1.0 B
9: 1 1 0.5 Adata.frame(lapply(DF, rev)) V1 V2 V3 V4
1 1 9 1.5 C
2 2 8 1.0 B
3 1 7 0.5 A
4 2 6 1.5 C
5 1 5 1.0 B
6 2 4 0.5 A
7 1 3 1.5 C
8 2 2 1.0 B
9 1 1 0.5 ATB |> mutate(across(everything(), rev))# A tibble: 9 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 9 1.5 C
2 2 8 1 B
3 1 7 0.5 A
4 2 6 1.5 C
5 1 5 1 B
6 2 4 0.5 A
7 1 3 1.5 C
8 2 2 1 B
9 1 1 0.5 APL.with_columns(pl.all().reverse())shape: (9, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 9 ┆ 1.5 ┆ C │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 1 ┆ 0.5 ┆ A │
└─────┴─────┴─────┴─────┘PD.iloc[::-1].reset_index(drop=True) V1 V2 V3 V4
0 1 9 1.5 C
1 2 8 1.0 B
2 1 7 0.5 A
3 2 6 1.5 C
4 1 5 1.0 B
5 2 4 0.5 A
6 1 3 1.5 C
7 2 2 1.0 B
8 1 1 0.5 AApply a transformation to each element of the variables selected.
DT[, lapply(.SD, sqrt), .SDcols = V1:V2] V1 V2
<num> <num>
1: 1.000000 1.000000
2: 1.414214 1.414214
3: 1.000000 1.732051
4: 1.414214 2.000000
5: 1.000000 2.236068
6: 1.414214 2.449490
7: 1.000000 2.645751
8: 1.414214 2.828427
9: 1.000000 3.000000TODO
TODO
PL.with_columns(pl.col("V1", "V2") * 2)shape: (9, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 2 ┆ 2 ┆ 0.5 ┆ A │
│ 4 ┆ 4 ┆ 1.0 ┆ B │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 4 ┆ 8 ┆ 0.5 ┆ A │
│ 2 ┆ 10 ┆ 1.0 ┆ B │
│ 4 ┆ 12 ┆ 1.5 ┆ C │
│ 2 ┆ 14 ┆ 0.5 ┆ A │
│ 4 ┆ 16 ┆ 1.0 ┆ B │
│ 2 ┆ 18 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘PD.assign(V1=PD["V1"] * 2, V2=PD["V2"] * 2) V1 V2 V3 V4
0 2 2 0.5 A
1 4 4 1.0 B
2 2 6 1.5 C
3 4 8 0.5 A
4 2 10 1.0 B
5 4 12 1.5 C
6 2 14 0.5 A
7 4 16 1.0 B
8 2 18 1.5 CApply a transformation for each element of the variables selected.
DT[, names(.SD) := lapply(.SD, sqrt), .SDcols = V1:V3]
DT V1 V2 V3 V4
<num> <num> <num> <char>
1: 1.000000 1.000000 0.7071068 A
2: 1.414214 1.414214 1.0000000 B
3: 1.000000 1.732051 1.2247449 C
4: 1.414214 2.000000 0.7071068 A
5: 1.000000 2.236068 1.0000000 B
6: 1.414214 2.449490 1.2247449 C
7: 1.000000 2.645751 0.7071068 A
8: 1.414214 2.828427 1.0000000 B
9: 1.000000 3.000000 1.2247449 C V1 V2 V3 V4
<num> <num> <num> <char>
1: 1.000000 1.000000 0.7071068 A
2: 1.414214 1.414214 1.0000000 B
3: 1.000000 1.732051 1.2247449 C
4: 1.414214 2.000000 0.7071068 A
5: 1.000000 2.236068 1.0000000 B
6: 1.414214 2.449490 1.2247449 C
7: 1.000000 2.645751 0.7071068 A
8: 1.414214 2.828427 1.0000000 B
9: 1.000000 3.000000 1.2247449 CPL.with_columns(pl.col("V1", "V2").cast(pl.Float64))shape: (9, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ f64 ┆ f64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1.0 ┆ 1.0 ┆ 0.5 ┆ A │
│ 2.0 ┆ 2.0 ┆ 1.0 ┆ B │
│ 1.0 ┆ 3.0 ┆ 1.5 ┆ C │
│ 2.0 ┆ 4.0 ┆ 0.5 ┆ A │
│ 1.0 ┆ 5.0 ┆ 1.0 ┆ B │
│ 2.0 ┆ 6.0 ┆ 1.5 ┆ C │
│ 1.0 ┆ 7.0 ┆ 0.5 ┆ A │
│ 2.0 ┆ 8.0 ┆ 1.0 ┆ B │
│ 1.0 ┆ 9.0 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘PD.astype({"V1": float, "V2": float}) V1 V2 V3 V4
0 1.0 1.0 0.5 A
1 2.0 2.0 1.0 B
2 1.0 3.0 1.5 C
3 2.0 4.0 0.5 A
4 1.0 5.0 1.0 B
5 2.0 6.0 1.5 C
6 1.0 7.0 0.5 A
7 2.0 8.0 1.0 B
8 1.0 9.0 1.5 CApply a transformation to each element of the variables selected by condition.
DT[, .SD - 1, .SDcols = is.numeric] V1 V2 V3
<num> <num> <num>
1: 0.0000000 0.0000000 -0.2928932
2: 0.4142136 0.4142136 0.0000000
3: 0.0000000 0.7320508 0.2247449
4: 0.4142136 1.0000000 -0.2928932
5: 0.0000000 1.2360680 0.0000000
6: 0.4142136 1.4494897 0.2247449
7: 0.0000000 1.6457513 -0.2928932
8: 0.4142136 1.8284271 0.0000000
9: 0.0000000 2.0000000 0.2247449data.frame(lapply(DF,
\(x) if (is.numeric(x)) x - 1 else x)) V1 V2 V3 V4
1 0 0 -0.5 A
2 1 1 0.0 B
3 0 2 0.5 C
4 1 3 -0.5 A
5 0 4 0.0 B
6 1 5 0.5 C
7 0 6 -0.5 A
8 1 7 0.0 B
9 0 8 0.5 CTB |>
transmute(across(where(is.numeric),
~ '-'(., 1L)))# A tibble: 9 × 3
V1 V2 V3
<int> <int> <dbl>
1 0 0 -0.5
2 1 1 0
3 0 2 0.5
4 1 3 -0.5
5 0 4 0
6 1 5 0.5
7 0 6 -0.5
8 1 7 0
9 0 8 0.5PL.with_columns(pl.col(pl.NUMERIC_DTYPES) * 2)shape: (9, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 2 ┆ 2 ┆ 1.0 ┆ A │
│ 4 ┆ 4 ┆ 2.0 ┆ B │
│ 2 ┆ 6 ┆ 3.0 ┆ C │
│ 4 ┆ 8 ┆ 1.0 ┆ A │
│ 2 ┆ 10 ┆ 2.0 ┆ B │
│ 4 ┆ 12 ┆ 3.0 ┆ C │
│ 2 ┆ 14 ┆ 1.0 ┆ A │
│ 4 ┆ 16 ┆ 2.0 ┆ B │
│ 2 ┆ 18 ┆ 3.0 ┆ C │
└─────┴─────┴─────┴─────┘`NUMERIC_DTYPES` was deprecated in version 1.0.0. Define your own data type groups or use the `polars.selectors` module for selecting columns of a certain data type.PD.select_dtypes("number").mul(2) V1 V2 V3
0 2 2 1.0
1 4 4 2.0
2 2 6 3.0
3 4 8 1.0
4 2 10 2.0
5 4 12 3.0
6 2 14 1.0
7 4 16 2.0
8 2 18 3.0Apply a transformation to each element of the variables selected by condition.
DT[, names(.SD) := lapply(.SD, as.integer),
.SDcols = is.numeric]
DT V1 V2 V3 V4
<int> <int> <int> <char>
1: 1 1 0 A
2: 1 1 1 B
3: 1 1 1 C
4: 1 2 0 A
5: 1 2 1 B
6: 1 2 1 C
7: 1 2 0 A
8: 1 2 1 B
9: 1 3 1 C V1 V2 V3 V4
<int> <int> <int> <char>
1: 1 1 0 A
2: 1 1 1 B
3: 1 1 1 C
4: 1 2 0 A
5: 1 2 1 B
6: 1 2 1 C
7: 1 2 0 A
8: 1 2 1 B
9: 1 3 1 CDF[sapply(DF, is.numeric)] <-
lapply(DF[sapply(DF, is.numeric)],
as.integer)
DF V1 V2 V3 V4
1 1 1 0 A
2 2 2 1 B
3 1 3 1 C
4 2 4 0 A
5 1 5 1 B
6 2 6 1 C
7 1 7 0 A
8 2 8 1 B
9 1 9 1 CTB = TB |>
mutate(across(where(is.numeric),
as.integer))
TB# A tibble: 9 × 4
V1 V2 V3 V4
<int> <int> <int> <chr>
1 1 1 0 A
2 2 2 1 B
3 1 3 1 C
4 2 4 0 A
5 1 5 1 B
6 2 6 1 C
7 1 7 0 A
8 2 8 1 B
9 1 9 1 CPL.with_columns(pl.col(pl.NUMERIC_DTYPES).fill_null(0))shape: (9, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘`NUMERIC_DTYPES` was deprecated in version 1.0.0. Define your own data type groups or use the `polars.selectors` module for selecting columns of a certain data type.PD.fillna({c: 0 for c in PD.select_dtypes("number")}) V1 V2 V3 V4
0 1 1 0.5 A
1 2 2 1.0 B
2 1 3 1.5 C
3 2 4 0.5 A
4 1 5 1.0 B
5 2 6 1.5 C
6 1 7 0.5 A
7 2 8 1.0 B
8 1 9 1.5 CCombine multiple functions in a single statement.
DT[, by = V4,
.(V1[1:2], "X")] V4 V1 V2
<char> <int> <char>
1: A 1 X
2: A 1 X
3: B 1 X
4: B 1 X
5: C 1 X
6: C 1 XDF = do.call(rbind,
by(DF, DF$V4, function(sub) {
head(data.frame(V1 = sub$V1,
V2 = "X"), 2)
}))TB |>
group_by(V4) |>
slice(1:2) |>
transmute(V1 = V1,
V2 = "X")# A tibble: 6 × 3
# Groups: V4 [3]
V4 V1 V2
<chr> <int> <chr>
1 A 1 X
2 A 2 X
3 B 2 X
4 B 1 X
5 C 1 X
6 C 2 XPL.with_columns(pl.col("V1").sum().alias("sumV1"), pl.col("V2").mean().alias("meanV2"))shape: (9, 6)
┌─────┬─────┬─────┬─────┬───────┬────────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 ┆ sumV1 ┆ meanV2 │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str ┆ i64 ┆ f64 │
╞═════╪═════╪═════╪═════╪═══════╪════════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A ┆ 13 ┆ 5.0 │
│ 2 ┆ 2 ┆ 1.0 ┆ B ┆ 13 ┆ 5.0 │
│ 1 ┆ 3 ┆ 1.5 ┆ C ┆ 13 ┆ 5.0 │
│ 2 ┆ 4 ┆ 0.5 ┆ A ┆ 13 ┆ 5.0 │
│ 1 ┆ 5 ┆ 1.0 ┆ B ┆ 13 ┆ 5.0 │
│ 2 ┆ 6 ┆ 1.5 ┆ C ┆ 13 ┆ 5.0 │
│ 1 ┆ 7 ┆ 0.5 ┆ A ┆ 13 ┆ 5.0 │
│ 2 ┆ 8 ┆ 1.0 ┆ B ┆ 13 ┆ 5.0 │
│ 1 ┆ 9 ┆ 1.5 ┆ C ┆ 13 ┆ 5.0 │
└─────┴─────┴─────┴─────┴───────┴────────┘PD.assign(sumV1=PD["V1"].sum(), meanV2=PD["V2"].mean()) V1 V2 V3 V4 sumV1 meanV2
0 1 1 0.5 A 13 5.0
1 2 2 1.0 B 13 5.0
2 1 3 1.5 C 13 5.0
3 2 4 0.5 A 13 5.0
4 1 5 1.0 B 13 5.0
5 2 6 1.5 C 13 5.0
6 1 7 0.5 A 13 5.0
7 2 8 1.0 B 13 5.0
8 1 9 1.5 C 13 5.0refresh_data()refresh_python()Chain (pipe)
Expression chaining allows you to perform multiple operations in sequence without creating intermediate objects.
# Chain operations using [][]
DT[, by = V4, .(V1sum = sum(V1))][
V1sum > 4]
# Sort results in descending order
DT[, by = V4, .(V1sum = sum(V1))][
order(-V1sum)] V4 V1sum
<char> <int>
1: B 5 V4 V1sum
<char> <int>
1: B 5
2: A 4
3: C 4# Chain using intermediate assignment
subset(aggregate(V1 ~ V4,
data = DF,
FUN = sum), V1 > 4)
# Sort results
# TODO V4 V1
2 B 5# Chain using pipe operator
TB |>
group_by(V4) |>
summarise(V1sum = sum(V1)) |>
filter(V1sum > 4)
# Sort results
TB |>
group_by(V4) |>
summarise(V1sum = sum(V1)) |>
arrange(desc(V1sum))# A tibble: 1 × 2
V4 V1sum
<chr> <int>
1 B 5# A tibble: 3 × 2
V4 V1sum
<chr> <int>
1 B 5
2 A 4
3 C 4PL.group_by("V4").agg(V1sum=pl.col("V1").sum()).filter(pl.col("V1sum") > 4).sort("V1sum", descending=True)shape: (1, 2)
┌─────┬───────┐
│ V4 ┆ V1sum │
│ --- ┆ --- │
│ str ┆ i64 │
╞═════╪═══════╡
│ B ┆ 5 │
└─────┴───────┘PD.groupby("V4", as_index=False).agg(V1sum=("V1", "sum")).query("V1sum > 4").sort_values("V1sum", ascending=False) V4 V1sum
1 B 5refresh_data()refresh_python()Join
First, let’s create example datasets for demonstrating joins.
x = data.table(
Id = c("A", "B", "C", "C"),
X1 = c(1L, 3L, 5L, 7L),
XY = c("x2", "x4", "x6", "x8")
)
y = data.table(
Id = c("A", "B", "B", "D"),
Y1 = c(1L, 3L, 5L, 7L),
XY = c("y1", "y3", "y5", "y7")
)Basic Joins
There are two basic ways to perform joins in data.table. First, we can use the standard merge() function that should be familiar to most R users.
Second, we can use the square bracket notation with the on argument. Here, the logic is that we select every row from the table outside that match rows from the table inside the bracket. The table inside the brackets is used as an “index”, so x[y] essentially means that we are merging x into y. The benefit of this syntax is that we can efficiently combine it with other operations like summaries, sorts, etc.
Note that, in data.table, the on argument specifies the joining columns, and the i. prefix refers to columns from the right table, while the x. prefix refers to columns from the left table.
# Left join (keep all rows from x)
# merge(x, y, all.x = TRUE, by = "Id")
y[x, on = "Id"]
# Right join (keep all rows from y)
# merge(x, y, all.y = TRUE, by = "Id")
x[y, on = "Id"]
# Inner join (keep only matching rows)
# merge(x, y, by = "Id"
x[y, on = "Id", nomatch = NULL]
# Anti join (keep rows from x with no match in y)
x[!y, on = "Id"]
# Full join (keep all rows)
merge(x, y, by = "Id", all = TRUE) Id Y1 XY X1 i.XY
<char> <int> <char> <int> <char>
1: A 1 y1 1 x2
2: B 3 y3 3 x4
3: B 5 y5 3 x4
4: C NA <NA> 5 x6
5: C NA <NA> 7 x8 Id X1 XY Y1 i.XY
<char> <int> <char> <int> <char>
1: A 1 x2 1 y1
2: B 3 x4 3 y3
3: B 3 x4 5 y5
4: D NA <NA> 7 y7 Id X1 XY Y1 i.XY
<char> <int> <char> <int> <char>
1: A 1 x2 1 y1
2: B 3 x4 3 y3
3: B 3 x4 5 y5 Id X1 XY
<char> <int> <char>
1: C 5 x6
2: C 7 x8Key: <Id>
Id X1 XY.x Y1 XY.y
<char> <int> <char> <int> <char>
1: A 1 x2 1 y1
2: B 3 x4 3 y3
3: B 3 x4 5 y5
4: C 5 x6 NA <NA>
5: C 7 x8 NA <NA>
6: D NA <NA> 7 y7# Left join (keep all rows from x)
merge(x, y, by = "Id", all.x = TRUE)
# Right join (keep all rows from y)
merge(x, y, by = "Id", all.y = TRUE)
# Inner join (keep only matching rows)
merge(x, y, by = "Id")
# Full join (keep all rows)
merge(x, y, by = "Id", all = TRUE)
# Anti join (more complex in base R)
x[!(x$Id %in% y$Id), ]Key: <Id>
Id X1 XY.x Y1 XY.y
<char> <int> <char> <int> <char>
1: A 1 x2 1 y1
2: B 3 x4 3 y3
3: B 3 x4 5 y5
4: C 5 x6 NA <NA>
5: C 7 x8 NA <NA>Key: <Id>
Id X1 XY.x Y1 XY.y
<char> <int> <char> <int> <char>
1: A 1 x2 1 y1
2: B 3 x4 3 y3
3: B 3 x4 5 y5
4: D NA <NA> 7 y7Key: <Id>
Id X1 XY.x Y1 XY.y
<char> <int> <char> <int> <char>
1: A 1 x2 1 y1
2: B 3 x4 3 y3
3: B 3 x4 5 y5Key: <Id>
Id X1 XY.x Y1 XY.y
<char> <int> <char> <int> <char>
1: A 1 x2 1 y1
2: B 3 x4 3 y3
3: B 3 x4 5 y5
4: C 5 x6 NA <NA>
5: C 7 x8 NA <NA>
6: D NA <NA> 7 y7 Id X1 XY
<char> <int> <char>
1: C 5 x6
2: C 7 x8# Left join (keep all rows from x)
left_join(x, y, by = "Id")
# Right join (keep all rows from y)
right_join(x, y, by = "Id")
# Inner join (keep only matching rows)
inner_join(x, y, by = "Id")
# Full join (keep all rows)
full_join(x, y, by = "Id")
# Anti join
anti_join(x, y, by = "Id") Id X1 XY.x Y1 XY.y
<char> <int> <char> <int> <char>
1: A 1 x2 1 y1
2: B 3 x4 3 y3
3: B 3 x4 5 y5
4: C 5 x6 NA <NA>
5: C 7 x8 NA <NA> Id X1 XY.x Y1 XY.y
<char> <int> <char> <int> <char>
1: A 1 x2 1 y1
2: B 3 x4 3 y3
3: B 3 x4 5 y5
4: D NA <NA> 7 y7 Id X1 XY.x Y1 XY.y
<char> <int> <char> <int> <char>
1: A 1 x2 1 y1
2: B 3 x4 3 y3
3: B 3 x4 5 y5 Id X1 XY.x Y1 XY.y
<char> <int> <char> <int> <char>
1: A 1 x2 1 y1
2: B 3 x4 3 y3
3: B 3 x4 5 y5
4: C 5 x6 NA <NA>
5: C 7 x8 NA <NA>
6: D NA <NA> 7 y7 Id X1 XY
<char> <int> <char>
1: C 5 x6
2: C 7 x8x = pl.DataFrame({"Id": ["A", "B", "C", "C"], "X1": [1, 3, 5, 7]})
y = pl.DataFrame({"Id": ["A", "B", "B", "D"], "Y1": [1, 3, 5, 7]})
x.join(y, on="Id", how="left")
x.join(y, on="Id", how="right")
x.join(y, on="Id", how="inner")
x.join(y, on="Id", how="full", coalesce=True)
x.join(y, on="Id", how="anti")shape: (5, 3)
┌─────┬─────┬──────┐
│ Id ┆ X1 ┆ Y1 │
│ --- ┆ --- ┆ --- │
│ str ┆ i64 ┆ i64 │
╞═════╪═════╪══════╡
│ A ┆ 1 ┆ 1 │
│ B ┆ 3 ┆ 3 │
│ B ┆ 3 ┆ 5 │
│ C ┆ 5 ┆ null │
│ C ┆ 7 ┆ null │
└─────┴─────┴──────┘shape: (4, 3)
┌──────┬─────┬─────┐
│ X1 ┆ Id ┆ Y1 │
│ --- ┆ --- ┆ --- │
│ i64 ┆ str ┆ i64 │
╞══════╪═════╪═════╡
│ 1 ┆ A ┆ 1 │
│ 3 ┆ B ┆ 3 │
│ 3 ┆ B ┆ 5 │
│ null ┆ D ┆ 7 │
└──────┴─────┴─────┘shape: (3, 3)
┌─────┬─────┬─────┐
│ Id ┆ X1 ┆ Y1 │
│ --- ┆ --- ┆ --- │
│ str ┆ i64 ┆ i64 │
╞═════╪═════╪═════╡
│ A ┆ 1 ┆ 1 │
│ B ┆ 3 ┆ 3 │
│ B ┆ 3 ┆ 5 │
└─────┴─────┴─────┘shape: (6, 3)
┌─────┬──────┬──────┐
│ Id ┆ X1 ┆ Y1 │
│ --- ┆ --- ┆ --- │
│ str ┆ i64 ┆ i64 │
╞═════╪══════╪══════╡
│ A ┆ 1 ┆ 1 │
│ B ┆ 3 ┆ 3 │
│ B ┆ 3 ┆ 5 │
│ D ┆ null ┆ 7 │
│ C ┆ 5 ┆ null │
│ C ┆ 7 ┆ null │
└─────┴──────┴──────┘shape: (2, 2)
┌─────┬─────┐
│ Id ┆ X1 │
│ --- ┆ --- │
│ str ┆ i64 │
╞═════╪═════╡
│ C ┆ 5 │
│ C ┆ 7 │
└─────┴─────┘x = pd.DataFrame({"Id": ["A", "B", "C", "C"], "X1": [1, 3, 5, 7]})
y = pd.DataFrame({"Id": ["A", "B", "B", "D"], "Y1": [1, 3, 5, 7]})
x.merge(y, on="Id", how="left")
x.merge(y, on="Id", how="right")
x.merge(y, on="Id", how="inner")
x.merge(y, on="Id", how="outer")
x.loc[~x["Id"].isin(y["Id"])] Id X1 Y1
0 A 1 1.0
1 B 3 3.0
2 B 3 5.0
3 C 5 NaN
4 C 7 NaN Id X1 Y1
0 A 1.0 1
1 B 3.0 3
2 B 3.0 5
3 D NaN 7 Id X1 Y1
0 A 1 1
1 B 3 3
2 B 3 5 Id X1 Y1
0 A 1.0 1.0
1 B 3.0 3.0
2 B 3.0 5.0
3 C 5.0 NaN
4 C 7.0 NaN
5 D NaN 7.0 Id X1
2 C 5
3 C 7Reshape
Wide to long
Converting data from wide format (where variables are in columns) to long format (where variables become rows).
melt(DT,
id.vars = "V4",
variable.name = "Variable",
value.name = "Value") V4 Variable Value
<char> <fctr> <num>
1: A V1 1.0
2: B V1 2.0
3: C V1 1.0
4: A V1 2.0
5: B V1 1.0
6: C V1 2.0
7: A V1 1.0
8: B V1 2.0
9: C V1 1.0
10: A V2 1.0
11: B V2 2.0
12: C V2 3.0
13: A V2 4.0
14: B V2 5.0
15: C V2 6.0
16: A V2 7.0
17: B V2 8.0
18: C V2 9.0
19: A V3 0.5
20: B V3 1.0
21: C V3 1.5
22: A V3 0.5
23: B V3 1.0
24: C V3 1.5
25: A V3 0.5
26: B V3 1.0
27: C V3 1.5
V4 Variable Value
<char> <fctr> <num>'measure.vars' [V1, V2, V3] are not all of the same type. By order of hierarchy, the molten data value column will be of type 'double'. All measure variables not of type 'double' will be coerced too. Check DETAILS in ?melt.data.table for more on coercion.reshape(DF,
varying = setdiff(names(DF), "V4"),
v.names = "value",
timevar = "variable",
times = setdiff(names(DF), "V4"),
direction = "long") V4 variable value id
1.V1 A V1 1.0 1
2.V1 B V1 2.0 2
3.V1 C V1 1.0 3
4.V1 A V1 2.0 4
5.V1 B V1 1.0 5
6.V1 C V1 2.0 6
7.V1 A V1 1.0 7
8.V1 B V1 2.0 8
9.V1 C V1 1.0 9
1.V2 A V2 1.0 1
2.V2 B V2 2.0 2
3.V2 C V2 3.0 3
4.V2 A V2 4.0 4
5.V2 B V2 5.0 5
6.V2 C V2 6.0 6
7.V2 A V2 7.0 7
8.V2 B V2 8.0 8
9.V2 C V2 9.0 9
1.V3 A V3 0.5 1
2.V3 B V3 1.0 2
3.V3 C V3 1.5 3
4.V3 A V3 0.5 4
5.V3 B V3 1.0 5
6.V3 C V3 1.5 6
7.V3 A V3 0.5 7
8.V3 B V3 1.0 8
9.V3 C V3 1.5 9TB |> tidyr::pivot_longer(
cols = c("V1", "V2", "V3"),
names_to = "Variable",
values_to = "Value")# A tibble: 27 × 3
V4 Variable Value
<chr> <chr> <dbl>
1 A V1 1
2 A V2 1
3 A V3 0.5
4 B V1 2
5 B V2 2
6 B V3 1
7 C V1 1
8 C V2 3
9 C V3 1.5
10 A V1 2
# ℹ 17 more rowsPL.unpivot(index="V4", variable_name="Variable", value_name="Value")shape: (27, 3)
┌─────┬──────────┬───────┐
│ V4 ┆ Variable ┆ Value │
│ --- ┆ --- ┆ --- │
│ str ┆ str ┆ f64 │
╞═════╪══════════╪═══════╡
│ A ┆ V1 ┆ 1.0 │
│ B ┆ V1 ┆ 2.0 │
│ C ┆ V1 ┆ 1.0 │
│ A ┆ V1 ┆ 2.0 │
│ B ┆ V1 ┆ 1.0 │
│ … ┆ … ┆ … │
│ B ┆ V3 ┆ 1.0 │
│ C ┆ V3 ┆ 1.5 │
│ A ┆ V3 ┆ 0.5 │
│ B ┆ V3 ┆ 1.0 │
│ C ┆ V3 ┆ 1.5 │
└─────┴──────────┴───────┘PD.melt(id_vars="V4", var_name="Variable", value_name="Value") V4 Variable Value
0 A V1 1.0
1 B V1 2.0
2 C V1 1.0
3 A V1 2.0
4 B V1 1.0
5 C V1 2.0
6 A V1 1.0
7 B V1 2.0
8 C V1 1.0
9 A V2 1.0
10 B V2 2.0
11 C V2 3.0
12 A V2 4.0
13 B V2 5.0
14 C V2 6.0
15 A V2 7.0
16 B V2 8.0
17 C V2 9.0
18 A V3 0.5
19 B V3 1.0
20 C V3 1.5
21 A V3 0.5
22 B V3 1.0
23 C V3 1.5
24 A V3 0.5
25 B V3 1.0
26 C V3 1.5Long to wide
Converting data from long format back to wide format.
# Create example long data
long = CJ(a = 1:2, b = 1:2, c = c("x", "y"))
long[, let(d = rnorm(8))]
dcast(long, a + b ~ c)Key: <a, b, c>
a b c d
<int> <int> <char> <num>
1: 1 1 x -0.02338218
2: 1 1 y 0.42891442
3: 1 2 x 0.70248790
4: 1 2 y 0.86931623
5: 2 1 x 0.38553837
6: 2 1 y -1.41591750
7: 2 2 x -0.09252323
8: 2 2 y -0.88496360Key: <a, b>
a b x y
<int> <int> <num> <num>
1: 1 1 -0.02338218 0.4289144
2: 1 2 0.70248790 0.8693162
3: 2 1 0.38553837 -1.4159175
4: 2 2 -0.09252323 -0.8849636Using 'd' as value column. Use 'value.var' to override# Create example long data
long = expand.grid(a = 1:2, b = 1:2, c = c("x", "y"))
long$d = rnorm(8)
reshape(long,
idvar = c("a", "b"),
timevar = "c",
direction = "wide") a b d.x d.y
1 1 1 -0.35219467 -0.4503642
2 2 1 -0.92816489 0.3570645
3 1 2 0.67548182 1.4765579
4 2 2 0.01580377 0.4535436# Create example long data
long = tidyr::expand_grid(a = 1:2, b = 1:2, c = c("x", "y"))
long$d = rnorm(8)
tidyr::pivot_wider(long,
id_cols = c("a", "b"),
names_from = "c",
values_from = "d")# A tibble: 4 × 4
a b x y
<int> <int> <dbl> <dbl>
1 1 1 0.127 0.415
2 1 2 -0.0540 -1.22
3 2 1 0.596 -0.672
4 2 2 1.15 1.21long = pl.DataFrame({
"a": [1, 1, 2, 2], "b": [1, 2, 1, 2],
"c": ["x", "y", "x", "y"], "d": [0.1, 0.2, 0.3, 0.4],
})
long.pivot(on="c", index=["a", "b"], values="d")shape: (4, 4)
┌─────┬─────┬──────┬──────┐
│ a ┆ b ┆ x ┆ y │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ f64 │
╞═════╪═════╪══════╪══════╡
│ 1 ┆ 1 ┆ 0.1 ┆ null │
│ 1 ┆ 2 ┆ null ┆ 0.2 │
│ 2 ┆ 1 ┆ 0.3 ┆ null │
│ 2 ┆ 2 ┆ null ┆ 0.4 │
└─────┴─────┴──────┴──────┘long = pd.DataFrame({
"a": [1, 1, 2, 2], "b": [1, 2, 1, 2],
"c": ["x", "y", "x", "y"], "d": [0.1, 0.2, 0.3, 0.4],
})
long.pivot(index=["a", "b"], columns="c", values="d")c x y
a b
1 1 0.1 NaN
2 NaN 0.2
2 1 0.3 NaN
2 NaN 0.4Split rows
Separating data into groups based on a factor.
split(DT, by = "V4")$A
V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 A
2: 2 4 0.5 A
3: 1 7 0.5 A
$B
V1 V2 V3 V4
<int> <int> <num> <char>
1: 2 2 1 B
2: 1 5 1 B
3: 2 8 1 B
$C
V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 3 1.5 C
2: 2 6 1.5 C
3: 1 9 1.5 Csplit(DF, DF$V4)$A
V1 V2 V3 V4
1 1 1 0.5 A
4 2 4 0.5 A
7 1 7 0.5 A
$B
V1 V2 V3 V4
2 2 2 1 B
5 1 5 1 B
8 2 8 1 B
$C
V1 V2 V3 V4
3 1 3 1.5 C
6 2 6 1.5 C
9 1 9 1.5 CTB |> group_split(V4)<list_of<
tbl_df<
V1: integer
V2: integer
V3: double
V4: character
>
>[3]>
[[1]]
# A tibble: 3 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 1 0.5 A
2 2 4 0.5 A
3 1 7 0.5 A
[[2]]
# A tibble: 3 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 2 2 1 B
2 1 5 1 B
3 2 8 1 B
[[3]]
# A tibble: 3 × 4
V1 V2 V3 V4
<int> <int> <dbl> <chr>
1 1 3 1.5 C
2 2 6 1.5 C
3 1 9 1.5 CPL.partition_by("V4", as_dict=True){('A',): shape: (3, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
└─────┴─────┴─────┴─────┘, ('B',): shape: (3, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
└─────┴─────┴─────┴─────┘, ('C',): shape: (3, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘}{key: value for key, value in PD.groupby("V4")}{'A': V1 V2 V3 V4
0 1 1 0.5 A
3 2 4 0.5 A
6 1 7 0.5 A, 'B': V1 V2 V3 V4
1 2 2 1.0 B
4 1 5 1.0 B
7 2 8 1.0 B, 'C': V1 V2 V3 V4
2 1 3 1.5 C
5 2 6 1.5 C
8 1 9 1.5 C}Split columns
Separating a single column into multiple columns based on a delimiter.
# Create example data
tmp = data.table(a = c("A:a", "B:b", "C:c"))
tmp[, c("w", "z") := tstrsplit(a, split = ":")]
tmp a w z
<char> <char> <char>
1: A:a A a
2: B:b B b
3: C:c C c a w z
<char> <char> <char>
1: A:a A a
2: B:b B b
3: C:c C c# Create example data
tmp = data.frame(a = c("A:a", "B:b", "C:c"))
tmp$w = sapply(strsplit(as.character(tmp$a), ":"), `[`, 1)
tmp$z = sapply(strsplit(as.character(tmp$a), ":"), `[`, 2)
tmp a w z
1 A:a A a
2 B:b B b
3 C:c C c# Create example data
tmp = tibble(a = c("A:a", "B:b", "C:c"))
tmp = tidyr::separate(tmp, a, c("w", "z"), remove = FALSE)
tmp# A tibble: 3 × 3
a w z
<chr> <chr> <chr>
1 A:a A a
2 B:b B b
3 C:c C ctmp = pl.DataFrame({"a": ["A:a", "B:b", "C:c"]})
tmp.with_columns(pl.col("a").str.split_exact(":", 1).struct.rename_fields(["w", "z"])).unnest("a")shape: (3, 2)
┌─────┬─────┐
│ w ┆ z │
│ --- ┆ --- │
│ str ┆ str │
╞═════╪═════╡
│ A ┆ a │
│ B ┆ b │
│ C ┆ c │
└─────┴─────┘tmp = pd.DataFrame({"a": ["A:a", "B:b", "C:c"]})
tmp[["w", "z"]] = tmp["a"].str.split(":", expand=True)Bind rows
Combining multiple datasets by rows. To begin, we create example data.
x = data.table(1:3)
y = data.table(4:6)
z = data.table(7:9, 0L)# Simple row bind
rbind(x, y)
# Simple row bind
rbindlist(list(x, y, x))
# Row bind with different columns
rbind(x, z, fill = TRUE)
# Bind list of data.tables with ID column
rbindlist(list(x, y), idcol = TRUE) V1
<int>
1: 1
2: 2
3: 3
4: 4
5: 5
6: 6 V1
<int>
1: 1
2: 2
3: 3
4: 4
5: 5
6: 6
7: 1
8: 2
9: 3 V1 V2
<int> <int>
1: 1 NA
2: 2 NA
3: 3 NA
4: 7 0
5: 8 0
6: 9 0 .id V1
<int> <int>
1: 1 1
2: 1 2
3: 1 3
4: 2 4
5: 2 5
6: 2 6# Simple row bind
rbind(x, y)
# Simple row bind
do.call(rbind, list(x, y, x))
# Bind with ID column
rbind(
cbind(x, id = 1),
cbind(y, id = 2)
) V1
<int>
1: 1
2: 2
3: 3
4: 4
5: 5
6: 6 V1
<int>
1: 1
2: 2
3: 3
4: 4
5: 5
6: 6
7: 1
8: 2
9: 3 V1 id
<int> <num>
1: 1 1
2: 2 1
3: 3 1
4: 4 2
5: 5 2
6: 6 2# Simple row bind
bind_rows(x, y)
# Simple row bind
bind_rows(list(x, y, x))
# Row bind with different columns
bind_rows(x, z)
# Bind list with ID column
bind_rows(list(x, y), .id = "id") V1
<int>
1: 1
2: 2
3: 3
4: 4
5: 5
6: 6 V1
<int>
1: 1
2: 2
3: 3
4: 4
5: 5
6: 6
7: 1
8: 2
9: 3 V1 V2
<int> <int>
1: 1 NA
2: 2 NA
3: 3 NA
4: 7 0
5: 8 0
6: 9 0 id V1
<char> <int>
1: 1 1
2: 1 2
3: 1 3
4: 2 4
5: 2 5
6: 2 6x = pl.DataFrame({"value": [1, 2, 3]})
y = pl.DataFrame({"value": [4, 5, 6]})
z = pl.DataFrame({"value": [7, 8, 9], "other": [0, 0, 0]})
pl.concat([x, y])
pl.concat([x, z], how="diagonal")shape: (6, 1)
┌───────┐
│ value │
│ --- │
│ i64 │
╞═══════╡
│ 1 │
│ 2 │
│ 3 │
│ 4 │
│ 5 │
│ 6 │
└───────┘shape: (6, 2)
┌───────┬───────┐
│ value ┆ other │
│ --- ┆ --- │
│ i64 ┆ i64 │
╞═══════╪═══════╡
│ 1 ┆ null │
│ 2 ┆ null │
│ 3 ┆ null │
│ 7 ┆ 0 │
│ 8 ┆ 0 │
│ 9 ┆ 0 │
└───────┴───────┘x = pd.DataFrame({"value": [1, 2, 3]})
y = pd.DataFrame({"value": [4, 5, 6]})
z = pd.DataFrame({"value": [7, 8, 9], "other": [0, 0, 0]})
pd.concat([x, y], ignore_index=True)
pd.concat([x, z], ignore_index=True) value
0 1
1 2
2 3
3 4
4 5
5 6 value other
0 1 NaN
1 2 NaN
2 3 NaN
3 7 0.0
4 8 0.0
5 9 0.0Bind columns
# Column bind
base::cbind(x, y) V1 V1
<int> <int>
1: 1 4
2: 2 5
3: 3 6# Column bind
cbind(x, y) V1 V1
<int> <int>
1: 1 4
2: 2 5
3: 3 6# Column bind
bind_cols(x, y) V1...1 V1...2
<int> <int>
1: 1 4
2: 2 5
3: 3 6New names:
• `V1` -> `V1...1`
• `V1` -> `V1...2`x = pl.DataFrame({"x": [1, 2, 3]})
y = pl.DataFrame({"y": [4, 5, 6]})
pl.concat([x, y], how="horizontal")shape: (3, 2)
┌─────┬─────┐
│ x ┆ y │
│ --- ┆ --- │
│ i64 ┆ i64 │
╞═════╪═════╡
│ 1 ┆ 4 │
│ 2 ┆ 5 │
│ 3 ┆ 6 │
└─────┴─────┘the default behavior of `how='horizontal'` for `concat` is deprecated and will require equal heights in the next breaking release. Use `how='horizontal_extend'` to keep the current behavior.
(Deprecated in version 1.42.1)x = pd.DataFrame({"x": [1, 2, 3]})
y = pd.DataFrame({"y": [4, 5, 6]})
pd.concat([x, y], axis="columns") x y
0 1 4
1 2 5
2 3 6Set Operations
Set operations treat datasets as sets and perform mathematical set operations on them.
x <- data.table(c(1, 2, 2, 3, 3))
y <- data.table(c(2, 2, 3, 4, 4))# Intersection (rows present in both x and y)
fintersect(x, y)
# Set difference (rows in x but not in y)
fsetdiff(x, y)
# Include duplicates
fsetdiff(x, y, all = TRUE)
# Union (combine unique rows)
funion(x, y)
# Include duplicates
funion(x, y, all = TRUE)
# Test for equality
fsetequal(x, x[order(-V1),])
all.equal(x, x) V1
<num>
1: 2
2: 3 V1
<num>
1: 1 V1
<num>
1: 1
2: 3 V1
<num>
1: 1
2: 2
3: 3
4: 4 V1
<num>
1: 1
2: 2
3: 2
4: 3
5: 3
6: 2
7: 2
8: 3
9: 4
10: 4[1] TRUE[1] TRUE# Intersection
intersect(x, y)
# Set difference
setdiff(x, y)
# Union (unique rows)
union(x, y)
# With duplicates
unlist(c(x, y))
# Test for equality
identical(x, x)
all.equal(x, x) V1
<num>
1: 2
2: 3 V1
<num>
1: 1 V1
<num>
1: 1
2: 2
3: 3
4: 4V11 V12 V13 V14 V15 V11 V12 V13 V14 V15
1 2 2 3 3 2 2 3 4 4[1] TRUE[1] TRUE# Intersection
dplyr::intersect(x, y)
# Set difference
dplyr::setdiff(x, y)
# Union (unique rows)
dplyr::union(x, y)
# With duplicates
union_all(x, y)
# Test for equality
setequal(x, x[order(-V1),])
all.equal(x, x) V1
<num>
1: 2
2: 3 V1
<num>
1: 1 V1
<num>
1: 1
2: 2
3: 3
4: 4 V1
<num>
1: 1
2: 2
3: 2
4: 3
5: 3
6: 2
7: 2
8: 3
9: 4
10: 4[1] TRUE[1] TRUEx = pl.DataFrame({"value": [1, 2, 2, 3, 3]})
y = pl.DataFrame({"value": [2, 2, 3, 4, 4]})
x.join(y, on=x.columns, how="semi").unique()
x.join(y, on=x.columns, how="anti")
pl.concat([x, y]).unique()
x.equals(y)shape: (2, 1)
┌───────┐
│ value │
│ --- │
│ i64 │
╞═══════╡
│ 3 │
│ 2 │
└───────┘shape: (1, 1)
┌───────┐
│ value │
│ --- │
│ i64 │
╞═══════╡
│ 1 │
└───────┘shape: (4, 1)
┌───────┐
│ value │
│ --- │
│ i64 │
╞═══════╡
│ 3 │
│ 2 │
│ 1 │
│ 4 │
└───────┘Falsex = pd.DataFrame({"value": [1, 2, 2, 3, 3]})
y = pd.DataFrame({"value": [2, 2, 3, 4, 4]})
x.merge(y, how="inner").drop_duplicates()
x.merge(y, how="left", indicator=True).query("_merge == 'left_only'")
pd.concat([x, y]).drop_duplicates()
x.equals(y) value
0 2
4 3 value _merge
0 1 left_only value
0 1
1 2
3 3
3 4FalseRead and write
# Write to CSV
fwrite(DT, "DT.csv")
# Write tab-delimited
fwrite(DT, "DT.txt", sep = "\t")
# Read CSV or tab-delimited
DT1 = fread("DT.csv")
DT2 = fread("DT.txt", sep = "\t")
# Read specific columns
DT3 = fread("DT.csv", select = c("V1", "V4"))
DT4 = fread("DT.csv", drop = "V4")
# Combine multiple files
rbindlist(lapply(c("DT.csv", "DT.csv"), fread)) V1 V2 V3 V4
<int> <int> <num> <char>
1: 1 1 0.5 A
2: 2 2 1.0 B
3: 1 3 1.5 C
4: 2 4 0.5 A
5: 1 5 1.0 B
6: 2 6 1.5 C
7: 1 7 0.5 A
8: 2 8 1.0 B
9: 1 9 1.5 C
10: 1 1 0.5 A
11: 2 2 1.0 B
12: 1 3 1.5 C
13: 2 4 0.5 A
14: 1 5 1.0 B
15: 2 6 1.5 C
16: 1 7 0.5 A
17: 2 8 1.0 B
18: 1 9 1.5 C# Write to CSV
write.csv(DF, "DF.csv", row.names = FALSE)
# Write tab-delimited
write.table(DF, "DF.txt", sep = "\t",
row.names = FALSE, col.names = TRUE)
# Read CSV or tab-delimited
DF1 = read.csv("DF.csv")
DF2 = read.table("DF.txt", sep = "\t", header = TRUE)
# Read specific columns
DF3 = read.csv("DF.csv")[, c("V1", "V4")]
DF4 = read.csv("DF.csv")[, !(names(read.csv("DF.csv")) %in% "V4")]
# Combine multiple files
do.call(rbind, lapply(c("DF.csv", "DF.csv"), read.csv)) V1 V2 V3 V4
1 1 1 0.5 A
2 2 2 1.0 B
3 1 3 1.5 C
4 2 4 0.5 A
5 1 5 1.0 B
6 2 6 1.5 C
7 1 7 0.5 A
8 2 8 1.0 B
9 1 9 1.5 C
10 1 1 0.5 A
11 2 2 1.0 B
12 1 3 1.5 C
13 2 4 0.5 A
14 1 5 1.0 B
15 2 6 1.5 C
16 1 7 0.5 A
17 2 8 1.0 B
18 1 9 1.5 C# Write to CSV
TB |> readr::write_csv("TB.csv")
# Write tab-delimited
TB |> readr::write_delim("TB.txt", delim = "\t")
# Read CSV or tab-delimited
TB1 = readr::read_csv("TB.csv")
TB2 = readr::read_delim("TB.txt", delim = "\t")
# Combine multiple files
c("TB.csv", "TB.csv") |>
purrr::map_dfr(readr::read_csv)# A tibble: 18 × 4
V1 V2 V3 V4
<dbl> <dbl> <dbl> <chr>
1 1 1 0.5 A
2 2 2 1 B
3 1 3 1.5 C
4 2 4 0.5 A
5 1 5 1 B
6 2 6 1.5 C
7 1 7 0.5 A
8 2 8 1 B
9 1 9 1.5 C
10 1 1 0.5 A
11 2 2 1 B
12 1 3 1.5 C
13 2 4 0.5 A
14 1 5 1 B
15 2 6 1.5 C
16 1 7 0.5 A
17 2 8 1 B
18 1 9 1.5 CRows: 9 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): V4
dbl (3): V1, V2, V3
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 9 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: "\t"
chr (1): V4
dbl (3): V1, V2, V3
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 9 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): V4
dbl (3): V1, V2, V3
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Rows: 9 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): V4
dbl (3): V1, V2, V3
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.PL.write_csv("PL.csv")
PL.write_csv("PL.txt", separator="\t")
pl.read_csv("PL.csv")
pl.read_csv("PL.csv", columns=["V1", "V4"])shape: (9, 4)
┌─────┬─────┬─────┬─────┐
│ V1 ┆ V2 ┆ V3 ┆ V4 │
│ --- ┆ --- ┆ --- ┆ --- │
│ i64 ┆ i64 ┆ f64 ┆ str │
╞═════╪═════╪═════╪═════╡
│ 1 ┆ 1 ┆ 0.5 ┆ A │
│ 2 ┆ 2 ┆ 1.0 ┆ B │
│ 1 ┆ 3 ┆ 1.5 ┆ C │
│ 2 ┆ 4 ┆ 0.5 ┆ A │
│ 1 ┆ 5 ┆ 1.0 ┆ B │
│ 2 ┆ 6 ┆ 1.5 ┆ C │
│ 1 ┆ 7 ┆ 0.5 ┆ A │
│ 2 ┆ 8 ┆ 1.0 ┆ B │
│ 1 ┆ 9 ┆ 1.5 ┆ C │
└─────┴─────┴─────┴─────┘shape: (9, 2)
┌─────┬─────┐
│ V1 ┆ V4 │
│ --- ┆ --- │
│ i64 ┆ str │
╞═════╪═════╡
│ 1 ┆ A │
│ 2 ┆ B │
│ 1 ┆ C │
│ 2 ┆ A │
│ 1 ┆ B │
│ 2 ┆ C │
│ 1 ┆ A │
│ 2 ┆ B │
│ 1 ┆ C │
└─────┴─────┘PD.to_csv("PD.csv", index=False)
PD.to_csv("PD.txt", sep="\t", index=False)
pd.read_csv("PD.csv")
pd.read_csv("PD.csv", usecols=["V1", "V4"]) V1 V2 V3 V4
0 1 1 0.5 A
1 2 2 1.0 B
2 1 3 1.5 C
3 2 4 0.5 A
4 1 5 1.0 B
5 2 6 1.5 C
6 1 7 0.5 A
7 2 8 1.0 B
8 1 9 1.5 C V1 V4
0 1 A
1 2 B
2 1 C
3 2 A
4 1 B
5 2 C
6 1 A
7 2 B
8 1 CClean up local files.
file.remove(c("DT.csv", "TB.csv", "DF.csv", "DT.txt", "TB.txt", "DF.txt"))[1] TRUE TRUE TRUE TRUE TRUE TRUEfrom pathlib import Path
for path in ("PL.csv", "PL.txt", "PD.csv", "PD.txt"):
Path(path).unlink(missing_ok=True)