# Pandas: DataFrames, Series and the way over from SQL

<!---
https://pbpython.com/categorical-encoding.html
--->

## DataFrame and Series

The **DataFrame** is the data structure everything in Pandas revolves around: a table with named columns, each column with its own data type. If you know R, you know it as `data.frame`; if you have only used Numpy so far, think of it as a record array (`np.genfromtxt(..., names=True)` in the chapter [Exploratory analysis](eda-examples.ipynb) produces one of those), only more convenient. The nearest thing to it is an Excel sheet in Python.

Unlike a Numpy array, a DataFrame is always two-dimensional – there is no such thing as a DataFrame with three or four axes. That is not a restriction but a commitment to exactly the shape data usually arrive in: rows are observations, columns are features.

```python
import numpy as np
import pandas as pd

columns = {
    "species": ["setosa", "setosa", "versicolor"],
    "sepal_width": [3.5, 3.0, 3.2],
}
df = pd.DataFrame(columns)
print(df)
```

There are two ways to get at a column, and they do the same thing: with square brackets, as for a dictionary, or as an attribute. The brackets always work; the attribute notation only works if the column name is a valid Python identifier and does not happen to shadow a method – so when in doubt, brackets.

```python
print(df["sepal_width"])
print(df.sepal_width)  # the same thing

print(df.dtypes)  # here you can see that Numpy is underneath
print(df["sepal_width"].to_numpy())
```

A single column is not a DataFrame but the second of the two data structures Pandas has: a **Series**, that is, a one-dimensional array with an index. Most methods exist for both, some only for one of the two – the error message `'Series' object has no attribute ...` usually means you have accidentally got hold of a column instead of the table.

To disk and back is `to_csv` and `read_csv`; the latter also accepts a URL. The `index=False` when writing matters: without it the row index lands in the file as a column and comes back as `Unnamed: 0` when the file is read in again.

```python
print(type(df["sepal_width"]))

df.to_csv("example.csv", index=False, encoding="utf-8")
back_again = pd.read_csv("example.csv")
print(back_again)

import os
os.remove("example.csv")
```

