{ "cells": [ { "cell_type": "markdown", "id": "intro", "metadata": {}, "source": [ "# R to pandas\n", "## A Translation Reference\n", "\n", "**STAT 540 — Computing in Statistics**\n", "Yen-Yi Ho · Department of Statistics, University of South Carolina\n", "\n", "---\n" ] }, { "cell_type": "markdown", "id": "c000", "metadata": {}, "source": [ "## How to use this\n", "\n", "Every table maps an **R idiom** to the **pandas** way of saying it. The right-hand column\n", "is what you type; the notes under each table flag the places where the translation is\n", "*not* one-to-one --- which is where the bugs live.\n", "\n", "Three conventions throughout:\n", "\n", "- `df` is a DataFrame, `s` is a Series, and `x`/`y` are vectors or columns.\n", "- Where dplyr and base R differ, both are shown. dplyr is usually the closer analogy.\n", "- A **Mind this** note means the two languages genuinely disagree. Read those twice.\n", "\n", "The companion notebook runs every pandas expression on a small worked example, so you\n", "can see the output rather than trust the table." ] }, { "cell_type": "markdown", "id": "c001", "metadata": {}, "source": [ "## The worked example\n", "\n", "Everything below runs against this five-row frame." ] }, { "cell_type": "code", "execution_count": null, "id": "c002", "metadata": {}, "outputs": [], "source": [ "import numpy as np\n", "import pandas as pd\n", "\n", "df = pd.DataFrame({\n", " \"id\": [\"a\", \"b\", \"c\", \"d\", \"e\"],\n", " \"grp\": [\"x\", \"y\", \"x\", \"y\", \"x\"],\n", " \"score\": [88.0, 92.5, np.nan, 71.0, 95.0],\n", " \"n\": [3, 1, 4, 1, 5],\n", "})\n", "df" ] }, { "cell_type": "markdown", "id": "c003", "metadata": {}, "source": [ "In R this would be\n", "\n", "```r\n", "df <- data.frame(\n", " id = c(\"a\", \"b\", \"c\", \"d\", \"e\"),\n", " grp = c(\"x\", \"y\", \"x\", \"y\", \"x\"),\n", " score = c(88, 92.5, NA, 71, 95),\n", " n = c(3, 1, 4, 1, 5),\n", " stringsAsFactors = FALSE\n", ")\n", "```" ] }, { "cell_type": "markdown", "id": "c004", "metadata": {}, "source": [ "# 1. The mental map\n", "\n", "| Concept | R | pandas |\n", "|:---|:---|:---|\n", "| Homogeneous array | `matrix`, `array` | `numpy.ndarray` |\n", "| Labeled 1-D vector | named `vector` | `pandas.Series` |\n", "| Table of columns | `data.frame`, `tibble` | `pandas.DataFrame` |\n", "| Row labels | `rownames(df)` | `df.index` |\n", "| Column labels | `names(df)`, `colnames(df)` | `df.columns` |\n", "| Missing value | `NA`, `NaN`, `NULL` | `NaN`, `None`, `pd.NA` |\n", "| Factor | `factor` | `category` dtype |\n", "| List | `list` | `dict` or `list` |\n", "| `NULL` | `NULL` | `None` |\n", "| Pipe | `%>%`, `\\|>` | method chaining in `( ... )` |\n", "\n", "**Mind this.** R's three missing-ish values collapse to roughly one in pandas: a float\n", "column stores `None` as `NaN`, so `None` and `np.nan` behave identically there. `pd.NA`\n", "is the newer sentinel used by the nullable dtypes (`Int64`, `boolean`, `string`)." ] }, { "cell_type": "markdown", "id": "c005", "metadata": {}, "source": [ "# 2. Inspecting\n", "\n", "| Task | R | pandas |\n", "|:---|:---|:---|\n", "| Dimensions | `dim(df)` | `df.shape` |\n", "| Rows / columns | `nrow(df)` / `ncol(df)` | `len(df)` / `df.shape[1]` |\n", "| Column names | `names(df)` | `df.columns` |\n", "| Structure | `str(df)` | `df.info()` |\n", "| First / last rows | `head(df)` / `tail(df)` | `df.head()` / `df.tail()` |\n", "| Summary | `summary(df)` | `df.describe()` |\n", "| Column types | `sapply(df, class)` | `df.dtypes` |\n", "| Frequency table | `table(x)` | `s.value_counts()` |\n", "| Two-way table | `table(x, y)` | `pd.crosstab(x, y)` |\n", "| Distinct values | `unique(x)` | `s.unique()` |\n", "| Count distinct | `length(unique(x))` | `s.nunique()` |\n", "| Any missing? | `any(is.na(df))` | `df.isna().any().any()` |\n", "| Missing per column | `colSums(is.na(df))` | `df.isna().sum()` |\n", "| Complete rows | `complete.cases(df)` | `df.notna().all(axis=\"columns\")` |\n", "\n", "**Mind this.** `dim`, `names` and `shape` are **attributes** in pandas --- no parentheses.\n", "`info()` and `describe()` are **methods** --- parentheses required. Forgetting them on a\n", "method prints `` with no error at all." ] }, { "cell_type": "code", "execution_count": null, "id": "c006", "metadata": {}, "outputs": [], "source": [ "print(df.shape) # dim(df)\n", "print(df.dtypes.to_dict()) # sapply(df, class)\n", "print(df[\"grp\"].value_counts().to_dict()) # table(grp)\n", "print(df.isna().sum().to_dict()) # colSums(is.na(df))\n", "print(df.notna().all(axis=\"columns\").tolist()) # complete.cases(df)" ] }, { "cell_type": "markdown", "id": "c007", "metadata": {}, "source": [ "# 3. Selecting columns\n", "\n", "| Task | R (base) | R (dplyr) | pandas |\n", "|:---|:---|:---|:---|\n", "| One column, as a vector | `df$score` | `pull(df, score)` | `df[\"score\"]` |\n", "| One column, as a table | `df[\"score\"]` | `select(df, score)` | `df[[\"score\"]]` |\n", "| Several columns | `df[c(\"id\", \"n\")]` | `select(df, id, n)` | `df[[\"id\", \"n\"]]` |\n", "| Drop a column | `df$score <- NULL` | `select(df, -score)` | `df.drop(columns=\"score\")` |\n", "| By position | `df[, 2]` | | `df.iloc[:, 1]` |\n", "| Rename | `names(df)[1] <- \"ID\"` | `rename(df, ID = id)` | `df.rename(columns={\"id\": \"ID\"})` |\n", "| Reorder | `df[c(\"n\", \"id\")]` | `relocate(df, n)` | `df[[\"n\", \"id\"]]` |\n", "\n", "**Mind this.** `df[\"score\"]` returns a **Series**; `df[[\"score\"]]` returns a one-column\n", "**DataFrame**. Same distinction as `pull()` vs `select()`.\n", "\n", "Avoid attribute access (`df.score`). It fails on names with spaces, cannot create a new\n", "column, and silently loses to built-ins --- `df.size` is the element count, not a column\n", "called `size`." ] }, { "cell_type": "markdown", "id": "c008", "metadata": {}, "source": [ "# 4. Selecting rows and filtering\n", "\n", "| Task | R (base) | R (dplyr) | pandas |\n", "|:---|:---|:---|:---|\n", "| By position | `df[1:3, ]` | `slice(df, 1:3)` | `df.iloc[0:3]` |\n", "| By row label | `df[\"r1\", ]` | | `df.loc[\"r1\"]` |\n", "| Filter on a condition | `df[df$score > 85, ]` | `filter(df, score > 85)` | `df[df[\"score\"] > 85]` |\n", "| Two conditions | `df[df$n > 1 & df$grp == \"x\", ]` | `filter(df, n > 1, grp == \"x\")` | `df[(df[\"n\"] > 1) & (df[\"grp\"] == \"x\")]` |\n", "| Membership | `df[df$grp %in% c(\"x\"), ]` | `filter(df, grp %in% c(\"x\"))` | `df[df[\"grp\"].isin([\"x\"])]` |\n", "| Negate | `df[!cond, ]` | `filter(df, !cond)` | `df[~cond]` |\n", "| Drop missing | `na.omit(df)` | `drop_na(df)` | `df.dropna()` |\n", "| Top n | `head(df[order(-df$n), ], 3)` | `slice_max(df, n, n = 3)` | `df.nlargest(3, \"n\")` |\n", "| Random rows | `df[sample(nrow(df), 3), ]` | `slice_sample(df, n = 3)` | `df.sample(3)` |\n", "| Query string | | | `df.query(\"n > 1 and grp == 'x'\")` |\n", "\n", "**Mind this --- three separate traps.**\n", "\n", "1. Use `&`, `|`, `~` --- **never** Python's `and` / `or` / `not`, which cannot work\n", " element-wise.\n", "2. **Parenthesize every condition.** `&` binds more tightly than `>`, so\n", " `df[\"n\"] > 1 & df[\"grp\"] == \"x\"` evaluates in the wrong order and raises.\n", "3. `df.loc[0:2]` **includes** the endpoint (3 rows); `df.iloc[0:2]` **excludes** it\n", " (2 rows). Label slices are the one place Python's half-open rule does not hold." ] }, { "cell_type": "code", "execution_count": null, "id": "c009", "metadata": {}, "outputs": [], "source": [ "print(df[df[\"score\"] > 85][\"id\"].tolist()) # filter\n", "print(df[(df[\"n\"] > 1) & (df[\"grp\"] == \"x\")][\"id\"].tolist()) # two conditions\n", "print(df.query(\"n > 1 and grp == 'x'\")[\"id\"].tolist()) # same, as a string\n", "print(len(df.loc[0:2]), len(df.iloc[0:2])) # 3 and 2 -- endpoint rule differs" ] }, { "cell_type": "markdown", "id": "c010", "metadata": {}, "source": [ "# 5. `which()`, `match()` and set operations\n", "\n", "| R | pandas | Returns |\n", "|:---|:---|:---|\n", "| `which(x > 4)` | `np.flatnonzero(s > 4)` | integer positions (**0-based**) |\n", "| | `s.index[s > 4]` | index labels |\n", "| `x[which(x > 4)]` | `s[s > 4]` | the values --- preferred |\n", "| `length(which(cond))` | `cond.sum()` | a count |\n", "| `which.max(x)` | `s.idxmax()` | index **label** |\n", "| | `s.argmax()` | integer **position** |\n", "| `which(x == max(x))` | `s.index[s == s.max()]` | **all** ties |\n", "| `which(is.na(x))` | `s.index[s.isna()]` | labels of the missing |\n", "| `which(duplicated(x))` | `np.flatnonzero(s.duplicated())` | positions |\n", "| `which(m > 5, arr.ind = TRUE)` | `np.argwhere((m > 5).to_numpy())` | row/col positions |\n", "| | `m.stack()[(m > 5).stack()]` | labels **and** values |\n", "| `match(x, table)` | `pd.Index(table).get_indexer(x)` | positions, **`-1`** if absent |\n", "| *(table has duplicates)* | `pd.Index(table).get_indexer_for(x)` | every match |\n", "| `x %in% table` | `s.isin(table)` | Boolean mask |\n", "| `intersect(x, y)` | `pd.Index(x).intersection(y)` | in both |\n", "| `setdiff(x, y)` | `pd.Index(x).difference(y)` | in `x`, not `y` |\n", "| `union(x, y)` | `pd.Index(x).union(y)` | either |\n", "\n", "**Mind this.** Two differences bite everyone:\n", "\n", "- **Positions are 0-based**, so `which()` minus one.\n", "- **\"Not found\" is `-1`, not `NA`** --- and `-1` is a *legal* Python position meaning\n", " \"last element\". Never feed `get_indexer` output to `iloc` without filtering on `>= 0`.\n", "\n", "`get_indexer` also requires the lookup table to be **unique**, and the `Index` set\n", "operations **sort and deduplicate** their result." ] }, { "cell_type": "code", "execution_count": null, "id": "c011", "metadata": {}, "outputs": [], "source": [ "s = df[\"score\"]\n", "print(np.flatnonzero(s > 85)) # which(score > 85)\n", "print(s.index[s > 85].tolist()) # the labels\n", "print(s.idxmax(), s.argmax()) # which.max: label vs position\n", "\n", "ids = pd.Index(df[\"id\"])\n", "print(ids.get_indexer([\"c\", \"z\"])) # match(c(\"c\",\"z\"), id) -> 2, -1 (R: 3, NA)\n", "print(ids.difference([\"a\", \"b\"]).tolist()) # setdiff" ] }, { "cell_type": "markdown", "id": "c012", "metadata": {}, "source": [ "# 6. Sorting and ranking\n", "\n", "| Task | R (base) | R (dplyr) | pandas |\n", "|:---|:---|:---|:---|\n", "| Sort a vector | `sort(x)` | | `s.sort_values()` |\n", "| Sort rows | `df[order(df$n), ]` | `arrange(df, n)` | `df.sort_values(\"n\")` |\n", "| Descending | `df[order(-df$n), ]` | `arrange(df, desc(n))` | `df.sort_values(\"n\", ascending=False)` |\n", "| Several keys | `df[order(df$grp, -df$n), ]` | `arrange(df, grp, desc(n))` | `df.sort_values([\"grp\", \"n\"], ascending=[True, False])` |\n", "| Sort by row label | | | `df.sort_index()` |\n", "| Ordering vector | `order(x)` | | `s.argsort()` |\n", "| Rank | `rank(x)` | `min_rank(x)` | `s.rank()` |\n", "| Reverse | `rev(x)` | | `s[::-1]` |\n", "\n", "Tie-breaking in `rank(method=)`: `\"average\"` (default), `\"min\"`, `\"max\"`, `\"first\"`,\n", "`\"dense\"`.\n", "\n", "**Mind this.** Missing values sort **last** by default in both languages, but pandas\n", "lets you say `na_position=\"first\"`. And `sort_values` returns a **new** frame --- it\n", "never sorts in place unless you pass `inplace=True`, which is discouraged." ] }, { "cell_type": "markdown", "id": "c013", "metadata": {}, "source": [ "# 7. Creating and modifying columns\n", "\n", "| Task | R (base) | R (dplyr) | pandas |\n", "|:---|:---|:---|:---|\n", "| New column | `df$z <- df$n * 2` | `mutate(df, z = n * 2)` | `df[\"z\"] = df[\"n\"] * 2` |\n", "| Several at once | | `mutate(df, a = ..., b = ...)` | `df.assign(a=lambda d: ..., b=lambda d: ...)` |\n", "| Conditional | `ifelse(x > 2, \"big\", \"small\")` | `if_else(...)` | `np.where(x > 2, \"big\", \"small\")` |\n", "| Multi-way | `cut(x, breaks)` | `case_when(...)` | `np.select([c1, c2], [v1, v2], default=...)` |\n", "| Recode from a table | `lookup$v[match(df$k, lookup$k)]` | `left_join(df, lookup)` | `df[\"k\"].map(lookup.set_index(\"k\")[\"v\"])` |\n", "| Replace values | `x[x == -99] <- NA` | `na_if(x, -99)` | `s.replace(-99, np.nan)` |\n", "| Bin, equal width | `cut(x, 4)` | | `pd.cut(x, 4)` |\n", "| Bin, equal count | `cut(x, quantile(x))` | `ntile(x, 4)` | `pd.qcut(x, 4)` |\n", "| Cumulative | `cumsum(x)` | | `s.cumsum()` |\n", "| Lag / lead | `dplyr::lag(x)` | `lag(x)` / `lead(x)` | `s.shift(1)` / `s.shift(-1)` |\n", "| Row-wise difference | `diff(x)` | | `s.diff()` |\n", "\n", "**Mind this.** Inside `assign`, **pandas has no data masking** --- you cannot write bare\n", "column names the way `mutate` lets you. Use `lambda d: d[\"col\"]`, which also keeps the\n", "expression correct when an earlier step in the chain changed the rows.\n", "\n", "```python\n", "df.assign(z = lambda d: d[\"n\"] * 2) # right\n", "df.assign(z = n * 2) # NameError: name 'n' is not defined\n", "```\n", "\n", "`map` returns `NaN` for any key missing from the lookup --- **silently**. Count the\n", "`NaN`s afterwards, every time." ] }, { "cell_type": "code", "execution_count": null, "id": "c014", "metadata": {}, "outputs": [], "source": [ "out = df.assign(\n", " z = lambda d: d[\"n\"] * 2, # mutate(z = n * 2)\n", " sizec = lambda d: np.where(d[\"n\"] > 2, \"big\", \"small\"), # ifelse\n", " lagn = lambda d: d[\"n\"].shift(1), # lag(n)\n", ")\n", "out[[\"id\", \"n\", \"z\", \"sizec\", \"lagn\"]]" ] }, { "cell_type": "code", "execution_count": null, "id": "c015", "metadata": {}, "outputs": [], "source": [ "# recode from a lookup table: lookup$v[match(df$k, lookup$k)]\n", "lookup = pd.DataFrame({\"grp\": [\"x\", \"y\"], \"label\": [\"Control\", \"Treated\"]})\n", "recoded = df[\"grp\"].map(lookup.set_index(\"grp\")[\"label\"])\n", "print(recoded.tolist())\n", "print(\"NaNs introduced:\", recoded.isna().sum()) # ALWAYS check" ] }, { "cell_type": "markdown", "id": "c016", "metadata": {}, "source": [ "# 8. Missing data\n", "\n", "| Task | R | pandas |\n", "|:---|:---|:---|\n", "| Test for missing | `is.na(x)` | `s.isna()` |\n", "| Test for present | `!is.na(x)` | `s.notna()` |\n", "| Count missing | `sum(is.na(x))` | `s.isna().sum()` |\n", "| Drop missing rows | `na.omit(df)` | `df.dropna()` |\n", "| Drop if all missing | | `df.dropna(how=\"all\")` |\n", "| Keep rows with >= k present | | `df.dropna(thresh=k)` |\n", "| Fill with a constant | `x[is.na(x)] <- 0` | `s.fillna(0)` |\n", "| Fill per column | | `df.fillna({\"a\": 0, \"b\": -1})` |\n", "| Carry forward | `zoo::na.locf(x)` | `s.ffill()` |\n", "| Carry backward | | `s.bfill()` |\n", "| Mean imputation | `x[is.na(x)] <- mean(x, na.rm = TRUE)` | `s.fillna(s.mean())` |\n", "| Ignore NA in a summary | `mean(x, na.rm = TRUE)` | `s.mean()` *(default)* |\n", "| Include NA in a summary | `mean(x)` | `s.mean(skipna=False)` |\n", "\n", "**Mind this --- the defaults are opposite.** R's `mean(x)` returns `NA` if anything is\n", "missing and you must ask for `na.rm = TRUE`. pandas **skips missing by default**; you\n", "must ask for `skipna=False` to propagate. That one reversal silently changes results\n", "when translating code in either direction.\n", "\n", "`thresh=k` counts the values that are **present**, not the ones that are missing." ] }, { "cell_type": "code", "execution_count": null, "id": "c017", "metadata": {}, "outputs": [], "source": [ "print(\"count missing :\", df[\"score\"].isna().sum())\n", "print(\"pandas mean :\", round(df[\"score\"].mean(), 2), \" (R: mean(score, na.rm=TRUE))\")\n", "print(\"skipna=False :\", df[\"score\"].mean(skipna=False), \" (R: mean(score))\")\n", "print(\"ffill :\", df[\"score\"].ffill().tolist())\n", "print(\"dropna rows :\", len(df.dropna()), \"of\", len(df))" ] }, { "cell_type": "markdown", "id": "c018", "metadata": {}, "source": [ "# 9. Summary statistics\n", "\n", "| Task | R | pandas |\n", "|:---|:---|:---|\n", "| Mean / median | `mean(x)` / `median(x)` | `s.mean()` / `s.median()` |\n", "| SD / variance | `sd(x)` / `var(x)` | `s.std()` / `s.var()` |\n", "| Min / max | `min(x)` / `max(x)` | `s.min()` / `s.max()` |\n", "| Range | `range(x)` | `(s.min(), s.max())` |\n", "| Sum / product | `sum(x)` / `prod(x)` | `s.sum()` / `s.prod()` |\n", "| Quantiles | `quantile(x, .25)` | `s.quantile(.25)` |\n", "| Correlation | `cor(x, y)` | `s.corr(t)` |\n", "| Correlation matrix | `cor(df)` | `df.corr(numeric_only=True)` |\n", "| Covariance | `cov(x, y)` | `s.cov(t)` |\n", "| Column means | `colMeans(df)` | `df.mean(numeric_only=True)` |\n", "| Row means | `rowMeans(df)` | `df.mean(axis=\"columns\")` |\n", "| Count non-missing | `sum(!is.na(x))` | `s.count()` |\n", "| Length | `length(x)` | `len(s)` or `s.size` |\n", "| All / any | `all(cond)` / `any(cond)` | `cond.all()` / `cond.any()` |\n", "\n", "**Mind this.** `s.std()` uses the **sample** standard deviation (`ddof=1`), matching R's\n", "`sd()`. NumPy's `np.std()` uses `ddof=0` by default and will not match --- a classic\n", "source of \"why are my numbers slightly off?\"\n", "\n", "Also: `s.count()` is the number of **non-missing** values (R's `sum(!is.na(x))`), while\n", "`len(s)` and `s.size` count every row." ] }, { "cell_type": "code", "execution_count": null, "id": "c019", "metadata": {}, "outputs": [], "source": [ "x = df[\"score\"]\n", "print(\"pandas s.std():\", round(x.std(), 4), \" <- ddof=1, matches R's sd()\")\n", "print(\"numpy np.std() :\", round(np.std(x.dropna()), 4), \" <- ddof=0, does NOT match\")\n", "print(\"count vs len :\", x.count(), len(x))" ] }, { "cell_type": "markdown", "id": "c020", "metadata": {}, "source": [ "# 10. Split--apply--combine\n", "\n", "| Task | R (base) | R (dplyr) | pandas |\n", "|:---|:---|:---|:---|\n", "| Group means | `tapply(x, g, mean)` | `group_by(g) %>% summarize(m = mean(x))` | `df.groupby(\"g\")[\"x\"].mean()` |\n", "| Group sizes | `table(g)` | `count(df, g)` | `df.groupby(\"g\").size()` |\n", "| Several statistics | `aggregate(...)` | `summarize(m = mean(x), s = sd(x))` | `df.groupby(\"g\")[\"x\"].agg([\"mean\", \"std\"])` |\n", "| Named outputs | | `summarize(avg = mean(x))` | `df.groupby(\"g\").agg(avg=(\"x\", \"mean\"))` |\n", "| Two keys | | `group_by(g1, g2)` | `df.groupby([\"g1\", \"g2\"])` |\n", "| Keys as columns | | *(default)* | `df.groupby(\"g\", as_index=False)` |\n", "| Broadcast back | `ave(x, g)` | `group_by(g) %>% mutate(m = mean(x))` | `df.groupby(\"g\")[\"x\"].transform(\"mean\")` |\n", "| Filter groups | | `group_by(g) %>% filter(n() > 2)` | `df.groupby(\"g\").filter(lambda d: len(d) > 2)` |\n", "| Arbitrary function | `by(df, g, f)` | `group_modify(f)` | `df.groupby(\"g\").apply(f)` |\n", "| Split into pieces | `split(df, g)` | `group_split(df, g)` | `dict(list(df.groupby(\"g\")))` |\n", "\n", "**`agg` vs `transform`** is the distinction to internalize:\n", "\n", "- **`agg`** returns **one row per group** --- it is `summarize()`.\n", "- **`transform`** returns **one row per original row** --- it is `mutate()` after a\n", " `group_by()`, and it is what you need for group-centering.\n", "\n", "**Mind this.** Always report the group size beside a group statistic. Put\n", "`n=(\"col\", \"size\")` first in every `agg` call: a mean over 3 observations is not the\n", "same kind of number as a mean over 300." ] }, { "cell_type": "code", "execution_count": null, "id": "c021", "metadata": {}, "outputs": [], "source": [ "# summarize: one row per group\n", "print(df.groupby(\"grp\").agg(n=(\"id\", \"size\"),\n", " avg=(\"score\", \"mean\")).round(2))\n", "print()\n", "# mutate-after-group_by: one row per original row\n", "print(df.assign(grp_mean=lambda d: d.groupby(\"grp\")[\"score\"].transform(\"mean\"))\n", " [[\"id\", \"grp\", \"score\", \"grp_mean\"]].round(2))" ] }, { "cell_type": "markdown", "id": "c022", "metadata": {}, "source": [ "# 11. Joining tables\n", "\n", "| Join | R (base) | R (dplyr) | pandas |\n", "|:---|:---|:---|:---|\n", "| Inner | `merge(a, b)` | `inner_join(a, b, by = \"k\")` | `a.merge(b, on=\"k\")` |\n", "| Left | `merge(a, b, all.x = TRUE)` | `left_join(a, b, by = \"k\")` | `a.merge(b, on=\"k\", how=\"left\")` |\n", "| Right | `merge(a, b, all.y = TRUE)` | `right_join(a, b, by = \"k\")` | `a.merge(b, on=\"k\", how=\"right\")` |\n", "| Full | `merge(a, b, all = TRUE)` | `full_join(a, b, by = \"k\")` | `a.merge(b, on=\"k\", how=\"outer\")` |\n", "| Different key names | `merge(a, b, by.x=, by.y=)` | `join(a, b, by = c(\"k1\" = \"k2\"))` | `a.merge(b, left_on=\"k1\", right_on=\"k2\")` |\n", "| Several keys | `merge(a, b, by = c(\"k1\",\"k2\"))` | `by = c(\"k1\",\"k2\")` | `on=[\"k1\", \"k2\"]` |\n", "| On the row index | | | `a.join(b)` |\n", "| Keep matches only | | `semi_join(a, b)` | `a[a[\"k\"].isin(b[\"k\"])]` |\n", "| Keep non-matches | | `anti_join(a, b)` | `a[~a[\"k\"].isin(b[\"k\"])]` |\n", "| Stack rows | `rbind(a, b)` | `bind_rows(a, b)` | `pd.concat([a, b])` |\n", "| Stack columns | `cbind(a, b)` | `bind_cols(a, b)` | `pd.concat([a, b], axis=\"columns\")` |\n", "\n", "**Mind this --- the most expensive mistake in this document.** If the right table has\n", "**duplicate keys**, a merge silently **multiplies rows**, and `how=\"left\"` does *not*\n", "protect you: `how=` controls which **keys** survive, not the row count.\n", "\n", "```python\n", "before = len(a)\n", "out = a.merge(b, on=\"k\", how=\"left\")\n", "assert len(out) == before # cheapest insurance in pandas\n", "b[\"k\"].is_unique # or check first\n", "a.merge(b, on=\"k\", how=\"left\", validate=\"m:1\") # or let pandas refuse\n", "```\n", "\n", "Overlapping non-key column names get `_x` / `_y` suffixes rather than an error. Control\n", "them with `suffixes=(\"_a\", \"_b\")`." ] }, { "cell_type": "code", "execution_count": null, "id": "c023", "metadata": {}, "outputs": [], "source": [ "b = pd.DataFrame({\"grp\": [\"x\", \"y\"], \"boss\": [\"Ana\", \"Ben\"]})\n", "print(\"rows:\", len(df), \"->\", len(df.merge(b, on=\"grp\", how=\"left\")))\n", "\n", "dup = pd.DataFrame({\"grp\": [\"x\", \"x\", \"y\"], \"boss\": [\"Ana\", \"Alt\", \"Ben\"]})\n", "print(\"with a duplicated key:\", len(df), \"->\", len(df.merge(dup, on=\"grp\", how=\"left\")))\n", "try:\n", " df.merge(dup, on=\"grp\", how=\"left\", validate=\"m:1\")\n", "except Exception as e:\n", " print(type(e).__name__, \"->\", e)" ] }, { "cell_type": "markdown", "id": "c024", "metadata": {}, "source": [ "# 12. Reshaping\n", "\n", "| Task | R (tidyr) | R (base / reshape2) | pandas |\n", "|:---|:---|:---|:---|\n", "| Wide to long | `pivot_longer(df, cols)` | `melt(df, id.vars)` | `pd.melt(df, id_vars=...)` |\n", "| Long to wide | `pivot_wider(df, names_from, values_from)` | `dcast(df, ...)` | `df.pivot(index=, columns=, values=)` |\n", "| Long to wide, aggregating | | `dcast(df, ..., fun.aggregate = mean)` | `df.pivot_table(index=, columns=, aggfunc=)` |\n", "| Columns into rows | | | `df.stack()` |\n", "| Index level into columns | | | `df.unstack()` |\n", "| Transpose | `t(df)` | | `df.T` |\n", "| Column to row index | | `rownames(df) <- df$id` | `df.set_index(\"id\")` |\n", "| Row index to column | | | `df.reset_index()` |\n", "| Cross-tabulation | `table(a, b)` | `xtabs(~ a + b)` | `pd.crosstab(a, b)` |\n", "\n", "**Mind this.** `pivot` fails if the index/column pair is not unique --- that is a feature,\n", "since it means you have duplicates you did not know about. `pivot_table` aggregates them\n", "instead (mean by default), which is convenient but can hide the same problem." ] }, { "cell_type": "code", "execution_count": null, "id": "c025", "metadata": {}, "outputs": [], "source": [ "long = pd.melt(df, id_vars=[\"id\", \"grp\"], value_vars=[\"score\", \"n\"])\n", "print(long.head(4))\n", "print()\n", "print(long.pivot(index=\"id\", columns=\"variable\", values=\"value\"))" ] }, { "cell_type": "markdown", "id": "c026", "metadata": {}, "source": [ "# 13. Strings\n", "\n", "pandas puts vectorized string methods behind the **`.str` accessor**. They skip missing\n", "values instead of raising.\n", "\n", "| Task | R (base) | R (stringr) | pandas |\n", "|:---|:---|:---|:---|\n", "| Length | `nchar(x)` | `str_length(x)` | `s.str.len()` |\n", "| Upper / lower | `toupper(x)` | `str_to_upper(x)` | `s.str.upper()` |\n", "| Title case | | `str_to_title(x)` | `s.str.title()` |\n", "| Trim whitespace | `trimws(x)` | `str_trim(x)` | `s.str.strip()` |\n", "| Concatenate | `paste0(x, y)` | `str_c(x, y)` | `x + y` |\n", "| Collapse to one string | `paste(x, collapse=\",\")` | `str_c(x, collapse=\",\")` | `\",\".join(s)` |\n", "| Substring | `substr(x, 1, 3)` | `str_sub(x, 1, 3)` | `s.str[:3]` |\n", "| Contains | `grepl(\"a\", x)` | `str_detect(x, \"a\")` | `s.str.contains(\"a\")` |\n", "| Replace first | `sub(\"a\", \"b\", x)` | `str_replace(x, \"a\", \"b\")` | `s.str.replace(\"a\", \"b\", n=1)` |\n", "| Replace all | `gsub(\"a\", \"b\", x)` | `str_replace_all(...)` | `s.str.replace(\"a\", \"b\")` |\n", "| Split | `strsplit(x, \",\")` | `str_split(x, \",\")` | `s.str.split(\",\")` |\n", "| Which match | `grep(\"a\", x)` | `str_which(x, \"a\")` | `np.flatnonzero(s.str.contains(\"a\"))` |\n", "| Extract groups | `regmatches(...)` | `str_match(x, re)` | `s.str.extract(r\"(\\w+)@(\\w+)\")` |\n", "| Pad | `formatC(x, width=5)` | `str_pad(x, 5)` | `s.str.pad(5)` |\n", "| Starts / ends with | `startsWith(x, \"a\")` | `str_starts(x, \"a\")` | `s.str.startswith(\"a\")` |\n", "\n", "**Mind this.** `str.replace` replaces **every** occurrence by default --- it is `gsub`,\n", "not `sub`. Pass `n=1` for `sub` behaviour. And since pandas 2.0 it treats the pattern as\n", "a **literal** unless you pass `regex=True`." ] }, { "cell_type": "markdown", "id": "c027", "metadata": {}, "source": [ "# 14. Factors and categoricals\n", "\n", "| Task | R | pandas |\n", "|:---|:---|:---|\n", "| Create | `factor(x)` | `s.astype(\"category\")` |\n", "| With an order | `factor(x, levels=, ordered=TRUE)` | `pd.Categorical(s, categories=, ordered=True)` |\n", "| Levels | `levels(f)` | `s.cat.categories` |\n", "| Integer codes | `as.integer(f)` | `s.cat.codes` |\n", "| Rename levels | `levels(f) <- c(...)` | `s.cat.rename_categories([...])` |\n", "| Add a level | | `s.cat.add_categories([...])` |\n", "| Drop unused | `droplevels(f)` | `s.cat.remove_unused_categories()` |\n", "| Back to text | `as.character(f)` | `s.astype(str)` |\n", "| Counts incl. empty levels | `table(f)` | `s.value_counts()` *(keeps empty categories)* |\n", "| Dummy variables | `model.matrix(~ f)` | `pd.get_dummies(s, drop_first=True)` |\n", "\n", "**Mind this.** R's `as.integer(factor)` is **1-based**; pandas `cat.codes` is **0-based**\n", "and uses `-1` for missing. And `groupby` on a categorical keeps **unused categories** by\n", "default --- pass `observed=True` to drop them, or you get rows of `NaN` for groups that\n", "do not exist." ] }, { "cell_type": "markdown", "id": "c028", "metadata": {}, "source": [ "# 15. The apply family\n", "\n", "| R | pandas / NumPy | Note |\n", "|:---|:---|:---|\n", "| `sapply(x, f)` / `vapply` | `s.map(f)` | element-wise over a Series |\n", "| `lapply(x, f)` | `[f(i) for i in x]` | returns a list |\n", "| `apply(m, 2, f)` | `df.apply(f)` | once per **column** (the default) |\n", "| `apply(m, 1, f)` | `df.apply(f, axis=\"columns\")` | once per **row** |\n", "| `mapply(f, x, y)` | `np.vectorize(f)(x, y)` | or just write it vectorized |\n", "| `tapply(x, g, f)` | `df.groupby(g)[\"x\"].agg(f)` | split--apply--combine |\n", "| `Map(f, x, y)` | `zip(x, y)` in a comprehension | |\n", "| `Reduce(f, x)` | `functools.reduce(f, x)` | |\n", "| `do.call(rbind, lst)` | `pd.concat(lst)` | |\n", "| element-wise over a frame | `df.map(f)` | every **cell** |\n", "\n", "**Mind this --- the axis numbering is the mirror image of R's.**\n", "\n", "| What you want | R | pandas |\n", "|:---|:---|:---|\n", "| one value per **column** | `apply(m, 2, f)` | `df.apply(f)` or `axis=0` |\n", "| one value per **row** | `apply(m, 1, f)` | `df.apply(f, axis=1)` |\n", "\n", "Read `axis=` as **\"the axis that disappears.\"** `axis=0` collapses the rows, so you get\n", "one number per column.\n", "\n", "Prefer vectorized operations over `apply` whenever one exists: `df[\"x\"] * 2` is orders of\n", "magnitude faster than `df[\"x\"].apply(lambda v: v * 2)`, and shorter." ] }, { "cell_type": "code", "execution_count": null, "id": "c029", "metadata": {}, "outputs": [], "source": [ "num = df[[\"score\", \"n\"]]\n", "print(num.apply(lambda c: c.max() - c.min())) # apply(m, 2, f)\n", "print()\n", "print(num.apply(lambda r: r[\"n\"] * 2, axis=\"columns\").tolist()) # apply(m, 1, f)" ] }, { "cell_type": "markdown", "id": "c030", "metadata": {}, "source": [ "# 16. Dates\n", "\n", "| Task | R (base) | R (lubridate) | pandas |\n", "|:---|:---|:---|:---|\n", "| Parse | `as.Date(x)` | `ymd(x)` | `pd.to_datetime(x)` |\n", "| Today | `Sys.Date()` | `today()` | `pd.Timestamp.today()` |\n", "| Year / month / day | `format(d, \"%Y\")` | `year(d)` | `s.dt.year` |\n", "| Day of week | `weekdays(d)` | `wday(d, label=TRUE)` | `s.dt.day_name()` |\n", "| Format out | `format(d, \"%Y-%m\")` | | `s.dt.strftime(\"%Y-%m\")` |\n", "| Difference | `d2 - d1` | | `s2 - s1` *(a `Timedelta`)* |\n", "| Add days | `d + 7` | `d + days(7)` | `s + pd.Timedelta(days=7)` |\n", "| Sequence | `seq(d1, d2, \"day\")` | | `pd.date_range(d1, d2, freq=\"D\")` |\n", "| Round down | | `floor_date(d, \"month\")` | `s.dt.to_period(\"M\")` |\n", "\n", "Like `.str`, the **`.dt` accessor** is where all the date methods live --- and it only\n", "works once the column is actually a datetime dtype, which `pd.to_datetime` gives you." ] }, { "cell_type": "markdown", "id": "c031", "metadata": {}, "source": [ "# 17. Reading and writing\n", "\n", "| Format | R | pandas |\n", "|:---|:---|:---|\n", "| CSV | `read.csv(f)` / `readr::read_csv(f)` | `pd.read_csv(f)` |\n", "| Tab-separated | `read.delim(f)` | `pd.read_csv(f, sep=\"\\t\")` |\n", "| Excel | `readxl::read_excel(f)` | `pd.read_excel(f, sheet_name=0)` |\n", "| SAS | `haven::read_sas(f)` | `pd.read_sas(f)` |\n", "| Stata | `haven::read_dta(f)` | `pd.read_stata(f)` |\n", "| Fixed width | `read.fwf(f)` | `pd.read_fwf(f)` |\n", "| JSON | `jsonlite::fromJSON(f)` | `pd.read_json(f)` |\n", "| From a database | `DBI::dbGetQuery(con, q)` | `pd.read_sql(q, con)` |\n", "| Write CSV | `write.csv(df, f, row.names=FALSE)` | `df.to_csv(f, index=False)` |\n", "| Save native | `saveRDS(df, f)` | `df.to_parquet(f)` or `to_pickle(f)` |\n", "\n", "Useful `read_csv` arguments: `sep`, `header`, `names`, `index_col`, `na_values`,\n", "`skiprows`, `nrows`, `dtype`, `parse_dates`.\n", "\n", "**Mind this.** `df.to_csv(f)` writes the row index as an unnamed first column by default.\n", "Pass `index=False` --- otherwise every round trip through CSV grows a junk\n", "`Unnamed: 0` column." ] }, { "cell_type": "markdown", "id": "c032", "metadata": {}, "source": [ "# 18. False friends\n", "\n", "Same name, different behaviour. These are the ones that cost hours.\n", "\n", "| Looks like | In R | In pandas |\n", "|:---|:---|:---|\n", "| `df[1:3]` | rows 1 to 3 | **columns** sliced, or rows for a slice --- ambiguous; use `loc`/`iloc` |\n", "| `x[0]` | **error** (1-based) | the **first** element (0-based) |\n", "| `a:b` slice | **includes** `b` | **excludes** `b` --- except in `.loc`, which includes it |\n", "| `mean(x)` | `NA` if anything is missing | skips missing by default |\n", "| `sd(x)` | `ddof = 1` | `s.std()` is `ddof=1`, but `np.std()` is `ddof=0` |\n", "| `sub` vs `gsub` | distinct functions | `str.replace` is `gsub`; pass `n=1` for `sub` |\n", "| && / || | scalar and/or | no element-wise meaning --- use & / | |\n", "| `match()` not found | `NA` | `-1`, which is a **valid** position |\n", "| `which.max` ties | first only | `idxmax` first only --- same, but returns a **label** |\n", "| `merge(all.x=TRUE)` | keeps left keys | `how=\"left\"` keeps left keys but **can add rows** |\n", "| `df$col <- v` | always works | `df[\"col\"] = v` works; `df.col = v` silently makes an attribute |\n", "| `apply(m, 1, f)` | per row | `axis=1` per row --- **the numbering is reversed** |\n", "| factor codes | 1-based | `cat.codes` 0-based, `-1` for missing |\n", "| `rev(x)` | reverses | `s[::-1]` reverses **values and labels** together |" ] }, { "cell_type": "markdown", "id": "c033", "metadata": {}, "source": [ "# 19. The five silent failures\n", "\n", "None of these raises an error. Your code runs; your numbers are wrong.\n", "\n", "1. **Alignment.** Operations match on **labels**, not position. Two Series with different\n", " indexes give you `NaN`, not an error.\n", " *Guard:* check `.isna().sum()` after arithmetic between two Series.\n", "\n", "2. **Chained assignment.** `df[mask][\"col\"] = x` modifies a temporary copy and may do\n", " nothing at all.\n", " *Guard:* write one `.loc[rows, cols] = x` call, always.\n", "\n", "3. **`and` / `or`.** Python's keywords cannot work element-wise.\n", " *Guard:* `&`, `|`, `~`, and parenthesize every condition.\n", "\n", "4. **Silent `NaN` from `map`.** A key missing from the dictionary becomes missing data\n", " with no warning, and `value_counts()` then hides it.\n", " *Guard:* `recoded.isna().sum()` after every `map`.\n", "\n", "5. **Merges that grow.** Duplicate keys multiply rows, and `how=\"left\"` does not prevent\n", " it.\n", " *Guard:* `assert len(out) == len(left)`, or pass `validate=\"m:1\"`." ] }, { "cell_type": "markdown", "id": "c034", "metadata": {}, "source": [ "# Further reading\n", "\n", "- **pandas, Comparison with R / R libraries** ---\n", " \n", "- **McKinney, W.** *Python for Data Analysis*, 3rd ed. --- \n", "- **VanderPlas, J.** *Python Data Science Handbook*, Ch. 3 ---\n", " \n", "- **pandas user guide** --- \n", "- Course lectures: `PandasEssentials`, `TipsAnalysis`, `PandasDataManipulation`" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.3" }, "title": "R to pandas" }, "nbformat": 4, "nbformat_minor": 5 }