Pandas has some of the best documentation in the Python ecosystem, and it is worth using instead of guessing: the [tutorials for getting started](https://pandas.pydata.org/docs/getting_started/tutorials.html), the API reference with a page per method (`pandas.DataFrame.groupby`, say) and the [overview of the data types](https://pandas.pydata.org/docs/reference/arrays.html).

That last one is worth a look because the Pandas type system goes beyond the Numpy types. Besides `int64`, `float64` and `bool` there are, among others:

| Kind of data | pandas data type | Scalar |
|---|---|---|
| point in time with time zone | `DatetimeTZDtype` | `Timestamp` |
| duration | – | `Timedelta` |
| period | `PeriodDtype` | `Period` |
| interval | `IntervalDtype` | `Interval` |
| integer with missing values | `Int64Dtype`, … | – |
| categorical values | `CategoricalDtype` | – |
| sparse column | `SparseDtype` | – |
| strings | `StringDtype` | `str` |
| truth values with missing values | `BooleanDtype` | – |

Two of them will turn up again: `CategoricalDtype` in one-hot encoding in [Regression analysis](linear-regression.ipynb) and the timestamps in [Data cleaning](data-cleaning.ipynb). That the column `species` above has type `str` rather than `object` is new – up to pandas 2, strings lived in an `object` column, which you will still see often in older code and older tutorials.

## SQL and relational databases

Anything that can be expressed in SQL can be expressed with DataFrames, and the rest of this chapter puts the two side by side. A few words on where that language fits are in order first.

**SQL** is the standard query language for relational database systems (RDBMS) and has been an ISO standard since 1987. The two most widespread free systems are MySQL and PostgreSQL; they sit behind a large share of the smaller websites – every WordPress installation comes with a database, and WordPress powers something like 40% of all websites. Anyone pulling data out of a live system almost always writes SQL first and only then carries on in Python.

The most important commands, grouped by what they touch:

| Command | Effect |
|---|---|
| `SELECT` | select data and output it |
| `INSERT INTO` | add rows |
| `UPDATE` | change existing values |
| `DELETE` | delete rows |
| `CREATE` / `ALTER DATABASE` | create, change a database |
| `CREATE` / `ALTER` / `DROP TABLE` | create, change, delete a table |
| `CREATE` / `DROP INDEX` | create, delete a search key |

A query then looks like this, for example:

```sql
SELECT "Petal.Width" FROM iris;
```

The vocabulary of the relational world is worth knowing, because you meet it constantly in documentation and error messages:

:::{admonition} Definition
A **database schema** is a finite, non-empty set of **relation schemas**; a relation schema is the formal description of the data types of a **relation**. A relation is what you see as a **table**: its rows are called **tuples** or *entities*, its columns are called **attributes**, and the values permitted for an attribute form its **domain**.
:::

Translated into the Pandas world:

| relational | Pandas |
|---|---|
| relation / table | `DataFrame` |
| attribute / column | `Series` |
| tuple / row | a row, addressed via the index |
| domain | the `dtype` of the column |

One difference remains: mathematically, a relation is a *set* of tuples and so has no order, whereas a DataFrame keeps its rows in order and addresses them through an index. That is why SQL needs an `ORDER BY` to get any order at all – and why Pandas does not guarantee the row order after a `merge`.

## From SQL to Pandas

The comparison below follows the [Pandas documentation, section "Comparison with SQL"](https://pandas.pydata.org/docs/getting_started/comparison/comparison_with_sql.html). In each code cell, the SQL statement appears as a comment above the Pandas expression that does the same job.

```python
import numpy as np
import pandas as pd

url = (
    "https://raw.github.com/pandas-dev"
    "/pandas/main/pandas/tests/io/data/csv/tips.csv"
)

tips = pd.read_csv(url)
print(tips)
```

Operations on Pandas DataFrames produce copies; the original DataFrame stays unchanged.

```python
tips.sort_values("total_bill")
print(tips)
print("\nThat is not sorted at all! But the next one is:\n")
sorted_tips = tips.sort_values("total_bill")
print(sorted_tips)
```

Selecting columns is `SELECT`:

```python
# SELECT total_bill, tip, smoker, time FROM tips;
tips[["total_bill", "tip", "smoker", "time"]]
```

You can add new columns computed from old ones:

```python
# SELECT *, tip/total_bill as tip_rate FROM tips;
tips.assign(tip_rate=tips["tip"] / tips["total_bill"])
```

Filtering data (selecting rows by conditions) with `WHERE`

```python
# SELECT * FROM tips WHERE time = 'Dinner';
is_dinner = tips["time"] == "Dinner" # bitmask
tips[is_dinner]
```

Combining several conditions logically with `OR |` or `AND &`:

```python
# SELECT * FROM tips WHERE time = 'Dinner' AND tip > 6.00;
tips[(tips["time"] == "Dinner") & (tips["tip"] > 6.00)]
```

```python
# SELECT * FROM tips WHERE size >= 6 OR total_bill > 45;
tips[(tips["size"] >= 6) | (tips["total_bill"] > 45)]
```

Split the data into groups, compute aggregate statistics, put the groups back together – that is `GROUP BY` in SQL and `groupby` in Pandas:

```python
# SELECT sex, count(*) FROM tips GROUP BY sex;
tips.groupby("sex").size()
# tips.groupby("sex").count() would be wrong, that counts non-NA entries!
```

```python
# SELECT day, AVG(tip), COUNT(*) FROM tips GROUP BY day;
tips.groupby("day").agg({"tip": "mean", "day": np.size})
```

A trap that snaps shut again and again when you aggregate: **the mean of the group means is not the
overall mean**, unless all the groups are the same size. The overall mean is the *weighted*
mean of the group means, with the group sizes as weights. With the values $10, 20, 30$ in
group A and $5, 15$ in group B, the group means are $20$ and $10$ and their mean is $15$ – but
the overall mean of all five values is $16$. So if all you have is a table of group means and
you want the overall mean from it, you need the `size` column as well. The same phenomenon in
a sharper form – group means whose order reverses when the groups are pooled – is called
*[Simpson's paradox](descriptive-statistics.md)*.


```python
small = pd.DataFrame({"Group": ["A", "A", "A", "B", "B"], "Value": [10, 20, 30, 5, 15]})
group_means = small.groupby("Group")["Value"].mean()
print("mean of the group means:", group_means.mean())
print("overall mean:           ", small["Value"].mean())
# correct: weight by the group sizes
sizes = small.groupby("Group")["Value"].size()
print("weighted:               ", (group_means * sizes).sum() / sizes.sum())
```

```python
# SELECT smoker, day, COUNT(*), AVG(tip) FROM tips GROUP BY smoker, day;
tips.groupby(["smoker", "day"]).agg({"tip": [np.size, "mean"]})
```

Data rarely arrive in a single table. Suppose the Iris measurements sit in two files: one holds the species of each plant, the other its sepal width. Both have the same number of rows – but may we simply lay them side by side? Only if the rows are in the same order, and you cannot know that once anyone has sorted one of the files in between.

So every row gets a column that identifies it uniquely: a **key**. That can be a plain number, or some unique piece of information that already exists (a student number, an ISBN). When data come together from several sources and nobody can hand out numbers centrally, you use a **UUID** (*universally unique identifier*), a 128-bit random number for which a collision is so unlikely that it can be ignored. The column that uniquely indexes a table is called its **primary key**; when the same key turns up in another table to point at those rows, it is a foreign key there. That is the basic idea of relational databases. The chapter [Relational data](https://r4ds.had.co.nz/relational-data.html) of *R for Data Science* has a fuller account.

```python
import uuid

species = pd.DataFrame({"No": [1, 51], "species": ["setosa", "versicolor"]})
width = pd.DataFrame({"No": [51, 1], "sepal_width": [3.2, 3.5]})
print(species, "\n")
print(width, "\n")

# merged on the key No, so the order does not matter:
print(pd.merge(species, width, on="No"), "\n")

print(uuid.uuid4())  # a fresh UUID
```

It gets interesting when the two tables do not have the same keys. Then you have to decide what happens to the rows that find no partner – and that is exactly what separates the four kinds of join. The two tables below are built so that every case occurs: `A` exists only on the left, `E` only on the right, and `D` appears twice on the right.

```python
df1 = pd.DataFrame({"key": ["A", "B", "C", "D"], "value": np.random.randn(4)})
df2 = pd.DataFrame({"key": ["B", "D", "D", "E"], "value": np.random.randn(4)})
print(df1, "\n", df2)
```

```python
# SELECT * FROM df1 INNER JOIN df2 ON df1.key = df2.key;
pd.merge(df1, df2, on="key") # inner join is the default
```

The **inner join** keeps only the keys that occur in both tables: `B` and `D`. That `D` yields two rows is the most important point in the whole business – the key is not unique in `df2`, and a join then multiplies the rows on the other side. If a dataset is suddenly longer after a join than before, this is almost always where the problem lies.

```python
# SELECT * FROM df1 LEFT OUTER JOIN df2 ON df1.key = df2.key;
pd.merge(df1, df2, on="key", how="left")
```

The **left outer join** keeps every row of the left table, partner or not: `A` and `C` come along too, with `NaN` in the columns from `df2`. This is the commonest case in practice – you have a dataset and want to enrich it with extra information without losing any rows along the way.

```python
# SELECT * FROM df1 RIGHT OUTER JOIN df2 ON df1.key = df2.key;
pd.merge(df1, df2, on="key", how="right")
```

The **right outer join** is the same with the roles swapped: every row of `df2`, `E` included, and `NaN` where the left-hand side is missing.

```python
# SELECT * FROM df1 FULL OUTER JOIN df2 ON df1.key = df2.key;
pd.merge(df1, df2, on="key", how="outer")
```

The **full outer join** keeps everything, `A` to `E`, and pads both sides with `NaN` wherever nothing matches. Afterwards it is nearly always worth a look at `isna().sum()` to see how many gaps you have let yourself in for.

Changing data is `UPDATE` in SQL; in Pandas we simply run Python code, as if we were changing a single variable:

```python
# state before the change:
tips.sort_values("tip").head()
```

```python
# UPDATE tips SET tip = tip*2 WHERE tip < 2;
tips.loc[tips["tip"] < 2, "tip"] *= 2
tips.sort_values("tip").head()
```

```python
# state before the next change:
tips.sort_values("tip", ascending=False).head()
```

```python
# DELETE FROM tips WHERE tip > 9;
tips = tips.loc[tips["tip"] <= 9]
tips.sort_values("tip", ascending=False).head()
```

## A first look at a dataset

Pandas comes with the tools we discussed in the chapter on descriptive statistics – the ones that were still handwork with Numpy in the Titanic notebook. The 'broken' CSV from there is read directly by `read_csv`, which understands quotation marks. `info` shows the columns, the data types and how many values are missing: compare this with the Titanic notebook from the chapter on Numpy, where we converted the CSV into a TSV by hand, marked missing values with masks and counted groups with loops.

```python
titanic = pd.read_csv("titanic.csv")
titanic.info()
```

For every numerical column, `describe` gives the count, the mean, the standard deviation (with $n-1$ in the denominator!), the minimum, the quartiles and the maximum:

```python
titanic.describe()
```

For categorical columns, `value_counts` counts the levels:

```python
print(titanic["Sex"].value_counts())
print(titanic["Embarked"].value_counts(dropna=False))   # count the missing values too
```

`isna` spots missing values. You can discard them (`dropna`) or replace them (`fillna`), with the median for instance. How to deal systematically with missing values, outliers, text fields and dates is the subject of the chapter [Data cleaning](data-cleaning.ipynb).

Skewness and kurtosis from the chapter on expected values are built in as well; `kurt` gives the excess kurtosis, that is, the kurtosis minus $3$:

```python
print(titanic.isna().sum())

age = titanic["Age"].fillna(titanic["Age"].median())
print("skewness:", age.skew(), " excess kurtosis:", age.kurt())
```

Grouped summary measures come from `groupby`, as above:

```python
titanic.groupby("Pclass")["Age"].describe()
```